Measure partition-count costs, skew, hot partitions, autovacuum/index burden, and planner memory so partition granularity is derived from workload evidence rather than folklore.
Too Many Partitions, Planner Overhead, Skew, Hot Partitions, and Maintenance Strategy
Measure partition-count costs, skew, hot partitions, autovacuum/index burden, and planner memory so partition granularity is derived from workload evidence rather than folklore.
Learning outcomes
Partitioning shifts work rather than eliminating it. Every leaf is a relation with catalogs, statistics, indexes, autovacuum state, locks, and planner bookkeeping. PostgreSQL 18 improves planning efficiency for queries that access many partitions, but “more partitions” is still not free. This lesson builds two equivalent histories with daily versus monthly granularity and measures the costs instead of adopting “one partition per day” as doctrine.
Generate a controlled many-partition hierarchy and a coarser comparison hierarchy.
Measure planning and execution separately with EXPLAIN ANALYZE TIMING OFF.
Inspect skew, hot-leaf updates, dead tuples, index count, and per-partition statistics.
Connect partitionwise plan settings to memory/node multiplication.
Derive a partition-granularity decision record from retention, pruning, planning, and maintenance evidence.
1. Build 90 daily partitions and three monthly partitions
DROP TABLE IF EXISTS app.ch16_many_daily CASCADE;CREATE TABLE app.ch16_many_daily ( event_id bigint NOT NULL, occurred_on date NOT NULL, customer_id bigint NOT NULL, payload text NOT NULL) PARTITION BY RANGE (occurred_on);DO $$DECLARE d date; part_name text;BEGIN FOR d IN SELECT generate_series(DATE '2026-06-01', DATE '2026-08-29', INTERVAL '1 day')::date LOOP part_name := format('ch16_many_daily_%s', to_char(d,'YYYYMMDD')); EXECUTE format( 'CREATE TABLE app.%I PARTITION OF app.ch16_many_daily FOR VALUES FROM (%L) TO (%L)', part_name, d, d + 1 ); END LOOP;END$$;INSERT INTO app.ch16_many_dailySELECT 2000000 + g, DATE '2026-06-01' + (g % 90), 7000 + (g % 1000), repeat('d',30)FROM generate_series(1,90000) AS g;ANALYZE app.ch16_many_daily;DROP TABLE IF EXISTS app.ch16_three_months CASCADE;CREATE TABLE app.ch16_three_months ( event_id bigint NOT NULL, occurred_on date NOT NULL, customer_id bigint NOT NULL, payload text NOT NULL) PARTITION BY RANGE (occurred_on);CREATE TABLE app.ch16_three_months_jun PARTITION OF app.ch16_three_monthsFOR VALUES FROM ('2026-06-01') TO ('2026-07-01');CREATE TABLE app.ch16_three_months_jul PARTITION OF app.ch16_three_monthsFOR VALUES FROM ('2026-07-01') TO ('2026-08-01');CREATE TABLE app.ch16_three_months_aug PARTITION OF app.ch16_three_monthsFOR VALUES FROM ('2026-08-01') TO ('2026-08-30');INSERT INTO app.ch16_three_monthsSELECT * FROM app.ch16_many_daily;ANALYZE app.ch16_three_months;2. Count the management surface
SELECT 'daily' AS design, count(*) FILTER (WHERE isleaf) AS leaf_partitions, count(*) AS tree_relationsFROM pg_partition_tree('app.ch16_many_daily'::regclass)UNION ALLSELECT 'monthly', count(*) FILTER (WHERE isleaf), count(*)FROM pg_partition_tree('app.ch16_three_months'::regclass);The daily design has 90 leaves versus three. That means more catalog rows, statistics objects, potential indexes, autovacuum targets, lock entries, and DDL operations. Whether those costs are worthwhile depends on what daily granularity buys the workload.
3. Measure planning separately from execution
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT count(*)FROM app.ch16_many_dailyWHERE occurred_on = DATE '2026-08-15';EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT count(*)FROM app.ch16_three_monthsWHERE occurred_on = DATE '2026-08-15';The daily hierarchy can prune to a smaller physical leaf, while the monthly hierarchy prunes to August but still filters within that leaf. Record both Planning Time and Execution Time. On this small lab the absolute numbers are machine-dependent and may not favor either design; the experiment teaches which cost each granularity changes.
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT customer_id, count(*)FROM app.ch16_many_dailyWHERE occurred_on >= DATE '2026-06-15' AND occurred_on < DATE '2026-08-15'GROUP BY customer_id;EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT customer_id, count(*)FROM app.ch16_three_monthsWHERE occurred_on >= DATE '2026-06-15' AND occurred_on < DATE '2026-08-15'GROUP BY customer_id;A broad analytical query may make a fine-grained hierarchy pay planning/executor-node overhead without much pruning benefit.
4. Index multiplication is write and maintenance multiplication
CREATE INDEX ch16_many_daily_customer_idxON app.ch16_many_daily (customer_id);CREATE INDEX ch16_three_months_customer_idxON app.ch16_three_months (customer_id);SELECT CASE WHEN tablename LIKE 'ch16_many_daily_%' THEN 'daily' WHEN tablename LIKE 'ch16_three_months_%' THEN 'monthly' END AS design, count(*) AS physical_indexesFROM pg_indexesWHERE schemaname = 'app' AND ( tablename LIKE 'ch16_many_daily_%' OR tablename LIKE 'ch16_three_months_%' )GROUP BY designORDER BY design;One parent index becomes one physical child index per leaf. This expands CREATE/REINDEX/VACUUM/catalog work and write amplification. A partitioning scheme that requires many indexes on every tiny leaf can cost more than it saves.
5. Observe skew and the hot partition
Uniform generated data is convenient for planner comparisons but unrealistic for operations. Add a burst of recent activity to create a hot August 29 daily leaf.
INSERT INTO app.ch16_many_dailySELECT 3000000 + g, DATE '2026-08-29', 9000 + (g % 50), repeat('h',40)FROM generate_series(1,20000) AS g;UPDATE app.ch16_many_dailySET payload = payload || 'u'WHERE occurred_on = DATE '2026-08-29' AND event_id >= 3000001;ANALYZE app.ch16_many_daily;SELECT s.relname, s.n_live_tup, s.n_dead_tup, s.n_tup_ins, s.n_tup_upd, pg_size_pretty(pg_total_relation_size(s.relid)) AS total_sizeFROM pg_stat_user_tables AS sWHERE s.relid IN ( SELECT relid FROM pg_partition_tree('app.ch16_many_daily'::regclass) WHERE isleaf)ORDER BY pg_total_relation_size(s.relid) DESCLIMIT 10;Cumulative statistics can lag and are estimates. They identify a maintenance pattern: one leaf can dominate inserts/updates/dead tuples while dozens of cold leaves barely change. Tune autovacuum/index strategy at the hot leaf where evidence supports it rather than copying one setting to all partitions.
6. Too many partitions can multiply planner and memory work
PostgreSQL 18 improves planning efficiency for many-partition
queries, but its documentation still warns that excessive
partition counts can increase planning CPU/memory. Partitionwise
joins and aggregates can further multiply plan nodes whose
memory is individually limited by work_mem.
BEGIN;SET LOCAL enable_partitionwise_aggregate = on;EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON, SETTINGS)SELECT occurred_on, customer_id, count(*)FROM app.ch16_many_dailyGROUP BY occurred_on, customer_id;ROLLBACK;Count aggregate/hash/sort nodes in the plan and consider concurrent sessions. A setting that is safe for three partitions can create much larger memory concurrency with hundreds.
7. Wrong rule: “one partition per day because pruning is faster”
Daily partitions are not automatically better. If retention operates monthly, queries usually scan weeks/months, and each leaf carries several indexes, daily granularity can create dozens of extra relations and indexes without improving the dominant workload.
Build the decision from four dimensions:
| Evidence | Question |
|---|---|
| Retention/lifecycle | What is the smallest unit we truly detach/archive/drop? |
| Pruning selectivity | How many leaves do top queries normally need? |
| Hotspot/write pattern | Does one leaf become a contention/autovacuum hotspot? |
| Management overhead | How many physical indexes, stats objects, DDL jobs, locks, and plan nodes result? |
8. Build a partition-granularity decision log
SELECT now() AS observed_at, current_setting('server_version') AS server_version, current_setting('enable_partition_pruning') AS pruning, current_setting('enable_partitionwise_join') AS pwise_join, current_setting('enable_partitionwise_aggregate') AS pwise_aggregate, current_setting('work_mem') AS work_mem;SELECT 'daily' AS design, count(*) FILTER (WHERE isleaf) AS leavesFROM pg_partition_tree('app.ch16_many_daily'::regclass)UNION ALLSELECT 'monthly', count(*) FILTER (WHERE isleaf)FROM pg_partition_tree('app.ch16_three_months'::regclass);Store the EXPLAIN outputs, retention requirement, leaf/index counts, peak write distribution, and operational incidents with the schema decision. Revisit the partition granularity as workload and retention policy change.
9. Cleanup the Chapter 16 experiment
DROP TABLE IF EXISTS app.ch16_work_order_event CASCADE;DROP TABLE IF EXISTS app.ch16_work_orders CASCADE;DROP FUNCTION IF EXISTS app.ch16_audit_work_order();DROP TABLE IF EXISTS app.ch16_partition_audit CASCADE;DROP TABLE IF EXISTS app.ch16_many_daily CASCADE;DROP TABLE IF EXISTS app.ch16_three_months CASCADE;DROP TABLE IF EXISTS app.ch16_customer_bucket CASCADE;DROP TABLE IF EXISTS app.ch16_region_queue CASCADE;DROP TABLE IF EXISTS app.ch16_work_orders_unpartitioned CASCADE;Check your understanding
- Why should planning time be recorded separately from execution time?
- How does one parent index affect a many-leaf hierarchy?
- Why can a single hot partition need different maintenance attention from cold leaves?
- Why are partitionwise planning settings a memory concern?
- What evidence should determine daily versus monthly partitioning?
Review the answers
Fine-grained partitioning can change planning overhead independently of runtime pruning. A parent index creates physical indexes on leaves, multiplying write/maintenance objects. Hot leaves accumulate churn while cold leaves do not. Partitionwise plans can multiply work_mem-bounded nodes. Granularity should follow retention units, query pruning, hotspot behavior, and operational/planner overhead.
Authoritative references
Partitioning behavior is planner-, lock-, constraint-, and version-sensitive. These PostgreSQL 18 primary sources define the mechanisms used in this lesson.