PostgreSQL Partitioning for Mobile Backend Time-Series Data

---
title: "PostgreSQL Partitioning for Mobile Backend Time-Series Data: Range, Hash, and the Query Planner Behavior That Breaks Your Indexes"
published: true
description: "Range vs. hash partitioning for mobile event logs and telemetry — partition pruning mechanics, foreign key gotchas, pg_partman pitfalls, and why naive partitioning can slow your most common queries."
tags: postgresql, mobile, architecture, api
canonical_url: https://mvpfactory.co/blog/postgresql-partitioning-mobile-time-series-backends
---

## What we will cover

By the end of this workshop, you will understand how PostgreSQL declarative partitioning behaves in production mobile backends — not just how to set it up, but how the query planner actually prunes partitions, where foreign keys silently break, and what `pg_partman` does at 2 AM that you never want to discover the hard way.

A single mid-sized app generating session, crash, and interaction events can push **50–200 million rows per day** into a single table. Without partitioning, `VACUUM`, index bloat, and query latency degrade predictably past the 500M-row mark. Let me show you a pattern I use in every project.

---

## Prerequisites

- PostgreSQL 12 or later (PG 15 recommended)
- A mobile backend ingesting time-series event data
- Basic familiarity with `EXPLAIN ANALYZE`
- `pg_partman` installed if you want automated maintenance

---

## Step 1: Choose your partition strategy based on reads, not writes

Here is the minimal setup to get this working. The table:

| Strategy | Partition Pruning | Unique Constraints | Write Throughput |
|---|---|---|---|
| Range (`event_time`) | Excellent | Per-partition | Good |
| Hash (`device_id`) | Poor for time queries | Not globally enforced | Excellent |
| List (`event_type`) | Good for type filters | Supported | Moderate |

For a mobile backend where 80% of queries are `WHERE event_time BETWEEN $1 AND $2`, range partitioning by month or week lets the planner scan **1–2 partitions** instead of all 36. Hash partitioning distributes writes evenly but forces a full partition scan for every time-range query. **Partition design must be query-driven.** Model your top-5 queries before choosing a strategy.

---

## Step 2: Understand how partition pruning actually works

PostgreSQL prunes partitions at plan time for static values and at execution time for bind parameters (PG 11+). This distinction matters for prepared statements from mobile API servers.

sql
-- Partition pruning works at execution time here (PG 11+)
PREPARE get_events(timestamptz, timestamptz) AS
SELECT * FROM mobile_events
WHERE event_time >= $1 AND event_time < $2;

Always verify with:

sql
EXPLAIN (ANALYZE, BUFFERS) EXECUTE get_events('2025-01-01', '2025-02-01');

Check that `Partitions selected` matches expectations.

---

## Step 3: Configure pg_partman for automated maintenance

`pg_partman` is solid for production, but run maintenance with a pre-creation buffer:

sql
-- Run maintenance with a pre-creation buffer (create 4 weeks ahead)
SELECT partman.run_maintenance(
p_parent_table := 'public.mobile_events',
p_analyze := false -- skip auto-analyze on large tables
);

Schedule this during low-traffic hours. Pre-create at least **4 future partitions**. The docs do not emphasize this, but `p_analyze := false` is non-negotiable — letting it auto-analyze a 200M-row partition during peak traffic is a bad day.

---

## Gotchas

**Implicit casts break pruning silently.** If your partition key is `timestamptz` but you pass a `text` literal, the planner will not prune. Match types exactly.

**Function wrapping disables pruning entirely.** `WHERE date_trunc('day', event_time) = '2025-01-01'` kills partition elimination. Always filter on the raw column.

**Foreign keys don't work the way you think.** Partitioned tables cannot be the target of foreign keys from non-partitioned tables. If `crash_reports` references `mobile_events(event_id)`, you must either reference a specific child partition, drop the FK and enforce integrity at the application layer, or restructure `crash_reports` as a partitioned table too. This catches teams every time.

**Unique constraints must include the partition key.** A globally unique `event_id` UUID is not enforceable across partitions without additional tooling — a separate ID registry table or application-level deduplication.

**Naive device-scoped queries hit every partition.** Here is the gotcha that will save you hours:

sql
-- This hits ALL partitions if device_id is not in the partition key
SELECT COUNT(*) FROM mobile_events WHERE device_id = $1;

With 24 monthly partitions, query time increases linearly. Fix it with composite partitioning (range + hash subpartition on `device_id`) or a separate device-aggregated summary table maintained by a background worker.

**pg_partman lock contention at midnight.** `run_maintenance()` acquires a brief lock on the parent table when creating new partitions. Run it too close to midnight while mobile clients are hammering inserts and you will see lock spikes. Schedule with margin.

---

## Conclusion

Three things I would tell any team starting this migration:

**Partition by time first**, then profile your device-scoped queries. If device-level lookups are frequent, add hash subpartitioning or maintain a summary table — don't fight the planner.

**Audit foreign keys and unique constraints** before migrating. Referential integrity requires architectural changes, not just a `CREATE TABLE ... PARTITION BY` swap.

**Configure pg_partman to pre-create at least 4 future partitions** and schedule maintenance during off-peak hours. Lock contention at partition-creation time is avoidable with a 10-minute configuration change.

Declarative partitioning in PostgreSQL 12–15 is mature and production-ready — but only when the strategy matches your actual read patterns.