Feature Focus: UNION ALL

2018-01-31, by PJ Hagerty

We announced CrateDB 2.3 last week.

The 2.3.x release line isn't stable yet, but if you're interested in trying it out for testing purposes, head on over to the downloads page.

In this post, I will take a closer look at some of the new features.


We have added support for UNION ALL.

Previously, CrateDB did not support UNION queries at all, making it difficult to combine the results of multiple queries. Previously, you had to combine results manually in your application code. And unfortunately, this was often error prone.

For example, here's the sort of query you can do now:

SELECT id_1 FROM table_1
SELECT id_2 FROM table_2
SELECT id_3 FROM table_3

Previously, you would have had to execute all of those SELECT statements individually and then combine the results yourself.

To further improve UNION ALL queries, our engineering team optimized ORDER BY performance. This was done by "pushing down" an ORDER BY to the individual subqueries in a UNION ALL query, as they run across the cluster. This is done instead of sorting the result of the UNION ALL operation after it has been assembled.

We plan to add support for UNION DISTINCT at some point in the future. So keep a lookout!

Container Monitoring

With containers being on the forefront of application deployment and DevOps brains around the world, our team want to ensure that CrateDB was exposing metrics for container monitoring in the best possible way.

Towards this end, we removed our old metrics library, and deprecated a number of the values (such as CPU information, disk IO, network IO, and so on) that you can query from the sys tables. These records are marked as deprecated in the docs and now return -1.

At the same time, we added support for querying cgroups information. This is how CrateDB will expose metrics going forward.


Security and authentication are a concern for every developer in existence. In the previous versions of CrateDB, we had host-based, trust-based, and certificate-based authentication. In this batch, we added traditional password authentication, which, combined with an SSL configuration, provide a convenient and secure option for many basic deployments scenarios.

Password authentication works with HTTP and PostgreSQL wire protocols. And each of our official clients has been updated accordingly.

Wrap Up

For a full list of change, check out the CrateDB 2.3.x release notes.

In particular, the 2.3.x releases add support for an extremely fast HyperLogLog powered distinct count function. And I cover that in my next Feature Focus post.

  • Follow us