Nice article - this kind of retrospective is always really interesting. One little thing that caught my eye -
It turns out that postgres has an ‘auto vacuum’ setting that when it is enabled will cause the database to go on some introspective tour every hour which was the cause of the enormous periodical loads. Disabling auto vacuum and running it once nightly when the system is very quiet anyway solved that problem.
Often vacuum problems can also be fixed by running auto vacuum more often too - this means it has less to do per run, so should be able to keep up a little more easily. Loads of stuff on vacuum on the postgres wiki: https://wiki.postgresql.org/wiki/VacuumHeadaches#Perverse_Fe...
Yeah, this was one of the eyebrow raisers in the article. The other was a load average of 0.6 being "high".
For what it's worth, autovacuum can be enabled/disabled on a per table basis, too. Some tables need frequent vacuuming, others less frequent, and others none at all. If you manually VACUUM a table, don't forget to also ANALYZE it!
It turns out that postgres has an ‘auto vacuum’ setting that when it is enabled will cause the database to go on some introspective tour every hour which was the cause of the enormous periodical loads. Disabling auto vacuum and running it once nightly when the system is very quiet anyway solved that problem.
Often vacuum problems can also be fixed by running auto vacuum more often too - this means it has less to do per run, so should be able to keep up a little more easily. Loads of stuff on vacuum on the postgres wiki: https://wiki.postgresql.org/wiki/VacuumHeadaches#Perverse_Fe...