Disabling hot_standby_feedback on db4 (a secondary) led to an increase in CPU usage and dead tuples on the primary
Yesterday I enabled pgbouncer, which involved restarting PostgreSQL. The restart lead to hot_standby_feedback
being reset since it was enabled in a non-persistent manner (to prevent Chef from messing things up). Since then we noticed an increase in CPU usage and dead tuples on the primary:
Re-enabling hot_standby_feedback on db4 caused CPU usage to drop back to normal, and vacuuming to kick in again. We also noticed an increase in throughput (web transactions overall):
We can also see a reduction in SQL timings for Grape API calls:
My hypothesis is that because only 1 out of 2 nodes was reporting standby feedback, PostgreSQL decided to hang on to dead tuples. This in turn can negatively impact query performance, probably leading to higher CPU usage (as queries have to work their way through more (dead) data). I'll see if I can find some info on the matter.