Skip to content

How to design data for KDB-X performance

This page explains how to lay out data so that a KDB-X query reads and processes as little as possible. It covers types, columnar tables, partitioning, sort order, attributes, segments, tiers, and compression.

Data layout is often the highest-leverage performance decision. Code cannot recover the time spent opening partitions that the query did not need, scanning an unindexed column, reading unused columns, or transferring a high-cardinality text field stored in an inefficient form.

Start from the important queries, not from the source message. Record:

  • the columns used as constraints and how selective they are;
  • the usual time range and other partition constraints;
  • the columns returned, grouped, sorted, or joined;
  • whether the data is appended, corrected, or immutable; and
  • the warm and cold retention periods.

Then design the schema and physical layout to minimize the bytes and files those queries touch.

Choose types deliberately

A compact simple vector uses less storage, page cache, memory bandwidth, and CPU cache than a wider or nested representation. Use the narrowest datatype that preserves the required range and precision:

  • short, int, and long use 2, 4, and 8 bytes per item in a simple vector. Arithmetic on short is promoted to int, but int and long arithmetic can still overflow without an error. Validate ranges before narrowing a column.
  • real uses 4 bytes and float uses 8. Use real only when about seven significant decimal digits are enough.
  • Use temporal types for dates and times, rather than text, so comparisons, arithmetic, sorting, and partition constraints operate on fixed-width values.
  • Use booleans, bytes, or chars for genuinely small domains when their meaning remains clear.

Keep columns simple where possible. A simple list holds one fixed type and is the natural input to q's vector primitives. A general or nested list adds indirection, usually consumes more memory, and limits which tables can be mapped or splayed. If a nested field is rarely queried, consider keeping it outside the hot table and joining or retrieving it only when needed.

Choose symbols by cardinality

Symbols work well for repeated categorical values such as tickers, venues, sides, and status codes. q interns each distinct symbol once and compares references, and an on-disk database can enumerate symbol columns against a shared sym file.

Do not use symbols for values that are mostly unique or grow without a bound, such as order IDs, request IDs, free text, or generated names. Every distinct symbol remains in the process's symbol table for its lifetime and cannot be reclaimed by garbage collection. Use strings or GUIDs for high-cardinality identifiers, and monitor syms and symw in .Q.w.

Design for on-disk queries

KDB-X stores a splayed table as one file per column. A query maps only the columns that it refers to, so selecting two columns can avoid reading the rest of a wide table. Keep frequently filtered and returned data in simple columns, and select only the columns required.

For a data set too large for one splayed table, use a partitioned database. Each partition contains a splayed table, and the partition value is reconstructed from the directory name rather than stored in every row.

Choose the partition column and interval

Partition on a column that nearly every large query constrains. For time-series data this is usually a date, month, or year. Put the unmodified partition column first in the where phrase so q can eliminate directories before it maps any table columns:

select sym, price from trade where date within 2026.04.01 2026.04.10, sym=`AAPL

Choose the interval from the access pattern:

  • Partitions that are too coarse make a short-range query open and scan more data than it needs.
  • Partitions that are too fine create many directories and files, increase mapping and metadata work, and leave too little work in each partition for efficient I/O and parallelism.

Daily partitions are common for market data, but they are not a universal default. Benchmark representative ranges and include retention, end-of-day work, backup, and recovery in the choice.

Do not write the virtual partition column into the splayed table. See partitioned tables for the supported partition types and write workflow.

Sort within each partition

Sort rows so that values commonly queried together are contiguous. A typical market-data partition is sorted by sym,time: all rows for one symbol occupy one range, and time remains ordered inside that range. A selective symbol query can then read a narrow range from every requested column instead of scattered pages.

.Q.dpft sorts on its field argument, applies the parted attribute, enumerates symbols, and writes the table into a partition. dsave also enumerates symbols and applies the parted attribute to the first column, but it does not sort the rows; sort the table before calling it.

The best sort key is workload-specific. Sorting by a field improves queries that constrain that field but can make a different access path less contiguous. Keep a second table or a precomputed aggregate when two critical workloads require incompatible layouts and the measured gain justifies the extra storage and ingestion work.

Apply attributes where they are used

An attribute records a property of a list so that q can avoid a full scan:

Attribute Property and typical use Cost or restriction
Sorted (s) Values are ascending; equality and range searches can use a binary search. Common on time in memory. The order must remain ascending.
Unique (u) Every value is distinct; useful for keys and lookups. q must maintain the property and its lookup structure.
Parted (p) Equal values are contiguous; preferred for a repeated field such as sym in a sorted on-disk partition. Any operation on the list removes the attribute; the data must remain parted.
Grouped (g) An index records the positions of every value; useful for repeated values in memory when rows are not contiguous. Consumes memory, costs time to build and maintain, and can produce scattered reads on disk.

For an in-memory real-time table, a common layout is grouped sym and sorted time. For a historical partition sorted by sym,time, sym commonly has the parted attribute. Prefer p over g when the data can be made contiguous: it generally uses less memory and performs better on disk and in memory.

A select can use the attribute of the first applicable constraint. On a partitioned table, constrain the partition column first, then put the unmodified attributed column on the left of =, in, or within:

select from trade where date=2026.04.10, sym in `AAPL`MSFT

Measure before and after applying an attribute. Building and preserving it has a cost, and small lists often do not benefit. Also remember:

  • setting or removing most attributes copies the list;
  • updates can remove an attribute whose property no longer holds;
  • a grouped attribute can retain index keys as values change; and
  • applying an attribute directly to compressed data on disk decompresses it.

Inspect meta after ingestion and after schema maintenance. An attribute intended by the design but missing from one partition can make query latency vary by date. See database maintenance for evolving columns and attributes safely.

Control the number and placement of files

Every partition-column pair is a file. The total matters because queries map and unmap files, .Q.MAP can hold file descriptors open, backups walk the tree, and the operating system caches file metadata. Avoid needless columns and overly fine partitions, and set the file and map limits from the measured database shape.

When one device cannot provide enough throughput, a segmented database places whole partitions on several locations. Queries spanning those partitions can read the devices in parallel. Striping with LVM or RAID spreads blocks from every file across devices instead; compare the tradeoffs in combine disks.

Use storage tiers when recent data and old data have different latency, capacity, or cost requirements. Keep the queried working set on the faster tier, and include migration and cache behavior in the benchmark.

Compress for the measured access pattern

File compression trades CPU time for fewer storage bytes. It can improve query time when storage or the network is the bottleneck, and slow a CPU-bound or page-cache-resident query. The result depends on the column values, algorithm, level, logical block size, storage, and query selectivity.

Benchmark representative columns and queries with several settings. Measure:

  • compression and write time during ingestion or end-of-day work;
  • cold and warm query latency;
  • CPU utilization and peak memory;
  • the resulting file size; and
  • selective queries, because reading one item decompresses at least one logical block.

Do not assume the highest compression ratio is best. Avoid applying native KDB-X compression and file-system compression to the same data without measuring the double work. If several threads process compressed data, give each thread a different file; do not read or write the same compressed file concurrently from multiple threads.

Validate the whole layout

Before adopting a layout, test it with the important workload and verify:

  • the partition constraint prunes the expected directories;
  • only required columns are mapped;
  • sort order and attributes exist in every partition;
  • high-cardinality text does not grow the symbol table;
  • cold and warm latency both meet their targets;
  • ingestion, end-of-day, backup, and recovery still fit their windows; and
  • the layout scales when the data and number of concurrent queries grow.

Next steps