Point-in-Time Recovery (PITR)

HeliosDB-Lite supports point-in-time recovery using its MVCC versioning system. Every row modification is timestamped, so the database can reconstruct the state of any table at any past moment. PITR always writes recovered data to a new branch to preserve the current state.

Prerequisites

HeliosDB-Lite v3.5+ with time-travel enabled (on by default)
Understanding of HeliosDB branching (branches are isolated copies of data)

How It Works

HeliosDB stores versioned snapshots of every row (the v: key prefix).
RECOVER DATABASE TO TIMESTAMP reads every user table at the requested snapshot and writes the result into a new branch.
The main branch is never modified — PITR is a non-destructive operation.

Step 1: Create Test Data and Note the Time

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    name TEXT,
    balance DECIMAL
);

INSERT INTO accounts VALUES (1, 'Alice', 10000.00);
INSERT INTO accounts VALUES (2, 'Bob',   5000.00);
INSERT INTO accounts VALUES (3, 'Carol', 7500.00);

Note the timestamp right after the initial insert (your application would record this, or you can query SELECT now()):

-- Suppose the current time is 2026-03-26 10:00:00

Step 2: Make Changes Over Time

-- 10:15 - Transfer funds
UPDATE accounts SET balance = 8000.00 WHERE id = 1;
UPDATE accounts SET balance = 7000.00 WHERE id = 2;

-- 10:30 - Accidental delete
DELETE FROM accounts WHERE id = 3;

-- 10:45 - More changes
INSERT INTO accounts VALUES (4, 'Dave', 3000.00);
UPDATE accounts SET balance = 6000.00 WHERE id = 1;

Current state:

id	name	balance
1	Alice	6000.00
2	Bob	7000.00
4	Dave	3000.00

Carol’s row is gone and Alice’s balance has changed twice.

Step 3: Recover to a Specific Timestamp

Recover to 10:00 to get the original state before any changes:

RECOVER DATABASE TO TIMESTAMP '2026-03-26 10:00:00';

Expected output:

col0	col1	col2	col3	col4	col5
RECOVER	pitr_1711443600	1711443600	1	3	8

Columns: operation, branch name (auto-generated), target timestamp, tables recovered, rows recovered, duration in milliseconds.

The auto-generated branch name follows the pattern pitr_{timestamp}.

Step 4: Recover to a Named Branch

You can specify a meaningful branch name with AS BRANCH:

RECOVER DATABASE TO TIMESTAMP '2026-03-26 10:15:00' AS BRANCH 'before_delete';

This creates branch before_delete containing the state at 10:15, which includes the fund transfer but not the accidental delete of Carol.

Step 5: Query the Recovered Branch

Use time-travel queries to inspect data on the recovered branch, or switch to the branch:

-- Using AS OF on the main branch to see historical data
SELECT * FROM accounts AS OF TIMESTAMP '2026-03-26 10:00:00';

id	name	balance
1	Alice	10000.00
2	Bob	5000.00
3	Carol	7500.00

Step 6: Recovery Workflow

A typical disaster recovery workflow:

-- 1. Identify when the problem occurred
SELECT * FROM accounts AS OF TIMESTAMP '2026-03-26 10:25:00';
-- Carol still exists at 10:25 -- problem is the DELETE at 10:30

-- 2. Recover to a point just before the incident
RECOVER DATABASE TO TIMESTAMP '2026-03-26 10:29:00' AS BRANCH 'pre_incident';

-- 3. Verify the recovered data on the branch
-- (switch to branch or query the branch)

-- 4. If satisfied, copy the needed rows back to main
-- e.g., re-insert Carol's row from the recovered state
INSERT INTO accounts VALUES (3, 'Carol', 7500.00);

Step 7: View Historical Versions

HeliosDB also supports querying version ranges:

-- See all versions of a table between two timestamps
SELECT * FROM accounts VERSIONS BETWEEN
    TIMESTAMP '2026-03-26 10:00:00' AND
    TIMESTAMP '2026-03-26 10:45:00';

This returns every version of every row that existed in the time range, useful for auditing exactly what changed and when.

Interaction with Backups

PITR and backups serve complementary purposes:

Feature	Backup/Restore	PITR
Granularity	Backup-point only	Any timestamp
Speed	Fast (binary copy)	Depends on table sizes
External files	Yes (.hldb files)	No (uses internal MVCC data)
Retention	As long as files exist	Limited by version GC
Non-destructive	Optional (TO BRANCH)	Always (creates a branch)

For best protection, combine both: use backups for disaster recovery and PITR for fine-grained “oops” recovery within the version retention window.

Common Pitfalls

Version garbage collection — VACUUM removes old versions older than the cutoff (default: 1 hour). If you run VACUUM aggressively, PITR to timestamps older than the cutoff will recover incomplete data. Adjust the retention window if you need longer PITR coverage.
Branch names must be unique — The auto-generated name pitr_{ts} can collide if you recover to the same timestamp twice. Use AS BRANCH with a unique name for production workflows.
System tables are skipped — PITR recovers user tables only. Tables prefixed with __mv_ or helios_ are excluded because they are internally managed.
Timestamp format — Use ISO 8601 format: 'YYYY-MM-DD HH:MM:SS'. The timestamp is parsed with second-level precision.
Recovery creates data, not schema — PITR writes row data to the new branch using the current schema. If you have altered the table schema since the target timestamp, the recovered data uses the original serialized format.