QuestDB 10.2.0

QuestDB 10.2.0 adds GROUPING SETS, ROLLUP and CUBE, row-level retention for materialized views, a DEDUP SKIP mode that keeps the first-written row, and opt-in power-loss durability for WAL tables, enabled with cairo.commit.mode=adaptive. QWP ingestion of small messages is much faster, and this release fixes a large batch of wrong-result, crash and hang bugs in queries, out-of-order ingestion and Parquet partitions.

This release contains a few breaking changes. Read the Breaking changes section before you upgrade, especially if you create materialized views with LIMIT or window functions, send the designated timestamp twice over QWP, or may need to downgrade after using DEDUP SKIP.

For any questions or feedback, please join us on Slack or on Discourse.

See also our prettier release notes page.

Breaking changes :boom:

• CREATE MATERIALIZED VIEW rejects LIMIT in queries that read the base table, and base-table window functions nested in expressions or SAMPLE BY subqueries. Replaying older SHOW CREATE output that contains them fails. #7263
• QWP rejects table blocks that carry the designated timestamp twice, once through at(...) and once as a named column. Before this change, QuestDB accepted them and stored wrong timestamps. A sender that uses both forms on one table now gets a schema error on every later batch for that table. #8052
• An unknown cairo.commit.mode value now fails startup, instead of silently selecting nosync. #7411
• After 10.2.0 has run on a database, downgrading, writing with the older version and upgrading again is not supported. See downgrade to an earlier release. #7411
• Downgrading after using DEDUP SKIP: older versions treat SKIP tables as UPSERT without an error, and suspend WAL apply when they replay a logged ALTER TABLE ... DEDUP SKIP. #7885

Highlights

GROUPING SETS, ROLLUP and CUBE

GROUP BY and SAMPLE BY now accept ROLLUP, CUBE and GROUPING SETS, with the GROUPING() and GROUPING_ID() functions. One query returns subtotals and a grand total in a single scan of the input:

:::sql
SELECT symbol, side, sum(amount)
FROM trades
GROUP BY ROLLUP(symbol, side);

Grouping sets run in parallel where the plan allows. SAMPLE BY grouping sets support FILL with NULL, constants or NONE. cairo.sql.max.grouping.sets caps how many grouping sets one query expands to (default 4096). Grouping sets do not vectorize: they ran 1.4x slower than the equivalent UNION ALL of plain GROUP BYs serially, and up to 6.9x slower in parallel, in the PR's benchmark. #6853

Passthrough views and row-level retention

Materialized views can now be passthrough views: a filtered copy of a table (SELECT ... WHERE, no SAMPLE BY), kept up to date as rows arrive. The new EXPIRE ROWS policy adds row-level retention on top, and hides expired rows of a materialized view from every query: WHEN <predicate>, KEEP LATEST, KEEP [N] HIGHEST|LOWEST, or a window predicate. For monotonic WHEN policies, such as ts < now(), a background job also frees the disk; cairo.mat.view.row.expiry.cleanup.* settings control it. ALTER MATERIALIZED VIEW ... SET EXPIRE ROWS and ... DROP EXPIRE change a view's policy. #7263

DEDUP SKIP: keep the first row

The new DEDUP SKIP mode keeps the first-written row and drops later duplicates, where UPSERT keeps the last one. CREATE TABLE, CTAS and ALTER TABLE ... DEDUP accept it, and SHOW CREATE TABLE prints it. Commits that hold only duplicates leave native and Parquet partitions untouched, and resending unchanged rows into DEDUP UPSERT Parquet partitions no longer rewrites them. #7885

Power-loss durability for WAL tables (opt-in)

cairo.commit.mode=adaptive makes WAL tables survive power loss for much less than sync mode costs. QuestDB flushes the WAL on commit, applies it to table files without per-apply flushes, and rebuilds the table files from the WAL after a crash, so the server restarts with consistent tables instead of failing on a damaged partition. cairo.adaptive.commit.group.window (default 50 ms) batches flushes across commits, so commits acknowledged within the last window can be lost. Pair it with request_durable_ack=local on QWP clients (local,replicated on QuestDB Enterprise) to be told when each batch is on disk, or set the window to 0. The default stays nosync. #7411

Faster ingestion

• QWP over WebSocket ingests one-row messages at about 3.9M rows/s, up from about 210k, on the PR's benchmark. Large messages are unchanged. The wire protocol and ack semantics are unchanged. #8110
• WAL apply no longer falls back to slow one-by-one mode on backlogs of small out-of-order transactions that span a wide timestamp range, where it could fall behind ingestion indefinitely. #8008
• With cairo.file.async.munmap.enabled, a released file mapping stays cached until the background job unmaps it, so reopening the same file reuses it without a new mmap. #7751

Storage and operations

• Repair Parquet indexes after upgrading from 9.3.4-10.0.1. Those versions could leave stale metadata after merging out-of-order rows into Parquet partitions, so index scans could miss rows and deduplication could keep duplicates. ALTER TABLE ... FORCE REINDEX PARQUET repairs affected partitions online; ADD INDEX and out-of-order writes repair them automatically. Until a partition is repaired, index queries on it fail with index does not cover parquet partition data instead of returning wrong rows. #8139
• Quiet QWP connections no longer block WAL cleanup. A connection idle for qwp.min.idle.ms.before.writer.release (default 30s, 0 disables) releases its WAL writers. #7936
• WAL apply no longer pauses when partition compaction holds the table writer. #8194

Correctness fixes

This release fixes wrong results, crashes and hangs in RIGHT/FULL hash joins, SPLICE JOIN, WINDOW JOIN, set operations over geohashes, SAMPLE BY with month and year strides, LIMIT and FILL, and parallel filtered queries under concurrent load. It also fixes queries returning wrong rows during out-of-order ingestion, index scans on Parquet partitions, covering indexes returning NULLs after restore or column changes, materialized views after a base column type change, crashes and hangs when an out-of-order commit fails, and querying narrow DECIMAL columns over QWP.

Changelog

Features

• feat(sql): add GROUPING SETS, ROLLUP, CUBE support by @javier in #6853
• feat(core): breaking change 💥 - add row-level retention to materialized views by @nwoolmer in #7263
• feat(sql): breaking change 💥 - add DEDUP SKIP mode to keep existing rows on duplicate keys by @bluestreak01 in #7885
• feat(core): Introduce new adaptive commit mode by @nwoolmer in #7411

Performance

• perf(qwp): speed up QWP WebSocket ingestion of small messages by @jerrinot in #8110
• perf(core): treat async munmap as an eviction hint, revive released mappings by @ideoma in #7751

Fixes

• fix(sql): fix INSERT ... SELECT with a column list naming the wrong column in type errors by @eugenels in #7992
• fix(core): fix crash and hangs when an out-of-order commit fails by @bluestreak01 in #8063
• fix(http): fix client timeouts when following redirects by @jerrinot in #8084
• fix(sql): fix wrong SAMPLE BY results for month and year strides, LIMIT and mixed FILL by @puzpuzpuz in #8053
• fix(sql): fix wrong values in materialized views after a base column type change by @eugenels in #8039
• fix(sql): fix crashes and wrong results in RIGHT/FULL hash joins, SPLICE JOIN and keyed SAMPLE BY FILL by @ideoma in #8109
• fix(core): release native column files after converting a split active partition to Parquet by @RaphDal in #8065
• fix(core): fix queries returning wrong rows or failing during out-of-order ingestion by @jerrinot in #7935
• fix(core): fix covering index returning NULLs after restore and column changes by @ideoma in #8117
• fix(qwp): fix quiet QWP connections blocking WAL cleanup by @jerrinot in #7936
• fix(core): fix WAL apply pausing when partition compaction holds the table writer by @kafka1991 in #8194
• fix(core): fix wrong rows from index scans and conversions of parquet partitions by @ideoma in #7988
• fix(core): fix WAL apply falling back to slow 1-by-1 mode on backlogs with wide timestamp range by @ideoma in #8008
• fix(sql): fix wrong results and internal errors in WINDOW JOIN, set operations, SAMPLE BY FILL, string functions and UDP ingestion by @eugenels in #8001
• chore(qwp): breaking change 💥 - reject conflicting designated timestamps by @RaphDal in #8052
• fix(qwp): fix querying DECIMAL columns with precision up to 9 over QWP egress by @sklarsa in #8134
• fix(core): fix missing indexed rows after changing column types on Parquet tables by @ideoma in #8236
• fix(sql): fix SAMPLE BY FILL(LINEAR) returning NULL between large DOUBLE values by @eugenels in #8042
• fix(core): fix checkpoints of altered views and flaky cookie expiry tests by @glasstiger in #8248
• fix(core): fix parquet index repair and deduplication after upgrades by @ideoma in #8139
• fix(core): fix parallel queries hanging under concurrent load and skipping rows in batched PG Wire fetches by @kafka1991 in #8278

Full Changelog: 10.1.0...10.2.0