Tuning Planner Settings for Large Partition Counts

Past a few hundred children per table, the planner rather than the storage becomes the thing to tune. This guide covers the settings that matter at that scale — locks, partition-wise operations, memory multiplication and statistics — and where the honest answer is to reduce partition count instead. It extends Partition Pruning & Query Planning inside Database Partitioning Fundamentals & Architecture.

Prerequisites

Step 1 — Measure planning as a share of response time

Before changing anything, establish which statements are planner-bound:

SELECT round(mean_plan_time::numeric, 2) AS plan_ms,
       round(mean_exec_time::numeric, 2) AS exec_ms,
       round(100 * mean_plan_time / nullif(mean_plan_time + mean_exec_time, 0), 1) AS plan_pct,
       calls,
       left(query, 60) AS query
FROM   pg_stat_statements
WHERE  calls > 1000
ORDER  BY mean_plan_time * calls DESC
LIMIT  10;
 plan_ms | exec_ms | plan_pct |  calls  | query
---------+---------+----------+---------+--------------------------------------------
   14.21 |    3.09 |     82.1 | 4120391 | SELECT id, payload FROM events WHERE tenant
    9.84 |   41.02 |     19.4 |  188204 | SELECT count(*) FROM events WHERE occurred_

Anything above roughly 30% planning share is a candidate. The first row above is the classic shape: a cheap query planned expensively, four million times a day.

Operational note: track_planning adds measurable overhead on very high-throughput systems. Turn it on for a diagnostic window rather than leaving it on permanently if your call rate is in the tens of thousands per second.

DBA tip: Multiply by calls before ranking. A 60 ms planning time on a nightly report is irrelevant; 14 ms on a statement called four million times is fifteen CPU-hours a day.

Step 2 — Size the lock table for the widest statement

Every partition a statement opens takes a relation lock, and locks live in a fixed-size shared table:

lock table slots ≈ max_locks_per_transaction × (max_connections + max_prepared_transactions)

# postgresql.conf — requires a restart
max_locks_per_transaction = 256    # default 64
max_connections           = 300

A statement touching 2,000 partitions inside a transaction that also touches a dozen other tables needs more than 2,000 slots for itself. With the default of 64 the server will eventually fail with out of shared memory and a hint pointing at this setting — usually during a maintenance job rather than during normal traffic, which makes it hard to attribute.

Lock slots consumed per statement against configured capacity With max_locks_per_transaction at 64 and 300 connections, the shared lock table holds about 19,200 slots. A pruned query needs three slots, an unpruned query on 500 partitions needs 501, and a maintenance transaction touching 2,000 partitions needs 2,001. Forty concurrent unpruned queries exhaust the table, and the failure appears as an out of shared memory error unrelated to the statement that caused it. Lock slots per statement (log scale) — capacity 19,200 pruned point query report over 500 partitions maintenance over 2,000 40 concurrent reports 3 slots 501 slots 2,001 slots 20,040 slots — exceeds capacity, cluster-wide failure The failing statement is rarely the guilty one: the first transaction to request a slot after the table fills is the one that errors, which is why this looks like a random outage until the lock arithmetic is done.

Operational note: Changing max_locks_per_transaction needs a restart, and it allocates shared memory proportional to max_connections. Reducing max_connections by adding a pooler often makes room for a much larger per-transaction allowance.

SRE tip: Alert on pg_locks row count as a fraction of capacity. Crossing 60% is the warning that a schema change or a growth trend is heading for this failure.

Step 3 — Enable partition-wise operations for aligned workloads

When two tables are partitioned identically, the planner can join them partition by partition rather than appending everything first:

Partition-wise join: twelve small joins instead of one large one Without the setting, both tables are appended in full and a single hash join runs over the combined rows, requiring a hash table that spills to disk. With it enabled, twelve pairwise joins run, one per matching partition, each with a hash table small enough to stay in memory. Planning takes longer because both paths are considered. enable_partitionwise_join = off = on Append orders (12)Append items (12) one Hash Join — 41M rows, spills o+i · 08o+i · 09o+i · 10… joinjoinjoinjoin execution 38 s · work_mem exceeded · temp files written execution 6 s · 12 hash tables, each in memory planning 4 ms planning 11 ms — both paths costed The setting only helps when both tables share an identical bound set. Enable it per role for reporting rather than cluster-wide, so transactional queries do not pay the extra planning for a path they can never use. 
-- per role, for reporting workloads
ALTER ROLE reporting SET enable_partitionwise_join = on;
ALTER ROLE reporting SET enable_partitionwise_aggregate = on;

The benefit is large and the cost is planning time, because the planner must consider both the partition-wise and the conventional path. The break-even point is roughly where the join is the dominant cost and the partition count is moderate — dozens, not thousands.

Setting What it enables Cost Turn on when
enable_partitionwise_join pairwise joins of matching partitions more paths to consider at plan time both tables share a partition scheme and joins are large
enable_partitionwise_aggregate aggregate per partition, then combine same GROUP BY includes the partition key
parallel_workers per partition parallel scan within each child worker slots and memory wide scans over few large partitions

Operational note: Partition-wise joins require the two tables to have identical bound sets, not merely the same interval. A table partitioned monthly since 2024 will not join partition-wise with one partitioned monthly since 2025 until the bound lists match.

DBA tip: enable_partitionwise_aggregate is the bigger win of the two for time-series reporting, because a GROUP BY month over a monthly-partitioned table becomes an aggregate per partition with nothing to re-group.

Step 4 — Control memory multiplication

work_mem is a per-operation limit, and partition-wise plans create one operation per partition:

-- a partition-wise aggregate over 16 partitions can allocate 16 × work_mem
SHOW work_mem;              -- 64MB
-- worst case for one query: 1 GB, before parallel workers multiply it again
# a safer shape for partitioned analytics
work_mem = 16MB                      # modest global default
max_parallel_workers_per_gather = 4

and then raise it deliberately for the sessions that need it:

BEGIN;
SET LOCAL work_mem = '256MB';
-- the one heavy report
COMMIT;
How one query's memory multiplies on a partitioned table A single hash aggregate uses one work_mem allocation. Partition-wise aggregation over sixteen partitions uses sixteen. Adding four parallel workers per gather multiplies again, to sixty-four allocations. At 64 MB per allocation that is four gigabytes for one query, which is why a modest global work_mem with per-session escalation is the safer configuration. plain aggregate partition-wise over 16 + 4 parallel workers 1 × work_mem = 64 MB 16 × work_mem = 1.0 GB 64 × work_mem = 4.0 GB for a single query Ten concurrent reports at this shape request 40 GB. The machine has 64 GB, most of which is meant to be page cache, so the practical result is heavy swapping or the out-of-memory killer choosing a backend at random. Keep the global value small and raise it with SET LOCAL inside the transactions that genuinely need it.

Operational note: SET LOCAL scopes the change to the transaction, so a connection returned to the pool cannot leak a 256 MB setting to the next request. SET without LOCAL in a pooled application is a memory incident waiting for traffic.

SRE tip: Log queries whose temp file usage exceeds a threshold (log_temp_files = 10MB). Spilling to disk is the signal that work_mem is too small for a specific plan shape, and it identifies exactly which statements deserve an escalation.

Step 5 — Keep statistics fresh per partition

Autovacuum analyses partitions individually, and the parent’s statistics are only refreshed by an explicit ANALYZE on the parent. Stale parent statistics produce bad row estimates for queries that span partitions:

-- refresh the parent's inherited statistics after bulk loads or partition changes
ANALYZE events;

-- check how stale they are
SELECT relname, last_analyze, last_autoanalyze
FROM   pg_stat_user_tables
WHERE  relname LIKE 'events%'
ORDER  BY coalesce(last_analyze, last_autoanalyze) NULLS FIRST
LIMIT  10;

Operational note: Add ANALYZE on the parent to the partition-maintenance job, immediately after new partitions are created or old ones detached. Both operations change the parent’s inherited statistics and neither triggers autoanalyze on it.

DBA tip: For very wide tables, raise default_statistics_target on the partition key column only, rather than globally — better bound estimates are what the planner needs, and the extra sampling cost lands where it pays.

Verification

Re-measure the same statements after each change and compare planning share:

SELECT pg_stat_statements_reset();
-- let production run for an hour, then re-run the query from Step 1

Expected shape after tuning a planner-bound workload — planning time down, execution unchanged:

 plan_ms | exec_ms | plan_pct |  calls  | query
---------+---------+----------+---------+--------------------------------------------
    2.98 |    3.11 |     48.9 | 4120391 | SELECT id, payload FROM events WHERE tenant

If planning time has not moved, the cause is partition count rather than configuration — and the fix is structural.

Failure mode table

Failure mode Root cause SRE mitigation
out of shared memory during maintenance statements touching thousands of partitions exhaust the lock table sized by max_locks_per_transaction raise the setting and restart during a window; reduce max_connections with a pooler to afford a larger allowance
Backends killed by the OOM killer during reports partition-wise aggregation plus parallel workers multiplied work_mem beyond available RAM lower the global work_mem, escalate with SET LOCAL per transaction, cap max_parallel_workers_per_gather
Planning time unchanged after tuning the bottleneck is the number of children, not the settings coarsen granularity, add a sub-partition level, or shorten retention so the count falls

FAQ

Should I raise max_locks_per_transaction for a partitioned schema?

Usually yes. Every partition a statement opens takes a lock, and the shared lock table is sized by max_locks_per_transaction multiplied by max_connections. A query touching 2,000 partitions inside a transaction that also touches other tables can exhaust the default allocation and fail with out of shared memory. Raising it requires a restart, so size it for the worst statement you expect rather than the average.

Is enable_partitionwise_join safe to turn on globally?

It is safe but not free. Partition-wise joins let matching partitions join pairwise, which is dramatically faster for large aligned joins, and they increase planning time because the planner considers more paths. Enable it per role for reporting workloads rather than cluster-wide, so transactional queries with tight latency budgets do not pay for a feature they never use.

Why does work_mem behave differently on partitioned tables?

work_mem is a per-operation limit, and a partition-wise aggregate or a parallel append can run one sort or hash per partition simultaneously. Sixteen concurrent hash nodes at 64 MB is a gigabyte for a single query. Size work_mem against the number of concurrent operations a plan can create, not against the number of queries.

Back to Partition Pruning Query Planning