From: Tom Lane <tgl@sss.pgh.pa.us>
To: Don Seiler <don@seiler.us>
cc: Postgres General <pgsql-general@postgresql.org>
Subject: Re: Use of max_slot_wal_keep_size parameter
In-reply-to: 
 <CAHJZqBArXQ0245uvwdBa+ax520BjgRNEuTdFXbnuDwyaJbxUAg@mail.gmail.com>
References: 
 <CAHJZqBArXQ0245uvwdBa+ax520BjgRNEuTdFXbnuDwyaJbxUAg@mail.gmail.com>
Comments: In-reply-to Don Seiler <don@seiler.us>
	message dated "Tue, 26 Mar 2024 08:49:54 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2818929.1711462144.1@sss.pgh.pa.us>
Date: Tue, 26 Mar 2024 10:09:04 -0400
Message-ID: <2818930.1711462144@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2818930.1711462144%40sss.pgh.pa.us>
Precedence: bulk

Don Seiler <don@seiler.us> writes:
> With our recent upgrade to PG 15, we've put the max_slot_wal_keep_size into
> place, setting it about 3% lower than the size of the volume dedicated to
> pg_wal. However last night we had an incident where the volume filled up
> while we were performing a massive insert from one table into another.

> I don't believe the replica was lagging, and we have pgbackrest configured
> for async archiving, which I assume was fine. It seemed to just be a matter
> of PG not cleaning up the WAL. Our understanding was that
> max_slot_wal_keep_size would prevent this but perhaps it only deals with
> situations where the replication slot lag is a factor?

My immediate reaction is that 3% is a mighty small margin for error.
I don't know exactly how max_slot_wal_keep_size is enforced these
days, but in the past restrictions like that were implemented by
deciding during a checkpoint whether to unlink a no-longer-needed WAL
file (if we had too much WAL) or rename/recycle it to become a future
WAL segment (if not).  So you could overshoot the specified target by
more or less the amount of WAL that could be emitted between two
checkpoints.  Perhaps it's tighter nowadays, but I really doubt that
it's exact-to-the-kilobyte-at-all-times.

			regards, tom lane