PostgreSQL partitioning internals for time-series SaaS: partition pruning, constraint exclusion, and the index strategy that keeps your queries under 10ms at 100M rows

Meta description: Deep dive into PostgreSQL declarative range partitioning for append-heavy SaaS workloads — partition pruning, constraint exclusion pitfalls, per-partition index tradeoffs, and lock-free rolling retention patterns.

Tags: backend microservices saas api architecture

---

TL;DR

Declarative range partitioning in PostgreSQL is the right tool for time-series SaaS tables at 100M+ rows — but only if you understand how the planner performs partition pruning, when constraint exclusion silently fails, and how per-partition index overhead compounds over time. Done correctly, you can maintain sub-10ms query latency and detach old partitions without locking your write path.

---

The problem every SaaS metrics table eventually faces

In my experience building production systems that ingest continuous event or metric streams, the inflection point is predictable: somewhere between 50M and 150M rows, a table that once responded in milliseconds starts returning queries in seconds. The culprit is almost never missing indexes — it is the planner scanning partitions it should never touch.

What most teams get wrong: they treat partitioning as a storage concern. It is actually a query planning concern first.

---

How declarative range partitioning works internally

PostgreSQL’s declarative partitioning (introduced in v10, matured in v11–v14) attaches child tables to a parent via partition constraints. At plan time, the planner evaluates these constraints against your WHERE clause predicates to eliminate irrelevant partitions — a process called partition pruning.

CREATE TABLE events (
  id          BIGSERIAL,
  tenant_id   UUID        NOT NULL,
  occurred_at TIMESTAMPTZ NOT NULL,
  payload     JSONB
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2025_10
  PARTITION OF events
  FOR VALUES FROM ('2025-10-01') TO ('2025-11-01');

With enable_partition_pruning = on (default in PG 11+), a query like:

SELECT * FROM events
WHERE occurred_at >= '2025-10-15'
  AND occurred_at <  '2025-10-16'
  AND tenant_id = $1;

should touch exactly one partition. On a 100M-row table partitioned monthly, you reduce the planner’s scan candidate set from ~36 partitions (3 years) to 1 — a 97% reduction before a single index lookup.

---

When constraint exclusion silently fails

This is where teams lose hours to mysterious slow queries. Partition pruning fails when:

Scenario	Why pruning breaks
WHERE date_trunc('month', occurred_at) = '2025-10-01'	Non-immutable expression; planner can’t infer bounds
WHERE occurred_at = $1 with parameter not yet bound	Runtime pruning requires PG 12+ and plan_cache_mode
Partition key cast implicitly (TIMESTAMP vs TIMESTAMPTZ)	Type mismatch prevents constraint match
WHERE tenant_id = $1 only (no time predicate)	Partition key is occurred_at; full scan is correct behavior

The third case is particularly insidious in multi-timezone SaaS applications. Store everything as TIMESTAMPTZ, partition on TIMESTAMPTZ, and query with explicit casts. A silent type coercion costs you the entire pruning benefit.

---

The index overhead tradeoff per partition

The architecture decision most teams defer too long: every CREATE INDEX on the parent creates a corresponding index on every child partition. For a table with 36 monthly partitions and three indexes each, you are managing 108 index structures.

Write amplification on append-heavy workloads follows a simple formula:

Write amplification = N_indexes × partition_count × row_size_factor

Keep it to two indexes per partition on high-ingest tables.

1. Primary lookup: (tenant_id, occurred_at) — covers the dominant query shape
2. Partial index on hot partitions only: CREATE INDEX ON events_2025_10 (tenant_id) WHERE occurred_at > now() - interval '7 days'

Manual partial indexes on individual partitions — not the parent — let you optimize the current month without paying the storage cost across historical partitions.

---

The attach/detach pattern for lock-free rolling retention

Naive DELETE FROM events WHERE occurred_at < $cutoff acquires row-level locks, generates WAL at full row cost, and blocks your write path under load. Don’t do it.

The correct pattern:

-- 1. Detach the oldest partition (non-blocking)
ALTER TABLE events DETACH PARTITION events_2023_01 CONCURRENTLY;

-- 2. Drop or archive the now-standalone table
DROP TABLE events_2023_01;
-- or: pg_dump it to cold storage first

DETACH PARTITION ... CONCURRENTLY (PG 14+) acquires only a SHARE UPDATE EXCLUSIVE lock, which does not block reads or writes on the parent. Your ingest pipeline sees zero interruption. A bulk DELETE on a 5M-row monthly partition, by contrast, can hold locks for 30–90 seconds under concurrent write pressure. That is the difference between a background operation and an incident.

Pre-create the next month’s partition in your maintenance job, and the planner always has a valid destination for incoming rows:

ALTER TABLE events ATTACH PARTITION events_2026_11
  FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

---

What to actually do

1. Audit your partition key types before you partition. A TIMESTAMPTZ vs TIMESTAMP mismatch silently disables pruning. Fix it at schema design time, not after 100M rows exist.

2. Use EXPLAIN (ANALYZE, BUFFERS) to verify pruning, not just plan shape. Look for Partitions removed: in the output. If it is absent or lower than expected, you have a constraint exclusion failure — not an index problem.

3. If you are on PG 14+, there is no justification for bulk deletes on time-series tables. Use DETACH PARTITION ... CONCURRENTLY for all retention jobs. The lock profile difference is not marginal — it is the difference between a maintenance window and a transparent background operation.