CrateDB SQL vs. Elasticsearch Comparison

CrateDB is a fully-featured distributed SQL database. Beneath the covers, CrateDB uses technology borrowed from NoSQL projects, including Elasticsearch. CrateDB embeds open source code from Elasticsearch to handle cluster management, node discovery and communication. If you’re an Elasticsearch user, you’ll notice similarities in the way that Crate and Elasticsearch scale.

Here are the differences in the CrateDB and Elasticsearch experience:


CrateDB supports all join variants, including cross joins, inner joins, left outer joins, right outer joins and full outer joins.

Elasticsearch does not support joins.

Blob Storage

CrateDB also offers a complete solution for distributed blob storage that comes with replication and rebalancing. This offers you the opportunity to replace expensive network or cloud storage solutions with cheap commodity hardware.

Elasticsearch does not support blob storage. Typically, Elasticsearch is used together with GridFS or HDFS for blob storage.

Aggregation Functions

Elasticsearch offers HyperLogLog aggregations, which return approximate results.

CrateDB SQL aggregations return exact results by default (though, if desired, the HyperLogLog++ algorithm can also be applied in count distinct queries).

CrateDB distributes aggregation calculations across the whole cluster using a simple modulo based hashing algorithm. As a result, aggregation calculations use the complete memory and processing power of the cluster.

Elasticsearch scatters queries to all the nodes, and then gathers the responses on the node that is handling the client request, resulting in high memory usage, and under-utilization of your cluster’s resources.

Post-Aggregation Filtering

Post-aggregation filtering (i.e. GROUP BY ... HAVING) is fully implemented in CrateDB.

Elasticsearch has a number of limitations on this type of query.

Partitioned Tables

CrateDB supports the creation of partitioned tables, which transparently partition your data based on the value of particular column.

Elasticsearch supports table aliases. This can be used to achieve the same, but you will have to implement the logic for yourself in your application.

Import and Export

CrateDB supports the COPY FROM and COPY TO SQL statements for exporting or importing data in JSON format.

Elasticsearch does not offer an import or export feature.

Array Support

CrateDB fully supports array types.

Elasticsearch does not strictly distinguish between arrays and core types (a string type can be string or string array depending how you insert it).

Update by Query

CrateDB allows you to update one or multiple documents with a WHERE clause.

Elasticsearch only allows you to update a single document at a time, and you must reference the document by its _id value.

Insert by Query

CrateDB allows you to insert data with the results of a query instead of manually passing in the data values.

This feature can be used to dynamically create new records as well as to restructure a table by renaming a field, changing a field’s data type, or by converting a normal table into a partitioned one.

Elasticsearch does not support insertion via query.

Integrated Admin UI

CrateDB ships with an administration user interface. This admin UI shows cluster, node, and table information. It also includes an interactive SQL console, notifications of new CrateDB news, and a “Getting Started” section.

Resource Library

CrateDB Comparison Resources

Words Vs standing for Comparison on a lightcyan background with rectangles on it
White Paper

CrateDB, Cassandra and MongoDB

Learn how CrateDB data model and querying ca­pa­bilities compare with Mon­goDB and Cassandra.

Words Vs representing Comparison on a lightgrey background with lines and rectangles on it
White Paper

CrateDB vs. PostgreSQL

How CrateDB query per­formance compares to a traditional SQL DBMS (PostgreSQL).

Words VS representing a Comparison written on cyan background with lines and rectangles in different sizes
White Paper

CrateDB vs. Time Series Databases

Learn about functional differences between CrateDB time series and databases like InfluxDB.

Words VS representing Comparison on a white background with lines and rectangles in different sizes
White Paper

Linear Scalability Benchmark

Showing how CrateDB query throughput scales linearly as the cluster grows.