QuestDB 9.4.1

QuestDB 9.4.1 is a hardening release that builds on 9.4.0. Introduction of our new SQL fuzz-testing engine and test framework improvements has flushed out more than 60 latent bugs around query correctness and resource leaks. 9.4.1 also brings additional hardening and bugfixes for the newly released posting/covering indexes, performance enhancements for materialised views, upgrades to parquet querying, and new aggregate and window functions.

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

See also our prettier release notes page.

Breaking changes :boom:

• Parquet metadata files (_pm) will be migrated to a new version at startup.
• Calculations of the shape INT - FLOAT now returns FLOAT type, not DOUBLE.
• Nested INT calculations compared to LONG values will now run at 64-bit width.
• STRING and VARCHAR with <=/>= null comparisons will now correctly return matching null rows.
• twap() now correctly rejects non-designated timestamps.
• EXPLAIN plan output has changed for non-keyed group by/sample by, if you assert a particular shape of the query plan, you'll need to update your assertions.

Highlights

New SQL Query fuzzer

QuestDB contains almost 1 million lines of test code, covering unit, integration, fuzz, and performance tests. This release includes a new fuzz testing mechanism specifically to improve the robustness of the SQL engine. This enhances our existing fuzz testing suite that runs 24/7, ensuring rare edge cases or flaky bugs are reproduced and fixed at the earliest opportunity.

Additionally, we've revised our unit testing API to ensure that additional assertions are executed and checked by default, closing some under-asserted tests.

So far, this has caught more than 60 bugs, and we're looking forward to finding and fixing more!

New SQL functions

array_agg()

A new aggregate that collects/rolls-up per-group values into a DOUBLE[]:

SELECT symbol, array_agg(price) FROM trades SAMPLE BY 1m;

See the aggregate functions reference and the array type.

regr_r2()

regr_r2(y, x) is the standard SQL coefficient of determination — how well a linear regression of Y on X fits, on a 0–1 scale. It is handy for separating real trends from noise, e.g. finding sensors whose temperature is genuinely trending up versus drifting randomly.

Window functions for DECIMAL columns

All six DECIMAL sub-types (D8–D256) now work with the window functions first_value, last_value, nth_value, lag, lead, min, max, count, sum, and avg — across the same frame shapes (whole partition, ROWS BETWEEN, RANGE BETWEEN, partitioned and non-partitioned) supported for the primitive numeric types.

See the window functions reference and the DECIMAL type.

Opt-in native memory caps for window functions and ORDER BY

Users can now opt-in to memory caps for SQL queries that rely on window functions and sorts:

cairo.sql.window.cache.max.bytes
cairo.sql.window.rowid.max.bytes
cairo.sql.window.tree.max.bytes
cairo.sql.sort.key.max.bytes
cairo.sql.sort.light.value.max.bytes
cairo.sql.sort.value.max.bytes

This will help avoid any instability caused by edge case queries; for example, when a window function tries to sort a table with billions of rows.

Performance

Materialized Views

Refreshing materialised views during an out-of-order backfill will is much faster and more efficient. A new adaptive algorithm balances the number of rows refreshed versus the number of commits, ensuring that performance is maintained across varied or dynamic workloads. Refreshing a 512 symbol view with a constant -12 hours backfill plus live ingestion dropped from ~160ms to just ~2ms, with consistent performance.

Materialized Views will also now discard no-op transactions much earlier, avoiding wasted work.

SQL

• 2-3x speedup for twap() and sparkline. This has a small memory overhead for very high cardinality group by queries, approximately ~15%.
• Non-keyed group by/sample will now always run in parallel.
• 1.5-2x faster covering index builds over parquet partitions.

Bug fixes

SQL

• Fixed corr, stddev, var, and covar aggregates returning NULL on sparse-NULL data under parallel group by.
• Fixed twap() and sparkline() returning wrong results under concurrent query load.
• Fixed joins on a SYMBOL key combined with a UUID or DECIMAL key failing with an internal error.
• Fixed a rare JIT-compiler crash (SIGSEGV) during query execution.

Storage, Parquet and WAL

• Fixed a crash and boot-time crash loop when deduplicating a STRING dedup key whose partition data file exceeds 2 GiB.
• The WAL sequencer can now rebuild a bricked or version-mismatched _meta from the committed transaction log, surviving table renames.
• CREATE MATERIALIZED VIEW ... TTL N months is now validated up front and rejected when N months isn't a whole multiple of the derived partition size.
• Fixed tables with an indexed SYMBOL column getting stuck mid-conversion to Parquet under a storage policy.

Protocols

• Fixed QWP WebSocket upgrades and reject responses tearing down the connection when they couldn't be sent in a single write.
• QWP now drains the receive buffer on graceful WebSocket close, so the CLOSE frame and its diagnostic reach the client instead of surfacing as ECONNRESET.
• QWP now splits large query results across multiple batches instead of failing when they don't fit the HTTP send buffer.
• The /api/v1/sql/validate endpoint is now side-effect free — it no longer enforces permissions, runs inline statements, or emits logs/metrics, but does resolve the target object so validating against a missing table reports it.

Changelog

• feat(sql): add array_agg() aggregate for collecting double values into arrays by @javier in #6812
• feat(sql): add regr_r2 coefficient of determination aggregate by @jovfer in #7104
• feat(sql): add window function support for DECIMAL datatype by @kafka1991 in #7135
• feat(sql): add byte caps for CachedWindow and ORDER BY native memory by @puzpuzpuz in #7157
• feat(qwp): add deferred commit for transactional ingestion by @bluestreak01 in #7144
• feat(qwp): expand ingest wire-type coverage and advertise batch cap by @bluestreak01 in #7108
• perf(core): skip unchanged buckets on O3 incremental refresh by @nwoolmer in #7112
• perf(sql): stop materialized views emitting no-op WAL transactions under base table backlog by @puzpuzpuz in #7136
• perf(sql): speed up parallel twap() and sparkline() by @mtopolnik in #7140
• perf(sql): parallelise low-precision approx_percentile for LONG arg by @mcadariu in #7084
• perf(sql): remove vect group by factory and fix missing gaps in non-keyed async factory by @puzpuzpuz in #7102
• perf(sql): single-pass parquet decode for covering index build by @bluestreak01 in #7171
• perf(qwp): keep partitions open on egress to lower full-scan latency by @jerrinot in #7125
• fix(sql): fix 55 correctness and resource-leak bugs surfaced by the new query fuzzer by @puzpuzpuz in #7021
• fix(sql): fix 9 correctness and resource-leak bugs surfaced by stricter query assertions by @bluestreak01 in #7187
• fix(core): fix crash deduplicating STRING keys in partitions over 2GB by @bluestreak01 in #7196
• fix(core): read a column added after Parquet conversion as NULL by @bluestreak01 in #7185
• fix(core): validate months-based TTL granularity on materialized views by @glasstiger in #7180
• fix(core): fix storage policy for tables with posting indexes by @RaphDal in #7176
• fix(sql): fix rare JIT compiler crash when allocator's search region drifts below block end by @puzpuzpuz in #7173
• fix(sql): fix statistical aggregates returning NULL on sparse-NULL data by @brunocalza in #7165
• fix(sql): make covering indexes work on Parquet partitions and bound RSS during seal by @nwoolmer in #7145
• fix(qwp): drain recv buffer on WebSocket graceful close to prevent TCP RST by @bluestreak01 in #7164
• fix(core): fix indexed NULL lookups against Parquet partitions by @bluestreak01 in #7146
• fix(qwp): fix WebSocket handshake under partial socket writes by @bluestreak01 in #7129
• fix(core): do not execute statements during SQL validation by @glasstiger in #7124
• fix(core): fix crash adding a covering index when the latest partition is Parquet by @nwoolmer in #7141
• fix(sql): return the last N rows for LIMIT -N over a covering index by @bluestreak01 in #7182
• fix(core): prevent posting index wrong results after failed O3 commit by @jerrinot in #7192
• fix(wal): recover sequencer metadata from committed log by @jerrinot in #7105
• fix(qwp): allow query results larger than the HTTP send buffer by @kafka1991 in #7130
• fix(sql): fix wrong twap() and sparkline() results under concurrent query load by @mtopolnik in #7126
• perf(core): inline bloom filters into Parquet metadata generated by migration by @RaphDal in #7148
• fix(sql): prevent internal errors in symbol hash joins by @jerrinot in #7115

Full Changelog: 9.4.0...9.4.1