TimescaleDB

Tidsserie

En PostgreSQL extension der forvandler PostgreSQL til en kraftfuld time-series database med fuld SQL support.

Beskrivelse

TimescaleDB er en innovativ approach til time-series databases: i stedet for at bygge en helt ny database fra bunden, er det en extension til PostgreSQL. Dette betyder du får alle PostgreSQL's features (ACID, JOINs, transactions, indexes, foreign keys) plus specialiserede time-series optimizations. TimescaleDB introducerer konceptet hypertables - tabeller der automatisk partitioneres i chunks baseret på tid. Hver chunk er en PostgreSQL tabel, hvilket gør administration og backup lettere. Data ældre end en vis alder kan flyttes til billigere storage eller komprimeres aggressivt. TimescaleDB tilbyder continuous aggregates (materialized views der automatisk opdateres), data retention policies, compression (op til 90% reduction), og hyperfunctions til advanced time-series analysis. Fordi det er PostgreSQL under the hood, kan du bruge alle eksisterende PostgreSQL tools, backup solutions, og connectors. Dette gør TimescaleDB særligt attraktiv for teams der allerede kender PostgreSQL og har brug for time-series capabilities uden at lære en helt ny database.

Features

•Full SQL support (det er PostgreSQL)
•Automatic time-based partitioning (chunks)
•Continuous aggregates (auto-updating views)
•Native compression (columnar)
•Data retention policies
•Hyperfunctions for analytics
•Multi-node clustering (TimescaleDB 2.0+)
•Alle PostgreSQL features (JOINs, indexes, etc)

Query Eksempel

-- TimescaleDB bruger standard PostgreSQL SQL

-- 1. Enable TimescaleDB extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- 2. Opret normal PostgreSQL tabel
CREATE TABLE sensor_data (
  time TIMESTAMPTZ NOT NULL,
  sensor_id INTEGER NOT NULL,
  temperature DOUBLE PRECISION,
  humidity DOUBLE PRECISION,
  location TEXT
);

-- 3. Konverter til hypertable (TimescaleDB magic)
SELECT create_hypertable('sensor_data', 'time');

-- 4. Indsæt data (standard SQL)
INSERT INTO sensor_data VALUES
  (NOW(), 1, 23.5, 65.2, 'warehouse-a'),
  (NOW(), 2, 21.1, 70.5, 'warehouse-b');

-- 5. Query med standard SQL
SELECT 
  time_bucket('5 minutes', time) AS bucket,
  sensor_id,
  AVG(temperature) AS avg_temp,
  MAX(temperature) AS max_temp
FROM sensor_data
WHERE time > NOW() - INTERVAL '1 day'
GROUP BY bucket, sensor_id
ORDER BY bucket DESC;

-- 6. Continuous aggregates (auto-updating materialized view)
CREATE MATERIALIZED VIEW sensor_data_hourly
WITH (timescaledb.continuous) AS
SELECT 
  time_bucket('1 hour', time) AS hour,
  sensor_id,
  AVG(temperature) AS avg_temp,
  MIN(temperature) AS min_temp,
  MAX(temperature) AS max_temp
FROM sensor_data
GROUP BY hour, sensor_id;

-- 7. Query continuous aggregate (hurtig!)
SELECT * FROM sensor_data_hourly
WHERE hour > NOW() - INTERVAL '7 days'
ORDER BY hour DESC;

-- 8. Retention policy (auto-delete old data)
SELECT add_retention_policy('sensor_data', INTERVAL '90 days');

-- 9. Compression policy (save space)
ALTER TABLE sensor_data SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'sensor_id'
);

SELECT add_compression_policy('sensor_data', INTERVAL '7 days');

-- 10. JOIN med relational data
CREATE TABLE sensors (
  sensor_id INTEGER PRIMARY KEY,
  name TEXT,
  type TEXT,
  building TEXT
);

INSERT INTO sensors VALUES
  (1, 'Sensor-A', 'temperature', 'Building-1'),
  (2, 'Sensor-B', 'temperature', 'Building-2');

-- Kombiner time-series og relational!
SELECT 
  s.name,
  s.building,
  time_bucket('1 hour', sd.time) AS hour,
  AVG(sd.temperature) AS avg_temp
FROM sensor_data sd
JOIN sensors s ON sd.sensor_id = s.sensor_id
WHERE sd.time > NOW() - INTERVAL '24 hours'
GROUP BY s.name, s.building, hour
ORDER BY hour DESC;

-- 11. Advanced time-series functions
SELECT 
  time,
  temperature,
  LAG(temperature) OVER (PARTITION BY sensor_id ORDER BY time) AS prev_temp,
  temperature - LAG(temperature) OVER (PARTITION BY sensor_id ORDER BY time) AS delta
FROM sensor_data
WHERE time > NOW() - INTERVAL '1 hour';

-- 12. Hyperfunctions (TimescaleDB 2.0+)
SELECT 
  sensor_id,
  toolkit_experimental.percentile_agg(temperature) AS temp_pct
FROM sensor_data
WHERE time > NOW() - INTERVAL '1 day'
GROUP BY sensor_id;

Anvendelsesområder

•IoT sensor data med relational context
•Financial market data analysis
•Application monitoring med metadata joins
•Industrial equipment monitoring
•Environmental data tracking

Fordele

✓Fuld PostgreSQL kompatibilitet
✓Kan kombinere time-series og relational data
✓Brug PostgreSQL ecosystem og tools
✓ACID transactions
✓Lettere at lære hvis du kender SQL

Ulemper

✗Kræver PostgreSQL ekspertise for optimization
✗Overhead fra PostgreSQL features du måske ikke bruger
✗Clustering er komplekst
✗Ikke lige så hurtig som purpose-built time-series DB'er
✗Mere memory intensiv end specialized solutions

Bedst til

→Teams der allerede bruger PostgreSQL
→Time-series data der skal joines med relational data
→Complex queries med SQL
→Financial applications med ACID requirements
→Når du har brug for PostgreSQL features + time-series

Ikke anbefalet til

⚠Ultra-high frequency data (millioner writes/sec)
⚠Når du kun har brug for basic time-series (brug InfluxDB)
⚠Embedded systems med begrænsede ressourcer
⚠Teams uden PostgreSQL ekspertise
⚠Simple metrics collection (overkill)

Relaterede databaser

PostgreSQLInfluxDBPrometheusQuestDBClickHouse