PostgreSQL partitioning for mobile backends: Range, Hash, or List?

Meta description: Compare PostgreSQL Range, Hash, and List partitioning strategies for mobile event streams and session data — with query benchmarks, pruning mechanics, and a zero-downtime retention pattern.

Tags: backend architecture mobile microservices api

---

TL;DR

For mobile backends storing time-series event data, Range partitioning wins in nearly every real-world scenario — partition pruning eliminates irrelevant pages at the planner level, rolling retention becomes a single DDL statement, and query locality matches how engineers actually query the data (by time window). Hash works for write distribution under extreme throughput. List is a niche tool. The numbers and mechanics below explain exactly why.

---

The problem most mobile teams hit too late

In my experience building production systems, mobile backends accumulate data faster than any other application category. A single active user might generate 50–200 events per session — clicks, impressions, errors, purchases, heartbeats. At 500K DAU, that’s 25–100 million rows per day before you’ve added any analytics tables.

Teams typically notice the problem when a SELECT that took 40ms at launch takes 4 seconds six months later, and EXPLAIN ANALYZE reveals a sequential scan across 800 million rows.

Here’s what most teams get wrong: they reach for indexing first. An index on created_at helps, but it doesn’t eliminate the fundamental cost of a monolithic heap. Partitioning does.

---

Partition strategy comparison

Strategy	Pruning trigger	Write distribution	Retention eviction	Cross-partition join cost
Range (time)	WHERE created_at BETWEEN	Hot partition absorbs writes	DETACH PARTITION — O(1)	Low — pruned by time
Hash	WHERE user_id = ?	Even across N buckets	Requires per-partition deletion	High — full scan if time filter absent
List	WHERE region = 'APAC'	Skewed by region traffic	Manual per-value	Medium

The difference matters. A Range-partitioned table with monthly partitions and a WHERE created_at >= NOW() - INTERVAL '30 days' query touches 1–2 partitions. The same query on a Hash-partitioned table by user_id touches all partitions because the time predicate can’t prune by hash bucket.

---

Range partitioning: the mechanics

CREATE TABLE mobile_events (
    id          BIGSERIAL,
    user_id     UUID        NOT NULL,
    event_type  TEXT        NOT NULL,
    payload     JSONB,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

CREATE TABLE mobile_events_2026_09
    PARTITION OF mobile_events
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

PostgreSQL’s constraint exclusion evaluates partition bounds at plan time. When a query carries a created_at predicate, the planner reads the partition catalog and eliminates non-matching child tables before execution begins — not during it. This isn’t index-assisted filtering; it’s structural elimination.

The attach/detach pattern for rolling retention

Zero-downtime retention eviction is why Range wins for mobile backends:

-- Detach 90-day-old partition (instant, non-blocking)
ALTER TABLE mobile_events
    DETACH PARTITION mobile_events_2026_06 CONCURRENTLY;

-- Archive to cold storage, then drop
DROP TABLE mobile_events_2026_06;

DETACH CONCURRENTLY (PostgreSQL 14+) acquires only a ShareUpdateExclusiveLock — reads and writes to other partitions continue uninterrupted. Compare that to DELETE WHERE created_at < '2026-06-01' on a monolithic table: massive WAL, heap bloat, and a subsequent VACUUM that can run for hours at scale.

---

When hash partitioning makes sense

Hash shines in one scenario: you have a firehose of writes and your query patterns are almost exclusively WHERE user_id = ? lookups with no time dimension. Think: user preference stores or real-time session state. Even then, you pay a steep cross-partition join cost any time analytics queries enter the picture.

A practical hybrid: partition by Range on created_at, then sub-partition the hot current-month partition by Hash on user_id. This gives write distribution on the hot path while preserving time-based pruning for historical queries.

---

Partition pruning gotchas

Two patterns silently disable pruning and are common in mobile backend ORMs:

-- BAD: function wrapping defeats pruning
WHERE date_trunc('month', created_at) = '2026-09-01'

-- GOOD: direct comparison preserves pruning
WHERE created_at >= '2026-09-01' AND created_at < '2026-10-01'

Always verify with EXPLAIN (ANALYZE, BUFFERS) and check that Partitions removed appears in the output. If it doesn’t, the planner is scanning everything.

---

Three things worth doing before you need them

1. Default to Range partitioning on your primary timestamp column. Monthly or weekly granularity fits most mobile workloads — daily creates partition management overhead, quarterly degrades pruning precision.

2. Build the attach/detach retention cycle from day one. Wiring this up after a table has grown to billions of rows is miserable. A scheduled job that pre-creates next month’s partition and detaches the oldest on the first of each month takes an hour to build and saves days of pain later.

3. Benchmark your specific query patterns before choosing Hash or a hybrid strategy. Run EXPLAIN ANALYZE on your five most frequent queries against a partitioned staging table populated with production-scale data. Pruning behavior depends entirely on how your application passes predicates — and ORMs frequently surprise you.