How to safely cancel a database query

Tinybird has introduced a feature allowing users to cancel queries, addressing a limitation in their system that previously prevented this capability. The challenge stemmed from technical complexities associated with query cancellation, particularly when queries originate from clients like the Tinybird web app or CLI. Traditionally, database query IDs are assigned at the API layer, meaning the UI is unaware of these IDs until the query is complete, rendering cancellation efforts ineffective. To resolve this, Tinybird now delegates query ID assignment to the UI, which then communicates this ID to the API server, enabling the client to initiate a query cancellation request. This approach overcomes earlier limitations by allowing safe and targeted query cancellation, carefully avoiding disruption of critical background processes through a unique query ID strategy, endpoint restriction, and targeted server cancellation. These measures ensure that only specific queries can be safely terminated, maintaining system integrity without overloading replication services like ZooKeeper. This feature highlights the complexity behind seemingly simple functionalities and offers a framework for other products considering similar implementations.