Re: Add a pg_wal_preallocate() SQL function to eagerly create future WAL segments

Hi,

On Tue, 4 Aug 2026 at 12:26, Bharath Rupireddy <
bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:

> Hi,
>
> On Mon, Aug 3, 2026 at 7:24 AM Ayush Tiwari <ayushtiwari(dot)slg01(at)gmail(dot)com>
> wrote:
> >
> > v4 attached.
> >
> > By default the request is now limited to the whole segments that fit
> within
> > max_wal_size, and force => true bypasses that when you really do want a
> bigger
> > warm-up. A NOTICE is issued only when an explicit request is reduced; a
> plain
> > no-argument call stays quiet.
>
> Thanks for working on this!
>
> I have the following design thoughts:
>
> 1/ Why does this have to be a function? Why not let the checkpointer
> or wal writer scale this automatically based on recent heuristics, for
> example how many WAL files have been allocated in the last hour or so,
> the rate of WAL generation, and so on? It could track a simple metric
> in shared memory (or local to the checkpointer or wal writer), do some
> basic math, and kick in when enabled by a GUC.
>

Automatic scaling and this function are orthogonal, not competing.
Heuristics
need history; the cases this targets have none, such as a freshly initdb'd
cluster or a quiet system about to take a burst. This is mainly for
benchmarks.

> 2/ What happens if I allocate, say, a billion WAL files and fill up
> the disk space (I'm a legitimate superuser and I use force mode, just
> that I got the calculation wrong or such), and then right after
> creating them I restart or crash for some reason? Replay time is not
> affected, since these segments sit ahead of the insertion point and
> carry no useful records. But would it affect checkpoint time, or
> snapshot times/size (disk/storage-based snapshots)? A restart is fine,
> but the snapshot now has to carry all these files, which are empty in
> the PostgreSQL sense but still take up disk space, increasing the
> snapshot size. And what if I create them, then fail over to a standby
> and try to rejoin this old primary as a new standby. Would pg_rewind
> need to go through all these files?
>

Replay: unaffected.

Checkpoint: RemoveOldXlogFiles does one ReadDir, and each future segment
costs a single strcmp. Should be marginal(?)

pg_basebackup: unaffected, it doesn't copy pg_wal contents. Storage level
snapshots do carry the files.

pg_rewind: those segments exist on the old primary but not the promoted
standby, so decide_file_action() returns FILE_ACTION_REMOVE. It unlinks
them, it does not copy them.

> 3/ I played with the v4 patch a bit on local NVMe SSD storage. With
> max_wal_size=128MB and 16MB segments, a single call for 640 segments
> grew pg_wal from 17MB to 11GB in about 40 seconds, roughly 80x
> max_wal_size. I noticed that a checkpoint does not reclaim it.
> RemoveOldXlogFiles only recycles or removes log files older or equal
> to the last segment to be kept, which it computes from the checkpoint
> redo pointer, but the preallocated WAL files sit ahead of the
> insertion point, so repeated CHECKPOINTs left the count unchanged. The
> number of WAL files only reduces once enough WAL is written to reach
> and use those files. So force can leave a large multiple of
> max_wal_size on disk, and it stays there until that much WAL is
> actually written, not until the next checkpoint. Is this intentional?
> If the database lands in this situation, how can we recover the disk
> space to avoid no-space-left-on-device issues or downtime?
>

Yes. RemoveOldXlogFiles only considers files at or before the last segment
to
keep, and preallocated segments sit ahead of the insertion point. The
checkpointer's own preallocation behaves the same way, one segment at a
time.
The space isn't leaked: it is consumed as WAL advances.

On filling the disk, WAL has closer precedents: an inactive replication
slot or
a failing archive_command also pins WAL that checkpoints won't remove, and
the
answer there was a bounding GUC rather than removing the feature. Here the
limit is the default, and force is an explicit superuser opt-out.

If you're suggesting force should go entirely, that's a design decision I'm
happy to defer to consensus on. Nazir proposed it upthread, so it would be
good
to hear other opinions. Either way I'll document that forced segments stay
until WAL advances into them.

Regards,
Ayush