How to Work with Table Dictionaries

This page explains what table dictionaries are, when to use them, and how to create, query, and persist them in q.

A table dictionary is a dictionary whose values are tables. The key is a unique list, and each value is a table. When you sort the underlying tables, the structure becomes a sorted table dictionary.

Trade-offs

In q, when you insert new rows into a table beyond its reserved memory capacity, q allocates a new memory block and copies the existing data into it. For large tables this reallocation can take time, during which all other operations – including incoming queries – block. A table dictionary partitions the data into several smaller tables rather than one large one. Smaller tables are faster to reallocate, so queries block more frequently but for significantly shorter periods each time.

Table dictionaries are also faster to persist to a partition when the key column needs the parted attribute: you can append each individual table directly to disk, whereas a normal table must first be sorted, which can be expensive for large datasets.

Note that table dictionaries are not q tables, so you cannot apply select, update, delete, and exec statements to them directly.

Create a table dictionary from a normal table

The examples below use the Datagen module for demonstration.

q)([getInMemoryTables; buildPersistedDB]): use `kx.datagen.capmkts
q)(trade;;): getInMemoryTables ([tbls: `trade])
q)trade
sym    time                 price  size stop cond ex
----------------------------------------------------
AMZN   0D09:30:00.016189096 222    38   0    E    V
META   0D09:30:00.117850372 722.8  68   0    V    W
TZOO   0D09:30:00.251826992 9.88   62   1    P    T
BAC    0D09:30:00.305215733 50.84  74   0    K    I
PRU    0D09:30:00.349035419 103.02 51   0    8    L
META   0D09:30:00.635974705 723    18   0    6    S
ARBE.W 0D09:30:00.820173637 0.29   83   0    N    C

You can build a sorted table dictionary from a sorted table by iterating over the key values:

q)td: syms!{[x] update time:`s#time from delete sym from select from trade where sym=x}each syms: `u#exec asc distinct sym from trade
q)key td
`u#`AAPL`AGEN`AIG`AMD`AMTM`AMZN`ARBE.W`ASND`BAC`BIIB`BSBK`CAMP`CCL`CPRT`CUZ`DELL`DERM`DOW`EMA`FMTM`GOGO`GOOG`HP..
q)td`AAPL
time                 price  size stop cond ex
---------------------------------------------
0D09:30:42.241036851 255.45 48   0    M    J
0D09:33:13.713964710 255.82 35   0    9    N
0D09:33:48.812586607 255.85 23   0    8    M
0D09:34:10.713642862 255.8  22   0    B    M
0D09:45:32.522056655 256.76 84   0    X    M
q)sum count each td
2542

Retaining the key column inside each table simplifies subsequent queries at the cost of additional memory.

The general-purpose converter below uses the functional forms of select and update. It takes a table t, a key column keycol, and a time column timecol:

getTableDict: {[t; keycol:`s; timecol:`s]
    ks: `u#?asc distinct t keycols;
    ks!{[t;keycol; timecol; k]
        ![; (); 0b; (enlist timecol)!enlist (`s#; timecol)]
            ![; (); 0b; enlist keycol]
                ?[t; enlist (=; keycol; enlist k); 0b; ()]
        }[t; keycol; timecol] each ks
    }

Convert a table dictionary back into a normal q table when you need to run standard queries against the data. The result is a flat table with rows grouped by key:

q)normalize: {[td] cnt: count each td; ([] sym: where cnt) ,' raze td}

Applying normalize to a sorted table dictionary does not produce a sorted table, but you can restore the original ordering with xasc:

q)trade ~ `time xasc normalize getTableDict[;`sym;`time] trade
1b

The normalized table groups rows by key, so you can apply the parted attribute where it benefits downstream queries:

q)meta update `p#sym from normalize td
c    | t f a
-----| -----
sym  | s   p
time | n
price| f
size | j
stop | b
cond | c
ex   | s

Query a table dictionary

You cannot apply select statements to table dictionaries directly. Rewriting a query that targets a single key is straightforward:

Table

select time, price from trade where sym=`AAPL

select avg price by ex from trade where sym=`AAPL

Table Dictionary

select time, price from td `AAPL

select avg price by ex from td `AAPL

If the query restricts on several keys, iterate over the selected subset:

Table

select sym, time, price from trade where sym in `AAPL`CPRT`GOOG

select avg price by sym from trade where sym in `AAPL`CPRT`GOOG

Table Dictionary

normalize {select time, price from x} each `AAPL`CPRT`GOOG#td

normalize {select avg price from x} each `AAPL`CPRT`GOOG#td

Because the individual tables often vary in size, per-key query times can differ significantly. Using peach in place of each typically speeds up these queries by parallelizing the work across threads.

Aggregating across keys requires either an upfront conversion back to a normal table or a manual map-reduce:

Table

select medSize: med size by ex from trade

select avgPrice: avg price by ex from trade

Table Dictionary

select medSize: med size by ex from raze {select ex, size from x} peach td  / upfront conversion

select avgPrice: (sum s) % sum cnt by ex from normalize {0!select s: sum price, cnt: count price by ex from x} each td  / manual map-reduce

Run per-key and last-row queries

Table

select from trade where i=(last;i) fby sym

Table Dictionary

normalize {-1#x} each td

Save to disk

Persisting a table dictionary is efficient when the key column needs the parted (`p) attribute on disk – the typical case. Iterate over the individual tables and upsert each one to the partition directory; you need no upfront sort.

saveTD: {[d:`s; p:`d; f:`s; tn:`s; td]
    part: .Q.par[d; p; tn];
    (key td) {[d; part; f; k; t]
        .Q.dd[part;`] upsert .Q.en[d] f xcols ![t; (); 0b; (enlist f)!enlist enlist k]
        }[d; part; f]' td;
    @[part; f; `p#]
  }

For example, to save the td table dictionary to a partition:

q)saveTD[`:/tmp/testdb; 2026.05.16; `sym; `trade; td]