How eterDB works

1. Transactions, and what gets undone

Every change is a transaction. You rarely type the word. Run a bare UPDATE and Postgres wraps it in one for you:

UPDATE accounts
  SET balance = balance - 100
WHERE id = 7;

BEGIN;     ← added
  UPDATE accounts
    SET balance = balance - 100
  WHERE id = 7;
COMMIT;    ← added

It matters when one action is really several writes that must stand or fall together. A transfer is a debit and a credit, never one without the other. Your ORM's transaction block lowers to exactly that shape: a BEGIN, the writes, a COMMIT.

All of it is written or none of it is. Fail partway and Postgres discards the unit as if it never ran. Commit and the change survives a crash. That all-or-nothing unit is the natural thing to reverse, which is why eterDB undoes a transaction and not a stray row.

So the question is what a committed transaction leaves behind that's enough to run it backwards. Two things, and they're captured by two different halves of the system: the rows it wrote, and the rows it read.

2. The shape of the system

Exactly one job has to run inside the Postgres process: recording what each query read (§4). A read leaves nothing behind once the query finishes, and it's visible only inside the backend that ran it. Everything else runs outside the engine. Write history goes to a sidecar, storage snapshots to another, and the dependency graph and undo logic to extensions plus a separate metadata store.

Reverts are rare and don't need to be fast, so anything not required at commit time stays off the hot path. Hover any box for detail.

The in-engine part: a read-capture hook, an append-only file the reads are written to, transient per-backend bookkeeping of what has been read, and the functions that apply compensating DML. The dependency graph is not built in the engine. Captured reads are forwarded to the store, and the graph is derived there when an undo is requested.

Revert latency therefore depends on the transaction rather than on the database.

revert time   scales with     candidate read-edges + the txn's own ops
              independent of  database size, history length, txn age

A months-old target on a million-row database costs about what a fresh one with the same fan-out costs. The work happens at revert time, where seconds are acceptable, rather than at commit time.

3. Capturing writes

Undoing a write needs the row as it was before. Postgres writes one down already, without being asked. An UPDATE doesn't overwrite the row where it sits: it writes a new version and marks the old one dead, so a row that has just been debited physically holds two versions.

Each version carries two stamps: xmin, the transaction that created it, and xmax, the transaction that retired it (∞ while it's live). A reader sees whichever version was live when its own transaction began, so readers and writers never block each other. That's MVCC, and the dead version it leaves behind is exactly what an undo restores.

The last frame is the problem. VACUUM, Postgres's background cleanup, reclaims dead versions, and once it runs the old value is gone. eterDB can't count on a before-image still being in the heap an hour later, so it copies one out of the way at commit time. The before/after pairs come from logical decoding, the same stream that drives logical replication, which reads the WAL: the log Postgres writes every change to before it touches the table itself.

The WAL normally records just enough to redo a change, not the whole prior row. For a true before-image, set REPLICA IDENTITY FULL on the tables you want reversible. One statement per table:

ALTER TABLE orders REPLICA IDENTITY FULL;   -- WAL now carries the whole old row

The figure alternates it on and off. Watch what reaches the history:

All of this happens after commit. The transaction commits at normal speed and the history record follows from the WAL, read out of process by a sidecar that appends to the metadata store.

The replication slot needs operational attention. While the sidecar is down, Postgres retains WAL on its behalf. That retention keeps capture gap-free across restarts (exactly-once, tested), and it is also disk that grows until capture catches up. Watch slot lag when you run the stack yourself.

REPLICA IDENTITY FULL logs the entire old row on every update, not just the key. On wide rows or large TEXT/JSONB values that's write amplification, and the meter in the figure shows it. A TOASTed value is logged in full every time, which is why undo restores it even when the update never touched it. It's per-table: a table you never need to undo keeps the default identity and skips the cost.

4. The dependency a backup can't see

This is the part nothing outside the engine can reconstruct. B reads a value A wrote, then writes something derived from it:

A:  UPDATE config SET max_qty = 500;         -- writes 500
B:  SELECT max_qty FROM config;            -- reads A's 500
    INSERT INTO orders(qty) VALUES (500);   -- derives an order from it

undo A  →  config reverts, but B's order still says 500   ✗ silently wrong

B now holds a result computed from a value that no longer exists, and nothing flags it. eterDB records these read-dependencies so it can undo B too, or refuse and report it. Reads are the hard part: a write leaves a row, a read leaves nothing, so triggers and CDC never see one. The figure replays the same undo from both views:

eterDB doesn't add new tracking for this. Postgres already has it, in the machinery behind SERIALIZABLE. The default isolation level, READ COMMITTED, is loose and fast. SERIALIZABLE instead guarantees a result that some serial order of the transactions would have produced, and aborts a transaction with a 40001 error rather than let it break that guarantee.

Deciding what to abort means knowing who read what. So SSI (Serializable Snapshot Isolation), Postgres's algorithm for SERIALIZABLE, records each read as a predicate lock: a note that this transaction read the rows matching some condition, which blocks nobody. When another transaction writes a version of that data, SSI draws an rw-antidependency edge between the two, meaning "A read some data, and B wrote a version of it A didn't see." That edge is the relationship that makes undoing A alone unsafe, which makes it precisely what eterDB wants.

The patch reads the same tracking in observe mode, under ordinary READ COMMITTED:

• No behavior change. Queries return what they returned before. Observe mode produces no 40001s and no serialization failures.
• Small, flat cost. A few percent of read overhead that doesn't grow with concurrency, and no shared-memory tuning. An earlier design cost far more (§8).
• Strict mode is still there. Full SERIALIZABLE, with the usual 40001 retries, if you want it.

Predicate locks coarsen under memory pressure, from tuples to pages to whole relations, which can make the graph over-approximate. Coarsening only ever adds candidate edges, never drops a real one, so it costs a review, not a missed dependency. §5 shows how each edge is labelled.

Naming the row that was read

An edge is only useful if it names a row that can still be found later. Postgres's own name for a row is its physical address, the ctid. A table on disk is a stack of fixed-size 8 KB pages; each page keeps an array of line pointers at the top, one per row version stored in it, and the ctid is which page plus which slot:

ctid = (0, 2)
        │   └── slot 2   line pointer inside the page
        └────── page 0   which 8 KB block

A ctid is not stable. When a version dies and vacuum frees its slot, an unrelated row can move in, and the same address comes to mean a different row:

Record "row R is at (0,2)", look it up later, and you may read row S. So eterDB identifies a read by the row it touched, as (table, primary key), never by address. An early build got the timing of that translation wrong.

5. Computing an undo

eter preview <txid> builds the graph from the store and classifies the target. If nothing depends on it, the transaction is clean: eterDB generates the inverse DML from the captured before-images and, with --apply, runs it against your tables. If later transactions read or overwrote those rows, it's dependent, and a blind revert would corrupt them, so eterDB reports the affected set and gives you the choice.

History is in a separate store, so an undo spans two databases. The part that must be atomic stays in one: eterDB reads the plan and before-images from the store, then applies all the compensating DML to your database in a single transaction. Both failure modes resolve safely.

• Crash mid-undo. One transaction, so it fully commits or fully rolls back. Never half-reverted.
• The inverse DML can't apply, say a unique constraint added since would reject the restored row. The transaction aborts, the undo reports failed, nothing changes.

The revert doesn't trust its own preview either. --apply re-runs classification inside the applying transaction, so a dependent that committed after you previewed blocks a clean_only undo instead of slipping through. Concurrent writers to the same rows queue behind ordinary row locks. A concurrent reader doesn't block: it can read a pre-undo value mid-revert, and that read is captured like any other, so it surfaces as a dependent afterwards rather than disappearing.

A cohort undo (eter undo-cohort) reverses every write since an incident marker. One transaction with a live dependent doesn't abort the batch: eterDB reverts the clean ones and returns reverted_txns, skipped_dependent and skipped_txids, so you or the agent decide about the rest. Every command supports --json and returns stable exit codes.

Reading a blocked undo

A yes/no answer isn't enough when a transaction is dependent. You need to see what depends on it. A config write (txid 4821) was read by later transactions before you caught it, and preview returns everything that depends on it:

$ eter preview 4821 --json{
  "txid": 4821,
  "classification": "dependent",
  "op_count": 1,
  "ops": [
    { "table": "public.app_config", "original_op": "U",
      "compensating_op": "UPDATE", "pk": { "key": "max_order_qty" } }
  ],
  "conflicts": [4822, 4825, 4830],
  "conflict_edges": [
    { "txid": 4822, "kinds": ["rw"],       "precision": "exact",       "rw_granularity": "tuple" },
    { "txid": 4825, "kinds": ["rw"],       "precision": "over-approx", "rw_granularity": "relation" },
    { "txid": 4830, "kinds": ["ww","rw"],  "precision": "exact",       "rw_granularity": "tuple" }
  ],
  "precision": {
    "exact_dependents": 2,
    "over_approx_dependents": 1,
    "coarse_tables": ["public.order_audit"]
  },
  "external_refs": { "count": 0, "kinds": {}, "samples": [] },
  "dependency_basis": "write-write exact + read-write from persisted SSI graph"
}

On a busy database, one write to a hot row (a config, a feature flag) is read by many later transactions, and the dependent set grows fast. Two things keep that from collapsing into "everything depends on everything":

• Exact vs over-approximate edges. Every dependent is labelled. exact means it provably read the reverted row. over-approx means it only looks dependent because a writer seq-scanned the table and SSI coarsened the lock to relation level (the dashed edge above). Those are candidates to review, not confirmed reads. coarse_tables names the tables driving them: index the columns those writers scan and the reads become tuple-precise.
• You choose how far to reverse. clean_only (the default) refuses and reports when dependents exist. cascade reverses the dependents too, newest-first. targeted reverses this transaction only and leaves the dependents to diverge. A hot-row write is still reversible; the preview above gives you what you need to pick a mode.

eterDB reverses database state. A reverted row that already charged a card or sent an email surfaces under external_refs in the same preview, so you see the external fallout before you decide.

6. Schema changes and time travel

Row-level undo can't restore a dropped table. A DROP isn't row data. The heap is gone and no before-image in eter.history covers it, so destructive DDL needs a storage answer. Ours was ZFS: put the data directory on a ZFS dataset, let a sidecar take copy-on-write snapshots, and recover by clone + WAL replay + extract. Snapshots copied nothing up front and clones materialized in milliseconds.

Deploying it was the problem. ZFS is a host kernel module, so it wants a privileged container on a Linux host you control (§7 has the full tally). Every deployment pays that, always, to cover a rare event.

The sidecar was already a point-in-time-recovery engine. WAL archiving, replay to an exact position, standing up a throwaway Postgres, extracting one object: all substrate-agnostic. ZFS supplied two things, a base image and its materialization, and both have standard unprivileged Postgres equivalents. So the sidecar now takes pg_basebackup base backups alongside the WAL archive. Recovery copies the newest backup from before the change, replays archived WAL forward to just before the drop, and pulls the object out of the copy. No root, no kernel module, no privileged container.

Every recovery reads from a copy. The live database stays up and is never restored over. An event trigger records destructive DDL in eter.ddl_log with the WAL position it happened at, so eterDB knows exactly how far to replay: one instant before the drop.

• The substrate is standard Postgres PITR. There is no page store, and destructive DDL is never rewritten inside the database (rename-to-tombstone plus views). Both of those make the production database harder to reason about.
• The backup is only a base. WAL replay covers everything after it, so backup cadence bounds how long a recovery replays, not how much it recovers. Writes made after the last backup and before the drop come back too.
• As-of reads. Read a table as it was before a bad deploy without restoring anything, then revert the deploy's writes. Unrelated writes in the same window survive.
• What it costs. A ZFS clone materialized in milliseconds. A recovery now copies a base backup and replays WAL, which is minutes on a large database, and backups are full physical copies rather than shared blocks. In exchange the storage tier runs anywhere containers run, which every deployment gets and a recovery costs only when one happens.
• History is never pruned. eter.history is append-only. Base backups and WAL segments are retained by default too, so a months-old restore still works. History grows by one row image per write to a tracked table; backups and WAL you can bound with ETER_BACKUP_RETAIN_COUNT or ETER_BACKUP_HORIZON_DAYS.

7. Deploying it

eterDB is the Postgres: a patched engine build with observe mode and the eter_ssi background worker, the way Aurora runs on Aurora and Neon runs on Neon. So the thing to know is what deploying it takes. It used to take a lot:

Concretely: two containers sharing one volume. The engine has the patched build baked in, so you never compile Postgres. It runs with no history trigger on your write path, and pre-creates the capture slot on first boot, which makes change capture gap-free even if the rest of the stack starts late.

The control plane bundles everything else behind one supervisor: the capture sidecar from §3, part of the stack and not an option, reading the slot out of process; the orchestrator carrying the storage machinery; and a separate metadata store holding history, the dependency graph and the backup catalog. That metadata has to survive the tenant failing, so it is a database of its own even inside that one container. The CLI and agents talk to one URL, the orchestrator's API. Recoveries take minutes, so they run as managed background jobs: queued, one at a time, reporting crashes instead of hanging a session.

The patch is nine files and about 390 lines, tracked against upstream. The isolation and regression suites pass on the patched binary, and thirteen extensions, pgvector included, build against it and pass their own suites. It targets one major at a time; today that's 18.

You run it yourself, on anything that runs containers or plain processes: the engine with wal_level=logical and a replication entry in pg_hba.conf (base backups arrive over the replication protocol), plus the control plane next to it. Apps connect over the normal Postgres wire protocol with any driver.

8. Trade-offs and overhead

Capturing writes is nearly free and off the commit path (§3). Observe mode is where the cost was, and the first implementation put it in the wrong place. Profiling found it and took read overhead from ~50% to ~3%.

The first pass reused Postgres's own machinery. A read leaves no row behind, so there's nothing to trigger on, but Postgres already solves that: SSI tracks who-read-what with predicate locks to enforce SERIALIZABLE. So observe mode registered a serializable transaction under each query, let stock SSI take the locks, and harvested them at commit. Reusing a subsystem hardened over a decade produced a correct graph, at about half the read throughput under load: ~27% overhead at 16 clients, ~53% at 100, worsening as concurrency rose.

Where the cost actually was. The assumption, including in the ticket that filed it, was that the predicate locks cost the throughput: an SIREAD lock on every read, the machinery that makes native SERIALIZABLE expensive. Profiling a busy backend under a pure read workload, before optimizing anything, put taking the locks at 0.2%. Two-thirds of the backend's wall-clock sat in semop at commit, asleep on one cluster-wide lock.

The cost was the teardown. Registering that hidden serializable transaction meant unlinking it at commit, which took one global lock (SerializableXactHashLock) exclusively, every time. At tens of thousands of commits a second, every backend queued behind every other, and the wait grows with concurrency, which is the rising curve in the figure below. Three lines of commit-time bookkeeping, and they bite only under load.

The fix. Postgres keeps predicate locks in shared memory for one reason: so other backends' writes can detect a conflict against your reads. Observe mode never needs that. Nobody reads another backend's locks, and dependency matching happens later, out of process, by (table, primary key), so the registration, the shared lock table and the commit-time teardown all come out. Observe now keeps its read-set backend-local. Same coarsening and promotion logic, per-backend memory, no shared transaction to register, nothing to tear down at commit. Nothing touches shared memory on the hot path.

Read overhead after the change:

The workload is pgbench point-select traffic (-S) at 16 to 100 clients against the production-shape (assertions-off, -O2) build, observe on vs off on the same binary. The harness is test/observe-read-scaling.sh. Writes improved too, since the same teardown is gone for them, settling around ~13%. That residual is the work of streaming the read-set out at commit.

The completeness harness holds 100% read-edge recall across its adversarial seeds, and the patch passes the isolation (119/119) and regression (231/231) suites on PG 18 with observe off. Flip observe on and a read-heavy workload writes tens of thousands of read-set records to disk. Flip it off and it writes zero.

Observe serializes parallel query. Capture is in one backend and a parallel worker's reads would bypass it, so with observe on the planner runs every query without parallel workers. Point reads and OLTP writes never notice. A big analytical aggregate that used four workers does, and for that workload the lost parallelism dominates the 2-3%. It is settable per role: a role whose reads need no capture (a BI tool, a read-only analyst) turns observe off for itself and keeps full parallelism. Capturing from parallel workers is future engine work.

9. What's next

More profiling. Mixed read/write workloads, larger datasets, longer runs, plus the write path's remaining lever: moving commit-time read-set emission off the backend entirely.

Testing at realistic parameters. The recovery suite already runs against real application schemas: an inventory system at ~1M rows with months of backdated history, a CRM with cross-table cohorts. Next are the dials production turns: bigger databases, longer histories, higher churn, more concurrent writers.

Shrinking the dependency radius. Today's dial is §5's exact/over-approx split plus the index hint. The next one is per-source scoping: keeping reads from a read-only analytics role out of the graph, so the dashboard that scans every table doesn't surface as a dependent of every write.

Storage-compute separation. Today's substrate is unprivileged and runs anywhere (§6), and a restore is a replay, so recovery time grows with how far back you reach. The end-state is a page server in the Neon mold, retaining every version of every page and serving reads as of any point in time. "This table as of 13:59" becomes a lookup rather than a replay, and recovery drops from minutes to seconds. It is a large piece of engineering, so it comes after the launch.

Further reading

Everything above is compressed to fit one scroll. These are the sources behind it.

• PostgreSQL 14 Internals ↗ Egor Rogov (free book) The MVCC, tuple versioning and WAL chapters behind §3, and the page layout behind §4.
• Architecture of a Database System ↗ Hellerstein, Stonebraker & Hamilton (survey) How a relational engine is assembled from parts. The backdrop to §2.
• Designing Data-Intensive Applications ↗ Martin Kleppmann Chapter 7 on transactions and isolation levels, including the serializability that read-dependency tracking builds on.
• Logical Decoding ↗ PostgreSQL documentation The change stream the capture sidecar reads for every write's before and after image (§3).
• Serializable Snapshot Isolation in PostgreSQL ↗ Ports & Grittner (VLDB 2012) The predicate-lock and rw-conflict machinery eterDB reuses (§4).
• Continuous Archiving and Point-in-Time Recovery ↗ PostgreSQL documentation The base-backup plus WAL-replay substrate behind schema recovery and time travel (§6).