questdb/questdb 9.2.1

on GitHub

QuestDB 9.2.1 is a stability release, bringing a number of fixes, and some key performance enhancements, including markout horizon CROSS JOIN upgrades, and improvements to SQL query latencies across the board.

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

See also our prettier release notes page.

Highlights

Materialized Views

Materialized Views now have simplified syntax for building view swith only-complete buckets, by specifying that the refresh period should match the sample by interval.

CREATE MATERIALIZED VIEW trades_daily_prices
REFRESH PERIOD (SAMPLE BY INTERVAL) AS
SELECT
  timestamp,
  symbol,
  avg(price) AS avg_price
FROM trades
SAMPLE BY 1d ALIGN TO CALENDAR TIME ZONE 'Europe/London';

In addition, PERIOD views now supported intervals in seconds.

CROSS JOIN

We've released a new optimisation for certain CROSS JOIN queries, specifically for markout analysis. This is a new query hint, which drastically speeds up building a market horizon table. Here is an example:

CREATE TABLE orders (
      ts TIMESTAMP,
      price DOUBLE
  ) timestamp(ts) PARTITION BY DAY;

INSERT INTO orders
  SELECT
      generate_series as timestamp,
      rnd_double() * 10.0 + 5.0
      FROM generate_series('2025-01-02', '2025-01-02T00:10', '200u');

WITH
  offsets AS (
    SELECT sec_offs, 10_000_000 * sec_offs usec_offs 
    FROM (SELECT x-61 AS sec_offs FROM long_sequence(121))
  )
  SELECT /*+ markout_horizon(orders offsets) */ sum(price) FROM (SELECT * FROM (
    SELECT price, ts + usec_offs AS timestamp
    FROM orders CROSS JOIN offsets
    ORDER BY ts + usec_offs
  ) TIMESTAMP(timestamp));

On an r7a.4xlarge instance:

This is part of a series of features focused around optimising post-trade analysis, and driven directly by user feedback, so keep it coming!

Performance

• Reduction in GC overhead for executing high-cardinality GROUP BY/SAMPLE BY queries.
• count_distinct() has been sped up, up to 2x based on standard benchmarks.
• Reduced memory usage for reads and writes over HTTP.
• General SQL query latency reduction by optimising munmap usage:
  • This is opt-in, and can be enabled by setting cairo.file.async.unmap.enabled=true

SQL

• New files(s) and glob(s) for scanning and filtering the server's filesystem.
• New glob(Ss) function, an alternative to LIKE and ~ that uses glob-style syntax.
• New first_not_null(array) and last_not_null(array) group by functions.
• New API endpoint for validating SQL queries: /api/v1/sql/validate.

Changelist

• fix(ilp): backwards incompatible way of writing null arrays by @puzpuzpuz in #6396
• perf(http): garbage-free HTTP header parser by @jerrinot in #6397
• fix(ilp): remove multi-url blacklisting code by @nwoolmer in #6393
• perf(sql): remove litter generated by ordered map rehashing by @puzpuzpuz in #6399
• fix(pgwire): support UUID bind variables in JIT filters by @jerrinot in #6413
• fix(sql): order-by ignored in sub-queries of aggregation by @RaphDal in #6414
• feat(sql): add first_not_null(array) by @mcadariu in #6344
• fix(sql): cannot compile query with aggregate function containing unary minus by @nwoolmer in #6404
• perf(sql): improve SQL latency by moving munmap() to a background job by @jerrinot in #6386
• fix(sql): query with limit x, y (x > 0 & y > 0) return correct size() by @kafka1991 in #6409
• feat(sql): add last_not_null(array) by @mcadariu in #6368
• fix(sql): fix add index issue for symbol columns in matviews by @kafka1991 in #6424
• feat(sql): implement files(s), glob(s) and glob(Ss) functions by @nwoolmer in #6391
• perf(sql): optimized Markout Horizon CROSS JOIN by @mtopolnik in #6283
• perf(sql): speed up keyed parallel GROUP BY in case of high cardinality count_distinct() by @puzpuzpuz in #6432
• fix(sql): fix sample by with only one fill option by @kafka1991 in #6437
• fix(core): fix rare suspended WAL table when ALTER and RENAME are executed concurrently by @ideoma in #6440
• fix(sql): fix a bug that made some ASOF JOIN queries fail with an internal error by @mtopolnik in #6433
• feat(http): REST API for SQL validation by @bluestreak01 in #6383
• fix(sql): fix a bug where a query hint in main SELECT would end up in CTEs as well by @mtopolnik in #6441
• fix(sql): limited subqueries within a union not skipping rows correctly by @nwoolmer in #6395
• fix(sql): export parquet support empty table/partition by @kafka1991 in #6420
• feat(sql): period sample by interval syntax for materialized views by @puzpuzpuz in #6428
• fix(http): negative questdb_json_queries_connections gauge by @nwoolmer in #6444

New Contributors

• @liuguoqingfz made their first contribution in #6242
• @Mittalkabir made their first contribution in #6260

Full Changelog: 9.2.0...9.2.1