Hello,
We have been struggling with weird issue with postgresql physical replication, and i wonder if someone had this problem before, we have two servers (primary, replica) same hardware spec connected directly with switch running postgresql 13.4, with replication lag less than 1 second, we use physical replication, and one of the tables is heavily loaded with writes and locks (up to one second), and reads, when we start reading from this table from the replica we found a weird issue, same query on the primary is taking few milliseconds, on the replica it grows until we do vacuum analyze and when we did explain analyze buffers, what we noticed that there are huge difference in shared buffers hit for this query on the replica vs the primary, please find the details below:
on primary:
on replica:
Note the Buffers shared hit difference between the two queries,
again when we run this query on the replica after vacuuming analyze the table:
note that the buffers shared hit grows on the replica for this query or any query related to this table until we do vacuum analyze.
Regards