The most recent CrateDB version has been released as CrateDB 6.3. The focus for the development cycle has been on compatibility to make it easier to integrate with 3rd party tools.
As always, CrateDB aims at being a robust analytical database, and this development cycle is no exception. CrateDB 6.3 comes with improvement to resilience of the day-to-day operations of the database.
Unifying object store access
The COPY TO and COPY FROM commands can be used with Azure, S3, and GCS endpoints. Prior to CrateDB 6.3, some parameters were only used to configure Azure, while others were for S3 only, etc. With CrateDB 6.3 we have unified access to object stores, and the following parameters have been removed:
- Azure: location_mode, secondary_endpoint, endpoint_suffix, timeout, proxy_type, proxy_host, proxy_port
- S3: canned_acl
- GCS: token_uri
SQL and PostgreSQL compatibility
The release of CrateDB 6.3 marks a big milestone in the progress of compatibility – with the SQL standard in general, and with PostgreSQL in particular. PostgreSQL compatibility is important as it opens to a vast ecosystem of 3rd party software.
Compatibly when working with default values has been expanded. It is possible to drop and set a default value for a column after it has been created: ALTER COLUMN SET/DROP DEFAULT. Moreover, you can now explicitly declare that a default value/expression must be used in INSERT
Creating a new table based on an existing table can be as easy as using the new CREATE TABLE ... LIKE.
DROP SCHEMA was introduced in CrateDB 6.2, In CrateDB 6.3, the functionality is expanded by accepting the RESTRICT keyword, which behaves as previously, and implementing the CASCADE functionality to drop all dependent objects instantly.
SELECT statements can omit the column list. This pattern is used in tools like TinyETL
A SQL statement with only a comment is now valid. This is often used by Golang’s pgx to ping the server.
Moreover, the useful Boolean predicates IS [NOT] TRUE | FALSE have been added.
Five scalar functions have been added: regexp_count(), regexp_inst(), parse_indent(), pg_sleep() and has_function_privilges(). This gives CrateDB better PostgreSQL compatibility. The later function is known to be used by pgpool-II. Moreover, the scalar function percentile() supports the type interval.
Furthermore, a new table has been added to information_schema: view_column_usage. The table identifies the columns used in a query expression. Moreover, the table columns in information_schema includes values for the udt_name and udt_catalog columns.
Resilience
No CrateDB release without improvement of resilience. ANALYZE is now processing the tables sequentially instead of concurrently. This puts a lower I/O burden on the database, and other operations are not heavily impacted as before. The tradeoff is that ANALYZE is likely to take longer.
To control the circuit breaker mechanism, a new setting – indices.breaker.policy - is introduced. The default value is current which mimics the previous behavior. An alternative behavior (top_consumer) is to abort the query with the highest memory consumption. This new behavior is considered experimental, and it might change in the future.
SWAP and RENAME TO will not interfere with snapshots. Previously, it was possible that a snapshot would fail and be left in the PARTIAL state if one of two statements was executed simultaneously. Likewise, INSERT INTO statements which create new partitions will not be interrupted by SWAP or RENAME TO.
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:
- Dhruv Patel contributed to SQL compatibility and scalar functions, and takes the prize as the top external contributor of CrateDB 6.3
- Şahin Akyol added Boolean predicates to enhance PostgreSQL compatibility
- Daniel Martínez Maqueda and Marc Theisen worked on scalar functions related to regular expressions.
The CrateDB core team would like to take the opportunity to thank each of them for moving CrateDB forward.