Choosing an Index Strategy for High‑Throughput Time‑Series Data in PostgreSQL 14+

Decision and Constraints

You need to ingest >10 000 rows per second into a sensor‑readings table that stores timestamp, device_id, and value. Typical queries filter a recent time window (last hour, last day) and occasionally look up a single device_id within that window. Storage is limited, and the database runs PostgreSQL 14 or newer.

The index choice must keep write latency low, keep index size small, and still serve both range scans and point lookups efficiently.

Index Strategy Comparison

Strategy	Write Overhead	Range Scan (time)	Point Lookup (device_id)	Storage	Maintenance
BRIN on timestamp	Very low	Excellent when data is physically ordered	Poor (full‑table scan)	Tiny (few MB per 10 M rows)	Auto; only brin_summarize_new_values needed
B‑tree on timestamp	Moderate	Good	Good	Medium (≈ 30 % of table size)	Regular VACUUM/ANALYZE
Partitioned B‑tree (daily partitions)	Low per partition	Excellent via partition pruning	Good	Medium (similar to single B‑tree)	Partition management (creation, detachment)
Hybrid: BRIN + partial B‑tree on device_id	Low	Excellent (BRIN)	Good for active devices (partial B‑tree)	Low (BRIN + small partial index)	Moderate (partial index rebuild when active‑device list changes)

Trade‑offs

• BRIN shines when inserts arrive in timestamp order so that heap pages correlate with the indexed column. Random or out‑of‑order inserts cause the index to degrade to sequential scans.
• B‑tree gives predictable point‑lookup performance but incurs higher write amplification and index bloat under heavy insert loads.
• Partitioned B‑tree adds operational complexity (partition creation, retention policies) but enables fast data‑lifecycle operations such as dropping old days.
• Hybrid captures the best of both worlds for the described workload: BRIN handles the dominant time‑range scans, while a partial B‑tree on device_id serves the occasional device‑specific queries without indexing every device.

Concrete Implementation (Hybrid on Daily Partitions)

Run the following as a superuser or a role with CREATE privileges on the target schema.

-- 1. Create the partitioned table
CREATE TABLE sensor_readings (
    reading_id   BIGSERIAL,
    ts           TIMESTAMPTZ NOT NULL,
    device_id    INT NOT NULL,
    value        DOUBLE PRECISION NOT NULL,
    PRIMARY KEY (reading_id, ts)   -- partition key must be part of PK
) PARTITION BY RANGE (ts);

-- 2. Create a default partition to catch stray rows
CREATE TABLE sensor_readings_default PARTITION OF sensor_readings DEFAULT;

-- 3. Use pg_partman (install via CREATE EXTENSION pg_partman) to automate daily partitions
SELECT partman.create_parent(
    p_parent_table := 'public.sensor_readings',
    p_control      := 'ts',
    p_type         := 'range',
    p_interval     := '1 day',
    p_premake      := 30,
    p_automatic    := 'true'
);

-- 4. Add a BRIN index on each partition (pages_per_range=128 keeps index tiny)
--    pg_partman can apply this automatically via a template table, but a one‑off example:
CREATE INDEX brin_sensor_ts ON sensor_readings USING BRIN (ts) WITH (pages_per_range = 128);

-- 5. Partial B‑tree for the current set of active devices (example: devices 1‑100)
CREATE INDEX btree_sensor_device_active ON sensor_readings (device_id)
    WHERE device_id BETWEEN 1 AND 100;

Notes:

• The primary key includes ts because partitioned tables require the partition key in any unique index.
• pages_per_range = 128 means each BRIN entry summarizes 128 heap pages (≈ 1 MiB), balancing index size and scan precision.
• The partial index predicate must be stable; if the active‑device list changes, drop and recreate the index or maintain a materialized view of active devices and reference it in the predicate.

Validation Script

Execute on a test instance with the same PostgreSQL version and hardware profile as production.

-- 1. Load 10 M rows over 30 days (≈ 333 k rows/day)
--    Use pgbench with a custom script or a simple INSERT loop.
--    Example pgbench script (save as load.sql):
\set n 10000000
\set start '2026-01-01 00:00:00+00'
INSERT INTO sensor_readings (ts, device_id, value)
SELECT
    (timestamp :start + (random() * interval '30 days')) AS ts,
    (floor(random() * 1000) + 1)::int AS device_id,
    random() * 100 AS value
FROM generate_series(1, :n);

-- Run: pgbench -f load.sql -c 8 -j 4 -t 1 

-- 2. Query 1 – recent time‑range scan (last hour)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM sensor_readings
WHERE ts >= now() - interval '1 hour';

-- 3. Query 2 – device lookup with timestamp filter
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM sensor_readings
WHERE device_id = 42
  AND ts >= now() - interval '1 day';

-- 4. Write latency benchmark (pgbench, 5‑minute run)
pgbench -c 16 -j 4 -T 300 -f <(cat <<'EOF'
INSERT INTO sensor_readings (ts, device_id, value)
VALUES (now(), (random()*1000)::int, random()*100);
EOF
) 

Collect for each strategy:

• Execution time of the two EXPLAIN ANALYZE queries.
• Index size: SELECT pg_size_pretty(pg_total_relation_size('sensor_readings')); and per‑index sizes via pg_indexes_size.
• Average write latency from pgbench output (tps, latency average).

Verification Steps

1. BRIN correlation check – after loading, run:

   SELECT attname, correlation FROM pg_stats WHERE tablename = 'sensor_readings';
   

   A correlation close to 1.0 on ts confirms that BRIN will remain effective.
2. Index bloat monitoring – after the sustained write test, use pgstattuple or the community function pg_index_bloat_check() to verify that B‑tree bloat stays within acceptable limits (< 20 %).
3. Partition pruning proof – in the EXPLAIN output for the time‑range query, ensure only the relevant daily partitions appear (look for Partitioned Seq Scan on a single partition).

Limitations & Practical Checks

• BRIN indexes require physical ordering. If your ingest pipeline ever writes out‑of‑order timestamps, schedule a periodic CLUSTER sensor_readings USING brin_sensor_ts; or reload data in order.
• Partial indexes on device_id become stale when the active‑device set changes. Automate a daily job that recreates the index with the current list, or maintain a materialized view active_devices and reference it via WHERE device_id IN (SELECT device_id FROM active_devices) (requires PostgreSQL 14+ for subquery in index predicate).
• Partitioned tables in PostgreSQL 14+ do not support foreign keys referencing them, and unique indexes must include the partition key. Plan schema changes accordingly.
• The validation script measures a single workload shape. Before committing, replay a production‑like query mix (including aggregations, down‑sampling) to ensure the hybrid approach still meets latency SLAs.

Result Check

If the hybrid strategy shows:

• Time‑range query ≤ 50 ms for 1 hour window on 10 M rows,
• Device‑specific query ≤ 5 ms,
• Write latency ≤ 1 ms per row at 10 k rows/sec,
• Total index size < 5 % of table size,

then the design meets the stated constraints. Otherwise, iterate: increase pages_per_range, adjust partition granularity, or widen the partial index predicate.