📅Range and hash partitioning

Time-aligned slices vs spread keys

Range partitions line up with ordered keys, time-series workloads get partition elimination on typical WHERE occurred_at BETWEEN … filters and clean retention by dropping months. Hash spreads high-cardinality keys pseudo-randomly, useful when time is not the dominant filter and you want even size, at the cost of range scans across many children.

SQL

Monthly range child tables

CREATE TABLE events_2026_04 PARTITION OF events
FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

✓Illustrative result set

status
CREATE TABLE

SQL

Pruning-friendly query

SELECT COUNT(*) AS n
FROM events
WHERE occurred_at >= TIMESTAMPTZ '2026-04-01'
  AND occurred_at < TIMESTAMPTZ '2026-05-01';

✓Illustrative result set

n
18400000

SQL

Hash shard key (conceptual)

-- Pseudo: shard_id = hashtext(customer_id) % num_shards
SELECT customer_id, shard_id
FROM customers_shard_map
WHERE customer_id IN (101, 102, 103);

✓Illustrative result set

customer_id	shard_id
101	2
102	0
103	2

Operational surprises

Hash-only layout when almost every query filters by time, no prune, cold scans.
Running into “partition does not exist” because future months were never pre-created.
Bucket count chosen without cluster topology, uneven nodes under load.

Automation and EXPLAIN

Automate partition creation, archival, and alerts on child table row skew.
Confirm partition bounds with EXPLAIN on worst-case parameters.
Document composite strategies (e.g., range on time, subpartition on tenant) for large anchors.

🔀Hybrid mindset

When both retention and skew matter.

Range first

Anchor time for lifecycle and compliance; use hash or list inside only when a single month still hosts too much data for one child.

Rebalance

Tenant growth can turn an even hash map into a skew map, schedule moves before one partition becomes an accidental single point of pain.

Engine differences

Hash bucket counts, default partitions, and attach/detach semantics differ across vendors, treat recipes as starting points, not portable law.

TL;DR

Range partitioning splits data by contiguous key intervals (time, tenant ranges): great for pruning old months and rolling maintenance. Hash partitioning spreads rows evenly across buckets for load balance when range locality is weak. Both require queries to filter on the partition key to prune; hash adds reshuffle cost for cross-bucket joins. Defaults and DDL differ by engine, validate attach/detach and default-partition behavior in your vendor docs.