QuestDB 9.4.0: Posting index, cross-column fill, and smarter Web Console | QuestDB<br>New: QuestDB For AI Agents<br>New: QuestDB For AI Agents<br>Learn more
QuestDB is the open-source time-series database for demanding workloads—from trading floors to mission control.<br>It delivers ultra-low latency, high ingestion throughput, and a multi-tier storage engine.<br>Native support for Parquet and SQL keeps your data portable, AI-ready—no vendor lock-in.
QuestDB 9.4.0 ships a new index type for SYMBOL columns that is 13x smaller<br>than the bitmap index, a FILL(PREV(col)) syntax that carries values across<br>columns in SAMPLE BY, and a smarter Web Console that generates materialized<br>view definitions and filters autocompletion by grammar context. Also in this<br>release: three new window functions, sparkline() / bar() text<br>visualizations, and speed-ups across GROUP BY, hash joins, and top-K queries.
Posting index for SYMBOL columns
The existing bitmap index works well for low-cardinality symbols, but it<br>struggles on wide tables with hundreds or thousands of distinct values. The new<br>posting index is built for exactly<br>that scenario: 13x smaller index files, 1.3-1.5x faster lookups, at roughly 9%<br>write-amplification cost.
Create it on a new table:
Create a table with a posting index<br>CREATE TABLE trades_pi (<br>ts TIMESTAMP,<br>sym SYMBOL INDEX TYPE POSTING,<br>price DOUBLE,<br>qty INT<br>) TIMESTAMP(ts) PARTITION BY DAY;
Or add it to an existing column:
Add a posting index to an existing column<br>ALTER TABLE my_table<br>ALTER COLUMN sym ADD INDEX TYPE POSTING;
Covering index
The real payoff comes when you add an INCLUDE clause. This builds a covering<br>sidecar so queries that only need the indexed column plus the included columns<br>skip the main column files entirely:
Covering index with INCLUDE<br>ALTER TABLE my_table<br>ALTER COLUMN sym ADD INDEX TYPE POSTING<br>INCLUDE (price, qty);
Queries of the form WHERE sym = 'X', WHERE sym IN (...),<br>LATEST ON ts PARTITION BY sym, and SELECT DISTINCT sym all benefit from the<br>covering path. Covering data is ALP-compressed for floats, FoR bit-packed for<br>integers, and FSST-compressed for strings. AVX2 decoding kicks in on supported<br>hardware.
Use EXPLAIN to verify a query is using the covering path. The plan shows<br>CoveringIndex on: sym with: ....
WARNING
Drop the posting index before rolling back to a pre-9.4.0 binary. Older<br>versions do not recognise the new index type and will refuse to open the table.<br>Run ALTER TABLE ALTER COLUMN DROP INDEX on every posting-indexed<br>column first.
Read the posting index documentation
Cross-column FILL(PREV) and parallel SAMPLE BY FILL
Say you have a stream of FX quotes and you want one-minute bars of average bid<br>and ask. On quiet minutes, both prices should carry forward the last known ask<br>price, not the last bid for one and the last ask for the other, which is what<br>FILL(PREV, PREV) gives you. Before 9.4.0, you needed a CTE with<br>last_value(...) IGNORE NULLS OVER (...) and a coalesce per column.
Now it is a one-liner:
Cross-column FILL(PREV) on FX quotesDemo this query<br>SELECT timestamp, symbol,<br>avg(bid_price) AS bid_price,<br>avg(ask_price) AS ask_price<br>FROM core_price<br>WHERE symbol = 'EURUSD'<br>AND timestamp IN '$today'<br>SAMPLE BY 100T<br>FILL(PREV(ask_price), PREV);
FILL(PREV(ask_price)) on the bid_price aggregate carries forward the<br>previous value of ask_price instead of bid_price. The reference must match<br>the target column's type and is rejected when either side is a SYMBOL.<br>FILL(NULL), FILL(), and bare FILL(PREV) can be mixed freely in<br>the same fill list.
At the same time, SAMPLE BY FILL(NULL | | PREV) has moved from<br>the sequential cursor path onto the parallel GROUP BY engine. Keyed queries<br>on wide tables benefit the most. Keyed FROM-TO with constant bounds is also<br>now supported natively.
Other fixes that ride along:
Sub-day SAMPLE BY with TIME ZONE and FROM/TO no longer misaligns the<br>fill grid by the timezone offset
ALIGN TO CALENDAR WITH OFFSET combined with FILL no longer falls into an<br>infinite fill loop
SAMPLE BY FILL now works on tables with pre-1970 timestamps
Read the SAMPLE BY FILL documentation
New window functions: ntile(), cume_dist(), nth_value()
QuestDB's window function<br>library picks up three SQL-standard additions. All three honour PARTITION BY<br>and ORDER BY:
ntile(n) distributes rows into n approximately equal buckets. Useful for<br>percentile-based bucketing:
Split trades into volume quartilesDemo this query<br>SELECT symbol, price, amount,<br>ntile(4) OVER (<br>PARTITION BY symbol<br>ORDER BY amount DESC<br>) AS volume_quartile<br>FROM trades<br>WHERE symbol = 'BTC-USDT'<br>AND timestamp IN '$today'<br>LIMIT 20;
cume_dist() returns the cumulative distribution of the current row within<br>its partition, as a value between 0 and 1:
Cumulative price distributionDemo this query<br>SELECT symbol, price, amount,<br>cume_dist() OVER (<br>PARTITION BY symbol<br>ORDER BY price<br>) AS price_percentile<br>FROM trades<br>WHERE symbol = 'BTC-USDT'<br>AND timestamp IN '$today'<br>LIMIT 20;
nth_value(expr, n) returns the n-th...