I upgraded my RDS instance (db.t2.medium) from Postgres 9.5 to 9.6 couple of days back. Since the upgrade, the CPU usage was almost 100% all the time. This brought down my application. Presuming that the problem is with the 9.6 version, I did another upgrade to 10.6. It got better but still considerably higher than the CPU usage for the original 9.5 version.

I haven't changed the application code during the upgrade. How can I check the reason for the increased CPU usage and possibly fix it?

enter image description here

  • Don't presume, run a regular "ps -ef" and capture the output to be sure it's Postgres. Feb 6 '19 at 9:05
  • since it is an RDS instance, AWS would run only Postgres in the VM Feb 6 '19 at 9:10
  • Even so, Postgres consists of multiple processes. Feb 6 '19 at 14:41
  • Did you execute all steps as in postgresql.org/docs/9.6/pgupgrade.html ? Feb 6 '19 at 14:57
  • 3
    Did you run ANALYZE after the upgrade (or does RDS do that for you automatically)?
    – jjanes
    Feb 6 '19 at 15:04

You need to run an ANALYZE query on the whole database using the above command.

The issue is that the query plans postgres has generated are optimised for the previous version of postgres, when you do an RDS update it does not implicity regenerate these plans, this needs to be done manually (I'm sure there's a reason why AWS doesn't do this manually but I've really no idea why).

In my case I saw roughly a week of extremely high CPU usage, just as in your case, then after running ANALYZE my cpu dropped back to it's previous baseline. As you can see in the image below the upgrade (in my case from 9.4 - 9.5) was run on 11/27, the analyze query was run on 12/02.

(The VERBOSE is not strictly necessary but it's useful to be able to watch the progress of the command)

Postgres RDS CPU usage over the course of a week

  • 3
    I appreciate you. Mar 24 '20 at 11:55
  • 4
    'God bless you' This saved us millions!
    – Monku
    Jul 25 '20 at 5:20
  • 2
    I wish I could upvote more than once.
    – nil
    Dec 21 '20 at 4:44
  • Thank you so much!
    – max kaplan
    Feb 4 '21 at 15:33
  • came here because my materialized views were dead slow after upgrading from postgres 11
    – barnacle.m
    Oct 27 '21 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.