Physical replication high shared buffers hits

From: Talal Majali <t(dot)majali93(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Physical replication high shared buffers hits
Date: 2022-04-26 18:51:54
Message-ID: CA+9sjg-ObCMEdTjhGcOORFKuCzgAAeyn4Z4T6TMNQrGgLW=TMg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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:
https://explain.depesz.com/s/TuMD
on replica:
https://explain.depesz.com/s/auJp

Note the Buffers shared hit difference between the two queries,
again when we run this query on the replica after vacuuming analyze the
table:
https://explain.depesz.com/s/vodZ

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

Browse pgsql-general by date

  From Date Subject
Next Message JORGE MALDONADO 2022-04-26 19:15:17 Backing up a DB excluding certain tables
Previous Message Tom Lane 2022-04-26 18:43:13 Re: Backing up a DB excluding certain tables