New Release: CrateDB 6.5

The latest CrateDB version has been released as CrateDB 6.5. If you run analytics on large amounts of data, you will notice: aggregations and GROUP BY queries are faster, in some cases a lot faster, so dashboards load quicker, and the same cluster can handle more queries. The query planner got smarter as well, which helps with complex queries without you having to tune them by hand.

Besides performance, we focused on things that make daily work easier. More PostgreSQL tools work with CrateDB out of the box, migrating PostgreSQL schemas needs fewer changes, JWT authentication is simpler to set up with OpenID Connect Discovery, and SWAP TABLE and RENAME TABLE no longer break your snapshots. And again, a big part of this release comes from our open-source community.

Faster Aggregations and GROUP BY

Counting distinct values and grouping data are common operations in analytical queries and dashboards. CrateDB 6.5 makes several of them faster, without any change on your side. You just upgrade and your existing queries benefit.

A count(DISTINCT col) without a GROUP BY on a string column is now rewritten into a grouping, so it is executed like SELECT count(col) FROM (SELECT col FROM tbl GROUP BY col) tmp. In our benchmarks this made certain queries up to 21x faster.

Here is a real example: SELECT count(DISTINCT "cCode") FROM uservisits took 8,293 ms on average before the rewrite and around 397 ms after it. So a query that kept users waiting for 8 seconds now returns in well under half a second.

GROUP BY queries on STRING columns with doc values enabled now read the values directly from doc values where possible. Here we saw a performance increase of more than 100%. How much you gain depends on the number of such columns in the GROUP BY clause and their combined cardinality.

Keys-only GROUP BY queries on text columns without a filter, e.g., SELECT text_column FROM tbl GROUP BY text_column, now use the Lucene term dictionary. For high-cardinality columns, these queries take 41% less time in our benchmark. This is typical for filling drop-downs and filters in dashboards with all distinct values of a column.

Query Planning

The better the planner understands your data and queries, the less time you spend on rewriting queries by hand. ANALYZE now collects statistics for foreign tables, so the planner can make better decisions for queries on them. This is useful if you query data in other PostgreSQL databases from CrateDB and join it with your CrateDB data. The optimizer also simplifies some redundant conditions, like double negations or duplicate join conditions. Such conditions are often created by BI tools or ORMs, so these generated queries now run faster. And queries against information_schema views are faster now, because view definitions are cached. 

SQL and PostgreSQL Compatibility

We added the pg_is_in_recovery() function and the information_schema.constraint_column_usage view. Both are queried by tools like DataGrip, Pgpool-II, and lazysql, so you can now use these tools with CrateDB without running into errors. In addition, date can be used as a column type. If you migrate from PostgreSQL, you can take over more of your existing schemas as they are.

There are also a few new SQL features that save you some work. You can drop multiple tables with a single statement, e.g., DROP TABLE t1, t2, t3, which is handy for cleanups and scripts. Bitstrings can be concatenated with the concat function or the || operator, and the new regexp_match scalar function makes it easier to extract parts of strings with regular expressions.

Authentication

The JWT authentication method now supports OpenID Connect Discovery. CrateDB can get the configuration of your identity provider automatically, so setting up and maintaining JWT authentication needs less manual configuration, for example, when signing keys get rotated.

Resilience of SWAP TABLE and RENAME TABLE

Many users use SWAP TABLE to replace a table with a newly loaded or restructured version without downtime. With 6.5 this is safer to do while other operations are running.

Swapping and renaming tables no longer reallocates the shards of the affected table. This means snapshots that are in progress can finish successfully. Before, these snapshots failed and stayed in PARTIAL state. So you can rely on your backups, even if a SWAP TABLE happens during a snapshot.

In addition, DELETE, DROP TABLE, DROP COLUMN, ADD COLUMN, RENAME COLUMN, SET/DROP DEFAULT, DROP CONSTRAINT, SET/RESET, and REROUTE are now protected against a concurrent SWAP TABLE while they are already running. RENAME TABLE fails if the source name points to a different table during execution, and SWAP TABLE fails if the source or target name does. This prevents a statement from ending up working on a different table than you intended.

A Note on Breaking Changes

CrateDB 6.5.0 contains some breaking changes. Please check them in the release notes before you upgrade.

External contributions

CrateDB is open source, licensed under Apache 2.0, and we value contributions from our community a lot. For this release, we got many of them:

• Manish Singh added dropping multiple tables with a single DROP TABLE statement and the pg_is_in_recovery() function.

• Dhruv Patel added date as a column type in CREATE TABLE and the normalization of NOT (NOT <predicate>) in query planning.

• Gaurav K Ohri added the regexp_match scalar function.

• Bing O'Dowd added deduplication of identical join conditions combined with AND.

• Somil Jain added caching of analyzed view definitions, table-level statistics for foreign tables in ANALYZE, the information_schema.constraint_column_usage view and concatenation of bitstrings with concat and ||.

Thanks a lot to all of you!

For the complete list of changes, including all fixes and smaller improvements, see the official CrateDB 6.5.0 release notes.