Skip to content
Historical Database

Archiving Realtime Data

Moving data from a realtime table into a historical table on a schedule is the most common way historical tables are populated, using a procedure driven by a timer.

Overview

A realtime table holds live state in memory, which suits data that is read and mutated constantly but grows without bound if nothing removes it. A historical table holds accumulated data on disk. Archiving is the bridge between them: it copies rows that have aged out of the live working set into a historical table, then clears them from the realtime table so its memory footprint stays bounded.

An archive cycle is three steps: copy the aged rows into history, clear them from the realtime table, and optimize the historical table. Each step is an ordinary statement; a timer runs them together on a schedule.

Copying rows into history

The copy is a single INSERT ... SELECT from the realtime table into the historical table:

1
INSERT INTO Trades SELECT * FROM LiveTrades;

The two tables must be column-compatible. In practice the copy is filtered to the rows being archived, usually a completed day:

1
INSERT INTO Trades SELECT * FROM LiveTrades WHERE tradeDate==20260101;

Note

Driven over REST, the endpoint caps a result set at 10,000 rows and that cap propagates into INSERT ... SELECT. Archive in batches under that size, or run the archive server-side from a procedure where the REST cap does not apply.

Clearing archived rows

Once the rows are in the historical table, remove them from the realtime table so the copy is not repeated on the next cycle. Delete the same rows that were copied, or, for a full end-of-day roll, TRUNCATE the realtime table:

1
DELETE FROM LiveTrades WHERE tradeDate==20260101;

The delete is against the realtime table, not the historical one. Historical tables are not suited to sparse DELETE; the realtime table is where the working set is trimmed.

Optimizing after the copy

Rows appended to a historical table land in a partition that is still accepting writes, and inserting into a key whose partition has already been optimized creates a second partition for that key. Optimize merges them back and settles the new data:

1
CALL __OPTIMIZE_HISTORICAL_TABLE("Trades");

See Optimization for what the pass does and Partitioning for why an incrementally loaded table fragments without it.

Scheduling the cycle

The three statements above are the body of the archive. In a Center they run together on a schedule: a stored procedure holds the copy, the clear, and the optimize, and a timer invokes the procedure at the archive interval, such as once at end of day.

Note

Historical tables do not support triggers, so the archive cannot be hung off a trigger on the historical table. It is driven the other way around: a timer fires a procedure on a schedule. A trigger on the realtime table can still react to live writes for other purposes.