QuestDB 9.4.3

QuestDB 9.4.3 brings some key bug-fixes, along with parquet-native tables and new parquet-querying performance enhancements, with order-of-magnitude speedups for ORDER BY ... LIMIT queries.

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

See also our prettier release notes page.

Breaking changes :boom:

• Two-bound LIMIT null semantics changed, aligning LIMIT lo, hi with LIMIT n:
  • LIMIT null, n now returns the first N rows (previously the full sorted set)
  • LIMIT n, null now returns the empty result (previously all rows).
  • LIMIT null is unchanged, and returns the full result set.
• EXPLAIN plan output rewording for encodable sorts:
  • There was inconsistency in the rendering for 'encoding sorts'; this plan node will now render consistently as Encode sort light.
  • If you assert any particular query plan shapes in your regression suite, you will need to update the assertions.

Highlights

Table-level Parquet format

Tables can now store partitions in PARQUET format by default, rather than only NATIVE. Previously, you would need to:

• Run ALTER TABLE ... CONVERT PARTITION ... TO PARQUET queries to synchronously convert partitions in QuestDB OSS.
• Enable STORAGE POLICY to asynchronously convert partitions in QuestDB Enterprise.

CREATE TABLE trades (ts TIMESTAMP, price DOUBLE, sym SYMBOL)
TIMESTAMP(ts) PARTITION BY DAY FORMAT PARQUET WAL;

ALTER TABLE trades SET FORMAT PARQUET;
ALTER TABLE trades SET FORMAT NATIVE;

Please note that:

• FORMAT PARQUET can only be set on partitioned write-ahead-log (WAL) tables.
• When applying FORMAT PARQUET via ALTER TABLE, historical partitions will not be automatically converted.
• FORMAT NATIVE remains the default format for tables in QuestDB.
• Out-of-order writes are more expensive when applied to parquet partitions.

Memory-budgeted Parquet decode cache

There is a fine balance of memory budget versus CPU time when scanning and decoding parquet row groups. A fixed number of decode slots was an inflexible way to balance these two concerns.

Therefore, we have introduced a new memory-budget configuration and deprecated the old slot-based config:

• New: cairo.sql.parquet.cache.memory.size
  • This is a per-cursor byte budget for decoded row groups (default 256 MB).
• Deprecated: cairo.sql.parquet.frame.cache.capacity
  • This is still accepted, but not used.

Faster ORDER BY ... LIMIT N

The query engine has a number of optimisations based on the native-format data layout. We've revised the long top-K query path for parquet partitions, by removing a sorting tree and pruning projected columns earlier in the process. This is most valuable for queries over wide tables, with a significant sort plus limit workload.

This dramatically reduces unnecessary row-group decoding, and thus improves the scalability of this query shape for larger limits:

Concurrent ILP/TCP stale-writes fix

ILP/TCP is a mostly-deprecated ingestion path, though maintained for backwards compatibility. As part of this maintenance, we have fixed a bug which could cause stale data to be written to a table, affecting DECIMAL, LONG256 and implicit casts from BINARY to SYMBOL. This only impacted concurrent writes, so a straightforward single worker TCP stream was unaffected.

Thank you to our users who continue to use this feature and give us valuable feedback that helps us to keep it well-maintained.

With the upcoming QuestDB 10.0 release, we recommend planning an upgrade to our new ingestion API, which brings dramatically better performance and usability across the board.

Changelog

Features

• feat(sql): add is_end_of_month SQL function by @Dracylfrr in #7214
• feat(core): add periodic memory usage logging by @jerrinot in #7042
• feat(core): support parquet format table level by @ideoma in #7107
• feat(sql): add a memory-budgeted cache for Parquet random access by @puzpuzpuz in #7230

Performance

• perf(sql): speed up ORDER BY ... LIMIT N queries by @kafka1991 in #7222
• perf(sql): speed up parallel ORDER BY ... LIMIT N queries with encoded sort keys by @kafka1991 in #7241
• perf(sql): skip parquet decoding of rows that LIMIT discards by @kafka1991 in #7215
• perf(sql): push down filters into joins wrapped in views and subqueries by @ideoma in #7244
• perf(parquet): speed up partition-to-parquet conversion and reduce file size by @RaphDal in #7206

Fixes

• fix(sql): fix internal error when a dotted name follows an operator by @bluestreak01 in #7260
• fix(core): fix NATIVE_INDEX_READER leak when a posting-index row cursor is closed after its reader by @bluestreak01 in #7256
• fix(sql): fix wrong results in splice, right and full outer joins with a left-table filter by @puzpuzpuz in #7225
• fix(core): fix unexpected logout during HTTP session rotation by @brunocalza in #7249
• fix(sql): fix internal error when an IN subquery contains a join, and handle declared variables in join ON clauses by @puzpuzpuz in #7211
• fix(core): fix snapshot restore accepting truncated parquet partitions when _pm sidecar exists by @bluestreak01 in #7243
• fix(core): fix POSTING-indexed symbol queries returning wrong rows or crashing by @bluestreak01 in #7253
• fix(wal): fix symbol value corruption when skipping replaced transactions across DDL by @jovfer in #7248
• fix(core): fix Parquet crashes on malformed files and data corruption on partial reads by @glasstiger in #7223
• fix(ilp): fix data corruption in concurrent ILP over TCP by @jerrinot in #7240
• fix(sql): fix wrong first/last results in parallel GROUP BY by @ideoma in #7224
• fix(core): fix posting index crash and wrong covered reads after rollback by @bluestreak01 in #7229
• fix(qwp): fix unresponsive network I/O under heavy QWP ingestion by @jerrinot in #7231
• fix(core): fix crash and native memory overrun in covering index out-of-order commits by @bluestreak01 in #7227

Full Changelog: 9.4.2...9.4.3