Re: Force the old transactions logs cleanup even if checkpoint is skipped

On Wed, Nov 08, 2023 at 12:44:09PM +0000, Zakhlystov, Daniil (Nebius) wrote:
>> I am not sure to understand your last sentence here.  Once the
>> archiver is back up, you mean that the WAL segments that were not
>> previously archived still are still not archived?  Or do you mean that
>> because of a succession of checkpoint skipped we are just enable to
>> remove them from pg_wal.
>
> Yes, the latter is correct - we are unable to clean up the already archived WALs
> due to the checkpoint being skipped.

Yes, theoretically you could face this situation if you have an
irregular WAL activity with cycles where nothing happens and an
archive command that keeps failing while there is WAL generated, but
works while WAL is not generated.

>> I am not convinced that this is worth the addition in the skipped
>> path.  If your system is idle and a set of checkpoints is skipped, the
>> data folder is not going to be under extra space pressure because of
>> database activity (okay, unlogged tables even if these would generate
>> some WAL for init pages), because there is nothing happening in it
>> with no "important" WAL generated.  Note that the backend is very
>> unlikely going to generate WAL only marked with XLOG_MARK_UNIMPORTANT.
>
>> More to the point: what's the origin of the disk space issues?  System
>> logs, unlogged tables or something else?  It is usually a good
>> practice to move logs to a different partition.  At the end, it sounds
>> to me that removing segments more aggressively is just kicking the can
>> elsewhere, without taking care of the origin of the disk issues.
>
> This problem arises when disk space issues are caused by temporary failed archiving.
> As a result, the pg_wal becomes filled with WALs. This situation
> leads to Postgres being unable to perform any write operations since there is no more
> free disk space left. Usually, cloud providers switch the cluster to a Read-Only mode
> if there is less than 3-4% of the available disk space left, but this also does not resolve
> this problem.

> The actual problem is that after archiving starts working normally again, Postgres is
> unable to free the accumulated WAL and switch to Read-Write mode due to the
> checkpoint being skipped, leading to a vicious circle. However, nothing prevents
> Postgres from exiting such situations on its own. This patch addresses this specific
> behavior, enabling Postgres to resolve such situations autonomously.

Yep, but it does not really solve your disk space issues in a reliable
way.

I am not really convinced that this is worth complicating the skipped
path for this goal.  In my experience, I've seen complaints where WAL
archiving bloat was coming from the archive command not able to keep
up with the amount generated by the backend, particularly because the
command invocation was taking longer than it takes to generate a new
segment.  Even if there is a hole of activity in the server, if too
much WAL has been generated it may not be enough to catch up depending
on the number of segments that need to be processed.  Others are free
to chime in with extra opinions, of course.

While on it, I think that your patch would cause incorrect and early
removal of segments.  It computes the name of the last segment to
remove based on last_important_lsn, ignoring KeepLogSeg(), meaning
that it ignores any WAL retention required by replication slots or
wal_keep_size.  And this causes the calculation of an incorrect segno
horizon.
--
Michael