Database: tables in the filesystem¶
Roughly speaking, kdb+ is what happens when q tables are persisted and then mapped back into memory for operations.
— Jeffry A. Borror, Q for Mortals
Tables are first-class entities in q. Large q tables can be held in memory, but memory is finite and every process eventually terminates.
Sooner or later we need to persist tables in the filesystem. We will also need to perform operations on tables that are too large to hold in memory.
Roughly speaking, q tables and columns are represented in the filesystem as eponymous directories and binary files.
How you serialize a table depends on its size and how you need to use it.
serialization | representation | best where |
---|---|---|
object | single binary file | small and most queries use most columns |
splayed table | directory of column files | up to 100 million rows |
partitioned table | table partitioned by e.g. date, with a splayed table for each date | more than 100 million records; or growing steadily |
segmented database | partitioned tables distributed across disks | tables larger than disks; or you need to parallelize access |
Object¶
Q will serialize and file any object as a single binary file – the simplest way to persist a table.
A database with tables trades
and quotes
, and a sym list:
db/
├── quotes
├── sym
└── trades
By specifying the extension (e.g. CSV, XLS) you can also export the table in another format.
If most queries on a table do not need all the columns for each query consider splaying it.
Splayed table¶
A table is splayed by storing each of its columns as a single file. The table is represented by a directory.
db/
├── quotes/
| ├── time
| ├── sym
| └── price
└── trades/
├── time
├── sym
├── price
└── vol
With a splayed table, a query deserializes into memory only files for the column/s it requires.
If the table either
- grows
- holds more than 100 million records
- has columns that exceed the maximum size of a vector in memory
consider partitioning it.
Partitioned table¶
The records of a partitioned table are divided in its root directory between multiple partition directories. The table is partitioned by the values of a single column. Each partition contains records that have the same value in the partitioning column. With timeseries data, this is most commonly a date or time.
db/
├── 2020.10.03/
│ ├── quotes/
│ │ ├── price
│ │ ├── sym
│ │ └── time
│ └── trades/
│ ├── price
│ ├── sym
│ ├── time
│ └── vol
├── 2020.10.05/
│ ├── quotes/
│ │ ├── price
│ │ ├── sym
│ │ └── time
│ └── trades/
│ ├── price
│ ├── sym
│ ├── time
│ └── vol
└── sym
The partition directory is named for its partition value and contains a splayed table with just the records that have that value.
If either
- your table exceeds the size of your storage
- you need to parallelize access to it
- you want to partition it by a datatype that is not integer-based, e.g. a symbol
consider segmenting it across multiple storage devices.
Segmented database¶
The root directory of a segmented database contains only two files:
par.txt
: a text file listing the paths to the segments- the sym file for enumerated symbol columns
Segments are stored outside the root, usually on various volumes. Each segment contains a partitioned table.
DISK 0 DISK 1 DISK 2
db/ db/ db/
├── par.txt ├── 2020.10.03/ ├── 2020.10.04/
└── sym │ ├── quotes/ │ ├── quotes/
│ │ ├── .d │ │ ├── .d
│ │ ├── price │ │ ├── price
│ │ ├── sym │ │ ├── sym
│ │ └── time │ │ └── time
│ └── trades/ │ └── trades/
│ ├── .d │ ├── .d
│ ├── price │ ├── price
│ ├── sym │ ├── sym
│ ├── time │ ├── time
│ └── vol │ └── vol
├── 2020.10.05/ ├── 2020.10.06/
│ ├── quotes/ │ ├── quotes/
.. ..
Dividing the table between storage devices lets you
- store very large tables
- parallelize queries
- optimize updates
Queries on serialized tables¶
Deserialization and reserialization is implicit in qSQL queries.
q)select city,pop,country.code from `:linked/cities
city pop code
----------------------
Tokyo 37435191 81
Delhi 29399141 91
Shanghai 26317104 86
q)`:linked/countries upsert (`Brazil;`$"South America";55)
`:linked/countries
q)get`:linked/countries
country| cont code
-------| ------------------
China | Asia 86
India | Asia 91
Japan | Asia 81
Brazil | South America 55
Operations on serialized tables¶
Some operators and keywords work on some serialized tables.
For example, cols
works on tables in memory or mapped to memory, and on filesymbols for splayed tables but not tables serialized as an object.
Serialize as an object
Q for Mortals
§14. Introduction to kdb+