Time Series Database for Real Time Analytics | CrateDB

CrateDB is a distributed time series database built to handle high-volume ingestion, real time analytics, and long-term retention without the limitations of traditional time series systems. With a columnar engine optimized for fast aggregations, automatic indexing, and native SQL, CrateDB lets you analyze fresh and historical data with the same ease, no matter how fast your data grows.

Store every event, keep full fidelity across billions of rows, and run analytics, search, and AI workloads together in one scalable engine.

Time series data querying with SQL

Hyper-fast. Results in milliseconds.

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

        
/* To identify gaps on the readings, the following queries generates a series
 * and by joining it with the original data, you can spot any gap */
with avg_battery AS (
  SELECT battery_level, time 
  FROM devices.readings
  WHERE device_id = 'demo000007' 
  AND time > 1480118400000 
  AND time < 1480301200000
  ORDER BY 2
),

all_hours AS (
  SELECT generate_series(1480118430000,1480301200000,'30 second'::interval) AS generated_hours
)

SELECT time, generated_hours, battery_level
FROM all_hours 
LEFT JOIN avg_battery ON generated_hours = time
ORDER BY 2
LIMIT 20;

        
+---------------+---------------+---------------+
|          time |         hours | battery_level |
+---------------+---------------+---------------+
| 1480118430000 | 1480118430000 |            67 |
| 1480118460000 | 1480118460000 |            66 |
| 1480118490000 | 1480118490000 |            66 |
| 1480118520000 | 1480118520000 |            66 |
| 1480118550000 | 1480118550000 |            66 |
| 1480118580000 | 1480118580000 |            66 |
| 1480118610000 | 1480118610000 |            65 |
| 1480118640000 | 1480118640000 |          NULL |
| 1480118670000 | 1480118670000 |            65 |
| 1480118700000 | 1480118700000 |            65 |
| 1480118730000 | 1480118730000 |            65 |
| 1480118760000 | 1480118760000 |            65 |
| 1480118790000 | 1480118790000 |            65 |
| 1480118820000 | 1480118820000 |            65 |
| 1480118850000 | 1480118850000 |            65 |
| 1480118880000 | 1480118880000 |            65 |
| 1480118910000 | 1480118910000 |            65 |
| 1480118940000 | 1480118940000 |            65 |
| 1480118970000 | 1480118970000 |          NULL |
| 1480119000000 | 1480119000000 |          NULL |
+---------------+---------------+---------------+

        
/* Based on device data, this query returns the number of battery charges 
 * per day for a given device_id */
WITH aux_charging AS (
    SELECT time, 
      DATE_BIN('P1D'::INTERVAL,time,0) AS day, 
      battery_status, 
      LAG(battery_status) OVER (PARTITION BY device_id ORDER BY time) AS prev_battery_status
    FROM devices.readings
    WHERE device_id = 'demo000001'
    ORDER BY time
  ),

count_start_charging AS (
    SELECT day, (case when battery_status <> prev_battery_status then 1 else 0 end) AS start_charging
    FROM aux_charging
    ORDER BY 1
  )

SELECT day, sum(start_charging) as charges_number
FROM count_start_charging
GROUP BY 1
ORDER BY 1;

        
+---------------+---------------+
| count_charges |           day |
+---------------+---------------+
|             2 | 1479168000000 |
|             4 | 1479254400000 |
|             2 | 1479340800000 |
|            10 | 1479427200000 |
|             7 | 1479600000000 |
|             8 | 1479686400000 |
|             6 | 1479772800000 |
|            11 | 1479859200000 |
|             5 | 1480032000000 |
|             7 | 1480118400000 |
|             6 | 1480204800000 |
|            10 | 1480291200000 |
|             3 | 1480464000000 |
|             3 | 1480550400000 |
|             7 | 1480636800000 |
|             2 | 1480723200000 |
+---------------+---------------+

        
/* Based on device data, this query returns the average of the battery temperature
 * for each OS version */
SELECT device_info['os_name'], avg(battery_temperature)
FROM "devices"."readings"
GROUP BY 1
LIMIT 100;

         
+---------+--------------------------+
| os_name | avg(battery_temperature) |
+---------+--------------------------+
| 4.4.4   |        90.85937893039049 |
| 5.1.0   |        90.86754559738132 |
| 6.0.1   |        90.84230101265824 |
| 5.0.0   |        90.8574802739726  |
+---------+--------------------------+

High-performance storage for time series data

CrateDB’s columnar storage format accelerates analytics on time-stamped data, even at multi-year scale. It efficiently handles wide tables with hundreds of columns and delivers fast scans, aggregations, and filtering across large datasets.

This makes CrateDB ideal for:

High-frequency sensor data
Event streams
Telemetry and operational metrics
Application logs
Long-term analytical workloads

Whether you’re running rolling averages, multi-day dashboards, or ad-hoc investigations across billions of records, CrateDB keeps performance consistent.

Unlimited cardinality and long-term retention

Traditional time series databases often rely on downsampling or rigid retention policies. CrateDB removes these limitations.

With time-based partitioning and tiered storage, you can:

Store granular data at full resolution
Retain historical data cost-effectively
Keep recent partitions on SSDs for fast queries
Move older partitions to cheaper disks without sacrificing access
Query years of data with predictable performance

Keep every detail today, and unlock new insights tomorrow as your analytics evolve.

Built-in time series functions

CrateDB includes advanced SQL functions for streamlined time-series analysis, without complex pipelines or custom tooling.

DATE_BIN for consistent resampling
LEAD / LAG (with IGNORE NULLS) for filling gaps and trend detection
WINDOW functions for moving averages, cumulative sums, and rolling metrics
JOIN support to enrich metrics with metadata or reference tables
Aggregation functions optimized for large, continuous datasets

Everything runs on standard SQL, so teams stay productive with familiar tools and workflows.

More than a time series database

CrateDB is built for real time analytics across diverse data types. Store and query complex structures alongside time series data, all in one engine:

JSON documents
Vector embeddings
Full-text data
Geospatial shapes
Relational tables
Binary objects

This unified model lets you simplify your architecture by eliminating the need for separate systems for logs, metrics, search, or vector search.

Native SQL interface

CrateDB uses native SQL for all operations, making analytics accessible to any developer or analyst.

Standard SQL queries
PostgreSQL wire protocol support
HTTP endpoint for simple integration
Compatibility with BI tools, data science notebooks, and modern pipelines

No proprietary languages or query models to learn.

Integrations with your analytics ecosystem

Visualize, process, and explore time series data effortlessly with the tools you already use:

Grafana for dashboards and monitoring
Python + Pandas, Dask, Spark for data science workflows
Matplotlib, Plotly for visualization
Connectors and drivers for popular languages and frameworks

CrateDB fits naturally into existing analytics stacks.

Distributed, scalable architecture

CrateDB is built on a distributed SQL engine that scales horizontally and handles demanding workloads with ease:

Billions of time-stamped events
High-throughput ingestion from IoT devices, gateways, apps, or logs
Concurrent analytics and search queries
Stable performance under load
Elastic scaling as your data grows

Add nodes when needed and maintain low-latency insights at any scale.

Open source and deployment flexibility

CrateDB’s open-source foundation combines transparency, flexibility, and operational freedom.

Choose the deployment model that fits your needs:

CrateDB Cloud: fully managed, secure, always up to date
Self-managed: on-premises or in your private cloud

Both options deliver the same fast, scalable, real time performance.

Learn more about CrateDB

Guide for time series data projects

Whitepaper" Guide for Time Series Data Projects

Guide for time series data projects

This comprehensive guide covers the different key aspects of time series data projects. It is divided in 3 distinct white papers. Part 1: data modeling, storage, lifecycle; Part 2: ingestion, indexing, analysis, and optimization; Part 3: visualization, and advanced analysis through machine learning.

Demo: Querying heterogeneous time-series data with SQL

Demo: Querying heterogeneous time-series data with SQL

In this video, discover how to effortlessly create a table and seamlessly import weather data into CrateDB. Witness the power of CrateDB's time-series query capabilities in action with the weather dataset, showcasing the dynamic schema flexibility. Dive deeper into CrateDB's multi-modal features with demonstrations on handling JSON, geospatial data, and conducting full-text searches.

Top 8 most asked questions

Top 8 Most Asked Questions around Time-series Databases

Top 8 most asked questions

Discover some of the common questions around time-series databases, including their advantages over traditional databases, best practices for managing them, and the industries that can benefit the most from their adoption. This white paper contains 8 essential things you need to know about time-series databases.

Time series online course

Advanced Time Series Course: learn.cratedb.com

Time series online course

The free CrateDB Advanced Time Series course will teach you all you need to know about using CrateDB for time series data. Once you’ve completed this course you’ll be ready to get your certificate and tackle your first time series project with CrateDB.

Supply chain & Logistics

TGW Logistics Group is one of the leading international suppliers of material handling solutions. As systems integrator, TGW plans, produces and implements complex logistics centres, from mechatronic products and robots to control systems and software.

Using CrateDB, TGW accelerates data aggregation and access from warehouse systems worldwide, resulting in increased database performance. The system can handle over 100,000 messages every few seconds.

"CrateDB is a highly scalable database for time series and event data with a very fast query engine using standard SQL".

Alexander Mann
Owner Connected Warehouse Architecture
TGW Logistics Group

Read more

TGW-Warehouse-2

Construction

Thomas Concrete Group is the world- leader organization in the construction industry with 150+ concrete plants and 2,100+ employees. They use CrateDB both for tracking of their delivery trucks and tracking the curing of the concrete in real-time.

"Thanks to CrateDB's great indexing, dedicated data types, and subsequent great performance, we could execute an event and data-driven architecture, with the performance and scalability necessary for storing time-series data over time. The SQL query syntax capability of CrateDB also played a part in achieving this great outcome, as it made it easy for the team to write good performing queries using existing know-how. CrateDB is now an integral part of our big data streaming architecture and it is delivering as promised."

Kristoffer Axelsson
Principal Solution Architect
Thomas Concrete Group

Read more

Thomas

Entertainment

Digital domain is a global leader in visual effects, interactive content and creating “virtual humans” for use in films and live events. Rather than use a commercial monitoring system, they chose to build their own performance monitoring solution. They use CrateDB to process a lot of system metrics data (time series) in real time and to support streaming log analytics, which required search capability.

"I'm glad it's SQL behind those charts. If I had to use Elasticsearch to answer new questions, we wouldn't be nearly as responsive to new requirements."

Joe Hacobian
Infrastructure Engineer
Digital Domain

Read more

Digital-domain

Time-series data is a sequence of data points organized chronologically, illustrating how variables change over time. This format is characterized by patterns such as trends, seasonal variations, and irregularities. Effective time-series data management can significantly enhance query performance and data analysis. CrateDB supports time-series data with built-in functionalities like window functions and time-based indexes, making it easier to analyze and query the data efficiently.

Examples of time series data include temperature readings, stock prices, population growth, monthly subscriptions, quarterly sales, and interest rates. CrateDB supports time series data and offers a comprehensive solution by allowing the integration of multiple data types such as JSON, vector, full-text, geospatial, BLOB, and relational data, minimizing the need for additional technologies and complex maintenance.

Efficient storage of time series data is essential, considering factors like data volume, speed, query requirements, and scalability. The primary storage solutions include time-series databases, NoSQL databases, relational databases, and data warehouses, each offering unique strengths and limitations. CrateDB is designed to handle complex time series data with the simplicity of SQL, allowing for high ingest rates and integration of various data types, making it an ideal choice for storing and querying extensive time series data efficiently. Read more about time series data storage >

When choosing a time-series database, consider factors such as performance, scalability, query language capabilities, data model flexibility, security, maintainability, and reliability. Examples of time-series databases include CrateDB, InfluxDB, KX, and Timescale. CrateDB stands out as a distributed database that can scale horizontally across multiple nodes, making it especially suited for handling large volumes of time-series data from diverse sources in real-time. Learn how to choose the best database for time series data >

Time-series data analysis allows us to analyze and understand how variables change over time. It is widely used across various industries, such as manufacturing, transportation and logistics, and the energy sector. Regardless of the industry, time-series data is crucial for enabling data-driven decision-making. With CrateDB, you can easily enrich your time series data with other data types, handle high ingest rates, and store years of history. Read how TGW Logistics Group uses CrateDB as their modern time series database >