Skip to content

CONCEPT Cited by 1 source

PRAGMA-based point-in-time recovery

Definition

PRAGMA-based PITR is a point-in-time-recovery surface that exposes the timestamp knob as a SQL-level PRAGMA on the database connection, rather than as a CLI flag on an out-of-band restore operation. The caller sets a target timestamp inside a live session; subsequent reads on that connection resolve against the database state at that timestamp.

Canonical instance: PRAGMA litestream_time in Litestream VFS (shipped 2025-12-11).

Syntax

-- Relative timestamp
PRAGMA litestream_time = '5 minutes ago';
SELECT * FROM sandwich_ratings ORDER BY RANDOM() LIMIT 3;

-- Absolute timestamp
PRAGMA litestream_time = '2000-01-01T00:00:00Z';
SELECT * FROM sandwich_ratings ORDER BY RANDOM() LIMIT 3;

From the shipping post:

"We're now querying that database from a specific point in time in our backups. We can do arbitrary relative timestamps, or absolute ones, like 2000-01-01T00:00:00Z. What we're doing here is instantaneous point-in-time recovery (PITR), expressed simply in SQL and SQLite pragmas." (Source: sources/2025-12-11-flyio-litestream-vfs)

Why it matters vs CLI-style PITR

Traditional Litestream PITR was an out-of-band operation:

$ litestream restore -timestamp 2025-12-11T17:30:00Z /path/to/db
$ sqlite3 /path/to/db "SELECT ..."

Two separate commands, a full-database restore to disk, and a commit to the chosen timestamp for the entire subsequent query session.

PRAGMA-based PITR collapses this to two SQL statements inside a live connection:

  • Zero RTO: no data is moved; setting the PRAGMA adjusts which LTX pages the VFS serves reads from. "Updating your database state to where it was an hour (or day, or week) ago is just a matter of adjusting the LTX indices Litestream manages."
  • Per-session targeting: different connections (or different statements on the same connection, if the implementation allows re-setting the PRAGMA) can read different historical points simultaneously.
  • SQL-composable: the timestamp is a parameter of the query session, not a prerequisite of the toolchain.

Worked disaster-recovery example

From the post — somebody runs a bulk UPDATE in prod without a WHERE clause:

sqlite> UPDATE sandwich_ratings SET stars = 1;
sqlite> SELECT * FROM sandwich_ratings ORDER BY RANDOM() LIMIT 3;
97|French Dip|Los Angeles|1
140|Bánh Mì|San Francisco|1
62|Italian Beef|Chicago|1

On a dev machine, operator sets the PRAGMA and queries the pre-disaster state:

sqlite> PRAGMA litestream_time = '5 minutes ago';
sqlite> SELECT * FROM sandwich_ratings ORDER BY RANDOM() LIMIT 3;
30|Meatball|Los Angeles|5
33|Ham & Swiss|Los Angeles|2
163|Chicken Shawarma Wrap|Detroit|5

No restore, no ticket, no runbook — just two SQL statements.

Precondition: VFS read path + LTX random access

The pragma works because the read path is a VFS-intercepted operation against LTX files in object storage, with random page access via the LTX index trailer. Setting the PRAGMA tells the VFS which subset of LTX files (compacted states + deltas up to the target timestamp) to consult when resolving a page read. The disk-level file the application thinks it's reading is unchanged; the VFS silently redirects.

Design consequence: PITR becomes ad-hoc

Before PRAGMA-based PITR, doing a historical query was a decision the operator had to commit to up front (run a restore, query, throw away the restored database). With PRAGMA-based PITR, historical queries are a zero-cost exploratory surface: developers can casually ask "what did this look like at noon yesterday?" from an already-open session.

The post frames this as the critical ergonomic unlock:

"Ever wanted to do a quick query against a prod dataset, but didn't want to shell into a prod server and fumble with the sqlite3 terminal command like a hacker in an 80s movie? Or needed to do a quick sanity check against yesterday's data, but without doing a full database restore? Litestream VFS makes that easy."

Write semantics with PRAGMA set — undefined in post

The post is silent on what happens if a caller sets litestream_time and then attempts a write. Litestream VFS is read-side-only (writes still flow through the Litestream Unix program on the primary), so writes presumably either (a) get rejected, (b) go to the primary at now, (c) fail the session, or (d) some other behaviour — the shipping post doesn't specify. Recorded as a caveat pending follow-up disclosure.

Seen in

  • sources/2025-12-11-flyio-litestream-vfs — canonical wiki instance. PRAGMA litestream_time shipped as the SQL-level PITR surface in Litestream VFS; accepts relative and absolute timestamps; two-pragma worked disaster-recovery example in the post (the "missing WHERE on UPDATE" scenario resolved via PRAGMA litestream_time = '5 minutes ago').
Last updated · 200 distilled / 1,178 read