SoftwareDevs mvpfactory.io

Posted on Oct 29 Originally published at mvpfactory.io

PostgreSQL Partitioning Internals for Time-Series SaaS

#webdev #programming

---
title: "PostgreSQL Partitioning Internals for Time-Series SaaS: Partition Pruning, Constraint Exclusion, and the Index Strategy That Keeps Your Queries Under 10ms at 100M Rows"
published: true
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: postgresql, architecture, performance, api
canonical_url: https://mvpfactory.co/blog/postgresql-partitioning-internals-time-series-saas
---

What We Are Building

By the end of this workshop, you will understand how PostgreSQL's declarative range partitioning works at the planner level — not just how to create partitions, but how to verify the planner actually prunes them, when constraint exclusion silently fails, and how to detach old partitions without locking your write path.

This is the setup I use on append-heavy SaaS event tables hitting 100M+ rows.

Prerequisites

PostgreSQL 14+ (some features require PG 14 specifically)
A time-series table approaching 50M–150M rows, or planning for one
Familiarity with EXPLAIN (ANALYZE, BUFFERS) output

Step 1: Create Your Partitioned Table

Here is the minimal setup to get this working.

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 (the default in PG 11+), a well-formed query like this:

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

touches exactly one partition. On a table with 36 monthly partitions covering three years, the planner eliminates 35 of them before a single index lookup — a 97% reduction in scan candidates.

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

Step 2: Verify Pruning Is Actually Happening

Do not trust the plan shape alone. Run this:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE occurred_at >= '2025-10-15'
  AND occurred_at <  '2025-10-16'
  AND tenant_id = $1;

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.

Step 3: Apply the Right Index Strategy Per Partition

Let me show you a pattern I use in every project. Keep it to two indexes per partition on high-ingest tables:

Primary lookup: (tenant_id, occurred_at) — covers the dominant query shape
Partial index on hot partitions only:

CREATE INDEX ON events_2025_10 (tenant_id)
  WHERE occurred_at > now() - interval '7 days';

Create partial indexes on individual partitions — not the parent. This lets you optimize the current month without paying the storage cost across all historical partitions. Every index on the parent creates a corresponding index on every child. Three indexes across 36 partitions means 108 index structures to maintain. Write amplification is predictable:

Write amplification = N_indexes × partition_count × row_size_factor

Two indexes is the ceiling on high-ingest tables.

Step 4: Roll Retention Without Locking Your Write Path

Here is the gotcha that will save you hours. 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. On a 5M-row partition, that lock can hold for 30–90 seconds under concurrent write pressure. That is the difference between a background operation and an incident.

The correct pattern on PG 14+:

-- Non-blocking detach
ALTER TABLE events DETACH PARTITION events_2023_01 CONCURRENTLY;

-- Drop or archive
DROP TABLE events_2023_01;
-- or: pg_dump it to cold storage first

DETACH PARTITION ... CONCURRENTLY acquires only a SHARE UPDATE EXCLUSIVE lock — reads and writes on the parent are completely unaffected. Pre-create the next partition in your maintenance job so 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');

Gotchas

The docs do not mention this, but partition pruning fails silently in several predictable scenarios. Here are the ones that cost teams the most debugging time:

Scenario	Why pruning breaks
`WHERE date_trunc('month', occurred_at) = '2025-10-01'`	Non-immutable expression; planner cannot infer bounds
`WHERE occurred_at = $1` with parameter not yet bound	Runtime pruning requires PG 12+ and correct `plan_cache_mode`
Partition key type mismatch (`TIMESTAMP` vs `TIMESTAMPTZ`)	Silent coercion prevents constraint match
`WHERE tenant_id = $1` only, no time predicate	Full scan is correct — partition key is `occurred_at`

The type mismatch is particularly insidious in multi-timezone SaaS applications. Store everything as TIMESTAMPTZ, partition on TIMESTAMPTZ, and query with explicit casts. Fix this at schema design time — not after 100M rows exist.

Conclusion

Declarative range partitioning is the right tool for append-heavy SaaS tables at scale, but only if the planner can prune effectively. Audit your partition key types before you partition. Verify pruning with EXPLAIN (ANALYZE, BUFFERS). Use DETACH PARTITION ... CONCURRENTLY for all retention jobs on PG 14+. With that pattern in place, sub-10ms query latency at 100M+ rows is not an aspiration — it is the expected baseline.

Further reading: PostgreSQL Partitioning docs · Partition Pruning internals

DEV Community