1

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:

  • COMMIT;
  • 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?

1 Answer 1

1

Since you are using synchronous_commit = remote_apply, the problem is most likely replication conflicts. Such conflicts delay the replay of the replicated transaction log, which in turn will delay the commit of transactions on the primary.

The only way you can use synchronous_commit = remote_apply together with queries on the standby and still get reasonably short commit times is to set max_standby_streaming_delay = 0 on the standby, but then you have to expect and repeat canceled queries on the standby. Something has got to give.

See my article for a more thorough treatment of the subject of replication conflicts.

4
  • Thank you for your response. Currently, I am trying to set max_standby_streaming_delay = 0 in development and see its impacts. Do I need to set this parameter on the primary node as well or only on the standby node?
    – Rio Fajar
    Feb 24, 2023 at 10:32
  • Only on the standby. The parameter has no effect on the primary. Feb 24, 2023 at 11:00
  • Oh, okay. So, what does it mean to handle canceled queries on the standby? Does it mean canceling read queries that are currently running on the replica database when there are WAL (Write-Ahead Log) transactions on the same table as the read queries running on the replica and need to be applied to the replica db? I want to make sure of the possible consequences that would occur if I change max_standby_streaming_delay to 0 on production
    – Rio Fajar
    Mar 1, 2023 at 1:56
  • I meant "repeat" when I said "handle". You are mostly right. Did you read my article? It explains most of these questions, and I wrote it so that I don't have to repeat the same stuff over and over. Mar 1, 2023 at 6:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.