QuestDB 9.3.2

QuestDB 9.3.2 continues the trend of performance upgrades and bugfixes, with some additional new features. Importantly, we introduce the new TICK syntax, a compact DSL for expressing time intervals, alongside faster aggregations, improved applicability of interval scans, and fast parquet queries.

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

See also our prettier release notes page.

Highlights

New TICK syntax

The Temporal Interval Calendar Kit (TICK is a new DSL for expressing complex time ranges and intervals in a compact, easy-to-use format.

For example, let's say that you want to query one month of data from NYSE, only including trading days and hours. The data in the database is stored in UTC format, so you'd need to convert to different time zones, and build a very complex WHERE clause with ANDs and ORs. Or, alternatively, send lots of narrow queries and combine the results.

Instead, you can express this in a simple string:

-- NYSE trading hours on workdays for January
SELECT * FROM trades
WHERE ts IN '2024-01-[01..31]T09:30@America/New_York#workday;6h29m';

Reading this from left to right, we will the days in 2024-01 in the range of 01..31 (all of them), with the interval beginning at 09:30 in New York time, only considering working days, and the interval ending after 6h29m on each day (inclusive, so at 16:00 New York time).

We then compile this to an efficient interval scan, with the time filtering pushed down:

 intervals: [
    ("2024-01-01T14:30:00.000000Z","2024-01-01T20:59:59.999999Z"),     

    ("2024-01-02T14:30:00.000000Z","2024-01-02T20:59:59.999999Z"),

    ("2024-01-03T14:30:00.000000Z","2024-01-03T20:59:59.999999Z"),

    ...
]

This syntax makes it easier to construct complex intervals, and keep confidence that the execution plan will be optimal.

Please see the TICK docs for more information.

arg_min, arg_max, and geomean aggregates

Let's say you are monitoring trading data, and you track what the max trading price was over an hourly period. You can express that like this:

SELECT timestamp, symbol, max(price)
FROM trades
WHERE timestamp IN today()
AND symbol IN ('BTC-USDT', 'ETH-USDT')
SAMPLE BY 1h;

| timestamp | symbol | max(price) | | --------------------------- | -------- | ---------- | | 2026-01-28T00:00:00.000000Z | ETH-USDT | 3029.32 | | 2026-01-28T00:00:00.000000Z | BTC-USDT | 89488.3 | | 2026-01-28T01:00:00.000000Z | BTC-USDT | 89495.0 | | 2026-01-28T01:00:00.000000Z | ETH-USDT | 3026.58 | | 2026-01-28T02:00:00.000000Z | BTC-USDT | 89450.0 | | 2026-01-28T02:00:00.000000Z | ETH-USDT | 3018.31 | | ... | ... | ... |

But now we have a problem - at what time or trade was that the max price?

Using arg_max, we can extract values from the row where the price was max:

SELECT timestamp, symbol, max(price), arg_max(timestamp, price)
FROM trades
WHERE timestamp IN today()
AND symbol IN ('BTC-USDT', 'ETH-USDT')
SAMPLE BY 1h;

| timestamp | symbol | max(price) | arg_max(timestamp, price) | | --------------------------- | -------- | ---------- | --------------------------- | | 2026-01-28T00:00:00.000000Z | ETH-USDT | 3029.32 | 2026-01-28T00:26:05.512000Z | | 2026-01-28T00:00:00.000000Z | BTC-USDT | 89488.3 | 2026-01-28T00:59:57.971000Z | | 2026-01-28T01:00:00.000000Z | BTC-USDT | 89495.0 | 2026-01-28T01:20:45.782000Z | | 2026-01-28T01:00:00.000000Z | ETH-USDT | 3026.58 | 2026-01-28T01:03:35.940000Z | | 2026-01-28T02:00:00.000000Z | BTC-USDT | 89450.0 | 2026-01-28T02:05:50.368000Z | | 2026-01-28T02:00:00.000000Z | ETH-USDT | 3018.31 | 2026-01-28T02:02:04.936999Z | | ... | ... | ... |

Pretty handy!

geomean(D) calculates the geometric mean for a set of positive numbers; this is a useful average variant, which improves accuracy for data with large outliers, and commonly used for growth rate calculations.

For more details, please check out the aggregate function documentation.

EMA, VWEMA, percent_rank window functions

We've introduced new exponential moving average (EMA) window functions. This smooths out your data base on a smoothing and time period.

Additionally, we've added a volume-weighted variant (VWEMA), which is commonly used for trading data, allowing for smoothing of the function whilst still prioritising higher-volume trades.

Here's an example of the syntax:

-- Standard average
avg(value) OVER (window_definition)

-- Exponential Moving Average (EMA)
avg(value, kind, param) OVER (window_definition)

-- Volume-Weighted Exponential Moving Average (VWEMA)
avg(value, kind, param, volume) OVER (window_definition)

percent_rank returns the relative rank of a row within a group of values. This is calculated based on the number of rows within the group, and its position.

For more information, please see the window functions documentation.

Web Console

We've added new improvements to the 'QuestDB AI Assistant' feature, improving the speed and responsiveness of the chat. We'll be following up soon with an expansion of supported model providers.

https://github.com/user-attachments/assets/e856a6ef-d17f-4e35-b4af-5fc9988a6719

Performance

Time-intrinsics upgrades

QuestDB will now optimise time-range queries over tables where the range uses a constant dateadd offset.
- e.g. WHERE dateadd('m', 15, timestamp) = '2022-03-08T18:30:00Z'
QuestDB will optimise time filters that include OR clauses:
- e.g. WHERE timestamp IN '2018-01-01' OR timestamp IN '2018-01-02' OR timestamp IN '2018-01-03'
We've improved how time-range pushdown is handled within nested queries:
- e.g. SELECT * FROM (SELECT dateadd('h', -1, timestamp) as ts, price FROM trades) WHERE ts in '2022'

Parquet and File-handling

We have significantly sped-up querying parquet queries by optimising how row-groups are decoded, giving up to a 6x performance boost versus the last release.
We've optimised how file mappings are handled, removing sporadic query latency increases over extremely concurrent query workloads.

Changelist

feat(sql): volume-weighted exponential moving average (VWEMA) window function by @bluestreak01 in https://github.com/questdb/questdb/pull/6651
feat(sql): add arg_min() and arg_max() aggregate functions by @bluestreak01 in https://github.com/questdb/questdb/pull/6652
feat(sql): implement EMA window function via avg() by @bluestreak01 in https://github.com/questdb/questdb/pull/6650
fix(ilp): prevent writes going to the wrong place after a table rename by @nwoolmer in https://github.com/questdb/questdb/pull/6654
feat(sql): add bool_and() and bool_or() aggregate functions by @bluestreak01 in https://github.com/questdb/questdb/pull/6658
feat(sql): add bit_and(), bit_or(), and bit_xor() aggregate functions by @bluestreak01 in https://github.com/questdb/questdb/pull/6660
feat(sql): add geomean() aggregate function by @bluestreak01 in https://github.com/questdb/questdb/pull/6656
fix(sql): prevent SIGSEGV in window join by awaiting workers before freeing cache by @bluestreak01 in https://github.com/questdb/questdb/pull/6662
feat(sql): recognize dateadd predicates in time intrinsic filters by @puzpuzpuz in https://github.com/questdb/questdb/pull/6666
fix(sql): potential segfault on vectorized GROUP BY query timeout by @puzpuzpuz in https://github.com/questdb/questdb/pull/6667
feat(sql): support nested window functions with deduplication optimization by @bluestreak01 in https://github.com/questdb/questdb/pull/6643
feat(sql): add within_box, within_radius, and geo_within_radius_latlon functions by @bluestreak01 in https://github.com/questdb/questdb/pull/6664
feat(sql): recognize OR of timestamp IN predicates as interval intrinsics by @bluestreak01 in https://github.com/questdb/questdb/pull/6673
fix(sql): handle unbalanced quotes in SQL line comments by @bluestreak01 in https://github.com/questdb/questdb/pull/6684
perf(core): avoid contention across concurrent queries on expensive file operation by @mtopolnik in https://github.com/questdb/questdb/pull/6688
perf(sql): apply scan optimization for first/last/min/max(timestamp) as function argument by @kafka1991 in https://github.com/questdb/questdb/pull/6690
fix(sql): error on group by with many aggregate functions by @puzpuzpuz in https://github.com/questdb/questdb/pull/6689
feat(sql): add length_bytes() function for varchars by @puzpuzpuz in https://github.com/questdb/questdb/pull/6685
fix(sql): fix reverse-order argument parsing in multi-arg window function by @mtopolnik in https://github.com/questdb/questdb/pull/6697
fix(sql): proper error message when timestamp used along another join key in ASOF/LT join by @mtopolnik in https://github.com/questdb/questdb/pull/6698
fix(sql): fix an issue where the DECLAREd symbols weren't honored when parsing function arguments by @mtopolnik in https://github.com/questdb/questdb/pull/6700

New Contributors

@mghildiy made their first contribution in https://github.com/questdb/questdb/pull/6644

Full Changelog: https://github.com/questdb/questdb/compare/9.3.1...9.3.2

questdb

Related Projects

mapbox-navigation-android

ToastFish

barcodelib

haze