Skip to content

Database maintenance

This page explains how to keep a partitioned KDB-X database consistent and how to change its schema after the data is written. It covers filling gaps where a table is missing from a partition, and adding, renaming, casting, or removing columns across every partition at once.

Writing a database is one operation. set persists a flat or splayed table. set or .Q.dpft writes partitioned ones. What takes work is everything afterwards, because schemas change:

  • Adding, renaming, or removing tables
  • Adding or modifying columns
  • Adding an attribute to speed up queries
  • Correcting or updating data
  • Casting a column, for example, from int to long

For a flat or splayed table each of those is a single operation on a single file or directory. For a partitioned table you must apply it to every partition, consistently. A database holding a few years of data across a handful of tables is already thousands of files. A change that succeeds on some partitions and fails on others leaves the database in a worse state than before the change started.

Partition consistency

Every partitioned table must exist in every partition, even where it holds no data for that date. q doesn't enforce this, and doesn't check it: the failure surfaces later, as a query error.

The examples use a database generated with the Datagen module:

q)([getInMemoryTables; buildPersistedDB]): use `kx.datagen.capmkts
q)buildPersistedDB["db"; ([tbls:`trade`quote; start:2026.04.06; end:2026.04.07])]

Remove the quote directory from one partition and any query touching that table fails, naming the table rather than the partition:

q)\l db
q)select n:count i by date from quote
'quote

Gaps like that arise from a table directory being deleted, a partition being written without every table, or a .d file that no longer matches the column files beside it.

Fill the gaps on disk with .Q.chk

.Q.chk scans a database for tables missing from partitions and writes an empty table into each gap, using the most recent partition that has the table as the template. It reports the partitions it fixed:

q).Q.chk[`:db]
,`:db/2026.04.07
()

.Q.chk fills the gap on disk, and queries succeed. The repaired partition contributes no rows, which is the point — an empty table is what lets a query span the partition instead of failing on it:

q)\l db
q)select n:count i by date from quote
date      | n
----------| ------
2026.04.06| 498001

This is the fix to apply after a write-down, as part of the same routine that creates the partition.

Fill the gaps in one session with .Q.bv

.Q.bv solves the same problem without touching the disk. It scans for missing tables and builds the dictionary .Q.vp of prototype schemas, taken from the last partition that has each table. The partitioned select then consults that dictionary:

q)\l db
q)select n:count i by date from quote
'quote
q).Q.bv[]
q)key .Q.vp
`s#`quote`trade
q)select n:count i by date from quote
date      | n
----------| ------
2026.04.06| 498001

The database on disk is unchanged — the missing directory is still missing. Call .Q.bv[] after each load, since a reload discards it.

The two are for different situations:

Aspect .Q.chk .Q.bv
Fixes The database on disk The current session only
Writes Empty tables into the gaps Nothing
Lasts Permanently Until the next load
Use it In the write-down routine When writing to the database is impossible or barred

.Q.bv[] takes its prototype from the last partition; .Q.bv[`] takes it from the first.

Auto-filling gaps is not the default, deliberately

Neither function runs on its own. Some administrators prefer a query to fail loudly rather than return a silently manufactured empty table, because an empty result for a date expected to hold data is a bug worth hearing about. Decide which you want before wiring either into a startup script.

Change a schema with DBmaint

The DBmaint module applies a schema change across every partition of a database in one call, which is the part that is tedious and error-prone to do by hand. Load it and it gives you a dictionary of functions:

q)dbm:use `kx.dbmaint
q)asc key dbm
`s#`addCol`addMissingCols`addTab`castCol`copyCol`delCol`delTab`fnCol`hasCol`listCols`renameCol`renameTab`reorderCols`rmAttr`setAttr

addMissingCols is the counterpart to .Q.chk at column granularity. Where .Q.chk fills in whole missing tables, addMissingCols brings older partitions up to the newest partition's column list, which prevents missing-column errors on queries that span a schema change.

For the full signatures and examples, see the DBmaint module and its documentation on GitHub.

Before you change anything

These operations rewrite files in every partition, so the work is proportional to the size of the database, not to the size of the change. Two consequences worth planning for:

  • Take a backup, or test on a copy first. A schema change applied to hundreds of partitions isn't something to try out on production data; there is no undo.
  • Expect it to take real time and I/O on a large database. Schedule it like a write-down rather than running it between queries.

Next steps