Skip to content

Database overview

This page explains what a KDB-X database is, the objects it can hold, the forms a persisted table can take, and where to read about each in detail.

A KDB-X database is a directory of serialized KDB-X objects. There is no server to install and no separate storage layer: q writes objects to files in its own native format, and loading the directory makes those objects variables in the q session, queryable exactly like values built in memory.

If you come from PostgreSQL, MySQL, or SQL Server...

In these systems, you connect to a server that owns the data and answers on its behalf. In KDB-X, "database" names the data on disk — the directory this page describes — and not a running service. The process that loads it and answers queries is an ordinary q process, conventionally called an HDB when the data it serves is historical: see the HDB process. Any number of them can load the same database at once, and none of them owns it.

The distinction is not unique to KDB-X. Oracle draws the same line between a database (the files) and an instance (the processes serving them). PostgreSQL keeps three separate words: a data directory on disk, a server process, and a database as a logical container within it. Where a sentence here could be read either way, this documentation says database directory or database root for the files, and q process or HDB for the process.

A database is more than tables

Tables are the usual reason to build a database, and the bulk of this section is about them. But any q object can be persisted, and anything in the database root becomes a variable when the database is loaded — vectors, dictionaries, nested lists, keyed tables, and even functions.

This matters in practice, because reference data is often more natural as a dictionary than as a table.

To see it, generate a sample database with the Datagen module:

q)([getInMemoryTables; buildPersistedDB]): use `kx.datagen.capmkts
q)buildPersistedDB["/tmp/testdb"; ([tbls: `trade`quote`daily; start: 2026.04.10; end: 2026.04.11])]

Alongside the partitioned trade and quote tables it writes a flat table master, the sym list, and an exnames dictionary mapping each exchange code to its full name. Loading the database brings them all into the root namespace together:

q)\l /tmp/testdb
q)system"v"
`daily`date`exnames`master`quote`sym`trade

exnames is an ordinary dictionary, not a table:

q)type exnames
99h
q)3#exnames
A| "NYSE American"
B| "NASDAQ OMX BX"
C| "NYSE National"

Because it is an ordinary q value, it composes straight into a query, labelling the ex column of the partitioned trade table:

q)select trades: count i, size: sum size by exch: exnames ex from trade where ex in `N`Q`Z
exch                     | trades size
-------------------------| ------------
"Cboe BZX Exchange"      | 94     5222
"NASDAQ Stock Exchange"  | 224    12313
"New York Stock Exchange"| 77     4414

The data is randomly generated, so your own figures will differ.

Why a dictionary rather than a table?

Had exnames been persisted as a two-column table instead, the lookup could not sit in the by clause. The query would need a join against it, and the grouping would move to the joined column — more to write, more to read, and a join that every query wanting exchange names has to repeat and keep correct as the schema changes.

q treats dictionaries and functions alike, since both are just mappings, so anything you can apply you can apply inside a query. There are further tricks in the same vein. A step dictionary — an ordinary dictionary with the sorted attribute applied — returns the value of the nearest preceding key when a key is absent:

q)buckets: `s#(0D00:00; 0D09:30; 0D12:00; 0D16:00)!`closed`morning`afternoon`after
q)buckets 0D10:15:00.000
`morning

Being a mapping, it drops into a query exactly as exnames did, bucketing trades by trading session — and it does so with no as-of join, which is what matching a "nearest preceding bound" would otherwise require:

q)select trades: count i, size: sum size by bucket: buckets time from trade
bucket   | trades size
---------| ------------
afternoon| 1454   78188
morning  | 1088   60518

Keeping reference data in the database rather than in a script means every process that loads the database sees the same values, versioned with the data they describe.

Non-table objects in the root cost resident memory

A splayed or partitioned table in the root costs nothing at start-up, because its columns are memory-mapped and read only when a query touches them. A dictionary, a serialized table, or a symbol vector cannot be mapped: it is deserialized onto the heap every time the database is loaded. Keep such objects small, and see objects in the database root for what is mapped and what is not.

Forms of a persisted table

Smaller tables can be held in memory, but a table must be persisted once it is large enough, or once it needs to outlive the process. How to serialize it depends on its size and how it is queried.

Serialization Representation Recommended use case
flat table single binary file small table, and most queries use most columns
splayed table directory of column files up to 100 million rows
partitioned table table partitioned by e.g. date, with a splayed table for each partition more than 100 million records; or growing steadily
segmented database partitioned tables distributed across disks tables larger than disks; or you need to parallelize access

Flat table

q will serialize and file any object as a single binary file, the simplest way to persist a table.

A database with tables trades and quotes:

db/
├── quotes
└── trades

We can also export the table in other formats (for example, .csv, .xls) if needed with save.

Splayed table

A table is splayed by storing each of its columns as a single file. The table is stored as a directory.

db/
├── quotes/
|   ├── time
|   ├── sym
|   └── price
└── trades/
|   ├── time
|   ├── sym
|   ├── price
|   └── vol
|   └── sym

With a splayed table, each column file is only read into memory when a query requires it.

Consider splaying a table if most queries on a reasonably sized table do not need all the columns.

Partitioned table

The records of a partitioned table are distributed across multiple partition directories within its root directory. The table is partitioned by the values of a single column. Each partition contains records that have the same value in the partitioning column. With time series data, this is most commonly a date or time.

db/
├── 2020.10.03/
│   ├── quotes/
│   │   ├── price
│   │   ├── sym
│   │   └── time
│   └── trades/
│       ├── price
│       ├── sym
│       ├── time
│       └── vol
├── 2020.10.05/
│   ├── quotes/
│   │   ├── price
│   │   ├── sym
│   │   └── time
│   └── trades/
│       ├── price
│       ├── sym
│       ├── time
│       └── vol
└── sym

The partition directory is named for its partition value and contains a splayed table with just the records that have that value.

Consider partitioning a table if any of the following conditions apply:

  • It grows over time.
  • It contains more than 100 million records.
  • It includes columns that exceed the maximum object size allowed in memory.

Segmented database

The root directory of a segmented database contains only two files:

  • par.txt: a text file listing the paths to the segments
  • the sym file for enumerated symbol columns

Segments are stored outside the root, usually on various volumes. Each segment contains a partitioned table.

DISK 0             DISK 1                     DISK 2
db/                db/                       db/
├── par.txt        ├── 2020.10.03/           ├── 2020.10.04/
└── sym            │   ├── quotes/           │   ├── quotes/
                   │   │   ├── .d            │   │   ├── .d
                   │   │   ├── price         │   │   ├── price
                   │   │   ├── sym           │   │   ├── sym
                   │   │   └── time          │   │   └── time
                   │   └── trades/           │   └── trades/
                   │       ├── .d            │       ├── .d
                   │       ├── price         │       ├── price
                   │       ├── sym           │       ├── sym
                   │       ├── time          │       ├── time
                   │       └── vol           │       └── vol
                   ├── 2020.10.05/           ├── 2020.10.06/
                   │   ├── quotes/           │   ├── quotes/
                   ..                        ..

Consider segmenting a table across multiple storage devices if any of the following conditions apply:

  • The table exceeds the capacity of a single storage device.
  • You need to parallelize access to the table.
  • You want to partition the table by a non-integer datatype, such as a symbol.

Dividing a table across storage devices allows you to:

  • Store very large tables that exceed single-device capacity.
  • Parallelize queries across partitions.
  • Isolate updates to specific partitions, avoiding rewrites of the entire table.

A database mixes all of these

A KDB-X database typically contains many tables of different types. Flat, splayed and partitioned tables can reside nicely next to each other. Flat and splayed tables are in the root directory together with other KDB-X objects (like lists and dictionaries).

The sample database generated above is exactly such a mix. Review its file structure:

/tmp/testdb
├── 2026.04.10
│   ├── quote
│   │   ├── .d
│   │   ├── asize
│   │   ├── ask
│   │   ├── bid
│   │   ├── bsize
│   │   ├── ex
│   │   ├── mode
│   │   ├── sym
│   │   └── time
│   └── trade
│       ├── .d
│       ├── cond
│       ├── ex
│       ├── price
│       ├── size
│       ├── stop
│       ├── sym
│       └── time
├── daily
│   ├── .d
│   ├── close
│   ├── date
│   ├── high
│   ├── low
│   ├── open
│   ├── price
│   ├── size
│   └── sym
├── exnames
├── master
└── sym

Where:

  • exnames stores a dictionary
  • master is a flat table
  • sym is the symbol list
  • daily is a splayed table
  • trade and quote are partitioned tables

Loading the database (for example, with \l) makes every one of these a variable in the root namespace. They do not all cost the same, though: daily, trade, and quote are memory-mapped and cost nothing at load time, while exnames, master, and sym are read onto the heap. See objects in the database root for the full breakdown.

The sym file

Splaying a table requires its symbol columns to be enumerated: each distinct symbol is stored once in a sym file at the database root, and the column holds indexes into it. Because symbols repeat heavily in a historical database, this saves both space and comparison time.

.Q.en does the enumeration, and dsave and .Q.dpft call it for you. See manage sym files for how to populate, verify, migrate, and compact one.

Next steps