SQL Academy · Lesson

Time-Series Index Choices

Choose the right indexes for time-series workloads — composite (device_id, ts DESC) covers the common pattern.

The Common Time-Series Query

"Recent data for one entity":

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

The canonical index for time-series:

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

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