Skip to content

Managing sym files

This page explains the sym file that every splayed or partitioned KDB-X database depends on: how to inspect it, how to move tables between databases without corrupting them, how to compact it safely, and how to stop it growing out of hand.

A symbol column on disk does not store text. Each distinct symbol is stored once in the database's sym file, and the column stores an integer index into it. That is what makes symbol columns compact and comparisons fast — and it is also why the sym file is the single most important file in the database. Lose it and every symbol column becomes meaningless integers.

flowchart LR
    %% Styles
    classDef dataNode fill:#1565C0,stroke:#0D47A1,color:#fff,stroke-width:2px;
    classDef fileNode fill:#E65100,stroke:#BF360C,color:#fff,stroke-width:2px;
    classDef colNode fill:#2E7D32,stroke:#1B5E20,color:#fff,stroke-width:2px;

    subgraph Input [Input Symbols]
        direction TB
        I1("#96;AAPL"):::dataNode
        I2("#96;GOOG"):::dataNode
        I3("#96;AAPL"):::dataNode
    end

    subgraph SymMap [Sym File]
        direction TB
        Sym("<div style='text-align: left'>0: #96;AAPL<br/>1: #96;GOOG</div>"):::fileNode
    end

    subgraph Result [Enumerated Column]
        direction TB
        Out("<div style='text-align: center'>0<br/>1<br/>0</div>"):::colNode
    end

    %% Connections
    I1 -- "Map" ---> Sym
    I2 -- "Map" ---> Sym
    I3 -- "Reuse" ---> Sym
    Sym -- " Indices " ----> Out

    linkStyle default stroke:#666,stroke-width:2px,fill:none;

Figure 1: Symbol enumeration

.Q.en is what performs this, and dsave and .Q.dpft call it for you. See enumerations for the language mechanics, and Q for Mortals for more background.

The sample database

The examples use a database generated with the Datagen module — a partitioned trade table and a splayed daily table, sharing one sym file at the root:

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

Inspect the enumeration

Loading the database makes the sym file a variable, sym, in the root namespace:

q)\l hdb
q)count sym
122
q)6#sym
`AAPL`AGEN`AIG`AMD`AMTM`AMZN

The column on disk has type 20h — an enumeration, not a symbol vector:

q)type get `:./2026.04.06/trade/sym
20h

Cast it to an integer to see what is actually stored. Sampling scattered positions shows each symbol beside its index in sym:

q)c:get `:./2026.04.06/trade/sym
q)idx:0 8000 16000 24000 32000 40000
q)([] position:idx; sym:c idx; index:`int$c idx)
position sym  index
-------------------
0        AAPL 0
8000     BAC  8
16000    DERM 16
24000    INTC 24
32000    NVDA 32
40000    SOFI 40
What a lost sym file looks like

Deleting the sym variable simulates a missing or corrupted file. The column still reads — but as bare indices, with no way to recover what they meant:

q)delete sym from `.
`.
q)6#get `:./2026.04.06/trade/sym
`sym!0 0 0 0 0 0

Do this only to understand the dependency. In production the sym file is the one file you cannot afford to lose.

Back up the sym file

The sym file is the only link between your data and its meaning. If it is deleted or corrupted, every integer in every symbol column becomes unreadable and the dataset is effectively destroyed. Back it up, and back it up together with the data it describes.

Migrate tables between databases

Two databases built independently have different sym files: AAPL might be index 0 in one and index 37 in the other. Copying a column file from one to the other therefore silently changes what the data means — the same failure mode as reordering a foreign key's parent.

The safe route is to de-enumerate to symbols and re-enumerate against the destination. Interprocess communication (IPC) does the first half for free: q converts enumerations to symbols when it sends them.

Start the source database as a server:

q hdb -p 5401

Then from another process, query it and observe that the enumeration doesn't survive the wire — 20h on the server, 11h on arrival:

q)h:hopen `::5401
q)h "type get `:./2026.04.06/trade/sym"
20h
q)t:h "select from daily"
q)type t`sym
11h
q)3#t`sym
`AAPL`AGEN`AIG

Because the table holds plain symbols, .Q.en maps them onto the destination's own sym file, creating it if needed:

q)`:dst/daily/ set .Q.en[`:dst] t
`:dst/daily/
q)6#get `:dst/sym
`AAPL`AGEN`AIG`AMD`AMTM`AMZN

The same applies to writing into a partition of the destination — enumerate against the destination root, never the source's.

Enumerate against a named domain

.Q.en always uses the name sym. Where you want a different domain name, .Q.ens takes it as a third argument and does the rest — no manual distinct or cast required:

q)`:hdb_manual/trade/ set .Q.ens[`:hdb_manual; ([] sym:`AAPL`GOOG`MSFT`AAPL; price:4?100.0); `manualSym]
`:hdb_manual/trade/

The domain file is written at the database root under the name you gave:

q)key `:hdb_manual
`s#`manualSym`trade
q)get `:hdb_manual/manualSym
`AAPL`GOOG`MSFT

This is the tidy route to the per-table sym files discussed under keep sym files healthy, since each table can enumerate against a domain of its own.

Compact a sym file

A sym file only ever grows. Symbols enter it and are never removed, so over time it accumulates entries nothing references any more — delisted instruments, retired venue codes, columns that have since been dropped.

The sample database shows this immediately. Its sym file holds 122 entries, but only 69 of them are referenced by any enumerated column:

q)count sym
122
q)used:distinct raze {[d] p:hsym `$"./",(string d),"/trade";
    raze {c:get x; $[20h=type c; value distinct c; ()]} each p .Q.dd/:key[p] except `.d} each 2026.04.06 2026.04.07
q)count used
69
q)count sym except used
53

Compaction rewrites the sym file with only the symbols still in use, and rewrites every enumerated column to match the new indexes. It reads and writes every symbol column in the database, so treat it as scheduled maintenance rather than something to run casually.

The algorithm is:

  1. Back up the current sym file, by renaming it — say to zym.
  2. Reset the sym file: create a new, empty sym.
  3. Find every enumerated column file in the database.
  4. Re-enumerate each one: read it against the old domain, write it against the new one.

Step 3 is where compaction goes wrong

Every enumerated column must be re-enumerated. A column that is missed keeps indexes into the old domain while the file it reads has been replaced — so it silently returns the wrong symbols, with no error at all.

Scanning only the partition directories is the mistake, because it misses splayed tables in the database root. In the sample database that is daily: compacting the partitions alone leaves daily's first row reading S where it used to read AAPL.

The script below scans partitions and root-level splayed tables. If your database holds enumerated data anywhere else, extend symCols to find it, and verify before you trust the result.

The compaction script

The paths are normalized with getFSym, the filter function that accepts a database root as a string, a symbol, or a file symbol and hands the body a file symbol either way. Declaring it as the parameter pattern hdb:getFSym lets callers choose the form, so the body never has to check:

/ normalize a string, symbol or file symbol to a file symbol
getFSym: {hsym $[10h ~ type x; `$; ] x}

/ @desc Locate every enumerated column file in the database: the columns of
/       every table in every partition, plus those of any splayed table in the
/       database root. Missing one would corrupt it, so the scan covers both.
/ @return {symbol[]} file handles of the column files whose type is 20h
symCols: {[]
  entries: string key `:.;
  isTable: {`.d in key hsym `$x};
  parts: entries where entries like "????.??.??";
  ptabs: raze {p:x; p,/:"/",/:string key hsym `$p} each parts;
  roots: entries where isTable each entries;
  files: raze {t:hsym `$x; t .Q.dd/:key[t] except `.d} each ptabs,roots;
  files where 20h=type each get each files}

/ @desc Compact a database's sym file: rewrite it holding only the symbols still
/       referenced, and re-enumerate every symbol column against the new domain.
/       The old domain is kept as zym, so the change can be reversed.
/ @param hdb {string|symbol|hsym} database root
/ @return {::}
compactSym: {[hdb: getFSym]
  cwd: system"cd";
  system"cd ", 1_string hdb;
  files: symCols[];
  -1 "found ", string[count files]," enumerated column file(s)";
  system "mv sym zym";
  `:sym set `symbol$();
  {[f: `s]
    `sym set get `:zym;
    s:get f; a:attr s; s:value s;
    `sym set get `:sym;
    f set a#.Q.en [`:.; ([]s:s)]`s;
    -1 "re-enumerated ",string f;
   } each files;
  system "cd ", cwd;
  -1 "compaction complete"; }

Run it against a copy first. Any of the three path forms works:

q)\l compact.q
q)compactSym "hdb"
found 5 enumerated column file(s)
re-enumerated :2026.04.06/trade/ex
re-enumerated :2026.04.06/trade/sym
re-enumerated :2026.04.07/trade/ex
re-enumerated :2026.04.07/trade/sym
re-enumerated :daily/sym
compaction complete

Note that it found five files: both enumerated columns of trade in each partition — sym and ex — plus daily/sym in the root. Missing any of them corrupts that column.

Verify the result

Confirm that the sym file has shrunk and that every table reads exactly as it did before:

q)\l hdb
q)count sym
69
q)3#select sym, price from trade where date=2026.04.06
sym  price
-----------
AAPL 255.56
AAPL 255.44
AAPL 255.51
q)3#select sym, close from daily
sym  close
-----------
AAPL 276.39
AGEN 4.29
AIG  84.58

The backup zym is still there. Keep it until you are satisfied, then remove it — while it remains, you can restore the original state by renaming it back.

Keep sym files healthy

Avoid sym bloat

Sym bloat is a sym file grown to millions of entries, most of them rarely or never referenced. It costs on two fronts: enumerating a new batch means checking it against a larger domain, and the file is read onto the heap every time the database is loaded rather than being memory-mapped, so every process pays for it in resident memory.

Two habits prevent it:

  • Do not enumerate high-cardinality data. A column of order IDs or request GUIDs has no repetition to exploit, so enumeration buys nothing and the domain grows without limit. Use strings or GUIDs instead.
  • Give tables their own domains where they don't need to share, using .Q.ens.

Never compress a sym file

You can compress a sym file, and the database still loads and queries. But the domain can then no longer be extended, so the next .Q.en that meets a new symbol fails:

q).Q.en[`:.; ([] sym:`BRANDNEW; v:1)]
'no append to zipped enums: ./sym

That breaks the next write-down. When compressing a database, exclude the sym file.

Reduce risk with several sym files

One global sym file is a single point of failure: corrupt it and every table in the database becomes unreadable. Giving each table — or each small group of related tables — its own domain limits the damage to that table, reduces contention when several processes enumerate at once, and makes individual tables safe to move between databases.

The cost is that a symbol shared by two tables is stored twice, and that queries joining across domains must de-enumerate first. Decide per database which matters more.

Next steps