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;

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;

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.

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 #6651
• feat(sql): add arg_min() and arg_max() aggregate functions by @bluestreak01 in #6652
• feat(sql): implement EMA window function via avg() by @bluestreak01 in #6650
• fix(ilp): prevent writes going to the wrong place after a table rename by @nwoolmer in #6654
• feat(sql): add bool_and() and bool_or() aggregate functions by @bluestreak01 in #6658
• feat(sql): add bit_and(), bit_or(), and bit_xor() aggregate functions by @bluestreak01 in #6660
• feat(sql): add geomean() aggregate function by @bluestreak01 in #6656
• fix(sql): prevent SIGSEGV in window join by awaiting workers before freeing cache by @bluestreak01 in #6662
• feat(sql): recognize dateadd predicates in time intrinsic filters by @puzpuzpuz in #6666
• fix(sql): potential segfault on vectorized GROUP BY query timeout by @puzpuzpuz in #6667
• feat(sql): support nested window functions with deduplication optimization by @bluestreak01 in #6643
• feat(sql): add within_box, within_radius, and geo_within_radius_latlon functions by @bluestreak01 in #6664
• feat(sql): recognize OR of timestamp IN predicates as interval intrinsics by @bluestreak01 in #6673
• fix(sql): handle unbalanced quotes in SQL line comments by @bluestreak01 in #6684
• perf(core): avoid contention across concurrent queries on expensive file operation by @mtopolnik in #6688
• perf(sql): apply scan optimization for first/last/min/max(timestamp) as function argument by @kafka1991 in #6690
• fix(sql): error on group by with many aggregate functions by @puzpuzpuz in #6689
• feat(sql): add length_bytes() function for varchars by @puzpuzpuz in #6685
• fix(sql): fix reverse-order argument parsing in multi-arg window function by @mtopolnik in #6697
• fix(sql): proper error message when timestamp used along another join key in ASOF/LT join by @mtopolnik in #6698
• fix(sql): fix an issue where the DECLAREd symbols weren't honored when parsing function arguments by @mtopolnik in #6700
• fix(sql): fix parquet read failure on chained window join by @kafka1991 in #6676
• fix(sql): resolve 'Invalid column' error in WINDOW JOIN with aliased columns by @bluestreak01 in #6701
• feat(sql): TICK - Temporal Interval Calendar Kit for interval literals by @bluestreak01 in #6674
• fix(pgwire): prepared batch with same SQL returns corrupted results by @kafka1991 in #6706
• fix(sql): fix nanosecond timestamp handling in tables() and wal_transactions() by @mtopolnik in #6704
• feat(sql): timestamp predicate pushdown through virtual models with dateadd offset by @bluestreak01 in #6702
• feat(sql): implement percent_rank() window function by @bluestreak01 in #6712
• fix(sql): fix order by position resolution with window functions over CTEs by @bluestreak01 in #6716
• feat(sql): extend TICK date variable arithmetic with additional time units by @bluestreak01 in #6711
• perf(sql): optimize parquet decode rowgroup performance by @kafka1991 in #6632
• fix(core): broken logging in TableWriter that may lead to unresponsive instance by @puzpuzpuz in #6720
• fix(core): fix dictionary and bitpack encoding for symbol columns in parquet by @kafka1991 in #6708
• feat(ui): ai assistant improvements by @emrberk in questdb/ui#529

New Contributors

• @mghildiy made their first contribution in #6644

Full Changelog: 9.3.1...9.3.2