Time-Series Database Design: Handling High-Volume Temporal Data

What Makes Time-Series Data Different

Time-series data — metrics, events, sensor readings, financial ticks, user activity logs — shares characteristics that make standard relational table design insufficient:

1. Append-heavy: Rows are almost always inserted, rarely updated or deleted
2. Time-ordered: Queries almost always filter on a time range
3. High volume: Systems can ingest millions of rows per hour
4. Retention-bounded: Old data is eventually deleted or archived
5. Aggregation-centric: Most queries compute averages, sums, or counts over time windows

Designing for these characteristics from the start prevents the performance cliff that hits when a naive time-series table reaches tens of millions of rows.

Table Partitioning by Time

PostgreSQL's declarative table partitioning is the most impactful optimization for time-series data. Each partition holds data for a specific time range, allowing the query planner to skip entire partitions (partition pruning) when the WHERE clause filters on the partition key.

-- Parent partitioned table
CREATE TABLE metrics (
  id          BIGSERIAL,
  sensor_id   UUID NOT NULL,
  metric_name VARCHAR(100) NOT NULL,
  value       DOUBLE PRECISION NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL,
  tags        JSONB
) PARTITION BY RANGE (recorded_at);

-- Create monthly partitions
CREATE TABLE metrics_2026_03
  PARTITION OF metrics
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

CREATE TABLE metrics_2026_04
  PARTITION OF metrics
  FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

-- Index on each partition (automatically inherited)
CREATE INDEX ON metrics_2026_03 (sensor_id, recorded_at DESC);
CREATE INDEX ON metrics_2026_04 (sensor_id, recorded_at DESC);

-- This query prunes all partitions except 2026_04
EXPLAIN SELECT * FROM metrics
WHERE recorded_at >= '2026-04-01'
  AND recorded_at <  '2026-05-01'
  AND sensor_id = $1;
-- → "Seq Scan on metrics_2026_04" — other partitions not touched

Automate partition creation with a scheduled job or the pg_partman extension, which manages partition creation and retention automatically.

Efficient Time-Series Indexing

Standard B-tree indexes work well for time-series data when queries filter on time ranges. The key is to always include the time column in index scans.

-- Composite index: sensor first, then time
-- Optimal for: WHERE sensor_id = $1 AND recorded_at > $2
CREATE INDEX idx_metrics_sensor_time
  ON metrics (sensor_id, recorded_at DESC);

-- BRIN index: very small footprint for naturally ordered time columns
-- Works because rows are inserted in time order (heap correlation ≈ 1)
CREATE INDEX idx_metrics_brin
  ON metrics USING BRIN (recorded_at)
  WITH (pages_per_range = 128);
-- 100x smaller than a B-tree index; good for full-range scans

-- GIN index for tag-based filtering
CREATE INDEX idx_metrics_tags ON metrics USING GIN(tags);
-- Query: WHERE tags @> '{"region": "us-east-1"}'

Continuous Aggregates and Rollups

Querying raw time-series data for long time ranges is expensive. Pre-aggregate data into rollup tables at multiple resolutions.

-- Raw data: one row per sensor reading (every second)
-- Rollup: one row per sensor per minute
CREATE TABLE metrics_1min (
  sensor_id   UUID NOT NULL,
  metric_name VARCHAR(100) NOT NULL,
  bucket      TIMESTAMPTZ NOT NULL,  -- Truncated to minute
  min_value   DOUBLE PRECISION,
  max_value   DOUBLE PRECISION,
  avg_value   DOUBLE PRECISION,
  sum_value   DOUBLE PRECISION,
  count_value BIGINT,
  PRIMARY KEY (sensor_id, metric_name, bucket)
);

-- Populate rollup for a time range
INSERT INTO metrics_1min
SELECT
  sensor_id,
  metric_name,
  DATE_TRUNC('minute', recorded_at) AS bucket,
  MIN(value),
  MAX(value),
  AVG(value),
  SUM(value),
  COUNT(*)
FROM metrics
WHERE recorded_at >= $start AND recorded_at < $end
GROUP BY sensor_id, metric_name, DATE_TRUNC('minute', recorded_at)
ON CONFLICT (sensor_id, metric_name, bucket) DO UPDATE SET
  min_value   = LEAST(EXCLUDED.min_value, metrics_1min.min_value),
  max_value   = GREATEST(EXCLUDED.max_value, metrics_1min.max_value),
  avg_value   = (EXCLUDED.sum_value + metrics_1min.sum_value) /
                (EXCLUDED.count_value + metrics_1min.count_value),
  sum_value   = EXCLUDED.sum_value + metrics_1min.sum_value,
  count_value = EXCLUDED.count_value + metrics_1min.count_value;

-- Create hourly rollup from minute rollup (hierarchical aggregation)
CREATE TABLE metrics_1hour (
  sensor_id   UUID NOT NULL,
  metric_name VARCHAR(100) NOT NULL,
  bucket      TIMESTAMPTZ NOT NULL,
  min_value   DOUBLE PRECISION,
  max_value   DOUBLE PRECISION,
  avg_value   DOUBLE PRECISION,
  count_value BIGINT,
  PRIMARY KEY (sensor_id, metric_name, bucket)
);

Data Retention and Archival

-- Drop old partitions instantly (no per-row DELETE needed)
-- Dropping a partition is a metadata operation — milliseconds, not hours
DROP TABLE IF EXISTS metrics_2024_01;

-- Or detach and archive to cold storage before dropping
ALTER TABLE metrics DETACH PARTITION metrics_2024_01;
-- Export to S3 or object storage, then drop
COPY metrics_2024_01 TO '/tmp/metrics_2024_01.csv' CSV;
DROP TABLE metrics_2024_01;

-- pg_partman: automated retention policy
-- SELECT partman.run_maintenance_proc();  -- Runs on schedule

This is one of the most compelling reasons to use range partitioning: removing old data is instant regardless of how many rows are in the partition.

Time-Series Extensions Worth Considering

For very high ingest rates (>1 million rows per second), consider TimescaleDB, an open-source PostgreSQL extension that adds automatic hypertable partitioning, continuous aggregates, and native compression:

-- TimescaleDB: Convert table to hypertable (automatic partitioning)
SELECT create_hypertable('metrics', 'recorded_at', chunk_time_interval => INTERVAL '1 day');

-- Native columnar compression (typically 90-95% size reduction)
ALTER TABLE metrics SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'sensor_id',
  timescaledb.compress_orderby = 'recorded_at DESC'
);

-- Compress chunks older than 7 days automatically
SELECT add_compression_policy('metrics', INTERVAL '7 days');

Model Your Time-Series Schema with Erdmates

Time-series schemas involve partitioned tables, rollup tables, and retention policies that benefit from visual documentation. Erdmates lets you diagram your entire time-series architecture — from raw event tables to multi-resolution rollups — and share the design with your team before implementation. A clear ERD ensures everyone understands the data flow and prevents the schema drift that leads to conflicting rollup definitions.

Conclusion

Effective time-series design combines range partitioning for instant pruning and retention, composite and BRIN indexes for efficient range queries, pre-aggregated rollup tables at multiple resolutions to keep dashboards fast, and automated partition management to remove old data without locking. Apply these patterns from the start and your database will handle millions of rows per day without performance degradation.