Partitioning Historical Tables¶
Partitioning splits a historical table into independent directories keyed by one or more columns, so a query filtered on a partition column reads only the partitions that can match.
Overview¶
A historical table is not one file. It is a directory holding one subdirectory per partition, and each partition holds its own copy of every column.
That layout is what keeps a query fast as the table grows. A query filtered on the partition column can rule out whole partitions without opening them, so the cost of the query tracks the data it actually needs rather than the size of the table. A query with no such filter reads everything.
A column becomes the partition key with the PARTITION keyword in place of a storage mode:
Every distinct tradeDate now gets its own directory, and SELECT ... WHERE tradeDate==20260101 opens one of them.
The partition column costs no storage¶
A partition column is not written per row. Its value is the identity of the directory, recorded once in that partition's KEY.3fhkey file rather than once per row.
A table partitioned by date therefore stores the date exactly once per day of data, no matter how many rows that day holds. This is the reason to partition on a column you would otherwise be storing millions of times over.
Choosing the partition column¶
This is the one decision that is expensive to change. Once a table holds rows, adding, modifying, or dropping a partition column fails:
The layout on disk follows the partition key, so changing it would mean rewriting every partition. Choose it before loading data.
Three properties make a good partition column:
It appears in the WHERE clause of most queries. Partitioning only pays off when queries filter on it. A tick store queried by date should partition by date; partitioning it by venue instead leaves every date query reading the whole table.
Its cardinality is moderate. Each distinct value is a directory holding a file per column. A date column over two years is roughly 500 directories, which is fine. A timestamp column to the millisecond would be millions, each holding a handful of rows, and the file count becomes the bottleneck.
It is coarser than the queries that filter it. Partitioning by date and querying a single date is ideal. Partitioning by date and querying a month reads 30 partitions, which is still far better than reading everything.
A useful check: if the column has more distinct values than the table has rows per distinct value, the partitioning is too fine.
For a rough target, aim for partitions between 100,000 and 10,000,000 rows. The upper bound is the point at which partitions stop being merged, covered in Size limits below. The lower bound is softer, and reflects that very small partitions multiply file and directory counts without buying much elimination.
Tip
Date as a LONG in YYYYMMDD form is the common choice for market and trading data. It sorts correctly, it is cheap to compare, and it matches how those queries are usually written.
Cardinality is not fixed at one¶
Zero, one, or several partition columns are all legal. The only requirement is that at least one column is not a partition column:
With several partition columns, a partition is identified by the combination of their values, and the directory count multiplies. Two columns of 500 and 20 distinct values give up to 10,000 partitions. Multiply before committing.
With none, the table still works and still writes partitions, but nothing can be ruled out at query time.
How partitions get created¶
A partition is created when data arrives with a partition key that has no partition currently accepting writes. Partitions are numbered from 0001 and increment.
Whether a second insert for an existing key reuses its partition depends on the state that partition is in:
- Still accepting writes. The rows are appended to the existing partition. Its row count grows and no new directory appears.
- Already optimized. A second partition is created for the same key, holding only the new rows, and it starts out accepting writes.
So a key can end up spread across several partitions, but only across an optimize boundary. Loading a day's data in ten inserts produces one partition; loading it, optimizing, then loading more produces two.
This is deliberate. Appending to a fresh partition is fast, and reconciling it with what is already committed on disk is deferred rather than paid on every write.
Optimization is what reconciles them. It merges partitions sharing a key back into one:
| Step | Partitions |
|---|---|
Insert 2 rows for 20260101 |
PID=1, 2 rows, accepting writes |
| Optimize | PID=1, 2 rows, committed |
Insert 1 more row for 20260101 |
PID=1 with 2 rows, and PID=2 with 1 row |
| Optimize | PID=1, 3 rows |
Between those two optimize calls, a query filtered on that date reads both partitions. The result is correct either way; it is just doing more work than it needs to. Tables written incrementally benefit from optimizing on a schedule for this reason alone.
Inspecting partitions¶
DIAGNOSE TABLE reports one PID row per partition, with its numeric id, its key values, and its on-disk path:
The PKEY column carries the partition key, COUNT its row count, and COMMENTS shows IN_APPEND_MODE while the partition is still accepting writes. See Optimization for the full lifecycle.
Indexes and partition columns¶
A partition column cannot be indexed:
This is not a limitation to work around. Partition elimination already does the job an index would do on that column, and does it without reading any file at all. Index the columns you filter on within a partition, such as a symbol or an account, and let partitioning handle the rest.
See Querying for how indexes behave.
Diagram pending
A diagram belongs here showing one table split into three partition directories by date, with a date-filtered query touching only one. Three existing classic images cover roughly this and should be checked for reuse before new ones are commissioned.
Size limits¶
| Limit | Value |
|---|---|
| Rows per partition | 2,147,483,647, after which the engine starts a new one |
| Rows above which partitions stop being merged | 10,000,000 |
The second figure matters far more than the first. A partition can hold two billion rows, but once the largest partition for a key passes ten million, optimize stops consolidating that key and every later partition for it stays separate.
Treat 10 million rows per partition as the working ceiling, not two billion.