Time travel and branches: SELECT ... AS OF, CREATE DATABASE ... BRANCH OF

Read a table as it was at an earlier time or commit, undo a bad change with one query, and make a full writable copy of a database in a moment, without copying its files.

• Category: Databases & folders
• Keywords: AS OF, AS OF TIMESTAMP, AS OF LSN, AS OF SYSTEM TIME, FOR SYSTEM_TIME AS OF, time travel, flashback, read the past, table as it was, undo, undo a bad import, point in time, BRANCH OF, branch, clone, copy database, zero-copy, test copy, hard link, keep_versions, KDB_KEEP_VERSIONS, no longer kept
• See also: Databases and the data folder, Change stream and live subscriptions: changes('t'), GET /api/changes, GET /api/subscribe, Backup, check, repair and restore, kdb serve, The trash - UNDROP TABLE

KDB never changes a data file once it is written. Each checkpoint writes the changed files anew and keeps the older versions for a while. With those and the log, KDB can show a table as it was, and make a new database from another one without copying its files.

SELECT ... AS OF

Syntax:

SELECT ... FROM <table> AS OF TIMESTAMP <time> [[AS] alias] ...
SELECT ... FROM <table> AS OF LSN <commit lsn> [[AS] alias] ...
SELECT ... FROM <table> AS OF SYSTEM TIME <time>        -- CockroachDB's spelling
SELECT ... FROM <table> FOR SYSTEM_TIME AS OF <time>    -- SQL:2011's spelling

• AS OF TIMESTAMP <time>: the table with every change committed at or before that time (to the millisecond). The time is any expression: '2026-09-25 14:30', now() - INTERVAL '10 minutes'. A time without a zone is read like any other time of the session.
• AS OF LSN <n>: the table with every transaction committed at or before log position n. changes('t') shows the commit LSN of each change (change stream).
• Each table in a query has its own AS OF. The others are read as they are now, so a query can compare then and now:

-- Which prices did the 14:30 import change?
SELECT now.sym, before.price AS was, now.price AS is_now
FROM prices now
JOIN prices AS OF TIMESTAMP '2026-09-25 14:29' before ON before.sym = now.sym
WHERE before.price <> now.price;

Undo a bad change with one statement:

INSERT OR REPLACE INTO prices SELECT * FROM prices AS OF TIMESTAMP '2026-09-25 14:29';
CREATE TABLE prices_before AS SELECT * FROM prices AS OF LSN 1200345;

Good to know:

How far back

A read AS OF needs, for each file of the table, a version older than the point, and the log after it. Both are kept only for a while:

So a table that changes often can be read back over its last few checkpoints, a table that rarely changes much further. A point that can no longer be rebuilt is refused, never answered with rows that were not there together. The error says why and the oldest point that still works:

the data as of LSN 4 is no longer kept: an older version of its files was removed (keep_versions) ...
The oldest point it can be read at is LSN 14.

These also end a table's history, because their rows are in no file any more:

• a partition that became empty (all its rows deleted), DROP PARTITION, a TTL drop
• TRUNCATE, and PURGE VERSIONS
• a database last written by a KDB from before AS OF, or restored from a backup: its history starts at its next open for writing

CREATE DATABASE ... BRANCH OF

Syntax:

CREATE DATABASE [IF NOT EXISTS] <new> BRANCH OF <database>
CREATE DATABASE <new> BRANCH OF <database> AS OF TIMESTAMP <time>
CREATE DATABASE <new> BRANCH OF <database> AS OF LSN <commit lsn>

A branch is a full, writable database with every table, view, routine, sequence and row of <database>, as it is now or as it was at the point. Try a repair, a migration or a new release on real data, compare, then drop the branch (or swap it in: POST /api/databases/swap).

CREATE DATABASE market_test BRANCH OF market;
-- database 'market_test' created as a branch of 'market' at LSN 1204551 in 120 ms (401 data files shared, 0 copied)
USE market_test;
UPDATE ochl_day SET close = close * 1.1 WHERE symbol = 'ACME';   -- market is not touched
DROP DATABASE market_test;