Slow Sidekiq + Postmaster using 95%+ CPU (32 cores) after Postgresql Version Upgrade

[eboehnisch (Eric)]

Thank you, everyone here! I fixed the duplicate key, dropped the obsolete indexes, and then ran

REINDEX DATABASE discourse;
VACUUM VERBOSE ANALYZE;

successfully. CPU usage has returned back to normal. Phew.

Question: Why does a non-optimized database or a broken index lead to such high CPU usage by the postmaster? Just curious.

[sam (Sam Saffron)]

I think the broken index is a red herring, it is certainly not great, and should be fixed

The giant issue is that this move from 10-12 leaves the db with a terrible statistics story, this leads to bad performance .

Perf is bad cause the query optimiser chooses very bad plans for queries, cause the statistics it has about data in the tables is totally off

We will integrate the rebuilding of statistics via vacuum into our automated move

[eboehnisch (Eric)]

Thank you, @sam, for the explanations. That makes sense. I guess it’s a good idea to integrate the rebuilding into the automated move. Thank you, again, for you help!