PostgreSQL partitioning for mobile time-series backends

Meta 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.

---

TL;DR

Declarative table partitioning in PostgreSQL works well for mobile backends ingesting high-volume telemetry and event logs — but only if you understand how the query planner actually behaves. Range partitioning on event_time is the right default. Hash partitioning looks appealing for write throughput but kills your most frequent time-range queries. Foreign keys don’t work the way you think on partitioned tables, and pg_partman has a maintenance window gotcha that will bite you at 2 AM.

---

The problem: mobile backends drown in time-series data

In my experience building production systems for mobile analytics backends, a single mid-sized app generating session, crash, and interaction events can push 50–200 million rows per day into a single PostgreSQL table. Without partitioning, VACUUM, index bloat, and query latency degrade predictably past the 500M-row mark.

Declarative table partitioning, introduced in PostgreSQL 10 and matured in PG 12–15, is the standard answer. But most teams get this wrong the same way: they partition for write throughput and forget to model their read access patterns first.

---

Range vs. hash: choose based on your queries, not your writes

Strategy	Best For	Partition Pruning	Unique Constraints	Write Throughput
Range (on event_time)	Time-windowed queries, data retention	Excellent — planner eliminates partitions	Supported per-partition	Good
Hash (on device_id)	Even write distribution	Poor for time queries	Not globally enforced	Excellent
List (on event_type)	Low-cardinality categories	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 will allow the planner to scan 1–2 partitions instead of all 36. Hash partitioning distributes writes evenly but forces a full partition scan for every time-range query.

---

How partition pruning actually works (and when it fails)

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

-- 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;

Pruning silently breaks in two common scenarios:

1. Implicit casts: If your partition key is timestamptz but you pass a text literal, the planner won’t prune.
2. Function wrapping: WHERE date_trunc('day', event_time) = '2025-01-01' disables pruning entirely. Always filter on the raw column.

Use EXPLAIN (ANALYZE, BUFFERS) and verify Partitions selected matches expectations.

---

Foreign keys and unique constraints: the silent breakage

This catches teams every time. Partitioned tables in PostgreSQL cannot be the target of foreign keys from non-partitioned tables. If your crash_reports table references mobile_events(event_id), you must either:

• Reference the specific child partition (loses flexibility)
• Drop the FK and enforce referential integrity at the application layer
• Restructure crash_reports as a partitioned table too

Unique constraints are equally constrained — they must include the partition key. A globally unique event_id UUID is not enforceable across partitions without additional tooling (e.g., a separate ID registry table or application-level deduplication).

---

pg_partman: powerful, but watch the maintenance window

pg_partman automates partition creation and retention, and it’s solid for production use. The gotcha: 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 the insert path, and you will see lock contention spikes.

-- 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 maintenance at low-traffic hours, pre-create at least 4 future partitions, and always set p_analyze := false — letting it auto-analyze a 200M-row partition during peak traffic is a bad day.

---

Why naive partitioning slows your most common queries

Let me walk you through the architecture failure I see repeatedly. A team partitions mobile_events by month on event_time. Their most common query is:

SELECT COUNT(*) FROM mobile_events WHERE device_id = $1;

Without device_id in the partition key, this query hits every partition. With 24 monthly partitions, query time increases linearly. The fix is either a composite partition strategy (range + subpartition by hash on device_id) or a separate device-aggregated summary table maintained by a background worker.

Partition design must be query-driven. Model your top-5 queries before choosing a strategy.

---

Conclusion

Three things I’d 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 separate summary table — don’t fight the planner.

Audit your foreign keys and unique constraints before migrating to partitioned tables. 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.

---

postgresql backend mobile architecture api