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.