TimescaleDB: Time Series Data in PostgreSQL

Why TimescaleDB?

TimescaleDB is PostgreSQL with time-series superpowers. You get familiar SQL, existing tooling, and 100x query performance on time-series data.

Setup

-- Enable extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Create regular table
CREATE TABLE metrics (
  time        TIMESTAMPTZ NOT NULL,
  device_id   TEXT NOT NULL,
  metric_name TEXT NOT NULL,
  value       DOUBLE PRECISION
);

-- Convert to hypertable (auto-partitioned by time)
SELECT create_hypertable('metrics', 'time',
  chunk_time_interval => INTERVAL '1 day',
  if_not_exists => TRUE
);

-- Add composite index for common queries
CREATE INDEX idx_metrics_device_time ON metrics (device_id, time DESC);

Insert and Query

-- Insert (same as regular PostgreSQL)
INSERT INTO metrics (time, device_id, metric_name, value)
VALUES
  (NOW(), 'sensor-001', 'temperature', 23.5),
  (NOW(), 'sensor-001', 'humidity', 65.2),
  (NOW(), 'sensor-002', 'temperature', 21.1);

-- Time-bucket aggregation
SELECT
  time_bucket('1 hour', time) AS hour,
  device_id,
  AVG(value) AS avg_temp,
  MAX(value) AS max_temp,
  MIN(value) AS min_temp
FROM metrics
WHERE metric_name = 'temperature'
  AND time > NOW() - INTERVAL '24 hours'
GROUP BY hour, device_id
ORDER BY hour DESC, device_id;

-- Gap filling (insert null for missing intervals)
SELECT
  time_bucket_gapfill('1 hour', time) AS hour,
  device_id,
  LOCF(AVG(value)) AS temperature  -- Last observation carried forward
FROM metrics
WHERE metric_name = 'temperature'
  AND time > NOW() - INTERVAL '7 days'
  AND device_id = 'sensor-001'
GROUP BY hour, device_id
ORDER BY hour;

Continuous Aggregates

-- Pre-compute hourly averages (auto-refreshed)
CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', time) AS hour,
  device_id,
  metric_name,
  AVG(value) AS avg_value,
  MAX(value) AS max_value,
  MIN(value) AS min_value,
  COUNT(*) AS sample_count
FROM metrics
GROUP BY hour, device_id, metric_name
WITH NO DATA;

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

-- Query the view (super fast!)
SELECT hour, avg_value, max_value
FROM metrics_hourly
WHERE device_id = 'sensor-001'
  AND metric_name = 'temperature'
  AND hour > NOW() - INTERVAL '7 days'
ORDER BY hour DESC;

Compression Policy

-- Compress chunks older than 7 days (typically 90%+ compression)
SELECT add_compression_policy('metrics',
  compress_after => INTERVAL '7 days'
);

-- Monitor compression
SELECT
  chunk_name,
  before_compression_total_bytes,
  after_compression_total_bytes,
  round(after_compression_total_bytes::numeric / before_compression_total_bytes * 100, 1) AS pct
FROM chunk_compression_stats('metrics')
ORDER BY chunk_name;

Data Retention

-- Auto-drop data older than 90 days
SELECT add_retention_policy('metrics', drop_after => INTERVAL '90 days');

Node.js with TimescaleDB

import { Pool } from 'pg'

const pool = new Pool({ connectionString: process.env.DATABASE_URL })

async function insertMetrics(readings: MetricReading[]) {
  const values = readings.map(r => `('${r.time}', '${r.deviceId}', '${r.metric}', ${r.value})`).join(',')
  await pool.query(`INSERT INTO metrics (time, device_id, metric_name, value) VALUES ${values}`)
}

async function getDeviceMetrics(deviceId: string, hours: number) {
  const result = await pool.query(`
    SELECT
      time_bucket('5 minutes', time) AS bucket,
      AVG(value) AS avg_value,
      MAX(value) AS max_value
    FROM metrics
    WHERE device_id = $1
      AND time > NOW() - $2 * INTERVAL '1 hour'
      AND metric_name = 'temperature'
    GROUP BY bucket
    ORDER BY bucket DESC
  `, [deviceId, hours])
  return result.rows
}

TimescaleDB vs InfluxDB