Historical Database

Historical Database¶

The 3forge Historical Database is the Center's on-disk, column-oriented store for data that accumulates over time, queried with the same AMI SQL used against realtime tables.

Overview¶

The Center holds realtime tables in memory, which suits live state that is read and mutated constantly. Data that only accumulates, such as tick history, order and execution records, or end-of-day snapshots, eventually outgrows that model. The Historical Database is the alternative: a table declared with PersistEngine="HISTORICAL" is stored as a tree of files on disk rather than in the heap, and is queried through the same SQL interface, in the same Center, alongside live data.

A table stored this way is called a historical table. Creating one differs from creating a realtime table by a single clause:

CREATE PUBLIC TABLE Trades (
    tradeDate LONG PARTITION,
    sym       STRING,
    qty       LONG
) USE PersistEngine="HISTORICAL";

Three mechanisms do the work. Partitioning splits a table into independent directories keyed by a column, so a query filtered on that column reads only the partitions it needs. Storage modes encode each column according to the shape of its data, so a repetitive string column costs far less on disk than a unique one. The optimize pass compacts partitions after they are written, merging those that share a key and tightening column encodings.

Note

Historical tables do not support triggers. A trigger on a realtime table can reference one, which is the usual way to drive an archive.

Capabilities¶

Capability	Enabled by	Covered in
Store data on disk rather than in memory	`PersistEngine="HISTORICAL"`	Creating tables
Keep queries reading only relevant data as volume grows	`PARTITION` columns	Partitioning
Reduce disk cost per column by data shape	`FLAT`, `BITMAP`, and `VARSIZE` storage modes	Data types
Compact and merge partitions after write	`__OPTIMIZE_HISTORICAL_TABLE`	Optimization
Query history beside live data	AMI SQL, including two-table joins	Querying
Evolve a schema after data exists	`ALTER TABLE ... ADD`	Creating tables
Speed up lookups on a column	`CREATE INDEX`	Querying
Roll realtime data into history on a schedule	procedures and timers	Archiving

Relationship to the Center¶

A historical table is a Center table. It is created with the same CREATE PUBLIC TABLE statement, holds the same column types, and is queried with the same AMI SQL. The Center documentation is the reference for all of that, and this section does not repeat it.

What changes when a table is stored on disk is the following, and only the following.

What historical tables add¶

Capability	Covered in
Splitting a table into partitions on disk	Partitioning
Per-column storage modes and their compaction	Data Types and Storage Modes
The optimize pass	Optimization
The `ami.hdb.*` properties and the storage root	Configuration

What historical tables do not support¶

Everything else in AMI SQL behaves as it does against a realtime table, with these exceptions:

Not supported	Detail
Triggers	A trigger on a realtime table may reference a historical table, which is the usual way an archive is driven. See Triggers.
Joins beyond two historical tables	A third fails outright. See Querying
Nested `IN (SELECT ...)`	Fails rather than returning a result. See Querying
Table aliases	Rejected in any statement against a historical table
Qualified column names in a join `WHERE`	The `SELECT` list requires them; the `WHERE` clause rejects them
Index types other than sorted	One index per column, and never on a partition column. See Indexes.
Altering a partition column while rows exist	The table must be truncated first. See Creating a Historical Table

Procedures and timers work normally, and are how a scheduled archive is built. See Procedures and Timers.

Where the general reference lives¶

For	See
`CREATE PUBLIC TABLE` syntax and `USE` options	CREATE PUBLIC TABLE
Persist engines, of which `HISTORICAL` is one	Persist Engine
Column types	Supported Data Types
`ALTER PUBLIC TABLE` syntax	ALTER PUBLIC TABLE
`DESCRIBE`, `SHOW TABLES`, and other shell commands	Tools
Running a statement over HTTP	REST API

Limits¶

Every figure here is a compiled constant, not a measurement.

Limit	Value
Rows per partition	2,147,483,647, after which a new partition starts
Rows above which partitions stop being merged	10,000,000
`VARSIZE3` payload, per column per partition	8 MB
`VARSIZE4` payload, per column per partition	2 GB
`BITMAP1` distinct values, per column per partition	255
`BITMAP2` distinct values, per column per partition	65,535
Columns per index	1
Indexes per column	1
Historical tables per join	2
Non-partition columns required	at least 1
Default wait for a table lock	20 seconds

Two figures deliberately absent. VARSIZE5 has no stated ceiling: it is defined only as holding more than 2 GB, and no confirmed upper bound exists. Total table size is unbounded by any constant, and no measured figure is published, so size against the per-partition limits above rather than a whole-table number.

The row limit that matters in practice is 10,000,000, not 2,147,483,647. Past that a partition stops being merged, so it is the practical ceiling on partition size. See Partitioning.

Where to start¶

New to the feature, start with the Quickstart, which creates a historical table, loads it, optimizes it, and queries it back.

Evaluating whether the Historical Database fits a problem, see Use cases for worked scenarios.

Deploying it, see Configuration for the storage root and the schema persistence requirement, both of which need attention before the first table is created.

Diagram pending

A diagram belongs here showing a realtime table and a historical table in one Center, with the archive path between them.