# Performance tips
> Make loading and queries fast - memory, threads, batch commits, bulk INSERTs, good table design, time filters, what the column engine takes, zone maps, saved stats, and how to measure.
- Category: Performance tips
- Keywords: performance, speed, slow query, fast, tuning, column engine, row path, zone maps, skipped by column stats, KDB\_NO\_ZONE\_MAPS, slice reads, one symbol not in memory, KDB\_NO\_SLICE\_READS, slice\_reads, saved stats, answer from saved stats, column mask, KDB\_NO\_COLUMN\_MASK, keyless table, DEFERRED trigger, cache, EXPLAIN lines, memory, --memory, threads, --threads, --sync-commits, batch commits, synchronous\_commit, SET synchronous\_commit, X-KDB-Synchronous-Commit, group commit, bulk insert, batch size, rows per INSERT, result cache, --cache-mb, KDB\_CACHE\_VERIFY, cache per symbol, benchmark, kdb\_bench, timings, EXPLAIN, partition pruning, time filter, SYMBOL, LATEST ON, LIMIT, index, line protocol, checkpoint
- See also: [EXPLAIN](explain.md), [Indexes](indexes.md), [Line protocol (POST /write)](line-protocol.md), [Partitions, SORT BY and DEDUP KEY](partitions-and-dedup.md), [LATEST ON](latest-on.md), [Column types](column-types.md), [kdb serve](cli-serve.md), [Limits](limits.md)
KDB is fast for the work it was made for: chart queries on one symbol or one sensor, live commits, bulk loads.
Some queries that read **every row** of a big table are still slow. This page tells you how to avoid them, and how to measure.
The first rule: **measure first.** Look at the time KDB prints (`(3 rows, 0.4 ms)`) and at [EXPLAIN](explain.md), then change one thing at a time.
## Measure: timings and EXPLAIN
Syntax:
```sql
kdb sql "SELECT ..." -- table format ends with (N rows, X ms)
EXPLAIN SELECT ... -- how KDB will read the data
```
- `kdb sql` and `kdb shell` print `(N rows, X ms)` after each result: the time to parse and run the statement, without opening the database.
- The HTTP API gives `timings` (`parse_us`, `execute_us`, in microseconds) with each result (see [HTTP exec](http-exec.md)).
- `EXPLAIN` shows how many **partitions** are read (`1 of 12 partitions`), whether only a **key range** is read, whether an **index** is used, and on its last line which **engine** reads the table (`engine: column` is the fast one; `engine: row (...)` says why not).
- Run a query twice. The first run may read files from disk; the second is warm.
- With `kdb serve`, a repeated query comes from the [result cache](#result-cache). To time the engine, add `cache=0`: `GET /exec?query=...&cache=0`. A benchmark without it times the cache, not the query.
- `/exec` answers at most 10,000 rows unless you pass `limit`, and then says `"truncated": true`. Check that flag before you compare times: a cut answer is faster.
- For bigger tests there is `kdb_bench` (see `bench/README.md`).
**Command line: The time is on the last line** (sample data: sample)
```bash
kdb sql market "SELECT symbol, round(avg(close), 2) AS avg_close FROM ochl_minutes WHERE time IN '2026-09-10' GROUP BY symbol"
```
Output:
```text
symbol | avg_close
-------+----------
CITY | 504.24
COAST | 378.36
(2 rows, 0.6 ms)
```
**Example: EXPLAIN: a time filter and a symbol read only one key range** (sample data: sample)
```sql
EXPLAIN SELECT time, close FROM ochl_minutes WHERE symbol = 'CITY' AND time IN '2026-09-10';
```
Result:
| plan (STRING) |
|---|
| scan table ochl_minutes (1 of 1 partitions, about 956 rows) |
| read only 1 key range(s) of each partition |
| build 3 of 7 stored column(s) per row: time, symbol, close |
| filter while scanning: 2 condition(s) |
| engine: column |
(5 rows)
**Example: EXPLAIN without filters: every row is read** (sample data: sample)
```sql
EXPLAIN SELECT count(*) FROM ochl_minutes WHERE close > 500;
```
Result:
| plan (STRING) |
|---|
| scan table ochl_minutes (1 of 1 partitions, about 956 rows) |
| build 2 of 7 stored column(s) per row: time, close |
| filter while scanning: 1 condition(s) |
| group by 0 key(s), 1 aggregate(s) |
| engine: column |
(5 rows)
## EXPLAIN lines to look for
| Line in EXPLAIN | What it tells you |
|---|---|
| `scan table t (k of n partitions, ...)` | Only `k` files are read. A small `k` means the time filter works. |
| `..., m skipped by column stats)` | [Zone maps](#zone-maps) left out `m` more files by their min and max. |
| `answer from saved stats of table t: ...` | `count`, `sum`, `min`, `max` come from [saved stats](#saved-stats). No row is read. |
| `read only k key range(s) of each partition` | `symbol = ...` on the first sort column reads only those rows. |
| `use index name (...)` | An [index](#indexes) finds the rows. |
| `build k of n stored column(s) per row: ...` | The row path builds only these columns ([column mask](#column-mask)). |
| `aggregates pushed into the N UNION ALL parts` | A `GROUP BY` over a `UNION ALL` is grouped part by part ([many groups](#many-groups)). |
| `join (nested loop) ...` | The join has no `a = b` condition: every row meets every row. Slow on big tables. |
All lines are explained in [EXPLAIN](explain.md#plan-lines).
## Filter on time and symbol first
Syntax:
```sql
WHERE symbol = 'CITY' AND time IN '2026-09'
WHERE symbol IN ('CITY', 'COAST') AND time >= '2026-09-01' AND time < '2026-10-01'
```
On a time-series table, a condition on the **time column** skips whole partitions (files), and `symbol = ...` or `symbol IN (...)` on the first key column jumps straight to those rows.
- Always add a time range to queries on big tables: `time IN '2026-09'`, `time >= '2026-09-01'`.
- `symbol = 'X'`, `symbol IN ('X', 'Y')` and `symbol = 'X' OR symbol = 'Y'` on a `SYMBOL` column also skip the partitions that have **no row** of those symbols. KDB knows which symbols each partition holds without reading it. A symbol that traded for 3 months of 10 years reads 3 partitions.
- A symbol that is in every partition still reads every partition. Add a time range for those.
- A function around the column (`date_trunc('d', time) = ...`, `lower(symbol) = ...`) cannot skip anything. Compare the column itself.
- Select only the columns you need. `SELECT *` on 20 million rows moves much more data.
- `OR` between different columns reads everything; `IN (...)` on one column does not.
**Example: One symbol, one day: fast even on 20 million rows** (sample data: sample)
```sql
SELECT time, open, high, low, close FROM ochl_minutes
WHERE symbol = 'CITY' AND time IN '2026-09-10'
LIMIT 5;
```
Result:
| time (TIMESTAMP) | open (DOUBLE) | high (DOUBLE) | low (DOUBLE) | close (DOUBLE) |
|---|---|---|---|---|
| 2026-09-10T05:15:00.000Z | 504.6 | 504.6 | 504.4 | 504.4 |
| 2026-09-10T05:16:00.000Z | 504.4 | 504.7 | 504.3 | 504.6 |
| 2026-09-10T05:17:00.000Z | 504.6 | 504.9 | 504 | 504.2 |
| 2026-09-10T05:18:00.000Z | 504.2 | 504.6 | 504.1 | 504.4 |
| 2026-09-10T05:19:00.000Z | 504.4 | 504.8 | 504.1 | 504.5 |
(5 rows)
## Parameters, variables and computed dates
Syntax:
```sql
WHERE time >= d AND time < dateadd('d', 1, d) -- d: a procedure parameter or variable
WHERE time >= dateadd('d', -30, '2026-09-11')
```
A procedure parameter, a variable, a `$1` value and an expression without columns skip partitions just like a written date.
- KDB works out `dateadd(...)`, `CAST(...)`, `+ INTERVAL '1 day'`, sums of numbers and `now()` **once** per statement, before it picks the partitions.
- `=`, `<`, `<=`, `>`, `>=`, `BETWEEN` and `IN (...)` on the time column all skip partitions.
- A variable is read again every time a statement runs. A loop that changes it reads the right days.
- A `NULL` parameter matches no row and reads nothing.
- `random()` and `nextval()` still run for every row, so they cannot skip anything.
- `time::date = CURRENT_DATE` (or `>=`, `>`, `<=`, `<` a date) is read as `time >= the day's start AND time < the next day's start` on the session's clock, so an index on the column, partition skipping and the column engine take it as they take a written range. `<>` stays a check of every row.
- `CALL ... WITH DEBUG` shows `partitions read: N` for each statement (see [procedures](procedures.md#with-debug)).
**Example: A computed start date still reads only the days it needs** (sample data: sample)
```sql
EXPLAIN SELECT count(*) FROM ochl_minutes
WHERE time >= dateadd('d', -1, '2026-09-11') AND time < '2026-09-11';
```
Result:
| plan (STRING) |
|---|
| scan table ochl_minutes (1 of 1 partitions, about 956 rows) |
| build 1 of 7 stored column(s) per row: time |
| filter while scanning: 2 condition(s) |
| group by 0 key(s), 1 aggregate(s) |
| engine: column |
(5 rows)
**Example: A date cast compared with a date is a range of the time column** (sample data: sample)
```sql
EXPLAIN SELECT count(*) FROM ochl_minutes WHERE time::date = DATE '2026-09-10';
```
Result:
| plan (STRING) |
|---|
| scan table ochl_minutes (1 of 1 partitions, about 956 rows) |
| build 1 of 7 stored column(s) per row: time |
| filter while scanning: 1 condition(s) |
| group by 0 key(s), 1 aggregate(s) |
| engine: column |
(5 rows)
**Example: A procedure parameter reads one day, like a written date** (sample data: sample)
```sql
CREATE PROCEDURE minutes_of(day TIMESTAMP) AS BEGIN
SELECT count(*) AS minutes FROM ochl_minutes WHERE time >= day AND time < dateadd('d', 1, day);
END;
CALL minutes_of('2026-09-10') WITH DEBUG;
```
Result:
`CREATE PROCEDURE minutes_of(day TIMESTAMP) AS BEGIN`
```text
procedure 'minutes_of' created
```
`CALL minutes_of('2026-09-10') WITH DEBUG`
```text
procedure 'minutes_of' done: 0 row(s) changed
```
`SELECT count(*) AS minutes FROM ochl_minutes WHERE time >= day AND time < dateadd('d', 1, day)`
| minutes (LONG) |
|---|
| 476 |
(1 row)
`debug`
| step (LONG) | routine (STRING) | statement (STRING) | rows (LONG) | time_us (LONG) | changes (STRING) |
|---|---|---|---|---|---|
| 1 | | CALL minutes_of('2026-09-10') WITH DEBUG | 0 | 612 | partitions read: 1 |
| 2 | minutes_of | SELECT count(*) AS minutes FROM ochl_minutes WHERE time >= day AND time < dateadd('d', 1, day) | 1 | 570 | partitions read: 1 |
(2 rows)
## LIMIT
Syntax:
```sql
SELECT ... LIMIT n
SELECT ... ORDER BY col DESC LIMIT n
```
- `LIMIT` without `ORDER BY` stops reading as soon as it has `n` rows.
- `ORDER BY time DESC LIMIT n` on a time-series table reads **newest first**: it reads a short stretch of the newest rows, then older stretches only while it has fewer than `n` rows. The same goes for the newest `n` buckets of a chart, `SAMPLE BY 5m ORDER BY time DESC LIMIT n`, also with a key such as the symbol (`SELECT time, symbol, last(close) ... SAMPLE BY 1h ORDER BY time DESC LIMIT n`: the newest bars of every symbol). So the last 300 bars cost about the same with or without a time lower bound. [EXPLAIN](explain.md) shows `newest first: ...`. `ORDER BY time LIMIT n` (the oldest rows or buckets) reads the oldest stretch first the same way (`oldest first: ...`). It applies to one table (no join), with `ORDER BY` the time column alone, and for `SAMPLE BY` without `HAVING`. Since 2026-10-02 it also covers keys of any type (`SELECT time, symbol, volume > 0 AS traded, count(*) ...`) and `GROUP BY` next to `SAMPLE BY`, `ALIGN TO FIRST OBSERVATION` (the oldest row is found first, then the buckets are counted from it) and `FILL` without keys (`SAMPLE BY 5m FILL(PREV) ORDER BY time DESC LIMIT 5000`: when the first stretch ends in a gap and holds too few buckets, a longer stretch is read again, so the filled bars after the gap still take their `PREV` or `LINEAR` value from the real bar before it). `FILL` with a key (every symbol gets every bucket, also a symbol whose rows are all older), `FILL` with `FROM ... TO`, and a `LIMIT` per key (`ORDER BY symbol, time DESC`) still read every row: no short stretch can show which rows those need. A view or a subquery in `FROM` counts as its table when it only keeps some rows of it (`SELECT *` or plain columns `FROM t WHERE ...`): its `WHERE` is added to the query's. A `DATE` time column reads whole days. A table with `PARTITION BY NONE` is read this way when its rows are kept in time order (`SORT BY (time)`, or `PARTITION BY NONE, SYMBOL(sym) SORT BY (sym, time)`): each stretch is found by binary search on the time column. The rows it reads count against `max_result_memory` like any kept rows; a `LIMIT` too big for that limit is read the usual way (and refused when its rows do not fit).
- Any other `ORDER BY ... LIMIT n` must look at every matching row, but keeps only the best `n` in memory.
- `ORDER BY` one whole-number, `DATE` or `TIMESTAMP` column with no `LIMIT` (one symbol's trades of a day, newest first) reads the rows straight from the columns and sorts only them: about 3 ms became 1 ms for 5,000 of 64,000 rows (2026-09-25).
- Top `n` per group, `SELECT ... FROM (SELECT ..., row_number() OVER (PARTITION BY symbol ORDER BY qty DESC) AS rn FROM t WHERE ...) WHERE rn <= 5`: when every window of the inner query is `row_number()` or `rank()` with such a limit, over one `SYMBOL` column and ordered by one number or time column, KDB first finds the `n`-th best value of each symbol and then collects only the rows at least that good. 30 days of 686,000 rows: 57 ms became 4 ms (2026-09-25). A `NULL` or `NaN` in either column makes it read every row, as before.
- `--max-rows` of `kdb sql` and `limit=` of HTTP are a safety net that cuts the result. When you want fewer rows, write `LIMIT` in the SQL.
- For pages of a big result, use keyset pages (`WHERE time < ORDER BY time DESC LIMIT 20`), not a big `OFFSET`: `OFFSET 20000` still reads the 20,000 rows before.
**Example: The 3 biggest minute volumes of one day** (sample data: sample)
```sql
SELECT time, symbol, volume FROM ochl_minutes
WHERE time IN '2026-09-10'
ORDER BY volume DESC
LIMIT 3;
```
Result:
| time (TIMESTAMP) | symbol (SYMBOL) | volume (DOUBLE) |
|---|---|---|
| 2026-09-10T06:02:00.000Z | COAST | 1490 |
| 2026-09-10T07:22:00.000Z | COAST | 1490 |
| 2026-09-10T07:21:00.000Z | CITY | 1480 |
(3 rows)
## LATEST ON with a time filter
Syntax:
```sql
SELECT ... FROM live_data WHERE timestamp >= '' LATEST ON timestamp PARTITION BY symbol
```
[LATEST ON](latest-on.md) gives the newest row per symbol. It reads **every row in the time range** (column by column, on several threads), so it gets slower as the table grows.
- Add a time filter: `WHERE timestamp >= '2026-09-10'` reads only today's partition.
- For a live page of the last price per symbol, a small relational table updated with `INSERT OR REPLACE` (one row per symbol) is fastest.
**Example: The newest price per symbol, reading only one day** (sample data: sample)
```sql
SELECT symbol, timestamp, ltp FROM live_data
WHERE timestamp >= '2026-09-10'
LATEST ON timestamp PARTITION BY symbol;
```
Result:
| symbol (SYMBOL) | timestamp (TIMESTAMP) | ltp (DOUBLE) |
|---|---|---|
| CITY | 2026-09-10T09:14:00.000Z | 527.1 |
| COAST | 2026-09-10T09:14:00.000Z | 401.9 |
| TELCO | 2026-09-10T09:14:00.000Z | 897.7 |
| VOLT | 2026-09-10T09:14:00.000Z | 184 |
(4 rows)
## Bulk INSERT: many rows per statement
Syntax:
```sql
INSERT [OR REPLACE | OR IGNORE] INTO table VALUES (...), (...), (...), ...
```
Every statement is one **commit**: one write to the log and one wait for the disk. So many rows in one statement are much faster than one statement per row.
| Way | Rows per second on the test PC (about) |
|---|---|
| One `INSERT` per row, one HTTP request each | about 1,300 |
| `INSERT` with 100 rows per request | about 35,000 |
| `INSERT` with 2,000 to 20,000 rows per statement | about 110,000 to 160,000 |
| [Line protocol](line-protocol.md) (`POST /write`), many lines per request | the fastest way |
- Use **1,000 to 5,000 rows per `INSERT`** for loads. Much bigger statements only use more memory.
- Load in **time order** when you can.
- Send many statements in one `kdb sql -` run or one HTTP request, not one process per statement.
- `REFERENCES`, `UNIQUE` indexes and triggers check every row: a big first import is faster without them. Create indexes **after** a big first import.
- A `BEGIN` ... `COMMIT` with millions of changed rows keeps the old rows in memory (at most 10 million). For loads, use plain statements.
- (Numbers from `bench/README.md` and `bench/questdb/README.md`, measured on 2026-09-11 on an Intel i9-14900HX. Your PC will differ.)
**Example: Three rows in one statement: one commit** (sample data: sample)
```sql
INSERT OR REPLACE INTO ochl_day VALUES
('2026-09-13', 'CITY', 505.0, 509.0, 503.1, 508.2, 91000),
('2026-09-13', 'COAST', 376.0, 380.0, 375.2, 379.5, 120500),
('2026-09-13', 'VOLT', 191.0, 193.4, 190.1, 192.8, 40200);
```
Result:
```text
3 row(s) inserted
```
## Many small writes: --sync-commits batch
Syntax:
```sql
kdb serve [] --sync-commits always|batch
```
By default (`always`) KDB waits until each commit is **on the disk** before it answers. That is safe, but a disk can do only a few hundred to a few thousand such waits per second. Writers that commit at the same moment share one wait, so many clients together get many more commits per second than one client.
One wait is the disk's own flush: about 2-5 ms on a laptop SSD, on Docker Desktop and on many cloud disks. PostgreSQL waits for the same flush. When a few clients commit in a loop, KDB waits a moment (at most half a flush) for the ones that are about to commit, so one flush carries all of them instead of two flushes carrying half each. Measured, `UPDATE ... SET n = n + 1` over HTTP: 2 to 8 clients get 1.2 to 1.7 times more commits a second than before; one client and 16 or more are unchanged.
With `--sync-commits batch` KDB answers at once and writes all waiting commits to the disk together, every 10 ms.
- **Much faster** for many small writers (live ticks, one row per request, many clients).
- A power cut or a crash of the computer can **lose the commits of the last few milliseconds** that were already confirmed. The data is **never damaged**: after the restart the database is consistent, only those last commits are missing.
- Use `always` for money and accounting. `batch` is fine for market data you can load again.
**Command line: A server for a live feed** (sample data: sample)
```bash
kdb serve ./market --sync-commits batch
```
Output:
```text
KDB 0.2.0-dev
console http://127.0.0.1:9900
data ./market
backups ./_backups/market (from default)
access no token
capacity 32 HTTP threads (default), 500 PostgreSQL connections (default), lock wait 5 s (default)
lanes reads using 100 ms of CPU (three times in a row) wait for one of 16 heavy slots, at most 16 HTTP in line (--lanes on)
reads 32 reads run at once (the CPUs, at least 2; follows the CPU limit), the rest wait in line (--read-slots)
commits synced every 10 ms (--sync-commits batch)
time zone Asia/Katmandu (the computer's); also timers and backup schedules
readers read while a long trigger or CALL writes (--reader-yield on); idle open transaction rolled back after 30 s (default)
memory 26.95 GB for the whole process (85% of the machine's memory); table data 512.0 MB of it (--memory)
cpu 32 CPUs (the machine's); 16 query threads (half of the CPUs, follows the limit), limits checked every 10 s
limits statement timeout 1 min, result memory 1.00 GB, 5000000 groups, 32 statements per user
safety writes allowed; DROP TABLE kept 7 days (UNDROP TABLE); audit log off
logs slow queries >= 1000 ms to ./market/_logs/slow_queries.jsonl; pg_stat_statements on; metrics at /metrics
recovery manifest copy: a, last shutdown: clean
open opened in 3 ms: manifest 1 ms, 1 files read 0 ms (8 table files left for their first use), log check 1 ms, replay 0 ms (0 transactions), clean-up 1 ms
Press Ctrl+C to stop.
```
### One session only: SET synchronous_commit = off
Syntax:
```sql
SET synchronous_commit = off | on one session (HTTP session or PostgreSQL connection)
SET LOCAL synchronous_commit = off this transaction only (PostgreSQL connection)
X-KDB-Synchronous-Commit: off one HTTP request
```
The same trade as `batch`, but only for the writes that can take it - a rate-limit counter, a cache row - while every other session keeps `always`. No restart is needed. It works as in PostgreSQL:
- The commit is confirmed without waiting for the disk. A background thread puts it on the disk within about 10 ms.
- A power cut can lose the commits of about the last 10 ms that were already confirmed. A crash or kill of the KDB program (`docker rm -f`, an out-of-memory kill) can lose those of the last few ms: the log is handed to the operating system at most a millisecond later, except while a disk sync is running. The data is never damaged, and no other session's commit is ever lost. PostgreSQL makes the same trade.
- Measured on a laptop SSD, one client doing `UPDATE ctr SET n = n + 1 WHERE k = $1`: **5.1 ms** per update with `on`, **0.36 ms** with `off` (16 clients: 1,740 and 5,490 updates a second).
### One table: WITH (synchronous_commit = off)
Syntax:
```sql
CREATE TABLE t (...) WITH (synchronous_commit = on | off)
ALTER TABLE t SET (synchronous_commit = on | off)
ALTER TABLE t RESET (synchronous_commit)
```
The same trade, chosen once for a table instead of per session. A commit that changes **only** such tables is confirmed without waiting for the disk; the disk follows within about 10 ms, with the same risk as above. A commit that also changes a table that waits - an account, a user, an order - waits for the disk as before, so money and users are never confirmed early.
| Table | Without the option |
|---|---|
| a cache table (`WITH (max_bytes = ...)`, `evict`, `durability`) | answers before the flush |
| the key-value store's `_kv` and `_kv_items` | answers before the flush (Redis's `appendfsync everysec`); `CONFIG SET appendfsync always` sets `on` ([key-value](kv.md#kv-sync)) |
| every other table | waits for the flush |
- `SHOW CREATE TABLE` shows the option when it is set; `RESET` goes back to what the table's kind gives.
- `SET synchronous_commit = off` of a session still makes every commit of that session answer early.
**Example: Counters that need not wait for the disk, beside a ledger that must** (sample data: sample)
Setup:
```sql
CREATE TABLE page_hits (page STRING PRIMARY KEY, n LONG) WITH (synchronous_commit = off);
CREATE TABLE ledger (id LONG PRIMARY KEY, amount DECIMAL(2));
INSERT INTO page_hits VALUES ('/home', 1);
```
```sql
SHOW CREATE TABLE page_hits;
```
Result:
| create_sql (STRING) |
|---|
| CREATE TABLE page_hits ( page STRING PRIMARY KEY, n LONG ) WITH (synchronous_commit = off) |
(1 row)
### Many writes in one go: one disk wait for all
Each commit that waits for the disk costs one flush, so a client that sends one counter update per request is held to a few hundred a second. Send several writes together and they share **one** flush, with no loss of safety:
| Client | Send | One disk wait for |
|---|---|---|
| HTTP | several statements in one `POST /exec` (each commits on its own), or with `atomic=1` (one transaction) | the whole request |
| PostgreSQL port | a pipeline: several `Execute`s, then one `Sync` (pgx `SendBatch`, JDBC `executeBatch`, psycopg pipeline mode) | the whole pipeline, before `ReadyForQuery` |
Each statement of a pipeline still commits on its own, as before; what changed is that they wait for the disk together, at the `Sync` (since 2026-09-25). Measured on a busy laptop, counter updates (`UPDATE ... SET n = n + 1 ... RETURNING n`), one connection, 32 per batch (`node bench/counter.mjs`):
| Pipeline of 32, PostgreSQL port | before | now |
|---|---|---|
| 1 connection | 211 a second | 4,277 a second |
| 16 connections | 2,253 | 10,443 |
A `Sync` after every `Execute` still waits for each one, as PostgreSQL does. For single writes that may lose the last 10 ms in a power cut, use `synchronous_commit = off` (above).
## Memory: --memory
Syntax:
```sql
kdb serve [] --memory
kdb sql "..." --memory
kdb shell [] --memory
```
`--memory` is the memory for table data that KDB keeps loaded, for **all open databases together**. The default is **512 MB**. Data over the budget is unloaded and read from disk again when needed.
- When the tables are bigger than the budget, queries over much data read files again and again. On the test PC, a count over 20 million minute rows took 4.4 s with 512 MB and 1.4 s with `--memory 4096`.
- A good start: about the size of the data folder on disk, plus some room, if the computer has the RAM. `kdb info` shows the file sizes.
- When a database opens with more than **8 times** `--memory` of table data, the server log says so, and `GET /api/info` has the same text in `warning`.
- Do not give KDB more than the computer has: the operating system would swap, which is much slower.
What the budget counts, and what it does not:
| Memory | Limited by | Freed |
|---|---|---|
| Saved rows of loaded partitions | `--memory` | oldest use first, when over the budget |
| Lookup maps of `DEDUP KEY` / `PRIMARY KEY` for those partitions | `--memory` | with the partition |
| Column copies of changed rows (for fast scans) | `--memory` | at the checkpoint, or when over the budget |
| Changes not yet written to partition files | a checkpoint starts at a quarter of `--memory` per database | at the checkpoint |
| Kept query results | `--cache-mb` (all databases together) | oldest first |
| `CREATE INDEX` indexes | not limited (see [indexes](indexes.md)) | `DROP INDEX` |
| The rows of a statement while it runs (a big `INSERT`, a `/write` with triggers, a big result) | the size of the request | when it ends |
So the memory of the process is about `--memory` + `--cache-mb` + a quarter of `--memory` for each database being written + indexes + the requests running now + about 30 MB for the program itself. `GET /api/stats` shows every part (`memory` of each database) next to `resident_bytes`.
- Partitions a running write statement uses (the partition it inserts into, the rows it looks up) are not unloaded until the statement ends, and partitions with unsaved changes go after those without. So a small budget makes big queries slower, but a load does not read the same partition again for every statement.
- A query that reads a few columns of a table bigger than the room left, when partitions of that table are held whole (just written, or read for every column), first cuts those partitions down in memory to the columns it reads, and only then unloads anything. It does so only when the cut lets all the columns it reads fit the budget next to what other tables hold; a query bigger than the budget even at its own columns, or one over a small table, unloads as before (its own reads first), so the partitions other queries keep whole stay whole. The next run of the same query finds all its columns in memory instead of reading the same files again. `GET /api/stats` counts these as `partitions_slimmed`; `KDB_NO_SLIM_SCAN=1` turns it off.
- When the databases together are over `--memory`, memory is taken back in this order: databases with no statement in the last 2 seconds, then databases that only read, then the database that needs the room, then other databases that are writing.
- A database that is writing keeps at least `max(64 MB, --memory / (2 x open databases))` (never more than an equal share), whatever the others need. So one database kept in memory by earlier reads cannot slow down a load into another one. `GET /api/stats` shows each database's `share_bytes`, `floor_bytes` and `evicted_by_other_databases`.
**Command line: More memory for a big history** (sample data: sample)
```bash
kdb serve ./market --memory 4096
```
Output:
```text
KDB 0.2.0-dev
console http://127.0.0.1:9900
data ./market
backups ./_backups/market (from default)
access no token
capacity 32 HTTP threads (default), 500 PostgreSQL connections (default), lock wait 5 s (default)
lanes reads using 100 ms of CPU (three times in a row) wait for one of 16 heavy slots, at most 16 HTTP in line (--lanes on)
reads 32 reads run at once (the CPUs, at least 2; follows the CPU limit), the rest wait in line (--read-slots)
commits synced to disk before they are confirmed (--sync-commits always)
time zone Asia/Katmandu (the computer's); also timers and backup schedules
readers read while a long trigger or CALL writes (--reader-yield on); idle open transaction rolled back after 30 s (default)
memory 26.95 GB for the whole process (85% of the machine's memory); table data 4.00 GB of it (--memory)
cpu 32 CPUs (the machine's); 16 query threads (half of the CPUs, follows the limit), limits checked every 10 s
limits statement timeout 1 min, result memory 1.00 GB, 5000000 groups, 32 statements per user
safety writes allowed; DROP TABLE kept 7 days (UNDROP TABLE); audit log off
logs slow queries >= 1000 ms to ./market/_logs/slow_queries.jsonl; pg_stat_statements on; metrics at /metrics
recovery manifest copy: a, last shutdown: clean
open opened in 2 ms: manifest 0 ms, 1 files read 1 ms (8 table files left for their first use), log check 0 ms, replay 0 ms (0 transactions), clean-up 1 ms
Press Ctrl+C to stop.
```
### Memory per database and resident tables
When several apps share one server, a big scan in one database can unload the tables another database reads all the time. Three settings stop that:
| Setting | What it does |
|---|---|
| [`ALTER DATABASE d SET MEMORY_LIMIT = '512MB'`](databases.md#memory) | `d` holds at most this much loaded data. Over it, `d` unloads its **own** partitions first, even when `--memory` has room. |
| [`ALTER DATABASE d SET MEMORY_RESERVED = '128MB'`](databases.md#memory) | this much of `d`'s loaded data is never unloaded for another database or for the [process memory limit](cli-serve.md#memory-limit). |
| [`ALTER TABLE t SET (resident = on)`](alter-table.md#resident) | the table's partitions stay loaded once read, whoever needs memory. They still count against the database's `MEMORY_LIMIT`. |
- All reservations together may be at most three quarters of `--memory`, and a database's reservation at most its limit.
- `GET /api/stats` shows each database's `used_bytes`, `limit_bytes`, `reserved_bytes` and `resident_bytes` under `memory`.
- kdb.conf can give the start values: `"database_memory": {"sales": {"limit_mb": 512, "reserved_mb": 128}}`. `ALTER DATABASE` wins over it; `DEFAULT` goes back to it.
## Big GROUP BY over a UNION
`INSERT INTO daily SELECT ... FROM (SELECT ... FROM a UNION ALL SELECT ... FROM b) u GROUP BY ...` does not keep the rows of the `UNION ALL` in memory: when every part is a plain read of one table (no `GROUP BY`, `DISTINCT`, `ORDER BY` or `LIMIT` inside), the rows go straight into the grouping, part after part. A view of that shape works the same way.
- Only the groups are kept. When they need more than half of `--max-result-memory`, the rows of further groups go to sorted temporary files in the database folder and are added up after the scan ([memory limits](protection.md#memory-limits)). The result is the same, only slower.
- Conditions of the outer `WHERE` are not moved into the parts then: write a time condition inside each part, so whole partitions are skipped.
- To keep a very big grouping fast, group one day (or one month) per statement.
## Threads: --threads
Syntax:
```sql
kdb serve [] --threads N
```
Big queries and big writes use several threads. The default, and the most, is **half** of the CPU threads, so KDB never takes the whole computer.
- Use fewer threads when the same computer runs your app and other programs that need the CPU: `--threads 2`.
- More threads help queries that read many rows (counts, sums, GROUP BY over months). A query on one symbol and one day does not need them.
- All databases of one server share these threads.
- `kdb sql` and `kdb shell` always use the default.
**Command line: Leave CPU for other programs** (sample data: sample)
```bash
kdb serve ./market --threads 1
```
Output:
```text
KDB 0.2.0-dev
console http://127.0.0.1:9900
data ./market
backups ./_backups/market (from default)
access no token
capacity 32 HTTP threads (default), 500 PostgreSQL connections (default), lock wait 5 s (default)
lanes reads using 100 ms of CPU (three times in a row) wait for one of 16 heavy slots, at most 16 HTTP in line (--lanes on)
reads 32 reads run at once (the CPUs, at least 2; follows the CPU limit), the rest wait in line (--read-slots)
commits synced to disk before they are confirmed (--sync-commits always)
time zone Asia/Katmandu (the computer's); also timers and backup schedules
readers read while a long trigger or CALL writes (--reader-yield on); idle open transaction rolled back after 30 s (default)
memory 26.95 GB for the whole process (85% of the machine's memory); table data 512.0 MB of it (--memory)
cpu 32 CPUs (the machine's); 1 query thread (--threads), limits checked every 10 s
limits statement timeout 1 min, result memory 1.00 GB, 5000000 groups, 32 statements per user
safety writes allowed; DROP TABLE kept 7 days (UNDROP TABLE); audit log off
logs slow queries >= 1000 ms to ./market/_logs/slow_queries.jsonl; pg_stat_statements on; metrics at /metrics
recovery manifest copy: a, last shutdown: clean
open opened in 2 ms: manifest 1 ms, 1 files read 0 ms (8 table files left for their first use), log check 0 ms, replay 0 ms (0 transactions), clean-up 1 ms
Press Ctrl+C to stop.
```
## Result cache: --cache-mb
Syntax:
```sql
kdb serve [] --cache-mb
```
`kdb serve` keeps the results of queries in memory (default **256 MB** for all open databases together). The same query again gets the kept result at once, as long as **no table it reads has changed**.
- Good for dashboards and live pages that ask the same question many times.
- A write makes old only the kept results that **read what it changed**. A result of `WHERE symbol = 'NABIL'` (or `symbol IN (...)`) stays valid while other symbols are written, on a table with a [rev column](revisions.md#rev-column): `PARTITION BY ..., SYMBOL(symbol)`, or `symbol` first in `SORT BY` / `DEDUP KEY`. A new NEPSE candle every minute no longer makes every NABIL chart run again.
- Every other kept result of the table - no symbol filter, a join of the table with itself, `symbol = 'x' OR ` - is made old by any write to the table, as before.
- A page can ask cheaply whether its symbol changed at all: [GET /api/rev](revisions.md) and `kdb_rev()`.
- A result that took **1 ms or more** is kept the first time. A quicker one is kept the **second** time the same query comes. So one-off lookups (a session token, a user id) do not fill the cache and push out the pages that are asked for all day.
- Random functions and similar are never kept. Queries with `now()` (or `current_date`, `age(t)`) are not kept either, unless the caller says how old an answer it takes: `/exec?cache_now_ms=500`, or `SET result_cache_now_ms = 500` for a session. The answer is then the one computed up to that long ago, as if the query had run then.
- `--cache-mb 0` turns it off. A bigger value helps when many different queries repeat.
- The size can also come from `cache_mb` in [kdb.conf](cli-serve.md#kdb-conf) (the console's [Settings page](console-settings.md#result-cache) changes it while the server runs) or `KDB_CACHE_MB`. A database can get a space of its own that other databases' results cannot push out: [ALTER DATABASE ... SET RESULT_CACHE](databases.md#result-cache).
- One result is kept up to a quarter of the cache (64 MB with the default: a whole day of a floor sheet fits), and the oldest results go first when it is full. A result that took 1 ms or more to compute is passed over once before it goes. A query that must always be ready - the slow one a dashboard asks on every load - can be **pinned** ([pinned queries](pinned-queries.md)): computed when the database opens, never pushed out, allowed three quarters of the cache, and computed again by the server after a write.
- A plain `SELECT` sent again with the same text is not parsed again, and neither is an `INSERT ... VALUES`, `UPDATE` or `DELETE` sent a third time: the server keeps the parsed statement for each database ([the plan cache](server-settings.md#plan-cache), at most 2,048 texts of up to 2 KB). An `INSERT ... VALUES`, `UPDATE`, `DELETE` or `CALL` whose values are written into the text is also kept by its shape, per connection: the next one with other values (`CALL transfer(1018, 3, 9, 25)` after `CALL transfer(1017, 5, 2, 10)`) puts its values into the kept parse instead of being parsed. After `ALTER TABLE` or `DROP TABLE` the next run parses it again. A prepared statement with `$1` values (the PostgreSQL protocol's Parse, then Bind and Execute) is parsed once, when it is prepared: each Execute only puts its values in place. That holds for a `SELECT` (also with `FOR UPDATE`, or a `$1` in `LIMIT`, `OFFSET` or `FETCH FIRST`), `INSERT ... VALUES`, `UPDATE` and `DELETE`, with or without `RETURNING`. So prepare a query or a write your app sends often once per connection (most drivers, such as pgx and psycopg, do this for you) instead of sending new text each time. These still parse their text each time: `INSERT ... SELECT`, a statement that starts with `WITH`, one that names another database (`db.t`), `FOR UPDATE SKIP LOCKED`, `LIMIT $1, $2` and `LIMIT -$1`, and an `INSERT` or `UPDATE` that calls `password_hash()`.
- For a fair benchmark, skip the cache for one query or one session instead: `/exec?cache=0` (see [GET /exec](http-exec.md#get-exec)), or `SET result_cache = off` (back with `SET result_cache = on`). The answer is the same; only the time changes.
- To check the cache itself, start the server with `KDB_CACHE_VERIFY=1` in the environment: every cache hit is computed again and compared. A difference is logged, counted in `GET /api/stats` (`cache_verify_mismatches`, next to `cache_verify_checks`) and answered with the fresh result. It costs every hit a full query, so it is for tests and for chasing a suspected stale answer.
```sql
SET result_cache = off;
SELECT symbol, sum(volume) FROM ochl_day GROUP BY symbol; -- computed every time
```
```sql
SET result_cache_now_ms = 1000;
SELECT count(*) AS n, max(now()) AS asked_at FROM ochl_day; -- an answer up to 1 s old may come from the cache
```
**Command line: A bigger result cache for a dashboard** (sample data: sample)
```bash
kdb serve ./market --cache-mb 256
```
Output:
```text
KDB 0.2.0-dev
console http://127.0.0.1:9900
data ./market
backups ./_backups/market (from default)
access no token
capacity 32 HTTP threads (default), 500 PostgreSQL connections (default), lock wait 5 s (default)
lanes reads using 100 ms of CPU (three times in a row) wait for one of 16 heavy slots, at most 16 HTTP in line (--lanes on)
reads 32 reads run at once (the CPUs, at least 2; follows the CPU limit), the rest wait in line (--read-slots)
commits synced to disk before they are confirmed (--sync-commits always)
time zone Asia/Katmandu (the computer's); also timers and backup schedules
readers read while a long trigger or CALL writes (--reader-yield on); idle open transaction rolled back after 30 s (default)
memory 26.95 GB for the whole process (85% of the machine's memory); table data 512.0 MB of it (--memory)
cpu 32 CPUs (the machine's); 16 query threads (half of the CPUs, follows the limit), limits checked every 10 s
limits statement timeout 1 min, result memory 1.00 GB, 5000000 groups, 32 statements per user
safety writes allowed; DROP TABLE kept 7 days (UNDROP TABLE); audit log off
logs slow queries >= 1000 ms to ./market/_logs/slow_queries.jsonl; pg_stat_statements on; metrics at /metrics
recovery manifest copy: a, last shutdown: clean
open opened in 2 ms: manifest 1 ms, 1 files read 0 ms (8 table files left for their first use), log check 0 ms, replay 0 ms (0 transactions), clean-up 1 ms
Press Ctrl+C to stop.
```
## What the column engine takes
Syntax:
```sql
WHERE part AND part AND ... -- every part must be one of the shapes below
```
KDB has two ways to run a query on one table: the **column engine** (whole columns at a time, on all threads) and the **row path** (row by row, one thread). The column engine takes a `WHERE` only when **every** part joined by `AND` is one of these:
| `WHERE` part | Example |
|---|---|
| a column against a value: `=`, `!=`, `<`, `<=`, `>`, `>=` | `volume > 1000`, `symbol = 'CITY'` |
| `BETWEEN` and `NOT BETWEEN` two values | `close BETWEEN 500 AND 510` |
| `IN` and `NOT IN` a list of values | `symbol IN ('CITY', 'COAST')` |
| `OR` of `=` (or `IN`) on the **same** column | `symbol = 'CITY' OR symbol = 'COAST'` |
| `LIKE`, `ILIKE`, `NOT LIKE` on a `SYMBOL` or `STRING` column | `symbol LIKE 'C%'` |
| `time IN '...'` on a `TIMESTAMP` or `DATE` column | `time IN '2026-09-10'` |
| `IS NULL`, `IS NOT NULL` | `close IS NOT NULL` |
| a column against a column: two whole-number columns, two `DATE`, two `TIMESTAMP`, two `FLOAT`/`DOUBLE`; two `SYMBOL` columns with `=` or `!=` | `close > open`, `buyer = seller` |
| `NOT`, `OR` and `AND` of the shapes above, nested any way | `NOT (volume <= 0 AND high = low AND open = close)`, `symbol = 'CITY' OR volume > 1000` |
| one member of a `JSON` column against text: `=`, `!=`, `<`, `<=`, `>`, `>=` | `extra_data->>'category' = 'auth'` |
| a `TIMESTAMP` column against `date_trunc` of a `TIMESTAMP` column: `=` and `!=`, also with a time zone | `time = date_trunc('day', time)` (daily bars only: 2.7 ms became 0.8 ms, 2026-09-25) |
These send the query to the row path:
- **Arithmetic in the `WHERE`**: `close * 1 > 500`, `high - low > 5`. Keep the column alone on one side.
- **A function around a column**: `lower(symbol) = 'city'`, `abs(close) > 1` (only `time = date_trunc(...)` above is taken).
- `JSON` and `BYTES` columns (other than `json->>'key'` against text), and `DECIMAL` or `STRING` compared with another column.
- **Two tables**: a `JOIN` runs on the row path.
- **One such part is enough.** `volume > 1000 AND close * 1 > 500` runs as slowly as the slow part alone: on 1,000,000 rows the row path took 107 to 151 ms where the column engine took 1.3 to 2.4 ms.
What the newer shapes gave, 1,000,000 rows, one thread, before and after they came into the column engine (TASKS.md, 2026-09-23):
| Query | Before | After |
|---|---|---|
| `sym LIKE 'S6%'` | 242 ms | 1.2 ms |
| `sym ILIKE ...` | 230 ms | 2.2 ms |
| `sum(qty) WHERE sym = 'S1' OR sym = 'S2'` | 151 ms | 0.30 ms |
| random whole numbers, `x > L` | 4.68 ms | 0.67 ms |
| `b BETWEEN ...` | 8.77 ms | 0.84 ms |
`NOT`, `OR` and `AND` trees (2026-09-24, 8.4 million minute bars of 20 symbols stored by symbol, all threads; `reports/speed-trees-or-index-agg-expr-2026-09-24.md`):
| Query | Before | After |
|---|---|---|
| a chart read: `symbol = 'NABIL-1' AND NOT (volume <= 0 AND high = low AND open = close) ORDER BY time DESC LIMIT 300` | 189 ms | 5.3 ms |
| the same, `LIMIT 5000` | 256 ms | 35 ms |
| `count(*) WHERE symbol = ... AND (volume IS NULL OR volume > 550 OR (open = close AND high = low))` | 129 ms | 1.7 ms |
**Example: LIKE, BETWEEN and an OR on one column: all in the column engine** (sample data: sample)
```sql
SELECT count(*) AS minutes, sum(volume) AS volume FROM ochl_minutes
WHERE (symbol = 'CITY' OR symbol = 'COAST') AND symbol LIKE 'C%' AND close BETWEEN 400 AND 600;
```
Result:
| minutes (LONG) | volume (DOUBLE) |
|---|---|
| 480 | 365430 |
(1 row)
## Select only the columns you need
Syntax:
```sql
SELECT time, close FROM ... -- builds 2 columns per row
SELECT * FROM ... -- builds every column
```
When a query runs on the row path, KDB builds only the columns the query reads. `EXPLAIN` shows them: `build 2 of 7 stored column(s) per row: time, close`.
- On a table with 21 columns, `count(*) WHERE a * 1 > X` went from 460 ms to 210 ms, `GROUP BY` from 549 to 242 ms. `SELECT *` still builds every column (465 to 436 ms).
- So name the columns, even when the `WHERE` must stay on the row path.
- `KDB_NO_COLUMN_MASK=1` in the environment of `kdb serve` turns this off, to compare.
**Example: The row path builds only the columns the query reads** (sample data: sample)
```sql
EXPLAIN SELECT time, close FROM ochl_minutes WHERE close * 1 > 500;
```
Result:
| plan (STRING) |
|---|
| scan table ochl_minutes (1 of 1 partitions, about 956 rows) |
| build 2 of 7 stored column(s) per row: time, close |
| filter while scanning: 1 condition(s) |
| engine: row (a condition the column engine does not do) |
(4 rows)
## count, sum, min, max of a whole table
Syntax:
```sql
SELECT count(*), sum(x), min(x), max(x), avg(x) FROM t [WHERE