Time-Series Indexing for Asset and Timestamp Queries

Scope: which queries, which table

This guide is about choosing indexes for a narrow readings table, one row per point per timestamp, as designed in How to Store IoT Sensor Data in a Database. It assumes you already have that table, or something close to it, and want to know which indexes to add, in which column order, and what each costs. It is written for developers and DBAs working on BMS, CAFM, CMMS and IoT platforms in PostgreSQL, with notes for MySQL and TimescaleDB.

It does not repeat partitioning (see Choosing Time-Series Partition Intervals) or the write cost of indexes in general (see Time-Series Database Performance). If B-tree indexes are new to you, read SQL indexes explained for beginners and how SQL indexes improve performance first.

The example data

The example models 50 air handling units with four points each (supply and return air temperature, zone CO2, fan speed): 200 points, one reading a minute for seven days, 2,016,000 rows. Rows are inserted in time order, as a live ingest would write them, so the readings for one point are interleaved with those of the other 199. One reading an hour per point (minute 17) has a non-zero quality code, to give the exception queries something to find.

SET TimeZone = 'UTC';

CREATE TABLE point (
    point_id   integer PRIMARY KEY,
    asset_tag  text NOT NULL,
    point_name text NOT NULL
);
INSERT INTO point
SELECT p,
       'AHU-' || lpad(((p - 1) / 4 + 1)::text, 2, '0'),
       (ARRAY['supply_air_temp', 'return_air_temp', 'zone_co2', 'fan_speed'])[(p - 1) % 4 + 1]
FROM generate_series(1, 200) AS p;

CREATE TABLE reading (
    point_id  integer NOT NULL,
    ts        timestamptz NOT NULL,
    value     double precision,
    quality   smallint NOT NULL DEFAULT 0
);
INSERT INTO reading (point_id, ts, value, quality)
SELECT p,
       g.ts,
       20 + (p % 7) + extract(minute FROM g.ts) / 10.0,
       CASE WHEN extract(minute FROM g.ts) = 17 THEN 1 ELSE 0 END
FROM generate_series(1, 200) AS p
CROSS JOIN generate_series('2026-10-01 00:00+00'::timestamptz,
                           '2026-10-07 23:59+00'::timestamptz,
                           interval '1 minute') AS g(ts)
ORDER BY g.ts, p;

SELECT count(*) AS rows FROM reading;
--   rows
-- ---------
--  2016000

Composite B-tree: point first, timestamp second

The PostgreSQL documentation states the rule for multicolumn B-trees precisely: equality constraints on leading columns, plus an inequality on the first column without an equality constraint, limit the part of the index that is scanned. Constraints on later columns are checked inside the index but do not necessarily shrink the scan. For WHERE point_id = 42 AND ts BETWEEN ... that means:

• (point_id, ts): the scan starts at the first entry for point 42 inside the range and stops at the last one. Only matching entries are read.
• (ts, point_id): the range on ts bounds the scan, but inside it every point's entries are read and filtered on point_id. For six hours that is 72,000 entries to find 360.

The run below creates the timestamp-first index, measures the query, then replaces it with the point-first primary key and measures again. EXPLAIN (ANALYZE, BUFFERS) runs the query and reports how many 8 kB buffers each plan node touched, which is a fairer comparison than timing on a small machine.

CREATE INDEX reading_ts_point ON reading (ts, point_id);
VACUUM ANALYZE reading;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT ts, value FROM reading
WHERE point_id = 42
  AND ts >= '2026-10-03 00:00+00' AND ts < '2026-10-03 06:00+00';

Index Scan using reading_ts_point on reading (actual rows=360.00 loops=1)
  Index Cond: ((ts >= '2026-10-03 00:00:00+00'::timestamp with time zone) AND (ts < '2026-10-03 06:00:00+00'::timestamp with time zone) AND (point_id = 42))
  Index Searches: 1
  Buffers: shared hit=369 read=278
Planning:
  Buffers: shared hit=15

DROP INDEX reading_ts_point;
ALTER TABLE reading ADD PRIMARY KEY (point_id, ts);
VACUUM ANALYZE reading;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT ts, value FROM reading
WHERE point_id = 42
  AND ts >= '2026-10-03 00:00+00' AND ts < '2026-10-03 06:00+00';

Bitmap Heap Scan on reading (actual rows=360.00 loops=1)
  Recheck Cond: ((point_id = 42) AND (ts >= '2026-10-03 00:00:00+00'::timestamp with time zone) AND (ts < '2026-10-03 06:00:00+00'::timestamp with time zone))
  Heap Blocks: exact=360
  Buffers: shared hit=360 read=4
  ->  Bitmap Index Scan on reading_pkey (actual rows=360.00 loops=1)
        Index Cond: ((point_id = 42) AND (ts >= '2026-10-03 00:00:00+00'::timestamp with time zone) AND (ts < '2026-10-03 06:00:00+00'::timestamp with time zone))
        Index Searches: 1
        Buffers: shared read=4
Planning:
  Buffers: shared hit=14

Reading the two plans

These buffer counts are an illustrative measurement on a small PGlite sample (PostgreSQL 18.6 compiled to 32-bit WebAssembly); not a production benchmark and not representative of your server. They come from one run on synthetic, perfectly ordered data; read them as relative differences between the two plans, not as performance figures. The point-first index found the 360 rows by reading 4 index pages. The other 360 buffers are heap pages: Heap Blocks: exact=360 means each of the 360 readings sits on a different table page, because the table is in arrival order and 199 other points were written between consecutive readings of point 42. The timestamp-first plan touched 647 buffers for the same rows; the extra buffers are the index entries of the other points in the range. The gap grows with the number of points and the length of the range.

PostgreSQL 18 adds B-tree skip scan, which can sometimes use an index whose leading column has no condition. The documentation describes it as worthwhile mainly when the leading column has few distinct values; it is not a reason to put ts first. The plan above reports Index Searches: 1, so no skipping took place.

Asset queries and latest values use the same index

You do not need an asset_id column in the readings table to query by asset. Filter the small point table, then let the planner probe the primary key once per point. The plan is a nested loop: a scan of point for the asset, then a bitmap index scan of reading_pkey per point.

For the latest value of each point, a LATERAL subquery with ORDER BY ts DESC LIMIT 1 lets each point read one entry from the end of its part of the index. B-tree indexes can be scanned backward, so no separate descending index is needed.

EXPLAIN (COSTS OFF)
SELECT p.point_name, r.ts, r.value
FROM reading r
JOIN point p USING (point_id)
WHERE p.asset_tag = 'AHU-11'
  AND r.ts >= '2026-10-03 08:00+00' AND r.ts < '2026-10-03 09:00+00';

-- Nested Loop
--   ->  Seq Scan on point p
--         Filter: (asset_tag = 'AHU-11'::text)
--   ->  Bitmap Heap Scan on reading r
--         Recheck Cond: ((point_id = p.point_id) AND (ts >= '2026-10-03 08:00:00+00'::timestamp with time zone) AND (ts < '2026-10-03 09:00:00+00'::timestamp with time zone))
--         ->  Bitmap Index Scan on reading_pkey
--               Index Cond: ((point_id = p.point_id) AND (ts >= '2026-10-03 08:00:00+00'::timestamp with time zone) AND (ts < '2026-10-03 09:00:00+00'::timestamp with time zone))

EXPLAIN (COSTS OFF)
SELECT p.point_name, l.ts, l.value
FROM point p
CROSS JOIN LATERAL (
    SELECT ts, value FROM reading r
    WHERE r.point_id = p.point_id
    ORDER BY ts DESC
    LIMIT 1
) l
WHERE p.asset_tag = 'AHU-11';

-- Nested Loop
--   ->  Seq Scan on point p
--         Filter: (asset_tag = 'AHU-11'::text)
--   ->  Limit
--         ->  Index Scan Backward using reading_pkey on reading r
--               Index Cond: (point_id = p.point_id)

SELECT p.point_name, l.ts, l.value
FROM point p
CROSS JOIN LATERAL (
    SELECT ts, value FROM reading r
    WHERE r.point_id = p.point_id
    ORDER BY ts DESC
    LIMIT 1
) l
WHERE p.asset_tag = 'AHU-11';

--    point_name    |           ts           | value
-- -----------------+------------------------+-------
--  supply_air_temp | 2026-10-07 23:59:00+00 |  31.9
--  return_air_temp | 2026-10-07 23:59:00+00 |  25.9
--  zone_co2        | 2026-10-07 23:59:00+00 |  26.9
--  fan_speed       | 2026-10-07 23:59:00+00 |  27.9

When to denormalise the asset

Copying asset_id into every reading and indexing (asset_id, ts) serves "everything for this asset" with one range scan instead of one probe per point. It costs 4 bytes per row and an extra index on every insert, and it goes stale if points move between assets. In our judgement it is worth considering only when assets have many points (hundreds is an illustrative figure, not a threshold) and asset-level queries dominate; compare both plans on your data. For a live dashboard over thousands of points, a small latest_value table updated by the ingest job (one row per point, upserted) may be cheaper than querying the history table, at the cost of an extra write per reading. With several ingest workers, two writes for the same point can race: a read-then-write pattern (look up the stored row, then UPDATE or INSERT) can let an older reading overwrite a newer one or fail on a duplicate key. The safer documented alternative is a single INSERT ... ON CONFLICT (point_id) DO UPDATE statement, which the PostgreSQL documentation says guarantees an atomic insert or update outcome even under high concurrency, with a WHERE on the update so that only a newer ts replaces the stored row; the documentation adds that the condition is evaluated after a conflict has been identified, and that all conflicting rows are locked when the update action is taken. The example below is educational: it ran in a single session only, so it shows the statement's logic, not its behaviour under concurrent workers, which rests on the documentation quoted here and was not run.

CREATE TABLE latest_value (
    point_id integer PRIMARY KEY,
    ts       timestamptz NOT NULL,
    value    double precision
);

-- the ingest job runs this once per reading (or per batch, one row per point)
INSERT INTO latest_value AS lv (point_id, ts, value)
VALUES (42, '2026-10-07 23:59+00', 21.7)
ON CONFLICT (point_id) DO UPDATE
    SET ts = EXCLUDED.ts, value = EXCLUDED.value
    WHERE lv.ts < EXCLUDED.ts;

-- a late, older reading for the same point arrives afterwards: not applied
INSERT INTO latest_value AS lv (point_id, ts, value)
VALUES (42, '2026-10-07 23:58+00', 21.5)
ON CONFLICT (point_id) DO UPDATE
    SET ts = EXCLUDED.ts, value = EXCLUDED.value
    WHERE lv.ts < EXCLUDED.ts;
SELECT point_id, ts::text AS ts, value FROM latest_value;
--  point_id |           ts           | value
-- ----------+------------------------+-------
--        42 | 2026-10-07 23:59:00+00 |  21.7

-- a newer reading: applied
INSERT INTO latest_value AS lv (point_id, ts, value)
VALUES (42, '2026-10-08 00:00+00', 21.9)
ON CONFLICT (point_id) DO UPDATE
    SET ts = EXCLUDED.ts, value = EXCLUDED.value
    WHERE lv.ts < EXCLUDED.ts;
SELECT point_id, ts::text AS ts, value FROM latest_value;
--  point_id |           ts           | value
-- ----------+------------------------+-------
--        42 | 2026-10-08 00:00:00+00 |  21.9

Covering indexes: avoid the heap for trend queries

The point-first plan still visited 360 heap pages to fetch value. A covering index stores the payload column in the index leaf with INCLUDE, so the query can be answered by an index-only scan. That works only for heap pages marked all-visible in the visibility map, which VACUUM (or autovacuum) maintains; on append-mostly readings that is most of the table once vacuum has run.

CREATE INDEX reading_point_ts_incl ON reading (point_id, ts) INCLUDE (value);
VACUUM ANALYZE reading;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT ts, value FROM reading
WHERE point_id = 42
  AND ts >= '2026-10-03 00:00+00' AND ts < '2026-10-03 06:00+00';

Index Only Scan using reading_point_ts_incl on reading (actual rows=360.00 loops=1)
  Index Cond: ((point_id = 42) AND (ts >= '2026-10-03 00:00:00+00'::timestamp with time zone) AND (ts < '2026-10-03 06:00:00+00'::timestamp with time zone))
  Heap Fetches: 0
  Index Searches: 1
  Buffers: shared hit=1 read=4
Planning:
  Buffers: shared hit=19

What the covering index costs

In this run (an illustrative measurement on a small PGlite sample (PostgreSQL 18.6 compiled to 32-bit WebAssembly); not a production benchmark and not representative of your server), five buffers instead of 364, but the covering index was 78 MB next to a 61 MB primary key and a 116 MB table (sizes below). Keeping both doubles the B-tree work on every insert. Two common ways out: make the primary key itself covering (PRIMARY KEY (point_id, ts) INCLUDE (value), which the documentation allows), or accept the heap visits and rely on partitioning to keep the working set small. Do not include wide or rarely read columns; the documentation warns that payload columns bloat the index.

BRIN for site-wide time windows

Reports such as "all meters yesterday" constrain only ts. A B-tree on ts would work, but it is as large as the primary key and adds the same write cost. A BRIN index stores a summary (here the minimum and maximum timestamp) per range of table blocks and is very small (24 kB in the illustrative PGlite run below). It works because readings are written roughly in time order, so each block range covers a narrow slice of time. BRIN is lossy: the executor rechecks every row in the matching block ranges and discards the rest, which the plan shows as Rows Removed by Index Recheck.

CREATE INDEX reading_ts_brin ON reading USING brin (ts);
VACUUM ANALYZE reading;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*), avg(value) FROM reading
WHERE ts >= '2026-10-05 00:00+00' AND ts < '2026-10-05 01:00+00';

Aggregate (actual rows=1.00 loops=1)
  Buffers: shared hit=133
  ->  Bitmap Heap Scan on reading (actual rows=12000.00 loops=1)
        Recheck Cond: ((ts >= '2026-10-05 00:00:00+00'::timestamp with time zone) AND (ts < '2026-10-05 01:00:00+00'::timestamp with time zone))
        Rows Removed by Index Recheck: 5408
        Heap Blocks: lossy=128
        Buffers: shared hit=133
        ->  Bitmap Index Scan on reading_ts_brin (actual rows=1280.00 loops=1)
              Index Cond: ((ts >= '2026-10-05 00:00:00+00'::timestamp with time zone) AND (ts < '2026-10-05 01:00:00+00'::timestamp with time zone))
              Index Searches: 1

Where BRIN stops working

BRIN depends on physical order matching time order. A gateway that uploads a week of buffered readings after an outage, a bulk back-fill, or heavy updates that move rows all widen the block ranges, and the index then matches more of the table. New block ranges are summarised by vacuum (or immediately if the index has autosummarize enabled), so very recent data may be read without help from the index until then. If you partition by time, partition pruning already removes most of the table for these queries and BRIN only narrows the scan within each partition. For late data and how it affects storage order, see Late-Arriving and Out-of-Order Sensor Events.

Partial indexes for exception queries

Data quality and override reports ask for the small fraction of rows with a non-zero quality code or a status flag set. A partial index contains only rows that match its predicate, so it stays small and its maintenance cost falls only on those rows. The query must include the same predicate for the planner to use it.

CREATE INDEX reading_bad_quality ON reading (point_id, ts) WHERE quality <> 0;
ANALYZE reading;

EXPLAIN (COSTS OFF)
SELECT point_id, ts FROM reading
WHERE quality <> 0 AND point_id = 7 AND ts >= '2026-10-06 00:00+00';

-- Index Only Scan using reading_bad_quality on reading
--   Index Cond: ((point_id = 7) AND (ts >= '2026-10-06 00:00:00+00'::timestamp with time zone))

SELECT c.relname AS relation, pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_class c
WHERE c.relname IN ('reading', 'reading_pkey', 'reading_ts_brin',
                    'reading_point_ts_incl', 'reading_bad_quality')
ORDER BY pg_relation_size(c.oid) DESC;

--        relation        |  size
-- -----------------------+---------
--  reading               | 116 MB
--  reading_point_ts_incl | 78 MB
--  reading_pkey          | 61 MB
--  reading_bad_quality   | 1048 kB
--  reading_ts_brin       | 24 kB

MySQL InnoDB and TimescaleDB differences

MySQL InnoDB. The primary key is the clustered index: the table rows are stored in primary-key order. With PRIMARY KEY (point_id, ts), one point's readings sit together, so the heap-scatter problem above does not arise for point queries, but inserts arriving in time order land in many places in the tree instead of at one end. Every InnoDB secondary index carries the primary-key columns, so a secondary index on ts already covers point_id. The plans below are from MySQL 8.0.46 on 72,000 rows (50 points, one day, one-minute data).

CREATE TABLE reading (
    point_id  int NOT NULL,
    ts        datetime(3) NOT NULL,
    value     double,
    quality   smallint NOT NULL DEFAULT 0,
    PRIMARY KEY (point_id, ts),
    KEY reading_ts (ts)
) ENGINE=InnoDB;

-- 50 points x one day of one-minute readings (72,000 rows)
SET SESSION cte_max_recursion_depth = 2000;
INSERT INTO reading (point_id, ts, value)
WITH RECURSIVE m (n) AS (SELECT 0 UNION ALL SELECT n + 1 FROM m WHERE n < 1439),
               p (id) AS (SELECT 1 UNION ALL SELECT id + 1 FROM p WHERE id < 50)
SELECT p.id, TIMESTAMP '2026-10-05 00:00:00' + INTERVAL m.n MINUTE, 20 + p.id / 10
FROM p CROSS JOIN m;
ANALYZE TABLE reading;

EXPLAIN FORMAT=TREE SELECT ts, value FROM reading
WHERE point_id = 42 AND ts >= '2026-10-05 06:00' AND ts < '2026-10-05 12:00';
-- -> Filter: ((reading.point_id = 42) and (reading.ts >= TIMESTAMP'2026-10-05 06:00:00') and (reading.ts < TIMESTAMP'2026-10-05 12:00:00'))  (cost=72.4 rows=360)
--     -> Index range scan on reading using PRIMARY over (point_id = 42 AND '2026-10-05 06:00:00.000' <= ts < '2026-10-05 12:00:00.000')  (cost=72.4 rows=360)

EXPLAIN FORMAT=TREE SELECT point_id, ts FROM reading
WHERE ts >= '2026-10-05 06:00' AND ts < '2026-10-05 06:05';
-- -> Filter: ((reading.ts >= TIMESTAMP'2026-10-05 06:00:00') and (reading.ts < TIMESTAMP'2026-10-05 06:05:00'))  (cost=50.4 rows=250)
--     -> Covering index range scan on reading using reading_ts over ('2026-10-05 06:00:00.000' <= ts < '2026-10-05 06:05:00.000')  (cost=50.4 rows=250)

EXPLAIN FORMAT=TREE SELECT ts, value FROM reading
WHERE point_id = 42 ORDER BY ts DESC LIMIT 1;
-- -> Limit: 1 row(s)  (cost=145 rows=1)
--     -> Index lookup on reading using PRIMARY (point_id=42) (reverse)  (cost=145 rows=1440)

-- A hypertable gets a B-tree on the time column (descending) by default.
-- Unique indexes and primary keys must include the time (partition) column,
-- because uniqueness is enforced per chunk.
CREATE TABLE reading (
    point_id  integer NOT NULL,
    ts        timestamptz NOT NULL,
    value     double precision,
    quality   smallint NOT NULL DEFAULT 0,
    PRIMARY KEY (point_id, ts)
) WITH (tsdb.hypertable);

TimescaleDB notes

In TimescaleDB each index exists per chunk, so the index being written to covers only the current chunk rather than the whole table; this is the same effect as partitioning in plain PostgreSQL. The documentation says the default time index can be turned off with timescaledb.create_default_indexes = false if you plan to create your own, which avoids maintaining both a time-only index and a (point_id, ts) key. Once chunks move to the columnstore, row-level B-tree indexes are no longer the main access path; see Time-Series Compression and Columnar Storage and TimescaleDB Explained.

Decision checklist, limitations and when not to add an index

Start from the primary key and add the others only when a query pattern you actually run shows up in EXPLAIN as reading far more than it returns.

Limitations and when not to add an index

Limitations. The numbers above are an illustrative measurement on a small PGlite sample (PostgreSQL 18.6 compiled to 32-bit WebAssembly); not a production benchmark and not representative of your server, from one run on synthetic, perfectly regular data in an in-process PostgreSQL build. Real tables have dead rows, uneven point counts and late data; confirm with EXPLAIN (ANALYZE, BUFFERS) on a copy of your own data before and after each change, and watch insert throughput as well as query plans.

When not to add an index. Do not index a column because it appears in a WHERE clause once a month; a scan of one partition may be acceptable. Do not add a (ts) B-tree "for safety" on a table partitioned by time without checking a plan first: for queries that filter on the partition key, pruning already handles the time predicate. And do not index tag-like columns with many distinct values per row without checking their cardinality first; see High-Cardinality Tags in Time-Series Databases. For the overall picture of time-series storage, return to the time-series overview.

Frequently asked questions

Sources

• PostgreSQL 18 documentation: Multicolumn Indexes
• PostgreSQL 18 documentation: Index-Only Scans and Covering Indexes
• PostgreSQL 18 documentation: BRIN Indexes
• PostgreSQL 18 documentation: Partial Indexes
• PostgreSQL 18 documentation: Indexes and ORDER BY
• PostgreSQL 18 documentation: Using EXPLAIN
• PostgreSQL 18 documentation: Table Expressions (LATERAL)
• MySQL 8.0 Reference Manual: Clustered and Secondary Indexes
• TimescaleDB documentation: Hypertable indexes
• PostgreSQL 18 documentation: INSERT (ON CONFLICT)

Checked 27 October 2026.

How we research these articles: our editorial method. Research-based from official documentation; SQL examples are run on a local database where the page says so. CodeWithSQL earns nothing from the vendors mentioned.