TimescaleDB PostgreSQL Time-Series: Setup Hypertable Compression 1000x Faster

TimescaleDB คือ PostgreSQL Extension สำหรับ Time-Series Data: เร็ว 1000x บีบอัด 90% TimescaleDB เปลี่ยน PostgreSQL ให้เป็น time-series database ชั้นนำ ใช้ hypertables แบ่ง chunk อัตโนมัติ ACID compliant รองรับ IoT monitoring DevOps metrics

TimescaleDB vs PostgreSQL Performance

Core Features

✅ Hypertables: Auto-partition by time
✅ Continuous Aggregates: Materialized views
✅ Compression: 90-95% storage reduction
✅ Retention Policies: Auto-drop old data
✅ SQL 100%: Full PostgreSQL features

Quick Setup & Hypertable

-- 1. Enable extension
CREATE EXTENSION timescaledb;
-- 2. Create hypertable (1 command!)
CREATE TABLE metrics (
  time TIMESTAMPTZ NOT NULL,
  device_id TEXT,
  value DOUBLE PRECISION
);
SELECT create_hypertable('metrics', 'time');
-- 3. Insert 1M rows/second
INSERT INTO metrics (time, device_id, value) 
VALUES (NOW(), 'sensor1', 23.5);
-- 4. Fast queries
SELECT time_bucket('1 hour', time) AS hour,
       AVG(value) FROM metrics 
GROUP BY hour ORDER BY hour DESC;

Production Use Cases

📊 DevOps: Prometheus remote storage
🔌 IoT: Sensor data (1M+ devices)
💰 Fintech: Tick data/trades
🎮 Gaming: Player metrics
🌡️ Observability: Cloudflare/Netflix

Continuous Aggregates (Magic)

-- Auto-aggregate every 5min
CREATE MATERIALIZED VIEW hourly_avg
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS hour,
       AVG(value) AS avg_temp
FROM metrics GROUP BY hour;
-- Auto-refreshed, query like normal table
SELECT * FROM hourly_avg WHERE hour > NOW() - INTERVAL '1 day';

Compression Setup

-- Compress chunks > 7 days old
ALTER TABLE metrics SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'device_id'
);
-- 90% storage reduction, 15x query speedup
SELECT * FROM metrics_compressed;

Docker Production Setup

# docker-compose.yml
services:
  postgres:
    image: timescale/timescaledb:latest-pg16
    environment:
      POSTGRES_DB: metrics
      POSTGRES_PASSWORD: password
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
volumes:
  pgdata:

Schema Best Practices

✅ time TIMESTAMPTZ first column
✅ Partition: time_bucket('1h', time)
✅ Index: CREATE INDEX ON metrics (device_id, time DESC)
✅ Compress: >7 days old
✅ Retain: 90 days policy

Cloud Options 2026