Skip to content

CONCEPT Cited by 2 sources

Point-in-Time Recovery (PITR)

Definition

Point-in-Time Recovery (PITR) is the database capability of producing a fresh, queryable copy of the database at a chosen past timestamp, typically for:

  • Incident undo — "we dropped the wrong table 4 minutes ago — give us a version from 5 minutes ago."
  • Accidental-delete recovery — restore specific rows wiped by a bad query, pipeline bug, or user error.
  • Forensic / audit — inspect historical state without running a transaction-replay.
  • Dev / QA / staging — fork a past production state as a sandbox without the replay cost of log-shipped standby.

The classical implementation — periodic full snapshots + WAL / binlog replay between the snapshot and the target time — is operationally expensive: restore a 2 TB snapshot to a new EBS volume (minutes to hours), then replay WAL forward to the target (more minutes). For a live incident this makes PITR the option of last resort rather than the first thing tried.

PITR on compute-storage-separated substrates

On a compute-storage- separated substrate where the storage layer already keeps historical page versions + the WAL as durable shared state (for example Pageserver + Safekeeper on Neon / Lakebase), PITR collapses into a copy-on-write fork targeted at a past timestamp. No snapshot restore, no physical copy, no minutes-long wait. The operation becomes:

  1. Ask the storage layer to expose a logical view of the pages as they were at time t.
  2. Point a new compute instance at that logical view.
  3. Connect and query.

Each of these is sub-second. PITR's wall-clock cost becomes dominated by the control-plane round-trip + compute boot, not by the data volume.

Canonical disclosure (Lakebase, 2026-04-30)

"Branching and Point-in-Time Recovery (PITR) are essentially the same primitive: branching is just PITR with source_branch_time = now." — Thoughtworks Backstage POC.

Measured: 3.78 seconds end-to-end from a wipe of the final_entities table (32 rows → 0) to a recovery branch with all 32 entities restored, while production itself was still at zero (branches are fully isolated). See sources/2026-04-30-databricks-backstage-with-lakebase.

This is an order of magnitude faster than the traditional snapshot-restore-plus-WAL-replay PITR shape, and reframes PITR from "last-resort disaster-recovery operation" to "routine undo-button you hit when a command goes wrong."

Target-time granularity (WAL-bounded)

PITR's target-time is not caller-specified precision — it is bounded by the WAL-record cadence of the underlying store. Ask for 22:56:02Z; get 22:55:50Z (12 seconds earlier) if the nearest durable WAL record is at 22:55:50Z. This is the concepts/wal-write-ahead-logging property — PITR always snaps backward to the nearest known durable state.

For time-sensitive workflows (e.g. "recover to just before the bad commit at T") this means the caller must request T − ε for sufficient margin, rather than T exactly. The Lakebase POC disclosed a 12-second snap-back as representative; different workload intensities + WAL-write cadences will produce different granularities.

Contrast: branching vs PITR

On a copy-on-write-capable substrate, the two are the same operation with a different source_branch_time:

Branch PITR
Source time now past timestamp
Data content current production state state at past time
Mechanism COW-fork at current head COW-fork at historical head
Use case pre-deploy test, dev sandbox, policy testing, agent operation accidental-delete recovery, incident undo
Latency 1.09 s (63 MB Backstage catalog) 3.78 s (recovery + verify)

See branching-is-pitr-with-time-now for the architectural unification.

Prior art on classical substrates

  • AWS RDS / Aurora — PITR supported via automated backups + transaction log retention. Recovery is restore-to-new- instance (fresh RDS instance provisioned, historical state materialised, minutes to hours depending on data volume). See systems/amazon-aurora.
  • MySQL / Postgres DIY — pg_basebackup + WAL archive; operator runs pg_createsubscriber or pg_wal_replay on a fresh cluster.
  • Traditional snapshot systems — storage-tier snapshot (EBS / ZFS / LVM) plus log-replay on top.

All of these are available; none of them is fast enough to be the first thing you try during a live incident. The compute-storage-separated substrate's advantage isn't that PITR becomes possible — it becomes cheap enough to be routine.

Seen in

  • sources/2026-10-01-databricks-lakebase-postgres-branch-based-restores-for-fast-recovery-at-scale — The restore-time-is-independent-of-data-size canonicalization. Databricks 2026-10-01 Tier-3 post dedicated entirely to PITR mechanics. Sharpens the traditional-PITR critique into three stages that each scale with data volume: (1) provision a new coupled compute+storage instance (large EBS volumes are slow to come up before the restore even starts); (2) hydrate the snapshot from S3 onto the Postgres disk (RDS marks the instance available while most pages are still in S3 and fetches missing blocks on-demand — acceptable latency for an internal check, not for production); (3) replay WAL from snapshot time to T. "Unless the database is small, PITR is almost always a multi-hour operation." Also canonicalises the HA-replica-is-not-a-substitute point: failover saves you when a machine dies, but "does not help when the bad write is already on the standby" — a dropped table has already replicated, so a restore is still required. The Lakebase reframing: because compute and storage are decoupled and history is an immutable addressable timeline, a restore "is a branch at a point in history" — the control plane maps the chosen timestamp to an LSN, creates a branch, and attaches compute; "a restore is metadata: a pointer to a point in history" with no copy and no replay. The load-bearing new claim: restore time is decoupled from data size — "restoring 100 TB is as fast as restoring 10 GB" — removing the perverse coupling where the largest, most-critical databases waited longest and improving RTO at scale. The only variable cost becomes validation (how much restored state you choose to query). Cited pain survey (50 devs, 1 TB+ prod Postgres, neon.com/restores-survey): 30% down 3+ hours, only 21% recovered in <60 min. Also frames fast branch-at-timestamp restore as an agent/product primitive (Replit, v0 undo/versioning: checkpoint = snapshot of main + stored checkpoint ID next to the code version; undo = restore the checkpoint onto the live branch).

  • sources/2026-04-30-databricks-backstage-with-lakebase — canonical first wiki instance of PITR at Lakebase / Neon altitude. Thoughtworks POC demonstrates 3.78-second end-to-end recovery from a 32-row-deletion incident, with 12-second WAL-snap-back granularity disclosed. Canonicalises the branching ≡ PITR-with-time-now architectural unification + the "every incident gets an undo" framing that follows from PITR at sub-10-second latencies.

  • sources/2026-09-30-databricks-a-practical-guide-to-cost-optimization-with-lakebase-postgre — PITR window + snapshot schedule as a storage-cost tuning decision. Lakebase's cost guide reframes PITR from pure recovery capability to a recovery-vs-cost tradeoff: PITR storage grows with write activity × window length, so a long window on a write-heavy app is expensive. The cost-conscious shape: choose the shortest PITR window that meets incident-recovery needs (configurable 2–30 days) and supplement with scheduled snapshots for longer-term recovery points. Both are cheaper than regular branch storage — snapshot $0.090/GB-mo (~74% cheaper), PITR $0.200/GB-mo (~42% cheaper) — and scheduled snapshots are incremental after the first full one. Division of labor: PITR for unexpected incidents (accidental deletes, bad writes at any moment in the window); snapshots for planned recovery points (before a risky migration/bulk update).

Merged aliases

  • log-recovery-time
  • point-in-time-reconstruction
  • recovery-point-objective
  • recovery-time-objective- pragma-based-pitr- partial-restoration-pattern
Last updated · 766 distilled / 2,225 read