pg-timemachine

git bisect for your data. Find the minute a value in Postgres became wrong.

Written up in full, with the measurements and the two ways a bisect lies: Git Bisect for Your Data

You know the number is wrong today. You do not know when it started, which deploy to blame, or how many rows are affected. The usual answer is to guess from a created_at column, if the table has one and if the bug touched it.

This does the search instead. It creates a branch of your database as it was at a moment in time, runs your query against it, and narrows down until it has the minute the answer changed.

pg-timemachine bisect \
  --project my-project-id \
  --branch  br-my-main-branch \
  --query   "select count(*) from orders where total_cents < 0" \
  --expect-good 0 \
  --since -6h

Built on Neon's branch-at-a-timestamp, which is the part that makes this practical: a branch costs no copy of the data and takes under a second.

What it costs

Measured against a real Neon project, Postgres 18, in eu-central-1, from a client roughly 45 ms of round trip away:

create a branch at a past timestamp    843 ms
connect and run the first query        837 ms
                                       --------
one probe                             ~1.7 s

A bisect is logarithmic, so a 24 hour window at one-minute precision is about 13 probes, roughly 20 seconds. A scan of the same window would be 1,440.

Each probe is bounded: 20 seconds to connect, 30 to run the query, enforced both client-side and as a statement_timeout. A hung probe would otherwise stall the whole search and leave a compute running.

The one limit that matters

You cannot look further back than your history retention window. On Neon that is history_retention_seconds, 24 hours by default on most plans. If your data broke last week and you keep a day of history, this tool cannot help you, and it will say so rather than returning a wrong answer:

This project keeps 24h of history, so the earliest moment it can reconstruct is
2026-09-16T10:12:00Z. You asked for 2026-09-10T00:00:00Z.

Raise retention before you need it, not after.

Install

git clone https://github.com/The-DevOps-Daily/pg-timemachine
cd pg-timemachine
npm install
export NEON_API_KEY=...        # Neon console, Account settings, API keys

Commands

bisect — when did this change?

pg-timemachine bisect \
  --project <project-id> \
  --branch  <parent-branch-id> \
  --query   "select count(*) from orders where total_cents < 0" \
  --expect-good 0 \
  --since -6h --until now \
  --precision 1m

The query must return exactly one row with one column. That restriction is deliberate: a bisect on an ambiguous verdict returns a confident wrong answer. Wrap anything more complicated: select count(*) from (your query) q.

Use --expect-good <value> when you know what right looks like, or --expect-bad <value> when you only know what wrong looks like.

at — what did this look like then?

pg-timemachine at --project <id> --branch <id> \
  --query "select count(*), sum(total_cents) from orders" \
  --at -3h

check — did it only break once?

A bisect assumes the answer changes exactly once across the range, exactly as git bisect assumes. Data does not always oblige. This samples the range and tells you whether the assumption holds before you trust a result.

pg-timemachine check --project <id> --branch <id> \
  --query "select count(*) from orders where total_cents < 0" \
  --expect-good 0 --since -6h --samples 8

sweep — clean up after a crash

Every branch this tool creates is named tm-<timestamp> and deleted when the probe finishes, including when the query throws. If a run is killed partway, sweep removes what it left:

pg-timemachine sweep --project <id>          # list them
pg-timemachine sweep --project <id> --yes    # delete them

It will only ever touch branches whose name starts with tm-.

Times

--since, --until and --at accept an ISO 8601 instant, now, or an offset like -30s, -90m, -6h, -2d.

Try it on a seeded database

demo/seed.mjs writes orders steadily and, partway through, starts applying a discount without a floor so some orders land with a negative total. It prints the exact moment the first bad row appeared, so you can check the bisect against the truth rather than taking its word.

DATABASE_URL=<branch connection string> node demo/seed.mjs --minutes 14 --break-after 8

See demo/RUN.md for a recorded run.

How it works

Nothing clever. For each probe:

1. POST /branches with parent_timestamp set to the moment being tested. Neon reconstructs the data as it was then, from the WAL it already keeps.
2. Connect to the new branch and run the query.
3. Compare the single returned value to what you said good looks like.
4. Delete the branch.

The search itself is an ordinary binary search over time, in src/bisect.mjs, with no Postgres in it at all. It takes a probe callback, which is why the test suite can cover every edge case without creating a single branch.

What it does not do

• It cannot see past your retention window. Covered above.
• It assumes one transition. Use check when that matters.
• It does not tell you what changed, only when. That is usually enough: the minute points at a deploy, a migration or a cron run, and from there you know what to read.
• It is not free. Each probe starts a compute. On a paid plan that is compute time; the branches themselves are removed as it goes.
• One project at a time. Every branch it makes is named tm-<timestamp>, and sweep deletes anything with that prefix. Two bisects running against the same project at once will work, but a sweep from one can remove the other's in-flight branch. Finish one before sweeping.

Tests

npm test

37 tests, none of which touch the network.

Interrupting a bisect with Ctrl-C deletes the branch that was in flight before exiting. If that cleanup cannot finish it prints the branch name so sweep can remove it.

The write-up

Git Bisect for Your Data covers why the retention window is the whole ballgame, the two cases where a bisect returns a confident wrong answer, and what the search costs against a real database.

Contributing

The search in src/bisect.mjs has no I/O in it, so anything about the algorithm can be tested without a Neon account:

npm test

The parts that do talk to Neon are in src/neon.mjs and src/probe.mjs, both small. If you want to make this work against another Postgres that can reconstruct a past state, those two files are the ones to replace.

Issues and pull requests welcome.

Licence

MIT