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

From: Ayush Tiwari <ayushtiwari(dot)slg01(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Add a pg_wal_preallocate() SQL function to eagerly create future WAL segments
Date: 2026-07-22 15:07:29
Message-ID: CAJTYsWXAPabSWBeT=bqJD+6w0D4-ozvoVoH7KiFUncM88bsQOg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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]; 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
<https://www.postgresql.org/message-id/20201225200953.jjkrytlrzojbndh5%40alap3.anarazel.de>

Regards,
Ayush

Attachment Content-Type Size
v1-0001-pg_wal_preallocate.patch application/octet-stream 17.4 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2026-07-22 15:10:04 Re: [PATCH] Batched clock sweep to reduce cross-socket atomic contention
Previous Message Christoph Berg 2026-07-22 14:52:29 Re: Allow pg_read_all_stats to see database size in \l+