Partitions, SORT BY and DEDUP KEY

How PARTITION BY, SORT BY and DEDUP KEY of a time-series table change where rows go, their order, and what happens to duplicates; DROP PARTITION deletes old data fast.

• Category: Time-series
• Keywords: partition, partitions, PARTITION BY, DAY, MONTH, YEAR, NONE, SYMBOL(col), KEY(col), partition by symbol, symbol partitions, DROP PARTITION OLDER THAN, OLDER THAN, TRUNCATE, keyless table, batch dedup, KDB_NO_BATCH_DEDUP, how loads find duplicates, SORT BY, SORT, sort order, row order, DEDUP KEY, DEDUP, KEY, dedup, deduplication, duplicates, duplicate rows, upsert, INSERT OR REPLACE, INSERT OR IGNORE, DROP PARTITION, delete old data, retention, purge, files, key range, TIMESERIES, time-series table, QuestDB, DROP PARTITION LIST, dedup, skip dedup, DEDUP TABLE, repair duplicates, dedup_insert, WITH (dedup_insert, plain INSERT upserts, DEDUP UPSERT, compression, WITH (compression, SET (compression, COMPRESS TABLE, packed, pack, compress, bytes a row, disk size, sealed partition, tail files, tail_files, KDB_TAIL_FILES, --tail-files, append, appends, checkpoint, rewrite, delta files, KDB_NO_PARTITION_DELTA, partition_deltas, small update, small replace, trash growth
• See also: CREATE TABLE, ALTER, RENAME and DROP TABLE, INSERT, UPDATE and DELETE, Time and time zones (storage), EXPLAIN (explain), Performance tips

A time-series table (CREATE TABLE ... TIMESERIES(time)) has three extra options. This page shows what they do. The full CREATE TABLE and ALTER TABLE grammar is in create-table and alter-table.

PARTITION BY and SORT BY cannot be changed after the table is made. Choose them when you create the table. The DEDUP KEY can be set or taken off later with ALTER TABLE ... DEDUP ENABLE, which rewrites the rows.

Example: The sample market tables and their partitions (sample data: sample)

SHOW TABLES;

Result:

(6 rows)

PARTITION BY

Syntax:

CREATE TABLE name (...) TIMESERIES(time_column)
    PARTITION BY DAY | MONTH | YEAR | NONE

A partition is one part of the table on disk. KDB puts each row into the partition of its time: with PARTITION BY MONTH, all rows of September 2026 go into the partition 2026-09.

• Notes:
  • Partitions follow the UTC date. '2026-09-10T02:00:00+05:45' (2 AM in Nepal) is still 9 September in UTC, so it goes into partition 2026-09-09.
  • A query with a time condition on the first table (time IN '2026-09', >=, <, BETWEEN) reads only the partitions in that range. EXPLAIN shows it as 1 of 2 partitions.
  • A condition without the time column (close > 500) reads every partition.
  • A partition is made when its first row arrives. Old partitions can be deleted fast with DROP PARTITION.
  • Too small partitions (DAY for 50 rows a day) make many small files; too big ones make time filters less useful. See the table above.
  • Only time-series tables have partitions. PARTITION BY on a relational table is an error.
  • A table that is nearly always read one symbol at a time can be split by symbol too: PARTITION BY ..., SYMBOL(col).

Example: A condition on time skips whole partitions (sample data: sample)

EXPLAIN SELECT symbol, close FROM ochl_day WHERE time IN '2025-12';
EXPLAIN SELECT symbol, close FROM ochl_day WHERE close > 500;

Result:

EXPLAIN SELECT symbol, close FROM ochl_day WHERE time IN '2025-12'

(4 rows)

EXPLAIN SELECT symbol, close FROM ochl_day WHERE close > 500

(4 rows)

Example: Month partitions: rows of three months make three partitions (sample data: sample)

CREATE TABLE prices (time TIMESTAMP, symbol SYMBOL, close DOUBLE)
  TIMESERIES(time) PARTITION BY MONTH;
INSERT INTO prices VALUES
  ('2026-07-31T11:00:00+05:45', 'CITY', 511.0),
  ('2026-08-30T11:00:00+05:45', 'CITY', 507.3),
  ('2026-09-10T11:00:00+05:45', 'CITY', 504.6);
EXPLAIN SELECT close FROM prices WHERE time IN '2026-09';

Result:

CREATE TABLE prices (

table 'prices' created

INSERT INTO prices VALUES ('2026-07-31T11:00:00+05:45', 'CITY', 511.0), ('2026-08-30T11:00:00+05:45'

3 row(s) inserted

EXPLAIN SELECT close FROM prices WHERE time IN '2026-09'

(4 rows)

Example: Early morning in Nepal is the day before in UTC: the error lists partition 2026-09-09 (sample data: sample; shows an error)

Setup:

CREATE TABLE ticks (time TIMESTAMP, symbol SYMBOL, price DOUBLE) TIMESERIES(time) PARTITION BY DAY;
INSERT INTO ticks VALUES ('2026-09-10T02:00:00+05:45', 'CITY', 1), ('2026-09-10T11:00:00+05:45', 'CITY', 2);

ALTER TABLE ticks DROP PARTITION '2026-09-11';

Result:

Error: table 'ticks' has no partition '2026-09-11'; partitions: 2026-09-09, 2026-09-10

Example: Partitions are only for time-series tables (sample data: sample; shows an error)

CREATE TABLE app_notes (id INT PRIMARY KEY, text STRING) PARTITION BY DAY;

Result:

Error: line 1, column 58: PARTITION BY is only for time-series tables; expected TIMESERIES(<TIMESTAMP or DATE column>) on table 'app_notes'

PARTITION BY time, SYMBOL(col)

Syntax:

CREATE TABLE name (...) TIMESERIES(time_column)
    PARTITION BY YEAR | MONTH | NONE, SYMBOL(column)
    SORT BY (column, ...) [DEDUP KEY (column, ..., time_column)]
PARTITION BY ..., KEY(column)        -- the same, for a column that is not a market symbol

A second level of partitions: each time bucket is split again into one file per value of the column. A query for one symbol then opens only that symbol's files.

• Returns: SHOW TABLES shows the partition as YEAR, SYMBOL(symbol). KEY(col) is shown as SYMBOL(col).
• Notes:
  • WHERE symbol = 'CITY' or symbol IN (...) opens only those files. EXPLAIN shows it as 1 of 4 partitions.
  • Good for data read one value at a time: one symbol's candles, one source's news. Not for a table read a whole day at a time across all symbols (a trade list): plain PARTITION BY DAY is right there.
  • Reads get faster, writes do not: a load that holds 300 symbols writes 300 files.
  • Not for a column with very many values (a user id). Keep such a column as the first SORT BY column instead: that already reads only its rows.
  • DROP PARTITION '2025' drops every symbol of that year. Single symbols cannot be dropped by name: use DELETE ... WHERE symbol = .... With NONE there is no time part to drop: use DELETE or TRUNCATE.
  • Only for new tables: an existing table cannot be changed to it. Not with CREATE TABLE ... AS.
  • An older KDB build cannot open a database with such a table (see versions).

Example: Daily candles in one file per year and symbol (sample data: sample)

Setup:

CREATE TABLE candles (time TIMESTAMP ROUND 1d, symbol SYMBOL, close DOUBLE)
  TIMESERIES(time) PARTITION BY YEAR, SYMBOL(symbol) SORT BY (symbol, time) DEDUP KEY (symbol, time);
INSERT INTO candles VALUES
  ('2025-09-10', 'CITY', 500.0), ('2026-09-10', 'CITY', 504.6),
  ('2026-09-10', 'COAST', 376.1), ('2026-09-10', 'VOLT', 191.2);

SHOW TABLES;
EXPLAIN SELECT time, close FROM candles WHERE symbol = 'COAST';
ALTER TABLE candles DROP PARTITION '2025';
SELECT time, symbol, close FROM candles ORDER BY symbol;

Result:

SHOW TABLES

(7 rows)

EXPLAIN SELECT time, close FROM candles WHERE symbol = 'COAST'

(4 rows)

ALTER TABLE candles DROP PARTITION '2025'

1 partition(s) of 'candles' dropped

SELECT time, symbol, close FROM candles ORDER BY symbol

(3 rows)

Example: News split by source: KEY(col) and PARTITION BY NONE (sample data: sample)

Setup:

CREATE TABLE news (time TIMESTAMP, source STRING, title STRING)
  TIMESERIES(time) PARTITION BY NONE, KEY(source) SORT BY (source, time);
INSERT INTO news VALUES
  ('2026-09-10T11:00:00+05:45', 'wire one', 'City Bank profit rises 18%'),
  ('2026-09-11T09:30:00+05:45', 'market desk', 'Index closes higher');

EXPLAIN SELECT title FROM news WHERE source = 'market desk';
SHOW CREATE TABLE news;

Result:

EXPLAIN SELECT title FROM news WHERE source = 'market desk'

(4 rows)

SHOW CREATE TABLE news

(1 row)

Example: DAY is refused with SYMBOL(col) (sample data: sample; shows an error)

CREATE TABLE ticks (time TIMESTAMP, symbol SYMBOL, price DOUBLE)
  TIMESERIES(time) PARTITION BY DAY, SYMBOL(symbol);

Result:

Error: PARTITION BY ..., SYMBOL(column) on table 'ticks': the time part must be YEAR, MONTH or NONE (as in PARTITION BY YEAR, SYMBOL(symbol))

Example: The column must be the first SORT BY column (sample data: sample; shows an error)

CREATE TABLE candles (time TIMESTAMP, symbol SYMBOL, close DOUBLE)
  TIMESERIES(time) PARTITION BY MONTH, SYMBOL(symbol) SORT BY (time);

Result:

Error: PARTITION BY ..., SYMBOL(column) on table 'candles': 'symbol' must be the first SORT BY column (as in SORT BY (symbol, time))

Example: The DEDUP KEY must hold the column (sample data: sample; shows an error)

CREATE TABLE candles (time TIMESTAMP, symbol SYMBOL, close DOUBLE)
  TIMESERIES(time) PARTITION BY MONTH, SYMBOL(symbol) DEDUP KEY (time);

Result:

Error: PARTITION BY ..., SYMBOL(column) on table 'candles': the DEDUP KEY must include 'symbol'

SORT BY

Syntax:

CREATE TABLE name (...) TIMESERIES(time_column)
    [PARTITION BY ...]
    SORT BY (column [, column ...])

Sets the order of rows inside each partition. Rows are kept sorted by these columns, whatever order you insert them in.

• Returns: a SELECT without ORDER BY gives rows partition by partition, and inside a partition in SORT BY order.
• Notes:
  • The default is the first SYMBOL column, then the time column: SORT BY (symbol, time). Without a SYMBOL column it is the time column.
  • With SORT BY (symbol, time), WHERE symbol = 'CITY' or symbol IN (...) reads only CITY's rows (with a time range: only that part of them). EXPLAIN shows read only 1 key range(s). This is why the default is good for market data: most queries ask for one symbol.
  • With SORT BY (time), a filter on symbol alone must read every row of the partitions. A time range still reads only that part.
  • Do not depend on the order without ORDER BY: the partitions, SORT BY and the query plan decide it. Write ORDER BY when the order matters.
  • SORT BY only decides the order. Two rows with the same SORT BY values are not duplicates; that is the job of DEDUP KEY.

Example: Default SORT BY (symbol, time): rows come grouped by symbol (sample data: sample)

Setup:

CREATE TABLE trades (time TIMESTAMP, symbol SYMBOL, broker INT, qty LONG) TIMESERIES(time) PARTITION BY DAY;
INSERT INTO trades VALUES
  ('2026-09-10T11:02:00+05:45', 'COAST', 58, 10),
  ('2026-09-10T11:00:00+05:45', 'CITY', 45, 20),
  ('2026-09-10T11:01:00+05:45', 'COAST', 34, 30),
  ('2026-09-10T11:00:00+05:45', 'VOLT', 1, 40);

SELECT * FROM trades;
EXPLAIN SELECT * FROM trades WHERE symbol = 'COAST';

Result:

SELECT * FROM trades

(4 rows)

EXPLAIN SELECT * FROM trades WHERE symbol = 'COAST'

(4 rows)

Example: SORT BY (time): rows come in time order, but a symbol filter reads everything (sample data: sample)

Setup:

CREATE TABLE trades (time TIMESTAMP, symbol SYMBOL, broker INT, qty LONG)
  TIMESERIES(time) PARTITION BY DAY SORT BY (time);
INSERT INTO trades VALUES
  ('2026-09-10T11:02:00+05:45', 'COAST', 58, 10),
  ('2026-09-10T11:00:00+05:45', 'CITY', 45, 20),
  ('2026-09-10T11:01:00+05:45', 'COAST', 34, 30),
  ('2026-09-10T11:00:00+05:45', 'VOLT', 1, 40);

SELECT * FROM trades;
EXPLAIN SELECT * FROM trades WHERE symbol = 'COAST';

Result:

SELECT * FROM trades

(4 rows)

EXPLAIN SELECT * FROM trades WHERE symbol = 'COAST'

(3 rows)

Example: SORT BY needs real columns (sample data: sample; shows an error)

CREATE TABLE bad (time TIMESTAMP, symbol SYMBOL) TIMESERIES(time) SORT BY (sym);

Result:

Error: line 1, column 76: SORT BY column 'sym' does not exist; expected one of the columns of 'bad'

DEDUP KEY

Syntax:

CREATE TABLE name (...) TIMESERIES(time_column)
    [PARTITION BY ...] [SORT BY (...)]
    DEDUP KEY (column [, column ...], time_column)

INSERT INTO name VALUES (...)              -- a duplicate: error, nothing is saved (see dedup_insert)
INSERT OR REPLACE INTO name VALUES (...)   -- a duplicate: the old row is replaced
INSERT OR IGNORE INTO name VALUES (...)    -- a duplicate: the new row is skipped

Says which columns make a row unique. Two rows with the same values in all DEDUP KEY columns are the same row. DEDUP KEY (symbol, time) means "one row per symbol per time".

• Returns: messages like 1 row(s) inserted, 1 replaced or 0 row(s) inserted, 1 skipped (already there).
• NULL: NULL values in key columns are compared like values: two rows with the same time and a NULL symbol are duplicates.
• Notes:
  • The DEDUP KEY must include the time column. KDB finds duplicates inside one partition, and the time decides the partition. A stored column holding the time's minute may take its place: see a key on the minute.
  • Without a DEDUP KEY, every INSERT adds rows, also exact copies. Load the same file twice and you have every row twice. Such a table loads fast: nothing is looked up (see how loads find duplicates).
  • Duplicates inside one statement count too: with INSERT OR REPLACE, the last one wins.
  • INSERT OR REPLACE replaces the whole row: columns you do not give become their DEFAULT or NULL. To change some columns only, use UPDATE.
  • With ROUND 1d or ROUND 1m on the time column, two times in the same day or minute become equal, so they are duplicates (create-table).
  • Time-series tables cannot have a PRIMARY KEY. Use DEDUP KEY (..., time).
  • Tip: give every time-series table that you import into a DEDUP KEY and load with INSERT OR REPLACE. Then you can run the same import again and again (insert).

Example: Live snapshots: a snapshot sent again replaces the old one (sample data: sample)

Setup:

CREATE TABLE snapshots (time TIMESTAMP, symbol SYMBOL, ltp DOUBLE, volume LONG)
  TIMESERIES(time) PARTITION BY DAY DEDUP KEY (symbol, time);
INSERT INTO snapshots VALUES ('2026-09-10T11:05:00+05:45', 'CITY', 513.0, 65850);

INSERT OR REPLACE INTO snapshots VALUES
  ('2026-09-10T11:05:00+05:45', 'CITY', 513.2, 66010),
  ('2026-09-10T11:06:00+05:45', 'CITY', 513.5, 67000);
INSERT OR IGNORE INTO snapshots VALUES ('2026-09-10T11:05:00+05:45', 'CITY', 999, 1);
SELECT * FROM snapshots;

Result:

INSERT OR REPLACE INTO snapshots VALUES ('2026-09-10T11:05:00+05:45', 'CITY', 513.2, 66010), ('2026-

1 row(s) inserted, 1 replaced

INSERT OR IGNORE INTO snapshots VALUES ('2026-09-10T11:05:00+05:45', 'CITY', 999, 1)

0 row(s) inserted, 1 skipped (already there)

SELECT * FROM snapshots

(2 rows)

Example: A plain INSERT of a duplicate is refused (sample data: sample; shows an error)

INSERT INTO ochl_day VALUES ('2026-09-10', 'CITY', 504.6, 507.0, 500.7, 504.9, 183040);

Result:

Error: a row with symbol='CITY', time='2026-09-10T00:00:00.000Z' already exists in 'ochl_day'. Use INSERT OR REPLACE to overwrite it, or INSERT OR IGNORE to skip it

Example: Trade log: the same transaction twice in one import keeps one row (the last) (sample data: sample)

INSERT OR REPLACE INTO trade_log (date, transaction_id, symbol, buyer, seller, quantity, rate) VALUES
  ('2026-09-11', 200001, 'CITY', 45, 1, 10, 515.0),
  ('2026-09-11', 200001, 'CITY', 45, 1, 10, 516.0);
SELECT date, transaction_id, symbol, quantity, rate, amount FROM trade_log WHERE date IN '2026-09-11';

Result:

INSERT OR REPLACE INTO trade_log (date, transaction_id, symbol, buyer, seller, quantity, rate) VALUE

1 row(s) inserted, 1 replaced

SELECT date, transaction_id, symbol, quantity, rate, amount FROM trade_log WHERE date IN '2026-09-11

(1 row)

Example: Without a DEDUP KEY every copy is kept (sample data: sample)

Setup:

CREATE TABLE raw_ticks (time TIMESTAMP, symbol SYMBOL, price DOUBLE) TIMESERIES(time) PARTITION BY DAY;

INSERT INTO raw_ticks VALUES ('2026-09-10T11:00:00+05:45', 'CITY', 510);
INSERT INTO raw_ticks VALUES ('2026-09-10T11:00:00+05:45', 'CITY', 510);
SELECT count(*) AS rows_kept FROM raw_ticks;

Result:

INSERT INTO raw_ticks VALUES ('2026-09-10T11:00:00+05:45', 'CITY', 510)

1 row(s) inserted

INSERT INTO raw_ticks VALUES ('2026-09-10T11:00:00+05:45', 'CITY', 510)

1 row(s) inserted

SELECT count(*) AS rows_kept FROM raw_ticks

(1 row)

Example: A market index: one value per minute, DEDUP KEY on the time alone (sample data: sample)

Setup:

CREATE TABLE market_index (time TIMESTAMP ROUND 1m, value DOUBLE)
  TIMESERIES(time) PARTITION BY MONTH DEDUP KEY (time);

INSERT OR REPLACE INTO market_index VALUES
  ('2026-09-10T11:00:05+05:45', 2840.1),
  ('2026-09-10T11:00:40+05:45', 2841.5),
  ('2026-09-10T11:01:10+05:45', 2842.0);
SELECT * FROM market_index;

Result:

INSERT OR REPLACE INTO market_index VALUES ('2026-09-10T11:00:05+05:45', 2840.1), ('2026-09-10T11:00

2 row(s) inserted, 1 replaced

SELECT * FROM market_index

(2 rows)

Example: An UPDATE may not make a duplicate (sample data: sample; shows an error)

UPDATE ochl_day SET time = '2026-09-09' WHERE symbol = 'CITY' AND time IN '2026-09-10';

Result:

Error: the update would create a second row with symbol='CITY', time='2026-09-09T00:00:00.000Z' in 'ochl_day'

Example: The DEDUP KEY must include the time column (sample data: sample; shows an error)

CREATE TABLE bad (time TIMESTAMP, symbol SYMBOL, close DOUBLE) TIMESERIES(time) DEDUP KEY (symbol);

Result:

Error: DEDUP KEY must include the time column 'time', e.g. DEDUP KEY (symbol, time), or a column GENERATED ALWAYS AS (date_trunc('minute', time)) STORED

Ragged seconds: a DEDUP KEY on the minute

Syntax:

minute TIMESTAMP GENERATED ALWAYS AS (date_trunc('minute', time)) STORED
... DEDUP KEY (symbol, minute)

A feed of minute candles often sends the same minute with different seconds: 11:01:07, then 11:01:42. With DEDUP KEY (symbol, time) those are two rows. Put the minute in a stored generated column and make that the key: then both are the same row, and the later one replaces the earlier one.

• Notes:
  • The time column keeps the real time of the row (11:01:42). Only the key uses the minute.
  • The column must be date_trunc('<unit>', <time column>), STORED, with a unit of minute, second or ms. An hour is refused: in Nepal (+05:45) a local hour can start before midnight UTC and end after it, so it would reach into another day's partition.
  • A plain INSERT of the same minute fails like any repeated key. Use INSERT OR REPLACE, or give the table WITH (dedup_insert = 'upsert') (above). POST /write and /imp always replace.
  • Such a table checks its keys row by row, so a bulk load into it is slower than into a table keyed on the time itself.
  • An existing table moves to the minute with ALTER TABLE ... ADD COLUMN and then ALTER TABLE ... DEDUP ENABLE UPSERT KEYS (...). Rows of one minute are folded then: the newest stays.

Example: Minute candles: 11:01:07 and 11:01:42 of one symbol keep one row, the later (sample data: sample)

Setup:

CREATE TABLE candle_1m (time TIMESTAMP, symbol SYMBOL, close DOUBLE,
    minute TIMESTAMP GENERATED ALWAYS AS (date_trunc('minute', time)) STORED)
  TIMESERIES(time) PARTITION BY DAY DEDUP KEY (symbol, minute) WITH (dedup_insert = 'upsert');

INSERT INTO candle_1m (time, symbol, close) VALUES ('2026-09-25T11:01:07+05:45', 'CITY', 510.0);
INSERT INTO candle_1m (time, symbol, close) VALUES ('2026-09-25T11:01:42+05:45', 'CITY', 512.5),
                                                ('2026-09-25T11:02:03+05:45', 'CITY', 513.0);
SELECT time, symbol, close, minute FROM candle_1m ORDER BY time;

Result:

INSERT INTO candle_1m (time, symbol, close) VALUES ('2026-09-25T11:01:07+05:45', 'CITY', 510.0)

1 row(s) inserted

INSERT INTO candle_1m (time, symbol, close) VALUES ('2026-09-25T11:01:42+05:45', 'CITY', 512.5), ('2

1 row(s) inserted, 1 replaced

SELECT time, symbol, close, minute FROM candle_1m ORDER BY time

(2 rows)

Example: Move a table that is there to the minute: seconds of one minute fold into the newest row (sample data: sample)

Setup:

CREATE TABLE ticks_1m (time TIMESTAMP, symbol SYMBOL, close DOUBLE)
  TIMESERIES(time) PARTITION BY DAY DEDUP KEY (symbol, time);
INSERT INTO ticks_1m VALUES ('2026-09-25T11:01:07+05:45', 'CITY', 510.0), ('2026-09-25T11:01:42+05:45', 'CITY', 512.5);

ALTER TABLE ticks_1m ADD COLUMN minute TIMESTAMP GENERATED ALWAYS AS (date_trunc('minute', time)) STORED;
ALTER TABLE ticks_1m DEDUP ENABLE UPSERT KEYS (symbol, minute);
SELECT time, close FROM ticks_1m;

Result:

ALTER TABLE ticks_1m ADD COLUMN minute TIMESTAMP GENERATED ALWAYS AS (date_trunc('minute', time)) ST

table 'ticks_1m' altered (2 row change(s) written)

ALTER TABLE ticks_1m DEDUP ENABLE UPSERT KEYS (symbol, minute)

table 'ticks_1m' now dedups on (symbol, minute) (1 row(s) rewritten, 1 duplicate row(s) folded: the newest row of each key was kept)

SELECT time, close FROM ticks_1m

(1 row)

Example: An hour is too coarse to stand in for the time (sample data: sample; shows an error)

CREATE TABLE candle_1h (time TIMESTAMP, symbol SYMBOL, close DOUBLE,
    hour TIMESTAMP GENERATED ALWAYS AS (date_trunc('hour', time)) STORED)
  TIMESERIES(time) PARTITION BY DAY DEDUP KEY (symbol, hour);

Result:

Error: DEDUP KEY must include the time column 'time', e.g. DEDUP KEY (symbol, time), or a column GENERATED ALWAYS AS (date_trunc('minute', time)) STORED

A plain INSERT that replaces: dedup_insert

Syntax:

CREATE TABLE name (...) TIMESERIES(time) ... DEDUP KEY (...) WITH (dedup_insert = 'upsert')
ALTER TABLE name SET (dedup_insert = 'upsert')    -- a plain INSERT replaces the row with the same key
ALTER TABLE name SET (dedup_insert = 'error')     -- back to the default: it fails
ALTER TABLE name RESET (dedup_insert)             -- the same as 'error'

In QuestDB a plain INSERT into a DEDUP UPSERT KEYS table replaces the row with the same key. On a table made with KDB's own DEDUP KEY (...) it fails, so you do not overwrite a row by mistake. A table made with QuestDB's words, DEDUP UPSERT KEYS (...), gets QuestDB's behaviour: dedup_insert = 'upsert' is set by itself (since 2026-09-25), so an app written for QuestDB keeps its plain INSERTs unchanged. Any table can switch with ALTER TABLE ... SET (dedup_insert = ...).

• Returns: table 'name': a plain INSERT of a DEDUP KEY that is there replaces the row (dedup_insert = 'upsert').
• Notes:
  • Only a table with a DEDUP KEY can have it. A table with a PRIMARY KEY takes INSERT OR REPLACE or INSERT ... ON CONFLICT.
  • It works for every way rows come in: SQL INSERT, a PostgreSQL COPY, POST /write and /imp.
  • INSERT OR IGNORE still skips, and INSERT ... ON CONFLICT still does what it says.
  • SHOW CREATE TABLE shows it as WITH (dedup_insert = 'upsert'). DEDUP DISABLE turns it off with the key.
  • A table made with DEDUP UPSERT KEYS before 2026-09-25 keeps the rule it had (fails); ALTER TABLE t SET (dedup_insert = 'upsert') changes it. ALTER TABLE t DEDUP ENABLE UPSERT KEYS (...) does not change it.
  • New rows cost nothing extra: the setting is looked at only when a key is already there.
  • A database with such a table needs KDB 2026-09-24 or newer (feature dedup_insert, see versions).

Example: A price feed written for QuestDB: plain INSERTs replace the tick (sample data: sample)

Setup:

CREATE TABLE ltp (time TIMESTAMP, symbol SYMBOL, price DOUBLE)
  TIMESERIES(time) PARTITION BY DAY DEDUP KEY (symbol, time) WITH (dedup_insert = 'upsert');

INSERT INTO ltp VALUES ('2026-09-24T11:00:00+05:45', 'CITY', 510.0);
INSERT INTO ltp VALUES ('2026-09-24T11:00:00+05:45', 'CITY', 512.5), ('2026-09-24T11:01:00+05:45', 'CITY', 513.0);
SELECT * FROM ltp;

Result:

INSERT INTO ltp VALUES ('2026-09-24T11:00:00+05:45', 'CITY', 510.0)

1 row(s) inserted

INSERT INTO ltp VALUES ('2026-09-24T11:00:00+05:45', 'CITY', 512.5), ('2026-09-24T11:01:00+05:45', '

1 row(s) inserted, 1 replaced

SELECT * FROM ltp

(2 rows)

Example: Turn it on for a table that is there (sample data: sample)

Setup:

CREATE TABLE ltp (time TIMESTAMP, symbol SYMBOL, price DOUBLE)
  TIMESERIES(time) PARTITION BY DAY DEDUP KEY (symbol, time);
INSERT INTO ltp VALUES ('2026-09-24T11:00:00+05:45', 'CITY', 510.0);

ALTER TABLE ltp SET (dedup_insert = 'upsert');
INSERT INTO ltp VALUES ('2026-09-24T11:00:00+05:45', 'CITY', 511.0);
SELECT * FROM ltp;

Result:

ALTER TABLE ltp SET (dedup_insert = 'upsert')

table 'ltp': a plain INSERT of a DEDUP KEY that is there replaces the row (dedup_insert = 'upsert')

INSERT INTO ltp VALUES ('2026-09-24T11:00:00+05:45', 'CITY', 511.0)

0 row(s) inserted, 1 replaced

SELECT * FROM ltp

(1 row)

Example: A table without a DEDUP KEY cannot have it (sample data: sample; shows an error)

Setup:

CREATE TABLE accounts (id INT PRIMARY KEY, balance DOUBLE);

ALTER TABLE accounts SET (dedup_insert = 'upsert');

Result:

Error: dedup_insert = 'upsert' needs a DEDUP KEY, and table 'accounts' has none (a table with a PRIMARY KEY takes INSERT OR REPLACE or INSERT ... ON CONFLICT)

Packed partitions: compression

Syntax:

CREATE TABLE name (...) TIMESERIES(time) PARTITION BY DAY ... WITH (compression = on)
ALTER TABLE name SET (compression = on)     -- sealed partitions are stored packed
ALTER TABLE name SET (compression = off)    -- back to the default: stored as plain arrays
ALTER TABLE name RESET (compression)        -- the same as off
COMPRESS TABLE name                         -- how far it is; packs what is left, in the background
COMPRESS TABLE name WAIT                    -- packs what is left now, then answers

A partition is sealed when a newer time bucket exists: yesterday's day, last month's month. Only sealed partitions are packed. The newest one is where rows still arrive, so it stays plain and quick to write again. A packed file keeps the same rows in far fewer bytes: times and ids that go up by small steps, broker numbers, sorted symbols and prices with a few decimals take 1 to 3 bytes instead of 8.

• Returns: ALTER TABLE says what it does. COMPRESS TABLE answers with one row:

• Notes:
  • It is off by default. Nothing changes for a table until you turn it on.
  • Queries answer exactly the same: a packed file is unpacked into the same arrays when it is loaded. Loading reads fewer bytes, so it is often faster; with every partition in memory the speed is the same.
  • After SET (compression = on) the partitions already on disk are packed in the background, one partition at a time, with a pause after each one, while reads and writes go on. Run COMPRESS TABLE name again to see how far it is. SET (compression = off) unpacks them the same way.
  • When the first rows of a new day arrive, yesterday becomes sealed. The server packs it in the background after its next save, with no COMPRESS TABLE and no restart.
  • Rows added to a sealed day later (a late load, UPDATE, DELETE) are fine: the day is written packed again with them.
  • A crash in the middle loses nothing: a packed file replaces the old one only once it is complete, like every file KDB writes.
  • A table without time partitions has no sealed partition, so nothing is packed.
  • A database that has used it needs KDB 2026-09-25 or newer (feature column_compression, see versions). An older KDB refuses it with "needs a newer KDB", never misreads it. kdb downgrade writes packed files plain again once the table is set to off.

Example: Pack the old days of a trade table (sample data: sample)

Setup:

CREATE TABLE trades (time TIMESTAMP, symbol SYMBOL, qty LONG, price DOUBLE)
  TIMESERIES(time) PARTITION BY DAY;
INSERT INTO trades VALUES ('2026-09-21T10:00:00Z', 'NABIL', 100, 512.5), ('2026-09-21T10:01:00Z', 'NABIL', 20, 513.0),
  ('2026-09-22T10:00:00Z', 'CITY', 50, 99.5), ('2026-09-23T10:00:00Z', 'NABIL', 10, 515.0);

ALTER TABLE trades SET (compression = on);
COMPRESS TABLE trades WAIT;
SELECT symbol, sum(qty), max(price) FROM trades GROUP BY symbol;

Result:

ALTER TABLE trades SET (compression = on)

table 'trades': sealed partitions are stored packed (compression = on); the sealed partitions on disk are written again in the background (COMPRESS TABLE trades shows how far, COMPRESS TABLE trades WAIT does it now)

COMPRESS TABLE trades WAIT

(1 row)

table 'trades': 2 sealed partition(s) written packed

SELECT symbol, sum(qty), max(price) FROM trades GROUP BY symbol

(2 rows)

Example: compression takes on or off (sample data: sample; shows an error)

Setup:

CREATE TABLE ticks (time TIMESTAMP, price DOUBLE) TIMESERIES(time) PARTITION BY DAY;

ALTER TABLE ticks SET (compression = small);

Result:

Error: line 1, column 24: compression takes on (sealed partitions are stored packed) or off (the default)

Appending without rewriting: tail files

Syntax:

kdb serve --tail-files on       -- or KDB_TAIL_FILES=on; off by default

Every save (checkpoint) used to write each changed partition whole. On PARTITION BY YEAR, SYMBOL(symbol) one more month of candles wrote each symbol's whole year again, and a live feed touching every symbol wrote every year file again at each save. Most of the bytes written, and kept in the trash, were rows already on disk.

With tail files on, a partition whose new rows all come after its last saved row gets them in a small extra file, a tail, next to its file. Its file is not read or written. Reads put the file and its tails together, so every query answers exactly the same.

• Notes:
  • It is off by default while it is new. Nothing changes until you turn it on.
  • "After the last saved row" is in the table's key order (SORT BY, or time then row id): for SORT BY (symbol, time) in a SYMBOL(symbol) partition, a later time.
  • A crash in the middle loses nothing: a tail is listed only once it is complete, like every file KDB writes. A tail cut short by a power cut was never listed, and the next start removes it.
  • Folding the tails back into the file is an ordinary save too: a crash during it leaves the file and its tails as they were.
  • Backups, restores, branches, the trash and kdb info see tails as ordinary files. Encryption at rest and column compression cover them like any file (a packed, sealed partition is written whole, never given a tail).
  • kdb check also checks that every tail sits behind its partition file, numbered 1, 2, 3 ... with none missing, and is no older than what it extends.
  • GET /api/disk and the console's Keep data page show how much of the data is in tails (tail_bytes, tail_files).
  • A read AS OF an earlier time reads the tails that existed then. When a partition is written whole and its tails go, reads AS OF a time before that are refused for the table, as after TRUNCATE.
  • Turned off again, existing tails are still read; the next change of such a partition writes it whole.
  • A database with tail files needs KDB 2026-10-02 or newer (feature partition_tails, see versions). An older KDB refuses it with "needs a newer KDB". kdb downgrade writes those partitions whole again first and says how many it folded.

Small changes without rewriting: delta files

Syntax:

KDB_NO_PARTITION_DELTA=1        -- turns them off: every change writes the whole partition again

An UPDATE, a DELETE or an INSERT OR REPLACE of a few rows used to make the next save write the whole partition again. One replaced row in a day of a million rows wrote the whole day, and the old day went to the trash. Do that a hundred times and the trash holds a hundred days.

Now, when the changes of a partition are a small share of its rows, the save writes them to one small extra file next to it, its delta: the rows replaced, updated or added, and the keys deleted. The partition's file (and its tails) stay as they are. Reads apply the delta, so every query answers exactly the same.

• Notes:
  • It is on by default. KDB_NO_PARTITION_DELTA=1 in the server's environment turns it off.
  • Each save writes the partition's delta again with everything in it so far, so a partition has one delta, and the bytes written and trashed are those of the changed rows, not of the partition.
  • A partition that is not in memory is still read once for the save (its new row count and summary come from its rows); only the writing is saved.
  • Compaction keeps the cost bounded: the delta bytes a partition writes before it is written whole again are about the size of the partition, never many times it. The count survives a restart.
  • /api/stats counts partition_whole_writes / partition_whole_bytes and partition_delta_writes / partition_delta_bytes since the start. GET /api/disk and the disk part of /api/stats show delta_files / delta_bytes, the delta files on disk now (part of data_bytes).
  • A power cut during a save loses nothing confirmed: a new delta, or the whole file that replaces it, is used only once the manifest names it, and both change in one manifest save.
  • A partition with a delta is always read whole: a per-column load would miss the replaced rows. Its next whole write makes it per-column again.
  • Backups, the trash and kdb info see a delta as an ordinary file. kdb check also reads every partition with a delta and reports one that does not fit its file (made for other rows, keys out of order, a row count other than the saved one) or that has no partition file.
  • A read AS OF an earlier time reads the delta that existed then. When the partition is written whole and its delta goes, reads AS OF a time before that are refused for the table, as for tails.
  • A database with delta files needs KDB 2026-10-02 or newer (feature partition_deltas, see versions). An older KDB refuses it with "needs a newer KDB". kdb downgrade writes those partitions whole again first.

How a load finds duplicates

A load over POST /write or POST /imp builds its rows as columns and writes them in one piece per partition. How much work the duplicate check is depends on the keys:

• Notes:
  • Some tables still go row by row, which is several times slower: a table with an immediate INSERT trigger, a CHECK, REFERENCES, a UNIQUE column or index, an AUTO column, or a BEFORE UPDATE row trigger. A trigger on another table does not slow the load. For a table you load in bulk, make its triggers DEFERRED.
  • A table with no DEDUP KEY is as fast to load as one with a key.
  • KDB_NO_BATCH_DEDUP=1 (read at start) turns the at-once check off: such partitions then go row by row. It is only for comparing the two ways.
  • SQL INSERT statements check key by key as described in DEDUP KEY; the result is the same.

Loads without the duplicate check: dedup=skip

Syntax:

POST /write?...&dedup=skip
POST /imp?...&dedup=skip

A load normally looks up every key in its day, so a day sent again replaces its rows. With dedup=skip the load does not look. Use it only for trusted bulk loads where you know the rows are new.

It matters only for a table whose DEDUP KEY is not the start of its SORT BY (the last row of the table above). For the other tables a load never looks anything up.

• The risk: if a key already exists, you get two rows with the same key. Queries then return both. A row with the same key and the same SORT BY values still replaces the old one; one with other SORT BY values (another symbol) is added next to it.
• Rights: the same as a normal write.
• The activity log says dedup skipped, and the event has dedup_skipped: the rows that went into days which already had rows.
• A table that goes row by row anyway (an immediate INSERT trigger, CHECK, REFERENCES, a UNIQUE column or index, an AUTO column) checks every key: dedup=skip does nothing there.
• dedup=skip cannot be used with upsert=merge.

The same in SQL: WITH (dedup = skip) and SET dedup

Syntax:

INSERT INTO table ... WITH (dedup = skip | on)     -- this statement only
SET dedup = skip | on | DEFAULT                     -- every INSERT and COPY FROM of this session
SHOW dedup                                          -- skip or on
RESET dedup                                         -- back to on

The words are those of ?dedup=. skip means: do not look the key up among the stored rows. A plain INSERT of a stored key then does not fail. Repeats inside the statement are still found.

Which value counts, first match wins:

• Returns: the usual message, then ; dedup = skip: N key(s) not looked up among the stored rows.
• Notes:
  • A stored key written again with skip: when the key is the whole SORT BY, the new row lands on the old one and replaces it. Otherwise the key is there twice. Repair it with DEDUP TABLE.
  • INSERT OR IGNORE, INSERT OR REPLACE and ON CONFLICT need the lookup: with WITH (dedup = skip) they are refused, and a session's skip does not count for them.
  • The same tables as ?dedup=skip ignore it: an immediate INSERT trigger, REFERENCES, a UNIQUE column or index, an AUTO column, a BEFORE UPDATE row trigger. A CHECK is fine.
  • A PostgreSQL COPY ... FROM STDIN takes the connection's SET dedup too.
  • A statement run by a trigger or a function for the INSERT does not take the session's skip.

Example: A day you know is new: write it without the lookups (sample data: sample)

Setup:

CREATE TABLE fills (date TIMESTAMP, id LONG, symbol SYMBOL, qty LONG)
  TIMESERIES(date) PARTITION BY DAY SORT BY (symbol, id) DEDUP KEY (date, id);
INSERT INTO fills VALUES ('2026-09-11', 1, 'CITY', 10);

INSERT INTO fills VALUES ('2026-09-12', 1, 'CITY', 11), ('2026-09-12', 2, 'COAST', 20) WITH (dedup = skip);
SELECT date, id, symbol, qty FROM fills ORDER BY date, id;

Result:

INSERT INTO fills VALUES ('2026-09-12', 1, 'CITY', 11), ('2026-09-12', 2, 'COAST', 20) WITH (dedup =

2 row(s) inserted; dedup = skip: 2 key(s) not looked up among the stored rows

SELECT date, id, symbol, qty FROM fills ORDER BY date, id

(3 rows)

Example: A session that skips, a key sent twice by mistake, and the repair (sample data: sample)

Setup:

CREATE TABLE fills (date TIMESTAMP, id LONG, symbol SYMBOL, qty LONG)
  TIMESERIES(date) PARTITION BY DAY SORT BY (symbol, id) DEDUP KEY (date, id);
INSERT INTO fills VALUES ('2026-09-11', 1, 'CITY', 10);

SET dedup = skip;
SHOW dedup;
INSERT INTO fills VALUES ('2026-09-11', 1, 'COAST', 12);
SELECT count(*) AS rows_for_id_1 FROM fills WHERE id = 1;
DEDUP TABLE fills FOR '2026-09-11';
RESET dedup;
SELECT id, symbol, qty FROM fills;

Result:

SET dedup = 'skip'

INSERTs and COPY FROM of this session do not look keys up among the stored rows (dedup = skip); a key that is stored already is then overwritten or there twice, DEDUP TABLE repairs it

SHOW dedup

(1 row)

INSERT INTO fills VALUES ('2026-09-11', 1, 'COAST', 12)

1 row(s) inserted; dedup = skip: 1 key(s) not looked up among the stored rows

SELECT count(*) AS rows_for_id_1 FROM fills WHERE id = 1

(1 row)

DEDUP TABLE fills FOR '2026-09-11'

1 duplicate row(s) removed from 1 of 1 partition(s) of 'fills'

SET dedup = 'default'

INSERTs of this session look every key up again (dedup = on)

SELECT id, symbol, qty FROM fills

(1 row)

Example: OR IGNORE needs the lookup (sample data: sample; shows an error)

Setup:

CREATE TABLE fills (date TIMESTAMP, id LONG, symbol SYMBOL, qty LONG)
  TIMESERIES(date) PARTITION BY DAY SORT BY (symbol, id) DEDUP KEY (date, id);

INSERT OR IGNORE INTO fills VALUES ('2026-09-11', 1, 'CITY', 10) WITH (dedup = skip);

Result:

Error: line 1, column 66: INSERT OR IGNORE cannot be used with WITH (dedup = skip): it looks every key up, and skip means no lookup

DEDUP TABLE

Syntax:

DEDUP TABLE table [FOR 'time']

Removes rows that repeat a DEDUP KEY. Of the rows with the same key, the one written last stays, as a normal load would have kept it. Use it to repair a load made with dedup=skip.

• Returns: N duplicate row(s) removed from X of Y partition(s) of 'table'.
• Notes:
  • It is a DELETE of the extra rows: it needs the same rights, runs DELETE triggers, and is saved like any change.
  • Without FOR it reads the whole table. Give the day when you know it.

Example: Repair a day: nothing to remove in a table loaded normally (sample data: sample)

Setup:

CREATE TABLE fills (date TIMESTAMP, id LONG, symbol SYMBOL, qty LONG)
  TIMESERIES(date) PARTITION BY DAY SORT BY (symbol, id) DEDUP KEY (date, id);
INSERT OR REPLACE INTO fills VALUES ('2026-09-11', 1, 'CITY', 10), ('2026-09-11', 2, 'COAST', 20);

DEDUP TABLE fills FOR '2026-09-11';
SELECT id, symbol, qty FROM fills ORDER BY id;

Result:

DEDUP TABLE fills FOR '2026-09-11'

0 duplicate row(s) removed from 0 of 1 partition(s) of 'fills'

SELECT id, symbol, qty FROM fills ORDER BY id

(2 rows)

Example: DEDUP TABLE needs a DEDUP KEY (sample data: sample; shows an error)

CREATE TABLE raw (time TIMESTAMP, v LONG) TIMESERIES(time) PARTITION BY DAY;
DEDUP TABLE raw;

Result:

table 'raw' created

Error: DEDUP TABLE needs a time-series table with a DEDUP KEY; 'raw' has none

DROP PARTITION

Syntax:

ALTER TABLE table DROP PARTITION 'name' [, 'name' ...]
ALTER TABLE table DROP PARTITION OLDER THAN n HOURS | DAYS | WEEKS | MONTHS | YEARS

Deletes whole partitions: all rows of a day, month or year at once. It removes files, so it is much faster than DELETE for old data. OLDER THAN picks the partitions whose last moment is more than that long ago (alter-table); a TTL does the same by itself.

• Returns: N partition(s) of 'table' dropped.
• Notes:
  • The name must be a partition that exists. If not, the error lists the partitions the table has. That is an easy way to see them.
  • The name is a UTC date (see PARTITION BY).
  • All names are checked first. When one is wrong, nothing is dropped.
  • Not for relational tables and not for PARTITION BY NONE tables. There, use DELETE FROM table WHERE ....
  • QuestDB's DROP PARTITION LIST '2026-09' works too (with LIST a partition that is not there is skipped, as in QuestDB), and so does DROP PARTITION WHERE time < '2026-09-01', which drops each partition whose whole time range matches (alter-table).
  • There is no undo. Make a backup first when the data matters.
  • DELETE FROM t WHERE time IN '2025' removes the same rows, one by one (slower, but works for any condition).
  • With SYMBOL(col) partitions, the name is the time part: '2025' drops every symbol of 2025.
  • To empty the whole table fast, use TRUNCATE: it drops every partition the same way.

Example: Delete the 2025 daily candles (sample data: sample)

SELECT count(*) AS rows_2025 FROM ochl_day WHERE time IN '2025';
ALTER TABLE ochl_day DROP PARTITION '2025';
SELECT count(*) AS rows_2025 FROM ochl_day WHERE time IN '2025';

Result:

SELECT count(*) AS rows_2025 FROM ochl_day WHERE time IN '2025'

(1 row)

ALTER TABLE ochl_day DROP PARTITION '2025'

1 partition(s) of 'ochl_day' dropped

SELECT count(*) AS rows_2025 FROM ochl_day WHERE time IN '2025'

(1 row)

Example: Trade log: delete two days at once (sample data: sample)

ALTER TABLE trade_log DROP PARTITION '2026-09-09', '2026-09-10';
SELECT count(*) AS trades_left FROM trade_log;

Result:

ALTER TABLE trade_log DROP PARTITION '2026-09-09', '2026-09-10'

2 partition(s) of 'trade_log' dropped

SELECT count(*) AS trades_left FROM trade_log

(1 row)

Example: A wrong name: the message lists the partitions (sample data: sample; shows an error)

ALTER TABLE ochl_day DROP PARTITION '2026-09';

Result:

Error: table 'ochl_day' has no partition '2026-09'; partitions: 2025, 2026

Example: The name needs quotes (sample data: sample; shows an error)

ALTER TABLE ochl_day DROP PARTITION 2025;

Result:

Error: line 1, column 37: expected a partition value in single quotes, e.g. '2026-09' but found '2025'

Example: Relational tables have no partitions (sample data: sample; shows an error)

ALTER TABLE brokers DROP PARTITION '2026';

Result:

Error: cannot drop partitions of table 'brokers': it is a relational table; only time-series tables have partitions