timescaledb

TimescaleDB

TimescaleDB is a time-series database built as an extension on top of PostgreSQL. It gives you the scale of NoSQL time-series with the reliability and tooling of Postgres.

When to Use

• High-Velocity Time-Series on PostgreSQL: Ingesting and querying millions of metrics, IoT sensor data, and financial ticks within PostgreSQL.
• Continuous Aggregations & Materialized Views: Automatically computing real-time 1-minute, hourly, or daily rollups as data arrives.
• Native Columnar Compression: Reducing time-series storage footprints by 90%+ using chunk-level columnar compression.
• Data Retention & Automated Tiering: Automatically dropping or migrating historical time-series data to cold object storage.

Quick Start

-- Convert standard table to hypertable
SELECT create_hypertable('conditions', 'time');

-- Query using standard SQL time-bucket functions SELECT time_bucket('15 minutes', time) AS bucket, avg(temperature) FROM conditions GROUP BY bucket ORDER BY bucket DESC;

## Core Concepts

### Hypertables & Automated Time Chunking

A hypertable exposes a unified SQL table interface while partitioning data into time-based chunks automatically in the background:

```sql
CREATE EXTENSION IF NOT EXISTS timescaledb;

CREATE TABLE stock_ticks (
    time TIMESTAMPTZ NOT NULL,
    symbol VARCHAR(10) NOT NULL,
    price DOUBLE PRECISION NOT NULL,
    volume INT NOT NULL
);

-- Convert to Hypertable with 1-day chunk intervals
SELECT create_hypertable('stock_ticks', 'time', chunk_time_interval => INTERVAL '1 day');

Continuous Aggregates (Real-Time Rollups)

Maintains incrementally updated aggregate views that combine historical materializations with raw live incoming data:

CREATE MATERIALIZED VIEW stock_candlestick_1h
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS bucket,
    symbol,
    first(price, time) AS open_price,
    max(price) AS high_price,
    min(price) AS low_price,
    last(price, time) AS close_price,
    sum(volume) AS total_volume
FROM stock_ticks
GROUP BY bucket, symbol;

Columnar Chunk Compression Policy

Automatically compresses chunks older than a specified duration into columnar format:

ALTER TABLE stock_ticks SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'symbol',
    timescaledb.compress_orderby = 'time DESC'
);

-- Automatically compress data older than 7 days
SELECT add_compression_policy('stock_ticks', INTERVAL '7 days');

Common Patterns

Continuous Aggregates with Real-Time Materialization

Problem: Calculating hourly and daily aggregations across hundreds of millions of raw metric rows causes slow dashboard loading.

Solution: Create a TimescaleDB continuous aggregate view:

CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', recorded_at) AS bucket,
  device_id,
  avg(temperature) as avg_temp,
  max(temperature) as max_temp,
  count(*) as reading_count
FROM device_metrics
GROUP BY bucket, device_id;

-- Add automated refresh policy
SELECT add_continuous_aggregate_policy('metrics_hourly',
  start_offset => INTERVAL '3 days',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '30 minutes');

Best Practices

Do:

• Size Chunk Intervals to Fit in Memory: Set chunk_time_interval so that the active chunk fits comfortably within the RAM buffer cache.
• Segment Compression by Entity ID: Use compress_segmentby = 'symbol' to enable rapid column-vector scans on specific entities.
• Use Continuous Aggregates for Dashboards: Query continuous aggregate views rather than scanning millions of raw rows in dashboards.
• Define Automated Retention Policies: Execute add_retention_policy('stock_ticks', INTERVAL '90 days') to purge old data automatically.

Don't:

• Use unindexed secondary columns for filtering: Always index auxiliary columns (symbol, device_id) used in query filters.
• Mutate historical data in compressed chunks frequently: Updating compressed chunks requires decompressing them, causing I/O spikes.
• Run TimescaleDB without monitoring chunk count: Having tens of thousands of tiny chunks degrades query planning performance.

Troubleshooting

Error	Cause	Solution
table is not a hypertable	Attempted TimescaleDB function on standard PostgreSQL table.	Run SELECT create_hypertable('tablename', 'time_column');.
Continuous aggregate view cannot be refreshed	Time column missing NOT NULL constraint or schedule interval conflict.	Ensure partitioning column is NOT NULL and policy offsets do not overlap.
Disk space exhaustion from uncompressed chunks	Raw historical data chunks consuming excessive storage.	Enable native compression: ALTER TABLE tablename SET (timescaledb.compress);.

References

• TimescaleDB Documentation