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 can 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-file 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"

A dictionary (similarly to a list, a q function, etc) can be used directly inside a query, labeling 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 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 — and flat-file tables — in the root cost resident memory

A splayed or partitioned table in the root costs nothing at startup, because its columns are memory-mapped and read only when a query touches them. A flat-file table is not mapped this way, and neither is a dictionary or a symbol vector: each 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-file 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-file table

q can 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

You 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/
|   ├── .d
|   ├── time
|   ├── sym
|   └── price
└── trades/
    ├── .d
    ├── time
    ├── sym
    ├── price
    └── vol

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, one per distinct value of a partition domain — conventionally a date, though other domains work too. Nothing enforces that a row actually belongs in the partition holding it: which directory a row lands in when the table is written is entirely up to whatever wrote it, not something kdb-x checks.

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. By convention its records share that value, but — as noted above — nothing enforces the relationship; see the partition domain.

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 partitioned tables.

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.

Dividing a table across storage devices allows you to:

  • Store very large tables that exceed single-device capacity.
  • Parallelize I/O across devices — a query against a partitioned table already fans out across partitions regardless of segmenting, but a single device serializes that work, where separate devices don't.
  • 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-file 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 don't 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.

What makes a directory a splayed table is its .d file, listing the columns. A directory without one is still loaded, as a dictionary keyed by the filenames inside it, and a subdirectory becomes a nested dictionary — so a tree of files arrives as a dictionary of dictionaries, indexable at depth. This is a tidy way to group reference data under one name, and worth knowing before leaving anything in a subdirectory of a database that you did not mean to publish, since the whole tree arrives as a variable.

q)refdata          / db/refdata/ holds the file labels and the subdirectory limits/
labels| `low`mid`high
limits| `s#`hard`soft!20 10
q)refdata[`limits;`hard]
20

The partition domain

Dates are the usual way to partition a table, but they are not the only one. q supports four partition domains — date, month, year, and int — and the choice is recorded nowhere in the database: it is implied entirely by the names of the partition directories, which is why each must start with a digit.

q reads the domain off the length of the first partition directory's name:

Domain Example directory Name length Virtual column
date 2026.01.01 10 date, type d
month 2026.01 7 month, type m
year 2026 4 year, type j
int 1, 20261 anything else int, type j

The domain then appears as a virtual column, named after the domain itself and prepended to every partitioned table in the database. It costs no storage — the value comes from the directory name:

q)meta t
c   | t f a
----| -----
date| d
id  | j
px  | f

int is the general case, and the reason for partitioning by something other than time: any bucketing that can be reduced to an integer works, and q treats a name of any other length as one.

Avoid int partition names that are 4, 7, or 10 digits long

The domain is inferred purely from the name length of the first partition, as sorted by name in alphanumeric order, so an int partition name that happens to land on 4, 7, or 10 digits is misread as year, month, or date instead. Partitioning by week number is a common way to trip this — the week number itself is small, but a day count derived from it is not:

q)floor .z.p%7*1D
1394

Pick a name length that cannot collide with the other domains — for example, zero-pad short integers to a length outside 4, 7, and 10. If the first partition (alphanumerically) is known to always have a colliding length, for example a single digit, zero-padding the rest is unnecessary.

Three utilities report what q decided: .Q.pf gives the domain, .Q.PV the partition values found on disk, and .Q.pt the tables that are partitioned.

q).Q.pf
`date
q).Q.PV
2026.01.01 2026.01.02
q).Q.pt
`s#,`t

Every partition must fit the domain inferred from the first

Because the inference looks only at one directory name, a database whose partitions are not all the same shape is read against the wrong domain and fails to load. Here database mixed has one partition named 2026 (4 digits) and another named 2026.01.01 (10 digits):

mixed/
├── 2026/
│   └── t/
└── 2026.01.01/
    └── t/

The first directory is taken as a year, and 2026.01.01 cannot be read as one:

q)\l mixed
'part

A directory whose name does not begin with a digit is not a partition at all. It is loaded as an ordinary root entry, and — if nothing else in the root looks like a partition — the database is not partitioned, leaving .Q.pf and .Q.PV undefined rather than signaling an error.

Staging files and directories are ignored

Everything described so far is loaded. One class of entry is deliberately skipped: a name ending in a dollar sign marks a q staging entry. The new version of something is written alongside the original under a $ name and renamed over it once it is complete, which is how a sym file, for example, can be updated without a reader ever seeing a half-written one.

This applies to files and directories alike, and a $-suffixed directory is skipped whatever it contains — a whole splayed table sitting beside the live one is passed over rather than loaded:

db/
├── trade/          (loaded)
│   ├── .d
│   ├── id
│   └── px
├── sym$            (skipped — a file)
└── trade.v2$/      (skipped — a directory, with its own .d)
    ├── .d
    ├── id
    └── px

q).Q.lo[`:db;0;0]
q)\v
,`trade

No sym, and no trade.v2$. The suffix is the only thing that hides an entry: a directory without a .d is loaded as a dictionary, and a leading dot does not exempt one either. That makes $ the right marker for anything you are staging or keeping for rollback, since the loader neither names it nor maps what is inside it.

Only in the database root

Inside a partition directory every entry is expected to be a table, and neither a $ suffix nor a leading dot exempts it. A staging directory left in a partition breaks the load:

q)\l pdb
't.v2$

So versioned or in-progress directories for a partitioned table belong in the database root, not in the partitions. See atomicity, integrity, and durability for the pattern.

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