Splayed tables¶
Medium-sized tables (up to 100 million rows) are best stored on disk splayed: each column is stored as a separate file, rather than using a single file for the whole table.
trade/
├── .d
├── cond
├── ex
├── price
├── size
├── stop
├── sym
└── time
The hidden file .d lists the columns in the order they appear in the table.
Tables that have many columns are good candidates for splaying, as most queries access only a small subset of those columns.
To save a table splayed, use set with the file handle in the left operand having a trailing slash to indicate saving to a directory.
q)`:dirname/ set table
`:dirname/
The table must be
- fully enumerated
- simple, not keyed
Take a table of trades from the Datagen module. .Q.en satisfies the first requirement — the next section covers what it does:
q)([getInMemoryTables]): use `kx.datagen.capmkts
q)(trade; ; ): getInMemoryTables ([tbls: `trade])
q)3#trade
sym time price size stop cond ex
--------------------------------------------------
ASND 0D09:30:00.090295466 203.16 35 0 7 S
TSLA 0D09:30:00.174023111 463.29 63 0 P P
BSBK 0D09:30:00.337977158 9.11 86 0 Q Y
q)`:trade/ set .Q.en[`:.] trade
`:trade/
One file per column, plus the hidden .d:
$ ls -a trade
. .. .d cond ex price size stop sym time
rsave and rload¶
rsave is save for a splayed table: instead of one file it writes a directory holding one file per column. Like save, it takes the name of a global and derives the directory name from it, so it gives you no say in where the directory lands.
q)t: ([] a: 1 2 3; b: 4 5 6)
q)rsave `t
`:t/
Directory t then contains a file per column, plus the .d file:
$ ls -a t
. .. .d a b
rload reads such a directory back as a splayed table, using .d to recover the columns — see loading a directory of files.
Because .d is itself a serialized symbol list, get reads it directly. This is a convenient way to check the column order without loading the table:
q)get hsym `$"t/.d"
`a`b
dsave¶
dsave splays tables like set and rsave, but does the surrounding work too. It enumerates symbol columns against the database's sym file and applies the parted attribute to the first column of each table. It is roughly .Q.en plus set, or .Q.dpft, in one call.
Because dsave parts on the first column without sorting by it, sort before saving. Here xasc is applied to both table names with the each-both iterator:
q)([getInMemoryTables]): use `kx.datagen.capmkts
q)(trade; quote; ; ): getInMemoryTables ([tbls: `trade`quote])
q)meta trade
c | t f a
-----| -----
sym | s g
time | n s
price| f
size | j
stop | b
cond | c
ex | s
q)`:/tmp/db1 dsave `sym xasc'`trade`quote
`trade`quote
The result is a database directory with one subdirectory per table and a shared sym file at the root:
/tmp/db1
├── quote
│ ├── .d
│ ├── asize
│ ├── ask
│ ├── bid
│ ├── bsize
│ ├── ex
│ ├── mode
│ ├── sym
│ └── time
├── sym
└── trade
├── .d
├── cond
├── ex
├── price
├── size
├── stop
├── sym
└── time
Loading it restores both tables, with p now on sym:
q)\l /tmp/db1
q)meta trade
c | t f a
-----| -----
sym | s p
time | n
price| f
size | j
stop | b
cond | c
ex | s
q)meta quote
c | t f a
-----| -----
sym | s p
time | n
bid | f
ask | f
bsize| j
asize| j
mode | c
ex | s
Two attributes changed. sym was grouped in memory and is parted on disk, which is what dsave applied. time lost its sorted attribute, because the rows were reordered by sym and are no longer in time order within the table — only within each symbol.
dsave also writes straight into a partition — see creating partitioned tables.
Nested columns¶
Nested columns are those where the column isn't a vector. In a splayed table, a nested column is represented in the filesystem by at least two files: one bears the column name, while the second file uses the same name suffixed with a #.
If the nested column contains symbols, a third file is present with a ## suffix. This file is used to enumerate the symbols referenced in the column.
The type of the column, when read back in, is anymap (type 77h), used for memory-mapped nested columns. The elements of such a column need not share a type, so nested and mixed columns are both fine; the storage is described under mapped lists.
A common question in designing a table schema is whether to represent text as strings (nested) or symbols (vector). Where many values are repeated, as in stock-exchange codes, symbols have important advantages:
- atomic semantics in code
- compact storage
- fast execution
With fewer repeated values, these advantages dwindle, but the penalty of a bloated sym list remains. Fields such as comments or notes should always be strings.
Enumerating symbol columns¶
If a table contains columns of type symbol where the column is not enumerated, trying to save it splayed results in a type error.
The full trade table has two symbol columns, sym and ex, so saving it as it stands fails:
q)3#trade
sym time price size stop cond ex
--------------------------------------------------
ASND 0D09:30:00.090295466 203.16 35 0 7 S
TSLA 0D09:30:00.174023111 463.29 63 0 P P
BSBK 0D09:30:00.337977158 9.11 86 0 Q Y
q)`:trade/ set trade
'type
Tables splayed across a directory must be fully enumerated and not keyed.
The solution is to enumerate symbol columns before saving the table splayed. This is done with the function .Q.en.
q)3#.Q.en[`:dir] trade
sym time price size stop cond ex
--------------------------------------------------
ASND 0D09:30:00.090295466 203.16 35 0 7 S
TSLA 0D09:30:00.174023111 463.29 63 0 P P
BSBK 0D09:30:00.337977158 9.11 86 0 Q Y
This assigns to the variable sym the list of unique symbols in the table:
q)5#sym
`ASND`TSLA`BSBK`EMA`AGEN
q)count sym
69
One domain covers every symbol column, not one per column — the 69 entries are the distinct values of sym and ex together:
q)count distinct trade`sym
51
q)count distinct trade`ex
19
It also creates the directory dir with a file in it, named sym, with the same contents:
q)\ls dir
"sym"
q)5#value `:dir/sym
`ASND`TSLA`BSBK`EMA`AGEN
Finally, it returns a table in which both symbol columns are enumerated — type 20h rather than 11h:
q)trenum: .Q.en[`:dir] trade
q)type each trenum`sym`ex
20 20h
q)5#trenum`sym
`sym$`ASND`TSLA`BSBK`EMA`AGEN
The original column is plain symbols, unchanged:
q)5#trade`sym
`ASND`TSLA`BSBK`EMA`AGEN
The enumerated table can now be saved splayed.
q)`:dir/trade/ set trenum
`:dir/trade/
The columns are saved separately, one per file:
q)\ls -a dir/trade
,"."
".."
".d"
"cond"
"ex"
"price"
"size"
"stop"
"sym"
"time"
This can also be done in a single step, without saving the enumerated table into a variable:
q)`:dir/trade/ set .Q.en[`:dir] trade
`:dir/trade/
Notice that the sym list is stored separately from the table. If the symbols are common to several tables it might be convenient to write the sym list in their common parent directory.
db/
├── sym
├── quote/
└── trade/
Since table trade has a column sym, represented by file db/trade/sym, one place you cannot write the sym list is in db/trade.
Loading splayed tables¶
There are various ways to load or read a splayed table.
- Start q with the directory:
q trade
q).z.f
`trade
- Load the directory:
q)\l trade
`trade
q)load`trade
`trade
Loading the table in fact maps it to memory. None of it is written into memory by the load itself. Whether the columns are mapped up front or as each query touches them depends on a trailing slash on the handle — see immediate and deferred memory mapping.
Warning
Going into the directory of the splayed table and loading from there with \l . not only fails to work, it also deletes all variables in the main namespace in the current session!
- Get the table:
q)3#get`:trade
sym time price size stop cond ex
-------------------------------------------------
0 0D09:30:00.090295466 203.16 35 0 7 51
1 0D09:30:00.174023111 463.29 63 0 P 52
2 0D09:30:00.337977158 9.11 86 0 Q 53
Symbol columns in a splayed table are stored as enumerations of a list sym, stored separately from the table.
Retrieving the table without the sym list leaves its symbol columns as bare enumerations.
That is what happened above: both sym and ex came back as bare integers, because they index a domain this session does not have. Loading the list fixes them, and the two ranges — 0 1 2 and 51 52 53 — show that both columns index the same one:
q)load `:sym
`sym
q)3#get`:trade
sym time price size stop cond ex
--------------------------------------------------
ASND 0D09:30:00.090295466 203.16 35 0 7 S
TSLA 0D09:30:00.174023111 463.29 63 0 P P
BSBK 0D09:30:00.337977158 9.11 86 0 Q Y
Loading the directory that holds both, rather than the table alone, brings the list along and avoids the problem:
q)\l .
q)\v
`s#`sym`trade
q)3#trade
sym time price size stop cond ex
--------------------------------------------------
ASND 0D09:30:00.090295466 203.16 35 0 7 S
TSLA 0D09:30:00.174023111 463.29 63 0 P P
BSBK 0D09:30:00.337977158 9.11 86 0 Q Y
When q loads a directory it loads everything in it that represents a KDB-X object. So it is convenient to store a shared sym list in the root directory of a database.
db/
├── sym
├── quote/
└── trade/
q db
q)5#sym
`ASND`TSLA`BSBK`EMA`AGEN
q)3#trade
sym time price size stop cond ex
--------------------------------------------------
ASND 0D09:30:00.090295466 203.16 35 0 7 S
TSLA 0D09:30:00.174023111 463.29 63 0 P P
BSBK 0D09:30:00.337977158 9.11 86 0 Q Y
Upserting into splayed tables¶
Sometimes you only want to grow the table by a few rows at a time. For this, use the built-in upsert keyword — but with the same requirements as creating a new table from scratch, so you still need to add the potential symbol columns to the enumeration:
q)trn:([] sym:`AAPL`MSFT; time:2#0D16:00:00.000000000; price:189.5 412.25; size:100 250; stop:10b; cond:"XX"; ex:`N`Q)
q)`:db/trade/ upsert .Q.en[`:db;trn]
`:db/trade/
After a reload the rows are there, with the two new symbols added to the shared domain:
q)count trade
2544
q)-2#select sym,time,price,size,ex from trade
sym time price size ex
----------------------------------------
AAPL 0D16:00:00.000000000 189.5 100 N
MSFT 0D16:00:00.000000000 412.25 250 Q
Parallelism¶
A single set writes the columns one after another. Writing is not among the multithreaded primitives — the text and binary loaders read in parallel, but set does not write in parallel — so starting q with secondary threads does nothing for it.
Because a splayed table is a directory of independent files, you can do the work yourself: write each column in its own thread with peach, then write the .d file. setPar (defined below) passes its left argument through to set one file at a time, so a bare directory handle works, and so do compression parameters — given either as a triple or as a per-column dictionary.
/ @desc Write a table splayed, one column per secondary thread. The left
/ argument is handed to `set` per file, so it takes what `set` takes:
/ a directory handle, optionally followed by compression parameters.
/ @param d {hsym|list} `:dir, (`:dir;logicalBlockSize;algorithm;level), or (`:dir;dict)
/ @param t {table} an enumerated, unkeyed table — not checked
/ @return {hsym} the directory handle
setPar:{[d;t]
if[1=count d; d: d, 3#0]; / no compression
.[set;] peach flip (d {@[x;0;.Q.dd[;y]]}/: cols t; value flip t); / the columns, in parallel
.Q.dd[first d; `.d] set cols t; / then .d
first d}
q)q: .Q.en[`:.] quote
q)setPar[`:qt; q]
`:qt
q)key `:qt
`s#`.d`asize`ask`bid`bsize`ex`mode`sym`time
The result is indistinguishable from set — the same .d, and byte-identical column files:
q)q~select from `:qt/
1b
An illustration, not a replacement for set
Writing the column files directly skips the checks set makes on the table as a whole. Most importantly it does not enforce enumeration: set refuses a table whose symbol columns are not enumerated, while setPar writes it without complaint, leaving a directory whose symbol column is a plain vector rather than an enumeration.
It also leaves .d uncompressed where set would compress it, and validates nothing. Treat it as a demonstration of the technique to adapt, not a utility to depend on.
Why it helps most when compressing¶
Writing a column is two kinds of work: q prepares the bytes, then the operating system moves them to disk. What parallelism is worth depends entirely on the balance between the two.
Uncompressed, there is barely any preparation to speak of. A vector's on-disk form is its in-memory form plus a 16-byte header, so serializing it costs almost no CPU, and the write is bound by the storage from beginning to end. Spreading the columns across threads does not make the device any faster, so expect little from setPar here.
Compression changes the balance, because every column has to be compressed before any of its bytes can be written, and that is real CPU work. Done column by column the two phases alternate and each side idles in turn — the disk waits while a column is compressed, the CPU waits while it is flushed. Overlapping the columns keeps both busy, which is what peach buys. Encryption tilts it the same way.
Passing compression parameters compresses the columns in parallel, to the same bytes set would have written:
q)setPar[(`:qtz;17;2;9); q]
`:qtz
q)-21! .Q.dd[`:qtz;`bid]
compressedLength | 78444
uncompressedLength| 203584
algorithm | 2i
logicalBlockSize | 17i
zipLevel | 9i
How much this approach gains depends on the number of columns, their width, the algorithm and level, and how many independent write paths the storage really has — so measure it on your own hardware rather than assuming a speedup.
Enumerate before the parallel write, not inside it
A secondary thread cannot amend a global, and .Q.en extends the global sym. Calling it inside peach fails:
q){.Q.en[`:.;([] s:x)]} peach (enlist each `a`b`c)
'noupdate: `. `sym
Enumerate the whole table first, on the main thread, as in the examples above — then the columns being written are already integers and the threads only write files.
A few further constraints:
- Parallelism in q is single-level. Spreading columns across threads means the work inside each column is not also parallelized; see peach vs implicit parallelism.
- More threads is not automatically faster. Threads writing to one device contend for it.
-
Compressing in parallel costs memory. Each thread compresses into a buffer of its own, so peak memory grows with the thread count where a serial
setneeds one buffer at a time. Writing uncompressed costs no more thanset, since raw bytes need no buffer.The rise is bounded, because compression works a logical block at a time rather than a column at a time: a thread holds roughly a block, not a whole column. Raising
logicalBlockSizetherefore raises the cost proportionally. Neither.Q.wnor\tsshows any of it — see memory in secondary threads.
Atomicity¶
Neither set nor upsert is atomic: a splayed table is a directory of separate files, and q writes them one at a time, so an interrupted write leaves a directory that is neither the old table nor the new one. A partial upsert is the dangerous case, because it leaves the columns at different lengths and reads back without an error.
See atomicity and integrity for what the two failure modes look like, the same problem at partition level, and the staging-and-symlink pattern that avoids both.
Changing the schema of splayed tables¶
Some keywords do not work with splayed tables, in particular those that would change the schema. You can still work around this limitation if needed by changing the underlying files that make up the table.
Removing a column from a table in memory takes a single expression:
q)3#delete stop from select sym,price,size,stop,cond from trade
sym price size cond
---------------------
ASND 203.16 35 7
TSLA 463.29 63 P
BSBK 9.11 86 Q
On a splayed table the same expression appears to work — and that is the trap. It does not fail, but neither does it change anything on disk:
.Q.lo is used instead of \l here because it loads without changing the working directory, which keeps the `:db/… handles below meaning the same thing throughout.
q).Q.lo[`:db;0;0]
q)delete stop from `trade
`trade
q)cols trade
`sym`time`price`size`cond`ex
q)value `:db/trade/.d
`sym`time`price`size`stop`cond`ex
The column is gone from the session and still present in the database. What happened is that the mapped table was materialized on the heap and the column dropped from that copy, so the edit also quietly gave up the mapping:
q).Q.s1 trade / before
"+`sym`time`price`size`stop`cond`ex!`:db/trade/+`s..."
q).Q.s1 trade / after
"+`sym`time`price`size`cond`ex!(`g#`sym$`ASND`TSLA`..."
A reload brings stop straight back. To change the schema on disk, edit the .d file, which is where a splayed table records its columns:
q)value `:db/trade/.d
`sym`time`price`size`stop`cond`ex
q).[`:db/trade/.d;();:;`sym`time`price`size`cond`ex]
`:db/trade/.d
For the change to take effect, reload:
q).Q.lo[`:db;0;0]
q)\v
`s#`quote`sym`trade
q)3#trade
sym time price size cond ex
---------------------------------------------
ASND 0D09:30:00.090295466 203.16 35 7 S
TSLA 0D09:30:00.174023111 463.29 63 P P
BSBK 0D09:30:00.337977158 9.11 86 Q Y
Notice that the file holding the stop column has not been deleted. It is just no longer used:
q)\ls db/trade
"cond"
"ex"
"price"
"size"
"stop"
"sym"
"time"
Adding a column is the same idea in reverse: write the column as a file in the table's directory, then extend .d.
q)@[`:db/trade;`seq;:;til count trade]
`:db/trade
q).[`:db/trade/.d;();,;`seq]
`:db/trade/.d
Reload to verify:
q).Q.lo[`:db;0;0]
q)3#trade
sym time price size cond ex seq
-------------------------------------------------
ASND 0D09:30:00.090295466 203.16 35 7 S 0
TSLA 0D09:30:00.174023111 463.29 63 P P 1
BSBK 0D09:30:00.337977158 9.11 86 Q Y 2
The new column must be as long as the table
Nothing checks it. A column file shorter or longer than the others produces the same broken table an interrupted write does, so derive the length from the table rather than hard-coding it.