public inbox for [email protected]
help / color / mirror / Atom feedFrom: Dharin Shah <[email protected]>
To: Álvaro Herrera <[email protected]>
Cc: [email protected]
Subject: Re: WAL Replay Buffer Invalidation Conflicts During Page Truncation on Read Replicas
Date: Tue, 8 Jul 2025 23:15:50 +0200
Message-ID: <CAOj6k6dnMDNUrsLbOsZFequ1UJ8N95Nq9hVRBu-Bj0rrbSD0ug@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAOj6k6cwLj3ohwc0RmwqvO7pAkJGchTXhAKVUk7fshmH-yYtBQ@mail.gmail.com>
<[email protected]>
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/e03c95287764158941d317972a332565729b6af2/src/backend/stora...
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 PM Álvaro Herrera <[email protected]> wrote:
> On 2025-Jul-08, Dharin Shah wrote:
>
> > *Problem Summary:*
> > WAL replay of relation truncation operations on read replicas 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. We have configuration options for that -- one is per table
> and was added in Postgres 12, changed with
> ALTER TABLE ... SET (vacuum_truncate=off);
> I think you can also do
> ALTER TABLE ... SET (toast.vacuum_truncate=off);
> 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 <[email protected]> []
> 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 <[email protected]>
> Author: Nathan Bossart <[email protected]>
> Co-authored-by: Gurjeet Singh <[email protected]>
> Reviewed-by: Laurenz Albe <[email protected]>
> Reviewed-by: Fujii Masao <[email protected]>
> Reviewed-by: Robert Treat <[email protected]>
> Discussion: https://postgr.es/m/Z2DE4lDX4tHqNGZt%40dev.null
>
>
> --
> Álvaro Herrera PostgreSQL Developer —
> https://www.EnterpriseDB.com/
> Al principio era UNIX, y UNIX habló y dijo: "Hello world\n".
> No dijo "Hello New Jersey\n", ni "Hello USA\n".
>
view thread (3+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: WAL Replay Buffer Invalidation Conflicts During Page Truncation on Read Replicas
In-Reply-To: <CAOj6k6dnMDNUrsLbOsZFequ1UJ8N95Nq9hVRBu-Bj0rrbSD0ug@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox