---
title: "PostgreSQL Partitioning for Mobile Backends: Range, Hash, or List?"
published: true
description: "Compare Range, Hash, and List partitioning strategies for mobile event streams and session data — with pruning mechanics, query benchmarks, and a zero-downtime retention pattern."
tags: postgresql, architecture, mobile, performance
canonical_url: https://mvpfactory.co/blog/postgresql-partitioning-mobile-backends
---
## What we are building
By the end of this tutorial you will understand which PostgreSQL partition strategy fits your mobile backend, wire up a Range-partitioned event table from scratch, and implement the attach/detach retention cycle that keeps large tables from becoming an operational nightmare. No migrations on a billion-row monolith required — if you start right.
## Prerequisites
- PostgreSQL 14+ (DETACH CONCURRENTLY requires it)
- A mobile backend storing time-series data — events, sessions, impressions, errors
- Familiarity with basic DDL and `EXPLAIN ANALYZE`
---
## The problem most mobile teams hit too late
Let me show you a pattern I see in every project that didn't plan for data volume early.
A single active user generates 50–200 events per session. At 500K DAU that's 25–100 million rows per day. Teams notice something is wrong 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.
The instinct is to add an index on `created_at`. That helps, but it does not eliminate the cost of a monolithic heap. Partitioning does.
---
## Choosing your strategy
Here is the minimal comparison you need before writing any DDL:
| Strategy | Pruning trigger | Retention eviction | Best fit |
|---|---|---|---|
| **Range (time)** | `WHERE created_at BETWEEN` | `DETACH PARTITION` — O(1) | Event streams, analytics |
| **Hash** | `WHERE user_id = ?` | Per-partition deletion | Pure key-value lookups |
| **List** | `WHERE region = 'APAC'` | Manual per-value | Narrow regional splits |
The critical difference: a Range-partitioned table with monthly partitions and `WHERE created_at >= NOW() - INTERVAL '30 days'` touches **1–2 partitions**. The same query on a Hash-partitioned table by `user_id` touches **all partitions** — the time predicate cannot prune by hash bucket.
For mobile event data, **Range wins almost every time**.
---
## Step 1 — Create the partitioned table
sql
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 your query carries a `created_at` predicate, the planner reads the partition catalog and eliminates non-matching child tables before execution begins. This is structural elimination, not index-assisted filtering.
---
## Step 2 — Implement rolling retention with zero downtime
Here is the gotcha that will save you hours: do not `DELETE` old rows from a monolithic table at scale. The WAL volume and subsequent VACUUM will ruin your weekend.
sql
-- 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 all other partitions continue uninterrupted. Wire this into a scheduled job that pre-creates next month's partition and detaches the oldest on the first of each month. It takes an hour to build and saves days of pain later.
---
## When Hash partitioning makes sense
Hash shines in one specific scenario: a firehose of writes with query patterns that are almost exclusively `WHERE user_id = ?` — no time dimension. Think real-time session state or user preference stores.
A practical hybrid: partition by Range on `created_at`, then sub-partition the hot current-month partition by Hash on `user_id`. You get write distribution on the hot path while preserving time-based pruning for historical queries.
---
## Gotchas
The docs do not mention this prominently enough: **function wrapping silently disables pruning**. This is the most common ORM-generated footgun I see.
sql
-- BAD: function wrapping defeats pruning entirely
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 confirm `Partitions removed` appears in the output. If it does not, the planner is scanning everything and you are getting no benefit from the partition structure.
Monthly or weekly granularity fits most mobile workloads. Daily creates management overhead; quarterly degrades pruning precision.
---
## Conclusion
Default to Range partitioning on your primary timestamp column and build the attach/detach retention cycle from day one. Wiring this up after a table has grown to billions of rows is genuinely miserable.
Before choosing Hash or a hybrid, benchmark your five most frequent queries against a partitioned staging table populated with production-scale data — ORMs frequently pass predicates in ways that surprise you.
**Resources:**
- [PostgreSQL Table Partitioning docs](https://www.postgresql.org/docs/current/ddl-partitioning.html)
- [DETACH CONCURRENTLY (PG 14 release notes)](https://www.postgresql.org/docs/14/release-14.html)
- [Using EXPLAIN](https://www.postgresql.org/docs/current/using-explain.html)
Top comments (0)