I've been struggling to eliminate replication lag on a Postgres 9.6.6 instance on Amazon RDS. I believe the lag is caused by early cleanup conflicts from vacuums on the master, because I can reliably resolve it by killing long-running queries on the standby. I most recently saw ten hours of lag on Saturday and addressed it this way.
The standby is running with hot_standby_feedback = on max_standby_streaming_delay = 5min max_standby_archive_delay = 30s I am not using replication slots on the primary due to reported negative interactions with pg_repack on large tables. My rationale for the first two settings is that hot_standby_feedback should address my issues almost all the time, but that max_standby_streaming_delay would sometimes be necessary as a fallback, for instance in cases of a transient connection loss between the standby and primary. I believe these settings are mostly working, because lag is less frequent than it was when I configured them. My questions are, * Am I overlooking anything in my configuration? * What would explain lag caused by query conflicts given the max_standby_streaming_delay setting? Shouldn't those queries be getting killed? * Is there any particular diagnostic info I should be collecting on the next occurrence, to help me figure out the cause? Note that as I'm on RDS, I don't have direct access to the datadir -- just psql. Thanks for any advice! Wyatt
