Historical Database

Historical Database Data Types and Storage Modes¶

Historical tables support the same column types as realtime tables, and each column additionally carries a storage mode that determines how its values are encoded on disk.

Overview¶

A realtime table column has a type. A historical table column has a type and a storage mode. The type is what the column holds; the storage mode is how many bytes each value costs and what the encoding can represent.

The mode is optional in CREATE TABLE. Omitted, the column takes the widest mode for its type, which always works and is rarely the cheapest. The optimize pass can narrow it later, and the modes below are also what optimize converts between.

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

Here tradeDate is the partition column, sym and qty declare a mode, and note takes the default for STRING.

Supported types¶


`BYTE`	`SHORT`	`INTEGER`	`LONG`
`FLOAT`	`DOUBLE`	`CHAR`	`BOOLEAN`
`DATE`	`UTC`	`UTCN`	`UUID`
`COMPLEX`	`STRING`	`BINARY`	`BIGINTEGER`
`BIGDECIMAL`

ENUM and NONE are rejected.

Warning

The arbitrary-precision types are BIGINTEGER and BIGDECIMAL, spelled in full. BIGINT and BIGDEC are not valid and fail with Invalid type: BIGINT.

Storage modes¶

Mode	Applies to	Cost and constraint
`FLAT`	fixed-width types	Default for fixed-width types. Fixed bytes per row, and allows null
`FLAT_NONULL`	fixed-width types	One byte per row cheaper than `FLAT`. Cannot store null
`FLAT_NOMIN`	fixed-width types	One byte per row cheaper than `FLAT`. Cannot store the type's minimum value, which is what marks null
`BITMAP1`	`STRING`	Stores each distinct value once. Up to 255 distinct values per partition
`BITMAP2`	`STRING`	Stores each distinct value once. Up to 65,535 distinct values per partition
`VARSIZE3`	variable-length types	Up to 8 MB of payload per partition
`VARSIZE4`	variable-length types	Up to 2 GB of payload per partition
`VARSIZE5`	variable-length types	Default for `STRING` and `BINARY`. Above 2 GB
`PARTITION`	the partition column	No per-row storage at all. Set by the `PARTITION` keyword, not written as a mode

The variable-length modes apply to STRING, BINARY, BIGINTEGER, and BIGDECIMAL.

Defaults¶

A column with no declared mode takes:

PARTITION for the partition column
VARSIZE5 for STRING and BINARY
FLAT for every fixed-width type

VARSIZE5 and FLAT are the widest options for their types. They are chosen because they always work, not because they are efficient.

`FLAT_NOMIN` and the type minimum¶

FLAT_NOMIN saves its byte by using the type's minimum value as the null marker. A column in this mode cannot store that value as data: Long.MIN_VALUE in a LONG column, or negative infinity in a DOUBLE.

This matters because optimize converts FLAT columns into FLAT_NOMIN automatically when it finds no occurrence of the minimum. A column that has never yet held the minimum value can be narrowed into a mode that cannot hold it.

What optimize converts¶

The optimize pass narrows modes where the data allows:

From	To	When
`FLAT`	`FLAT_NONULL`	The column holds no null
`FLAT`	`FLAT_NOMIN`	The column holds no occurrence of the type minimum
`BITMAP2`	`BITMAP1`	Fewer than 128 distinct values
`VARSIZE5`	`VARSIZE4` or `VARSIZE3`	The data fits a smaller offset width
`VARSIZE3`, `VARSIZE4`, `VARSIZE5`	`BITMAP1` or `BITMAP2`	A `STRING` column whose distinct values are fewer than a tenth of its rows

Conversion happens per partition, not per table, so two partitions of one column can sit in different modes.

Warning

None of these conversions happens under the default settings. Optimize only re-encodes when MinOptimizePct is set explicitly on the table. See Optimization.

Choosing a mode¶

The declared mode is a starting point, and a wrong choice is not permanent: a column widens automatically if data arrives that does not fit.

A STRING column with few repeated values, such as a symbol or a venue, suits BITMAP1 or BITMAP2. Count the distinct values, not the rows.
A STRING column of mostly unique values, such as an execution ID, suits a VARSIZE mode. Choose the smallest width the values fit.
A numeric column that is never null saves a byte per row with FLAT_NONULL.
Declaring nothing is a valid choice. It costs disk, and optimize can narrow it later.

Diagram pending

A diagram belongs here showing one column stored under FLAT, BITMAP, and VARSIZE, with the per-row cost of each. An existing classic image covers part of this and may be reusable.