The most recent CrateDB version has been released as CrateDB 6.4. The focus for this development cycle has been on performance and resilience of everyday operations, alongside a steady stream of PostgreSQL compatibility work. This release also adds the largest triangle three buckets aggregation function for downsampling.
Performance and resilience
CrateDB 6.4 comes with many major and minor performance improvements throughout the database engine.
A significant performance improvement has been achieved for SELECT strKeyColumn, count(*) FROM <tbl> GROUP BY 1 queries without a WHERE clause, which are up to 140% faster in some of our benchmarks.
Aggregations on varchar(n) columns now perform the same as text columns, so there is no longer a performance penalty for choosing a length-constrained column type. The to_char() scalar function is up to 25% faster. Column pruning for scalar subqueries and improved handling of bulk inserts into tables that combine user-defined functions with check constraints also improve performance. min() and max() aggregations on NUMERIC columns are faster as well. Improved query planning for lookup joins under LIMIT by deferring collection of columns that are not needed during join execution until the final fetch phase.
The system tables sys.jobs, sys.jobs_log, and sys.cluster gained session_id and state columns, which help with troubleshooting. Queries against sys.shards are up to 20% faster when selecting translog sequence-number stats. If a shard's data is corrupted beyond what a replica or backup can fix, the crate-node tool now has a remove-corrupted-data command, so recovering from that situation no longer requires a manual workaround.
SQL and PostgreSQL compatibility
CrateDB 6.4 continues to close gaps with PostgreSQL, which matters because it opens the door to third-party tools. The pg_catalog.pg_user table has been added, resolving schema introspection issues in tools like DbVisualizer. The new pg_get_constraintdef() scalar function improves compatibility for ORMs such as Prisma and TypeORM that rely on it to introspect schemas.
For further compatibility the information_schema.collations table has been added, information_schema.schemata now includes a catalog_name column and a new regtype data type has been introduced. geo_point values can now be imported in JSON array string format (for example "[14.988999953493476, 51.10299998894334]"), so CSV files using this format no longer need to be transformed before loading.
SQL functions and statements
This release adds the largest triangle three buckets (LTTB) aggregation function for downsampling data. LTTB reduces a time series down to a target number of points while keeping the peaks and valleys that make the shape of the data recognizable. This is useful for charting large time-series datasets, where returning every point would be too slow, or too much to render.
The scalar function blake3 was added to compute the BLAKE3 checksums of strings.
The to_char() scalar function has also been extended: it now supports the TH/th template patterns to add ordinal suffixes to numbers, and literal characters can be marked with double quotes so they are not interpreted as formatting tokens.
The new statement ALTER REPOSITORY lets you update settings on an existing backup repository, such as credentials or throttling limits, without dropping and recreating it.
A note on type validation
CrateDB 6.4 makes type validation for INSERT INTO consistent regardless of how many rows are provided. Previously, an implicit cast - for example from an object array to a text[] column - was allowed for single-row inserts but rejected for multi-row inserts. Both cases are now treated the same way, so some previously-working single-row inserts with mismatched types will now fail. The HTTP interface also now sets parameter type hints based on the args payload, which reduces the need for explicit casts on parameter placeholders, but can make cast semantics stricter in some cases. If you relied on the old behavior, it's worth checking your insert statements before upgrading.
External contributions
CrateDB is an open-source product - licensed under the Apache 2.0 license - and we value contributions from our community. It has been a privilege to accept pull requests from the following users:
-
HyeonSeon Won added the
catalog_namecolumn toinformation_schema.schemata -
Dhruv Patel added the
pg_catalog.pg_usertable for PostgreSQL compatibility -
Yogesh Kumar Pandey added support for
geo_pointvalues in JSON array string format -
Varun Kulkarni added support for adding and subtracting intervals with date values, and for literal character quoting in
to_char() -
Robert Palmer added the
blake3scalar function -
Bing O'Dowd added the largest triangle three buckets aggregation function
The CrateDB core team would like to take the opportunity to thank each of them for moving CrateDB forward.
For the complete list of changes, including all fixes and smaller improvements, see the official CrateDB 6.4.0 release notes.
