Skip to Content
We are live but in Staging 🎉
Data EnginesSQLAPI ReferenceExecute (SQL)DELETE

DELETE

DELETE through Tables.Execute. It mirrors UPDATE route for route — the same predicate classifier serves both. For the RPC contract, see the Execute hub.

The response is a row count only — ExecuteResponse.rows_affected. There is no strategy, pending_drain or noop field on the wire.

DELETE requires a WHERE clause

DELETE FROM t with no predicate is refused:

DELETE without a WHERE clause is not allowed. Use WHERE TRUEto delete every row, orTRUNCATE TABLE to clear the table while keeping the schema.

Source: dodil-tables/crates/htap-planner/src/dml.rs:240-253. DELETE FROM t was the historical footgun the rule exists to stop.

To empty a table, use TRUNCATE TABLE — one empty Delta commit plus a WAL/overlay purge, all-or-nothing, keeping the schema, primary key, secondary indexes, graph bindings and reservations.

Keyed deletes

On a table with merge_keys, every delete becomes WAL tombstones; the compactor builds the Delta DELETE predicate on the next drain. Safe under concurrent writes.

-- KeyedDelete — point delete on the PK
DELETE FROM events WHERE id = 1 AND user_id = 'u-101';
 
-- KeyedRangeDelete — a range over a single-column PK
DELETE FROM events WHERE id BETWEEN 1000 AND 2000;
 
-- KeyedDeleteFromSubquery — an explicit key list
DELETE FROM events
WHERE (id, user_id) IN (SELECT id, user_id FROM events_to_purge);

rows_affected is the tombstone count. Ranges over a composite PK are refused rather than guessed.

Non-keyed deletes

Same story as UPDATE, and the same narrowing: a non-PK predicate on a keyed table no longer bypasses the WAL. The planner synthesises SELECT <pk> FROM <target> WHERE <predicate> and routes it as a keyed delete, so the compactor cannot clobber it on the next drain.

-- Routes as KeyedDeleteFromSubquery, not a Delta-direct write
DELETE FROM events WHERE occurred_at < TIMESTAMP '2025-01-01 00:00:00';

NonKeyedDelete survives only for tables that declare no pk_columns — those have no WAL prefix to race, and the planner attaches a warning saying so. See UPDATE → Non-keyed updates for the full explanation.

Reclaiming the space

A delete is logical. The files stay until:

  1. Compact drains the tombstones into Delta, then
  2. OptimizeTable rewrites the surviving rows into packed files, then
  3. VacuumTable removes the superseded files past the retention window.

Retention has a 168-hour floor with no bypass — see Maintenance → VacuumTable.

See also

UPDATEMERGE