Deletes, Updates & the Vacuum

DistributedStorageMaintenance

Beta Works · surface still evolving · DELETE, UPDATE and the vacuum are proven under concurrent readers; UPDATE's SET is limited to integer literals.

DELETE

DELETE FROM orders WHERE amount < 10;

The WHERE compiles exactly like a SELECT’s and runs on the worker that owns the table; the reply is the row count. Matching rows are marked dead, not removed: the next query skips them, the bytes stay until the vacuum reclaims them. A WHERE clause is required — a bare DELETE FROM t is refused rather than silently truncating the table.

UPDATE

UPDATE orders SET status = 3 WHERE customer_id = 42;

An update is a delete of the old versions and an insert of the new ones. The statement returns after both; the old versions are dead rows for the vacuum. In this release SET values are integer literals and a WHERE is required.

The vacuum

Every worker runs a background sweep:

AETHERIUS_VACUUM_INTERVAL=60   # seconds, the default

Each sweep looks at every tenant’s files and rewrites those where more than half the rows are dead, into a dense new file. It only touches files that are sealed — finished, and not written to recently — so a container still receiving data is never in play. The switch to the new file is a single step: readers in the middle of a query keep the old file until they finish, new queries get the new one, INSERTs are never blocked, and nothing takes a lock.

The rewrite carries everything the file owns: surviving rows keep their deleted-row marks where a delete is still in flight, secondary indexes are rebuilt over the renumbered rows, and text data is compacted so that strings belonging only to dead rows are dropped too. Files written by transactions that were rolled back or reaped are removed on the same sweep.

What to expect on disk

• One INSERT statement produces one container file. Many small inserts produce many small files; batch rows into one INSERT … VALUES (…), (…) where you can. The vacuum compacts within a file, it does not merge files.
• A rewritten file appears as <name>_v2.acf (then _v3, …) with its sidecars beside it; the old file and its sidecars disappear when the last reader lets go.
• Snapshots taken before a vacuum keep the old files alive for as long as the snapshot exists.

Limits in this release

• UPDATE … SET accepts integer literals only.
• Between an update’s two stages a concurrent reader may see neither version.
• The vacuum compacts a file whose dead share exceeds 50 %; the threshold is not configurable.

Cluster Transactions →

Why a deleted row may still be visible to an older reader.

Secondary Indexes on Cluster Tables →

Indexes are rebuilt by the same sweep.

Multi-Tenancy →

Snapshots hold files the vacuum would otherwise remove.