Subject: Replication lag

From: Wasim Devale <[email-masked]>
Date: Fri, 23 May 2025 12:43:10 +0530

Hello,

Reply wal and query execution on replica can coexists?

Golden gate in oracle has this feature that they can coexists but in
postgresql do we have any provision like this.

Please assist.

Thanks,
Wasim

---

From: David Okeamah <[email-masked]>
Date: Fri, 23 May 2025 08:08:33 +0000

Query Execution and Reply WAL Coexistence in PostgreSQL

 Wasim,

Thanks for your question.

Yes, PostgreSQL does support concurrent WAL replay and read query execution on replicas through its hot standby feature. By setting hot_standby = on, a replica can serve read-only queries while applying WAL files from the primary via streaming replication.

However, there are a few caveats:

  *   Read queries on the standby may be canceled if they conflict with recovery operations. This behavior can be tuned using parameters like max_standby_streaming_delay and hot_standby_feedback.
  *   Unlike Oracle GoldenGate, PostgreSQL’s native logical replication is more limited in terms of conflict resolution and cross-version replication, though tools like pglogical or Debezium can bridge those gaps for more complex use cases.

Best regards,

David Okeamah

DAVID OKEAMAH,DEVELOPER
________________________________
From: Wasim Devale <[email-masked]>
Sent: Friday, May 23, 2025 8:13:10 AM
To: pgsql-admin <[email-masked]>; Pgsql-admin <[email-masked]>
Subject: Replication lag

Hello,

Golden gate in oracle has this feature that they can coexists but in postgresql do we have any provision like this.

Please assist.

Thanks,
Wasim

---

From: Dionysios-Charalampos Vythoulkas <[email-masked]>
Date: Fri, 23 May 2025 11:31:19 +0300

On streaming replication yes, you can perform read-only queries.
On logical replication, as far as I know you can also run write queries, but you have to be 
careful to keep the data consistent.

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 23 May 2025 11:46:48 +0200

> Reply wal and query execution on replica can coexists?

Yes, you can have both.
But there is the possibility of replication conflicts, which can
either delay replay of the WAL or lead to cacneled queries on the
standby.

To see why this is unavoidable in some cases, consider the following
scenario:

- on the standby, there is a long-running query on table A
- on the primary, somebody executes "DROP TABLE A"

The change gets replicated to the standby, but it clearly cannot be
replayed while the query is still running.

Yours,
Laurenz Albe