PostgreSQL Partition Pruning Internals: Static Planning vs. Run-Time Execution Pruning in Multi-Tenant Databases

The Illusion of Partitioning: Why Tables Still Scan Everything

Declarative table partitioning is celebrated as PostgreSQL’s silver bullet for multi-terabyte data scaling. In multi-tenant SaaS platforms or high-velocity event architectures, engineering teams segment tables by tenant_id (list partitioning) or created_at (range partitioning), anticipating that queries will automatically scan only the relevant partition slice.

In production, however, a distressing scenario frequently emerges: an operational query filtering on the partition key runs in hundreds of milliseconds instead of sub-5ms. Upon inspecting the query plan, the executor is scanning every single child partition in the entire hierarchy. Partition pruning has completely failed. Understanding why this occurs requires examining the internal mechanics of static plan-time pruning versus dynamic run-time pruning.

1. Static vs. Dynamic Partition Pruning

PostgreSQL executes partition pruning across two distinct phases in the query lifecycle:

• Static (Plan-Time) Pruning: Occurs during query optimization before execution. If the WHERE clause contains constant literals (e.g., WHERE created_at >= '2026-10-01'), the planner evaluates partition constraint bounds and completely strips non-matching child tables from the query tree.
• Dynamic (Run-Time) Pruning: Occurs when the query uses parameterized values ($1 in prepared statements or Django ORM parameterized queries) or subqueries (e.g., WHERE tenant_id = (SELECT current_tenant_id())). The planner cannot know the value during planning, so it leaves all partitions in the plan and relies on the executor to dynamically prune them upon parameter evaluation.

2. The Three Cardinal Sins that Break Pruning

A. Volatile Function Evaluations in Predicates

If your query filters with a volatile function such as NOW() or clock_timestamp() rather than a stable timestamp, PostgreSQL cannot guarantee that the value remains constant across the scan, disabling plan-time pruning:

-- Breaks static pruning if function volatility is unmanaged
SELECT * FROM audit_logs WHERE created_at >= (NOW() - INTERVAL '7 days');

-- Fix: Explicitly cast or pass stable parameters from the application layer

B. Implicit Type Casting and Operator Mismatches

If your partition key is a timestamptz (timestamp with time zone) and your application passes a string interpreted as timestamp (without time zone), PostgreSQL inserts an implicit conversion function timezone(). Because the function wrapping the column is not immutable, pruning is completely abandoned.

C. Cartesian Partition Joins vs. Partition-Wise Joins

When joining two partitioned tables (e.g., orders and order_items, both partitioned by created_at), PostgreSQL defaults to joining the full partitioned set across all combinations. To force Postgres to join partition N directly to partition N without cross-checking other tables, you must explicitly enable partition-wise joins in postgresql.conf:

# postgresql.conf
enable_partition_pruning = on
enable_partitionwise_join = on
enable_partitionwise_aggregate = on

3. Verifying Pruning with EXPLAIN (ANALYZE)

Always inspect the execution plan using parameterized tests to ensure dynamic pruning is active:

PREPARE tenant_lookup (uuid) AS
SELECT id, title, amount 
FROM invoices 
WHERE tenant_id = $1 AND status = 'unpaid';

EXPLAIN (ANALYZE, BUFFERS) EXECUTE tenant_lookup('c1b48b9f-6e82-4217-a0e2-6bb5c6d37682');

A properly tuned partitioned database will output:

Append  (cost=0.28..8.30 rows=1 width=72) (actual time=0.042..0.045 rows=1 loops=1)
  Subplans Removed: 47
  ->  Index Scan using invoices_c1b4_status_idx on invoices_tenant_c1b4 (actual time=0.038..0.041 rows=1 loops=1)
Planning Time: 0.182 ms
Execution Time: 0.071 ms

Notice the critical indicator: Subplans Removed: 47. Forty-seven child tables were completely bypassed during executor startup, delivering instantaneous sub-millisecond execution even on multi-billion-row multi-tenant platforms.