Partitioning tables across directories¶
A partitioned table is a splayed table that is further decomposed by grouping records that have common values along a column of special type. The allowable special column types have underlying integer values: date, month, year, and long.
If a database has multiple partitioned tables, the directory structure contains the partitioning column at the top level (which means this must be uniform across the database); then each table has its own directory for each partition. These directories effectively each contain a splayed table. A sym file sits directly in the root directory of the database — by default shared by every partitioned table, though a table can instead be enumerated against, and read from, its own separate sym file.
db
├── 2026.04.10
│ ├── quote
│ │ ├── .d
│ │ ├── asize
│ │ ├── ask
│ │ ├── bid
│ │ ├── bsize
│ │ ├── ex
│ │ ├── mode
│ │ ├── sym
│ │ └── time
│ └── trade
│ ├── .d
│ ├── cond
│ ├── ex
│ ├── price
│ ├── size
│ ├── stop
│ ├── sym
│ └── time
├── 2026.04.11
│ ├── quote
..
└── sym
Partition data correctly: data for a particular date must reside in the partition for that date.
Creating partitioned tables¶
To create a partitioned table, you have to create the splayed tables in the directory system as above. To do that, you just need to see which part of your table goes to which partition, and to create that partition itself in the correct place.
Take a table of trades from the Datagen module, and split it into two days' worth:
q)([getInMemoryTables]): use `kx.datagen.capmkts
q)(trade; quote; ; ): getInMemoryTables ([tbls: `trade`quote])
q)parts: (0; count[trade] div 2) cut trade
q)count each parts
1271 1271
The only new helper you need is .Q.par, which constructs the path of a table within a partition. .Q.dd adds the trailing slash that tells set to splay:
q){[d;x] .Q.dd[.Q.par[`:db;d;`trade];`] set .Q.en[`:db] x}.' flip (2026.04.10 2026.04.11; parts)
`:db/2026.04.10/trade/`:db/2026.04.11/trade/
That has created all the necessary files in their correct locations — the splayed table for each date, and the shared sym file at the root.
Give .Q.par a root without a trailing slash
.Q.par[`:db/;2026.04.10;`trade] returns `:db//2026.04.10/trade. The doubled separator works on POSIX, but pass `:db and the paths come out clean.
Most of the time, you don't need to create the entire table from scratch; you only add the splayed table for the latest date. Because that is a common task, there's a helper function that does exactly that which is called .Q.dpft (named after its arguments). The function saves the table in the correct place and also does the enumeration. You only have to call it and do nothing else:
q).Q.dpft[`:db; 2026.04.12; `sym; `trade]
`trade
Reload and the three partitions present as one table:
q).Q.lo[`:db;0;0]
q)select cnt: count i by date from trade
date | cnt
----------| ----
2026.04.10| 1271
2026.04.11| 1271
2026.04.12| 2542
Note
Notice that in both cases, at the point of writing the table down, it didn't have a date column. That's because the date column (along with its name, date) is automatically generated from the partition name. Writing down a table that has a date column date-partitioned has unintended consequences.
A third option is dsave, which enumerates and applies the parted attribute for you. Pass its left argument as a list — database root first, partition name after — to write into a partition:
q)(trade; quote; ; ): getInMemoryTables ([tbls: `trade`quote])
q)`:db2`2026.04.10 dsave `sym xasc'`trade`quote
`trade`quote
Re-fetch the tables after a reload
The reload above rebound trade and quote to the partitioned tables on disk, and xasc cannot sort one of those:
q)`:db2`2026.04.10 dsave `sym xasc'`trade`quote
'par
Hence the first line, which puts the in-memory tables back.
db2
├── 2026.04.10
│ ├── quote
│ │ ├── .d
│ │ ├── asize
│ │ ├── ask
│ │ ├── bid
│ │ ├── bsize
│ │ ├── ex
│ │ ├── mode
│ │ ├── sym
│ │ └── time
│ └── trade
│ ├── .d
│ ├── cond
│ ├── ex
│ ├── price
│ ├── size
│ ├── stop
│ ├── sym
│ └── time
└── sym
See dsave for the unpartitioned form and the reason to sort before saving.
Loading partitioned tables¶
To load a partitioned table, you load the directory that contains all the partitions. \l presents the partitions as a single table and adds the partitioning column as a virtual column taken from the directory names.
You lose the ability to get the table, which still works with splayed tables (getting the directory that contains the entire database does see everything the directory contains but doesn't map the tables into memory).
A partitioned table is always mapped deferred: nothing is mapped when the database loads, and each query maps only the columns it touches. That is what makes the startup cost independent of how large the database is.
Upserting into partitioned tables¶
Upserting works along the same logic with partitioned tables as with splayed ones: you can use the same functions you used when originally saving the table, but instead of set, you use upsert:
q)tn:([] 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).Q.dd[.Q.par[`:db;2026.04.12;`trade];`] upsert .Q.en[`:db;tn]
`:db/2026.04.12/trade/
q).Q.lo[`:db;0;0]
q)select cnt: count i by date from trade
date | cnt
----------| ----
2026.04.10| 1271
2026.04.11| 1271
2026.04.12| 2544
q)-2#select date,sym,price,size,ex from trade where date=2026.04.12
date sym price size ex
------------------------------
2026.04.12 AAPL 189.5 100 N
2026.04.12 MSFT 412.25 250 Q
Restricted actions on partitioned tables¶
A partitioned table is not a table in memory — it is a collection of splayed tables that q presents as one. Only the operations that can be answered partition by partition work on it, and the rest signal rather than silently doing something else.
Indexing does not work. A partitioned table has no single row 3, so anything that reaches for a row by position signals 'par:
q)trade 3
'par
q)first trade
'par
q)3#trade
'par
q)last trade
'par
The same applies to keywords that have to see the whole table at once — reverse, distinct, and xasc among them. 'par means exactly this: an unsupported operation on a partitioned table.
exec does not work either, and it reports a different error — 'nyi, not 'par:
q)exec sum size from trade
'nyi
What does work is select, in every form, along with the operations that need only the schema or the cached count:
q)count trade
2542
q)2#select sym,price from trade
sym price
-----------
ASND 203.16
TSLA 463.29
So the way round all of the above is to select first and work on the result, which is an ordinary in-memory table:
q)exec first s from select s:sum size from trade
138706
Constrain the partition column first
A select with no constraint on the partition column reads every partition. Putting the partition column first in the where clause lets q skip the partitions that cannot match, which is the whole reason for partitioning — see use case below.
Inspect a partitioned database¶
When q loads a partitioned database, it records the layout in variables in the .Q namespace. The examples in this section use the db database built above, with its three partitions of trade.
Partition field and values¶
.Q.pf is the name of the partitioning column — one of `date, `month, `year, or `int — and .Q.pt lists the partitioned tables:
q).Q.pf
`date
q).Q.pt
`s#,`trade
.Q.pv lists the partition values, one for each partition directory. It holds the same values as the virtual partition column, here date:
q).Q.pv
2026.04.10 2026.04.11 2026.04.12
.Q.pd lists the directory that holds each partition, conformant to .Q.pv. In a database that is not segmented, every partition sits under the root:
q).Q.pd
`:.`:.`:.
In a segmented database, .Q.pd shows which segment holds each partition.
The uppercase forms .Q.PV and .Q.PD hold the same lists. They differ from the lowercase forms only after you restrict the database with .Q.view.
Table counts¶
q caches the row count of each partition of a table in .Q.PN. The cache starts empty and fills the first time the main thread counts the table:
q).Q.PN
trade|
q)count trade
5084
q).Q.PN
trade| 1271 1271 2542
Each list of counts is conformant to .Q.pv, so flip the cache and key it by partition value for a table of counts per partition:
q).Q.pv!flip .Q.PN
| trade
----------| -----
2026.04.10| 1271
2026.04.11| 1271
2026.04.12| 2542
.Q.pn holds the counts for the partitions in the current view. Without a view, it matches .Q.PN. .Q.cn counts a table partition by partition and fills the cache:
q).Q.cn trade
1271 1271 2542
Restrict queries to some partitions¶
.Q.view restricts every query on the partitioned tables to a list of partition values. It guards against runaway queries that would read the whole history:
q).Q.view 2026.04.11 2026.04.12
q)select cnt: count i by date from trade
date | cnt
----------| ----
2026.04.11| 1271
2026.04.12| 2542
q)count trade
3813
The view updates the lowercase variables and leaves the uppercase ones holding the whole database:
q).Q.pv
2026.04.11 2026.04.12
q).Q.PV
2026.04.10 2026.04.11 2026.04.12
q).Q.pn
trade| 1271 2542
q).Q.PN
trade| 1271 1271 2542
.Q.view clears .Q.pn, so the next count in the view fills it again. A partition value that matches no partition signals an error:
q).Q.view 2020.01.01
'invalid partition filter
[0] .Q.view 2020.01.01
^
To remove the view, apply .Q.view to nothing:
q).Q.view[]
q)date
2026.04.10 2026.04.11 2026.04.12
Use case¶
Partition a table if any of the following conditions apply:
- It has over 100 million records.
- It has a column that cannot fit in memory.
- It grows.
- Many queries can be limited to a range of values of the partitioning column.