升级至PostgreSQL 10.3后CPU占用率大幅升高问题排查

Hey there, sorry you’re dealing with this unexpected CPU jump after your RDS PostgreSQL upgrade—major version bumps often bring subtle parameter changes that can throw workloads off, especially with managed services like RDS that tweak defaults for their environment. Based on common differences between PostgreSQL 9.6 and 10.3 (and RDS’s typical defaults), here are the top parameters to investigate:

Key Suspect Parameters #

• max_parallel_workers_per_gather: This is the most likely culprit. In PostgreSQL 9.6, parallel queries were disabled by default (this parameter set to 0), but PostgreSQL 10 turns it on with a default of 4 on RDS. If your workload has complex SELECTs, the database will now split their execution across multiple worker processes, which can send CPU usage skyrocketing. Check your query plans (using EXPLAIN ANALYZE) to see if parallel scans/joins are now being used.
• random_page_cost: RDS often adjusts this for SSD-backed instances. If the default dropped from 4 (the PostgreSQL standard) to 2 in 10.3, the query planner will favor index scans over sequential scans. While index scans are faster, they’re usually more CPU-intensive—this could be driving higher usage if your workload shifted to more index-heavy plans.
• autovacuum settings: PostgreSQL 10 improved autovacuum behavior, and RDS might have cranked up defaults like autovacuum_max_workers or shortened autovacuum_naptime. Right after an upgrade, there’s a lot of table and index churn, so more aggressive autovacuum activity can eat into CPU resources. Check CloudWatch’s AutovacuumCPUUtilization metric to confirm if this is a factor.
• work_mem: If RDS increased the default work_mem (e.g., from 4MB in 9.6 to 6MB+ in 10.3), queries doing sorting or hashing operations might now use more memory. While this speeds up those operations, it can also lead to more intensive in-memory processing that uses more CPU—especially if multiple queries are hitting this limit at once.
• effective_cache_size: A higher default value here can lead the query planner to pick more memory-heavy plans (like nested loops instead of hash joins). If the planner assumes more cache is available than actually exists, these plans might end up being less efficient and using more CPU than expected.

Quick Validation Steps #

1. Run EXPLAIN ANALYZE on your top CPU-consuming queries to spot parallel execution or plan changes.
2. Use the RDS console’s parameter group comparison tool to diff your old 9.6 group and new 10.3 group—look for any other parameter shifts that might impact CPU.
3. Check CloudWatch metrics to correlate CPU spikes with specific activities (like query execution or autovacuum runs).

Hope this helps you track down the issue! Let me know if you find a parameter that’s clearly driving the spike.

内容的提问来源于stack exchange，提问作者Diego Plentz