CrateDB — Real-Time Analytics Database

         

/* Most time-series databases force you to pre-aggregate 
   or flatten your data before you can query across devices
   and dimensions together. 
   With CrateDB, you join device metadata at query time.
   No pre-processing, no ETL, no schema redesign. */

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

         
/* JSON fields are first-class citizens in CrateDB. 
   You can filter, sort, and project nested document fields using 
   standard SQL bracket notation.
   No unpacking step, no separate document store, no ORM gymnastics. */

/* Return the name and truncated description for the 5 Chicago community
   areas with populations over 50,000 people. */

SELECT name, 
       details['population'] AS population, 
       concat(left(details['description'], 25), '...') AS description 
FROM community_areas 
WHERE details['population'] > 50000 
ORDER BY details['population'] DESC
LIMIT 5;

        
+-----------------+------------+------------------------------+
| name            | population | description                  |
+-----------------+------------+------------------------------+
| NEAR NORTH SIDE |     105481 | The Near North Side is th... |
| LAKE VIEW       |     103050 | Lakeview, also spelled La... |
| AUSTIN          |      96557 | Austin is one of 77 commu... |
| WEST TOWN       |      87781 | West Town, northwest of t... |
| BELMONT CRAGIN  |      78116 | Belmont Cragin is one of ... |
+-----------------+------------+------------------------------+

        
/* CrateDB's full-text search is built on Lucene, 
   the same engine as Elasticsearch — but accessed through SQL.
   You get relevance scoring, field weighting, and BM25 ranking without running 
   a separate search cluster alongside your database. */

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

        
/* Vector search runs inside the same SQL engine as your analytics.
   No separate vector database, no synchronization overhead, no dual-write pipeline. 
   One query can combine KNN similarity with filters, time constraints, and aggregations. */

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

        
/* Geospatial queries — distance, containment, routing — 
   run in the same distributed SQL engine as your time-series and analytical workloads. 
   No PostGIS extension to manage, no separate GIS layer. */

/* Using 311 data from the City of Chicago, this query returns 5 open
   work orders for locations closest to the Willis Tower. */

SELECT srnumber, 
       srtype, 
       locationdetails['streetaddress'] AS address, 
       distance(
           'POINT(-87.636256 41.8786492)'::GEO_POINT,
           locationdetails['location']
       ) / 1000 AS distance_km
FROM three_eleven_calls 
WHERE status != 'Completed'
ORDER BY distance_km ASC
LIMIT 5;

        
+---------------+-----------------------------------------------+--------------------+---------------------+
| srnumber      | srtype                                        | address            |         distance_km |
+---------------+-----------------------------------------------+--------------------+---------------------+
| SR24-00711535 | Cab Feedback                                  | 200 S WACKER DR    | 0.09800707616741176 |
| SR24-00694851 | No Building Permit and Construction Violation | 300 W ADAMS ST     | 0.1346164665090538  |
| SR24-00651822 | Sign Repair Request - All Other Signs         | 111 SW WACKER DR   | 0.20355339153863516 |
| SR24-00608464 | Building Violation                            | 235 W VAN BUREN ST | 0.26374860571526554 |
| SR24-00608655 | Building Violation                            | 235 W VAN BUREN ST | 0.26374860571526554 |
+---------------+-----------------------------------------------+--------------------+---------------------+

900,000 sensors per distribution center.

"Having a standardized SQL language is a big advantage with CrateDB. That makes it very easy for people to access this data and work with it in different tools like Grafana or Tableau."

60 million rows looked up in under a second.

"CrateDB allows us to do real-time dashboards on very big streaming and historic datasets in a simple way. We can scale the system easily as we grow the load and customers and have it all done with SQL."

1 million values ingested per second.

"Working with CrateDB brings positive outcomes. The ingestion and throughput have very good performance, with 1 million values/sec, the horizontal scalability where we can add as many nodes as we need and the automatic query distribution across the whole cluster."

400 data records per second.

"We needed a solution that could watch, record and analyze production in real time. CrateDB gives us the freedom to be cumulative and scale limitless - we found no alternative solution with such simplicity and efficiency.”

750 million records per day.

"With CrateDB, we can continue designing products that add value to our customers. We will continue to rely on CrateDB when we need a database that offers great scalability, reliability and speed."

The Moment Data Arrives,
It Is Ready To Query

Real-time industrial analytics

Real-time AI operations

Real-time application analytics

One analytics database. Live and historical data in the same query.

One engine. Time-series, JSON, full-text, vector, and geospatial in one SQL query

Trusted for operational scale

Real-Time Unified Data Layers

A New Era for Scalable Analytics, Search, and AI

Start Querying in Minutes

Product

Developers

Company

Community

The Moment Data Arrives, It Is Ready To Query