Skip to content

Atomicity, integrity, and durability

This page explains what happens when a write to a database is interrupted, at the level of a single table and of a whole partition; what a query sees while a table is appended to; how to publish a write so that readers only ever see a complete one; how to sort a table on disk without risking its rows; in which order to apply a schema change so that every step leaves a consistent database; and what it additionally takes for that publish to survive a crash.

A KDB-X database is a tree of ordinary files and directories, and q writes them one at a time. Nothing in the format provides a transaction, so there is no point at which a half-finished write is hidden from readers or rolled back. Whether a reader sees a loud error or silently wrong data depends on exactly where the write stopped.

Which writes are atomic

set on a single file is atomic

The unit that q writes atomically is a single file. When set writes one file, such as a column or a .d, it doesn't overwrite the file in place. It writes the new contents to a temporary file next to the target, whose name is the target's name with a $ appended, and then renames that over the target. While the write is under way, a reader of x sees the old file, and x$ grows beside it. Once the write is done, the reader sees the new file. It never sees a partial one.

The rename shows up as a new inode:

q)`:f/x set 1 2 3
`:f/x
q)\ls -i f/x
"105839904 f/x"
q)`:f/x set 9 9
`:f/x
q)\ls -i f/x
"105839908 f/x"

An append doesn't work like this. upsert and .[file;();,;data] extend the existing file in place, keeping the same inode, so an interrupted append can leave a partial file:

q)`:f/x set 1 2 3
`:f/x
q)`:f/x upsert 4 5
`:f/x
q)\ls -i f/x
"105839904 f/x"

Writing a splayed table is not atomic

The guarantee covers one file, and a splayed table is many files. set writes them one after another, so each column is replaced atomically but the table as a whole is not, and neither set nor upsert of a splayed table is atomic. If the write fails partway — a permission problem, a full disk, a killed process — what is left on disk is neither the old table nor the new one.

set writes the .d file first, then each column in the order .d lists them (not alphabetical order). So a failure leaves .d promising columns that were never written:

q)t:([] alpha:1 2 3; zeta:10 20 30; mid:1.5 2.5 3.5)
q)\mkdir -p t2/zeta      / put something in the way of column zeta
q)`:t2/ set t
't2/zeta. OS reports: Is a directory

The directory now looks like a table, and even reads back as one, because get trusts .d:

q)get `:t2/.d
`alpha`zeta`mid
q)get `:t2/
+`alpha`zeta`mid!`:t2/
q)get `:t2/alpha
1 2 3

but the column that was never reached doesn't exist, so any query touching it fails:

q)get `:t2/mid
't2/mid. OS reports: No such file or directory
q)select from get `:t2/
't2/mid. OS reports: No such file or directory

An upsert to a splayed table fails partway in the same way, and leaves the columns at different lengths. Because an append is also visible while it runs, it has a section of its own: appending to a table that is being queried.

Appending to a table that is being queried

An append to a splayed table extends each column file in place, one after another in .d order. Unlike set, it stages nothing, so a process that queries the table while the append runs sees it partway through. This is what happens in an intraday writedown when today's data is queried during the day.

An append to a splayed table can't be made atomic. It writes into the very files that readers are reading, so there is nothing to stage and nothing to swap. The publishing pattern used for other writes would mean copying the whole table for every batch. What you can do instead is make readers ignore the rows that an append hasn't finished, as described in publish a committed row count.

A query sees a consistent prefix

q appends to a column with a single write at the end of its file, and takes the length of a column from the size of its file. A query uses as many rows as the shortest of the columns it references. So a query that runs partway through an append sees only rows that every column it touches already has, and the values in each of those rows belong together.

Separate queries disagree

What varies is how many rows a query sees, because that depends on which columns it references. Here a batch is being appended to today's trade table, and has reached price but not size. Making size unwritable freezes that moment:

q)`:pdb/2026.01.02/trade/ set .Q.en[`:pdb] ([] sym:`a`b`a; price:10 20 30f; size:100 200 300)
`:pdb/2026.01.02/trade/
q)\chmod 444 pdb/2026.01.02/trade/size
q)`:pdb/2026.01.02/trade/ upsert .Q.en[`:pdb] ([] sym:`b`a; price:40 50f; size:400 500)
'pdb/2026.01.02/trade/size. OS reports: Permission denied
q)\l pdb
q)select avg price from trade where sym=`a
price
-----
30
q)select avg price, vwap:size wavg price from trade where sym=`a
price vwap
----------
20    25

Both queries ran against the same data at the same moment, and they disagree about the average price of a. The first includes the new row for a, and the second, which also references size, doesn't: adding the volume-weighted average price to the query changed the answer for avg price. count trade, which gives the length of the first column, reports a third view, of 5 rows. A dashboard that sends one query per column, or a client that compares the results of two queries, gets numbers that don't match, without an error.

An interrupted append corrupts the table

If the writer dies partway through an append, the columns stay at different lengths, and the disagreement becomes permanent. Which rows a query sees depends on the columns it references, and get of the table pads the short columns with nulls that can't be told apart from genuine nulls. So an interrupted append, including a process killed mid-upsert, counts as having corrupted the table, not as having partly succeeded.

String columns

A string column c, like any other nested column, is stored in two files: c holds an index into the data, and c# holds the data. An append writes c before c#, so for a moment the index points past the end of the data. A query in that window can fail with 'type, return garbage values, or return a wrong result without an error, for example from count each c.

New symbols

A process reads the sym file when it loads the database, and again whenever it reloads it, for example at the end of the day. When the writer appends rows with a symbol that is new, it adds the symbol to the sym file, and a query can reach a process that hasn't read the sym file since. That process doesn't know the symbol yet. It shows those rows with a blank sym, and a query for the new symbol finds nothing, without an error. Here another process has appended a row for each of the new symbols c and d:

q)select count i by sym from trade
sym| x
---| -
   | 1
   | 1
a  | 1
b  | 1
q)select from trade where sym=`d
date sym px
-----------

This isn't a passing race. It lasts until the process reads the sym file again. .Q.en only ever appends to the sym file, so a symbol never changes its position, and reading the file again is always safe:

q)sym:get `:sym
q)select count i by sym from trade
sym| x
---| -
a  | 1
b  | 1
c  | 1
d  | 1

Reading the sym file before each query isn't enough on its own, because rows appended while the query runs can carry symbols that the process hasn't read yet. Nor is it practical for the writer to tell every reader to read the sym file and wait until they have: a reader handles the message only between queries, so one long query holds up the writer, and a reader that hangs blocks it altogether. The committed row count described next solves this without the writer waiting for anyone.

Publish a committed row count

A query can't tell how far an append has got, so have the writer tell it. After each append is complete, the writer records how many rows the table has, and readers query only that many rows. Every column, string columns included, is complete below that count, and an append that is still running, or that failed, stays invisible.

The writer can keep the counts of all the tables it appends to in one dictionary, from table name to row count, and record it in a file in the database root, together with the number of symbols in the sym file under the key sym. Give the file a name that ends in $, so that the loader skips it. set replaces the file atomically:

q)`:pdb/2026.01.02/trade/ upsert .Q.en[`:pdb] batch
q)n[`trade]+:count batch
q)n[`sym]:count sym
q)(hsym `$"pdb/committed$") set n

.Q.en adds new symbols to the sym file before the append, and the counts are written last, so by the time a count is published, the sym file holds every symbol that the rows below it use.

Before each query, a reader reads the counts, and then, if the sym count has changed, reads the sym file again and keeps only that many symbols. The order matters. Because the counts are read first, the symbols the reader keeps include every symbol of the rows it is about to query, and rows with symbols it doesn't have are always above its counts, so its queries never see them. Most appends add no new symbol, so most queries don't read the sym file at all, which matters because reading it means reading the whole file. The reader then constrains each query to the committed rows with the virtual column i, the row index. A long query isn't affected by appends that happen while it runs, and the writer never waits for a reader.

Keeping only the committed number of symbols matters, because get doesn't lock the sym file. A reader can read it while .Q.en is extending it for the next batch, and the last symbol it reads may be cut short. That symbol is beyond the sym count, so the reader drops it, and it reads the symbol in full once the count includes it.

In the table that was frozen partway through an append above, the count of trade is still 3, and both queries now give the same answer:

q)n:get hsym `$"committed$"      / first the counts
q)n
trade| 3
quote| 2
sym  | 2
q)if[not n[`sym]=count sym; sym:n[`sym]#get `:sym]    / then the sym file, if it has grown
q)select avg price from trade where date=2026.01.02, i<n`trade, sym=`a
price
-----
20
q)select avg price, vwap:size wavg price from trade where date=2026.01.02, i<n`trade, sym=`a
price vwap
----------
20    25

i counts rows within a partition, so the constraint only works in a query on today's partition. A query that combines it with other dates fails with 'i. Query today's partition on its own, or keep today's data in a database of its own.

If the writer can also answer the queries on today's data, there is a simpler way. q runs one query at a time, so a query sent to the writer never overlaps an append, at the cost of the writer's time.

Publishing a write atomically

The remedy in every case is the same shape: write the new data where no reader is looking, then move it into place in a single operation. Only that last step is visible, so a process that loads the database sees either the old data or the new, never a half-written directory. A query that is already running when the step happens is a different matter, see replace the table that is being queried.

Somewhere no reader is looking means a name the loader skips — a directory whose name ends in $ is ignored, so it can sit inside the database while it is being built. A $ cannot appear in a `: literal, where it would parse as the cast operator, so build the handle with hsym:

q)(hsym `$"db/new$/trade/") set ([] id:1 2 3; px:1 2 3.0)
`:db/new$/trade/

When nothing is there yet

mv is all you need. Moving a directory within one filesystem is a rename(2), and that is atomic:

mv 'db/new$/trade' db/trade

Replacing a table that already exists

Here mv stops working, and it fails quietly. rename(2) refuses to replace a directory that is not empty. mv does not report that as a failure. Faced with an existing directory it moves the source inside the target, reports success, and publishes nothing:

$ mv -f 'db/new$/trade' db/trade
$ echo $?
0
$ ls db/trade/
id  px  trade
q)\l db
q)count trade
2

Still the old two-row version, with the new one stranded in a subdirectory beneath it.

So you have to clear the target first, which takes two moves — one to park the old version, one to publish the new:

mv db/trade 'db/trade.prev$'      # park the old version
mv 'db/new$/trade' db/trade       # publish the new one

Each move is atomic and the old version survives for rollback, so this is enough for many purposes. But two atomic steps are not an atomic step. Between them the table does not exist, and a reader loading the database in that window sees a database without it:

q)\l db
q)tables[]
,`other

In a partitioned database the same gap is worse, for the reasons below: a table missing from the most recent partition vanishes from the database altogether, and one missing partition directory makes queries fail for every date.

The window is two metadata operations wide, so it is brief — but it is real. A process that dies between them leaves the database missing a table until someone intervenes. Where that is not acceptable, the swap has to be a single operation.

Park the old version under a $ name too

The loader doesn't skip trade.prev. Once the new table is in place, trade cannot be both a table and a namespace, and the load fails:

q)\l db
'trade.prev

Ending the name in $ keeps the parked copy out of the loader's way.

A single-operation swap, using a symlink

A directory cannot be replaced atomically, but a file can: rename(2) over an existing file is atomic. So make the published name a symlink, and swap the link rather than the directory.

The versions live inside the database, in a $-suffixed container, and the name in the database root becomes a link to one of them:

db/
├── versions$/
│   ├── trade.v1/
│   └── trade.v2/
├── other/
└── trade -> versions$/trade.v2

Build the first version, link it in, and q reads straight through the link:

q)(hsym `$"db/versions$/trade.v1/") set ([] id:1 2; px:1 2.0)
`:db/versions$/trade.v1/
q)\ln -s 'versions$/trade.v1' db/trade
()
q).Q.lo[`:db;0;0]
q)trade
id px
-----
1  1
2  2

Writing the next version touches nothing that a reader is reading, so it can take as long as it likes and fail as often as it likes. Only the final ln is visible to a reader:

q)(hsym `$"db/versions$/trade.v2/") set ([] id:1 2 3; px:1 2 3.0)
`:db/versions$/trade.v2/
q)\ln -sfn 'versions$/trade.v2' db/trade
()
q).Q.lo[`:db;0;0]
q)trade
id px
-----
1  1
2  2
3  3

The price is that the live data no longer lives where the table appears to be. versions$ is not scratch space — it is where the production table actually is, and the database root holds only a pointer to it.

That can be confusing to whoever inherits the database. It also has practical consequences: a backup must follow the links (rsync -L, tar -h) or cover the versions directory too, du on the root under-reports, and old versions need a retention policy rather than accumulating forever.

A few further details decide whether this works:

  • Do not use mv to replace the link. mv follows a symlink that points at a directory, so instead of replacing the link it moves the new link inside the old version:

    $ ln -s 'versions$/trade.v1' db/trade.new
    $ mv -f db/trade.new db/trade      # does NOT replace db/trade
    $ ls 'db/versions$/trade.v2/'
    id  px  trade.new
    
  • ln -sfn is not quite atomic. It removes the old link and creates the new one, leaving a brief window in which the name does not exist. For a genuinely atomic swap, create the new link under a temporary name and rename(2) it over the old one — os.replace in Python, or mv -T on Linux where coreutils provides it:

    ln -s 'versions$/trade.v1' db/.trade.tmp
    python3 -c "import os; os.replace('db/.trade.tmp','db/trade')"
    
  • A running query can see both versions. With the default deferred mapping, a running process reads trade.v2 from its next query, without reloading. But a query that is running at the moment of the swap may have opened some columns of trade.v1 and opens the rest from trade.v2. If that matters, hold queries on the table around the swap, as described in replace the table that is being queried.

  • Keep the old version until nothing is reading it. A query that has already mapped files of trade.v1 keeps them until it finishes, so you can't delete the old directory immediately.

  • The versions can live on other storage. Because a link can point anywhere, the versions need not share a filesystem with the database, whereas mv between filesystems degrades to copy-then-delete and is not atomic at all.

On Linux there is one genuine atomic directory swap that avoids symlinks altogether — renameat2 with the RENAME_EXCHANGE flag, which exchanges two directories in a single operation, keeping the old version and leaving no window. It is not portable, needs filesystem support, and mv --exchange only exists in coreutils 9.5 and later, so reaching it usually means a few lines of ctypes.

A partition is not atomic either

A partition usually holds more than one table — a day of market data might be a trade directory and a quote directory side by side — and q writes them one after another. Between the two writes the partition exists but is incomplete, and that is visible to every reader.

It is worse than a missing directory, because q decides which tables the database has from the most recent partition. A table absent from that partition does not simply lack a day of data: it does not exist at all.

pdb/
├── 2026.01.01/
│   ├── quote/
│   └── trade/
└── 2026.01.02/
    └── trade/      (quote not written yet)
q)\l pdb
q)tables[]
,`trade
q)select count i by date from quote
'quote

quote is gone from the whole database, including the day that was complete. The error names the table, not the partition, which can lead a reader to misread the cause.

The mirror case is a table missing from an earlier partition. The table then exists, because the last partition has it, but any query that spans the gap fails on the missing directory:

q)select count i by date from quote
'/…/pdb/2026.01.01/quote. OS reports: No such file or directory

This is what .Q.chk repairs

.Q.chk fills in the missing table directories as empty tables, after which a query returns only the dates that hold data. Run it after a load that went wrong, and see database maintenance for the difference between repairing the disk with .Q.chk and patching the session with .Q.bv.

The fix is the same shape as for a single table: write where no reader is looking, and publish with a symlink. Because the loader only skips a $-suffixed directory in the database root, the staging area goes there rather than in the partition.

Build the new table under versions$, and nothing about the database has changed yet — the load still sees only trade:

q)(hsym `$"pdb/versions$/2026.01.02/quote/") set ([] id:3 4; bid:3 4.0)
`:pdb/versions$/2026.01.02/quote/
q)\l pdb
q)tables[]
,`trade

Adding the link is what publishes it, and the table appears complete:

q)\ln -s '../versions$/2026.01.02/quote' pdb/2026.01.02/quote
()
q)\l pdb
q)tables[]
`s#`quote`trade
q)select count i by date from quote
date      | x
----------| -
2026.01.01| 2
2026.01.02| 2

For a whole new partition, link the partition rather than each table, so that every table in it appears in one operation. The link lives in the database root, so its target is relative to that:

ln -s 'versions$/2026.01.02' pdb/2026.01.02

A partition directory may itself be a symlink — q resolves it like any other path, and the partition name still has to look like a partition value.

The same trick works for a whole database

Nothing here is specific to one table or one partition. Symlinking a whole database root lets you assemble a rebuilt database out of the way and publish it in one step.

Sorting a table on disk

Given a handle to a splayed table, xasc sorts the table on disk by rewriting its files. It works in three passes, one file at a time:

  1. Check. It opens each column file for writing, in the order .d lists them, without changing any of them. A column it can't write stops the sort before anything has changed.
  2. Rewrite. It computes the sort order from the sort columns, then writes each column in that order, again in .d order. Each column is written the way set writes a file, to a temporary file whose name ends in $ that is then renamed over the original. So each column is replaced atomically, but the table as a whole is not.
  3. Attribute. It rewrites the first sort column once more, to give it the sorted attribute s#.

A failure in the check pass leaves the table as it was, and a failure in the attribute pass leaves it sorted but without s#.

A failure in the rewrite pass misaligns the rows

The check pass doesn't cover everything the rewrite needs, above all memory. The sort holds the sort order, one long per row, and one column at a time in memory, so for a large table it can fail with 'wsfull, or be killed by the operating system when it runs out of memory. A failure during the rewrite leaves the columns before the failure in .d order sorted, and the rest in their original order, so every row combines values from different original rows.

An interrupted sort loses data

Once a sort column has been rewritten, nothing records which original row each value came from, so the table can be neither restored nor sorted to completion. Except in rare cases, the table is corrupt and its data is lost. Treat this as a data loss, which in most domains is a severe failure. Only a copy of the table from before the sort can replace it.

The damage is also silent. Every column is complete and has the right length, and there are no nulls, so the table loads and queries without an error, and nothing on disk shows that the rows are wrong. Unlike an interrupted append, it can't be seen from column lengths.

Readers during a sort

A process that queries the table while the sort runs can get wrong answers too. With the default deferred mapping, each query opens the column files by name, so a query that runs between two renames combines columns already sorted with columns not yet sorted, and returns misaligned rows without an error.

Sort a copy, then publish it

The most common on-disk sort comes from an intraday writedown. When today's data is too large for the real-time database (RDB), each batch is appended to a table on disk as it arrives, so the table is in time order. At the end of the day, it is sorted by sym and time, and sym is given the parted attribute p#. Until then, today's data is queried from the time-ordered table, which is slow but correct, so it can't be sorted in place.

So treat an on-disk sort like any other write: sort a copy where no reader is looking, and publish it once the sort has succeeded. Have the process that writes the table start the sort after its last append, so that nothing writes to the table while it is copied and sorted.

The copy costs no space at first, but every column the sort rewrites is a new file, so by the end the filesystem needs room for the table twice.

Because xasc replaces each file rather than writing into it, the copy can be made of hard links. cp -al creates it without copying any data, and the sort then replaces each link in the copy with a new file, leaving the files of the original untouched. Queries on the time-ordered table carry on while the sort runs. If the sort fails, or the process dies, delete the copy and start again from the original.

Hard links only work within one filesystem, and the move that publishes the copy is only atomic within one, so the time-ordered table, the staging directory and the database all have to be on the same filesystem. Put the staging directory in the database root, under a $ name the loader skips.

xasc can't sort a partitioned table, so in a partitioned database you sort each partition's table on its own. Here a time-ordered trade table, written down intraday to intraday/trade and enumerated against the sym file of the database pdb, is copied into the staging directory, sorted, and given p#:

mkdir -p 'pdb/sort$/2026.01.02'
cp -al intraday/trade 'pdb/sort$/2026.01.02/'
q)\l pdb
q)`sym`time xasc hsym `$"sort$/2026.01.02/trade"
`:sort$/2026.01.02/trade
q)@[hsym `$"sort$/2026.01.02/trade";`sym;`p#]
`:sort$/2026.01.02/trade

How to publish the sorted copy depends on whether readers are already querying the name it is published under.

Publish under a new name

If today's data is written down to a directory of its own, as in the example above, the sorted copy becomes a new partition of the database. No query can be using a partition that doesn't exist yet, so one move publishes it safely. Stage every table of the day under the same staging partition, and move the partition as a whole, so that all its tables appear at once:

mv 'pdb/sort$/2026.01.02' pdb/

HDB processes see the new date when they reload. The time-ordered table goes on serving its own readers in the meantime. Delete it once nothing queries it any more, and on NFS, delete it through a trash directory.

Replace the table that is being queried

If today's data is written down into today's partition of the database itself, so that HDB processes serve it during the day, the sorted copy has to take the place of the time-ordered table. Here an atomic swap is not enough. A query running at the moment of the swap may already have opened some columns of the old version and opens the rest from the new one. Both versions have the same columns and the same length, so the query returns misaligned rows without an error.

So hold queries on the table while you swap it, for example at the gateway, and release them afterwards. No reload is needed: a running process reads the new version, p# included, from its next query. Swap with a symlink if the table is published through one, because that is a single rename. With two moves, as in replacing a table that already exists, the table is missing between them, and in the most recent partition a process that loads the database in that gap doesn't see the table at all.

Delete the time-ordered version once no query can be reading it, and on NFS, delete it through a trash directory.

Changing a schema in place

Adding, deleting, or renaming a column or a table changes more than one file: the column files and .d, or a directory in every partition. Each single step can be atomic. Rewriting .d with set replaces it in one operation, because set on a single file is atomic, and mv and ln are each one system call. The sequence of steps is not atomic, so what matters is the order. For most changes there is an order in which every intermediate state is one a reader can load and query correctly.

Two rules decide the order:

  • A file that .d does not list is ignored, but a column that .d lists and that is missing breaks every query that touches it. So create a column's file before you list it in .d, and remove it from .d before you delete its file. A failure in between then leaves only a stray file, which does no harm.
  • In a partitioned database, the most recent partition decides the schema. Its .d gives the columns and its directory gives the tables, whatever the earlier partitions hold. So make an addition in the most recent partition last, and a removal there first. A table or column that only earlier partitions have is invisible. One that only the most recent partition has is visible, and fails for every date that lacks it.

Rewrite .d with set, not by amending it

@[dir;`.d;,;`c] is an append, so it extends the existing .d in place and doesn't get the write-then-rename that set does. Pass set the whole new column list instead.

Changing a database that is being queried

In production a schema change can happen while HDB processes are serving the database, and their queries must neither fail nor return wrong data while it runs. The two rules above protect a process that loads the database partway through the change. A process that loaded it before the change needs one more rule.

Such a process keeps the tables and columns it found when it loaded the database, and doesn't read .d again. With the default deferred mapping, each query opens the column files by the names the process remembers. So for a process that is already running:

  • Adding a file, a link, or a .d entry, or rewriting .d, doesn't affect it. It goes on answering with the old schema, and doesn't see the change until it reloads.
  • Deleting or moving a file or directory that it still knows about breaks it, even when you took every step in the safe order. Here the column size was deleted correctly, .d first, by another process after this one had loaded the database:

    q)select id from trade
    id
    --
    1
    2
    3
    q)select from trade
    './trade/size. OS reports: No such file or directory
    

So a schema change on a live database takes three phases:

  1. Make the change. Do every addition and every .d rewrite, in the order the following sections give, in every partition. Running processes keep serving the old schema throughout.
  2. Reload every process once the change is complete, with \l . or however the process loads its database. Don't reload partway through: most intermediate states are safe to load, but they aren't the change you meant, and some, such as a partitioned table renamed in only some partitions, aren't safe at all.
  3. Delete only after every process has reloaded. That covers the files of a deleted column, the old names of a renamed column, and the old links of a renamed table. Until then a running process may still read them. Deleting or renaming a whole table is the exception: a running process loses the table as soon as it moves, so stop clients querying it first (see delete a table).

A process that ran .Q.MAP holds every file mapped, so even a deletion doesn't change its answers until it reloads. The disk space isn't freed until then either. That doesn't make phase 3 optional, because the database may also be served by processes that use the default deferred mapping.

Add a column

Write the column file, then add its name to .d. Until .d changes, the loader doesn't see the new file:

q)`:db/trade/ set ([] id:1 2 3; px:1 2 3.0)
`:db/trade/
q)`:db/trade/size set 100 200 300
`:db/trade/size
q)\l db
q)cols trade
`id`px
q)`:db/trade/.d set `id`px`size
`:db/trade/.d
q)\l db
q)trade
id px size
----------
1  1  100
2  2  200
3  3  300

The new column needs one item per row, or the table has the mismatched column lengths described above.

In a partitioned database, do this in every partition and finish with the most recent. Until that last .d changes, the column isn't part of the schema and queries are unaffected:

q)cols trade
`date`id`px
q)select from trade
date       id px
----------------
2026.01.01 1  1
2026.01.01 2  2
2026.01.02 1  1
2026.01.02 2  2

Start with the most recent partition instead and the column appears at once, so every query fails on the partitions that don't have it yet:

q)cols trade
`date`id`px`size
q)select from trade
'./2026.01.01/trade/size. OS reports: No such file or directory

Delete a column

Do the reverse: drop the name from .d, then delete the file. A nested column c also has a c# file, and sometimes a c## file, so delete those too.

q)`:db/trade/.d set `id`px
`:db/trade/.d
q)hdel `:db/trade/size
`:db/trade/size

In a partitioned database, rewrite the most recent partition's .d first. That is the moment the column leaves the database. Then rewrite the earlier partitions' .d files, and delete the column files last, after every running process has reloaded.

A .d with one column is still a list

Delete down to a single column and `id is an atom, which .d must not be. The table stops loading:

q)`:db/trade/.d set `id
`:db/trade/.d
q)\l db
'type

Write enlist `id instead.

Rename a column

A plain rename has no safe order. Rename the file first and .d lists a column that is missing. Rewrite .d first and it lists a name that doesn't exist yet. Either way, queries fail until the second step is done.

A hard link removes that window. ln gives the existing file a second name without copying any data, so for a while the column exists under both names, and .d decides which one the table uses:

q)\ln db/trade/px db/trade/price
q)`:db/trade/.d set `id`price
`:db/trade/.d
q)hdel `:db/trade/px
`:db/trade/px
q)\l db
q)trade
id price
--------
1  1
2  2
3  3

Every step leaves a table that loads and queries correctly. Before .d changes, price is a stray file, and afterwards px is. For a nested column, link the # and ## files too. A hard link only works within one filesystem, which is always true of two files in the same directory.

In a partitioned database, add the links in every partition first. After that every partition has the column under both names, so the order in which you rewrite the .d files no longer matters. The rename becomes visible when the most recent partition's .d changes, and the earlier partitions read correctly whatever their own .d says:

q)select from trade
date       id price
-------------------
2026.01.01 1  1
2026.01.01 2  2
2026.01.02 1  1
2026.01.02 2  2

Delete the old names only after every .d has been rewritten and every running process has reloaded.

Reorder columns

Only .d changes, so a single set does it atomically:

q)`:db/trade/.d set `price`id
`:db/trade/.d
q)\l db
q)trade
price id
--------
1     1
2     2
3     3

The partitions of a partitioned database don't have to agree on column order. Queries return the columns in the most recent partition's order, so you can rewrite the partitions in any order.

Rename a table

A splayed table is a single directory, so one mv renames it atomically, provided nothing already has the new name (see replacing a table that already exists). To a process that is already running, though, the move removes the old name, which is the deletion phase of a live change. Its queries on the old name fail, and it doesn't see the new name until it reloads.

A partitioned table takes one move per partition, and no order is safe. Rename the most recent partition first and the new name appears with gaps in it, while the old name disappears:

q)tables[]
`s#`quote`trades
q)select from trades
'./2026.01.01/trades/id. OS reports: No such file or directory
q)select from trade
'trade

Rename it last and the old name stays, with gaps in it:

q)tables[]
`s#`quote`trade
q)select from trade
'./2026.01.01/trade/id. OS reports: No such file or directory

If the table is published through symlinks, as in stage the table, then add the symlink, a rename only changes links, and it has no gap. Add the new name to every partition, finishing with the most recent. Then remove the old name, starting with the most recent:

ln -s '../versions$/2026.01.01/trade' pdb/2026.01.01/trades
ln -s '../versions$/2026.01.02/trade' pdb/2026.01.02/trades   # trades appears, complete
# reload every process, and move clients to trades
rm pdb/2026.01.02/trade                                       # trade disappears, cleanly
rm pdb/2026.01.01/trade

Between the two halves, both names work and read the same data. Removing the old links is the deletion phase, so wait until every running process has reloaded and nothing queries trade any more. With plain directories, the first half still works: a link trades -> trade in each partition gives the table a second name with no gap. Retiring the old name means moving the data, though, and that brings the gap back.

Delete a table

rm -r deletes one file at a time, in whatever order the filesystem lists them, so an interrupted delete leaves part of a table behind. If .d goes first, a splayed table stops being a table without any error, and q loads the directory as a dictionary:

q)\l db
q)tables[]
,`other
q)trade
id| 1 2 3
px| 1 2 3

In the most recent partition of a partitioned database it is worse, because the whole database stops loading:

q)\l pdb
'./2026.01.02/trade/.d. OS reports: No such file or directory

So first move the table out of the way in one mv, to a $ name in the database root that the loader skips. Then delete it at leisure:

mv db/trade 'db/trade.del$'
rm -r 'db/trade.del$'

In a partitioned database, start with the most recent partition. Once that partition has no trade, the table is gone from the database cleanly:

q)\l pdb
q)tables[]
,`quote
q)select from trade
'trade

Then remove trade from the earlier partitions. Each move has to stay on one filesystem to be a single rename(2).

On a live database, even the move breaks queries on trade in a process that loaded the database before it, because that process still looks for trade in each partition. Queries on other tables are unaffected. So stop clients querying the table before you move it.

A reload doesn't forget a table

\l defines the tables it finds, but it doesn't delete a variable whose table has gone from disk. After trade is deleted, a reloaded process still lists it:

q)\l .
q)tables[]
`s#`quote`trade
q)select from trade
'./2026.01.01/trade/id. OS reports: No such file or directory

Remove it with delete trade from `., or restart the process instead of reloading it. The same applies to the old name of a renamed table.

Tools that do this for you

The DBmaint module makes each of these changes across every partition in one call. Whether a reader sees a consistent database while it runs depends on the order in which it takes the steps, so check that order against the rules above before you run it on a database that is being read.

Deleting files on NFS

Every deletion on this page assumes that a deleted file is gone. On an NFS mount that isn't true while a process still has the file open, and an HDB in use always has files open.

An open file you delete becomes a .nfs file

An NFS server doesn't track which files its clients have open. If it removed a file that a process was still reading, that process would lose the file mid-read. So the client protects its own processes. When a process deletes a file that is open on the same client, the client renames the file instead, to a hidden name in the same directory such as .nfs0000000001a2b3c400000001, and deletes it only when the last process closes it. This is known as a silly rename. On Linux clients, renaming a file over one that is open, which is how set replaces a file, has the same effect on the file it replaces.

The protection covers only processes on the client that made the deletion. A process on another host that has the file open isn't protected: the file is really removed, and that process's next read fails with Stale file handle.

An HDB in use will meet it

q maps column files with mmap, and a mapped file counts as open. With the default deferred mapping, a query maps the columns it touches for as long as it runs. With .Q.MAP, every column stays mapped for the life of the session, and so does a vector in the database root. So when you change an NFS-backed HDB while it is serving queries, some of the files you delete will be open. This isn't a rare race. It is the normal case.

A .nfs file on its own does no harm to the loader. Its name starts with ., so the loader skips it in the database root, and inside a table directory .d doesn't list it. The damage comes from what the leftovers do to deletions:

  • rm -r can't finish. NFS renames files only, never directories, so a .nfs file stays in the directory that held the deleted file. A table directory that still holds one isn't empty, so rm -r fails to remove it and leaves the directory behind without its .d. In the most recent partition that stops the whole database loading, as described in delete a table. In an earlier partition, the table still loads, but a query over that date fails:

    q)select count i by date from trade
    'type
    
  • Readers on other hosts lose their files. A deletion protects only the processes on the host that makes it. An HDB on another host that is reading a deleted column gets Stale file handle in the middle of a query.

Move to a trash directory first

The safe way to delete a file or a directory tree on NFS is to move it into a dedicated trash directory first, and delete it from there. A move is a rename. The processes that have the files open keep them, on every host, and any .nfs file that a later deletion creates lands in the trash directory, where no loader looks.

The trash directory must be on the same NFS volume as the database. Only then is mv a rename. Between two filesystems mv copies the data and then deletes the original in place, which is exactly the deletion you were trying to avoid, and it leaves .nfs files where the table was. A directory in the database root whose name ends in $ meets both needs: it is on the same volume, and the loader skips it.

Give each move a fresh name, so that deleting the same table twice doesn't collide:

dest="pdb/trash\$/$(date +%Y%m%dT%H%M%S)/2026.01.02"
mkdir -p "$dest"
mv pdb/2026.01.02/trade "$dest/"     # out of the database, in one rename
rm -rf "pdb/trash\$/"*               # files still open become .nfs files here

The rm -rf reports errors for files that are still open. Those are expected, and they are now harmless. Each .nfs file goes away on its own when the last process on its host closes it: a query finishes, or a process that ran .Q.MAP reloads or exits.

If HDBs on other hosts serve the same database, delay the rm until every one of them has reloaded. The move is safe for them at once, but the deletion isn't, because a silly rename protects only the host that deletes.

The trash directory needs cleaning

The .nfs files disappear on their own, but the directories that held them don't. rm -rf gave up on them while they still had files in them, and nothing removes them later. Without cleanup, the trash directory fills with empty directory trees. Schedule a job that removes the empty ones:

find "pdb/trash\$" -mindepth 1 -type d -empty -delete

Don't have the job delete .nfs files. A .nfs file that still exists is still open. Deleting it from another host pulls the file away from the process that holds it, and that process then fails with Stale file handle.

Atomic is not durable

Everything above makes the visible switch — the rename(2) behind a mv or an ln -sfn — atomic: a concurrent reader never sees a half-written table. That says nothing about what survives a crash. The OS and the storage hardware are both free to reorder when writes actually reach stable storage, and nothing forces them to honor the order your script issued them in.

write returning success only means the kernel accepted the bytes into its page cache. Whether — and when — those bytes reach the disk is up to the kernel's writeback and the device's own write cache, neither of which promises to flush in program order. So the staged column files and the rename that publishes them are not necessarily written to stable storage in that order, even though your script performed the writes first and the rename after. If a hardware failure or power loss lands between them, the machine can come back up with the new symlink already durable, pointing at column files that are not — truncated, zero-filled, or simply absent. That is the same corrupt state described earlier, reached by a crash rather than a q error, and a reader has no way to tell the two apart.

fsync closes that gap by acting as a commit: it blocks until its target has actually reached stable storage, giving you a point after which the data is guaranteed to survive a crash. Two calls are needed, at two different points, because a file and a directory entry are different kinds of durable state:

  • Sync the staged data before flipping the link. Once set or upsert returns, fdatasync(2) — from the shell, sync -d — forces a file's bytes to disk without also waiting on metadata like its mtime, which nothing here depends on.
  • Sync the directory after flipping the link. A rename or ln is a write to the directory's metadata, not to any file's data, so fdatasync on the file doesn't cover it — you need a full fsync(2) (plain sync from the shell) on the directory that holds the new entry.
# after `set`/`upsert` into the staging area has returned
sync -d 'pdb/versions$/2026.01.02/quote'/*

# only now publish it...
ln -s '../versions$/2026.01.02/quote' pdb/2026.01.02/quote

# ...and make the publish itself durable
sync pdb/2026.01.02

Skip the first sync and a crash can leave the link durable while the data behind it is not. Skip the second and a crash can silently undo the publish — the data is safe on disk, but the directory entry pointing at it never was, so the reboot sees the old version again.

fsync only helps if the hardware keeps its word

This guarantee runs only as deep as the storage stack's willingness to honor it. A device with a volatile write cache that reports completion before data is actually persisted — common on consumer SSDs and some virtualized or network-backed disks — can make fsync return successfully for data that a power loss then still erases. There is no software fix for that from q's side; it is a property of the disk you chose.

q has no built-in fsync

There's no keyword for this — call it from the OS, as above, before performing the symlink flip described earlier in this page. How aggressively to sync is a real trade-off: syncing every column file adds latency to every write-down, so weigh it against how much a given version can afford to lose — the final publish of a partition matters more than an intermediate scratch write into versions$ that a rebuild would redo anyway.

Let the DB Service do this for you

Making database operations robust takes a lot of work, and it is easy to get wrong. Each section on this page adds rules that a hand-written procedure has to follow:

  • Stage every write and publish it in one step.
  • Publish a committed row count for a table that is queried while it is appended to.
  • Sort a copy of a table, never the table itself.
  • Order each schema change so that every intermediate state can be loaded.
  • Coordinate reloads with every running process before you delete anything.
  • On NFS, move files to a trash directory before you delete them, and clean the trash.
  • fsync the data and the directories in the right order.

Most of these rules only matter when something fails at the wrong moment, so a procedure that breaks one of them can pass every test you give it and still corrupt a production database.

If you need a robust solution, use the DB Service instead of building one. Its Storage Manager handles persistence for you. It writes data down from memory to disk while queries keep running, and it manages tables, deletions, and storage tiers without exposing the intermediate states described on this page. Use the techniques on this page when you have to maintain a database yourself.

Next steps