0Pricing
SQL Academy · Lesson

Time-Series Index Choices

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

Time-Series Index Choices is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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;

Composite Index (device_id, ts DESC)

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.

Why Order Matters

  • (device_id, ts) — best for "this device, recent data"
  • (ts, device_id) — best for "this time, all devices"

Pick by query mix.

BRIN for Append-Only Tables

BRIN is tiny — a few MB for a billion-row table. Only effective when data is physically sorted by the indexed column (which time-series usually is):

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

B-tree vs BRIN

  • B-tree — millisecond lookups by exact id; lookups by recent time
  • BRIN — fast range scans on monotonic data; cheap to maintain

You can combine: B-tree on (device_id), BRIN on (ts).

Hypertable Indexes

TimescaleDB propagates indexes to all chunks. Drop unused indexes — they cost on every chunk.

Partial Indexes for Hot Subsets

If 99% of queries hit recent data:

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.

Avoiding Functions on the Index Column

Don't apply functions to the indexed column — kills index use:

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

Index for Latest-Row Queries

For "the latest reading per device", the right index turns the query into an index range scan:

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 on the Hot Order

CLUSTER physically reorders the table by an index. One-time operation; new data still appends in arrival order:

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

Hash Index for High-Cardinality Equality

Rarely used for time-series but exists. B-tree on device_id is usually enough.

Don't Over-Index

Time-series tables are write-heavy. Every index = write amplification. Add only what you query.

Recap

Time-series indexing is a small set of patterns.

  • Composite (entity, ts DESC) for per-entity queries
  • BRIN on ts for append-only range queries
  • Hypertables: indexes propagate to chunks
  • Avoid function-on-column predicates

Quick Check

Your hottest time-series query filters by device_id and reads the last 24 hours. Best index?

Frequently asked questions

Is the “Time-Series Index Choices” lesson free?

Yes — the full text of “Time-Series Index Choices” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Time-Series Index Choices”?

Choose the right indexes for time-series workloads — composite (device_id, ts DESC) covers the common pattern. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Time-Series Index Choices” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. TimescaleDB Hypertables
  2. Continuous Aggregates
  3. Compression and Retention Policies
  4. Time-Series Index Choices
← Back to SQL Academy