Historical Database

Optimizing the Historical Database¶

Optimizing a historical table merges partitions that share a key and are small enough to qualify, flushes indexes to disk, and, when the table is configured for it, re-encodes columns into tighter storage modes.

Overview¶

Writes to a historical table are made to be fast, not compact. Rows are appended to a partition that is still accepting them, columns stay in whatever storage mode they were declared with, and indexes are held in memory.

Optimize is the pass that settles all of that. It does three separate jobs:

Job	Runs at default settings
Merge partitions sharing a partition key	Yes, below a size limit
Flush indexes from memory to disk	Yes
Re-encode columns into tighter storage modes	No. Requires `MinOptimizePct`

The third is the one that surprises people, and it is covered in Storage-mode re-encoding below.

Run it on demand:

CALL __OPTIMIZE_HISTORICAL_TABLE("Trades");

The partition lifecycle¶

A partition moves through states, and DIAGNOSE TABLE reports where each one is in the COMMENTS column:

Row type	Before optimize	After optimize
`PID`, the partition	`IN_APPEND_MODE`	(blank)
`PCOLUMN`, a column within it	`NOT_OPTIMIZED`	(blank)
`PINDEX`, an index within it	`IN_MEMORY`	`ON_DISK`

A blank state means settled. IN_APPEND_MODE means the partition is still accepting writes, which is what makes a repeat insert for the same key append rather than create a second partition. See Partitioning.

Merging partitions¶

Inserting into a key whose partition has already been optimized creates a second partition for that key. Optimize merges them back:

INSERT INTO Trades VALUES (20260101,'IBM',100);
CALL __OPTIMIZE_HISTORICAL_TABLE("Trades");   -- one partition, committed
INSERT INTO Trades VALUES (20260101,'AAPL',250);
-- now two partitions for 20260101
CALL __OPTIMIZE_HISTORICAL_TABLE("Trades");   -- merged back into one

Queries return correct results throughout. An unmerged key simply costs more to read, because every partition holding it must be opened.

A table loaded incrementally, such as one fed a batch per day, accumulates this fragmentation. Optimizing on a schedule is the remedy, up to the size limit below.

Partitions above 10 million rows are never merged¶

Merging applies only when both partitions involved are under 10,000,000 rows. Optimize picks the largest partition holding a key and appends the others into it, but only while both it and the partition being appended are below that figure. Anything larger is optimized where it is and left as a separate partition.

The consequence is worth planning around. Once the largest partition for a key reaches 10 million rows, that key stops consolidating permanently, and every partition created for it afterwards remains separate. A table with daily partitions above 10 million rows accumulates fragmentation that optimize will not clear.

Tip

This is the practical ceiling on partition size. A partition column chosen so that partitions stay below 10 million rows keeps the table consolidating indefinitely. See Partitioning.

Storage-mode re-encoding¶

This is the part that does not happen by default.

Optimize can narrow a column's storage mode to fit the data actually in it, replacing VARSIZE5 with a bitmap for a repetitive string, or FLAT with FLAT_NOMIN for a column holding no nulls. See Data Types and Storage Modes for the conversions.

None of it runs unless MinOptimizePct is set on the table.

Warning

MinOptimizePct defaults to a value that disables re-encoding entirely. A table created without it will merge partitions and flush indexes when optimized, and leave every column in the mode it was declared with, however inefficient. Set the option at creation to enable re-encoding.

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

The effect is easy to see. Two tables, identical data, three distinct symbols in a BITMAP2 column, differing only in that option:

`USE` clause	`sym` after optimize
`PersistEngine="HISTORICAL"`	`BITMAP2`, unchanged
`PersistEngine="HISTORICAL" MinOptimizePct="0"`	`BITMAP1`

Note

If a conversion you expected did not happen, check MinOptimizePct before looking at the data. Partition size and row count are not the reason.

What the value means¶

MinOptimizePct takes a value from 0 to 1 inclusive, and gates how much saving a conversion must promise before it is made. 0 permits every conversion the data allows.

It is not a straightforward percentage of bytes saved. Each storage mode is compared against a best-case figure for that mode rather than against its actual current size, so the same value behaves differently across column types. Setting 0 avoids the question entirely and is the right choice unless optimize is taking too long.

Bounding how long it runs¶

Optimize holds a lock on the table while it works. MaxOptimizeSeconds caps how long a single run may hold it:

CREATE PUBLIC TABLE Trades (
    tradeDate LONG PARTITION,
    sym       STRING
) USE PersistEngine="HISTORICAL" MinOptimizePct="0" MaxOptimizeSeconds="60";

Worth setting on a table large enough that a full optimize would block queries for longer than the application tolerates.

Reading DIAGNOSE output¶

DIAGNOSE TABLE is the way to see what optimize did:

DIAGNOSE TABLE Trades;

It returns one row per structural element, identified by the TYPE column:

`TYPE`	What it describes
`TABLE`	The table, and its directory on disk
`COLUMN`	A column's declared type and mode
`INDEX`	An index and the column it covers
`PID`	One partition, with its key and row count
`PCOLUMN`	One column within one partition
`PINDEX`	One index within one partition

The distinction between COLUMN and PCOLUMN is what makes the command useful. COLUMN reports the mode the table declares; PCOLUMN reports the mode that partition's data is actually stored in. Re-encoding happens per partition, so those two can differ, and two partitions of one column can sit in different modes.

STORAGE carries the mode, COMMENTS the lifecycle state, and FILE the absolute path on disk.

Note

DISKSIZE reports 0 for INDEX rows regardless of what the index holds. Use it for columns, not indexes.

When optimize runs on its own¶

Optimize runs at the end of Center startup as well as on demand. For a scheduled optimize driven by a procedure and a timer, see Archiving.

Note

Reaching the cap during partition merging stops the run cleanly and leaves the remaining partitions for the next call. No partition is left half-written.