QuestDB 9.4.2: shareable queries, new aggregates, and a hardening pass

QuestDB 9.4.2 is a hardening release driven by continued fuzz testing and stricter query-result assertions. Under heavy ingestion, a few edge cases around Parquet tables and the new posting index could surface, sometimes only when combined with other commands. Those paths are now fixed.

We did not publish a separate post for 9.4.1, so this release also folds in the nicer additions that shipped there: two new aggregates, window functions for DECIMAL columns, and much faster materialized view refresh during out-of-order backfills.

Share a query from the Web Console

The Web Console now lets you share a runnable link to any query. Open the drop-down next to the green run button, copy the quick-link, and send it to a colleague. The URL opens the console and runs the query for them, with no copy-paste of SQL into a chat window.

To see it in action, here is one we shared earlier: a rolling realized-volatility query on EURUSD. Open it and the console runs the full query for you, DECLARE variables, CTEs, window functions and all, with no setup on your side.

For longer-lived collaboration you still have Tab Exporting to share and version-control entire editor tabs, or CREATE VIEW to publish production-ready queries. This release also improves the visibility of Storage Policy settings and makes query validation less eager, which smooths out editing.

New aggregates: array_agg() and regr_r2()

array_agg() rolls up the per-group values into a single DOUBLE[], which is handy for feeding array functions or pulling a whole window of prices into one row:

SELECT symbol, array_agg(price) AS prices
FROM trades
WHERE symbol IN ('BTC-USDT', 'ETH-USDT')
    AND timestamp IN '$now - 2s..$now'
GROUP BY symbol;

regr_r2(y, x) is the standard SQL coefficient of determination: how well a linear regression of y on x fits, on a 0 to 1 scale. It is a quick way to tell a real trend from noise. Here it acts as a trending-versus-ranging gauge across FX pairs, where the freely floating majors track their open closely while the tightly managed USDHKD peg barely moves:

SELECT symbol, regr_r2(close, open) AS r2
FROM market_data_ohlc_1d
WHERE symbol IN ('USDCHF', 'EURUSD', 'GBPUSD', 'EURGBP', 'USDHKD')
ORDER BY r2 DESC;

Window functions for DECIMAL columns

All six DECIMAL sub-types (D8 through 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 already supported for the primitive numeric types. See the window functions reference and the DECIMAL type.

Faster materialized view refresh

Refreshing a materialized view during an out-of-order backfill is now much faster. A new adaptive algorithm balances the number of rows refreshed against the number of commits, so performance holds up across varied workloads. Refreshing a 512-symbol view with a constant 12-hour backfill plus live ingestion dropped from around 160ms to roughly 2ms. Materialized views also discard no-op transactions earlier, avoiding wasted work.

Hardening and bug fixes

Most of 9.4.2 is correctness work. Beyond the Parquet and posting-index hardening above, two query fixes worth calling out:

• count() over a GROUP BY subquery no longer reports phantom duplicate rows.
• UNION ALL no longer fails with a column mismatch when its branches sit under aggregates.

The full set of fixes spans the posting index, Parquet reads, temporal joins, and a range of other SQL queries. See the complete list on the release notes page.

Breaking changes

• A plain INDEX TYPE POSTING (no INCLUDE) is now non-covering. The cairo.posting.index.auto.include.timestamp=true flag adds the designated timestamp to your INCLUDE list automatically when you do not, so queries that return the timestamp still run through the covering index instead of falling back to a table scan.
• SHOW CREATE TABLE now rejects views and materialized views. It previously rendered a misleading CREATE TABLE statement for any entity. Use SHOW CREATE VIEW and SHOW CREATE MATERIALIZED VIEW instead.

That is QuestDB 9.4.2: shareable queries in the console, two new aggregates and DECIMAL window functions carried over from 9.4.1, and a solid pass of hardening across Parquet and posting indexes.

Full changelog on GitHub

Ready to try it?

docker pull questdb/questdb
docker run -p 9000:9000 -p 8812:8812 questdb/questdb

Join our Slack or Discourse communities to share feedback and results.