Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uZFfd-000qgC-LP for pgsql-performance@arkaria.postgresql.org; Tue, 08 Jul 2025 21:16:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uZFfb-00ByvC-Fd for pgsql-performance@arkaria.postgresql.org; Tue, 08 Jul 2025 21:16:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uZFfb-00Byv4-4K for pgsql-performance@lists.postgresql.org; Tue, 08 Jul 2025 21:16:07 +0000 Received: from mail-ed1-x52e.google.com ([2a00:1450:4864:20::52e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uZFfX-006Wom-2V for pgsql-performance@postgresql.org; Tue, 08 Jul 2025 21:16:07 +0000 Received: by mail-ed1-x52e.google.com with SMTP id 4fb4d7f45d1cf-60700a745e5so9563667a12.3 for ; Tue, 08 Jul 2025 14:16:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1752009362; x=1752614162; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=4ZRfIyCTgzgtC4c8TdgBOSRYVK9pmgyVcbcZxz1w9D4=; b=BptwaUSK1U/HWwU3FyrAObRH/q2GhwJ0u1Iyu8B28afhG3VbRasDq4GIe+qQDVK8OO 6cWXDOiZtgc5R/ll+028t/3dfs7pQpvqBOL0ySZMUfF49RTNQivifQsFSuhHU6kgarte +loYkZfFBRZQUfUV+mx+Adp9pFMLm7jbt1UvxQCKAsmBD2lq0Jy2DO+332bW2TvSDngi 7YI1sPAq54CO3hQW+ADSSLoKeoWc5/MrhxhGRH/LutcD1/j/uHUhUz5/JNQRPXPdWnGa iYnrbT7xzxzSGoC9o4h22BBqwr8iMO1T0XHn5fb40edyoTxMKFwHAVenjb2axSk1aUZj ak6A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1752009362; x=1752614162; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=4ZRfIyCTgzgtC4c8TdgBOSRYVK9pmgyVcbcZxz1w9D4=; b=S2F9Nf6KgqNZPsBejmd6qcyI95p5JryidZ+8C9k1/Vf3/HJ0S25sGfwCRSnF6vvYqR 9igu8Lyzfrz2IDUnjNqLeU/r6xVFlrqk+nOQduSMjNml/Wq5dn4LzwYafbIDKtSewKHu 0QWs/Askh7XP6W65BME0MQVNnifNS4HxfBZx/9Ri8tCQ9n9dINUGh9ms92ZE6MDkZD3D 1AbMEnglYIaPOTPvnp8Dohu54cEK684bfp+UhfQsqBV5sjqhK2KZCUmHwdLifgd7CfdJ 5Xgfs5AC/6SmpIDrXLSmEXFfJYf0eCEI9yV0PhQM9yDeE4FPkSkA+cGV2MNDuFuCuGTy Nlhg== X-Gm-Message-State: AOJu0YyhKBHfW8g+/DClokbSrTWjd6fRUCwjDiNuO5M6zb9ms0xoeFZv BfCW9dGESRJVGXt8VmnZ7mdg14td+umvZZ8nvphThfenN5kh6Xmm3rsSRZBe1ez/KUeFAQj2ZCo g2DyfGM5JpxAOaFPrIivcS0dkDNNToIICZA== X-Gm-Gg: ASbGnctdz4Pun9wt4fBZLjGD4JyCWZcKMIb76n1tw+sxZ0otZEhAEH3P9Sye0NxiBaS FJR/WIu+Jtdhvl2woqLju4zgJw0WVok3KBtIb4t5/19F2Nw04LyOSmbAXnKuN/9Egp46m7gxLrH 1FVE/3tTKDMLlAk8TOWO8nD9ZGS9FzUlWD1+jbeHRv2NFPPMj9klysOgN2+/xl7whsMy3ibaPQD 8AMrQ== X-Google-Smtp-Source: AGHT+IHbultMGiDMMrVT5vsNytDN+mn2KNmODXbUpFN6BmdC0tyBvVygGHvkbca3Shvui/V8OvMABdReGchN+xuaDtQ= X-Received: by 2002:a17:906:6a1d:b0:ae0:cf01:a9ad with SMTP id a640c23a62f3a-ae6cfc3ccecmr7044566b.40.1752009361749; Tue, 08 Jul 2025 14:16:01 -0700 (PDT) MIME-Version: 1.0 References: <202507081405.g55lnrkhma44@alvherre.pgsql> In-Reply-To: <202507081405.g55lnrkhma44@alvherre.pgsql> From: Dharin Shah Date: Tue, 8 Jul 2025 23:15:50 +0200 X-Gm-Features: Ac12FXzbdCxFpKBws0tFN4Tm9OW3_eTRC_4F5ac9JPgLK3Znd7XX6xPZE18k6d8 Message-ID: Subject: Re: WAL Replay Buffer Invalidation Conflicts During Page Truncation on Read Replicas To: =?UTF-8?Q?=C3=81lvaro_Herrera?= Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary="000000000000a67c42063971777d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a67c42063971777d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thanks Alvaro, I read the thread and actually disabled truncate on my table with a large toast table which mitigated the issue. Unsure what happens with the empty pages now, I guess they would be reused for new inserts. I would like to see if there are better opportunities to improve this truncation process. Perhaps identify why we need this arbitrary threshold to determine a full buffer scan? */** * * This is the size (in the number of blocks) above which we scan the* * * entire buffer pool to remove the buffers for all the pages of relation* * * being dropped. For the relations with size below this threshold, we find* * * the buffers by doing lookups in BufMapping table.* * */* *#define BUF_DROP_FULL_SCAN_THRESHOLD (uint64) (NBuffers / 32)* https://github.com/postgres/postgres/blob/e03c95287764158941d317972a3325657= 29b6af2/src/backend/storage/buffer/bufmgr.c#L91 As this can cause significant issues as we scale memory for shared buffers. (Very often the case with Aurora) Thanks, Dharin On Tue, Jul 8, 2025 at 4:05=E2=80=AFPM =C3=81lvaro Herrera wrote: > On 2025-Jul-08, Dharin Shah wrote: > > > *Problem Summary:* > > WAL replay of relation truncation operations on read replicas trigger= s > > buffer invalidation that requires AccessExclusive locks, blocking > > concurrent read queries for extended periods. > > Hmm, sounds like disabling truncate of the TOAST relation by vacuum > could help. We have configuration options for that -- one is per table > and was added in Postgres 12, changed with > ALTER TABLE ... SET (vacuum_truncate=3Doff); > I think you can also do > ALTER TABLE ... SET (toast.vacuum_truncate=3Doff); > to disable it for the TOAST table. > > Postgres 18 added a global parameter of the same name which you can > change in postgresql.conf, and from the commit message it sound like it > was added to cope with scenarios precisely like yours. But if for you > it's always the same toast table (or a small number of them) then I > would think it'd be better to change the per-table param for those. > (Also, this won't require that you upgrade to Postgres 18 just yet, > which sounds particularly helpful in case Aurora doesn't offer that > version.) > > Here it's the commit message for the change in 18, see the "Discussion" > link for more info: > > commit 0164a0f9ee12e0eff9e4c661358a272ecd65c2d4 > Author: Nathan Bossart [] > AuthorDate: Thu Mar 20 10:16:50 2025 -0500 > CommitDate: Thu Mar 20 10:16:50 2025 -0500 > > Add vacuum_truncate configuration parameter. > > This new parameter works just like the storage parameter of the > same name: if set to true (which is the default), autovacuum and > VACUUM attempt to truncate any empty pages at the end of the table. > It is primarily intended to help users avoid locking issues on hot > standbys. The setting can be overridden with the storage parameter > or VACUUM's TRUNCATE option. > > Since there's presently no way to determine whether a Boolean > storage parameter is explicitly set or has just picked up the > default value, this commit also introduces an isset_offset member > to relopt_parse_elt. > > Suggested-by: Will Storey > Author: Nathan Bossart > Co-authored-by: Gurjeet Singh > Reviewed-by: Laurenz Albe > Reviewed-by: Fujii Masao > Reviewed-by: Robert Treat > Discussion: https://postgr.es/m/Z2DE4lDX4tHqNGZt%40dev.null > > > -- > =C3=81lvaro Herrera PostgreSQL Developer =E2=80=94 > https://www.EnterpriseDB.com/ > Al principio era UNIX, y UNIX habl=C3=B3 y dijo: "Hello world\n". > No dijo "Hello New Jersey\n", ni "Hello USA\n". > --000000000000a67c42063971777d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks Alvaro,

I read the thread and ac= tually disabled truncate on my table with a large toast table which=C2=A0mi= tigated the issue. Unsure what happens with the empty pages now, I guess=C2= =A0they would be reused=C2=A0for new inserts.
I would like to see= if there are better opportunities=C2=A0to improve this truncation process.= Perhaps identify=C2=A0why we need this arbitrary threshold to determine a = full buffer scan?

/*<= /i>
* This is the size (in the number of blocks= ) above which we scan the
* entire buffer p= ool to remove the buffers for all the pages of relation
* being dropped. For the relations with size below this thresh= old, we find
* the buffers by doing lookups= in BufMapping table.
*/
#define BUF_DROP_FUL= L_SCAN_THRESHOLD (uint64) = (NBuffers / 32)<= /i>

https://github.com/postgres= /postgres/blob/e03c95287764158941d317972a332565729b6af2/src/backend/storage= /buffer/bufmgr.c#L91

As this can cause significant issues as = we scale memory for shared buffers. (Very often the case with Aurora)
Thanks,
Dharin

On Tue, Jul 8, 2= 025 at 4:05=E2=80=AFPM =C3=81lvaro Herrera <alvherre@kurilemu.de> wrote:
=
On 2025-Jul-08, Dharin Sh= ah wrote:

> *Problem Summary:*
>=C2=A0 =C2=A0WAL replay of relation truncation operations on read repli= cas triggers
> buffer invalidation that requires AccessExclusive locks, blocking
> concurrent read queries for extended periods.

Hmm, sounds like disabling truncate of the TOAST relation by vacuum
could help.=C2=A0 We have configuration options for that -- one is per tabl= e
and was added in Postgres 12, changed with
=C2=A0 ALTER TABLE ... SET (vacuum_truncate=3Doff);
I think you can also do
=C2=A0 ALTER TABLE ... SET (toast.vacuum_truncate=3Doff);
to disable it for the TOAST table.

Postgres 18 added a global parameter of the same name which you can
change in postgresql.conf, and from the commit message it sound like it
was added to cope with scenarios precisely like yours.=C2=A0 But if for you=
it's always the same toast table (or a small number of them) then I
would think it'd be better to change the per-table param for those.
(Also, this won't require that you upgrade to Postgres 18 just yet,
which sounds particularly helpful in case Aurora doesn't offer that ver= sion.)

Here it's the commit message for the change in 18, see the "Discus= sion"
link for more info:

commit 0164a0f9ee12e0eff9e4c661358a272ecd65c2d4
Author:=C2=A0 =C2=A0 =C2=A0Nathan Bossart <nathan@postgresql.org> []
AuthorDate: Thu Mar 20 10:16:50 2025 -0500
CommitDate: Thu Mar 20 10:16:50 2025 -0500

=C2=A0 =C2=A0 Add vacuum_truncate configuration parameter.

=C2=A0 =C2=A0 This new parameter works just like the storage parameter of t= he
=C2=A0 =C2=A0 same name: if set to true (which is the default), autovacuum = and
=C2=A0 =C2=A0 VACUUM attempt to truncate any empty pages at the end of the = table.
=C2=A0 =C2=A0 It is primarily intended to help users avoid locking issues o= n hot
=C2=A0 =C2=A0 standbys.=C2=A0 The setting can be overridden with the storag= e parameter
=C2=A0 =C2=A0 or VACUUM's TRUNCATE option.

=C2=A0 =C2=A0 Since there's presently no way to determine whether a Boo= lean
=C2=A0 =C2=A0 storage parameter is explicitly set or has just picked up the=
=C2=A0 =C2=A0 default value, this commit also introduces an isset_offset me= mber
=C2=A0 =C2=A0 to relopt_parse_elt.

=C2=A0 =C2=A0 Suggested-by: Will Storey <will@summercat.com>
=C2=A0 =C2=A0 Author: Nathan Bossart <nathandbossart@gmail.com>
=C2=A0 =C2=A0 Co-authored-by: Gurjeet Singh <gurjeet@singh.im>
=C2=A0 =C2=A0 Reviewed-by: Laurenz Albe <laurenz.albe@cybertec.at>
=C2=A0 =C2=A0 Reviewed-by: Fujii Masao <masao.fujii@oss.nttdata.com>
=C2=A0 =C2=A0 Reviewed-by: Robert Treat <rob@xzilla.net>
=C2=A0 =C2=A0 Discussion: https://postgr.es/m/Z2DE4lD= X4tHqNGZt%40dev.null


--
=C3=81lvaro Herrera=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0PostgreSQL Developer= =C2=A0 =E2=80=94=C2=A0 https://www.EnterpriseDB.com/
Al principio era UNIX, y UNIX habl=C3=B3 y dijo: "Hello world\n".=
No dijo "Hello New Jersey\n", ni "Hello USA\n".
--000000000000a67c42063971777d--