Re: PostgreSQL 9.2 high replication lag

On Fri, 2021-08-20 at 01:33 +0000, Lucas wrote:
> After setting max_standby_streaming_delay to 120s it got a lot better.
> But the replication delay is still happening quite often, except this time goes up to 120s only.

That's exactly what this parameter should do.

If you don't want the delays, either reduce the value (and get more canceled queries)
or try to reduce the number of conflicts, for example by setting "hot_standby_feedback = on".

Note that you will never be able to completely get rid of replication colflicts;
for example, there are buffer pin conflicts or lock conflicts caused by autovacuum
truncation.

See this article for more:
https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/

If you want a standby that has no apply delays and no canceled queries is usually
not possible. Consider using two standby servers for these two purposes.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com