Hi, On Tue, 4 Aug 2026 at 18:11, Ayush Tiwari <[email protected]> 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
