timescaledb/notes.md

timescaledb

https://github.com/timescale/timescaledb

Test

Récup datas

ssh grab-4
docker exec client-demo-postgres-1 pg_dump -Fc -U postgres postgres > /tmp/backup_postgres.dump
exit

cd /tmp
scp grab-4:/tmp/backup_postgres.dump .
pg_restore -c -x -t mesure -d postgres -h localhost -U postgres -W  backup_postgres.dump
# pg_restore -c -x -I mesure_idx1 -d postgres -h localhost -U postgres -W  backup_postgres.dump
# pg_restore -c -x -I mesure_idx2 -d postgres -h localhost -U postgres -W  backup_postgres.dump
pg_restore -c -x -t captation -d postgres -h localhost -U postgres -W  backup_postgres.dump

Exploit

SELECT *
FROM timescaledb_information.chunks
WHERE hypertable_name = 'mesure_ng'

SELECT * FROM timescaledb_information.jobs

WIP

Grafana

mixe de

sur le range :

SELECT *
FROM (

SELECT
  "date" AS time,
  valeur
FROM mesure_gauge
WHERE $__timeFilter("date")
AND $__range_s < 86400

UNION ALL

SELECT
  "date" AS time,
  average(valeur_temporelle)
FROM mesure_gauge_heure
WHERE $__timeFilter("date")
AND $__range_s BETWEEN 86400 AND 2592000

UNION ALL

SELECT
  "date" AS time,
  average(valeur_temporelle)
FROM mesure_gauge_jour
WHERE $__timeFilter("date")
AND $__range_s > 2592000

) t
ORDER BY time

et où est dans le passé

SELECT *
FROM (

  -- données récentes → brut
  SELECT
    mg."date" AS time,
    mg.valeur
  FROM mesure_gauge mg
  WHERE $__timeFilter(mg."date")
  AND $__to > now() - interval '7 days'

  UNION ALL

  -- données anciennes → agrégées
  SELECT
    mgh."date" AS time,
    average(mgh.valeur_temporelle) AS valeur
  FROM mesure_gauge_heure mgh
  WHERE $__timeFilter(mgh."date")
  AND $__to <= now() - interval '7 days'

) t
ORDER BY time;