QuestDB 9.4.0

QuestDB 9.4.0 introduces a compact, high-performance posting and covering index for SYMBOL columns, a local parquet metadata sidecar that unlocks row-group pruning, parallelised SAMPLE BY FILL with new cross-column FILL(PREV) syntax, three new window functions, and sparkline() / bar() text visualisations. It also delivers meaningful GROUP BY / hash-join speed-ups and fixes a number of correctness issues across the SQL planner, the WAL apply path, and the PGWire protocol.

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

See also our prettier release notes page.

Highlights

Posting and covering index for SYMBOL columns

A new INDEX TYPE POSTING for SYMBOL columns delivers ~13x smaller index files and 1.3-1.5x faster lookups vs. the BITMAP index, at a ~9% write-amplification cost. An optional INCLUDE (...) list builds a covering sidecar so queries that read only the indexed column plus the included columns skip the column files entirely:

CREATE TABLE trades (
    ts TIMESTAMP,
    sym SYMBOL INDEX TYPE POSTING INCLUDE (price, qty),
    price DOUBLE,
    qty INT
) TIMESTAMP(ts) PARTITION BY DAY;

ALTER TABLE trades
    ALTER COLUMN sym ADD INDEX TYPE POSTING INCLUDE (price, qty);

Supported query shapes that benefit from the covering path include WHERE sym = 'X', WHERE sym IN (...), LATEST ON ts PARTITION BY sym, and SELECT DISTINCT sym. Covering data is ALP-compressed for FLOAT/DOUBLE, FoR bit-packed for integer types, and FSST-compressed for STRING/VARCHAR. Native AVX2 decoding fast paths kick in for common bit-widths.

A few practical notes:

• :warning: Drop the posting index before rolling back to an older QuestDB version. Pre-9.4.0 binaries do not recognise the new index type in column metadata and will refuse to open a table that has a posting index. If you need to downgrade, run ALTER TABLE <t> ALTER COLUMN <sym> DROP INDEX on every posting-indexed column first.
• High-cardinality SYMBOL columns benefit most — hundreds to thousands of distinct values on wide tables where the win from skipping full column files is largest.
• The designated timestamp is auto-appended to INCLUDE when you supply an INCLUDE clause, so SHOW CREATE TABLE renders INCLUDE (price, qty) back as INCLUDE (price, qty, ts). Controlled by cairo.posting.index.auto.include.timestamp (default true).
• Verify the covering path with EXPLAIN — the plan shows CoveringIndex on: sym with: ..., with op: latest for LATEST ON queries and op: distinct for SELECT DISTINCT. SHOW COLUMNS and table_columns() also expose new indexType and indexInclude fields.
• Async GROUP BY and filter paths through the covering index are currently slower than the regular plan in some workloads. A follow-up release will close this gap. If EXPLAIN shows a query picking the covering path and you see a regression, opt that query out with /*+ no_covering */ (or /*+ no_index */ to disable indexing entirely) until the optimisations land.

See the posting index and covering index documentation for the full feature reference, encoding variants (POSTING DELTA / POSTING EF, both for benchmarking), and storage layout details. by @bluestreak01 in #6861

Local parquet metadata sidecar

Each parquet partition now ships with a compact binary _pm sidecar that stores column descriptors, per-row-group byte ranges, encodings and min/max statistics. The query planner reads pruning information from _pm without ever opening data.parquet, which is a prerequisite for efficient cold-storage scans. A migration (Mig940) generates _pm files for all existing parquet partitions on engine upgrade. by @RaphDal in #6913

Cross-column FILL(PREV) and parallel SAMPLE BY FILL

Imagine a stream of FX quotes where you want one-minute bars of average bid and ask. On quiet minutes you want both prices to show the last known ask — not the last bid for bid_price and the last ask for ask_price, which is what FILL(PREV, PREV) would give you. Before 9.4.0 that meant dropping down to a CTE with last_value(...) IGNORE NULLS OVER (...) and a coalesce per column. Now it is a one-liner:

SELECT timestamp, symbol,
       avg(bid_price) AS bid_price,
       avg(ask_price) AS ask_price
FROM core_price
WHERE symbol = 'EURUSD' AND timestamp IN '$today'
SAMPLE BY 100T FILL(PREV(ask_price), PREV);

FILL(PREV(col_ref)) carries the previous value of another aggregate column by alias. The reference must match the target column's type, cannot be broadcast across aggregates, and is rejected when either side is a SYMBOL. FILL(NULL), FILL(<constant>), and bare FILL(PREV) can be mixed freely in the same per-aggregate fill list.

At the same time, SAMPLE BY FILL(NULL | <constant> | PREV) moves from the sequential cursor path onto QuestDB's parallel GROUP BY path. Read-side wins compound for keyed queries on wide tables and for queries with FROM/TO boundaries — keyed FROM-TO with constant bounds is also now supported natively, removing the previous cookbook workaround.

Other fixes that ride along:

• Sub-day SAMPLE BY with TIME ZONE and FROM/TO no longer misaligns the fill grid by the timezone offset.
• ALIGN TO CALENDAR WITH OFFSET combined with FILL no longer falls into an infinite fill loop.
• SAMPLE BY FILL now works on tables with pre-1970 timestamps.

FILL(LINEAR) and ALIGN TO FIRST OBSERVATION continue to use the cursor path. See the SAMPLE BY FILL options and the Fill from one column cookbook recipe. by @jovfer in #6946

New window functions

• ntile(n) — distributes rows of an ordered partition into n approximately equal buckets.
• cume_dist() — cumulative distribution of the current row within its partition.
• nth_value(expr, n) — the n-th value within the current frame; supports DOUBLE, LONG, and TIMESTAMP arguments. Works with ROWS and RANGE frames.

All three honour PARTITION BY and ORDER BY. by @jovfer in #6925 and #7037

Text visualisations: sparkline() and bar()

Two new functions render numeric data as Unicode glyphs directly in SQL output:

SELECT symbol, sparkline(price) FROM trades SAMPLE BY 1m;
-- ▁▂▄▆█▇▅▃▂▁

SELECT symbol, bar(volume, 0, max(volume) OVER ()) FROM daily_volumes;
-- ▊▋▌▍▎▏

sparkline() is an aggregate that renders a trend line with auto-scaling, clamping and width control. bar() is a scalar that renders a single value as a horizontal bar with eight levels of sub-character precision. Both return VARCHAR and work across all clients. by @javier in #6975

Configurable parquet encodings on export

PARQUET_ENCODING(...) now propagates through projected streaming parquet exports — COPY (SELECT ...) TO ... WITH format parquet and /exp?query=... preserve the configured encoding instead of silently reverting to defaults for pass-through columns. by @RaphDal in #6949

Performance

• Parallel keyed GROUP BY: batched aggregate dispatch eliminates per-row virtual calls on the reducer, with up to ~4.6x speed-up on single-column count() over fixed-size keys and 1.4-1.8x on multi-aggregate GROUP BY queries. Also fixes a pre-existing data-correctness bug in count(uuid) for UUIDs whose high half equals Long.MIN_VALUE. by @puzpuzpuz in #6959
• GROUP BY, hash joins and count_distinct: a faster xxh3-derived hash finalizer and denser hash tables shave ~15-25% off many GROUP BY queries and improve worst-case probe behaviour by orders of magnitude on adversarial inputs. ClickBench total time improves by ~1.2% across 43 queries. by @puzpuzpuz in #6997
• Parallel top-K with SELECT projections: ORDER BY ... LIMIT N now uses the parallel top-K path even when a column projection (duplicate columns, computed columns) sits between the limit and the filtered scan. by @DHRUV6029 in #6993
• Timestamp equality filters: comparing a TIMESTAMP column against a string bind variable (or any runtime constant) no longer re-parses the value on every row. EXPLAIN plans may show the equality arguments swapped. by @jerrinot in #6986
• Lateral join decorrelation: shared sub-query computations now execute once instead of being cloned per correlated reference, by introducing a shared-cursor framework that GROUP BY factories also use. by @kafka1991 in #6941

Web Console

• Create materialized view from the table menu: a new context-menu action on tables and existing materialized views generates a starter CREATE MATERIALIZED VIEW statement and drops it into the editor. From a base table the generator infers REFRESH IMMEDIATE, SAMPLE BY, PARTITION BY and TTL per QuestDB's default rules, and picks sum/last aggregates by column-name pattern. From an existing materialized view it produces a downsample one rung up the SAMPLE BY ladder, re-roots WITH BASE at the source view, rewrites aggregates against the layer-1 aliases, and steps TTL up to match. Disabled on non-WAL tables and tables without a designated timestamp. in questdb/ui#557

• Context-aware SQL autocompletion: function suggestions now filter by grammar context, so the popup stops dumping the full function list at every identifier position. Examples:

  • SELECT * FROM trades WHERE price = c| previously mixed scalars and aggregates (coalesce, count, count_distinct, corr, covar_*, ...) and now suggests scalars only (coalesce, concat, cos, ceil).
  • SELECT * FROM trades ASOF JOIN m| shrinks from 332 mixed entries to the three actual join targets (materialized_views, memory_metrics, table_writer_metrics).
  • INSERT INTO trades VALUES (n| previously offered nothing; now suggests now, now_ns, nullif, nanos, netmask.

  Statement-start suggestions also include implicit-SELECT targets, and the editor no longer hangs on a trailing comma in the SELECT list. in questdb/ui#556

Bug fixes

SQL planner and execution

• Fixed window functions nested inside GROUP BY expressions producing wrong results — e.g. avg(x) - avg(x) OVER () combined with GROUP BY. Such shapes are now rejected at compile time with a clear error rather than computing silently incorrect output. by @jovfer in #6943
• Fixed nested window inside aggregate with explicit GROUP BY (max(avg(x) OVER ()) patterns). by @jovfer in #6955
• Fixed EMA, VWEMA and KSUM failures when combined with other window functions in the same query. by @jerrinot in #7030
• Fixed WINDOW JOIN crash when the master side projects a symbol column. by @puzpuzpuz in #7098
• Fixed wrong results from the JIT filter when UUID bind variables are involved. by @nwoolmer in #7049
• Fixed BETWEEN evaluation inside sub-expressions (e.g. when it appears as an operand of a larger boolean expression). by @brunocalza in #7044
• Fixed silent drop of outer-join predicates of the form y.a = y.b (both sides on the slave); fixed NOT rewriting in UNION ALL queries that previously only descended into the left side; isolated parser state to allow concurrent SqlParser instances. by @bluestreak01 in #7027
• Fixed empty LIMIT inside a DECLARE sub-query. by @jerrinot in #6979
• Fixed crash on SELECT-CHOOSE with an explicit timestamp clause combined with a column rename. by @bluestreak01 in #6987
• Fixed ASOF/LT joins losing the timestamp shift when the slave subquery already shifts its timestamp. by @jerrinot in #6981
• Fixed arg_max / arg_min returning the wrong precision when the timestamp argument is a TIMESTAMP_NS column. by @bluestreak01 in #7078
• Fixed SAMPLE BY bucket timestamp after a DST-skipped day. by @bluestreak01 in #6988

Storage and WAL

• Fixed a rare WAL apply live-lock when transactions are committed at a very high rate. by @ideoma in #7064
• Fixed a race between WAL writer open and the table-drop purge. by @kafka1991 in #7090
• Fixed a server-main error on a WAL table rename race. by @jerrinot in #7046
• Fixed incorrect write amplification and dedup counts reported by tables(). by @nwoolmer in #7047
• Avoided a redundant bitmap-index rollback after a WAL O3 append. by @jerrinot in #6971

Protocols

• Fixed memory corruption from malformed PGWire messages. by @bluestreak01 in #6983
• Fixed partial query result when re-binding a suspended portal in PGWire. by @RaphDal in #7066
• Fixed JSON-escaping of CHAR column values in /exec and /query responses. by @jerrinot in #7106

Changelist

• feat(core): add compact, high-performance posting index for symbol columns by @bluestreak01 in #6861
• feat(core): add local parquet metadata sidecar file for optimized query planning by @RaphDal in #6913
• feat(sql): add cross-column FILL(PREV) and parallelise SAMPLE BY FILL by @jovfer in #6946
• feat(sql): add ntile, cume_dist, and nth_value window functions by @jovfer in #6925
• feat(sql): add nth_value support for LONG and TIMESTAMP arguments by @jovfer in #7037
• feat(sql): add sparkline() and bar() text visualization functions by @javier in #6975
• feat(sql): add configurable encodings on parquet export by @RaphDal in #6949
• perf(sql): speed up parallel keyed GROUP BY queries by introducing batch calls by @puzpuzpuz in #6959
• perf(sql): speed up GROUP BY, hash joins, and count_distinct with a faster hash finalizer by @puzpuzpuz in #6997
• perf(sql): apply parallel top-K to queries with SELECT projections by @DHRUV6029 in #6993
• perf(sql): cache operands in timestamp equality filters by @jerrinot in #6986
• perf(sql): share repeated computations in lateral join decorrelation by @kafka1991 in #6941
• fix(core): avoid redundant bitmap index rollback after WAL O3 append by @jerrinot in #6971
• fix(sql): report empty LIMIT in DECLARE subquery by @jerrinot in #6979
• fix(sql): preserve shifted timestamps for ASOF/LT joins by @jerrinot in #6981
• fix(sql): fix crash on select-choose with explicit timestamp and column rename by @bluestreak01 in #6987
• fix(sql): fix SAMPLE BY DST bucket timestamp after a skipped day by @bluestreak01 in #6988
• fix(pgwire): fix memory corruption from malformed wire messages by @bluestreak01 in #6983
• fix(sql): fix window functions nested inside GROUP BY expressions producing wrong results by @jovfer in #6943
• fix(sql): fix nested window inside aggregate with explicit GROUP BY by @jovfer in #6955
• fix(core): avoid server-main error on WAL table rename race by @jerrinot in #7046
• fix(sql): between and inside sub-expression by @brunocalza in #7044
• fix(sql): fix wrong results from JIT filter with UUID bind variables by @nwoolmer in #7049
• fix(sql): preserve outer-join predicates, fix NOT in UNION, isolate parser state by @bluestreak01 in #7027
• fix(sql): fix EMA, VWEMA and KSUM failures in combined window queries by @jerrinot in #7030
• fix(wal): correct write amplification and dedup counts in tables() by @nwoolmer in #7047
• fix(pgwire): fix partial query result when re-binding a suspended portal by @RaphDal in #7066
• fix(sql): return correct precision from arg_max/arg_min on TIMESTAMP_NS columns by @bluestreak01 in #7078
• fix(core): fix rare WAL apply live lock when transactions are committed at a very high rate by @ideoma in #7064
• fix(core): fix race between WAL writer open and table drop purge by @kafka1991 in #7090
• fix(sql): WINDOW JOIN crash when master projects a symbol column by @puzpuzpuz in #7098
• fix(http): JSON-escape CHAR column values in /exec and /query responses by @jerrinot in #7106

Full Changelog: 9.3.5...9.4.0