Skip to content

Loading a database

This page explains how to load a KDB-X database, how memory is mapped and how to serve to other users as an HDB.

The sample database

The examples below use a sample database generated with the Datagen module — five trading days of trade and quote partitions, plus a splayed daily table and some reference data in the root:

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

Add a vector of a million floats to the root as well, so that every kind of root object is represented:

q)`:/tmp/testdb/v set 1000000?100f
`:/tmp/testdb/v

This provides the following structure:

/tmp/testdb
├── 2026.04.06
│   ├── quote
│   └── trade
├── 2026.04.07
│   ├── quote
│   └── trade
├── 2026.04.08
│   ├── quote
│   └── trade
├── 2026.04.09
│   ├── quote
│   └── trade
├── 2026.04.10
│   ├── quote
│   └── trade
├── daily
│   ├── .d
│   ├── close
│   ├── date
│   ├── high
│   ├── low
│   ├── open
│   ├── price
│   ├── size
│   └── sym
├── exnames
├── master
├── sym
└── v

Loading with \l

The \l system command builds on get to load and organize a whole database, which is what you want for a partitioned or segmented database spread over many directories.

\l presents the partitions as one table, with date as a virtual column taken from the directory names:

q)\l /tmp/testdb
q)select trades: count i by date from trade
date      | trades
----------| ------
2026.04.06| 48329
2026.04.07| 46954
2026.04.08| 51390
2026.04.09| 50833
2026.04.10| 50899

There is no date column in any partition directory — each holds only sym, time, price, size, stop, cond, and ex. The partitioning column is reconstructed from the directory names, which is why writing a date column into a date-partitioned table is a mistake.

get on the same directory does no such assembly — it returns a dictionary keyed by partition, with the tables in each:

q)2#get `:/tmp/testdb
          | quote                                                            ..
----------| -----------------------------------------------------------------..
2026.04.06| +`sym`time`bid`ask`bsize`asize`mode`ex!(`p#`sym!0 0 0 0 0 0 0 0 0..
2026.04.07| +`sym`time`bid`ask`bsize`asize`mode`ex!(`p#`sym!0 0 0 0 0 0 0 0 0..

What \l maps

\l loads a partitioned table deferred, never immediate. Nothing is mapped at load time, and each query maps the columns it touches and releases them when it finishes.

Watch mmap across a load and a query. It moves once — by exactly the 8MB of the float vector v, which is mapped for the life of the session — and the partitioned query leaves it where it was:

q)`used`mmap#.Q.w[]
used| 471904
mmap| 0
q)\l /tmp/testdb
q)`used`mmap#.Q.w[]
used| 484128
mmap| 8000016
q)select trades: count i by date from trade
date      | trades
----------| ------
2026.04.06| 48329
..
q)`used`mmap#.Q.w[]
used| 485072
mmap| 8000016

146MB of trade and quote on disk therefore cost nothing to load, however many partitions there are.

.Q.s1 shows the same thing from the other side. A deferred splayed table holds its path; a partitioned table holds only its own name, because the path to a column depends on which partition a query reaches for:

q).Q.s1 trade
"+`sym`time`price`size`stop`cond`ex!`trade"
q).Q.s1 daily
"+`date`sym`open`high`low`close`price`size!`:./daily/"

Objects in the database root

\l recurses into the root and loads whatever else it finds there, not just the partition directories — but not every kind is mapped. All of them become variables in the root namespace, alongside the partitioned tables and the partition list date:

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

In terms of memory, they do not all cost the same:

  • A splayed table directory (daily) is mapped deferred, exactly like a partition — .Q.s1 shows its path rather than its columns, and it contributes to neither memory figure.
  • A vector of fixed-width type (v) is memory-mapped, and stays mapped for the life of the session — all 8MB of it shows up under mmap, and nothing shows under used.
  • A symbol vector (sym) is not mapped. Symbols have to be interned in the symbol pool, so the file is read and converted, putting the result on the heap.
  • A dictionary (exnames) or a serialized, non-splayed table (master) cannot be mapped either, and is deserialized onto the heap.

The last three are what the 12KB rise in used above paid for. They are small here — this sample database has only 122 symbols, 19 exchange names, and 51 rows of master:

q)count sym
122
q)count exnames
19
q)count master
51

But the distinction matters when sizing a real process. Partitions and splayed tables cost nothing at start-up however large the database, while a sym file, a dictionary, or a serialized table in the root is paid for in resident memory every time the database is loaded. In a historical database of any age, the sym file is the part that grows, and it is the usual reason such a database starts with a non-trivial heap.

Note that these root objects are not incidental: a dictionary like exnames is often the natural way to hold reference data, and it composes into a select like any other value — for more information, see a database is not only tables.

The current directory

\l doesn't only load — it changes the process's current directory to the database it loaded:

q)system"cd"
"/tmp"
q)\l /tmp/testdb
q)system"cd"
"/tmp/testdb"

This is what lets \l . reload the database in place, and it's why daily records `:./daily/ — the deferred path is relative to the new current directory. It also means later relative paths resolve against the database rather than against wherever the process started, which catches out a script that loads a database and then opens a file of its own.

.Q.lo loads the same database without that side effect. Its two flags control whether to change directory and whether to execute any scripts found in the database directory, as the following example shows them both flagged off:

q)system"cd"
"/tmp"
q).Q.lo[`:testdb;0;0]
q)system"cd"
"/tmp"
q).Q.s1 daily
"+`date`sym`open`high`low`close`price`size!`:testdb/daily/"

The result is otherwise the same load: trade is still a partitioned table and daily still mapped deferred. Only the recorded path differs, now relative to the unchanged current directory.

The .Q namespace adds further variants, such as .Q.li for loading partitions into a database that is already loaded.

Loading from the command line

Passing a directory path on the command line does at start-up what \l does, so a process can come up with its data already loaded:

$ q /tmp/testdb
q)select trades: count i by date from trade
date      | trades
----------| ------
2026.04.06| 48329
2026.04.07| 46954
2026.04.08| 51390
2026.04.09| 50833
2026.04.10| 50899

Add -s to give the process secondary threads, which lets queries run over several partitions in parallel:

q /tmp/testdb -s 4

The HDB process

Everything above is a single session loading a database for its own use — a q prompt, a script, or a Python process using KDB-X Python. There is no server, and most analysis never needs one.

The step to take this into production is to keep such a process running and let other people query it. A q process that has loaded a database and serves queries over IPC is conventionally called an HDB (historical database) when the data it serves is historical. It is the same \l and the same mapping described above; what changes is that the process outlives the question and answers on behalf of others. There is still no database server to install — an HDB is just q, started against a database directory.

Beyond the data, an HDB almost always loads a script of its own:

q hdb.q -p 5010 -s 4

where hdb.q loads the database and defines everything the process is expected to expose:

\l /tmp/testdb
/ business logic clients call
vwap:{[t;s] exec size wavg price from t where sym=s}
/ constants and reference data
tickSize:0.01
/ handlers for entitlements, logging, query limits
.z.pg:{[qry] logQuery qry; value qry}

That script is code: it belongs in version control and goes through review and release like any other code. The database directory beside it is data, and is not versioned the same way. Keeping the two apart is what lets one database be served by several processes running different releases of the logic, and lets the logic be rolled forward without touching a byte of data.

One query at a time

By default a q process is single-threaded and handles incoming messages sequentially, so an HDB serves one query at a time. While it is executing, other clients' requests wait in its input queue: one expensive or badly-bounded query delays everybody connected to that process.

Secondary threads (-s) do not change this. They parallelize work within one query — across partitions and columns — so that query finishes sooner, but the process still takes one request at a time.

Multithreaded input mode does not solve this

q has a multithreaded input queue mode that gives each connection its own thread, but it is designed for serving static in-memory data (RDB typically) and explicitly not for data from disk, and queries running in it cannot update globals.

Concurrency for an HDB is solved by running more than one HDB.

Handling concurrent queries

Every approach has the same shape: run several HDB processes against the same database, and put something in front of them to spread the work. Running several is safe and cheap — mapped data is read-only and shared by the operating system, so nothing is locked and nothing is copied, and as the pool section below notes, they even share the benefit of a warmed page cache. What differs is what does the spreading.

TCP socket sharding: a basic load balancer

Built into q, and the cheapest thing that works. Start each HDB with the rp prefix on the same port, and the kernel distributes incoming connections across them:

q hdb.q -p rp,5010    # on one host, started several times

Clients connect to a single address, with no extra component to build or run, and no change to client code. These limits matter though: the kernel balances connections, not query cost, and knows nothing about which process is busy — it may route a new connection to a listener that is blocked mid-query, leaving that client waiting while another process sits idle. It also cannot route by content, aggregate across processes, or fail over.

See socket sharding for supported patterns, including dynamic scaling and rolling updates, and for a worked demonstration of the busy-listener behavior.

A kdb+ gateway: the widespread solution

A gateway is a q process that clients connect to instead of the HDBs. It holds connections to the back-end processes, tracks which are free, forwards each query to one of them, and returns the result.

Because the gateway is itself q, it can do far more than balance load. It can route by content — today's data to the RDB, historical data to an HDB — and split one client query across several processes and aggregate the results, cache, enforce entitlements, and handle a back-end dying mid-query. This flexibility is why it's the standard setup in production systems. The cost comes from a component that you must design, write, and maintain, and from a hop that every query pays.

See gateway design for the design principles, query routing for a full worked framework, and a load-balancing server for a minimal implementation.

A protocol-aware load balancer: the performant solution

A dedicated load balancer that understands the kdb+ IPC protocol sits in front of the pool and is transparent to both sides: clients connect to it as though it were an HDB, and the HDBs need no gateway logic of their own.

Because it parses the protocol rather than merely accepting TCP connections, such a balancer can typically dispatch per query rather than per connection, health-check back ends properly, drain a process for maintenance, and absorb a back-end restart without breaking client connections. It is the option to reach for when the pool is large or availability requirements are strict — and where the alternative is maintaining gateway code that does the same job less well.

Query timeout

A single query can keep a q process busy for a very long time — an unbounded select over every partition, an accidental cross join, a scan with no constraint on the partitioning column — and because an HDB serves one query at a time, that one query blocks every other user connected to it. The -T command-line option puts a ceiling on it: -T n aborts any client call still running after n seconds with 'stop and leaves the process free to take the next request, so a runaway query costs one client an error instead of stalling everybody. The default is 0, meaning no limit, and \T changes it in a running process. Note that it applies only to calls arriving over IPC — work typed at the console or run from a start-up script is never interrupted, so testing -T in a local session appears to do nothing.

Keeping partitions mapped with .Q.MAP

Everything above describes the default case: a partitioned database is mapped deferred, and every query pays to map the columns it touches and unmap them afterwards. .Q.MAP trades that away for immediate mapping. Run after the load, it maps every partition of every table and holds them mapped for the life of the session, so repeated queries skip the mapping work entirely.

Both sides of the trade show up at once. Load the sample database and time a query — \t:20 gives the total milliseconds for 20 repetitions:

q)\l /tmp/testdb
q)`used`mmap#.Q.w[]
used| 482144
mmap| 8000016
q)\t:20 select vwap: size wavg price by sym from trade where date=2026.04.10
5

Then map the partitions and run exactly the same query:

q).Q.MAP[]
q)`used`mmap#.Q.w[]
used| 487920
mmap| 160354747
q)\t:20 select vwap: size wavg price by sym from trade where date=2026.04.10
1

The query got several times faster, and mmap went from the 8MB of v alone to 153MB — the whole database. That is the trade: the start-up cost that partitioning exists to avoid is paid back deliberately, in return for dropping the per-query mapping overhead.

Use .Q.MAP with caution, and never on a compressed database

  • Mapped memory is held for the life of the session, and it scales with the size of the database rather than with the queries. On a database of any real size this becomes the dominant memory cost for the process.
  • .Q.MAP opens a handle to every file, so a large database may need the process's file-handle limit raised (ulimit -n), and on Linux the file-map limit too (vm.max_map_count).
  • On a compressed database, it is worse than unhelpful: decompressed maps have no backing file on disk, so they occupy physical memory or swap for as long as they are held.
  • The .Q.MAP reference lists further limitations, including linked columns and virtual partition columns. Check it against your q version before relying on it.

Reach for it when one process serves the same uncompressed database over and over, query latency matters more than resident memory, and the machine has room to hold the database mapped. Otherwise, leave the default deferred mapping in place.

Since 4.1t 2024.01.11 the mapping is itself parallelized over tables and partitions when the process has secondary threads, so the .Q.MAP[] call is quicker on a process started with -s.

Pre-fetching with -23!

The first run of a query is often slower than the runs that follow it, and nothing about the query has changed. What changed is where the data came from: the first run reads column files from storage, and afterwards those pages sit in the operating system's page cache, so later reads are served from RAM.

This is a separate thing from q's mapping. \l and .Q.MAP decide whether q holds a mapping of a file; the page cache decides whether the bytes behind that mapping are already in memory. A mapped column still costs a disk read the first time it is touched.

-23!x (since V3.1t 2013.03.04) lets you pay that cost deliberately, ahead of time. It takes an object rather than a file path, and asks the OS to make the pages behind it resident — on POSIX systems this is the madvise hint (MADV_WILLNEED), and q may also fault the pages in directly.

It returns its argument, so end the call with a semicolon unless you want the data echoed to the console:

q)-23! v;

Warm only the columns a query will touch, for one partition:

q){-23! get hsym `$"./",(string x),"/trade/",string y;}[2026.04.10] each `price`size

Or hand it a whole table within a partition, located with .Q.par:

q)-23! get .Q.par[`:.;2026.04.10;`trade];

This earns its place in a process that knows in advance what will be asked of it: a start-up routine warming today's partition, or a scheduled job warming the columns a report needs just before the report runs. The I/O wait moves out of the query's latency and into a moment where it costs nothing.

Pre-fetching for a pool of HDB processes

The page cache belongs to the machine, not to the process that filled it, and that makes the benefit shared. A common deployment runs a pool of HDB processes — several q processes on one host, behind a gateway, all mapping the same on-disk database so that concurrent queries can be spread across them.

There, a single -23! warms the data for the whole pool. One process pre-fetches a file, and a query arriving at any other HDB that touches the same file is served from cache, even though that process never called -23! itself — provided the pages have not been evicted in the meantime.

So warming is worth doing once and centrally — from whichever process runs the start-up or scheduled routine, or from a dedicated warming process — rather than repeated in every member of the pool, where the later calls would mostly find the pages already resident and do nothing.

The page cache is not yours to command

  • -23! asks; the kernel decides. It does not guarantee the pages are resident when the call returns, and the OS may evict them again at any time under memory pressure.
  • The cache being machine-wide cuts both ways. It's what lets one process warm data for a whole pool, and it's also the risk: warming a large database can evict pages that another workload — including another HDB in the pool — was relying on.
  • How much it buys you depends entirely on your storage and on what the cache already holds — on fast local NVMe, the gap between a cold and a warm read is far smaller than on network storage. Measure with your own data and hardware rather than assuming.
  • Unlike .Q.MAP, -23! holds nothing on q's heap and adds nothing to mmap; it only influences what the OS keeps in RAM. The two are complementary — .Q.MAP keeps the mapping, -23! warms the bytes behind it.

Next steps