I have 2 PostgreSQL 13 database, one acting as a master and the other as a slave, using streaming replication with synchronous_commit=remote_apply. This means that all data written to the primary database will wait until it is applied to the slave database. Everything was running normally until recently, when there was a delay in replication on the slave due to some queries very slow being executed on the slave. Some of these queries are:
- UPDATE column values in a table
- Delete row from a table by an ID
As a result, many queries are being blocked by transactions/queries waiting to be executed on the slave.
The question is: What could be causing the delay in replication on the slave and what is the solution?