Comment maîtriser TimescaleDB en 2026

version: '3.8'
services:
  timescale:
    image: timescale/timescaledb:latest-pg16
    container_name: timescaledb
    restart: always
    environment:
      POSTGRES_PASSWORD: mysecretpassword
      POSTGRES_USER: postgres
      POSTGRES_DB: timescaledb_tutorial
    ports:
      - "5432:5432"
    volumes:
      - timescale_data:/var/lib/postgresql/data
volumes:
  timescale_data:
    driver: local

CREATE TABLE metrics (
  time TIMESTAMPTZ NOT NULL,
  device_id TEXT NOT NULL,
  temperature DOUBLE PRECISION,
  humidity DOUBLE PRECISION,
  location POINT
);

SELECT create_hypertable('metrics', 'time', chunk_time_interval => INTERVAL '1 day', partitioning_column => 'device_id', number_partitions => 4);

-- Insertion de données de test
INSERT INTO metrics (time, device_id, temperature, humidity, location)
SELECT
  NOW() - INTERVAL '1 day' * generate_series(0, 99),
  'device_' || (random() * 10)::INT,
  20 + random() * 10,
  40 + random() * 20,
  POINT(random() * 100, random() * 100)
FROM generate_series(1, 10000);

-- Moyenne horaire par device
SELECT
  time_bucket('1 hour', time) AS bucket,
  device_id,
  AVG(temperature) AS avg_temp,
  LAST(humidity, time) AS last_humidity,
  COUNT(*) AS points
FROM metrics
WHERE time > NOW() - INTERVAL '2 days'
GROUP BY 1, 2
ORDER BY 1 DESC, 2;

-- Top 5 devices les plus chauds
SELECT
  device_id,
  MAX(temperature) AS max_temp,
  ST_X(location) AS lon,
  ST_Y(location) AS lat
FROM metrics
WHERE time > NOW() - INTERVAL '1 day'
GROUP BY device_id, location
ORDER BY max_temp DESC
LIMIT 5;

-- Vue pour hourly aggregates
CREATE MATERIALIZED VIEW hourly_metrics
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', time) AS bucket,
  device_id,
  AVG(temperature) AS avg_temp,
  AVG(humidity) AS avg_hum,
  MIN(temperature) AS min_temp,
  MAX(temperature) AS max_temp
FROM metrics
GROUP BY 1, 2
WITH DATA;

-- Politiques de rafraîchissement
SELECT add_continuous_aggregate_policy('hourly_metrics',
  start_offset => INTERVAL '2 days',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour');

-- Query sur la vue (toujours fraîche)
SELECT * FROM hourly_metrics WHERE bucket > NOW() - INTERVAL '1 day';

-- Activer compression sur hypertable
ALTER TABLE metrics SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'device_id',
  timescaledb.compress_orderby = 'time DESC'
);

-- Compressor chunk le plus ancien
SELECT compress_chunk(i.chunk_schema || '.' || i.chunk_name)
FROM timescaledb_information.chunks i
WHERE i.hypertable_name = 'metrics'
ORDER BY i.range_start DESC
LIMIT 1;

-- Decompresser pour writes (si needed)
SELECT decompress_chunk(c)
FROM show_chunks('metrics', older_than => INTERVAL '3 days') c;

-- Stats compression
SELECT
  hypertable_name,
  sg.num_rows_pre_compression,
  sg.num_rows_post_compression,
  pg_size_pretty(sg.size_pre_compression) AS size_pre,
  pg_size_pretty(sg.size_post_compression) AS size_post
FROM timescaledb_information.compressed_chunk_stats sg
JOIN timescaledb_information.hypertables h ON h.hypertable_name = sg.hypertable_name
WHERE h.hypertable_name = 'metrics';

-- Drop automatique chunks > 30 jours
SELECT add_retention_policy('metrics',
  INTERVAL '30 days');

-- Custom : drop compressed only
SELECT add_retention_policy('metrics',
  INTERVAL '90 days',
  if_exists => 'ignore');

-- Lister jobs
SELECT job_id, proc_name, last_run_status
FROM timescaledb_information.jobs
WHERE proc_name LIKE '%retention%';

-- Pause job
SELECT alter_job(job_id, scheduled => false)
FROM timescaledb_information.jobs
WHERE hypertable_name = 'metrics';

-- Downsampling pour graphs (100 points max)
SELECT
  time_bucket('5 minutes', time) AS bucket,
  device_id,
  lttb(temperature, 20, 'time') OVER (PARTITION BY device_id ORDER BY time)
FROM metrics
WHERE time > NOW() - INTERVAL '1 hour'
GROUP BY 1, 2
ORDER BY 1, 2;

-- Gap filling
SELECT
  time_bucket('1 hour', generate_series(NOW() - INTERVAL '24 hours', NOW(), '1 hour')) AS bucket,
  device_id,
  COALESCE(AVG(temperature), 0) AS interp_temp,
  interpolate(gaps(temperature)) AS filled_temp
FROM metrics
GROUP BY 1, 2
ORDER BY 1, 2;