TimescaleDB

TimescaleDB is a time-series database built as a PostgreSQL extension. It gives you the power of PostgreSQL with optimizations for time-series data.

Why TimescaleDB?

Installation (Docker)

We run TimescaleDB in Docker to keep it isolated from the system PostgreSQL (Postgres.app):

docker run -d --name timescaledb \
  -p 5433:5432 \
  -e POSTGRES_PASSWORD=telemetry123 \
  -v timescaledb_data:/var/lib/postgresql/data \
  --restart unless-stopped \
  timescale/timescaledb:latest-pg15

Key points:

• Port 5433 (not 5432) to avoid conflict with Postgres.app
• Data persisted in Docker volume timescaledb_data
• Auto-restarts on reboot

Connecting

psql -h localhost -p 5433 -U postgres -d telemetry
# Password: telemetry123

Schema

Create Database and Enable Extension

CREATE DATABASE telemetry;
\c telemetry
CREATE EXTENSION IF NOT EXISTS timescaledb;

Car Metrics Table

CREATE TABLE car_metrics (
  time           TIMESTAMPTZ NOT NULL,
  rpm            INTEGER,
  speed          INTEGER,
  coolant        INTEGER,
  intake_temp    INTEGER,
  throttle       INTEGER,
  engine_load    INTEGER,
  map_kpa        INTEGER,
  fuel_level     INTEGER,
  timing_adv     INTEGER,
  battery_voltage DECIMAL(4,2)
);

-- Convert to hypertable (this is the TimescaleDB magic)
SELECT create_hypertable('car_metrics', 'time');

What is a Hypertable?

A hypertable looks like a regular table but is automatically partitioned into chunks by time:

┌─────────────────────────────────────────┐
│            car_metrics (hypertable)     │
├─────────────────────────────────────────┤
│  ┌─────────┐ ┌─────────┐ ┌─────────┐   │
│  │ Chunk 1 │ │ Chunk 2 │ │ Chunk 3 │   │
│  │ Jan 1-7 │ │ Jan 8-14│ │Jan 15-21│   │
│  └─────────┘ └─────────┘ └─────────┘   │
└─────────────────────────────────────────┘

Benefits:

• Queries on recent data only scan recent chunks
• Old chunks can be compressed or dropped
• Parallelized queries across chunks

Useful Queries

Latest Values

SELECT * FROM car_metrics ORDER BY time DESC LIMIT 1;

Last Hour of Data

SELECT * FROM car_metrics
WHERE time > NOW() - INTERVAL '1 hour'
ORDER BY time;

Average RPM by Minute

SELECT
  time_bucket('1 minute', time) AS minute,
  AVG(rpm) as avg_rpm,
  MAX(rpm) as max_rpm
FROM car_metrics
WHERE time > NOW() - INTERVAL '1 hour'
GROUP BY minute
ORDER BY minute;

Driving Statistics for a Trip

SELECT
  COUNT(*) as data_points,
  MAX(speed) as max_speed,
  AVG(speed) as avg_speed,
  MAX(rpm) as max_rpm,
  AVG(rpm) as avg_rpm,
  AVG(coolant) as avg_coolant_temp,
  MIN(fuel_level) as ending_fuel,
  MAX(fuel_level) - MIN(fuel_level) as fuel_used
FROM car_metrics
WHERE time > '2024-01-07 10:00:00'
  AND time < '2024-01-07 11:00:00';

Detect Hard Acceleration (RPM spikes)

SELECT
  time,
  rpm,
  rpm - LAG(rpm) OVER (ORDER BY time) as rpm_change
FROM car_metrics
WHERE time > NOW() - INTERVAL '1 hour'
HAVING rpm_change > 1000;

Retention Policy

Keep detailed data for 30 days, then drop:

SELECT add_retention_policy('car_metrics', INTERVAL '30 days');

Compression (for older data)

-- Enable compression
ALTER TABLE car_metrics SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = ''
);

-- Compress data older than 7 days
SELECT add_compression_policy('car_metrics', INTERVAL '7 days');

Docker Management

# Check if running
docker ps | grep timescaledb

# View logs
docker logs timescaledb

# Stop
docker stop timescaledb

# Start
docker start timescaledb

# Remove (keeps data in volume)
docker rm timescaledb

# Remove data volume (DESTRUCTIVE)
docker volume rm timescaledb_data

Resources

• TimescaleDB Docs
• Hypertable Concepts
• Time Bucket Function