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

Hi,

On Tue, 4 Aug 2026 at 18:11, Ayush Tiwari <ayushtiwari(dot)slg01(at)gmail(dot)com> wrote:
>
> v5 attached.

Thanks for sending the patch.

I benchmarked v5 by measuring pgbench init time (-s 1000), which
writes about 12 GB of WAL, 762 WAL files of 16 MB, on an Amazon EC2
m6id.4xlarge instance, across EBS gp3 (network-attached SSD) with ext4
and ZFS (copy-on-write, CoW), and local NVMe with ext4. I kept fsync
on and ensured no checkpoint occurs during the load to keep variance
to a minimum.

Below are the pgbench init times, 3 runs each, with wal_init_zero
(zero-fill) on or off:

Storage zero-fill HEAD Patched Speedup
ext4 (non-CoW) on 121.0s 90.7s 25% faster
local NVMe on 81.5s 71.6s 12% faster
ZFS (CoW) on 156.9s 140.2s 11% faster
ZFS (CoW) off 129.0s 129.7s no change

As expected, the gain comes from the WAL-file initialization
(zero-fill and fsync) that HEAD does while loading and the patch does
up front instead. HEAD creates all 762 WAL files during the load,
while the patch creates 0, since they are preallocated first.

Almost all of that init time is fsync, not the zero-fill write, and it
scales with the storage's fsync cost. Per WAL file that is roughly 31
ms of fsync on network SSD versus 8 ms on local NVMe (HEAD spent about
23.8s vs 6.3s in fsync total), which is why the speedup drops from 25%
to 12%.

CoW still gains about 11% with zero-fill on, and shows no gain with it
off, where creating a WAL file takes only about 0.9 ms, so there is
almost nothing for preallocation to save.

>> 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.

Given my experimentation above, I'm okay with first having a SQL
function like this, and perhaps as a next step we can think of adding
some sort of heuristic-based preallocation either built into the
checkpointer itself or the wal writer.

>> 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?
>
> Checkpoint: RemoveOldXlogFiles does one ReadDir, and each future segment
> costs a single strcmp. Should be marginal(?)

I didn't measure this, but could you give some numbers for the record
here to ensure we don't leave that cost unmeasured.

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

I think it's a trade-off for storage-level snapshots. I'm not sure if
we should cover that in the docs since storage-level snapshots are
outside the scope of PostgreSQL.

>> 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.
>> .... 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?
>
> 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.
> Either way I'll document that forced segments stay
> until WAL advances into them.

Even with the superuser-only check and recommendation that says use
this function mainly for benchmarks, the force option still worries
me. Why not limit it to max_wal_size and be done with it? If anyone
wants to really preallocate a large number of WAL files, they can
still do it by increasing max_wal_size (which may delay checkpoints
though), but that should be okay I guess.

Here are some comments on the v5 patch:

1/
+ /*
+ * Bail out if we are in recovery, or if the startup process has disabled
+ * segment installation. The latter is an intentional unlocked read, as
+ * in PreallocXlogFiles().
+ */
+ if (RecoveryInProgress() || !XLogCtl->InstallXLogFileSegmentActive)

We expect the PreallocXlogSegments() caller to ensure the database is
not in recovery. I think the startup process disabling segment
installation is also as important as the recovery-in-progress check
and the caller must error out in such cases. I suggest moving these
into the caller and having them as asserts at the beginning of
PreallocXlogSegments().

2/ Also, do we ever hit the case where the startup process disables
segment installation in the WAL files allocation loop in
PreallocXlogSegments()? AFAICS, maybe not. During crash recovery when
the startup process sets this flag in StartupXLOG(), at that point
RecoveryInProgress() is still true.

3/
+ * returns the number of segments that were newly created. If bytes is NULL
+ * (the default), min_wal_size is used. This lets an operator warm up the

Personally, I don't like an API where bytes being NULL derives
something from another parameter (others may have a different
opinion), but it creates a dependency on some other param. What's the
problem if it errors out in case of bytes <= 0 and makes the function
strict to not accept null inputs? That's a clean API IMHO.

4/
Commit message:

them. On copy-on-write file systems, where recycling is not cheaper than
creating (see wal_recycle), preallocation offers little benefit.

Docs:
+ space occupied for a long time. On file systems where
recycling a WAL file
+ is not cheaper than creating a new one (for example copy-on-write file
+ systems, see <xref linkend="guc-wal-recycle"/>), preallocation provides
+ little benefit. This function cannot be executed during recovery.

My experimentation says otherwise. CoW still gains about 11% with
zero-fill on, and shows no gain with it off. Can we be more specific
in the docs and commit message?

5/ Can we think of deduplicating the for loop in
PreallocXlogSegments() with PreallocXlogFiles()? Also the naming of
the new function and the existing function looks similar (WAL segment
is an internal term used for WAL file).

6/
+ allows_streaming => 1, extra => ['--wal-segsize=16']);

Why not use wal segment size 1MB for testing and use min and max
wal_size in multiples of 1MB? This can make the tests a bit faster.

7/ Tests look too many. I don't think we need to cover all the cases.
One positive case and one negative case should be enough. No need to
cover for recovery-in-progress errors and all, because that just
works. This keeps the number of tests to 1 or 2 and you can even think
of adding them to an existing closely related TAP test without the
need to start and stop another server for this (I'm aware of the fact
that our testing infrastructure isn't free).

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com