CrateDB - Database for Application Backends

(Traditional) relational databases offer SQL accessibility, high data availability and easy integration but they are expensive to scale, unable to process the vast amount of incoming data and unable to perform complex queries with quick response times.

CrateDB is easy to scale and offers no impact of volume on performance.

NoSQL databases offer scalability, flexibility and distributed architecture but generate high costs, require complex infrastructures (with intensive planning and administration to operate correctly) and need specific engineering expertise.

CrateDB is very simple to use with SQL, offers easy integration with 3rd party solutions and is much more cost efficient.

Combining both types of databases might go around some limitations but is highly costly, needs complex data synchronization to run in parallel, requires complex setups and has unnecessarily large cloud footprints. Not even mentioning the complexity of managing multiple database schemas, designing cross-database queries and the resulting slow performance.

CrateDB offers the benefits of both with no additional investment.

(Specialized) time-series database offer interesting capabilities but are not designed for interactive work under read/write intensive loads. They are also often required to be associated with other database types and are mostly single-node operations. Any change to the data models often incurs full reindexing and interruption of service. Finally, it’s difficult due to their architecture to implement advanced techniques such as machine learning on top of it.

CrateDB is a perfect fit for time-series. You keep getting query results in milliseconds, even for complex queries, multiple data types and high ingest rates, while storing years of data.

Know more about the product

Examples of SQL queries

        
        
/* Based on device data, this query returns the average
 * of the battery level for every hour for each device_id
 */
WITH avg_metrics AS (
    SELECT device_id,
       DATE_BIN('1 hour'::INTERVAL, time, 0) AS period,
       AVG(battery_level) AS avg_battery_level
    FROM devices.readings
    GROUP BY 1, 2 
    ORDER BY 1, 2
)
SELECT period,
       t.device_id,
       manufacturer,
       avg_battery_level  
FROM avg_metrics t, devices.info i
WHERE t.device_id = i.device_id 
      AND model = 'mustang'
LIMIT 10;

        
        
+---------------+------------+--------------+-------------------+
|    period     |  device_id | manufacturer | avg_battery_level |
+---------------+------------+--------------+-------------------+
| 1480802400000 | demo000001 |    iobeam    | 49.25757575757576 |
| 1480806000000 | demo000001 |    iobeam    | 47.375            |
| 1480802400000 | demo000007 |    iobeam    | 25.53030303030303 |
| 1480806000000 | demo000007 |    iobeam    | 58.5              |
| 1480802400000 | demo000010 |    iobeam    | 34.90909090909091 |
| 1480806000000 | demo000010 |    iobeam    | 32.4              |
| 1480802400000 | demo000016 |    iobeam    | 36.06060606060606 |
| 1480806000000 | demo000016 |    iobeam    | 35.45             |
| 1480802400000 | demo000025 |    iobeam    | 12                |
| 1480806000000 | demo000025 |    iobeam    | 16.475            |
+---------------+------------+--------------+-------------------+

        
        
/* Based on reports from IoT devices, this query returns
 * the voltage corresponding to the maximum
 * global active power for each meter_id
 */

SELECT meter_id,
       MAX_BY("Voltage", "Global_active_power") AS voltage_max_global_power
FROM iot.power_consumption 
GROUP BY 1 
LIMIT 10;

        
        
+------------+--------------------------+
|   meter_id | voltage_max_global_power |
+------------+--------------------------+
| 840073190N |          233.57          |
| 840072401F |          233.53          |
| 840072655G |          234.1           |
| 840071893D |          234.47          |
| 840073950P |          231.73          |
| 840075260N |          235.51          | 
| 840076398A |          234.56          |
| 84007B071E |          231.94          |
| 840075959Y |          237.21          |
| 840072534A |          231.96          |
+------------+--------------------------+

        
        
/* Based on the location of the International Space Station, 
 * this query returns the 10 closest capital cities from 
 * the last known position 
 */
SELECT city as "City Name",
       country as "Country",
       DISTANCE(i.position, c.location)::LONG / 1000 AS "Distance [km]"
FROM demo.iss i
CROSS JOIN demo.world_cities c
WHERE capital = 'primary'
      AND ts = (SELECT MAX(ts) FROM demo.iss)
ORDER BY 3 ASC
LIMIT 10;

        
        
+--------------+-----------------------------------+---------------+
|  City Name   |             Country               | Distance [km] |
+--------------+-----------------------------------+---------------+
|    Papeete   |         French Polynesia          |      3386     |
|    Avarua    |           Cook Islands            |      3708     |
|  Wellington  |            New Zealand            |      4565     |
|     Alofi    |                Niue               |      4628     |
|  Nuku‘alofa  |               Tonga               |      4887     |
|  Pago Pago   |          American Samoa           |      5063     |
|   Santiago   |               Chile               |      5112     |
|     Apia     |               Samoa               |      5182     |
|    Stanley   | Falkland Islands (Islas Malvinas) |      5266     |
|     Suva     |               Fiji                |      5611     |
+--------------+-----------------------------------+---------------+

        
        
/*
 * Based on system event logs, this query calculates:
 * - a filter for specific messages using a full-text index
 * - the number of entries per minute
 * - the average scoring ratio for each matched row
 */
SELECT DATE_TRUNC('minute', receivedat) AS event_time,
       COUNT(*) AS entries,
       AVG(_score) AS avg_score
FROM "syslog"."systemevents"
WHERE MATCH(message, 'authentication failure') 
USING most_fields WITH (analyzer = 'whitespace')
   AND MATCH(syslogtag, 'sshd')
GROUP BY 1
ORDER BY 1 DESC
LIMIT 10;

        
        
+---------------+---------+--------------------+
|    event_time | entries |          avg_score |
+---------------+---------+--------------------+
| 1620220260000 |       4 | 1.5798743814229965 |
| 1620220200000 |       8 | 1.7750384211540222 |
| 1620220140000 |      10 | 1.6113891124725341 |
| 1620220080000 |       9 | 1.676726798216502  |
| 1620220020000 |       8 | 1.6908064410090446 |
| 1620219960000 |       8 | 1.690401442348957  |
| 1620219900000 |       7 | 1.7646006005150932 |
| 1620219840000 |       7 | 1.7795820917401994 |
| 1620219780000 |      10 | 1.5844267368316651 |
| 1620219720000 |      13 | 1.5637413492569556 |
+---------------+---------+--------------------+

Application backend, Telco

VoIPstudio is an award-winning VoIP phone service. They use CrateDB to manage call detail records (CDRs). MySQL had been used successfully for several years, but due to increasingly poor performance it was replaced by CrateDB.

"After a successful and problem-free implementation it became clear to us that CrateDB was the solution we were looking for. We gained better performance and new possibilities for our customers. We are going to improve and extend our cloud system using CrateDB."

Application backend, Services

ClearVoice provides content writing services and with an ecosystem of top writers. They use CrateDB to power their search index, which stores 20 million records and this is expected to grow to billions in the near future.

"I started looking at CrateDB and was impressed by the quality of the code. Switching from MySQL to CrateDB took a couple of days. It was very convenient to integrate CrateDB into our existing base even though it was written for a different database. The fact that CrateDB uses SQL lowers the barrier to entry when using distributed search. And on top of that, with CrateDB you can replace MongoDB and Elastisearch with one scalable package ."

Jeff Nappi
Director of Engineering
ClearVoice

Application backend, Software

The GolfNow division of the NBC Universal GolfChannel is trusted by 4 million registered golfers to search for golf courses that fit preference and budget. They use CrateDB to currently perform real-time queries of more than 300 million rows of data, hundreds of times per second.

"CrateDB was a better solution for our needs than any other SQL or NoSQL database we tried. It was easy to migrate code off of our legacy SQL database and onto CrateDB to immediately benefit from its data flexibility and scalable performance."

Sheriff Mohamed
Director of Architecture
GolfNow

	Traditional RDBMS	NoSQL	Time-series databases	CrateDB
High data ingestion
High data versatility
Real-time performance
Simple scalability
SQL access

Database for Application Backend
that truly Scales

Interested?

Examples of SQL queries

ClearVoice migrates from MySQL to CrateDB

Want to know more?

Company

Ecosystem

Contact

Database for Application Backendthat truly Scales

Interested?

Examples of SQL queries

ClearVoice migrates from MySQL to CrateDB

Want to know more?

Database for Application Backend
that truly Scales