Range vs Hash Partitioning for Time-Series Workloads

This walkthrough gives you a decision framework for choosing between time-based RANGE partitions and HASH partitions for a time-series table — a core layout choice within range partitioning strategies and the broader partitioning implementation patterns & routing guide. The short version: RANGE on the timestamp wins for almost every genuinely time-series workload because it buys partition pruning on time-window queries and constant-time retention, while HASH buys uniform write spread at the cost of both. The rest of this page shows how to verify that claim against your workload rather than take it on faith, and when the composite range-then-hash escape hatch is the right answer.

What you need to know first

Collect these facts about your workload before walking the steps — every decision below keys off one of them:

• The dominant read shape: time-window scans (WHERE ts >= ...), single-key lookups (WHERE device_id = ...), or both combined
• Sustained and peak insert rate (rows/s), and whether ingest is append-only or includes late-arriving backfill
• The retention policy: how long rows live, and whether expiry is by age (typical) or by key
• Expected total row count and per-day volume, to size partition granularity (daily vs monthly)
• PostgreSQL 12+ or MySQL 8.0+ (both support the DDL shown; EXPLAIN output below is PostgreSQL)
• Whether partitions stay on one node or map to shards — hot-spotting only matters operationally when they map to different hardware

Step 1 — Profile your query shapes with EXPLAIN

The layouts are mirror images: RANGE prunes on time predicates, HASH prunes on key predicates. Build both layouts on a staging copy and compare plans for your top queries by frequency. The DDL for a 200-device sensor fleet ingesting one reading per device per second (~17M rows/day):

-- Layout A: RANGE by day
CREATE TABLE readings_range (
  device_id  BIGINT           NOT NULL,
  reading_ts TIMESTAMPTZ      NOT NULL,
  value      DOUBLE PRECISION NOT NULL,
  PRIMARY KEY (device_id, reading_ts)
) PARTITION BY RANGE (reading_ts);

CREATE TABLE readings_range_2026_07_05 PARTITION OF readings_range
  FOR VALUES FROM ('2026-07-05') TO ('2026-07-06');
CREATE TABLE readings_range_2026_07_06 PARTITION OF readings_range
  FOR VALUES FROM ('2026-07-06') TO ('2026-07-07');

-- Layout B: HASH by device, 8 partitions
CREATE TABLE readings_hash (
  device_id  BIGINT           NOT NULL,
  reading_ts TIMESTAMPTZ      NOT NULL,
  value      DOUBLE PRECISION NOT NULL,
  PRIMARY KEY (device_id, reading_ts)
) PARTITION BY HASH (device_id);

CREATE TABLE readings_hash_0 PARTITION OF readings_hash
  FOR VALUES WITH (MODULUS 8, REMAINDER 0);
-- ... repeat for REMAINDER 1 through 7

A dashboard query for the last hour prunes cleanly on the range layout:

EXPLAIN (COSTS OFF)
SELECT date_trunc('minute', reading_ts) AS m, avg(value)
FROM readings_range
WHERE reading_ts >= now() - interval '1 hour'
GROUP BY m;

 HashAggregate
   Group Key: date_trunc('minute', readings_range.reading_ts)
   ->  Seq Scan on readings_range_2026_07_06 readings_range
         Filter: (reading_ts >= (now() - '01:00:00'::interval))

One partition scanned out of however many exist — pruning happens at execution time even with the volatile now() predicate (“subplans removed” appears under EXPLAIN ANALYZE). The same query on the hash layout appends all eight partitions, because device_id hashes carry no time information:

 HashAggregate
   Group Key: date_trunc('minute', readings_hash.reading_ts)
   ->  Append
         ->  Seq Scan on readings_hash_0 readings_hash_1
               Filter: (reading_ts >= (now() - '01:00:00'::interval))
         ->  Seq Scan on readings_hash_1 readings_hash_2
               Filter: (reading_ts >= (now() - '01:00:00'::interval))
         ... 6 more partitions ...

Reverse the predicate and the tables turn: WHERE device_id = 4217 prunes to one partition on the hash layout but scans every daily partition on the range layout (each pruned further by its index). On a 90-day retention window that is 90 index probes vs 1.

Operational note: Run the comparison with EXPLAIN (ANALYZE, BUFFERS) and record shared read counts, not just plan shape. A fanned-out Append over 90 partitions whose indexes are cold can cost hundreds of random reads for a single-device query; that number is what your latency SLO feels.

DBA tip: Score each of your top ~10 queries as time-shaped, key-shaped, or both, weighted by calls/minute from pg_stat_statements. Most telemetry workloads come out 80–95% time-shaped, which is why the recommendation below defaults to RANGE. If your split is genuinely 50/50, jump ahead to the composite layout in Step 4.

Step 2 — Model retention and archival mechanics

Retention is where the two layouts stop being symmetrical and HASH starts losing. Time-series data almost always expires by age, and a range layout makes expiry a metadata operation:

-- Range layout: constant-time, non-blocking retention
ALTER TABLE readings_range DETACH PARTITION readings_range_2026_04_06 CONCURRENTLY;
DROP TABLE readings_range_2026_04_06;   -- or archive it first

DETACH ... CONCURRENTLY (PostgreSQL 14+) takes only a brief SHARE UPDATE EXCLUSIVE lock, so ingest never pauses. The freed space returns to the filesystem instantly, and the detached table can be dumped to object storage before the DROP. On the hash layout there is no partition whose contents are “the oldest day” — expiry means bulk DELETE on every partition:

-- Hash layout: retention is a rolling DELETE on all partitions
DELETE FROM readings_hash
WHERE reading_ts < now() - interval '90 days'
LIMIT 50000;  -- batched via a loop; plain DELETE has no LIMIT in PostgreSQL,
              -- so production jobs use ctid-batched or key-batched deletes

Every deleted row is a dead tuple that autovacuum must find and reclaim, the space does not return to the OS without VACUUM FULL or pg_repack, and the delete itself competes with ingest for I/O — every day, forever. Measured on the fleet above, dropping one daily range partition takes ~20 ms; deleting the equivalent 17M rows from the hash layout takes 40+ minutes of batched deletes followed by hours of vacuum activity.

Operational note: If a compliance archive is required, DETACH + COPY TO + DROP gives you an exact, restorable per-day artifact. Extracting the same day from a hash layout requires a filtered scan of all partitions before the delete even starts.

SRE tip: Whatever layout you pick, automate boundary management from day one — a missing future partition on a range layout rejects inserts outright. The partitioning implementation patterns & routing section’s automation guides cover schedulers for this; wire the creation job to an alert on relname absence, not just job success.

Step 3 — Compare index and vacuum behaviour under ingest

Write hot-spotting is the standard argument against time-range partitioning, so quantify what it actually costs. On the range layout, 100% of inserts land in today’s partition. On a single node this is the same B-tree behaviour as an unpartitioned append-only table: the right-most index leaf is hot, but PostgreSQL’s fastpath locking and (in 14+) bottom-up index deletion handle monotonic inserts well. The compensating win is cache locality — today’s partition indexes are the only hot indexes, they fit in shared_buffers, and yesterday’s partitions cool off completely: autovacuum touches them once after the day closes, then never again.

On the hash layout each partition receives 1/N of the ingest, so no single B-tree right edge is contended — genuinely useful past several hundred thousand rows/s or when partitions live on different tablespaces or shards (the same uniform-spread property that hash routing algorithms exploit across nodes). The costs: every partition’s index has a hot right edge (aggregate working set is N warm indexes, not one), and retention deletes keep autovacuum permanently busy on all partitions. Verify with:

SELECT relname,
       n_dead_tup,
       last_autovacuum,
       pg_size_pretty(pg_relation_size(relid)) AS size
FROM pg_stat_user_tables
WHERE relname LIKE 'readings_%'
ORDER BY n_dead_tup DESC
LIMIT 5;

On a healthy range layout, n_dead_tup is near zero everywhere except today’s partition. On a hash layout with rolling deletes, expect millions of dead tuples spread across all partitions between vacuum cycles — and plan autovacuum_max_workers accordingly.

DBA tip: Range partitions unlock BRIN indexes on the timestamp: because physical order correlates with reading_ts within each partition, a BRIN index a few hundred KB in size can replace a multi-GB B-tree for window scans. Hash partitions destroy that correlation, so they forfeit BRIN too.

Step 4 — Reach for range-then-hash sub-partitioning when both shapes matter

If Step 1 showed heavy time-window and single-key traffic, or Step 3 showed real insert contention, the escape hatch is composite partitioning: RANGE on time at the top level, HASH on the key inside each range. You keep time pruning and DROP-based retention, and spread each day’s ingest across N sub-partitions:

CREATE TABLE readings (
  device_id  BIGINT           NOT NULL,
  reading_ts TIMESTAMPTZ      NOT NULL,
  value      DOUBLE PRECISION NOT NULL,
  PRIMARY KEY (device_id, reading_ts)
) PARTITION BY RANGE (reading_ts);

-- Each daily range is itself hash-partitioned
CREATE TABLE readings_2026_07_06 PARTITION OF readings
  FOR VALUES FROM ('2026-07-06') TO ('2026-07-07')
  PARTITION BY HASH (device_id);

CREATE TABLE readings_2026_07_06_h0 PARTITION OF readings_2026_07_06
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE readings_2026_07_06_h1 PARTITION OF readings_2026_07_06
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE readings_2026_07_06_h2 PARTITION OF readings_2026_07_06
  FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE readings_2026_07_06_h3 PARTITION OF readings_2026_07_06
  FOR VALUES WITH (MODULUS 4, REMAINDER 3);

A query filtering on both reading_ts and device_id now prunes on both levels — one day, one hash bucket. Retention still detaches the whole daily range (children come with it). The price is table-count multiplication: 90 days × 4 buckets = 360 leaf tables, each with its own indexes, stats, and planner entries. The same layering works with LIST as the inner or outer level — see composite list-range partitioning for regional time-series data for the regional variant.

Operational note: Keep total leaf count in the low thousands. Planning time grows with partition count, and versions before PostgreSQL 13 lock every partition at plan time for some statement shapes.

SRE tip: Automate creation of the whole daily sub-tree atomically (one transaction). A daily range created without its hash children accepts no rows and fails ingest at midnight — the worst possible time.

Step 5 — Apply the decision table

Recommendation checklist — choose the first row that matches:

• Any query filters by time and data expires by age → RANGE on the timestamp (the default for time-series)
• Time-shaped reads dominate but ingest measurably contends on one partition, or ranges map to separate shards → composite range→hash
• Reads are almost entirely single-key, nothing expires by age, and uniform spread across nodes is the goal → HASH (your workload is key-value, not time-series)
• Undecided → RANGE. Converting range→composite later means only re-creating future partitions; escaping a hash layout means a full table rewrite.

Failure mode table

FAQ

Related

• Range Partitioning Strategies — parent guide covering boundary selection, pruning optimisation, and lifecycle management for RANGE-partitioned tables
• Hash Routing Algorithms — how hash-based distribution behaves at the routing layer, including consistent hashing across shards
• Composite List-Range Partitioning for Regional Time-Series Data — the sibling composite layout when region, not a hashed key, is the second dimension