The 2024 CrateDB architecture guide covering all key concepts is out.

Download now
Skip to content

The Hyper-Fast Database that Truly Scales

Store any type of data and combine the simplicity of SQL with the scalability of NoSQL. CrateDB is an open source, multi-model, distributed and containerized database that runs queries in milliseconds, regardless of data complexity, volume and velocity.

Query all types of data with SQL

 
        

/* 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            |
+---------------+------------+--------------+-------------------+
        

SELECT text, _score
FROM word_embeddings
WHERE knn_match(embedding,[0.3, 0.6, 0.0, 0.9], 2)
ORDER BY _score DESC; 
        

|------------------------|--------|
|         text           | _score |
|------------------------|--------|
|Discovering galaxies    |0.917431|
|Discovering moon        |0.909090|
|Exploring the cosmos    |0.909090|
|Sending the mission     |0.270270|
|------------------------|--------|
        

SELECT show_id, title, director, country, release_year, rating, _score
FROM "netflix_catalog"
WHERE MATCH(title_director_description_ft, 'title^2 Friday') USING best_fields 
AND type='Movie' 
ORDER BY _score DESC;
        

+---------+------------------------------------+-------------------+----------------------+--------------+--------+-----------+
| show_id | title                              | director          | country              | release_year | rating | _score    |
+---------+------------------------------------+-------------------+----------------------+--------------+--------+-----------+
|  s1674  | Black Friday                       | Anurag Kashyap    | India                | 2004         | TV-MA  | 5.6455536 |
|  s6805  | Friday the 13th                    | Marcus Nispel     | United States        | 2009         | R      | 3.226806  |
|  s1038  | Tuesdays & Fridays                 | Taranveer Singh   | India                | 2021         | TV-14  | 3.1089375 |
|  s7494  | Monster High: Friday Night Frights | Dustin McKenzie   | United States        | 2013         | TV-Y7  | 3.0620003 |
|  s3226  | Little Singham: Mahabali           | Prakash Satam     | NULL                 | 2019         | TV-Y7  | 3.002901  |
|  s8233  | The Bye Bye Man                    | Stacy Title       | United States, China | 2017         | PG-13  | 2.9638999 |
|  s8225  | The Brawler                        | Ken Kushner       | United States        | 2019         | TV-MA  | 2.8108454 |
+---------+------------------------------------+-------------------+----------------------+--------------+--------+-----------+
        

/* 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     |
+--------------+-----------------------------------+---------------+
        

/* The query returns for each device_id the average, minimum and maximum temperature 
 * over the last 100 temperature readings. */
SELECT r.device_id,
       AVG(battery['temperature']) OVER w AS "last 100 temperatures",
       MIN(battery['temperature']) OVER w AS "min temperature",
       MAX(battery['temperature']) OVER w AS "max temperature"
FROM devices_readings r
JOIN devices_info i ON r.device_id = i.device_id
WINDOW w AS (ORDER BY "ts" DESC ROWS BETWEEN 100 PRECEDING AND CURRENT ROW);
        

+------------+-----------------------+-----------------+-----------------+
| device_id  | last 100 temperatures | min temperature | max temperature |
+------------+-----------------------+-----------------+-----------------+
| demo000230 |     88.9              |            88.9 |            88.9 |
| demo000263 |     89.55000000000001 |            88.9 |            90.2 |
| demo000836 |     90.36666666666667 |            88.9 |            92.0 |
| demo001960 |     90.65             |            88.9 |            92.0 |
| demo001780 |     90.1              |            87.9 |            92.0 |
| demo001556 |     90.18333333333334 |            87.9 |            92.0 |
| demo001851 |     90.42857142857143 |            87.9 |            92.0 |
| demo001609 |     90.075            |            87.6 |            92.0 |
| demo002840 |     90.43333333333334 |            87.6 |            93.3 |
| demo002666 |     90.76             |            87.6 |            93.7 |
+------------+-----------------------+-----------------+-----------------+
        
 
SELECT
    title AS title,
    protagonist['first_name'] AS name,
    date_format(
        '%D %b %Y',
        'GMT',
        protagonist['details']['birthday']
     ) AS born,
    quotation['words'] AS quote
FROM quotes limit 100;
        

+---------------+---------+--------------------+
|    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 |
+---------------+---------+--------------------+







Get a unified access to your different types of data

Adopt a fast database that scales with your business

Any type of data

Structured, semi-structured, unstructured, time-series, geospatial, BLOB

Response time in milliseconds

Even for complex ad-hoc queries

Native SQL

For query simplicity and quick onboarding

Aggregations on the fly

Even with complex joins, large datasets and historical data

Flexible data schema

Editable on the fly at runtime

PostgreSQL Wire Protocol

For 3rd party integrations

Full-text search

No need for any extra database

Vector search

No need for additional investment

Open source

No vendor lock-in / Power of the community

Simplify your database operations

High availability

Automatic failover, recovery and replication

Multiple deployment models

DBaaS or self-managed / Edge extension

Cost-efficient architecture

No need to combine and synchronize different databases / Low carbon footprint

Upcoming Events

Meetup

The CrateDB European City Tour is a series of local technical events focused on solving a complex data use case through several hands-on activities,...

Trade Show

Join us at the Data Innovation Summit on April 24-25. We are participating in person in KistaMässan, Stockholm, and online.