| From: | Markos Fountoulakis <markos(at)planetscale(dot)com> |
|---|---|
| To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | [PATCH] basebackup: use sequential access advice for full-file reads |
| Date: | 2026-11-16 12:35:18 |
| Message-ID: | 71c24aaa-d0a4-4ea8-82be-854f80b63175@planetscale.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hello hackers,
This patch improves full-file base backup read performance. The idea is that since base backup reads use sequential offsets we can benefit by OS-level prefetching. On Linux, the POSIX_FADV_SEQUENTIAL hint increases the FS-level readahead window and can improve throughput for read-bound backups.
We do not apply the logic to incremental backups case, since the sequential advice may be detrimental to performance due to the potentially random read access pattern.
I have benchmarked this patch on an Amazon EC2 instance of type c5.4xlarge with a gp3 EBS volume with performance limits of 512MB/s and 4000 IOPS. The OS is Ubuntu 24.04 and the filesystem is ext4. The default readahead of the block device is 128KB at the OS level.
I built the dataset with pgbench with a scale factor of 100 and performed some additional simple updates for 100 seconds which resulted in a PGDATA directory size of 2591MiB:
===========================================
/usr/local/pgsql/bin/pgbench -s 100 -i test
/usr/local/pgsql/bin/pgbench --progress=1 -T 100 -j 64 -c 64 --skip-some-updates test
===========================================
At that point I stopped the postgres server and backed up the dataset (simple file copy) for reproducibility.
Before every pg_basebackup run I empty the linux page cache (sync ; echo 3 > /proc/sys/vm/drop_caches), start postgres server and time the backup while monitoring block device statistics via iostat. WAL path was disabled.
** Vanilla postgres run behaves as follows:
===========================================
$ time /usr/local/pgsql/bin/pg_basebackup --target=blackhole --wal-method=none --progress --checkpoint=fast
real 0m8.029s
===========================================
The effective throughput from statistics divided by time is 197MiB/s.
From block device statistics:
* maximum request size - 128KB
* maximum throughput - 220MiB/s
* average queue depth - 1.8
* average response time - 1.2 ms
** fadvise sequential enabled behaves as follows:
===========================================
$ time /usr/local/pgsql/bin/pg_basebackup --target=blackhole --wal-method=none --progress --checkpoint=fast
real 0m5.813s
===========================================
The effective throughput from statistics divided by time is 272MiB/s.
From block device statistics:
* maximum request size - 256KB
* maximum throughput - 300MiB/s
* average queue depth - 1.8
* average response time - 1.5 ms
The optimization seems to be working as expected, the maximum I/O request size is doubled and the backup is 38% faster. Backup performance is not doubled though, since there are other bottlenecks in play, e.g. many small files that don't allow for large prefetching and potential CPU serialization in the backup code.
POSIX_FADV_SEQUENTIAL exactly doubles the block device readahead /proc/ entry in linux, which is 128KB by default. ext4 uses this configuration entry and the observed behaviour matches expectations since maximum READ request size is 256KB during the backup. I/O queue depth remains the same, so nothing else changes in the I/O pattern. Important to note that EBS volumes have a maximum I/O size of 256KB so any larger requests would split into smaller ones and increase the queue depth as a side-effect.
I did an additional experiment by modifying global filesystem readahead from 128KB to 4MB as such:
===========================================
echo 4096 > /sys/block/nvme0n1/queue/read_ahead_kb
===========================================
** Vanilla postgres backup with this change performed as follows:
===========================================
postgres(at)ip-172-31-26-163:~$ time /usr/local/pgsql/bin/pg_basebackup --target=blackhole --wal-method=none --progress --checkpoint=fast
real 0m2.891s
===========================================
The effective throughput from statistics divided by time is 548MiB/s.
From block device statistics:
* maximum request size - 256KB
* maximum throughput - 837MiB/s
* maximum queue depth - 18
* maximum response time - 5.5 ms
Increasing the readahead globally to 4MB makes the backup 178% faster. We notice that the EBS throughput limits were temporarily exceeded thanks to burst credits that EBS has. It is clear that EBS latency demands higher I/O concurrency to fully saturate the underlying disk performance capacity. However, it is unclear how other (non-backup) workloads may suffer from such an increase of readahead globally. This just shows the potential maximum performance we can achieve during a simple backup.
In my opinion, this improvement is a net gain and not a trade-off. Doubling the OS-level readeahead only for those sequential accesses halves the number of I/O requests and doubles the I/O size in the kernel. This means less I/O interference and more efficient kernel block I/O, potentially increasing performance for all concurrent I/O workloads and not just for the postgres backup running.
Attached is the patch written by Matt Blewitt.
--
Markos Fountoulakis
PlanetScale
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-basebackup-use-sequential-access-advice-for-full-.patch | text/plain | 2.2 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Joao Rito | 2026-11-16 12:46:02 | [PATCH] Fix IMPORT FOREIGN SCHEMA resolving lookup names in pg_temp |
| Previous Message | Zhijie Hou | 2026-11-16 12:20:45 | Re: Document prepared transaction lock waits with two_phase subscriptions |