Time-Series Data in PostgreSQL — Partitioning, Retention, and Query Performance

-- Create partitioned time-series table
CREATE TABLE metrics (
  time TIMESTAMP NOT NULL,
  host_id BIGINT NOT NULL,
  cpu_percent NUMERIC,
  memory_mb BIGINT,
  disk_read_kb BIGINT,
  PRIMARY KEY (time, host_id)
) PARTITION BY RANGE (EXTRACT(EPOCH FROM time));

-- Partition key is EXTRACT(EPOCH FROM time) = Unix timestamp
-- Alternative: PARTITION BY RANGE (DATE_TRUNC('month', time))

-- Create partitions for past 3 months and next 1 month
CREATE TABLE metrics_202601 PARTITION OF metrics
  FOR VALUES FROM (
    EXTRACT(EPOCH FROM '2026-01-01'::TIMESTAMP)
  ) TO (
    EXTRACT(EPOCH FROM '2026-02-01'::TIMESTAMP)
  );

CREATE TABLE metrics_202602 PARTITION OF metrics
  FOR VALUES FROM (
    EXTRACT(EPOCH FROM '2026-02-01'::TIMESTAMP)
  ) TO (
    EXTRACT(EPOCH FROM '2026-03-01'::TIMESTAMP)
  );

CREATE TABLE metrics_202603 PARTITION OF metrics
  FOR VALUES FROM (
    EXTRACT(EPOCH FROM '2026-03-01'::TIMESTAMP)
  ) TO (
    EXTRACT(EPOCH FROM '2026-04-01'::TIMESTAMP)
  );

-- Create indexes on each partition
CREATE INDEX idx_metrics_202601_host ON metrics_202601(host_id);
CREATE INDEX idx_metrics_202602_host ON metrics_202602(host_id);
CREATE INDEX idx_metrics_202603_host ON metrics_202603(host_id);

-- Insert metrics (will automatically route to correct partition)
INSERT INTO metrics (time, host_id, cpu_percent, memory_mb)
VALUES (NOW(), 123, 45.2, 8192);

-- Verify partition routing
EXPLAIN PLAN SELECT * FROM metrics WHERE time >= NOW() - INTERVAL '1 day';
-- Output: "Seq Scan on metrics_202603" (only March partition scanned)

-- Query benefits from pruning (only 1 partition scanned)
EXPLAIN ANALYZE
SELECT AVG(cpu_percent)
FROM metrics
WHERE time >= '2026-03-01' AND time < '2026-03-31';
-- Plan: "Seq Scan on metrics_202603" (only March partition)
-- Rows: 1,000,000 in March partition

-- Query without pruning (all partitions scanned)
EXPLAIN ANALYZE
SELECT AVG(cpu_percent)
FROM metrics
WHERE EXTRACT(MONTH FROM time) = 3;
-- Plan: "Append" of all partitions (100M rows total)
-- Query time: 50-100 seconds (slow!)

-- Fix: use time range directly
SELECT AVG(cpu_percent)
FROM metrics
WHERE time >= '2026-03-01' AND time < '2026-04-01';

-- Constraint exclusion enables partition pruning
-- Verify it's enabled:
SHOW constraint_exclusion;
-- Output: partition (should be 'partition' for time-series)

-- Set if not enabled:
SET constraint_exclusion = partition;
ALTER SYSTEM SET constraint_exclusion = 'partition';
SELECT pg_reload_conf();

-- Pruning performance impact (real examples)
-- - Query 1 year of data: 12 partitions scanned, 500ms
-- - Query 1 day of data: 1 partition scanned, 50ms
-- - 10x improvement by narrowing time range

-- Install TimescaleDB extension
CREATE EXTENSION timescaledb CASCADE;

-- Create hypertable (auto-partitions by time)
CREATE TABLE metrics (
  time TIMESTAMP NOT NULL,
  host_id BIGINT NOT NULL,
  cpu_percent NUMERIC,
  memory_mb BIGINT
);

SELECT create_hypertable('metrics', 'time', if_not_exists => true);

-- Hypertables automatically create partitions (called "chunks" in TimescaleDB)
-- Default: 1 chunk per 7 days
-- Configurable via:
SELECT set_chunk_time_interval('metrics', INTERVAL '1 day');

-- Insert (automatic chunk routing)
INSERT INTO metrics (time, host_id, cpu_percent, memory_mb)
VALUES
  (NOW(), 1, 45.2, 8192),
  (NOW() - INTERVAL '1 day', 1, 42.1, 8000),
  (NOW() - INTERVAL '2 days', 1, 48.5, 8500);

-- Query (automatic pruning)
EXPLAIN ANALYZE
SELECT * FROM metrics
WHERE time > NOW() - INTERVAL '1 day';
-- Output: only recent chunks scanned

-- View chunks
SELECT chunk_name, range_start, range_end
FROM timescaledb_information.chunks
WHERE hypertable_name = 'metrics'
ORDER BY range_start DESC;

-- Compression (TimescaleDB exclusive)
-- Compress old chunks to save 90% storage
ALTER TABLE metrics SET (
  timescaledb.compress = true,
  timescaledb.compress_orderby = 'time DESC',
  timescaledb.compress_segmentby = 'host_id'
);

-- Compress chunks older than 1 week
SELECT compress_chunk(c) FROM timescaledb_information.chunks c
WHERE hypertable_name = 'metrics'
  AND range_end < NOW() - INTERVAL '1 week'
  AND NOT is_compressed;

-- Storage comparison:
-- Uncompressed: 10GB raw metrics
-- Compressed: 1GB (10x reduction)

-- PostgreSQL native: create partitions manually or with trigger
CREATE OR REPLACE FUNCTION create_monthly_partition()
RETURNS void AS $$
DECLARE
  next_month TIMESTAMP;
  partition_name TEXT;
BEGIN
  next_month := DATE_TRUNC('month', NOW() + INTERVAL '1 month');
  partition_name := 'metrics_' || TO_CHAR(next_month, 'YYYYMM');

  -- Check if partition exists
  IF NOT EXISTS (
    SELECT FROM pg_tables
    WHERE tablename = partition_name
  ) THEN
    EXECUTE format(
      'CREATE TABLE %I PARTITION OF metrics FOR VALUES FROM (%L) TO (%L)',
      partition_name,
      EXTRACT(EPOCH FROM next_month),
      EXTRACT(EPOCH FROM next_month + INTERVAL '1 month')
    );

    EXECUTE format(
      'CREATE INDEX %I ON %I(host_id)',
      'idx_' || partition_name || '_host',
      partition_name
    );

    RAISE NOTICE 'Created partition %', partition_name;
  END IF;
END;
$$ LANGUAGE plpgsql;

-- Run monthly (e.g., via cron or pg_cron)
-- SELECT create_monthly_partition();

-- Or use pg_cron extension (runs inside PostgreSQL)
-- CREATE EXTENSION pg_cron;
-- SELECT cron.schedule('create_monthly_partition', '0 0 1 * *', 'SELECT create_monthly_partition()');

-- TimescaleDB: automatic (no action needed)
-- Chunks created automatically as new time periods arrive
-- Example: 7-day chunks created automatically
SELECT show_chunks('metrics');
-- Returns: new chunks automatically as needed

-- PostgreSQL: drop old partition
DROP TABLE metrics_202601;  -- Delete January 2026 data

-- More carefully: detach partition first, backup, then drop
ALTER TABLE metrics DETACH PARTITION metrics_202601;
-- metrics_202601 now independent table (can backup if needed)

-- Option 1: Drop partition
DROP TABLE metrics_202601;

-- Option 2: Keep partition for backup, don't query
-- metrics_202601 becomes archived (query slow, but data preserved)

-- Option 3: Archive to S3 (backup before drop)
COPY (SELECT * FROM metrics_202601)
TO PROGRAM 'aws s3 cp /dev/stdin s3://bucket/metrics-202601.csv';

DROP TABLE metrics_202601;

-- TimescaleDB: automated retention policy
SELECT add_retention_policy('metrics', INTERVAL '90 days');

-- Drops chunks older than 90 days automatically
-- Equivalent to: DROP TABLE metrics_202601 every month

-- Verify retention policy
SELECT * FROM timescaledb_information.policies
WHERE hypertable_name = 'metrics';

-- Manual drop if needed
SELECT drop_chunks('metrics', OLDER_THAN => NOW() - INTERVAL '90 days');

-- Raw metrics table (1-second resolution)
CREATE TABLE metrics (
  time TIMESTAMP NOT NULL,
  host_id BIGINT NOT NULL,
  cpu_percent NUMERIC
);

SELECT create_hypertable('metrics', 'time');

-- Continuous aggregate: hourly averages
CREATE MATERIALIZED VIEW metrics_hourly AS
SELECT
  TIME_BUCKET(INTERVAL '1 hour', time) AS hour,
  host_id,
  AVG(cpu_percent) AS avg_cpu,
  MAX(cpu_percent) AS max_cpu,
  MIN(cpu_percent) AS min_cpu,
  COUNT(*) AS sample_count
FROM metrics
GROUP BY TIME_BUCKET(INTERVAL '1 hour', time), host_id
WITH DATA;

-- Continuous aggregates auto-refresh
ALTER MATERIALIZED VIEW metrics_hourly SET (
  timescaledb.continuous = true,
  timescaledb.materialized_only = false
);

-- Query hourly aggregates (super fast)
SELECT hour, host_id, avg_cpu, max_cpu
FROM metrics_hourly
WHERE hour > NOW() - INTERVAL '30 days'
ORDER BY hour DESC
LIMIT 30;
-- Returns instantly (precomputed)

-- Downsampling benefit
-- Raw query: SELECT * FROM metrics WHERE time > NOW() - INTERVAL '30 days'
--   - Scan: 1B rows (30 days * 1 sample/sec)
--   - Time: 200 seconds
-- Hourly aggregate: SELECT * FROM metrics_hourly WHERE hour > NOW() - INTERVAL '30 days'
--   - Scan: 720 rows (30 days * 24 hours)
--   - Time: 1 millisecond
-- 200,000x improvement!

-- Create additional aggregates (daily, weekly)
CREATE MATERIALIZED VIEW metrics_daily AS
SELECT
  TIME_BUCKET(INTERVAL '1 day', time) AS day,
  host_id,
  AVG(cpu_percent) AS avg_cpu,
  MAX(cpu_percent) AS max_cpu,
  MIN(cpu_percent) AS min_cpu
FROM metrics
GROUP BY TIME_BUCKET(INTERVAL '1 day', time), host_id
WITH DATA;

ALTER MATERIALIZED VIEW metrics_daily SET (
  timescaledb.continuous = true,
  timescaledb.materialized_only = false
);

-- Retention on aggregates (keep hourly for 1 month, daily for 1 year)
SELECT add_retention_policy('metrics_hourly', INTERVAL '30 days');
SELECT add_retention_policy('metrics_daily', INTERVAL '365 days');

-- BRIN index on time (ultra-compact)
CREATE INDEX idx_metrics_time_brin ON metrics USING BRIN (time);

-- Index size comparison:
-- B-Tree index: 1GB (for 10TB table)
-- BRIN index: 10MB (100x smaller!)

-- BRIN on time + host_id (segmented)
CREATE INDEX idx_metrics_time_host_brin
ON metrics USING BRIN (time, host_id)
WITH (pages_per_range = 128);

-- Query: recent metrics for single host (BRIN very efficient)
EXPLAIN ANALYZE
SELECT * FROM metrics
WHERE host_id = 42
  AND time > NOW() - INTERVAL '1 day';
-- Plan: "Bitmap Index Scan using idx_metrics_time_host_brin"
-- Much faster than full table scan

-- BRIN requirements for efficiency:
-- 1. Data physically ordered by index column (automatic with INSERT ORDER)
-- 2. Range queries (WHERE time > X AND time < Y)
-- 3. Not selective equality (WHERE time = '2026-03-15 14:30:00' will scan more)

-- Monitor BRIN effectiveness
SELECT
  schemaname, indexname,
  idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexname LIKE '%brin%'
ORDER BY idx_scan DESC;

-- Pattern 1: Recent metrics for one host
SELECT time, cpu_percent, memory_mb
FROM metrics
WHERE host_id = 42
  AND time > NOW() - INTERVAL '7 days'
ORDER BY time DESC
LIMIT 100;
-- Index: (host_id, time DESC) or BRIN on time
-- Expected: partition pruning + index scan, <100ms

-- Pattern 2: Latest value per host
SELECT DISTINCT ON (host_id) host_id, time, cpu_percent
FROM metrics
WHERE time > NOW() - INTERVAL '1 day'
ORDER BY host_id, time DESC;
-- Index: (host_id, time DESC)
-- Or use: SELECT * FROM LATERAL (SELECT * FROM metrics WHERE host_id = h.id ORDER BY time DESC LIMIT 1)

-- Pattern 3: Hourly aggregates across all hosts
SELECT
  TIME_BUCKET(INTERVAL '1 hour', time) AS hour,
  COUNT(*) AS sample_count,
  AVG(cpu_percent) AS avg_cpu
FROM metrics
WHERE time > NOW() - INTERVAL '30 days'
GROUP BY TIME_BUCKET(INTERVAL '1 hour', time)
ORDER BY hour DESC;
-- Use continuous aggregates (metrics_hourly) for speed

-- Pattern 4: Comparison (this week vs last week)
SELECT
  EXTRACT(DAY FROM time) AS day,
  AVG(cpu_percent) AS avg_cpu
FROM metrics
WHERE time >= NOW() - INTERVAL '14 days'
  AND host_id = 42
GROUP BY EXTRACT(DAY FROM time)
ORDER BY day DESC;
-- Partition pruning scans 2 partitions only
-- Expected: <1 second

-- Pattern 5: Percentile queries (P95 CPU)
SELECT
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY cpu_percent) AS p95_cpu
FROM metrics
WHERE time > NOW() - INTERVAL '24 hours'
  AND host_id = 42;
-- Index: single host, time range
-- Expected: <500ms