Date: Fri, 24 Oct 2025 15:18:44 +0200
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: depesz@depesz.com
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL General <pgsql-general@lists.postgresql.org>
Subject: Re: Why is this query touching 4gb of buffers?
Message-ID: <aPt8tD3dSiVCOzHu@depesz.com>
Reply-To: depesz@depesz.com
References: <aPtcnI2BZkeHjRvF@depesz.com>
 <aPtgp4xidEB_QA_H@depesz.com>
 <1754333.1761310446@sss.pgh.pa.us>
 <aPt3bONUNkgloyrf@depesz.com>
 <1755687.1761310871@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <1755687.1761310871@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/aPt8tD3dSiVCOzHu%40depesz.com>
Precedence: bulk

On Fri, Oct 24, 2025 at 09:01:11AM -0400, Tom Lane wrote:
> hubert depesz lubaczewski <depesz@depesz.com> writes:
> > On Fri, Oct 24, 2025 at 08:54:06AM -0400, Tom Lane wrote:
> >> The first execution probably had to set hint bits on a whole lot
> >> of recently-deleted rows.
> 
> > But why it doesn't happen/help on secondary?
> 
> IIRC, secondaries aren't authorized to update hint bits for
> themselves, they have to wait for the primary to do it and then
> propagate the new data.  There might also be some question of
> what the oldest open transaction is ...

OK. So tested this idea on yet another "setup".

We have single primary, and two streaming replicas. Before test I ran:
select now() - min(xact_start), now() - pg_last_xact_replay_timestamp() from pg_stat_activity

on both replicas, and got:

    ?column?     │    ?column?
═════════════════╪═════════════════
 00:00:00.003007 │ 00:00:00.003673
(1 row)

and

 ?column? │     ?column?
══════════╪══════════════════
 00:00:00 │ -00:00:00.006129
(1 row)

Then I ran the problematic query on replica 1 (the first one), and got
numbers:

Buffers: shared hit=21107
Execution Time: 18.621 ms

Subsequent runs on the replica showed the same buffers usage, and
similar time.

Then I ran this query twice on primary, and noticed improvement:
Buffers: shared hit=569 read=20927 dirtied=498
Execution Time: 2596.283 ms

and on 2nd run:

Buffers: shared hit=391
Execution Time: 2.015 ms

Awesome. Well, mostly.

Now, I waited ~ 1 minute, checked replication lag, and oldest transactions
replies, where all lags were < 1s, and longest transaction across
primary/secondary1/secondary2 was ~ 4s.

Then I re-ran the query on secondary 1 and got:
Buffers: shared hit=21107
Execution Time: 18.403 ms

Interestingly, on one "setup" running vacuum analyze of the table helped
execution on secondary, but on this one, it doesn't.

Best regards,

depesz