Rarely, the database can get into a state where it is so slow it appears broken (I have seen 3 instances of this issue in the last 4 years)
How can you tell you are impacted?
It is quite easy to tell the difference between database is under load, or database is under-provisioned to a broken state.
Everything suddenly got extremely slow
A rebuild does not fix the problem
You see multiple postgres threads consuming 100% CPU when you run
To ensure 100% you are impacted by this, stop the app and run a search:
./launcher restart app ./launcher enter app sv stop unicorn % rails c > Search.new('testing').execute > Search.new('testing').execute > Search.new('testing').execute
On a functioning DB a search will take under a second. On a DB that is gone into mode expect this to take more than 20 seconds
How to repair
The consistent way we were able to repair this was by dumping the database to disk and restoring:
./launcher enter app sv stop unicorn cd /shared sudo -u postgres pg_dump discourse > db.dump sudo -u postgres dropdb discourse sudo -u postgres createdb discourse sudo -u postgres psql discourse < db.dump
It is very unlikely you are experiencing this issue, we saw it about 3 times in 4 years. However, if you just ran a GIANT import I would strongly recommend following this so you start from a proper clean slate.