How it works

This is the only page in the SQL section that describes machinery. Nothing here changes how you write SQL; it explains why the contracts on the other pages hold.

Two tiers, one table

   bucket ─── database  (db_id = "<org>--<bucket>", the plane's only identity)
                 │
                 └── table ─── columns · primary key · partitions
                       │
                       ├── writes ──► WAL (S3, keyed by primary key)
                       │               │
                       │               └── compactor drains ──► Delta Lake commit log
                       │
                       └── reads ───► frontier check
                                        ├── log proven drained ──► Delta scan          (analytical)
                                        └── otherwise ───────────► overlay ∪ Delta       (strong, merged)

A table with a declared primary key is keyed: every INSERT, UPDATE, DELETE and MERGE against it — including one whose WHERE names non-key columns — is resolved to primary keys and appended to the WAL. A SELECT sees those appends immediately, because the read side overlays un-drained log entries on top of the Delta snapshot and deduplicates by key. The compactor later folds the entries into Delta as a normal commit.

A table with no primary key has no log. Its writes append straight to Delta and there is nothing to race against. Such a table gets a hidden _rowid key so that UPDATE and DELETE still address rows.

Every table is a real Delta Lake table in s3://<bucket>/…. External Delta readers can scan it; they see committed, drained data. Do not write to the Delta directory from outside — writes are the plane’s job.

Reads: read-your-writes with no knob

There is no freshness parameter, no consistency level and no replica to choose. Before each read the coordinator asks the writer one in-memory question: is this database’s log backlog empty on an authoritative shard?

The check is deliberately pessimistic. A wrong “not empty” costs one merged read; a wrong “empty” would return a result missing your own committed write, which the plane never does.

ServedBy

Responses over gRPC and HTTP carry which tier answered. The values reflect residency and path, not a consistency level you asked for:

Watermarks for gRPC and HTTP callers

The plane holds no session, so for callers outside the Postgres wire the watermark is the session. Every write returns a ULID (max_wal_ulid on Execute, wal_ulid on Upsert/Delete, max_ulid on WriteStream). Pass it as min_ulid on the next Query, GetRow or Execute and the read is guaranteed to observe at least that write. You rarely need it — reads are already strong — but it makes cross-process ordering explicit. Details on gRPC & HTTP API.

A strong read never lies

The overlay a merged read builds has a size cap. If compaction is far enough behind that the overlay would truncate, the read is aborted rather than served incomplete:

strong read aborted: the WAL overlay truncated at N entries, so the result would be
incomplete — compaction is catching up; retry shortly

Over gRPC this is FAILED_PRECONDITION; on the Postgres wire it surfaces as XX000 with that message. The compactor is already running by the time this can happen, so a retry a moment later succeeds.

Writes: routing

The planner classifies each write before it runs. The names below appear in EXPLAIN output and in gRPC responses; you do not choose them.

Two consequences: a non-key predicate is not a bypass — it goes through the log like everything else — and UPDATE / DELETE without a WHERE clause are refused (WHERE TRUE if you mean every row; TRUNCATE TABLE to clear a table).

rows_affected on a write counts log appends and tombstones. The insert-versus-update split is only known after the drain; force one with Compact and read its counters if you need it.

Concurrency, in one paragraph

There are no blocking locks. Each key in the log carries the ULID of its last write. A transaction records the keys it read under FOR UPDATE and the keys it writes; at COMMIT the writer checks that none of those keys has moved since, and aborts the whole block with 40001 if one has. Plain INSERT claims its key, so concurrent inserts of one key yield exactly one success and 23505 for the rest. The client-facing contract is on Transactions & concurrency.

Schema changes

Column metadata lives with the Delta table. ADD COLUMN widens the merged view immediately, so un-drained rows are read with the new column as NULL (or its DEFAULT) the moment the statement returns since 0.1.42. DROP COLUMN and widening retypes are recorded logically and applied at read time; no file is rewritten. Indexes are tables of their own (__idx_<table>_<col>, __uidx_…) maintained on the write path. See Schema changes.

History, time travel, retention

Delta keeps every commit. SELECT … FROM t VERSION AS OF n reads an older snapshot; RESTORE TABLE t TO VERSION AS OF n (or TO TIMESTAMP AS OF '…Z') rolls the table back. A restore is destructive to the tail: writes acknowledged after the target version are discarded and the un-drained log is wiped so they cannot resurface; indexes are not rolled back. VacuumTable removes files older than the retention window; the floor is 168 hours and cannot be bypassed, so a vacuum can destroy the history a time-travel query needs. The Time travel & restore recipe walks through it.

Pipeline-bound tables

A table can be created by binding a pipeline template instead of declaring columns. Its schema materialises on the first ingest, and rows land as objects arrive in the bucket — once an ingest rule is scoped at the pipeline. Creating the pipeline alone does not start ingesting. See Pipelines and the Pipeline-bound table recipe.