Skip to main content

Table snapshots

A rows table can be snapshotted — a copy-free, point-in-time capture of its data and its schema — and restored later to exactly that state. It is the safe way to do a full-table refresh: snapshot, TRUNCATE, reload — and if the reload goes wrong, restore (a snapshot's hard links survive the truncate).

Snapshots are for rows tables only. Timeseries tables are append-only and do not snapshot.

SNAPSHOT devices AS baseline NOTE 'before nightly load';
SHOW SNAPSHOTS FROM devices;   -- name, note, created_at, created_by
RESTORE devices TO baseline;   -- data and schema, exactly as they were
DROP SNAPSHOT baseline FROM devices;

SNAPSHOT without AS <name> mints one (x_snap_<timestamp> — engine-generated names carry the x_ prefix); NOTE is optional. A snapshot captures the table's schema too, so a restore after an ALTER TABLE brings the columns back as well — the table returns to how it was, not just its rows. The sync sequence travels with the data: after a restore the engine reseeds x_rev from what is now stored, and replicas whose cursor is ahead of it should resync in full (their cursor points at history that no longer exists).

How it stays cheap​

Columnar segments are immutable once written, so a snapshot is a set of hard links to them — zero data copy, and reference-counted by the filesystem itself. A later merge that rewrites or deletes a live segment leaves the linked inode alive for as long as any snapshot points at it; dropping the last snapshot that references a segment frees it. No manifest, no garbage collector — the kernel already runs one. You can keep many snapshots of a large table for almost nothing.

Restore is itself undoable​

Before a RESTORE replaces the current state, the engine snapshots that state automatically as x_pre_restore_<timestamp>. If the restore was the wrong call, you can restore back to it. Every SNAPSHOT, RESTORE and DROP SNAPSHOT lands in the event ring as an audit line stamped with the acting user.