The Postgres Extensions That Turn It Into a Time-Series Database

Time-series data has a reputation for requiring specialized databases. InfluxDB, TimescaleDB (marketed as separate from Postgres), Prometheus with remote storage, QuestDB - the assumption is that regular relational databases cannot handle the ingestion rates and query patterns that time-series data requires.

That assumption is partially wrong. PostgreSQL with the right extensions handles serious time-series workloads that would surprise most developers who dismissed it for this use case.

Why Time-Series Is Hard for Standard Databases

Time-series workloads have specific properties that stress traditional database designs:

High-frequency inserts. Metrics, IoT sensors, and event streams can generate thousands to millions of inserts per second. Standard row-level locking and B-tree index updates cannot keep up.

Time-ordered queries. Almost every query filters on a time range. Standard PostgreSQL table scans or indexes on a single timestamp column become slow when the table contains billions of rows.

Data tiering and retention. Time-series data is typically most valuable recently and can be compressed or deleted as it ages. Managing this manually requires partitioning and complex maintenance jobs.

Aggregate queries across time windows. Calculating hourly averages, daily sums, or rolling 7-day metrics across billions of rows is computationally expensive without pre-computation.

PostgreSQL handles all of these if you configure it correctly.

TimescaleDB: The Main Extension

TimescaleDB is the most important extension for Postgres time-series. It is open-source (Apache 2.0 for the core), maintained by a company with Postgres expertise, and deeply integrated with Postgres rather than replacing it.

The core concept: hypertables. A hypertable is a Postgres table that TimescaleDB automatically partitions into chunks by time. Queries that filter on time only touch the relevant chunks, not the entire table.

-- Create a standard Postgres table
CREATE TABLE sensor_readings (
    time        TIMESTAMPTZ NOT NULL,
    sensor_id   INTEGER NOT NULL,
    value       DOUBLE PRECISION NOT NULL
);

-- Convert it to a hypertable, partitioned by time in 1-day chunks
SELECT create_hypertable('sensor_readings', 'time', chunk_time_interval => INTERVAL '1 day');

-- Insert works exactly the same
INSERT INTO sensor_readings VALUES (NOW(), 1, 23.5);

The hypertable looks like a normal table. Queries use standard SQL. TimescaleDB handles the partitioning transparently.

Native compression. TimescaleDB’s columnar compression achieves 90-95% compression ratios on typical time-series data. A table with 100 GB of data compresses to 5-10 GB with no query changes needed.

-- Enable compression on chunks older than 7 days
ALTER TABLE sensor_readings SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id'
);

SELECT add_compression_policy('sensor_readings', INTERVAL '7 days');

After compression, compressed chunks are still queryable with standard SQL. The decompression is handled by TimescaleDB automatically.

Continuous aggregates. Pre-computed materialized views that update automatically as new data arrives.

-- Pre-compute hourly averages
CREATE MATERIALIZED VIEW hourly_averages
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS bucket,
    sensor_id,
    AVG(value) AS avg_value,
    MAX(value) AS max_value
FROM sensor_readings
GROUP BY bucket, sensor_id;

-- Refresh automatically
SELECT add_continuous_aggregate_policy('hourly_averages',
    start_offset => INTERVAL '3 hours',
    end_offset   => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour'
);

Querying hourly averages is now instant regardless of the underlying data volume.

Performance Numbers

What TimescaleDB actually delivers:

The insert throughput improvement comes from chunk-level locks rather than table-level locks, allowing parallel inserts into different time partitions simultaneously.

pg_partman for Custom Partitioning

If you want time-based partitioning without TimescaleDB’s overhead, pg_partman provides automatic partition management for standard PostgreSQL declarative partitioning.

-- Create a partitioned table by month
CREATE TABLE events (
    created_at TIMESTAMPTZ NOT NULL,
    event_type TEXT,
    payload    JSONB
) PARTITION BY RANGE (created_at);

-- pg_partman creates and manages monthly partitions automatically
SELECT partman.create_parent(
    p_parent_table => 'public.events',
    p_control      => 'created_at',
    p_type         => 'native',
    p_interval     => 'monthly'
);

pg_partman runs a background worker that creates future partitions and optionally drops old ones according to a retention policy. It is simpler than TimescaleDB and appropriate when you need partitioning without time-series-specific features.

The Time Functions You Need

PostgreSQL has excellent built-in time functions that work with any extension:

-- Group by arbitrary time buckets (requires TimescaleDB for time_bucket,
-- or date_trunc for standard Postgres)
SELECT
    date_trunc('hour', created_at) AS hour,
    COUNT(*) AS event_count
FROM events
WHERE created_at > NOW() - INTERVAL '24 hours'
GROUP BY hour
ORDER BY hour;

-- Moving average with window functions
SELECT
    created_at,
    value,
    AVG(value) OVER (
        ORDER BY created_at
        ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
    ) AS moving_avg_10
FROM sensor_readings;

-- First/last value in time windows
SELECT DISTINCT ON (sensor_id)
    sensor_id,
    time,
    value
FROM sensor_readings
ORDER BY sensor_id, time DESC;

When Postgres Is Not Enough

Postgres with TimescaleDB handles ingestion rates up to roughly 1-5 million rows per second on capable hardware. Beyond that, or for very large deployments (tens of petabytes), specialized systems like ClickHouse (OLAP), Apache Druid (real-time analytics), or Prometheus + Thanos (metrics-specific) are better suited.

The decision points:

For most organizations, the “you need a specialized time-series database” advice is premature. TimescaleDB on a well-specced Postgres instance handles workloads that development teams assume require specialized infrastructure.

Bottom Line

TimescaleDB transforms PostgreSQL into a capable time-series database through hypertables, native columnar compression, continuous aggregates, and automatic data tiering. Ingestion throughput above 1 million rows per second, 90%+ compression ratios, and sub-second queries on billions of rows are achievable without leaving the Postgres ecosystem. If you are already running Postgres and facing a time-series workload, try TimescaleDB before adopting a specialized database - the operational simplicity of staying in your existing stack has real value.