How eterDB works

1 · A change is a transaction, and a row lives in a page

First, every change is a transaction. You rarely type the word. Run a bare UPDATE and Postgres silently wraps it in one:

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

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

Why care, if you never type the word? Because sometimes one action is really several writes that must stand or fall together — a bank transfer is a debit and a credit, and you never want one without the other. That's when you reach for a transaction. You still don't write BEGIN/COMMIT by hand — your ORM's transaction block is the same thing, and it lowers to exactly that:

# your ORM's transaction block…
with db.transaction():
    accounts.debit(7, 100)
    accounts.credit(9, 100)

# …is this underneath:
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 7;
  UPDATE accounts SET balance = balance + 100 WHERE id = 9;
COMMIT;   -- both land — or one fails, neither does

Either all of it lands or none of it does. If anything fails partway, Postgres discards the whole unit as if it never ran; once it commits, the change is durable and survives a crash. That all-or-nothing unit is the natural thing to reverse, which is why eterDB undoes a transaction, not a stray row.

Second, a row lives in a page. A table isn't one long list of rows. On disk it's a stack of fixed-size 8 KB blocks called pages, and every row sits inside one of them. To change a row, Postgres loads its page, edits the bytes, and writes the change to a log first so a crash can't lose it. So every row has a physical address — which page, which slot — and, as the next section shows, that address can move.

2 · A row is a stack of versions, and its address can move

You'd expect an UPDATE to overwrite the row where it sits. It doesn't. Instead it writes a new version and marks the old one dead — so right after the transfer above, row 7 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 still live). A reader sees whichever version was live when its own transaction began, so readers and writers never block each other — this is MVCC, multi-version concurrency control. Play it forward:

That dead version is the row exactly as it was before the change — precisely what an undo restores. The catch: VACUUM, Postgres's background cleanup, eventually reclaims dead versions, and once it runs the old value is gone. So eterDB can't count on the dead version still being there an hour later; it copies that before-image out of the way, covered in capturing writes.

Where a version sits, and why its address changes

That physical address from section 1 has a name — the ctid — and a simple shape. Each page keeps a little array of line pointers at the top, one per version, and the ctid is just which page and which of those slots:

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

Here's the wrinkle that returns in part 2: a ctid is not stable. When a version dies and vacuum frees its slot, a later unrelated row can move in — so the same address can come to mean a different row. Step through exactly that:

Record "row R is at (0,2)", look it up later, and you might read row S instead. eterDB shipped exactly this bug and later fixed it; the read-time fix in part 2 comes back to it once there's enough machinery to explain it.

1 · The shape of the system

Start with the layout before the mechanics. Just one job has to run inside the Postgres process: capturing predicate reads (see the dependency a backup can't see). A predicate read leaves nothing behind once the query finishes, and it's only visible inside the backend that ran it, so nothing outside the process can observe it. Everything else runs outside the engine. Write history lives in a sidecar, storage snapshots in another, and the dependency graph and undo logic in extensions and a separate metadata store.

This split is on purpose. Reverts are rare and don't need to be fast, so anything not required at commit time is kept off the hot path and out of the engine. The figure traces it: writes and the read-set stream out across the engine boundary after each commit, and the revert path flows back in only on demand. Hover any box for detail.

The in-engine footprint is small: the read-capture hook, an append-only file the reads are written to, the patch's transient predicate-lock bookkeeping, and the functions that apply compensating DML. The dependency graph is not built in the engine; the captured reads are forwarded to the store and the graph is derived there, only when an undo is requested.

Building the graph on demand sets an honest — and reassuring — expectation about revert latency. What it costs depends on the transaction, not the database:

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

So a months-old target on a million-row database costs about the same as a fresh one with the same dependency fan-out. The work shows up only at revert time, where seconds are fine — never at commit time, where they aren't.

2 · Capturing writes

Undoing a write needs the row as it was before. Since vacuum will eventually erase the dead version (row versions), eterDB has to record the before-image at commit time and keep it. It gets before/after pairs from Postgres logical decoding (the same stream that drives logical replication), which reads the WAL, the append-only log every change is written to first.

The WAL normally records just enough to redo a change, not the whole prior row. To capture a true before-image, eterDB sets REPLICA IDENTITY FULL on the tables you want reversible — one statement per table, which tells the WAL to log the entire old row:

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

Toggle it in the figure and watch what reaches the history:

This happens after commit, not during it. The transaction commits at normal speed; the history record follows from the WAL. A sidecar reads the replication slot out of process and appends to the metadata store.

That before-image isn't free, as the meter in the figure shows: REPLICA IDENTITY FULL writes the entire old row into the WAL on every update, not just the key. On tables with wide rows or large TEXT/JSONB values that means real write amplification and more work for the capture sidecar (TOASTed values are logged only when they change, which softens the worst case but doesn't remove it). The trade-off is explicit and per-table: a table you don't need to undo can keep the default identity and skip the cost, at the price of not being row-level reversible.

The single-process demo and the test suite can fall back to an in-database trigger as an oracle, but logical decoding is the shipping path. test/capture-diff.sh checks that the two produce identical history on the same workload, that undo still works with the trigger removed, and that restarting the sidecar doesn't double-apply.

3 · The dependency a backup can't see

This is the part nothing outside the engine can reconstruct. Suppose 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 — wrong, with nothing to flag it. eterDB records these read-dependencies so it can either undo B as well or refuse and report it. And reads are the hard part: a write leaves a row, but a read leaves nothing, so triggers and CDC never see it. Switch this figure between a backup's view and eterDB's, then press undo:

eterDB doesn't add new tracking to see a read that leaves no trace; Postgres already has it. To enforce SERIALIZABLE, the engine records which transactions read data another wrote, using predicate locks and the rw-antidependency edges between transactions. The patch reads that same data in observe mode, under ordinary READ COMMITTED:

• No behavior change. Queries return exactly what they returned before, and observe mode adds no serialization failures.
• One cost, and it's memory. It holds more predicate locks, so a busy database needs max_pred_locks_per_transaction raised.
• Strict mode stays optional. A full SERIALIZABLE mode, with the usual 40001 retries, is there if you want it.

Lock coarsening can make the graph over-approximate, occasionally recording a dependency that wasn't really there. Coarsening only ever adds candidate edges, never drops a real one, so it costs an extra review, not a missed dependency. Completeness itself — that no genuine read-edge is ever lost — is the property the adversarial harness in the aside below exists to gate. Computing an undo shows how each edge is labelled.

4 · 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 instead of guessing and gives you the choice.

Because history lives in a separate store, an undo spans two databases — but the part that must be atomic stays in one place. eterDB reads the plan and before-images from the store, then applies all the compensating DML to your database inside a single transaction. Every failure mode then resolves the safe way:

• Crash mid-undo. The revert is one transaction, so it either fully commits or fully rolls back — your data is never left half-reverted.
• The inverse DML can't apply — say restoring a row would now violate a unique constraint added since. The transaction aborts, the undo is reported failed, and nothing changes; no partial revert.
• The "undo happened" marker fails to write back (it keeps the reversal from being re-captured as fresh history). Best-effort and cosmetic: worst case is an unstamped marker, never a corrupt revert.

A cohort undo (eter undo_cohort) reverses every write since an incident marker. If one transaction in the cohort has a live dependent, it doesn't abort the whole batch; it reverts the clean ones and returns reverted_txns, skipped_dependent, and skipped_txids, so you or the agent decide what to do with the rest. Every command supports --json and has stable exit codes.

Reading a blocked undo

When a transaction is dependent, a yes/no answer isn't enough — you need to see what depends on it. Say a config write (txid 4821) was read by later transactions before you caught it. preview returns the full blast radius:

$ 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"
}

There's a real worry here: on a busy database a single write to a hot row (a config, a feature flag) is read by many later transactions, and its 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 the dependent 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 in the figure above). Those are candidates to review, not confirmed reads. coarse_tables names the tables driving the coarse edges, and eter coarse suggests the index that makes those reads tuple-precise and shrinks the radius.
• You choose how far to reverse. undo takes a mode: clean_only (the default) refuses and reports when dependents exist; cascade reverses the dependents too, newest-first; targeted reverses only this transaction and leaves the dependents, which may then diverge. A hot-row write is still reversible; how far to reverse is a decision about blast radius, and eterDB gives you the data to make it.

Per-source scoping (for example, ignoring reads from read-only analytical roles so they never enter the graph) is a natural next lever and isn't built yet. Today the dial is the exact/over-approx split plus the index hint.

One boundary to be clear about: eterDB reverses database state. If a reverted row already caused an external effect — a charged card, a sent email — that surfaces under external_refs in the same preview, but eterDB doesn't claim to undo it.

5 · Schema changes and time travel

Row-level undo can't restore a dropped table. A DROP isn't row data — the heap is simply gone, and no before-image in eter.history covers it. Recovering from destructive DDL needs a storage answer, and our first one was ZFS: put the data directory on a ZFS dataset and let a sidecar take copy-on-write snapshots. A snapshot copied nothing up front, a clone materialized in milliseconds, and recovery was clone + WAL replay + extract. It worked.

Then we tried to deploy it. ZFS is a host kernel module: it demanded a privileged container with /dev/zfs passed through, on a Linux host you control — no Cloud Run, no Fargate, no Autopilot, and a "managed Postgres" whose storage tier dictates your host kernel. That is a disproportionate price for destructive-DDL recovery — a rare event — paid by every deployment, always.

The way out was noticing that 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 of it was substrate-agnostic. ZFS supplied only two things, the base image (a snapshot) and its materialization (a clone), and both have standard, unprivileged Postgres equivalents. So today the sidecar 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.

A dropped table, dropped column, or pre-migration value is recovered from a copy: the live database stays up and is never restored over. Destructive DDL is recorded by an event trigger in eter.ddl_log together with the WAL position it happened at, so eterDB knows exactly how far to replay — one instant before the drop.

• No custom storage engine, no DDL rewriting. We didn't build a page store, and we don't rewrite destructive DDL inside the database (rename-to-tombstone plus views), because both make the production database harder to reason about over time. The substrate is standard Postgres point-in-time recovery: base backups plus archived WAL.
• The backup is only a base. WAL replay covers everything after it, so backup cadence bounds how long a recovery replays — never how much data it recovers. Writes made after the last backup and before the drop come back too.
• As-of reads. You can 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.
• The honest cost. A ZFS clone materialized in milliseconds; a recovery now copies a base backup and replays WAL — minutes on a large database. And backups are full physical copies, not shared blocks. That trade bought the one property that matters: the storage tier runs anywhere containers run. Recoveries are rare; deployments are forever.
• No pruning of history. eter.history is append-only and never pruned, and by default every base backup and archived WAL segment is retained too, so a months-old restore still works. Bounding backup retention is an explicit operator opt-in, not a default. Tested on an InvenTree dataset scaled to about 1M rows with history backdated 60 days.

6 · How to deploy it

eterDB isn't an extension you install into somebody else's Postgres — it is the Postgres, a patched engine build (observe mode, plus the eter_ssi background worker). Aurora runs on Aurora and Neon runs on Neon; eterDB runs on eterDB. So the question that matters isn't which managed database it runs on — it's what deploying it takes. For a while, the honest answer was embarrassing:

Concretely: a few small containers sharing one volume. The engine image — the patched build ships inside it, you never compile Postgres. The capture sidecar from §2 — part of the stack, not an option: the engine runs with no history trigger, and the slot is created at first boot so capture is gap-free even if the sidecar starts late. The orchestrator image, which carries the storage machinery. And the metadata store — a separate small Postgres the stack owns, holding history, the dependency graph and the backup catalog: that data exists to survive the tenant having a very bad day, so it never lives inside the tenant. The CLI and agents talk to one URL, the orchestrator's API; recoveries run as managed background jobs — they take minutes, so they're queued, run one at a time, and report crashes honestly instead of hanging a session.

For the closed alpha we run that stack for you — a hosted instance, a database per tenant; you connect over the normal Postgres wire protocol with any driver, and the patched engine and sidecars are our problem, not yours. Self-hosting is the same ingredients 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 capture sidecar and the orchestrator next to it.

7 · Trade-offs and overhead

A few things worth being clear about, including where the overhead actually landed. The capture of writes is nearly free and off the commit path; the cost is in observe mode, which records read-dependencies. Here's where that cost shows up, stock Postgres against eterDB on the same transaction:

Near-stock is a compatibility claim, not a speed one. This is stock Postgres with a small, upstream-tracked patch set — every driver, ORM, and extension still works, and the patch passes the full regression and isolation suites on both 16.14 (isolation 117/117, regress 220/220) and 18.4, the two version patches differing only in context lines. What it costs is measured, not hand-waved (single host, -O2, median of three runs, PG18.4):

The split is the point. Write capture rides logical decoding off the commit path, so it never touches transaction latency. Observe writes stay flat because the commit-time harvest was rewritten to walk only the transaction's own predicate locks, not the whole cluster's. Observe reads are the honest weak spot: recording a read means taking an SSI predicate lock, and that lock manager contends under high read concurrency — the same pressure that makes native SERIALIZABLE expensive under load. Lowering that acquisition cost is the active work; the guarantee throughout is zero serialization aborts.

That read curve is surprising enough that it's worth saying how we know it's real and not a laptop artifact. The measurement holds one warm cluster and flips observe mode on and off between tightly interleaved runs (so any drift cancels), then repeats each level with the order reversed — running observe first — and gets the same number. During an observe-on read run, Postgres' own pg_locks shows the predicate locks being held; with observe off, none. The overhead is those predicate locks, and nothing else.

It's still Postgres, extensions included. A 13-extension matrix — pgvector among them, built from source — loads, smoke-tests, and passes its own pg_regress suite on the patched build, and works alongside capture: a read served through a GIN, GiST, or HNSW index is recorded as a dependency, and a clean write through any access method undoes correctly. Two rough edges to name: pgcrypto needs an OpenSSL-enabled build, and hstore round-trips through the sidecar history path but not the older in-database trigger path (where it becomes a jsonb shape the compensation code can't reverse — the sidecar is the shipping path).