Обсуждение: Replication lag
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
Sent: Friday, May 23, 2025 8:13:10 AM
To: pgsql-admin <pgsql-admin@postgresql.org>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Replication lag
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.
On Παρασκευή, 23 Μαΐου 2025 10:13:10 Π.Μ. EEST Wasim Devale wrote:
> 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
On Fri, 2025-05-23 at 12:43 +0530, Wasim Devale wrote: > 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