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 10⁹ 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:

1. Issue statement SNAPSHOT PREPARE, which locks all table readers and flushes filesystem caches to disk (through the sync syscall).
2. Invoke the cloud-specific snapshot procedure and leave it running for however long it needs.
3. Issue statement SNAPSHOT COMPLETE to 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:

CREATE TABLE sample_table AS (

SELECT

rnd_symbol('Dollar', 'Euro', 'Hryvnia', 'Yuan') fiat,

rnd_int(1, 1000, 0) sentiment,

timestamp_sequence(0, 3600000000L) ts

FROM long_sequence(500000)

) TIMESTAMP(ts) PARTITION BY MONTH;

SELECT * FROM sample_table

WHERE fiat in ('Dollar', 'Euro', 'Hryvnia', 'Yuan')

LATEST ON ts partition by fiat;

Read-only mode for PGWire protocol

You can now make connections to QuestDB over the PGWire protocol in read-only mode by setting pg.security.readonly 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):

Cheers

Read the full details of the release in our release notes page.

Vlad Ilyushchenko and Alex Pelagenko, two world-class performance engineers enjoying coffee at AWS Summit London 2022.