Add a pg_wal_preallocate() SQL function to eagerly create future WAL segments
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253168psql -h localhost -U postgresBuilt from patchset v25 (message #25), October 24, 2026 at 02:57 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253168_25 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253168_25 && git checkout t253168_25Patchset v25 (message #25) is on t253168_25
Hi,
Creating a new WAL segment (open + zero-fill + fsync) is quite a bit more
expensive than recycling an existing one, and on the write path that cost
falls on foreground backends. The pool of future segments only fills in
lazily as WAL is produced, so right after initdb, or before a benchmark or
bulk load, it can be essentially empty and backends end up creating the
segments themselves at the worst possible time.
This is the pg_wal_preallocate() idea Andres raised in the "Pre-allocating
WAL files" thread [1]PostgreSQL: Pre-allocating WAL files </messages/by-id/20201225200953.jjkrytlrzojbndh5@alap3.anarazel.de>; I finally sat down and tried it. The attached patch
adds a superuser-only function:
pg_wal_preallocate(bytes bigint DEFAULT NULL) returns bigint
It creates ceil(bytes / wal_segment_size) segments ahead of the current
insert location and returns how many it actually created; existing ones are
skipped. With no argument it uses min_wal_size, and the size can carry
units, e.g. pg_wal_preallocate(pg_size_bytes('1GB')). It reuses the
existing segment-creation code, is interruptible, and won't run during
recovery. It's best-effort, though: a later checkpoint may recycle or
remove anything past min_wal_size, and on copy-on-write filesystems (see
wal_recycle) there's little point to it.
On a fresh cluster (track_wal_io_timing on), a bulk INSERT creating ~13
segments paid the creation cost in the foreground (pg_stat_io, object=wal,
context=init): 13 creations and ~0.3 s of write + fsync without
preallocation, and 0 after it. The 0.3 s is storage-dependent, so take it
with a grain of salt.
Documentation and a TAP test are included.
Two things I'm unsure about and would welcome opinions on: whether a byte
count defaulting to min_wal_size is the right interface or a plain segment
count would be more honest; and whether there should be a cap, since nothing
currently stops a request large enough to fill the disk.
Thoughts?
[1]: PostgreSQL: Pre-allocating WAL files </messages/by-id/20201225200953.jjkrytlrzojbndh5@alap3.anarazel.de>
</messages/by-id/20201225200953.jjkrytlrzojbndh5@alap3.anarazel.de>
Regards,
Ayush
Attachments:
v1-0001-pg_wal_preallocate.patchapplication/octet-stream; name=v1-0001-pg_wal_preallocate.patchDownload+308-2
Hi
2026年7月23日(木) 0:07 Ayush Tiwari <ayushtiwari.slg01@gmail.com>:
Hi,
(...)
Two things I'm unsure about and would welcome opinions on: whether a byte
count defaulting to min_wal_size is the right interface or a plain segment
count would be more honest;
A plain segment count feels like the more intuitive value to provide, especially
as the function returns the number of segments created. OTOH min/max_wal_size
etc. are all specified by size, so maybe that's more consistent.
Regarding min_wal_size, if there is already more than that amount of WAL
present, the function is basically just adding an arbitrary number of
WAL segments.
Maybe the function could, if no value is provided, just create segments until
min_wal_size is reached?
and whether there should be a cap, since nothing
currently stops a request large enough to fill the disk.
Could max_wal_size be a reasonable default cap or soft upper limit?
Minor code nitpick:
-#define CATALOG_VERSION_NO 202607201
+#define CATALOG_VERSION_NO 202607220I don't think the catalog version bump is needed in patches,
it's up to the committer to set the appropriate value at commit time.
Regards
Ian Barwick
Hi,
On Wed, 22 Jul 2026 at 21:30, Ian Lawrence Barwick <barwick@gmail.com>
wrote:
2026年7月23日(木) 0:07 Ayush Tiwari <ayushtiwari.slg01@gmail.com>:
Hi,
(...)
Two things I'm unsure about and would welcome opinions on: whether a byte
count defaulting to min_wal_size is the right interface or a plainsegment
count would be more honest;
A plain segment count feels like the more intuitive value to provide,
especially
as the function returns the number of segments created. OTOH
min/max_wal_size
etc. are all specified by size, so maybe that's more consistent.
Thanks for the review!
I went back and forth too. I'd keep bytes for now, for consistency with
min/max_wal_size and so it composes with pg_size_bytes('1GB'), but I'm
happy to
switch if the count reads better to people.
Regarding min_wal_size, if there is already more than that amount of WAL
present, the function is basically just adding an arbitrary number of
WAL segments.
Maybe the function could, if no value is provided, just create segments
until
min_wal_size is reached?
That's what it already does, just undocumented: existing segments are
skipped,
so the default only creates the missing ones in the min_wal_size window
ahead
of the insertion point (0 if they're already there). I'll make the docs say
so.
and whether there should be a cap, since nothing
currently stops a request large enough to fill the disk.Could max_wal_size be a reasonable default cap or soft upper limit?
Possibly. I left it uncapped for now since it's a superuser-only explicit
action, but max_wal_size is a sensible ceiling if we want one. Happy to add
a
soft clamp if there's appetite for it.
Minor code nitpick:
-#define CATALOG_VERSION_NO 202607201 +#define CATALOG_VERSION_NO 202607220I don't think the catalog version bump is needed in patches,
it's up to the committer to set the appropriate value at commit time.
Agreed, I'll drop it in the next rebase / v2.
Regards,
Ayush
Hi,
On Thu, 23 Jul 2026 at 11:14, Ayush Tiwari <ayushtiwari.slg01@gmail.com>
wrote:
Hi,
On Wed, 22 Jul 2026 at 21:30, Ian Lawrence Barwick <barwick@gmail.com>
wrote:2026年7月23日(木) 0:07 Ayush Tiwari <ayushtiwari.slg01@gmail.com>:
Hi,
(...)
Two things I'm unsure about and would welcome opinions on: whether a
byte
count defaulting to min_wal_size is the right interface or a plain
segment
count would be more honest;
A plain segment count feels like the more intuitive value to provide,
especially
as the function returns the number of segments created. OTOH
min/max_wal_size
etc. are all specified by size, so maybe that's more consistent.Thanks for the review!
I went back and forth too. I'd keep bytes for now, for consistency with
min/max_wal_size and so it composes with pg_size_bytes('1GB'), but I'm
happy to
switch if the count reads better to people.Regarding min_wal_size, if there is already more than that amount of WAL
present, the function is basically just adding an arbitrary number of
WAL segments.
Maybe the function could, if no value is provided, just create segments
until
min_wal_size is reached?That's what it already does, just undocumented: existing segments are
skipped,
so the default only creates the missing ones in the min_wal_size window
ahead
of the insertion point (0 if they're already there). I'll make the docs
say so.and whether there should be a cap, since nothing
currently stops a request large enough to fill the disk.Could max_wal_size be a reasonable default cap or soft upper limit?
Possibly. I left it uncapped for now since it's a superuser-only explicit
action, but max_wal_size is a sensible ceiling if we want one. Happy to
add a
soft clamp if there's appetite for it.Minor code nitpick:
-#define CATALOG_VERSION_NO 202607201 +#define CATALOG_VERSION_NO 202607220I don't think the catalog version bump is needed in patches,
it's up to the committer to set the appropriate value at commit time.Agreed, I'll drop it in the next rebase / v2.
Rebased with updated doc and reverted catalog_version_no.
Regards,
Ayush
Hi all,
On Fri, Jul 31, 2026 at 11:16 AM Ayush Tiwari
<ayushtiwari.slg01@gmail.com> wrote:
Hi,
On Thu, 23 Jul 2026 at 11:14, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:
Hi,
On Wed, 22 Jul 2026 at 21:30, Ian Lawrence Barwick <barwick@gmail.com> wrote:
2026年7月23日(木) 0:07 Ayush Tiwari <ayushtiwari.slg01@gmail.com>:
Hi,
(...)
Two things I'm unsure about and would welcome opinions on: whether a byte
count defaulting to min_wal_size is the right interface or a plain segment
count would be more honest;A plain segment count feels like the more intuitive value to provide, especially
as the function returns the number of segments created. OTOH min/max_wal_size
etc. are all specified by size, so maybe that's more consistent.Thanks for the review!
I went back and forth too. I'd keep bytes for now, for consistency with
min/max_wal_size and so it composes with pg_size_bytes('1GB'), but I'm happy to
switch if the count reads better to people.Regarding min_wal_size, if there is already more than that amount of WAL
present, the function is basically just adding an arbitrary number of
WAL segments.
Maybe the function could, if no value is provided, just create segments until
min_wal_size is reached?That's what it already does, just undocumented: existing segments are skipped,
so the default only creates the missing ones in the min_wal_size window ahead
of the insertion point (0 if they're already there). I'll make the docs say so.and whether there should be a cap, since nothing
currently stops a request large enough to fill the disk.Could max_wal_size be a reasonable default cap or soft upper limit?
Possibly. I left it uncapped for now since it's a superuser-only explicit
action, but max_wal_size is a sensible ceiling if we want one. Happy to add a
soft clamp if there's appetite for it.Minor code nitpick:
-#define CATALOG_VERSION_NO 202607201 +#define CATALOG_VERSION_NO 202607220I don't think the catalog version bump is needed in patches,
it's up to the committer to set the appropriate value at commit time.Agreed, I'll drop it in the next rebase / v2.
Rebased with updated doc and reverted catalog_version_no.
Thanks for the updated patch. I tested this patch and was able to
verify the new pg_wal_preallocate() functionality on a freshly
initialized cluster. Before invoking the function, the pg_wal
directory contained a single WAL segment. Executing: SELECT
pg_wal_preallocate(), returned 5, and I confirmed that 5 additional
WAL segment files were created in the pg_wal directory, matching the
configured min_wal_size target. I also verified the updated default
behavior discussed in the thread. The default invocation only created
the missing segments needed to satisfy the min_wal_size window ahead
of the current insertion point, rather than creating an arbitrary
number of WAL segments. I then reset the WAL and I/O statistics and
executed a workload generating approximately 3.3 GB of WAL using a
5-million-row INSERT. Since the default invocation only preallocated
enough segments to satisfy min_wal_size (80 MB in my setup), the
workload still required additional WAL segment creation during
execution. This behavior seems consistent with the intended design and
the discussion in the thread. I did not encounter any functional
issues during testing.
Regards,
Solai
Hi,
On Fri, 31 Jul 2026 at 15:31, solai v <solai.cdac@gmail.com> wrote:
Hi all,
Thanks for the updated patch. I tested this patch and was able to
verify the new pg_wal_preallocate() functionality on a freshly
initialized cluster. Before invoking the function, the pg_wal
directory contained a single WAL segment. Executing: SELECT
pg_wal_preallocate(), returned 5, and I confirmed that 5 additional
WAL segment files were created in the pg_wal directory, matching the
configured min_wal_size target. I also verified the updated default
behavior discussed in the thread. The default invocation only created
the missing segments needed to satisfy the min_wal_size window ahead
of the current insertion point, rather than creating an arbitrary
number of WAL segments. I then reset the WAL and I/O statistics and
executed a workload generating approximately 3.3 GB of WAL using a
5-million-row INSERT. Since the default invocation only preallocated
enough segments to satisfy min_wal_size (80 MB in my setup), the
workload still required additional WAL segment creation during
execution. This behavior seems consistent with the intended design and
the discussion in the thread. I did not encounter any functional
issues during testing.
Thanks for testing!
Rebased patch attached.
Regards,
Ayush
Hi,
Thank you for working on this!
On Mon, 3 Aug 2026 at 08:13, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:
Hi,
Rebased patch attached.
Patch LGTM, it works as intended. My only concern is the unbounded max
limit; which can cause problems. I think 'max_wal_size' is a
reasonable limit. Perhaps we can add a 'force' boolean option to
function, then it can bypass the 'max_wal_size' limit; what do you
think?
--
Regards,
Nazir Bilal Yavuz
Microsoft
Hi,
On Mon, 3 Aug 2026 at 16:38, Nazir Bilal Yavuz <byavuz81@gmail.com> wrote:
Hi,
Thank you for working on this!
On Mon, 3 Aug 2026 at 08:13, Ayush Tiwari <ayushtiwari.slg01@gmail.com>
wrote:Hi,
Rebased patch attached.
Patch LGTM, it works as intended. My only concern is the unbounded max
limit; which can cause problems. I think 'max_wal_size' is a
reasonable limit. Perhaps we can add a 'force' boolean option to
function, then it can bypass the 'max_wal_size' limit; what do you
think?
Thanks for the review! I like the idea you suggested.
Let me try implementing it that way.
Regards,
Ayush
Hi,
On Mon, 3 Aug 2026 at 17:30, Ayush Tiwari <ayushtiwari.slg01@gmail.com>
wrote:
Hi,
On Mon, 3 Aug 2026 at 16:38, Nazir Bilal Yavuz <byavuz81@gmail.com> wrote:
Patch LGTM, it works as intended. My only concern is the unbounded max
limit; which can cause problems. I think 'max_wal_size' is a
reasonable limit. Perhaps we can add a 'force' boolean option to
function, then it can bypass the 'max_wal_size' limit; what do you
think?Thanks for the review! I like the idea you suggested.
Let me try implementing it that way.
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.
Regards,
Ayush
Hi,
On Mon, Aug 3, 2026 at 7:24 AM Ayush Tiwari <ayushtiwari.slg01@gmail.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.
One concern I have with a function like this is that it mostly ends up
unused, because most end users may not know when to use it, and even
if someone does want to use it before a bulk load, that may need
application changes, which require some careful thought and prior
estimation of how many WAL files are needed, which also requires some
expertise of internals and so on.
This could perhaps be designed along the lines of how relation
extension already works, where we extend by more blocks depending on
the number of lock waiters, so that actual usage decides how much we
preallocate rather than relying on the operator to anticipate it.
I don't have a concrete solution here, just some thoughts.
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?
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?
--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com
Hi,
On Mon, 3 Aug 2026 at 17:24, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:
On Mon, 3 Aug 2026 at 17:30, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:
On Mon, 3 Aug 2026 at 16:38, Nazir Bilal Yavuz <byavuz81@gmail.com> wrote:
Patch LGTM, it works as intended. My only concern is the unbounded max
limit; which can cause problems. I think 'max_wal_size' is a
reasonable limit. Perhaps we can add a 'force' boolean option to
function, then it can bypass the 'max_wal_size' limit; what do you
think?Let me try implementing it that way.
v4 attached.
Thanks!
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.
+ if (!force)
+ {
+ int64 maxsegs = XLogMBVarToSegs(max_wal_size_mb,
+ wal_segment_size);
+
+ if (nsegs > maxsegs)
+ {
+ /*
+ * Only report the reduction for an explicit request; the default
+ * (min_wal_size) is expected to fit within max_wal_size.
+ */
+ if (!PG_ARGISNULL(0))
+ ereport(NOTICE,
+ (errmsg("WAL preallocation request was reduced to fit "
+ "within \"max_wal_size\""),
+ errdetail("Only whole WAL segments fitting within "
+ "\"max_wal_size\" will be preallocated."),
+ errhint("Call pg_wal_preallocate() with
\"force\" set to true "
+ "to bypass this limit.")));
+ nsegs = maxsegs;
+ }
+ }I think it is not correct to reduce the request to fit within
max_wal_size. User wants to do something but you change the request
without any confirmation. I think you should reject the request if
'bytes > max_wal_size && !force', and show a notice or error that the
same request can be done with the force option.
--
Regards,
Nazir Bilal Yavuz
Microsoft
Hi Bharath,
On Tue, Aug 4, 2026 at 2:56 PM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:
Hi,
On Mon, Aug 3, 2026 at 7:24 AM Ayush Tiwari <ayushtiwari.slg01@gmail.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.One concern I have with a function like this is that it mostly ends up
unused, because most end users may not know when to use it, and even
if someone does want to use it before a bulk load, that may need
application changes, which require some careful thought and prior
estimation of how many WAL files are needed, which also requires some
expertise of internals and so on.This could perhaps be designed along the lines of how relation
extension already works, where we extend by more blocks depending on
the number of lock waiters, so that actual usage decides how much we
preallocate rather than relying on the operator to anticipate it.I don't have a concrete solution here, just some thoughts.
I've hovered on this patch before. I guess the current function
interface is chosen mainly from the suggestion of Andres in the prior
thread and the feasibility of implementing it. The more ambitious part
you suggested has been stranded in the past for some reasons I haven't
looked into.
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?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?
--
Regards,
Xuneng Zhou
HighGo Software Co., Ltd.
Hi,
On Tue, 4 Aug 2026 at 12:26, Bharath Rupireddy <
bharath.rupireddyforpostgres@gmail.com> wrote:
Hi,
On Mon, Aug 3, 2026 at 7:24 AM Ayush Tiwari <ayushtiwari.slg01@gmail.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
Hi,
On Tue, 4 Aug 2026 at 12:32, Nazir Bilal Yavuz <byavuz81@gmail.com> wrote:
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.
+ if (!force) + { + int64 maxsegs = XLogMBVarToSegs(max_wal_size_mb, + wal_segment_size); + + if (nsegs > maxsegs) + { + /* + * Only report the reduction for an explicit request; the default + * (min_wal_size) is expected to fit within max_wal_size. + */ + if (!PG_ARGISNULL(0)) + ereport(NOTICE, + (errmsg("WAL preallocation request was reduced to fit " + "within \"max_wal_size\""), + errdetail("Only whole WAL segments fitting within " + "\"max_wal_size\" will be preallocated."), + errhint("Call pg_wal_preallocate() with \"force\" set to true " + "to bypass this limit."))); + nsegs = maxsegs; + } + }I think it is not correct to reduce the request to fit within
max_wal_size. User wants to do something but you change the request
without any confirmation. I think you should reject the request if
'bytes > max_wal_size && !force', and show a notice or error that the
same request can be done with the force option.
Hmm makes sense, thanks for the input. I'll make it so.
Regards,
Ayush
Hi,
On Tue, 4 Aug 2026 at 13:55, Ayush Tiwari <ayushtiwari.slg01@gmail.com>
wrote:
Hi,
On Tue, 4 Aug 2026 at 12:32, Nazir Bilal Yavuz <byavuz81@gmail.com> wrote:
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.
+ if (!force) + { + int64 maxsegs = XLogMBVarToSegs(max_wal_size_mb, + wal_segment_size); + + if (nsegs > maxsegs) + { + /* + * Only report the reduction for an explicit request; the default + * (min_wal_size) is expected to fit within max_wal_size. + */ + if (!PG_ARGISNULL(0)) + ereport(NOTICE, + (errmsg("WAL preallocation request was reduced to fit " + "within \"max_wal_size\""), + errdetail("Only whole WAL segments fitting within " + "\"max_wal_size\" will be preallocated."), + errhint("Call pg_wal_preallocate() with \"force\" set to true " + "to bypass this limit."))); + nsegs = maxsegs; + } + }I think it is not correct to reduce the request to fit within
max_wal_size. User wants to do something but you change the request
without any confirmation. I think you should reject the request if
'bytes > max_wal_size && !force', and show a notice or error that the
same request can be done with the force option.Hmm makes sense, thanks for the input. I'll make it so.
v5 attached.
It does [1]ERROR: WAL preallocation request exceeds "max_wal_size" DETAIL: The request needs 64 WAL segments, but "max_wal_size" allows 8. HINT: Call pg_wal_preallocate() with "force" set to true to bypass this limit. instead of silently creating segments.
Nothing gets created when it errors out, and the NOTICE is gone.
I ended up comparing after rounding up to whole segments rather than on the
byte value directly. Otherwise asking for exactly max_wal_size still sneaks
a
segment past it when max_wal_size isn't a multiple of the segment size.
The no-argument form still uses min_wal_size and skips the check, since
that is
what the server keeps anyway.
Regards,
Ayush
[1]: ERROR: WAL preallocation request exceeds "max_wal_size" DETAIL: The request needs 64 WAL segments, but "max_wal_size" allows 8. HINT: Call pg_wal_preallocate() with "force" set to true to bypass this limit.
DETAIL: The request needs 64 WAL segments, but "max_wal_size" allows 8.
HINT: Call pg_wal_preallocate() with "force" set to true to bypass this
limit.
Attachments:
t253168_15v5-0001-pg_wal_preallocate.patchapplication/octet-stream; name=v5-0001-pg_wal_preallocate.patchDownload+349-1
Hi,
On Tue, 4 Aug 2026 at 18:11, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:
On Tue, 4 Aug 2026 at 13:55, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:
On Tue, 4 Aug 2026 at 12:32, Nazir Bilal Yavuz <byavuz81@gmail.com> wrote:
I think it is not correct to reduce the request to fit within
max_wal_size. User wants to do something but you change the request
without any confirmation. I think you should reject the request if
'bytes > max_wal_size && !force', and show a notice or error that the
same request can be done with the force option.Hmm makes sense, thanks for the input. I'll make it so.
v5 attached.
It does [1] instead of silently creating segments.
Nothing gets created when it errors out, and the NOTICE is gone.
I ended up comparing after rounding up to whole segments rather than on the
byte value directly. Otherwise asking for exactly max_wal_size still sneaks a
segment past it when max_wal_size isn't a multiple of the segment size.
I think comparing whole segments is correct. v5 LGTM.
--
Regards,
Nazir Bilal Yavuz
Microsoft
Hi,
On Tue, 4 Aug 2026 at 18:11, Ayush Tiwari <ayushtiwari.slg01@gmail.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 thePersonally, 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
Hi,
On Thu, 13 Aug 2026 at 08:12, Bharath Rupireddy <
bharath.rupireddyforpostgres@gmail.com> wrote:
Hi,
On Tue, 4 Aug 2026 at 18:11, Ayush Tiwari <ayushtiwari.slg01@gmail.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 thePersonally, 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
Hi,
On Fri, Aug 14, 2026 at 7:16 AM Ayush Tiwari
<ayushtiwari.slg01@gmail.com> wrote:
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.
I think a few milliseconds per 6K+ WAL files should be okay. I'm okay
with this. Thanks for measuring this.
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.
I don't think taking the control file lock (which
IsInstallXLogFileSegmentActive() acquires) in this SQL function path
is a good idea. It can easily increase lock contention on the control
file lock, which is used internally to update important control file
fields.
This boils down to the following: when the database is not in recovery
(that is, RecoveryInProgress() is false), can the startup process ever
block new WAL file creation? It turns out it cannot, so the invariant
is Assert(RecoveryInProgress() || IsInstallXLogFileSegmentActive()). I
would just keep this in pg_wal_preallocate():
if (RecoveryInProgress())
ereport(ERROR .....);
/*
* When idatabase is not in recovery, new WAL file creation is not
* blocked by the startup process, so assert that invariant here.
*/
Assert(IsInstallXLogFileSegmentActive());
4/
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.
Sounds good, but use the GUC name directly instead of "zero-fill" and
avoid saying "little benefit should be expected."
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.
Thinking about it more, I'm fine to keep a separate function and leave
PreallocXlogFiles() as-is to avoid any backpatching issues. Just name
the new function something like PreallocNXlogFiles() and keep it
closer to PreallocXlogFiles() in the code.
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?
Sounds good. Just keep the test comments and descriptions minimal with
key details.
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.
Increasing max_wal_size alone doesn't block checkpoints, timeout-based
checkpoints can still occur. So, getting rid of the force option seems
the right choice to address accidentally or intentionally
preallocating too many files, eating up storage, and affecting
checkpoints (even though only a little). A simple note in the docs
would suffice: the number of WAL files preallocated by this function
is limited by max_wal_size, and if a benchmark or bulk load operation
requires more WAL files to be preallocated, increase max_wal_size
accordingly. I know this sounds a bit complex, but it addresses the
too-many-files eating up storage issue. And that should be okay IMO.
--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com
Hi,
On Sun, 16 Aug 2026 at 05:51, Bharath Rupireddy <
bharath.rupireddyforpostgres@gmail.com> wrote:
Hi,
On Fri, Aug 14, 2026 at 7:16 AM Ayush Tiwari
<ayushtiwari.slg01@gmail.com> wrote: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.
I think a few milliseconds per 6K+ WAL files should be okay. I'm okay
with this. Thanks for measuring this.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.
I don't think taking the control file lock (which
IsInstallXLogFileSegmentActive() acquires) in this SQL function path
is a good idea. It can easily increase lock contention on the control
file lock, which is used internally to update important control file
fields.This boils down to the following: when the database is not in recovery
(that is, RecoveryInProgress() is false), can the startup process ever
block new WAL file creation? It turns out it cannot, so the invariant
is Assert(RecoveryInProgress() || IsInstallXLogFileSegmentActive()). I
would just keep this in pg_wal_preallocate():if (RecoveryInProgress())
ereport(ERROR .....);/*
* When idatabase is not in recovery, new WAL file creation is not
* blocked by the startup process, so assert that invariant here.
*/
Assert(IsInstallXLogFileSegmentActive());
Thanks, done this way.
4/
Docs: + space occupied for a long time. On file systems where recycling a WAL file + is not cheaper than creating a new one (for examplecopy-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 stillbenefit
when zero-fill is enabled, but little benefit should be expected when it
is
disabled. Will edit it.
Sounds good, but use the GUC name directly instead of "zero-fill" and
avoid saying "little benefit should be expected."
I've edited the commit message and docs, to sound better.
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.Thinking about it more, I'm fine to keep a separate function and leave
PreallocXlogFiles() as-is to avoid any backpatching issues. Just name
the new function something like PreallocNXlogFiles() and keep it
closer to PreallocXlogFiles() in the code.
Sounds good.
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?Sounds good. Just keep the test comments and descriptions minimal with
key details.
Done.
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 hearif
others prefer removing the escape hatch entirely.
Increasing max_wal_size alone doesn't block checkpoints, timeout-based
checkpoints can still occur. So, getting rid of the force option seems
the right choice to address accidentally or intentionally
preallocating too many files, eating up storage, and affecting
checkpoints (even though only a little). A simple note in the docs
would suffice: the number of WAL files preallocated by this function
is limited by max_wal_size, and if a benchmark or bulk load operation
requires more WAL files to be preallocated, increase max_wal_size
accordingly. I know this sounds a bit complex, but it addresses the
too-many-files eating up storage issue. And that should be okay IMO.
Upon thinking more, I've removed the force option for now.
We can revisit/add it if and when need be, adding something like that
is easier than removing it later.
v6 attached. Thoughts?
Regards,
Ayush