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

Hi,

On Thu, 13 Aug 2026 at 08:12, Bharath Rupireddy <
bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:

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

Thanks for the detailed review and benchmark.

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

I tried measuring this on an Azure Standard_D8as_v5 VM with ext4 on a
Premium_LRS
disk. I used separate clusters with 1, 641, and 6401 WAL files, and ran
three order-rotated rounds of 500 checkpoints per condition.

With fsync enabled, median checkpoint times were 14.890 ms, 14.652 ms,
and 17.141 ms respectively. Adding 640 WAL files caused no measurable
slowdown on this setup. Adding 6400 files increased the median from
14.89 ms to 17.14 ms, about 2.25 ms.

With fsync disabled to isolate directory scanning, the medians were 0.470
ms,
0.758 ms, and 3.271 ms, or roughly 0.44 to 0.45 microseconds per additional
file.

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

That makes sense to me. My thought is to move both checks to the SQL
caller,
use the locked accessor for the installation state, and leave assertions in
the internal helper. If that's what you had in mind too, I can add this in
v6.
Thoughts?

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

I couldn't find a normal SQL-callable state where installation is disabled
independently of recovery. That seems to support your point that this
should
be a caller precondition rather than a second runtime path in the helper.

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

I'd still prefer the no-argument form using min_wal_size. Not having to
estimate a byte count seems useful, though I understand the concern about
the
implicit dependency. I'd like to hear what others find less surprising.

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

Hmm, you are right about this, and results do show that. I think it
should describe the wal_init_zero distinction directly: CoW can still
benefit
when zero-fill is enabled, but little benefit should be expected when it is
disabled. Will edit it.

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

I think a small helper for the common one-file initialization and close
sequence would help. I'll also try names that distinguish on-demand work
from checkpoint preallocation more clearly.

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

You're right that the test can be smaller, but I'd keep the exact insertion
boundary case because it caught a real bug. A one-node prototype with 1 MB
segments and eight assertions passed and cut runtime to about half.
Does that seem like a reasonable balance?

I'm still inclined to keep force. Raising max_wal_size changes global
checkpoint scheduling, whereas force scopes the choice to one explicit
superuser call. The concern is legitimate, though, and I'd like to hear if
others prefer removing the escape hatch entirely.

Will wait for inputs and fold the above edits into v6 if the direction
sounds reasonable.

Regards,
Ayush