時系列インデックスの選択

SELECT * FROM metrics
WHERE device_id = 42
  AND ts >= NOW() - INTERVAL '24 hours'
ORDER BY ts DESC LIMIT 1000;

CREATE INDEX metrics_device_ts_idx
  ON metrics (device_id, ts DESC);

-- Equality on device_id, range + sort on ts: index handles both.

CREATE INDEX metrics_ts_brin ON metrics USING BRIN (ts);
-- Excellent for "give me last 24 hours" on append-only tables.

CREATE INDEX metrics_recent_idx
  ON metrics (device_id, ts DESC)
  WHERE ts >= NOW() - INTERVAL '7 days';
-- Issue: predicate must use literal date or be re-created periodically.

-- BAD:
WHERE date_trunc('hour', ts) = $1
-- GOOD:
WHERE ts >= $1 AND ts < $1 + INTERVAL '1 hour'

CREATE INDEX metrics_device_ts_idx ON metrics (device_id, ts DESC);

SELECT DISTINCT ON (device_id) device_id, ts, value
FROM metrics
ORDER BY device_id, ts DESC;
-- Uses the index to take the first row per device.

CLUSTER metrics USING metrics_device_ts_idx;
-- Cluster is heavy. TimescaleDB chunks help by keeping recent chunks small.