Hi, I'm implementing an improvement for incremental backups (.txt patch attached) which I think is relevant to this thread.
Context: I noticed pg_basebackup incremental backups can sometimes be very slow, sometimes even slower than a full backup. The more scattered the changed blocks, the worse it gets. Looking at disk metrics and the code I found one of the reasons is that delta blocks are requested serially, each changed block read strictly one at a time in a loop. A simple fix, while still keeping the single-threaded design, was to issue posix_fadvise() calls for upcoming blocks before the code gets to them, in the hope that the kernel brings them into cache before they're actually read. A few results so far, on a ~51GB cluster: ~10 min of pgbench running, then taking an incremental backup of 28GB took 2min 51s (~167.7 MiB/s). With the patched code: 2min 18s (~207.8 MiB/s), ~24% higher. With more scattered changes (via TABLESAMPLE SYSTEM(20), touching ~20% of a table uniformly at random): 31GB incremental in 4min 22s (~121.2 MiB/s). With the patched code: 2min 32s (~208.8 MiB/s), ~72% higher. Short overview of the patch: 1. Before iterating over the delta blocks, fadvise the first N blocks. 2. After each iteration (each block read), fadvise the block at positioncurrent_block + N. 3. Repeat this every iteration until the end. This way, we're always at least N blocks ahead of the current one. For now, N = maintenance_io_concurrency. The logic is similar to patch 0005 from Jakub, but for the random read pattern instead. I intend to keep working on this in a couple of days (add tests, collect metrics), but wanted to share where it stands now in case anyone has thoughts in the meantime. Thanks, Gustavo Oliveira Em sex., 28 de ago. de 2026 às 17:16, Jakub Wartak < [email protected]> escreveu: > Hi, > > While investigating pg_basebackup performance, I found that a significant > amount of CPU is being wasted on both the server and client sides, and that > there are substantial opportunities to improve throughput without > fundamentally changing the design (so without abandoning the simple > single-threaded, single-connection design). Keeping that design intact also > lets us answer some of the questions raised in the old parallel-backup > thread > [1]. The attached patches implement a couple of changes to make > benchmarking > easier (0001-0003), some small optimizations that make a significant > difference in my production-like testing (0004-0008), and then implement > io_uring (Direct I/O + Async I/O) support for pg_basebackup (0009-0010). > > First, a quick performance demonstration, from the AWS benchmarks > described in > full later (2x c6in.8xlarge, specs in [4]), when taking a backup without > checksum verification/SSL, while still using single socket/fd: > > master + 0001-0003, writing to disk: ~1610MB/s. > master + all patches, with MPTCP: 2750-2975MB/s. > > That's like ~1.85x speedup on real cloud hardware. What I found is that the > gains come from several independent sources: reducing syscall numbers, > avoiding redundant memory copies in libpq, and using Direct I/O with > asynchronous submission on the client side. Above such rates, I believe we > genuinely need independent connections and a parallel-backup design, but > everything up to that point is achievable with relatively low-invasive > changes. > > OK, now let's go through the patches: > > 0001 and 0002 extend the existing server-side --target blackhole concept > into > separate server and client (pg_basebackup) blackholes, so that each stage > of > the data transfer pipeline can be measured reliably. Together with 0003, > this > gives DBAs a simple way to locate a bottleneck using nothing but the tool > itself: > > -t server-blackhole measures the server's raw read speed > -t client-blackhole additionally sends the data over the network and but > discard writes; and a plain(classic) -Fp -D /path run exposes all three > potential bottlenecks at once. > > 0003 adds a simple timing message with the average transfer rate. The idea > is > that if we're going to discuss numbers on this thread, it's better if the > tool > measures them the same way for everyone, rather than each of us relying on > atop or other tooling. > > 0004 increases SINK_BUFFER_LENGTH from its current 32 kB. Profiling the > server side shows a huge number of small pread() calls coming from > basebackup_read_file(), which is surprising given that elsewhere in the > tree > (backend/storage/buffer/README) we already mention that a 256 kB ring for > seq. > scans scans is used because it fits comfortably in L2 cache. On my laptop, > with no SSL, no checksum generation or verification, a hot filesystem > cache, > and a client blackhole to eliminate client I/O, a 10 GB backup over > loopback > runs at: > 3.1 GB/s with the 32 kB buffer, > 5.1 GB/s at 128 kB, > 5.5 GB/s at 256 kB, > 6.0 GB/s at 1 MB > (those are average of five runs). The win comes from letting pread() > swallow much larger chunks of each segment in one go: per-core L2 caches > are > 1-2 MB even on laptops these days, syscalls have become more expensive > since > the Spectre/Meltdown mitigations, and the kernel's default readahead is > already in the 128-512 kB range, so there's little reason to trickle the > data > through 32 kB at a time. I also experimented with raising > PQ_SEND_BUFFER_SIZE > from 8 kB to 128 kB, but saw no improvement and sometimes a regression -- > apparently it defeats libpq's direct-send optimization for large messages. > > 0005 issues posix_fadvise(POSIX_FADV_SEQUENTIAL) on the assumption that > segments are usually cold at backup time. Even with 0004 applied, > cold-cache > throughput on my laptop's NVMe goes from 1.5 GB/s to 2 GB/s with this > patch, > because the synchronous pread() calls see lower latencies (visible with > eBPF > funclatency). One question I tried to answer was whether it makes sense to > issue one fadvise call per file or many smaller ones (in the spirit of > maintenance_io_concurrency), but on Linux FADV_SEQUENTIAL does exactly one > thing -- it just widens the maximum readahead horizon -- so repeated calls > buy > nothing; it's simply handled with read-as-you-go by the kernel's own > readahead > heuristics. > > 0006 avoids buffering client-side writes with glibc stdio (FILE *). I > initially thought this is going to be as easy as a setvbuf(3) call, since > the > extractor path was issuing a mismatched 4 kB + 258 kB write pair per 256 kB > received. A 1MB setvbuf buffer produced the tidy syscall pattern I wanted, > but > it actually got slower when measured carefully on tmpfs: glibc's fwrite() > internally stats the file for its block size and introduces an extra > memcpy() > for large writes. Direct I/O would bypass this entirely, but that felt > premature at this stage, so the patch takes a more conservative approach. > Throughput is roughly unchanged (3.1 vs 3.2 GB/s to a ramdisk), but the > syscall pattern becomes a clean one-to-one recvfrom()/write() pairing, > which > matters for the later patches. > > 0007 preallocates output files via posix_fallocate(). This gave about 7% > essentially for free, though you need to already be writing fast (probably > more than of 1 GB/s) before it becomes visible. > > 0008 eliminates a redundant memory copy in the receive path. After the > fixes > above, perf showed PQgetCopyData()'s internal memcpy() consuming 60-70% of > pg_basebackup's single CPU: libpq allocates a buffer and copies the > incoming > data into it, only for the callback (ReceiveArchiveStreamChunk and > friends) to > immediately consume that same buffer at an offset of one byte, skipping the > protocol message byte. Since pg_basebackup is the only consumer here, the > copy > can simply be avoided. On the receive path the kernel already copies from > the > NIC via DMA to a kernel buffer and again into userspace, so above roughly > 10 > Gbps this extra PostgreSQL-side copy becomes very visible. With the copy > removed, a CPU-saturated pg_basebackup over localhost goes from 2.4 GB/s to > ~5.5 GB/s when not writing to disk; when writing even to a ramdisk, the > bottleneck shifts to fundamental kernel-side vfs_write() costs such as > cgroups > v2 memory accounting. > > 0009: Even with all of the above, there was still idle sequential write > bandwidth left on the table, and buffered writes couldn't reach it (without > some parallelism). Direct I/O alone (O_DIRECT) carried an unavoidable > latency > hit and wouldn't be usable here alone either, so it had be combined with > asynchronous submission, which means liburing (client-side only). So this > patch adds io_uring (Direct I/O) for larger files on the client side. The > patch adds PG_BASEBACKUP_NODIO too for experimenting with this (set/unset > it > to see the difference). > > 0010 fixes a serious regression that 0009 introduced for -Ft (tar) output. > Because the final archive length isn't known in advance -- unlike -Fp > (plain), > where each file's target size is known -- every Direct I/O write via > io_uring > also extended the file, triggering synchronous space allocation and > serializing on ext4's per-inode i_rwsem, which defeated the entire point > of a > deep async queue. The fix is proper posix_fallocate() for the tar path, > with a > fallback to buffered writes when preallocation isn't possible. It's a > separate > commit because it needed its own explanation: without it, tar output ran at > 410MB/s; with it, ~2400MB/s. > > Now, the real AWS benchmarks. These are averages of 3-5 runs with the > lowest outliers discarded, on 2x c6in.8xlarge with ENA Express (full specs > in > [4]), using --no-verify-checksums and --manifest-checksums=NONE, and no SSL > unless stated otherwise: > > stageI: hw baseline > =================== > a1. master + 000[123] -t server-blackhole # 11573MB/s (disk I/O possible > on serverside from hot pagecache) > a2. master + 000[123] -t client-blackhole # 1820MB/s (single-thread TCP > limit, > no writing client side) > a3. master + 000[123] -D /db/backup # ~1610MB/s (when writing, proper > backup) > > stageII: basic optimizations > ============================ > > +0004 SINK_BUFFER_LENGTH increase: > b. master + 000[1234] -t client-blackhole # 2158MB/s > > cold-cache scenarios (how efficient we are when data is not in the VFS > cache > server-side; echo 3 > drop_caches): > c1. master + 000[1234] -t client-blackhole # 1476MB/s > + 0005 posix_fadvise whole file: > c2. master + 000[12345] -t client-blackhole # 1610MB/s > > +0006, hot-cache scenarios again, real I/O by pg_basebackup, 100% CPU > throughout for the tests below: > d1. master + 000[12345] -D /db/backup # ~1639MB/s > d2. master + 000[123456] -D /db/backup # around the same, but we now issue > matched recvfrom()+write() pairs > > +0007 preallocate file > e. master + 000[1234567] -D /db/backup # ~1687MB/s > > +0008 avoid the second memcpy in libpq > f. master + 000[12345678] -D /db/backup # 1818MB/s (we cannot get more; > this saturates the AWS's ENA Express link on a single connection/ > technically maxes out AWS network fabric/SDN) > > crosscheck against baseline, just in case: > g. master + 000[123] -D /db/backup # 1613MB/s > > stageIII: MPTCP > =============== > I had to fall back to MPTCP [2] because the maximum single-stream TCP > bandwidth even with ENA Express fluctuated far too much to isolate. That > patch > is going to posted independently in its own thread [1], but attached here > too. > > h0. iperf3 max with MPTCP, single connection: 43Gbit/s (but fluctuating > down > to just 27Gbit/s sometimes; just 2-3 subflows) > h1. master + 000[123] + PGMPTCP=0 -t client-blackhole # 2079MB/s > h2. master + 000[123] + PGMPTCP=1 -t client-blackhole # 2770MB/s > h3. master + 000[123] + PGMPTCP=0 -D /db/backup # 1870MB/s, occasionally > slightly lower (ENA Express) > h4. master + 000[12345678] + PGMPTCP=1 -t client-blackhole # 3840MB/s, a > real indication that the earlier ceiling was just AWS network -- > though note we are not actually writing here > > stageIV: DIO+AIO > ================ > Because pg_basebackup was pinned at 100% CPU while the storage could still > take more, we need io_uring with DIO+AIO (+0009): > > i1. master + 000[123456789] + PGMPTCP=1 -D /db/backup # 2750-2975MB/s; we > max out MPTCP bandwidth with the core saturated by writing > i2. master + 000[123456789] + PGMPTCP=0 -D /db/backup # 1695MB/s, a sample > of the fluctuations (down from 1870MB/s) > i3. master + 000[123456789] + PGMPTCP=1 without --no-verify-checksums > # still 2772MB/s (server-side checksum calculation is fast enough!) > i4. master + 000[123456789] + PGMPTCP=1 without --no-verify-checksums and > with default CRC32C for manifest checksumming # still 2673MB/s > > So "i4." divided by "a3." gives 2975MB/ 1610 = ~1.85x , with some of the > 0009 > benefits greatly take from ideas in 0007 and 0008. > > There were several surprises in the above, at least to me -- I hadn't > anticipated hardware-assisted checksums being that fast. > > Tar (-Ft) output initially suffered badly from the synchronous space > allocation (because that was not done), as per details described under > 0010. > With proper posix_fallocate() it recovers performance. Without checksums > and > without SSL: > j1. master + 000[123456789] + PGMPTCP=1 -D /db/backup.tar -Ft # 410MB/s, > regression due to the lack of effective posix_fallocate() > j2. master + 000[123456789]+0010 + PGMPTCP=1 -D /db/backup.tar -Ft > # ~2400MB/s > > TLS > === > Some measurements of TLS itself in this setup (TLSv1.3, cipher > TLS_AES_256_GCM_SHA384): > k1. ssl=on + master + 000[123] + PGMPTCP=1 -D /db/backup > #baseline of ~1247MB/s > k2. ssl=on + master + 000[123456789] + PGMPTCP=1 -D /db/backup > #1668-1791MB/s > > On SSL specifically: the cipher alone can push close to 8-10 GB/s (64-80 > Gbps) on a single CPU with openssl -bench, and reducing the number of > encryption rounds doesn't appear to yield any real benefit. But since > SSL/TLS > is additional work performed on the __same__ thread, it imposes a clear > CPU overhead here: 1791 / 2975 = 0.60x. > > A few conclusions: > > - pg_basebackup today tops out at roughly 10-15 Gbps, because in the end we > are constrained by what a single core can do. > > - pg_basebackup is very vulnerable to single-TCP-flow performance on real > networks. These tests were intentionally run on real hardware and > networks > within a single zone -- the kind people actually use -- rather than on an > isolated network. See table [3] for apparent real-world limitations; > single-flow TCP is IMHO the top constraint today, often well below 10-25 > Gbps, and without MPTCP there is no simple way to get past it... > > - Some tuning is possible on our side without invasive protocol changes. > > - MPTCP is easy to set up and enabled almost everywhere today, but it has > its own ceiling: apparently ~40 Gbps in iperf3 and realistically ~25 Gbps > when we are also writing single-threaded. Potentially in far future we > could put recvfrom() (where MPTCP reassembly happens) on one thread and > issue SQs/io_uring_submit() from a second thread, but that would require > Thomas's pg_thr_*() APIs to be in place. > > - I researched SSL/kTLS/sendfile/zcrx a bit as well, but this email is > already too long, so that's not covered here. > > Based on the measurements and conclusions above, I think we can improve > pg_basebackup's single-threaded performance for now with these > relatively low-invasive patches. > > Thanks for reading that far. > > -J. > > [1] - > https://www.postgresql.org/message-id/20200420201922.55ab7ovg6535suyz%40alap3.anarazel.de > [2] - > https://www.postgresql.org/message-id/flat/CAKZiRmy6j9PBzDHZwdgwHavwKDzv5GWtRSWOTj6-jv6SCOZ%3DYA%40mail.gmail.com > [3] - a small audit of the single-TCP-connection speeds one can expect > depending on where one is running: > > Hardware type | Max aggr bw | 1x TCP LAN | 1x TCP MAN| 1x TCP > WAN > Physical server | <= 200 GbE | 1 CPU | 1-10Gbps | > 1-10Gbps? > VM (Xen, VMware, etc) | <= 100 GbE? | 1 CPU | 1-10Gbps | > 1-10Gbps? > lowend AWS VM | 5-40 Gbps | 5 Gbps | 5 Gbps | <= 5 > Gbps > lowend AWS VM Cluster Pl. | <= 200 Gbps | 10 Gbps | 5 Gbps | <= 5 > Gbps > highend AWS VM | <= 200 Gbps | 5 Gbps | 5 Gbps | <= 5 > Gbps > highend AWS VM+ENA Express| <= 200 Gbps | 25 Gbps | 25 Gbps | <= 5 > Gbps > lowend Azure VM | 3(!)-40 Gbps| 3(!) Gbps | 1.5-3 Gbps| 1.5-3 > Gbps > highend Azure VM+acc. net.| <= 200 Gbps | 10-12.5Gbps| 10 Gbps | 1.5-3 > Gbps > > [4] - HW: 2x c6in.8xlarge EC2 (each with 32 vCPUs, 64 GB RAM), scale > 4000 / 50 GB cluster size, 3x NVMe of 50 GB / 8k IOPS each in LVM/RAID0 > (lvcreate -i 3), ext4, tuned TCP stack (BBR), ENA Express enabled. In > theory > and sometimes in practice this gives a real ~23 Gbps, sometimes more like > 15 Gbps -- highly nondeterministic and dependent on zone, region, and time > of > day. Aggregate ~50 Gbps bandwidth was always available without > problems; only the single-flow TCP performance fluctuated, typically > between > 15 and 19 Gbps during these measurements, and I rather lost hope of it > being > deterministic. It seems to be a function of the env rather than the > configuration, which was identical throughout the excercies. I suspect > that on > raw hardware with >= 40 GbE interfaces higher speeds are possible, but the > intent here was to simulate what real customers actually see. > > sysctls: > net.core.rmem_max = 33554432 > net.core.wmem_max = 33554432 > net.core.rmem_default = 2097152 > net.core.wmem_default = 2097152 > net.ipv4.tcp_rmem = 4096 87380 33554432 > net.ipv4.tcp_wmem = 4096 65536 33554432 > net.ipv4.tcp_window_scaling = 1 > net.ipv4.tcp_sack = 1 > net.core.default_qdisc = fq > net.ipv4.tcp_congestion_control = bbr > mptcp enabled by default so no change >
From 3528976822bf6fdbe08b0cb34516e29568625f65 Mon Sep 17 00:00:00 2001 From: Gustavo William <[email protected]> Date: Sat, 22 Aug 2026 16:47:55 -0400 Subject: [PATCH] Prefetch upcoming blocks during incremental base backups Incremental base backups read each changed block one at a time, synchronously. Under workloads scattered changed blocks throughout the relation each one requires its own independent random-access read, paid for serially, one block after another. Close some of that gap by issuing posix_fadvise(POSIX_FADV_WILLNEED) hints for upcoming blocks before the read loop reaches them, giving the kernel a chance to fetch them into page cache asynchronously while we're still busy handling the current block. Hints are kept a bounded number of blocks ahead of the current read position, rather than issued for the whole file up front, sized by maintenance_io_concurrency. Signed-off-by: Gustavo William <[email protected]> --- src/backend/backup/basebackup.c | 74 ++++++++++++++++++++++++++++++++- 1 file changed, 73 insertions(+), 1 deletion(-) diff --git a/src/backend/backup/basebackup.c b/src/backend/backup/basebackup.c index e3c04ecd810..3b9d882a2fa 100644 --- a/src/backend/backup/basebackup.c +++ b/src/backend/backup/basebackup.c @@ -12,6 +12,7 @@ */ #include "postgres.h" +#include <fcntl.h> #include <sys/stat.h> #include <unistd.h> #include <time.h> @@ -38,6 +39,7 @@ #include "replication/slot.h" #include "replication/walsender.h" #include "replication/walsender_private.h" +#include "storage/bufmgr.h" #include "storage/bufpage.h" #include "storage/checksum.h" #include "storage/dsm_impl.h" @@ -106,6 +108,12 @@ static off_t read_file_data_into_buffer(bbsink *sink, BlockNumber blkno, bool verify_checksum, int *checksum_failures); +#if defined(USE_POSIX_FADVISE) && defined(POSIX_FADV_WILLNEED) +static void prefetch_next_incremental_run(int fd, + BlockNumber *incremental_blocks, + unsigned num_incremental_blocks, + unsigned *pf_index); +#endif static void push_to_sink(bbsink *sink, pg_checksum_context *checksum_ctx, size_t *bytes_done, void *data, size_t length); static bool backup_checksums_verifiable(XLogRecPtr start_lsn); @@ -1591,7 +1599,10 @@ sendFile(bbsink *sink, const char *readfilename, const char *tarfilename, pgoff_t bytes_done = 0; bool verify_checksum = false; pg_checksum_context checksum_ctx; - int ibindex = 0; + unsigned ibindex = 0; +#if defined(USE_POSIX_FADVISE) && defined(POSIX_FADV_WILLNEED) + unsigned pf_index = 0; +#endif if (pg_checksum_init(&checksum_ctx, manifest->checksum_type) < 0) elog(ERROR, "could not initialize checksum of file \"%s\"", @@ -1607,6 +1618,26 @@ sendFile(bbsink *sink, const char *readfilename, const char *tarfilename, errmsg("could not open file \"%s\": %m", readfilename))); } +#if defined(USE_POSIX_FADVISE) && defined(POSIX_FADV_WILLNEED) + + /* + * Get a head start on reading the blocks we're about to send: a + * bounded window of readahead hints, sized by the tablespace's + * maintenance_io_concurrency, rather than hinting the whole file at + * once. The window is topped up as we consume it further down. + */ + if (incremental_blocks != NULL) + { + int prefetch_target; + + prefetch_target = maintenance_io_concurrency; + + while (prefetch_target-- > 0 && pf_index < num_incremental_blocks) + prefetch_next_incremental_run(fd, incremental_blocks, + num_incremental_blocks, &pf_index); + } +#endif + _tarWriteHeader(sink, tarfilename, NULL, statbuf, false); /* @@ -1730,6 +1761,15 @@ sendFile(bbsink *sink, const char *readfilename, const char *tarfilename, * supposed to include. */ relative_blkno = incremental_blocks[ibindex++]; + +#if defined(USE_POSIX_FADVISE) && defined(POSIX_FADV_WILLNEED) + /* Keep the prefetch window topped up as we consume runs. */ + if (pf_index < num_incremental_blocks) + prefetch_next_incremental_run(fd, incremental_blocks, + num_incremental_blocks, + &pf_index); +#endif + cnt = read_file_data_into_buffer(sink, readfilename, fd, relative_blkno * BLCKSZ, BLCKSZ, @@ -1840,6 +1880,38 @@ sendFile(bbsink *sink, const char *readfilename, const char *tarfilename, return true; } +#if defined(USE_POSIX_FADVISE) && defined(POSIX_FADV_WILLNEED) + +/* + * Advance *pf_index past the next run of contiguous block numbers in + * incremental_blocks (which is sorted in ascending order, so a run can be + * found with a simple forward scan) and issue a single readahead hint + * covering that whole run. This is advisory only: posix_fadvise() failures + * are ignored, since the worst that happens is that we don't get the + * intended prefetching benefit. + */ +static void +prefetch_next_incremental_run(int fd, BlockNumber *incremental_blocks, + unsigned num_incremental_blocks, + unsigned *pf_index) +{ + BlockNumber run_start = incremental_blocks[(*pf_index)++]; + unsigned run_len = 1; + + /* Merge contiguous blocks into a single run. */ + while (*pf_index < num_incremental_blocks && + incremental_blocks[*pf_index] == run_start + run_len) + { + run_len++; + (*pf_index)++; + } + + (void) posix_fadvise(fd, (off_t) run_start * BLCKSZ, + (off_t) run_len * BLCKSZ, + POSIX_FADV_WILLNEED); +} +#endif + /* * Read some more data from the file into the bbsink's buffer, verifying * checksums as required. -- 2.43.5
