Mapping IoT Sensor Workloads to Hash Partition Keys

Device telemetry has a distinctive shape: enormous append-only ingest, queries that are either “one device over a window” or “all devices in a window”, and a retention policy measured in months for raw data and years for aggregates. This guide maps that shape onto a concrete partition layout and shows what each decision costs. It applies the framework from Use-Case Mapping for Partition Strategies inside Database Partitioning Fundamentals & Architecture.

Prerequisites

Step 1 — Characterise the workload before choosing anything

Four numbers decide the layout:

-- ingest rate and device cardinality over the last hour
SELECT count(*)                                          AS readings,
       count(DISTINCT device_id)                         AS devices,
       round(count(*) / 3600.0)                          AS rows_per_second,
       round(count(*)::numeric / count(DISTINCT device_id), 1) AS rows_per_device
FROM   readings
WHERE  reading_ts >= now() - interval '1 hour';
 readings  | devices | rows_per_second | rows_per_device
-----------+---------+-----------------+-----------------
  4218804  |  118204 |            1172 |            35.7

-- which query shape dominates?
SELECT count(*) FILTER (WHERE query ~* 'device_id\s*=')   AS per_device,
       count(*) FILTER (WHERE query !~* 'device_id\s*=')  AS cross_device
FROM   pg_stat_statements WHERE query ILIKE '%from readings%';

Operational note: Rows per device per hour is the number that decides whether per-device queries need their own partition dimension. At 36 rows an hour, one device’s month is 26,000 rows — small enough that an index on a time-partitioned table would serve it.

DBA tip: Measure device cardinality growth as well as its current value. A fleet growing by 10,000 devices a month changes which layouts stay viable within a year.

Why time-only partitioning contends under telemetry ingest All 1,172 inserts per second carry a timestamp of now, so every one of them lands in the newest partition and appends to the right edge of its timestamp index. The older partitions are idle. Adding hash sub-partitions on the device id splits that single contended index into eight independent trees while keeping the monthly retention boundary intact. RANGE(reading_ts) only — one hot index 2026_06 · idle 2026_07 · idle 2026_081,172 inserts/s every insert buffer lock waits: 31% of insert time p99 insert latency: 62 ms RANGE(reading_ts) → HASH(device_id) × 8 — eight independent trees 2026_062026_07 2026_08parent h0h1h2h3 h4h5h6h7 ≈147 inserts/s each lock waits: 3% p99: 6 ms Total write volume is unchanged. What changed is the number of index pages competing for the same buffer locks, which is the specific bottleneck a monotonically increasing key creates.

Step 2 — Build the range-then-hash layout

CREATE TABLE readings (
    device_id    bigint       NOT NULL,
    reading_ts   timestamptz  NOT NULL,
    metric       text         NOT NULL,
    value        double precision NOT NULL,
    PRIMARY KEY (device_id, reading_ts, metric)
) PARTITION BY RANGE (reading_ts);

CREATE TABLE readings_2026_08 PARTITION OF readings
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01')
    PARTITION BY HASH (device_id);

DO $$ BEGIN
  FOR i IN 0..7 LOOP
    EXECUTE format('CREATE TABLE readings_2026_08_h%s PARTITION OF readings_2026_08
                      FOR VALUES WITH (MODULUS 8, REMAINDER %s)', i, i);
  END LOOP;
END $$;

Operational note: Putting device_id first in the primary key makes per-device time-series queries an index range scan rather than a filter, which is the shape most telemetry dashboards issue.

DBA tip: Skip a separate index on reading_ts alone. The partition bounds already answer time-range questions, and an extra index on the hottest column doubles write amplification for no read benefit.

Step 3 — Batch inserts so routing happens once

Row-at-a-time inserts pay routing overhead per row. Batching by bucket pays it once per batch:

Group the batch by bucket, then COPY once per bucket A batch of five thousand readings arrives unsorted. Grouping them in the client by the same hash the server uses produces eight groups, each written with a single COPY into its leaf partition. This replaces five thousand tuple-routing decisions with eight, and turns five thousand individual inserts into eight bulk loads. 5,000 readingsunsorted, mixed devices group by bucketsame hash as the server COPY → …_h0COPY → …_h1 COPY → …_h2… 5 more 8 bulk loads not 5,000 inserts The client must use the same hash as the server, so this optimisation ties the application to the layout. Keep a fallback that writes to the parent and let the database route, for use whenever the partition set is mid-change. 
# ingest.py — group by target bucket, then COPY each group
from collections import defaultdict

def bucket_of(device_id: int, modulus: int = 8) -> int:
    return hash_int64(device_id) % modulus      # must match the server's hash

def ingest(readings, conn, month: str):
    groups = defaultdict(list)
    for r in readings:
        groups[bucket_of(r.device_id)].append(r)

    with conn.cursor() as cur:
        for bucket, rows in groups.items():
            cur.copy_records_to_table(
                table_name=f"readings_{month}_h{bucket}",
                records=[(r.device_id, r.ts, r.metric, r.value) for r in rows],
                columns=("device_id", "reading_ts", "metric", "value"),
            )

Operational note: Writing directly to the leaf is an optimisation with a cost: the application now knows the layout. Keep a fallback path that writes to the parent, and use it whenever the layout is mid-change.

SRE tip: COPY is several times faster than multi-row INSERT for this shape. If the ingest path is on the edge of keeping up, that change usually buys more than any partitioning adjustment.

Step 4 — Downsample so retention can be short

Raw readings are needed for days; trends are needed for years:

CREATE TABLE readings_hourly (
    device_id  bigint      NOT NULL,
    bucket_ts  timestamptz NOT NULL,
    metric     text        NOT NULL,
    n          int         NOT NULL,
    avg_value  double precision NOT NULL,
    min_value  double precision NOT NULL,
    max_value  double precision NOT NULL,
    PRIMARY KEY (device_id, bucket_ts, metric)
) PARTITION BY RANGE (bucket_ts);

-- run hourly, one bucket behind the current hour
INSERT INTO readings_hourly (device_id, bucket_ts, metric, n, avg_value, min_value, max_value)
SELECT device_id, date_trunc('hour', reading_ts), metric,
       count(*), avg(value), min(value), max(value)
FROM   readings
WHERE  reading_ts >= date_trunc('hour', now()) - interval '1 hour'
  AND  reading_ts <  date_trunc('hour', now())
GROUP  BY 1, 2, 3
ON CONFLICT (device_id, bucket_ts, metric) DO NOTHING;
Two retention windows: 30 days raw, 5 years aggregated Raw readings accumulate at about 340 gigabytes a month and are dropped after thirty days, so the raw table stays near one month of data. Hourly aggregates are roughly one two-hundredth of the volume and are kept for five years, ending at about ninety gigabytes total. Together they cost far less than keeping raw data for five years, which would be twenty terabytes. 20 TB10 TB1 TB0 raw kept 5 years — 20 TB raw kept 30 days — flat at ~340 GB hourly aggregates kept 5 years — 90 GB year 1year 3year 5 Downsampling is what makes a short raw-retention policy acceptable to the people who would otherwise block it.

Operational note: Aggregate one full bucket behind the current hour, never the current one. Late-arriving readings for the in-progress hour would otherwise be silently excluded from a row already written.

DBA tip: ON CONFLICT DO NOTHING makes the aggregation job safe to re-run, which matters because it will be re-run — after a deploy, after a failure, and during a backfill.

Verification

Confirm the spread and the query shapes:

-- writes should be even across buckets
SELECT relname, n_tup_ins
FROM   pg_stat_user_tables
WHERE  relname LIKE 'readings_2026_08_h%'
ORDER  BY relname;
        relname        | n_tup_ins
-----------------------+-----------
 readings_2026_08_h0   |  52841002
 readings_2026_08_h1   |  52903118
 readings_2026_08_h2   |  52788410
 readings_2026_08_h3   |  52950227

Within a percent or two across buckets, and a per-device query planning to a single leaf:

EXPLAIN (COSTS OFF) SELECT reading_ts, value FROM readings
WHERE device_id = 8842 AND reading_ts >= '2026-08-01' AND reading_ts < '2026-08-02';

Failure mode table

Failure mode Root cause SRE mitigation
Insert latency spikes at month boundaries the new month’s sub-partitions did not exist yet, so rows landed in DEFAULT or failed pre-create months with their full hash set; alert on days-to-newest-bound as in the retention guide
One hash bucket is much larger than the others a single device emits far more readings than the rest, or device ids are not uniformly distributed give the dominant device its own list partition; verify the id distribution rather than assuming uniformity
Cross-device aggregates get slower every month they scan every bucket of every month in range, and the bucket count multiplied the leaf count serve them from readings_hourly instead of raw readings; keep raw scans to short windows

FAQ

Should IoT telemetry be partitioned by time or by device?

By time first if retention matters, with a hash of the device id underneath if per-device queries or write contention matter. Time alone gives cheap retention and a contended index right edge; device alone gives even writes and makes retention a row-by-row delete. The two-level layout is the usual answer for telemetry because both properties are needed.

How many hash buckets should the second level have?

Enough to spread index contention across cores, and no more. Eight to sixteen is typical: it removes the single hot btree right edge without multiplying the leaf count unnecessarily. Measure buffer lock waits before and after — if they have not fallen, the contention was somewhere else and more buckets will not help.

Does downsampling replace retention?

It complements it. Downsampling writes a coarser aggregate into a separate table so the raw partitions can be dropped earlier without losing the trend. Retention still drops the raw data on its schedule; downsampling is what makes dropping it acceptable to the people who would otherwise insist on keeping everything.

Back to Use Case Mapping For Partition Strategies