Tinybird's ability to delete data predates ClickHouse®'s lightweight delete functionality. Back when we shipped POST /v0/datasources/{name}/delete, the only way to delete data in ClickHouse was through a heavy mutation that rewrites parts on disk, and we exposed it in a safe way: it has worked fine for years, but it has never been cheap to run.
This was improved in ClickHouse via a native lightweight DELETE. Today we're exposing it in beta through a new endpoint, POST /v1/datasources/{name}/delete.
How Tinybird deletes used to work
POST /v0/datasources/{name}/delete runs an ALTER TABLE … DELETE WHERE … mutation under the hood. ClickHouse schedules it in the background, iterates through every part that could contain matching rows, rewrites each one without the rows that match the condition, and swaps the new parts in.
The result is durable and consistent, but expensive. The more partitions your condition touches, the more I/O and CPU the cluster has to spend just to remove a handful of rows. Because the mutation can take a while, the API returns a delete_data job that you poll until it reports done.
For the occasional cleanup, that's fine. For GDPR requests, surgical fixes after a bad ingest, or anything where you just want the row gone fast, the part-rewrite was hard to justify.
A new lightweight delete instead
The new endpoint runs DELETE FROM … WHERE … SETTINGS enable_lightweight_delete = 1. Instead of rewriting the affected parts, the ClickHouse database materializes a hidden _row_exists mask and reuses the unchanged column files in place, without copying any data. Queries filter those rows out through the mask at read time. The physical rows get dropped later when background merges run.
The practical difference:
- Deletes are acknowledged in milliseconds to a few seconds, not minutes or hours.
- The mutation only materializes the
_row_existsmask and reuses the rest of the column files in place, so the cluster isn't paying for a full part rewrite when you only wanted to mark a few rows gone. - Queries stop seeing the deleted rows as soon as the mutation finishes.
Same delete_condition semantics as /v0, plus a new optional partition parameter for when you want to scope the operation to a single partition (or a few).
Sync or async, your call
Two modes:
Synchronous (default). The call holds the connection open until the database confirms the mutation is done, then returns rows_affected and a mutation block describing the partitions touched. This is the mode you want for cleanup scripts, right-to-be-forgotten requests, and one-off fixes. Send the request and get the acknowledgement. The wait is capped at 60 seconds; if the mutation runs longer, you get a 200 OK with mutation.is_done = false and you can keep tracking it through the system.mutations table.
One caveat on rows_affected: the ClickHouse database doesn't expose the affected row count for lightweight deletes, so Tinybird computes it by counting the rows that match delete_condition immediately before issuing the delete. Treat it as a best-effort number, not a strict guarantee.
Asynchronous (wait=false). The call returns immediately with a job envelope. Poll the job through the Jobs API and each poll surfaces partition-level progress as the ClickHouse database works through them. Reach for this mode when the delete is large enough that you'd rather not hold a connection open, or when you already have a worker polling jobs and want to plug lightweight deletes into the same workflow.
When does it come handy
- GDPR and right-to-be-forgotten. Filter by user id, send the request, and your matching rows would be deleted.
- Removing errors, tests or dirty data. Bad ingest, wrong source, dev traffic leaking into production
- Partition-scoped cleanups. Combine
delete_conditionwithpartitionto narrow the delete down to specific partitions, so the ClickHouse database doesn't even consider parts in partitions you need to remove data from.
Try it
From the Tinybird CLI:
tb datasource delete events --sql-condition "user_id = 42" --lightweight-delete
Or directly against the API:
curl \
-H "Authorization: Bearer <token_with_ADMIN_scope>" \
-X POST "https://api.tinybird.co/v1/datasources/events/delete" \
-d delete_condition="user_id = 42"
Step-by-step examples live in Replace and delete data. The full parameter list is in the Data Sources API reference.
If you try it, tell us how it goes or join our Slack community. We're especially interested in places the lightweight path would help but doesn't quite cover what you need yet.