Skip to content
Historical Database

Historical Database Use Cases

Worked scenarios showing where a historical table is the right store, across capital markets, operations, and general time-series workloads.

Overview

A historical table suits data that accumulates, is keyed by time, and is read back later, and that you would otherwise push into a separate database beside the Center. The scenarios below share one shape: an append-heavy dataset, partitioned by a time column, loaded incrementally, optimized on a schedule, and queried by that time column.

Fits a historical table Belongs in a realtime table
Tick and trade history Live order book and current positions
Order and execution records State updated row by row as it changes
End-of-day snapshots and archives Working sets with frequent sparse DELETE
Any time-keyed series read back later Constantly-mutated live state

Each scenario below uses only statements that run as written against a Center.

Tick and trade history

A tick or trade store accumulates continuously and is almost always queried by date and symbol. Partition by the date so a single-day query reads one partition, and give the symbol a bitmap mode because it repeats heavily:

1
2
3
4
5
6
CREATE PUBLIC TABLE Trades (
    tradeDate LONG PARTITION,
    sym       STRING BITMAP2,
    qty       LONG,
    px        DOUBLE
) USE PersistEngine="HISTORICAL" MinOptimizePct="0";

MinOptimizePct="0" enables storage-mode re-encoding at optimize, so the symbol column narrows to fit its actual cardinality. See Optimization.

Load it incrementally, then optimize on a schedule to merge the day's partitions:

1
2
INSERT INTO Trades VALUES (20260101,'IBM',100,183.4),(20260101,'AAPL',250,201.1);
CALL __OPTIMIZE_HISTORICAL_TABLE("Trades");

A single-day query reads only that day:

1
SELECT sym, qty, px FROM Trades WHERE tradeDate==20260101;

Day-level summaries — traded quantity and average price per symbol — come from a GROUP BY:

1
SELECT sym, SUM(qty), AVG(px) FROM Trades WHERE tradeDate==20260101 GROUP BY sym;

See Querying for the aggregate rules.

Trades and their fills can be joined directly, without staging either side into a temporary table:

1
2
SELECT Trades.qty, Fills.qty
FROM Trades JOIN Fills ON Trades.qty==Fills.qty;

See Querying for the join rules.

Order and execution audit

An audit trail of orders and executions is append-only and is read back by date range for reconciliation and reporting. The design is the same: partition by date, and index a column that reconciliation filters on within a day, such as an account, as long as it never holds null:

1
2
3
4
5
6
7
8
CREATE PUBLIC TABLE OrderAudit (
    eventDate LONG PARTITION,
    account   STRING BITMAP2,
    orderId   STRING,
    status    STRING
) USE PersistEngine="HISTORICAL" MinOptimizePct="0";

CREATE INDEX idx_account ON OrderAudit (account);

Query a day's activity for one account:

1
SELECT orderId, status FROM OrderAudit WHERE eventDate==20260101;

The realtime side of the trail is trimmed by archiving completed days into this table on a schedule. See Archiving Realtime Data.

End-of-day snapshots

A daily snapshot of a metric, such as end-of-day positions or a computed risk figure, is a general time series: one set of rows per day, read back to compare across days. One partition per day fits it exactly:

1
2
3
4
5
CREATE PUBLIC TABLE EodPositions (
    asOfDate LONG PARTITION,
    sym      STRING BITMAP2,
    position LONG
) USE PersistEngine="HISTORICAL" MinOptimizePct="0";

Read one symbol across a range of dates to see its trend:

1
SELECT asOfDate, position FROM EodPositions WHERE sym=='IBM';

Because the query does not filter on the partition column, it reads every partition. That is the expected cost of a cross-date trend query, and it is why the partition column is the date rather than the symbol: the common single-day queries stay cheap, and the occasional trend query pays for what it scans.

When a historical table is the wrong choice

A historical table is built for accumulation, not for churn. Data that is updated in place, deleted sparsely, or read and rewritten constantly belongs in a realtime table, which holds it in memory for exactly that access pattern. Reach for a historical table when the data is written once, keyed by time, and read back later.

Diagram pending

A diagram belongs here showing a realtime table feeding a historical table on a daily archive schedule, with queries reading history by date.