May 2022 brings QuestDB 6.3, a release focused on performance and usability based on the vital feedback of our user community and customers. Here's a roundup of changes that have just landed in the latest, greatest version yet.
#Multi-threaded SQL execution for filter queries
We have parallelized and achieved significant performance gains in SQL statements that contain a
where clause with
simple arithmetic expressions, and for queries that make use of
limit with both positive and negative offsets. This
is thanks to the improved JIT sub-system, which is now always
switched on by default, and through extensive use of our in-house async bus.
Enter the Demo Box and experience how 1.63 x 109 rows are blazed through in milliseconds:
#Filesystem snapshot support for cloud environments (AWS, Azure, GCP)
We have optimised query cache retention for all ingestion methods (Http, ILP, PGWire), along with providing support for database backups via filesystem snapshots. Combined, these enable live database backups to be inexpensive in terms of impact on ongoing ingestion, SQL execution performance, and cloud costs.
You can now backup your database live via cloud-native filesystem snapshots in three steps:
- Issue statement
SNAPSHOT PREPARE, which locks all table readers and flushes filesystem caches to disk (through the sync syscall).
- Invoke the cloud-specific snapshot procedure and leave it running for however long it needs.
- Issue statement
SNAPSHOT COMPLETEto release the readers. You do not have to wait for #2 to complete.
This feature can be disabled by setting
cairo.snapshot.recovery.enabled to false.
#Performance optimisation for LATEST ON syntax
We have optimised the execution of
LATEST ON statements over un-indexed symbol columns to achieve two orders of
magnitude performance gains without the need for indexes. The following SQL creates a sample table containing
500_000 rows and then finds the latest sentiment per fiat currency:
#Read-only mode for PGWire protocol
You can now make connections to QuestDB over the PGWire protocol in read-only mode by setting
to true in
server.conf. Attempts to create, drop, truncate and alter tables, as well as to insert data will result in
exception org.postgresql.util.PSQLException: ERROR: Write permission denied.
#SQL syntax formatting and table name autocomplete in web console
The web console auto-completes table names:
and allows you to format SQL statements by means of options
Format Document and
Format Selection, available in the
context menu of the SQL editor (right click):
Read the full details of the release in our release notes page.