Skip to content

Storage tiering

This page explains how to spread one database across storage of different speed and cost, at partition, table and column level, and what to weigh when moving data between tiers.

Historical data is rarely queried evenly. Yesterday is read constantly, last quarter occasionally, and five years ago almost never — yet all of it usually sits on the same storage, sized and priced for the queries that touch the newest partitions. Tiering puts each part of the database on storage that matches how often it is actually read: recent data on NVMe, older data on spinning disks, archival data on NFS or object storage.

Because a KDB-X database is a tree of directories, this needs no special feature. A segmented database already spans several locations, and a symlink can redirect anything smaller. q resolves both while scanning, so a tiered database is queried exactly like a single-disk one.

Spreading partitions across tiers with par.txt

par.txt lists the directories the database is assembled from, one per line. Point each line at a different tier:

/nvme/db
/hdd/db
/nfs/db

Each of those directories is partitioned in the usual way, and which partitions live where is entirely up to you:

/nvme/db/            (last 3 months)
├── 2026.07.01/
├── 2026.07.02/
└── …
/hdd/db/             (3 months – 3 years)
├── 2024.02.11/
└── …
/nfs/db/             (older than 3 years)
├── 2019.05.06/
└── …

There is no rule q imposes about the distribution. It scans the segments at load time, builds its internal map of partition to location, and presents one table. Nothing requires the split to be by date, contiguous, or balanced — the partition values simply have to be unique across segments, and the data for a date has to be in the directory named for that date.

Loading such a database gives one table spanning every tier, with .Q.PV in date order regardless of which segment holds each partition:

q)\l /db
q).Q.PV
2026.01.01 2026.01.02 2026.03.01 2026.03.02
q)select count i by date from trade
date      | x
----------| -
2026.01.01| 2
2026.01.02| 2
2026.03.01| 2
2026.03.02| 2

.Q.par reports where a given partition actually resolves to, which is the quickest way to confirm a partition is on the tier you intended:

q).Q.par[`:/db; 2026.01.01; `trade]
`:/nfs/db/2026.01.01/trade

Move a partition by moving it and leaving a symlink

A partition does not have to be moved between segments to change tier. Leaving a symlink in its original segment works just as well, and avoids rewriting par.txt:

mv /nvme/db/2024.02.11 /hdd/db/
ln -s /hdd/db/2024.02.11 /nvme/db/2024.02.11

q follows the link while scanning, so the partition is queried from the slower tier without the database layout changing.

Tiering by table

Access frequency often differs between tables in the same partition as much as between dates. A quote table is typically an order of magnitude larger than trade and queried far less, which makes it a candidate for slower storage even for recent dates.

Since a table is just a directory inside the partition, a symlink moves one table without touching the rest:

mv /nvme/db/2026.07.01/quote /hdd/db/2026.07.01/quote
ln -s /hdd/db/2026.07.01/quote /nvme/db/2026.07.01/quote

The partition keeps trade on the fast tier and reads quote from the slow one, and a query joining them works unchanged.

Tiering by column

The same trick goes one level deeper. A splayed table stores each column as its own file, so a wide table with a few rarely-queried columns — a comment field, a raw payload, an audit column kept for compliance — can leave those columns on cheap storage while the columns queries actually filter and aggregate on stay fast:

mv /nvme/db/2026.07.01/trade/extra /hdd/cold/extra.2026.07.01
ln -s /hdd/cold/extra.2026.07.01 /nvme/db/2026.07.01/trade/extra

The column reads through the link like any other:

q)select cnt:count i, s:sum extra by date from trade
date      | cnt    s
----------| ---------------
2026.01.01| 100000 49876.88
2026.03.01| 100000 49995.1

A query that does not name the column never touches the slow tier at all, which is the whole point: q only reads the column files a query mentions.

Move a nested column with its companion files

A nested column is stored as several files — col, col#, and col## when it contains symbols (see mapped lists). They belong together: moving only the file named after the column leaves the payload behind and shifts nothing meaningful. The same applies to the .d file, which must stay with the table directory.

Compression per tier

Compression and tiering solve the same problem from different directions — one buys space with CPU, the other with latency — and they combine well, because the right trade-off differs by tier.

  • Fast tier: usually leave it uncompressed, or use a cheap algorithm at a low level. Data here is queried constantly and the storage is fast enough that decompression, not I/O, becomes the limit.
  • Slow tier: compress harder. Where reads are rare, spending CPU to cut the volume is worth it, and on a high-latency mount transferring fewer bytes can make reads faster rather than slower.

Compression is a per-file property, so a single database can hold both. Nothing needs to be declared centrally — q reads the parameters from each file:

q)-21! `:/nfs/db/2026.01.01/trade/px      / cold tier
compressedLength  | 578718
uncompressedLength| 800016
algorithm         | 2i
logicalBlockSize  | 17i
zipLevel          | 9i
q)-21! `:/nvme/db/2026.03.01/trade/px     / fast tier

An uncompressed file returns an empty dictionary, so 0=count -21!file is a convenient test for whether a file has been compressed yet.

Which algorithm and level suit which tier is a measurement question, not a rule — see choosing an algorithm and measure on your own hardware.

Moving data between tiers

A tiering policy is implemented by a job that runs periodically and relocates data that has aged past a threshold. Two things deserve care.

Atomicity. Moving a partition or a table is not a single operation, and a reader that loads the database midway through sees whatever state the filesystem is in. A missing table directory is particularly unkind: q derives the database's table list from the most recent partition, so a table absent from it disappears from the database entirely. Stage the copy under a name the loader skips, then publish it with one symlink operation — see atomicity and integrity for the failure modes and the pattern.

Cost of re-encoding. Moving bytes is cheap; changing how they are encoded is not. If the job also compresses, or recompresses at a different level, then for every file it must decompress, recompress and rewrite:

  • CPU — compression at a high level is expensive, and a bulk migration of years of data is a large amount of it. This competes with the queries the database is still serving, so it is worth restricting the job to off-peak hours, limiting its thread count, or running it on a separate process from the one answering queries.
  • Memory — each file is decompressed into memory before being written back. A migration that walks a table column by column has a footprint set by the largest column it touches, not by the table.
  • Time — the rewrite has to finish inside whatever window the policy allows. Measure a single partition before committing to a schedule for a multi-year archive, and remember that the read side of the move is coming from the slow tier.

Verify before deleting

A migration that copies, then symlinks, then deletes the original is recoverable at every step until the delete. Check the new location reads correctly — and that the symlink resolves from the database root — before removing anything.

Next steps