PostgreSQL Partitioning at 100 Million Rows: Why Declarative Pruning Sometimes Fails

When our event tracking table reached 120 million rows, analytical queries filtering by event date began grinding our primary database to a halt. Autovacuum ran around the clock, and indexes were growing larger than available RAM.

We partitioned the table using PostgreSQL’s declarative range partitioning into 365 daily partitions. In local testing, EXPLAIN confirmed that a query filtering for yesterday’s data scanned exactly one partition. We deployed to production expecting dramatic speedups.

Instead, production query latency jumped from 25ms to 950ms. EXPLAIN ANALYZE on live queries revealed that PostgreSQL was taking locks on and scanning all 365 partitions for every single query.

Static pruning vs. Run-time pruning

PostgreSQL executes partition pruning at two distinct phases: during query planning (static pruning) and during query execution (run-time pruning).

Static pruning occurs when the query’s WHERE clause contains literal constants (e.g., WHERE event_date = ‘2026-09-11’). The optimizer evaluates the partition boundary constraints and excludes non-matching partitions before generating the execution plan.

Run-time pruning occurs when the WHERE clause contains parameters, variables, or subqueries (e.g., WHERE event_date = $1 or WHERE event_date = CURRENT_DATE). The planner must build a plan that includes all potential partitions, and the executor prunes them only after parameter values are bound.

The prepared statement generic plan trap

The disaster in our production environment was caused by PostgreSQL’s plan caching mechanism for prepared statements.

When an application executes a prepared statement through an ORM or connection pooler, PostgreSQL generates custom plans for the first five executions, substituting the actual parameter values. On the sixth execution, the optimizer evaluates whether a generic plan (independent of parameter values) would be cost-effective.

If the optimizer selects a generic plan, static partition pruning is disabled because the planner does not know what parameter value will be passed in future executions. If the query structure prevents run-time pruning, PostgreSQL is forced to scan every single partition in the hierarchy.

-- Forcing custom plans to preserve partition pruning on prepared statements
SET plan_cache_mode = 'force_custom_plan';

-- Inspecting whether partitions are properly pruned in EXPLAIN
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM events WHERE event_date = $1;

Lock table explosion and shared memory

Every partition in PostgreSQL is an independent table in the system catalog (pg_class). When a query accesses a partitioned table, PostgreSQL must acquire an AccessShareLock on the parent table and every child partition that might be involved.

If you have 1,000 partitions and 200 concurrent queries, PostgreSQL must acquire hundreds of thousands of locks simultaneously in the shared memory lock table. If the lock table runs out of slots, queries fail with ‘out of shared memory: You might need to increase max_locks_per_transaction’.

Rule of thumb: Do not over-partition. Partitioning by day over five years creates 1,825 partitions. Partitioning by month or quarter maintains manageable partition counts (20 to 60) where catalog lookups and lock acquisitions remain negligible.

Index maintenance and global index realities

PostgreSQL does not support global indexes across partitioned tables. Every index is local to its partition. If your queries do not include the partition key in the WHERE clause, PostgreSQL must execute index scans across every single partition.

Ensure that every primary key and unique constraint on a partitioned table includes the partition routing column, and audit foreign key relationships before partitioning large production tables.

The practical standard

High-level database architecture is not about drawn boxes on an infrastructure diagram. It is about how the engine manages shared resources under concurrency — memory, latches, write-ahead logs, and lock tables. When things break at 2 AM, the fix is rarely adding another replica or throwing more CPU at the host. The fix is understanding the underlying resource bottleneck, measuring the exact wait event, and applying the architectural constraint that makes the system predictable.