questdb/questdb 8.1.4

on GitHub

It's spooky season. 🎃

What scares us the most? Poor performance. 😱

And just behind that, bugs and unintuitive UIs. Aaaahhhh!!

This release brings light to all of the aforementioned fears, as we continue with our seasonal theme of balance.

Breaking Changes 💥

SQL keyword quoting enforcement: SQL keywords used as column names now require double-quoting (e.g., "distinct", "order", "group"). While this was previously required, it's now enforced more strictly. This change supports PostgreSQL-compatible syntax and may break existing queries using unquoted keywords.

New features 🐣

Slick Web Console polish

Last patch, we introduced tabs to the Web Console experience. With tabs, navigating and work flows are easier and cleaner.

The polish continues in 8.1.4 and includes many small, tidy incremental improvements such as:

• More prominent error highlighting
• Prettier formatting when errors are generated by larger queries
• Revamped the look and feel of the table list panel — gone is the pop-up menu
• Once executed via shortcut, such as F9 or CTL + Enter, hitting the same shortcut will no longer cancel running SQL
• Indicates Running... when a query is running (... so you'd better go catch it!)
• While Running... the "play" button is replaced with "stop" button
• Removed = as word delimiter, so that expressions like == can be highlighted as errors

And more! Download, upgrade or checkout our live demo to see them.

Regression function regr_slope()

We've introduced the regr_slope(y, x) SQL function, which calculates the slope of the linear regression line determined by the dependent variable y and independent variable x. One might use such a function in statistical analysis, trend identification, and predictive modelling:

SELECT regr_slope(sales, time) FROM revenue_data;

This query computes the slope of sales over time. Great for trend spotting.

Support for COUNT(DISTINCT col) and STRING_AGG(DISTINCT col)

The DISTINCT keyword is now available within aggregate functions to operate on unique values.

Counting unique values:

SELECT count(DISTINCT user_id) FROM events;

Aggregating unique strings:

SELECT string_agg(DISTINCT city, ', ') FROM customers;

This simplifies queries where you need to work with distinct entries. We all appreciate more concise and expressive SQL.

Performance improvements 🚀

• Suped up many statistical functions with parallelized execution. Functions like var_samp(), var_pop(), stddev_samp(), stddev_pop(), covar_samp(), covar_pop(), and corr() are now up to 10× faster.

• Reduced memory usage during paralleled aggregate queries

General improvements and fixes 🔧

• Addressed a disk space leak issue when upserting with deduplication

• Resolved phantom errors in SELECT queries caused by concurrent partition squashing

• Fixed inconsistencies in the interaction between the Web Console and the static metadata cache

• Fixed issues causing unexpected timeouts and error messages in parallel query execution

• Resolved an infinite loop issue that could occur on errors in parallel filter and GROUP BY queries - as fun as it was...

• Addressed a potential transaction leak in table reader/writer initialization on ZFS.

• Fixed a rare failure to restore from a checkpoint, triggered by an indexed column being created while a checkpoint is in progress.

• Unified macOS runtime requirements to macOS 13 for build consistency.

• Fixed checkpoint backward compatibility with snapshot semantics.

• Fixed a memory leak with pipelined UPDATEs in the PostgreSQL Wire Protocol.

Pull requests

• fix(build): Unify MacOS runtime requirements to MacOS 13 by @jerrinot in #5035
• perf(sql): reduce memory footprint of parallel aggregate functions by @puzpuzpuz in #5046
• fix(sql): fix unexpected timeouts, error messages in parallel query execution by @glasstiger in #5039
• fix(core): fix disk space leak on upserting with dedup by @ideoma in #5048
• feat(http): signal empty query as a notice, not an error by @mtopolnik in #5031
• fix(sql): infinite loop on error in parallel filter and group by queries by @puzpuzpuz in #5055
• feat(sql): breaking change 💥 - support for count(distinct col) and string_agg(distinct col) by @jerrinot in #5045
• fix(core): fix phantom select query errors caused by concurrent partition squashing by @ideoma in #5058
• fix(sql): resolve inconsistencies in interaction between web console and static metadata cache by @nwoolmer in #5043
• feat(sql): add regr_slope() SQL function by @vss96 in #5021
• fix(http): improved error handling for unsupported column types by @jerrinot in #5068
• fix(pgwire): memory leak with pipelined UPDATEs by @jerrinot in #5083
• perf(sql): parallelise sample and population variance by @nwoolmer in #5072
• perf(sql): parallelise sample and population standard deviation by @nwoolmer in #5075
• perf(sql): parallelise sample and population covariance by @nwoolmer in #5074
• fix(core): potential transaction leak in table reader/writer initialization on ZFS by @puzpuzpuz in #5086
• fix(core): fix rare failure to restore from checkpoint, triggered by indexed column being created while checkpoint in progress by @bluestreak01 in #5090
• perf(sql): parallelise correlation by @nwoolmer in #5079
• fix(core): fix checkpoint backward compatibility with snapshot semantics by @ideoma in #5091
• fix(core): fix rare crash on read binary and other var length column types by @ideoma in #5093

Full Changelog: 8.1.2...8.1.4