Our Fall release of CrateDB is out, with a mix of new data types, query functionality, and improvements for administrators. And, of course, a performance improvement as well as additional information in EXPLAIN ANALYZE.
Features & Functionality
New fixed precision NUMERIC / DECIMAL data type
Commonly used in the financial industry, also certain engineering industries care about precise measurements and don’t want to deal with the tiny yet unavoidable rounding errors of floating point arithmetics.
CREATE TABLE mytable (precise_num NUMERIC(18,5)); INSERT INTO mytable (precise_num) VALUES("3453336538601.04365"); SELECT precise_num + 1 FROM mytable;
Basic operators + - * / and so on now support NUMERIC values, including the five basic aggregation functions MIN(), MAX(), SUM(), AVG(), and COUNT(). More functions will be supported in future CrateDB releases.
TOPK()
A common query is to get the values that occur most often in some samples: For example, which countries have the most Alps?
select topk(country, 3) from sys.summits; +--------------------------------------------------------------------------------------------------------+ | topk(country, 3) | +--------------------------------------------------------------------------------------------------------+ | [{"frequency": 436, "item": "IT"}, {"frequency": 401, "item": "AT"}, {"frequency": 320, "item": "CH"}] | +--------------------------------------------------------------------------------------------------------+ SELECT 1 row in set (... sec)
IS DISTINCT FROM
Added Lucene integration to the previously introduced IS DISTINCT FROM command. In other words, queries using DISTINCT FROM would previously not benefit from the Lucene indexes, but now utilize them. Naturally, such queries are now much faster!
SELECT * FROM football_results WHERE final_score IS DISTINCT FROM null;
Thank you, Martin Stein, for contributing this feature.
Improved Azure support
We added syntax to better support operations against Azure Blob Storage and Azure Snapshot repositories. The az:// URI format mimics the s3:// URIs used in AWS. In addition we allow to supply the sas_token outside the URI, so that it’s slightly more hidden, for example in log messages.
COPY source TO DIRECTORY 'az://myaccount.blob.core.windows.net/my-container/dir1/dir2/file1.json' WITH ( key = 'key' )
Admin
sys.sessions
Introduced a new system table where an administrator can list all active sessions. This table provides useful information about the users currently connected to the cluster, like their IP address, the last executed statement, whether the connection is through HTTP, or PG wire protocol, if it uses SSL or not, etc.
Session settings
It is now possible to specify default session settings for a role. See ALTER ROLE SET <setting> = <value>;
relations column in sys.snapshots
Added a column relations to sys.snapshots, which conveniently shows the tables and/or partitions in a snapshot.
Performance
ORDER BY optimization
Added a new rule to the query optimizer, which removes unnecessary ORDER BY clauses from a subquery when executing INSERT FROM <subquery> statements.
EXPLAIN improvements
Allow the use of EXPLAIN ANALYZE on queries with scalar subselects
Include shard/partition information in EXPLAIN ANALYZE output
PostgreSQL Compatibility
Made the `proisstrict`` property of the ``pg_catalog.pg_proc` more accurate.
Return actual CrateDB user in pg_get_userbyid()
You can download a new release here or create a new cluster in CrateDB Cloud.
The full release notes contain a more complete list of fixes that were intentionally not included here.