Do PostgreSQL subscriber servers lock when synchronizing to an update to the publisher server?

Question

If I have 3 databases

• Db1: only for writes, publisher
  • Db2: only for reads, subscriber to Db1
  • Db3: only for reads, subscriber to Db1

Just to clarify that "only for reads" means that my application won't try to modify that database. The inverse applies to "only for writes". I'm not talking about a feature of the database itself. Maybe I will use permissions to accomplish that, but regardless.

Some questions which I couldn't locate answers to by skimming through the official documentation for logical replication:

• In a scenario where there's a new write to Db1, will Db2 and Db3 both lock while synchronizing to the changes, or are they going to allow reads to be made in parallel with the synchronization?

• If the subscriber server is executing a read by the time a new change to Db1 is published, will the available change be the next operation to be executed as soon as they arrive at the subscriber, regardless of how many reads were already waiting to be executed (if any)?

My concern is with consistency in a load-balanced cluster of (only for reads) PostgreSQL servers which are replicas of Db1. They all should be in sync with Db1, not allowing any new reads to them before synchronizing to new changes published by Db1. If I can't do that with logical replication, then what are the alternatives, if any?

Answer 1

In a scenario where there's a new write to Db1, will Db2 and Db3 both lock reads while updating?

Writers do not block readers at the macroscopic level. Using logical replication does not alter this.

Will there be any waits for finishing the current reads before synchronizing with Db1 - even on a multi-core server?

In a sense. If a reader has a page locked for reading, the writer will not be able to write to the page. However, such locks are generally kept extremely short (submicrosecond).

If there are waits, will the synchronization be the next operation to be executed regardless of how many reads were already in the queue of operations of that server?

What is the "queue of operations"? I do not believe PostgreSQL has one.

They all should be in sync, not allowing any new reads to them before synchronizing to a new change to Db1.

Even if all queries are done on the same server, you can't control if a read-only query executed a nanosecond before something else committed, or a nanosecond after. Even with serializable isolation level, you are only guaranteed that some serial ordering of the transactions exist. You are not told what that order is, and aren't allowed to look at something like clock_timestamp() to figure it out. If a "read-only" query needs to make sure that something stands still, it needs to lock it.

If a read-only transaction uses its view of the data to make a decision, then that decision needs to be reflected back into the database, meaning the transaction is not really read-only.

If I can't do that with logical replication, then what are the alternatives, if any?

Want something different. You can't do this even within the same server.