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.
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
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!
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.