8.3.1

8.3.1 is a stability release, and moves Materialized Views into GA. Thanks to all the early-adopters who have helped us with crucial feedback, to make sure they are efficient and robust. Give them a try!

8.3.1 also brings the first SQL Hints, which will give you more control over your query execution plans. For now, only one hint is supported - USE_ASOF_BINARY_SEARCH - which allows you to swap between two algorithms for handling filtered ASOF JOIN queries. Next on the list - controlling when the database chooses to use an INDEX scan.

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

See also our prettier release notes page.

Changelist

• fix(sql): add reset query timeout timer before checkpoint create by @kafka1991 in #5604
• fix(sql): fix internal error with some ASOF JOINs by @jerrinot in #5606
• fix(pgwire): compatibility with the nodejs 'postgres' driver by @jerrinot in #5615
• fix(core): prevent total symbol data loss on db hard reset by @ideoma in #5598
• feat(sql): skip unchanged sample by intervals in materialized view incremental refresh by @puzpuzpuz in #5613
• feat(sql): alter materialized view symbol capacity by @puzpuzpuz in #5622
• fix(sql): missing DST gap correction in case of certain sample by intervals by @puzpuzpuz in #5626
• perf(sql): support for Oracle-style SQL hints by @jerrinot in #5620

Full Changelog: 8.3.0...8.3.1

8.3.0

8.3.0 moves Materialized Views into an advanced beta. Thanks to all of you have tried the beta and helped us iron out the remaining creases!

This release also brings significant ingestion performance improvements, particularly for small transactions and for batched PG Wire inserts. Not to mention a variety of bug fixes and safety enhancements to improve overall user experience.

But let's not forget about querying data! Check out how your ASOF JOIN queries are doing! We have released dramatic performance improvements for ASOF JOINs that include interval scans and/or post-join filters. Benching against larger datasets has shown 100-1000x speedups for some queries.

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

See also our prettier release notes page!

Breaking changes 💥

• Materialized Views will now be enabled by default. If you do not wish to use them, you can disable them by setting cairo.mat.view.enabled=false.

• Designated timestamp columns can no longer hold values beyond the Year 9999. We found that these values were generally produced by mistaken arithmetic. This prevents undroppable partition issues, and also reduces the chances of a configured TTL dropping your data.

• Some IPv4 built-in functions were removed. You may need to introduce explicit casts in SQL queries that relied on implicit conversions between ipv4 and other database types. This removes some ambiguity that was causing issues with queries unrelated to ipv4.

• Converting char to numeric values now aligns with PostgreSQL behaviour, whether implicitly or explicitly casted.

• SAMPLE BY will now correctly handle a variety of DST-related timestamp grouping issues. SAMPLE BY queries that use DST shifts are more likely to be executeed in parallel, too. These changes mean your result set might look different to before - but should be a consistent, rational output.

• FLOAT and DOUBLE types, when output to JSON or web console, will now have different precision. Previously, this was limited artificially. Now, it will print as many digits as it can. If you rely on the old output for any integration testing, you may need to upgrade your tests!

Highlights

• Materialized Views are now in an advanced BETA.
  • Views will be correctly invalidated when the base table is dropped.
  • Syntax errors in materialized view definitions should report the correct error position.
  • PARTITION BY and parentheses () are now optional.
  • Full mat view refreshes should no longer trigger occassional OOM errors.
  • Still to come: a mat view write-performance enhancement, which is also a pre-step to supporting time-range DELETEs!
• ASOF JOIN queries that include filters over large datasets will now run 100-1000x faster than before!
• SYMBOL columns can now have their capacity changed in-place, using ALTER TABLE table ALTER COLUMN column SYMBOL CAPACITY 4096!
• Ingestion of small batches has been sped by up to 100x!
• Ingestion via PG Wire batched inserts is faster and more memory efficient.

Changelist

• feat(core): speed up WAL small transaction apply 100x by @ideoma in #5347
• fix(core): mat view is not invalidated when base table is dropped by @glasstiger in #5486
• fix(core): make partition drop atomic by @ideoma in #5515
• fix(pgwire): fix handling of sparse bind variables by @bluestreak01 in #5514
• fix(core): prevent inserting designated timestamp beyond year 9999, this would create invalid partition name by @mtopolnik in #5478
• fix(sql): support casting and aliasing timestamp column in materialized view by @eugenels in #5485
• fix(pgwire): possible unhandled error (double-close) in case of INSERT failure by @bluestreak01 in #5523
• fix(sql): crash when GROUP BY key includes timestamp by @jerrinot in #5527
• perf(sql): speed-up of simple projections by @jerrinot in #5529
• feat(core): accept EntraID's cookie expiry timestamp format by @glasstiger in #5470
• feat(sql): change column type SQL to support changing symbol column capacity by @ideoma in #5497
• chore(core): merge identical mem.getDirectVarcharA/B by @mtopolnik in #5542
• fix(ilp): disable Nagle's algorithm in HttpClient (set TCP_NODELAY flag) by @mtopolnik in #5550
• fix(sql): stuck thread on high cardinality group by query timeout by @puzpuzpuz in #5539
• fix(http): unhandled NullPointerException on SQL query error by @puzpuzpuz in #5548
• fix(core): fix issue with missing WAL segment directory during replication by @eugenels in #5538
• fix(sql): make the function's argument overload rules consistent with functions' implementation by @kafka1991 in #5528
• fix(core): fix critical error message in writing data with deduplication and a possible crash on writing same timestamp lines by @ideoma in #5547
• fix(sql): improve error handling for malformed SQL queries by @kafka1991 in #5572
• fix(sql): breaking change 💥 - remove some ipv4 operator functions. by @kafka1991 in #5566
• fix(core): fix table suspended on concurrent ALTER and RENAME operations by @ideoma in #5563
• fix(core): fix cannot invoke "io.questdb.network.Epoll.close()" error by @eugenels in #5573
• perf(sql): breaking change 💥 - parallel SAMPLE BY with time zone execution by @puzpuzpuz in #5493
• fix(sql): accept long256 values via hex-encoded string in ILP by @nwoolmer in #5579
• fix(http): breaking change 💥 - fix incorrect FLOAT type scale in JSON response by @bluestreak01 in #5578
• fix(sql): spurious cancelled by user errors returned by queries executed in parallel by @puzpuzpuz in #5582
• perf(pgwire): improve performance of batch inserts via PostgreSQL driver by @bluestreak01 in #5564
• chore(core): ensure mat view logging cannot stall by @bluestreak01 in #5587
• perf(sql): expanded fast-path support for non-keyed ASOF JOINs by @jerrinot in #5553
• chore(sql): enable materialized views by default by @puzpuzpuz in #5460
• fix(sql): fix show create table union query issue. by @kafka1991 in #5577
• fix(ui): show JSON parsing error in case of an invalid query result by @emrberk in questdb/ui#411
• fix(ui): fix incorrect unit normalisation for wal row throughput by @emrberk in questdb/ui#414
• fix(ui): don't use navigator.clipboard in insecure context by @emrberk in questdb/ui#417
• fix(ui): workaround for broken safari copy schema mechanism by @emberk in questdb/ui#418
• feat(ui): add storage details, persistent expand states, symbol details by @emberk in questdb/ui#409

New Contributors

• @emrberk made their first contribution in questdb/ui#407

Full Changelog: 8.2.3...8.3.0

8.2.3

8.2.3 is a stability release, fixing tricky edge cases and increasing the performance of some key queries. These fixes cover many different aspects of the database - better handling of bind variables, faster ORDER BY and LIMIT, reduced memory usage, reduced disk storage for indexes, and more.

Many of these changes are driven by user feedback, so thanks to all of you whom have helped us to improve QuestDB!

We've also been hard at work on our roadmap features, and are excited to bring you an initial BETA release for Materialized Views! We will be finalising this feature in the next release, and upgrading it to support more of the SAMPLE BY syntax.

Along with this, we have added a number of quality-of-life features, some performance enhancements for filtering and distinct queries, and compatibility improvements for older Linux distros.

We have also made strong progress towards bringing the new ARRAY type, so keep an eye out in our upcoming releases. These are N-dimensional arrays and are heavily optimised for numeric operations. As part of the ARRAY development, we will be rolling out the first modifications to the ILP clients, to introduce parts of a new binary protocol. This will ensure that throughput is maximised especially for sending arrays and numeric data.

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

See also our prettier release notes page!

Breaking changes 💥

• UUID and LONG256 types will now be sorted correctly, respecting unsigned ordering. Previously, they would sort incorrectly. This is a breaking change - but the correct one!

• You now cannot use SAMPLE BY and GROUP BY in the same query. This wasn't really supported before, as SAMPLE BY is already a group by, and this can cause ambiguity in the query. Now this is enforced, so you should use a subquery for the SAMPLE BY, and move your GROUP BY to an outer query. You should not notice any performance change from this, simply a syntax change.

Highlights

• Materialized views has been released in BETA
  • Materialized views are tables driven by a SAMPLE BY query, which are incrementally updated for you whenever new data is written to their parent table.
  • These views help you to downsample data automatically, cache the latest values, and filter out subsets of data from your main table.
  • See the docs to get started and try it out!
• Relaxed GLIBC requirement (2.28 aarch64 and 2.17 amd64) to allow QuestDB run on older Linux distros
• New dense_rank() window function
  • This is like rank(), but guarantees that the ranking numbers are sequential - no gaps!
  • See the docs.
• New approx_median() function
  • This is a quicky and easy way to calculate a median value for your data set. It is equivalent to approx_percentile(_, 0.5).
  • See the docs.
• Performance optimisations giving significant speedups (up to 30x) for certain queries
  • IN with subqueries (especially with SYMBOL type) - value IN (select column .... )
  • SELECT DISTINCT queries

Changelist

• fix(core): relax minimal required glibc version to 2.17 on amd64 by @jerrinot in #5332
• fix(sql): spurious query cancellation on high server load by @puzpuzpuz in #5342
• fix(http): memory leak when querying tables with changing metadata by @puzpuzpuz in #5353
• fix(core): edge-case memory leak during disk failure fixed by @jerrinot in #5349
• fix(http): fix connection leak under memory pressure by @ideoma in #5355
• fix(sql): fix UnsupportedOperationException thrown when sorting an IPv4 column by @nwoolmer in #5365
• fix(sql): SHOW CREATE TABLE provides invalid ddl when table is not partitioned by @nwoolmer in #5361
• feat(SQL): refactor rank() windowFunction and introduce dense_rank() windowFunction by @kafka1991 in #5334
• fix(core): relax minimal required glibc version to 2.28 on aarch64 by @jerrinot in #5340
• fix(core): fix occasional config parsing error when the context path is set per service by @glasstiger in #5378
• fix(sql): NullPointerException in invalid sample by query by @puzpuzpuz in #5354
• fix(core): fix updates failing with table busy when non-WAL table is created from ILP by @ideoma in #5383
• fix(core): fix column name case sensitivity in alter column SQL by @ideoma in #5395
• fix(pgwire): fix cursor handling for r2dbc-postgresql compatibility by @jerrinot in #5396
• fix(pgwire): compatibility with PHP PDO client by @jerrinot in #5402
• feat(core): materialized views by @glasstiger in #4937
• fix(core): fix startup delay on instances with lots of data by @ideoma in #5399
• fix(sql): prevent stale metadata after table DROP and (re-)CREATE by @jerrinot in #5418
• fix(sql): fix issue comparing symbols and timestamps when symbols were not constant by @nwoolmer in #5419
• fix(sql): breaking change 💥 - unsigned UUID and Long256 sorting by @jerrinot in #5429
• fix(pgwire): fix bind variable values being ignored for LIMIT clause by @bluestreak01 in #5377
• fix(sql): ordering by alphanumeric columns that start with a number by @nwoolmer in #5408
• fix(sql): null values returned from index-based non-null filter in backward-scan query by @puzpuzpuz in #5432
• fix(sql): missing null symbol value in query with not in symbol filter by @puzpuzpuz in #5431
• fix(sql): occasional internal error after TRUNCATE by @jerrinot in #5436
• fix(core): fix checkpoint restore deleting a referenced directory by @ideoma in #5443
• fix(sql): cannot order by columns with purely numeric names by @nwoolmer in #5435
• fix(sql): fix memory leaks when parsing sub-queries in timestamp filter by @puzpuzpuz in #5450
• feat(sql): add approx_median() function by @nwoolmer in #5449
• fix(sql): fix wrong sample by result for timestamps before 1970 by @kafka1991 in #5412
• feat(sql): add INTEGER alias for INT for compatibility with third party tools by @AsteriosPanoras in #5274
• fix(core): fix update table busy error when table is created concurrently with update query by @ideoma in #5446
• fix(core): fix VACUUM table to clean unused symbol index files by @ideoma in #5264
• fix(sql): fix inefficient execution of symbol-in-cursor queries by @bluestreak01 in #5445
• fix(sql): Function args in GreatestNumericFunction and LeastNumericFunction should be deep-cloned by @kafka1991 in #5480
• fix(core): fix rare ILP commit exception when writing large string values by @ideoma in #5471

New Contributors

• @AsteriosPanoras made their first contribution in #5274

Full Changelog: 8.2.2...8.2.3

8.2.2

Our first release of 2025! 🎉

• Read our release blog for a high-level feature summary
• Checkout our release notes page or the items below for PR details
• Join us in Slack or our Discourse community for any feedback or questions

What's Changed

• fix(pgwire): fix error message resend loop during failed TLS connection initiation by @eugenels in #5219
• feat(ilp): introduce config property to disable logging problematic ILP messages by @puzpuzpuz in #5223
• fix(sql): fix server error when querying SAMPLE BY with FILL by @ideoma in #5224
• fix(sql): fix rare NullPointerException thrown when executing parallel filter query by @nwoolmer in #5221
• feat(sql): add SHOW CREATE TABLE table_name to get a table DDL by @nwoolmer in #5204
• fix(pgwire): fix query cache hit logging for prepared statements by @kafka1991 in #5192
• fix(core): fix bug when log rolling deletes recent instead of old files from disk by @ideoma in #5229
• feat(sql): less/greater correlated subqueries support for timestamp column by @puzpuzpuz in #5226
• fix(core): fix stale table suspended flag not cleared after auto resume on restart by @ideoma in #5026
• fix(sql): support filtering and sampling dot-prefixed tables by @nwoolmer in #5260
• fix(pgwire): error when using in('foo', 'bar') predicate by @jerrinot in #5222
• fix(sql): wrong var-size results returned by some read_parquet() and join queries by @puzpuzpuz in #5236
• fix(sql): fix rewrite sample by to group by same column names' bug. by @kafka1991 in #5113
• fix(sql): allow UNION between IPv4 and VARCHAR/STRING columns by @jerrinot in #5246
• feat(core): add configurable timezone for logger by @nwoolmer in #5231
• feat(pgwire): reloadable buffer size and connection limit configuration properties by @puzpuzpuz in #5183
• fix(core): fix symbol capacity displayed wrong after restart in show columns queries by @ideoma in #5242
• fix(sql): fix rewriteOrderBy issue case when orderBy column is not in projection. by @kafka1991 in #5271
• fix(pgwire): byte column binding issue fix by @jerrinot in #5253
• fix(core): bug in network services where service may not recover from connection limit hit by @bluestreak01 in #5276
• fix(core): ignore extra spaces in rolling logger config by @glasstiger in #5282
• fix(core): fix rare crash after disk full error by @ideoma in #5269
• feat(sql): add timezone-aware variant of dateadd by @abant07 in #5209
• fix(core): improve storage error handling to avoid resource leaks by @ideoma in #5288
• fix(sql): fix max txn-inflight error and log SQLs that leak resources by @bluestreak01 in #5277
• feat(sql): alter table add column [if not exists] by @irufus in #5080
• feat(sql): add DECLARE keyword to support variable bindings by @nwoolmer in #5207
• fix(pgwire): fix reader/txn leak under edge case conditions by @bluestreak01 in #5296
• fix(core): fix table type conversion leading to table drop if db restarts midway by @ideoma in #5295
• feat(sql): add support for the first_not_null_value(D) window function by @kafka1991 in #5191
• fix(http): fix bad error message to Web Console when SQL times out by @bluestreak01 in #5292
• perf(sql): speed up execution of window functions and joins on Windows platform by @bluestreak01 in #5300
• feat(core): table data TTL by @bluestreak01 in #5234
• fix(core): fix stuck sys.telemetry_wal by @ideoma in #5303
• fix(core): handle errors on table create so that table name is not left locked by @ideoma in #5301
• feat(sql): mark reloadable properties in show parameters SQL by @puzpuzpuz in #5312
• fix(core): fix resource leak on query errors by @ideoma in #5314
• fix(sql): fix errors when querying Window functions with non matching order with outer ORDER BY by @kafka1991 in #5220
• feat(sql): multi-threaded read_parquet() execution by @puzpuzpuz in #5256
• chore(core): add stack traces to WAL apply error logs by @puzpuzpuz in #5287
• fix(core): fix column conversion error leading to file descriptor leaks by @ideoma in #5321
• feat(sql): introduce last_value(D), last_not_null_value(D), lag(D) and lead(D) window function by @kafka1991 in #5255
• fix(sql): fix the missing of ORDER BY position's resolve in the JOIN-UNION QueryModel. by @kafka1991 in #5273
• fix(sql): fix errors for certain queries that use the window function on join with select same columns by @kafka1991 in #5320
• fix(core): fix memory leak when handing file open error by @ideoma in #5323
• fix(sql): fix PGWire char type mapping and binary protocol issues with boolean type by @jerrinot in #5285
• feat(core): http query and ilp connection soft limits by @glasstiger in #5278
• feat(core): configurable context path for HTTP endpoint by @glasstiger in #5297
• fix(sql): fix NPE in sample by with value fills by @bluestreak01 in #5324
• feat(sql): sql parser support for 'union distinct' and 'foo is [not] true/false' by @jerrinot in #5318
• feat(sql): SQL query tracing for analysis by @bluestreak01 in #5315
• feat(ui): new tab and system monitoring widgets by @insmac in questdb/ui#352
• fix(ui): case insensitive auto-complete by @insmac in questdb/ui#366
• fix(ui): kick user out if access token expired and there is no refresh token by @glasstiger in questdb/ui#373

New Contributors

• @abant07 made their first contribution in #5209
• @irufus made their first contribution in #5080

Full Changelog: 8.2.1...8.2.2

8.2.1

Winter is coming... And so are more features and improvements within 8.2.1.

This release follows a restive holiday weekend and the successful deployment of 8.2.0. Our prior release shipped our re-constructed PostgreSQL server implementation. After seeing the initial usage patterns, we've rolled out a series of stability fixes to smooth the overall journey. Those consuming PGWire, within tools like R Studio and PowerBI, will find a more seamless experience.

And, as usual, a series of fixes and performance improvements and general optimizations.

Breaking change 💥

• The dateadd(AIN) function will now error when provided with an invalid period or n (stride) value, if they are constants.
  • Use the errors to reconstruct the function syntax as appropriate.
• PowerBI integration now uses PostgreSQL type names in information_schema.columns().
  • Please update to these type names if you've applied an alternative.

Web Console 📈

Additions

• Support for ID token in Auth ui/#355
• News Image zoom, to better see all the cool new stuff :) ui/#350

Changes

• Highlight integer numbers that include _ (underscore) separator ui/#353
• Handle and display error received while scrolling the grid ui/#348
• Use new information_schema.questdb_columns() instead of information_schema.columns() ui/#359

New features 🐣

• New min(D) and max(D) window function.
  • We've also overhauled the window function docs, for better clarity and with more examples.

Performance improvements 🚀

• Reduced CPU idle load for improved overall efficiency.
• Improved length(varchar) function performance.

Bug fixes 🐛

• Fixed an issue when purging dropped tables that previously led to critical failures.
• Fixed issue where WAL tables would be suspended when a table update was followed by a rename.
• Fixed error reporting in the scenario where a 'drop table if exists' SQL command does not find the table.
• Fixed incorrect error reporting to PostgreSQL drivers:
  • Prepared statements that might fail at runtime will no longer contain duplicate error messages
  • SQL compilation errors will not be reported as NPE
• Modified INSERT AS SELECT will correctly return the number of rows inserted.

Pull requests

• perf(core): reduce CPU idle load by @ideoma in #5190
• perf(sql): performance improvement in length(varchar) function by @jerrinot in #5188
• fix(core): fix purging of dropped tables not to error out as critical failures by @ideoma in #5172
• fix(sql): breaking change 💥 - dateadd(AIN) will now error if provided invalid constant period or stride by @nwoolmer in #5166
• fix(pgwire): fix incorrect error reporting to PostgresSQL drivers by @bluestreak01 in #5189
• feat(sql): add support for the min(D) and max(D) window functions by @kafka1991 in #5173
• fix(sql): fix error returned that table does not exist on drop table if exists SQL by @ideoma in #5197
• fix(core): fix WAL table suspended when update to the table followed by table rename by @ideoma in #5200
• fix(sql): fix PowerBI integration, but breaking change 💥 - use PostgreSQL type names in information_schema.columns() by @jerrinot in #5206
• fix(pgwire): INSERT AS SELECT to return the number of rows inserted by @glasstiger in #5007

Full Changelog: 8.2.0...8.2.1

8.2.0

Interoperability is key. The systems that'll succeed in the future are those which play well with others. As such, QuestDB is committed to both open formats and full participation in the many open ecosystems used by our growing community.

To that goal, our latest release brings with it a major re-construction of our underlying PostgreSQL Wire Protocol implementation. With strict, optimized PGWire compliance, QuestDB 8.2.0 connects gracefully to PowerBI, RStudio, Looker, and much more. And of course, a wave of new functions, Enterprise enhancement, performance improvements, fixes, and much more.

For those in the US, we wish you a fine and comforting thanksgiving in the week to come.

Breaking Changes 💥

• Renamed column in wal_tables() function: The column writerLagTxnCount in the wal_tables() function has been renamed to bufferedTxnSize. This change aims to reduce confusion but may require updates to existing scripts or applications using this function.

New Features 🐣

• Enhanced PGWire server: The PostgreSQL server component has been rewritten to strictly comply with the PostgreSQL wire protocol. This ensures better compatibility with PostgreSQL clients and tools, enabling support for applications like Microsoft Power BI and R. Naturally, it's also much, much more performant. How much? Over 1M rows per second and 700,000 queries per second. 🔥 🚀 🎸

• QuestDB Enterprise | Microsoft Entra ID compatibility: QuestDB Enterprise now supports authentication and authorization via Microsoft Entra ID (formerly Azure Active Directory) for integration with Microsoft's identity services.

• QuestDB Enterprise | Improved PingFederate support: Enhanced compatibility with PingFederate, improving single sign-on (SSO) capabilities and integration with enterprise identity management systems.

• Window function count(): Support for the count() window function has been added. You know, for counting.

SELECT time, value, COUNT(*) OVER (ORDER BY time) AS running_total
FROM measurements;

• Hash functions md5, sha1, sha256: Introduced standard hash functions for generating cryptographic hashes of input strings or binary data.

SELECT md5('hello world') AS hash_value;

• Regression function regr_intercept(): Added the regr_intercept(y, x) function to compute the intercept of the linear regression line determined by dependent variable y and independent variable x.

SELECT regr_intercept(sales, time) FROM revenue_data;

Performance Improvements 🚀

• 10× Speedup for small transactions over PGWire: With the improvements to the PostgreSQL Wire Protocol, we're also seeing up to a tenfold increase in performance for small transactions. This is particularly beneficial for applications that issue frequent small inserts or updates. 🔥 🚀 🎸

• Optimized Window functions: Improved the performance of window functions via faster hash tables and optimized execution paths. Analytical workloads will appreciate it.

Improvements 🛠️

• Background job latency control: Introduced configurable latency setting and deep changes to allow background job execution control. The following new configuration options sets the max amount of time between consecutive commits before WAL apply job takes action and begins merge:

cairo.commit.latency=30s

• More responsive health endpoint: The Health HTTP endpoint is now more responsive, even under high CPU load. For better monitoring, and glory.

• Enhanced error handling in HTTP: HTTP returns more detailed error messages directly to clients, for easier debugging and client-side error handling.

Bug Fixes 🐛

• Smoother schema change via PGWire: Resolved issues where spurious errors could occur after table schema changes when using the PostgreSQL Wire Protocol.

• Memory limitations for string_agg(): Implemented a cap on memory usage for the string_agg() function to prevent potential out-of-memory errors when aggregating large amounts of data.

• Corrected Error Messages in table_storage(): Fixed incorrect error messages when selecting non-existing columns in the table_storage() function.

• Fixed disappearing tables after restart: Addressed a rare issue where tables could disappear after a restart if created with naming collisions. Heh.

• Cross-JOIN query circuit breaker: Ensured that cross join queries are properly cancelled when the circuit breaker is invoked, preventing runaway queries.

Full PR List

Enterprise

Learn more about QuestDB Enterprise

Includes all of OSS, plus:

• feat(core): support for decoding groups from ID token
• feat(core): replace admin-only checks with permissions
• fix(core): fix for not being able to login with other users if built-in admin is disabled
• feat(core): config switch to disable ILP over TCP authentication

OSS

• fix(pgwire): fix a spurios errors after table schema change by @jerrinot in #5099
• fix(sql): cap string_agg() memory use to the "strFunctionMaxBufferLength" value by @bluestreak01 in #5106
• feat(http): make healthcheck endpoint responsive on high CPU load by @ideoma in #5108
• fix(sql): fix incorrect error message out of table_storage function when selecting non-existing column by @bluestreak01 in #5109
• fix(sql): BREAKING 💥 - rename writerLagTxnCount to bufferedTxnSize in wal_tables() to reduce user confusion by @vss96 in #5001
• perf(core): replace unconditional txn sync with a conditional one. 10x improvement of ingress perf for small transactions by @bluestreak01 in #5115
• perf(core): optimize StringSink buffer reuse - garbage elimination by @jerrinot in #5127
• feat(sql): introduce latency configuration to ensure small batch commit visibility is upheld by @bluestreak01 in #5118
• fix(core): fix http client handling of cookies by @bluestreak01 in #5119
• feat(sql): add regr_intercept() SQL function by @vss96 in #5071
• fix(ilp): Java ILP client supports timestamps up to 9999-12-31 23:59:59.999999 by @jerrinot in #5134
• fix(http): send error to http client instead of closing connection by @glasstiger in #5078
• perf(sql): use faster hash table in window functions by @puzpuzpuz in #5135
• fix(sql): fix non-reporting of error for SQLs with multiple where clauses by @LinuxMercedes in #5082
• feat(sql): support for _ in SQL integer constants and human-friendly configuration values for time intervals in server.conf by @bluestreak01 in #5128
• perf(sql): speed up negative limit queries with a wildcard by @nwoolmer in #5139
• fix(core): fix drop partition SQL not removing the expected partition data and in some rare circumstances leading to a data loss by @mtopolnik in #5132
• fix(core): fix table disappear after restart if created with the naming collisions by @ideoma in #5144
• fix(sql): cross join queries not cancelling when circuit breaker is invoked by @nwoolmer in #5161
• feat(sql): add FORCE DROP partition SQL syntax, useful to unblock suspended WAL tables by @ideoma in #5140
• fix(core): fix metadata queries returning stale data in tables() and table_columns() functions after table drop concurrent with column add by @ideoma in #5168
• fix(sql): fix comparison error of rowsLow and RowsHigh in window function range frame. by @kafka1991 in #5170
• feat(sql): Add common hash functions such as md5, sha1 and sha256 by @mliezun in #5156
• feat(pgwire): server support for pipelining in PGWire protocol by @bluestreak01 in #4975
• perf(sql): improve performance of queries with negative limits and existing order by clauses by @nwoolmer in #5148
• feat(sql): add count(*), count(D), count(Ø), count(K) window function. by @kafka1991 in #5153
• fix(sql): correctly escape double quotes when exporting CSVs by @nwoolmer in #5178
• perf(sql): introduce fast path for ordered and limited queries over single long column by @puzpuzpuz in #5152

First timers!

We extend our gratitude to the new contributors who made valuable contributions to this release:

• @imalasong: Contributed in PR #4967
• @LinuxMercedes: Contributed in PR #5082
• @kafka1991: Contributed in PR #5170
• @mliezun: Contributed in PR #5156

8.1.4

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

8.1.2

October has arrived, and with it the fall. According to an interesting character, it's also Libra ♎ season, a phase of balance. It works for us, as this release restores the balance within QuestDB's production reliability. Chasing maximum performance and cutting-edge features is our favourite game. But every now and then, it's essential to double-back and smooth out the edges so that the overall experience presents consistent excellence. And, well, we'll keep improving performance too.

Download, upgrade, and prepare for the seasonal feasts. 🎃

Breaking Changes 💥

• Logging level adjustments: Some log messages previously logged at the INFO level have been changed to DEBUG. This reduction in log verbosity may impact monitoring systems relying on these messages. Adjust logging configurations as necessary.

New features 🐣

• Web Console UI tabs: The QuestDB Web Console features tabs for better query management. Tabs are archived upon closing and can be brought back from the history section as needed.

• ILP client row cancellation: The ILP client now supports cancelling the current row during data ingestion. This allows for greater control when writing data and helps prevent incomplete or erroneous data from being committed.

• New financial function spread_bps: Computes the spread in basis points between two numerical values.

• greatest and least functions: Determine the maximum or minimum value from a list of expressions. These functions simplify comparisons across multiple columns or expressions.

• Interval functions for date filters: Implemented new interval functions today(), tomorrow(), and yesterday() that return intervals representing the entire day. These functions make it easier to filter queries based on whole-day periods without manually specifying time ranges.

• New aggregate function string_distinct_agg: Aggregates unique string values into a single, concatenated string with a specified delimiter. This is useful for creating summaries of distinct values in a dataset.

• ILP traffic to Prometheus: Introduced metrics to monitor ILP (Influx Line Protocol) traffic volumes over both TCP and HTTP. These metrics help in tracking data ingestion rates and diagnosing network-related issues.

• RedHat OpenShift Compatible Docker Image: Beginning with this release, QuestDB Docker images with a -rhel suffix are now compatible with RedHat's OpenShift container orchestration platform.

New integrations

• Bento by Warpstream Labs: In the words of Bento they provide: Fancy stream processing made operationally mundane. Indeed, and now even fancier with a fresh QuestDB component.

• Redpanda Connect: Formerly Benthos. Comes with a QuestDB output component that can be used as a sink for your stream processing data. Read our docs for more information.

Performance improvements 🚀

• Static metadata cache: Implemented a static metadata cache to improve performance by reducing the overhead associated with frequent metadata access. This enhancement speeds up query compilation and execution.

• Optimized partition reloads: Reduced unnecessary reloads of TableReader partitions, which enhances query performance, especially in environments with high data ingestion rates and frequent queries.

• Improved parallel GROUP BY execution: Optimized parallel GROUP BY operations by eliminating redundant bytecode generation. This results in faster execution times for complex aggregation queries.

• Faster Web Console queries: You'll notice improved performance during the highligh and execution of queries with SQL comments in the editor.

Improvements 🛠️

• Enhanced log compatibility: Added a configuration option to control verbose log levels, improving compatibility with log readers and monitoring tools that require specific log formats or verbosity levels.

• Detailed ILP error messages: Enhanced the server logs to include specific error messages indicating the exact line that caused a receive buffer overflow during ILP ingestion. This aids in troubleshooting and correcting data formatting issues.

Bug fixes 🐛

• ILP client retry behaviour: Fixed an issue where ILP clients would retry data insertion when encountering invalid UUID values. The client now correctly handles bad UUIDs without unnecessary retries, improving ingestion stability.

• Server crash with first(varchar) and last(varchar): Resolved a critical issue where using first(varchar) or last(varchar) functions could cause the server to crash. These functions now operate reliably with VARCHAR data types.

• Deduplication key crash fix: Addressed a potential crash when writing data with deduplication enabled on VARCHAR or STRING columns as keys.

• Parquet byte column support: Added support for RleDictionary encoding of BYTE columns in the read_parquet() function. This enhances compatibility with Parquet files that use this encoding scheme.

• Handling comment-only queries: Fixed an issue where SQL queries containing only comments were not properly processed. The server now correctly handles such queries without errors.

• UUID and IPv4 in coalesce and switch: Corrected the lack of support for UUID and IPv4 literals in the coalesce() function and SWITCH statements. These functions now fully support these data types.

• Parallel GROUP BY race condition: Fixed a race condition that could occur during parallel GROUP BY operations involving a large number of groups.

• Symbol to integer comparison fix: Resolved a NullPointerException that occurred when comparing SYMBOL columns to integer values. Comparisons between these data types now function correctly.

• Responsive health check endpoint: Improved the responsiveness of the health check HTTP endpoint when the server reaches its memory limit. This ensures that health checks remain reliable under memory pressure.

List of PRs

• fix(ilp): ILP clients should not retry when inserting a bad UUID value by @jerrinot in #4934
• feat(ilp): ILP client allows to cancel the current row by @jerrinot in #4936
• feat(sql): add spread_bps function for finance by @nwoolmer in #4939
• feat(sql): add greatest and least functions by @nwoolmer in #4940
• fix(sql): fix NPE when using CTEs and compiling an async group by factory by @nwoolmer in #4929
• perf(core): introduce static metadata cache by @nwoolmer in #4848
• fix(core): breaking change 💥 - demote some info logs to debug logs by @nwoolmer in #4944
• fix(sql): first(varchar) and last(varchar) functions can crash server by @jerrinot in #4947
• fix(core): fix potential crash when writing with dedup key on on varchar, string column key by @ideoma in #4966
• fix(sql): add RleDictionary encoding support for byte columns in the read_parquet function by @eugenels in #4984
• fix(ilp): add error message in server log about the line that caused receive buffer overflow by @ideoma in #4987
• feat(sql): add interval SQL functions for easy-to-use today, tomorrow, yesterday time filters by @nwoolmer and @puzpuzpuz in #4968
• fix(core): improve compatibility with log-readers by adding config option for verbose log levels by @nwoolmer in #4950
• fix(sql): support comment-only query by @mtopolnik in #4983
• perf(sql): reduce unnecessary TableReader partition reloads by @ideoma in #5010
• feat(sql): string_distinct_agg function by @puzpuzpuz in #5000
• fix(sql): missing UUID and IPv4 literal support in coalesce function and switch statement by @puzpuzpuz in #5017
• fix(sql): race in parallel group by with many groups by @puzpuzpuz in #5018
• perf(sql): avoid redundant bytecode generation in parallel GROUP BY by @puzpuzpuz in #5023
• chore(sql): fix non-timezone aware test by @nwoolmer in #5008
• fix(sql): NullPointerException in symbol to int comparison by @puzpuzpuz in #4993
• feat(ilp): add ILP traffic volume metrics - TCP, HTTP by @vss96 in #5004
• fix(core): make health check http endpoint responsive when memory limit is reached by @ideoma in #5014
• fix(sql): bug in SAMPLE BY queries after a query cache hit by @jerrinot in #4990
• fix(core): fix db random crash after inserting into a table with varchar dedup key by @ideoma in #5030
• fix(core): do not reports APFS as unsupported on MacOS 15 - Sequoia by @jerrinot in #5032

New Contributors

• @vss96 made their first contribution in #5004

Full Changelog: 8.1.1...8.1.2

8.1.1

QuestDB 8.1.1

Back to school... Baaack to school...

Lunch? Check. Pants? Check (phew!) New QuestDB release? Oh yeah.

Let's go! 📚 ✏️ 🥪

Breaking changes 💥

We have changed the language in our SNAPSHOT related SQL statements. We introduce CHECKPOINT.

Apply it before taking a backup.

Instead of executing:

 SNAPSHOT PREPARE

You will now execute:

CHECKPOINT CREATE

When the backup is complete, instead of executing:

SNAPSHOT COMPLETE

You will execute:

  CHECKPOINT RELEASE

Both commands are supported for now, but we encourage you to migrate to the new system.

---

We have also introduced a fix for integer overflow, which now widens the type of the result.

For example, the following will be automatically widened to LONG (64bit) to avoid numeric overflow:

select 799990090 * 100000

Improvements 🚀

• General improvements to database stability
• Out-of-the-box ingress performance improved via reduced resource contention
• SQL execution performance improvements for cases like ORBER BY <long>, and GROUP BY with virtual columns such as now()
• Certain ASOF SQL queries have improved their performance by orders of magnitude
• Support for VARCHAR and STRING as deduplication keys.

Pull requests

• fix(sql): add support for filling weeks ('w') in SAMPLE BY queries by @nwoolmer in #4812
• fix(pgwire): fix insert as select statement running twice on pgwire when using prepared statements by @ideoma in #4820
• fix(sql): fix reading uint16 column as chars from parquet by @ideoma in #4814
• feat(core): configure to roll log files daily by default when QuestDB set up from binaries by @ideoma in #4810
• fix(sql): improve check to rule out keyed sample by queries with from-to by @nwoolmer in #4827
• feat(sql): add table_storage() functionality by @siddharth0815 in #4817
• fix(sql): fix edge case when using parallel sample by with fill by @nwoolmer in #4849
• fix(sql): fix file does not exist error on updates by @ideoma in #4846
• perf(sql): optimize queries with virtual columns, filter, and limit by @puzpuzpuz in #4858
• fix(sql): NullPointerException thrown on SAMPLE BY query with constant key by @puzpuzpuz in #4866
• feat(sql): replace SNAPSHOT SQL with CHECKPOINT to reduce confusion by @bluestreak01 in #4821
• fix(sql): fix error when using sample by with fill(none) by @nwoolmer in #4864
• feat(sql): checkpoint_status function for read-only checks of in-progress checkpoint by @puzpuzpuz in #4869
• fix(sql): improve query behaviour when using non-constant FROM clauses in SAMPLE BY by @nwoolmer in #4850
• fix(ilp): fix incorrect storage of non-ascii characters that can lead to incorrect SQL results by @bluestreak01 in #4876
• feat(sql): add support for weighted mid price by @alexandrebrilhante in #4801
• feat(sql): show server_version_num support by @puzpuzpuz in #4867
• perf(core): reduce default number of WAL apply threads by @puzpuzpuz in #4865
• fix(core): prevent potential double-close of File Descriptors by @ideoma in #4874
• fix(core): fix pgwire float precision for text format by @glasstiger in #4879
• perf(sql): optimize like/ilike/regexp functions on symbol column by @puzpuzpuz in #4871
• fix(core): align native library naming with JNA convention by @mtopolnik in #4882
• perf(sql): use radix sort to speed up single integer column order by by @puzpuzpuz in #4891
• perf(sql): JIT-optimise IN timestamp_literal operator by @zebs97 in #4750
• fix(core): fix query failure when high limit is used with bind variables by @glasstiger in #4896
• fix(sql): symbol cannot be set to CACHE immediately after setting it to NOCACHE by @nwoolmer in #4908
• fix(sql): better overflow behaviour when converting timezones by @jerrinot in #4824

New Contributors

• @alexandrebrilhante made their first contribution in #4801

Full Changelog: 8.1.0...8.1.1

8.1.0

QuestDB 8.1.0 has arrived.

In this release, QuestDB's foundation takes a second step towards our next generation architecture.

Our prior release introduced JSON extraction.

And now, by popular demand, QuestDB meets Apache Parquet.

New Features 🐣

• Parquet read support Use the read_parquet() function to read Apache Parquet files.
• Improved SAMPLE BY:
  • FROM-TO syntax: Specify result ranges, with support for prefilling and postfilling data. Also can correct misaligned buckets.
  • Parallel SAMPLE BY: Now supports FILL(VALUE) and FILL(NULL).
• Snapshot recovery: Added a trigger file to initiate snapshot recovery, improving database resilience and ease-of-use.

Performance 🚀

• Improved ILP ingress latency
• SQL Performance:
  • Speedup for small transaction writing by 50-100%.
  • Enhanced performance for the like/ilike operator on symbol columns.
  • Speed improvements for regexp_replace(varchar) with simple patterns.
• Aggregation queries: Aggregation queries are now faster.

Bug Fixes 🐛

• SQL:

  • Fixed spurious "unsupported operation" errors.
  • Corrected wrong results from parallel WHERE and GROUP BY for some function keys.
  • Fixed incorrect results from parallel GROUP BY with a single varchar function key.
  • Addressed issues with some window functions double counting rows.

• Core:

  • Prevented table suspension under memory pressure.
  • Fixed Charts when using together with OAuth.

Pull requests

• fix(sql): fix spurious "unsupported operation" errors by @bluestreak01 in #4632
• perf(core): speedup small transaction writing 50-100% by @ideoma in #4793
• fix(sql): fix wrong results returned from parallel WHERE and GROUP BY for some function keys by @puzpuzpuz in #4796
• fix(sql): incorrect results returned from parallel GROUP BY with single varchar function key by @puzpuzpuz in #4798
• fix(core): avoid table suspension when under memory pressure by @jerrinot in #4745
• perf(sql): speed up like/ilike operator on symbol column by @puzpuzpuz in #4794
• perf(sql): speed up regexp_replace(varchar) for simple patterns by @puzpuzpuz in #4668
• fix(sql): some window functions might double count rows by @jerrinot in #4804
• feat(sql): read_parquet() function by @eugenels in #4460
• feat(sql): introduce SAMPLE BY FROM-TO syntax for specifying result ranges by @nwoolmer in #4733
• fix(ui): fix Charts when using together with OAuth by @insmac in #4813
• feat(core): trigger file to initiate snapshot recovery by @jerrinot in #4807

New Contributors

• @jchrys made their first contribution in #4808

Full Changelog: 8.0.3...8.1.0