PostgreSQL bulk INSERT throughput collapses on partitioned table after stats collector restart

Environment

PostgreSQL 14 primary on dedicated server (32 cores, 256GB RAM) hosting a monthly-partitioned table with daily partitions; bulk import jobs insert ~10M rows per hour into today's partition via COPY and INSERT FROM staging. Stats collector (pg_statistic) process restarted during maintenance last night.

Symptoms

Bulk imports that normally complete in ~30 minutes now take 3–4× longer, with CPU usage low, WAL write rate normal and I/O not saturated. Query planner for subsequent index maintenance shows sequential scans where index scans were expected, and autovacuum triggered more frequently.

Observations

EXPLAIN ANALYZE of a key SELECT used during import shows planner choosing a generic plan with higher estimated cost. pg_statistic shows low or zero n_distinct and histogram entries for several columns in the partition. pg_stat_activity shows autovacuum workers running ANALYZE on new partitions repeatedly. The stats collector was restarted during maintenance and its PID changed; last_analyze timestamps for partitions are older than expected and autoanalyze appears triggered now. VACUUM VERBOSE indicates low page visibility map usage.

Already attempted

Manually ran ANALYZE on the affected partition and re-ran the bulk import; import throughput returned to expected levels for that partition. Repeating the test for newly created partitions showed the problem recurred until ANALYZE is run. Checked autovacuum settings; autovacuum_vacuum_scale_factor and autovacuum_analyze_scale_factor set to defaults. Did not change any planner-related configuration parameters.

Question

Why did restarting the stats collector lead to dramatically slower bulk INSERTs on a partitioned table, and what safe configuration or process changes will avoid this regression without requiring manual ANALYZE after each maintenance?

Post timeline

COMMUNITY RESPONSES

Verified community troubleshooting

0 replies

No answers yet..

Showing 0 of 0 community responses

AI DIAGNOSTICS

Incident analysis

Updated 1w ago• Trouble ID

Most likely causes

Partition-level pg_statistic entries missing or stale after stats collector restart, causing poor cardinality estimates 95%

Autovacuum/analyze thresholds too high for high-volume partition loads so ANALYZE not run promptly on new partitions 45%

Import pattern (COPY + concurrent DDL or partition creation) suppresses automatic ANALYZE triggers until threshold reached 25%

Summary

The planner relies on pg_statistic data populated by ANALYZE to choose efficient plans. Restarting the stats collector alone should not destroy stats, but if stats were lost or partition-level statistics were not present for new partitions, the planner fell back to generic or default estimates leading to suboptimal plans and slower imports. Manual ANALYZE immediately restored correct stats and performance. Autovacuum may not be aggressive enough to populate stats quickly under heavy data inflow, particularly when new partitions are created and filled rapidly.

Suggested Fixes

Diagnostic step 1

Automate running ANALYZE on new partitions immediately after creation or as part of the partition-creation job to ensure stats exist before heavy data load.

Diagnostic step 2

Lower autovacuum_analyze_scale_factor or add targeted autovacuum/analyze settings for the partitioned table to trigger ANALYZE sooner on large inserts; test in staging for overhead tradeoffs.

Diagnostic step 3

If maintenance requires restarting stats collector or PostgreSQL, schedule a post-maintenance ANALYZE run for affected schemas and partitions as standard operating procedure.