How Many Partitions Is Too Many?

There is no error message for too many partitions. There is a planning time that creeps up, a nightly backup that takes an hour longer each quarter, and an autovacuum queue that never quite empties. This guide measures the four costs that scale with partition count, finds where your workload’s ceiling actually is, and lists the ways down. It extends Scaling Limits & Cost Tradeoffs inside Database Partitioning Fundamentals & Architecture.

Prerequisites

Step 1 — Count what you actually have

Start with the inventory, at both levels:

What "1,095 partitions" actually costs the catalog A table with 1,095 leaf partitions and three indexes on each contributes 1,095 heap relations plus 3,285 index relations, for 4,380 catalog entries, 4,380 files on disk, 4,380 lock candidates and 4,380 objects for every backup to stat. The partition count alone understates the cost by a factor of four. heap relations (partitions) index relations (3 per partition) total catalog entries 1,095 3,285 4,380 — files on disk, lock candidates, and objects every backup must stat Autovacuum, backup duration and the lock table all scale with the bottom bar rather than the top one, which is why a table that looks like "about a thousand partitions" behaves like four thousand relations in every operational dimension. Dropping one redundant index per partition removes 1,095 relations — often a bigger win than any setting change. 
SELECT parent.relname                                   AS table_name,
       count(*)                                         AS children,
       count(*) FILTER (WHERE c.relkind = 'p')          AS intermediate,
       pg_size_pretty(sum(pg_total_relation_size(c.oid))) AS total_size
FROM   pg_inherits i
JOIN   pg_class c      ON c.oid = i.inhrelid
JOIN   pg_class parent ON parent.oid = i.inhparent
GROUP  BY parent.relname
ORDER  BY children DESC;
 table_name |  children | intermediate | total_size
------------+-----------+--------------+------------
 metrics    |      1095 |            0 | 4102 GB
 events     |        96 |           12 | 1188 GB
 audit_log  |        84 |            0 | 212 GB

-- database-wide relation count, which is what the catalog and lock table care about
SELECT count(*) FROM pg_class WHERE relkind IN ('r','p','i');

Operational note: Count indexes too. A table with 1,095 partitions and three indexes each is over 4,000 relations, and every one of them is a catalog row, a lock candidate and a file on disk.

DBA tip: Intermediate partitioned tables are cheap in storage and not in planning — they still appear in the plan tree. A two-level layout with twelve months and eight buckets is 96 leaves plus 12 intermediates plus one root.

Step 2 — Measure the four costs that scale with count

Each cost has a different curve and a different symptom:

-- 1. planning time on a pruned query
EXPLAIN (ANALYZE, COSTS OFF) SELECT count(*) FROM metrics
WHERE recorded_at >= '2026-08-01' AND recorded_at < '2026-08-02';
-- read "Planning Time" from the output

-- 2. lock pressure
SELECT count(*) AS locks_held,
       current_setting('max_locks_per_transaction')::int
       * (current_setting('max_connections')::int) AS approx_capacity
FROM   pg_locks;

-- 3. autovacuum backlog
SELECT count(*) FILTER (WHERE last_autovacuum IS NULL) AS never_vacuumed,
       count(*)                                        AS partitions
FROM   pg_stat_user_tables WHERE relname LIKE 'metrics_%';

-- 4. backup duration is read from the backup tool's own history
Four costs, four different curves, one shared cause Planning time is flat to about a thousand partitions and then rises sharply. Lock usage rises linearly with the number of partitions a statement touches. Autovacuum cycle time rises linearly because each partition is vacuumed independently. Backup duration rises linearly with file count regardless of data volume. Only the planning curve has a knee; the other three simply accumulate. 1005001,0005,00010,000 leaf partitions on one table relative cost the planning knee — the only sharp one planning time lock slots per statement autovacuum cycle backup duration The three linear curves are the ones that hurt first in practice, because they degrade operations rather than queries.

Operational note: Backup duration scales with file count as much as with bytes. A table split into 1,095 partitions with three indexes each is 4,380 files to stat, read and record — noticeable even when the data has not grown.

SRE tip: Chart partition count on the same dashboard as p99 latency and backup duration. The correlation is obvious in hindsight and invisible without the count on the same axis.

Step 3 — Find your workload’s actual ceiling

The published rules of thumb are a starting point; your ceiling depends on how well your queries prune:

-- reproduce the curve on staging: create N partitions, measure, repeat
DO $$
DECLARE d date := '2020-01-01';
BEGIN
  WHILE d < '2026-08-01' LOOP
    EXECUTE format('CREATE TABLE metrics_%s PARTITION OF metrics
                      FOR VALUES FROM (%L) TO (%L)',
                   to_char(d, 'YYYY_MM_DD'), d, d + 1);
    d := d + 1;
  END LOOP;
END $$;
Symptom Threshold in practice What it means
Planning time exceeds execution time on a pruned query often 800–1,500 leaves the planner is now the workload
pg_locks above 50% of capacity during maintenance varies with configuration a fleet-wide statement can exhaust the lock table
Partitions never autovacuumed any count above zero autovacuum cannot keep up with the number of relations
Backup window overlaps the business day site-specific file count, not data volume, is usually the driver

Operational note: A workload where every query prunes to one partition tolerates far more partitions than one with frequent unpruned scans. Measure with your own query mix rather than a synthetic point query.

DBA tip: Run the staging experiment with empty partitions. Pruning and planning depend on bounds and counts, not on data, so the experiment is cheap and still accurate for these curves.

Step 4 — Bring a runaway count down

Four levers, in order of how much disruption they cause:

-- 1. shorten retention: the cheapest lever, and usually a policy conversation
UPDATE partition_policy SET retention = '13 months' WHERE table_name = 'metrics';

-- 2. coarsen granularity going forward: mixed bounds are legal
CREATE TABLE metrics_2026_09 PARTITION OF metrics
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');   -- monthly from here on

-- 3. merge historical partitions into coarser ones, one at a time
BEGIN;
  CREATE TABLE metrics_2025_q1 (LIKE metrics INCLUDING ALL);
  INSERT INTO metrics_2025_q1 SELECT * FROM metrics_2025_01_01;  -- repeat per day
  ALTER TABLE metrics DETACH PARTITION metrics_2025_01_01;
  -- … attach the quarter, drop the days
COMMIT;

-- 4. archive and drop: the count falls with the data
What each lever does to a 1,095-partition table Starting from 1,095 daily partitions over three years: shortening retention to thirteen months leaves 395, coarsening to monthly going forward reduces future growth to twelve a year, merging history into quarters leaves about sixty, and doing all three leaves twenty-five. The effort rises from a policy change to a data migration across the four options. today — daily, 3 years retention → 13 months + monthly going forward + history merged to quarters 1,095 395 — one policy row ~230 falling to 25 over a year — no data moved 25 — a per-partition migration, done once The first two levers move no data and can ship the same week. The third is a real migration and should only be reached for when history itself is the problem — for example when a restore of one old day takes longer than the whole quarter would. Note that sub-partitioning appears nowhere on this list: it increases the leaf count and solves a different problem.

Operational note: Mixed granularity is fully supported — daily bounds for history and monthly for new data coexist as long as bounds abut exactly. This is the lever that costs nothing and is used least.

SRE tip: After reducing the count, re-run ANALYZE on the parent. Its inherited statistics are stale after partitions come and go, and stale statistics can leave the planner making the choices it made at the old count.

Verification

Re-measure the same four costs and confirm the direction of travel:

SELECT round(mean_plan_time::numeric, 2) AS plan_ms,
       round(mean_exec_time::numeric, 2) AS exec_ms,
       calls
FROM   pg_stat_statements
WHERE  query ILIKE '%from metrics where recorded_at%'
ORDER  BY calls DESC LIMIT 3;
 plan_ms | exec_ms |  calls
---------+---------+---------
    1.42 |    3.08 | 2841022

Planning back under execution time, lock usage well below capacity, and every partition showing a recent autovacuum. Then record the new count as the baseline, so the next drift is measured from a known point.

Failure mode table

Failure mode Root cause SRE mitigation
Latency creeps up over quarters with no code change partition count grew, planning time with it chart partition count next to p99; set a soft ceiling and treat crossing it as a scheduled task
Maintenance statement fails with out of shared memory a statement touched more partitions than the lock table could hold raise max_locks_per_transaction; reduce the count; avoid fleet-wide statements inside one transaction
Some partitions are never autovacuumed autovacuum workers cannot cycle through the relation count within their naptime raise autovacuum_max_workers and lower autovacuum_naptime, and reduce the count — the setting change alone only defers the problem

FAQ

Is there a hard limit on partition count?

No hard limit, several soft ones. Planning time, lock table capacity, autovacuum worker throughput, backup duration and catalog size all degrade gradually. That gradualness is the danger: nothing fails, everything gets a little worse, and the cause is invisible unless you are watching the count alongside the symptoms.

What is a reasonable ceiling to design for?

Roughly a thousand leaf partitions per table and a few thousand per database is a comfortable design envelope on current PostgreSQL versions for workloads that prune well. Beyond that, expect to spend effort on planner settings and lock configuration. Well above it, the honest answer is usually coarser granularity or a second level rather than tuning.

Does sub-partitioning reduce the count?

No — it multiplies leaves. What it reduces is the number of children at any one level, which helps some operations and not planning. Sub-partition to solve write contention or per-value policy, not to manage a large partition count; for that, coarser bounds and shorter retention are the effective levers.

Back to Scaling Limits And Cost Tradeoffs