Time travel & restore

Goal: recover from a bad write by rolling the table back to a known-good version. Understand how vacuum interacts with restore so you don’t lock yourself out of recovery.

Why this matters: every table keeps a versioned commit history (the on-disk format is Delta Lake), so every write is reversible — until vacuum reclaims old versions past retention. Knowing that relationship is what turns a panic into a one-line fix.

Shape:

   write → write → write    (versions advance monotonically)
       │       │       │
       ▼       ▼       ▼
   ┌──────────────────────┐
   │  version history     │
   └──────────────────────┘
              │
              ├─► RESTORE … TO VERSION AS OF K
              │     → commits a NEW version N+1 whose file set = state at K
              │     → every write after K is discarded, committed or pending
              │
              └─► VacuumTable (files older than retention, floor 168h)
                    → permanently removes files for old versions
                    → restoring past them then fails loudly

The surface

RESTORE is SQL, not an RPC and not a REST route. It works on every wire that carries SQL — the Postgres door, dodil data sql, the tables HTTP door.

RESTORE [TABLE] <table> TO VERSION   AS OF <n>
RESTORE [TABLE] <table> TO TIMESTAMP AS OF '<rfc3339-with-offset>'

There is no history listing. No History RPC, no SHOW HISTORY, no /history route. You find a restore target from what you already know: the version a DDL statement returned, TableStats.delta_version from GetDatabaseStats, your own audit trail, or TIMESTAMP AS OF with a wall-clock instant from your logs. Write down the version before a risky migration — that is the whole recovery plan.

Read-side time travel is likewise narrow: only the GetRow RPC takes an as_of_version, and only for a primary-key point lookup. There is no SELECT … VERSION AS OF clause.

Prerequisites

• A bucket + table with at least a few writes — we’ll reuse the events table from the Manual Table recipe.
• A connection on any SQL-carrying wire. See Connect & wire adapters.

Setting the scene — what we’ll roll back

Say the last known-good state of events was version 5. Now someone runs a destructive UPDATE with a too-broad predicate:

-- OOPS — meant `id = 99`, but the predicate matched everything
UPDATE events SET event_type = 'archived' WHERE id > 0;

Every row now says event_type = 'archived'. We want the previous state back.

Note the WHERE is not optional here — UPDATE and DELETE are refused without one. That guard stops the worst mistakes, but it can’t stop a bad predicate, which is what RESTORE is for.

1. Restore by version

RESTORE TABLE events TO VERSION AS OF 5;

conn.execute("RESTORE TABLE events TO VERSION AS OF 5")

dodil data sql -b kb-prod "RESTORE TABLE events TO VERSION AS OF 5"

The response is a DDL summary — the table name and the new version the restore committed:

{
  "statement_kind": "restore",
  "table_name": "events",
  "version": 8
}

Three things are true about that, and all three matter:

1. A restore is a forward commit. The target version’s state is republished as a new version rather than rewinding history. Version numbers keep climbing, and a restore is itself restorable — you can undo an undo.
2. It is destructive to the tail, deliberately. Every write made after the target version is gone — committed and pending alike — so nothing can resurface later.
3. Secondary indexes are not rolled back. An index has its own history with no counterpart to the base’s restored version. The statement returns a warning saying so; read-time revalidation and backfill reconcile them.

If the statement returns a warning that the pending-write cleanup did not complete, re-run the same RESTORE.

RESTORE is refused up front if the table doesn’t exist, and refused if the target version is beyond the current head — you can only restore to a version that has already committed.

Restore by timestamp

If you don’t have a clean version number but know the wall-clock time:

RESTORE TABLE events TO TIMESTAMP AS OF '2026-05-27T08:00:00Z';

This resolves to the last version committed at or before that instant. The timestamp must carry an offset (…Z or …+02:00) — a naive local timestamp has no defined meaning across a multi-region fleet, and guessing one is how you restore to the wrong hour. VERSION AS OF and TIMESTAMP AS OF are mutually exclusive; pass exactly one.

2. Verify the restore

SELECT event_type, COUNT(*) AS n
FROM events
GROUP BY event_type
ORDER BY n DESC;

Expect the original distribution (click, purchase, signup) — not a table full of archived. There is nothing to wait for: the restored state is visible to the next read.

3. The retention window that bounds recovery

VacuumTable permanently deletes storage files that are no longer referenced by the current version and are older than the retention window. Once a version’s underlying files are gone you can no longer restore to it — the restore fails loudly rather than committing a table with dangling paths.

There is no retention-check bypass. VacuumRequest has exactly four fields — db_id, table, dry_run, retention_hours — and a shorter window is clamped up to 168 hours on every door and in the CLI.

The practical consequence: your restore window is your vacuum retention, and the background maintenance loop is already vacuuming whether or not you ever run it by hand. A bad write you discover on day 9 cannot be restored past a 7-day window. Recovery beyond that needs a separate story — a materialized snapshot, a mirrored table, or an audit log.

4. Vacuum safely — dry run first

Always dry-run before vacuuming a table you might need to restore:

dodil data table vacuum events --bucket kb-prod --dry-run -o json

{
  "table_name": "events",
  "version": 8,
  "dry_run": true,
  "files_deleted": 42
}

files_deleted under dry_run is the count that would be removed. The response carries the count only — the plane does not return the file paths.

Then the real run, optionally with a longer window:

# Default (168h) retention
dodil data table vacuum events --bucket kb-prod
 
# 30-day retention — a month-long restore window
dodil data table vacuum events --bucket kb-prod --retention-hours 720

--retention-hours below 168 is clamped to 168; there is no way to go under it.

5. Pinning a restore point

Since retention is the only thing standing between you and an unrecoverable table, pin the snapshots you actually care about as independent tables before a risky change:

CREATE TABLE events_pre_migration AS SELECT * FROM events;

The copy is an ordinary table with its own history and its own retention clock — vacuum on events cannot touch it. Restoring from it is a normal INSERT … SELECT or CREATE OR REPLACE TABLE, not a RESTORE. See CTAS & Materialize.

6. Per-scenario restore patterns

Common gotchas

Next: the RPC shapes are on Tables & maintenance; the DDL contract on Schema changes.