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.
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.
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 (i.e.
GROUP BY ... HAVING) is fully implemented in CrateDB.
Elasticsearch has a number of limitations on this type of query.
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.
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
Elasticsearch only allows you to update a single document at a time, and you must reference the document by its
Insert by Query
CrateDB allows you to insert data with the results of a query instead 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.
CrateDB Comparison Resources
CrateDB, Cassandra and MongoDB
Learn how CrateDB data model and querying capabilities compare with MongoDB and Cassandra.
CrateDB vs. PostgreSQL
How CrateDB query performance compares to a traditional SQL DBMS (PostgreSQL).
CrateDB vs. Time Series Databases
Learn about functional differences between CrateDB time series and databases like InfluxDB.