Content-Type: multipart/alternative;
	boundary="Apple-Mail=_44D8BD1B-A556-4AFD-815E-EBD55B026AC7"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.200.81.1.6\))
Subject: Re: Seeking guidance on extremely slow pg_restore despite strong I/O
 performance
Date: Fri, 5 Dec 2025 15:27:38 -0500
In-Reply-To: 
 <CAK-u2rSsjHKu3qdrGrzuogE3DcdP6A2rOUSr93x43v=vAQ34Vw@mail.gmail.com>
Content-Transfer-Encoding: 7bit
From: pg254kl@georgiou.vip
To: MentionTheElephant <MentionTheElephant@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Message-ID: <176496646088.6.4993161944894462260.1049766710@georgiou.vip>
References: 
 <CAK-u2rSsjHKu3qdrGrzuogE3DcdP6A2rOUSr93x43v=vAQ34Vw@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/176496646088.6.4993161944894462260.1049766710%40georgiou.vip>
Precedence: bulk


--Apple-Mail=_44D8BD1B-A556-4AFD-815E-EBD55B026AC7
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

If the dump was taken with pd_dump -Fd and pg_restore -j has no effect =
on restore time, that=E2=80=99s a good clue.
You can start with testing deferring checkpoints, by setting =
wal_max_size =3D 1TB and checkpoint_timeout =3D 10h, and see how this =
affects the pg_restore (should be limited by WAL write throughput).  =
Perhaps increase wal_buffers to 128MB.  The idea being to identify (by =
elimination) the write chock-point, before starting to tune for it.

Irrelevant for your problem, you should set the *_io_concurrency to 200 =
since you use SSDs.
pg_restore rebuilds indices so also make sure the settings relevant to =
index building are set appropriately (see max_parallel_* and =
*_io_concurrency)

Kiriakos Georgiou

> On Dec 5, 2025, at 5:30=E2=80=AFAM, MentionTheElephant - =
MentionTheElephant at gmail.com =
<mentiontheelephant_at_gmail_com_xpdkqvpqqa@simplelogin.co> wrote:
>=20
> Hello,
>=20
> I would greatly appreciate your insight into an issue where pg_restore
> runs significantly slower than expected, even though the underlying
> storage shows very high random write throughput. I am trying to
> understand which PostgreSQL mechanisms or system layers I should
> investigate next in order to pinpoint the bottleneck and improve
> restore performance.
>=20
> The central question is: What should I examine further to understand
> why checkpoint processing becomes the dominant bottleneck during
> restore, despite fsync=3Doff, synchronous_commit=3Doff, and excellent
> random write latency?
>=20
> Below is a detailed description of the environment, the behavior
> observed, the steps I have already taken, and the research performed
> so far.
>=20
> During pg_restore, execution time remains extremely long: around 2+
> hours using a custom-format dump and over 4 hours using directory
> format. The machine consistently demonstrates high random write
> performance (median latency ~5 ms, ~45k random write IOPS), yet
> PostgreSQL logs show very long checkpoints where the write phase
> dominates (hundreds to thousands of seconds). Checkpoints appear to
> stall the entire restore process.
>=20
> I have tested multiple combinations of dump formats (custom and
> directory) and parallel jobs (j =3D 1, 12, 18). The restore duration
> barely changes. This strongly suggests that the bottleneck is not
> client-side parallelism but internal server behavior=E2=80=94specificall=
y the
> checkpoint write phase.
>=20
> Example log excerpts show checkpoint write times consistently in the
> range of 600=E2=80=931100 seconds, with large numbers of buffers =
written (from
> hundreds of thousands to over 1.6 million). Sync times remain
> negligible because fsync is disabled, reinforcing the suspicion that
> PostgreSQL's internal buffer flushing and write throttling mechanisms
> are the source of slowdown, not WAL or filesystem sync.
>=20
> Given that:
>=20
> * Storage is fast,
> * fsync and synchronous commits are disabled,
> * full_page_writes is off,
> * wal_level is minimal,
> * autovacuum is off,
> * the restore is the only workload,
>=20
> I am trying to determine what further PostgreSQL internals or Linux
> I/O mechanisms may explain why these checkpoints are taking orders of
> magnitude longer than the device=E2=80=99s raw write characteristics =
would
> suggest.
>=20
> I am particularly looking for guidance on:
>=20
> * Whether backend or checkpointer write throttling may still be
> limiting write concurrency even during bulk restore,
> * Whether XFS on Hyper-V VHDX + LVM + battery-backed SSD could
> introduce any serialization invisible to raw I/O tests,
> * Whether certain parameters (e.g., effective_io_concurrency,
> maintenance_io_concurrency, wal_writer settings, combine limits,
> io_uring behavior) could unintentionally reduce write throughput,
> * Whether parallel pg_restore is inherently constrained by global
> buffer flushing behavior,
> * Any other PostgreSQL mechanisms that could cause prolonged
> checkpoint write durations even with crash-safety disabled.
>=20
> Below are the configuration values and environment details referenced =
above.
>=20
> Machine:
> Hyper-V VM
> 24 vCPU
> 80 GB RAM
> Ubuntu 24.04.3 (kernel 6.8.0-88)
> PostgreSQL 18.1
>=20
> Database size:
> ~700 GB across two tablespaces on separate disks (freshly restored)
>=20
> Storage layout:
> Each disk is its own VHDX
> LVM on battery-backed SSD array
> XFS for PGDATA
> Barriers disabled
>=20
> Random write performance (steady state):
> Median latency: 5.1 ms
> IOPS: ~45.6k
>=20
> Restore tests:
> pg_restore custom format: ~2h+
> pg_restore directory format: ~4h+
> Parallelism tested with j =3D 1, 12, 18, 24
>=20
> Representative checkpoint log entries:
> (write phases ranging 76=E2=80=931079 seconds, buffer writes up to =
1.6M)
>=20
> postgresql.conf (relevant parts):
> shared_buffers =3D 20GB
> work_mem =3D 150MB
> maintenance_work_mem =3D 8GB
> effective_io_concurrency =3D 1
> maintenance_io_concurrency =3D 1
> io_max_combine_limit =3D 512kB
> io_combine_limit =3D 1024kB
> io_method =3D io_uring
>=20
> fsync =3D off
> synchronous_commit =3D off
> wal_sync_method =3D fdatasync
> full_page_writes =3D off
> wal_compression =3D lz4
>=20
> checkpoint_timeout =3D 60min
> checkpoint_completion_target =3D 0.9
> max_wal_size =3D 80GB
> min_wal_size =3D 10GB
>=20
> effective_cache_size =3D 65GB
> autovacuum =3D off
> max_locks_per_transaction =3D 256
>=20
> If anyone has encountered similar behavior or can recommend specific
> PostgreSQL subsystems, kernel settings, or I/O patterns worth
> investigating, I would be very grateful for advice. My main goal is to
> understand why checkpoint writes are so slow relative to the
> hardware=E2=80=99s demonstrated capabilities, and how to safely =
accelerate the
> restore workflow.
>=20
> Thank you in advance for any guidance.
>=20
>=20
>=20


--Apple-Mail=_44D8BD1B-A556-4AFD-815E-EBD55B026AC7
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html aria-label=3D"message body"><head><meta http-equiv=3D"content-type" =
content=3D"text/html; charset=3Dutf-8"></head><body =
style=3D"overflow-wrap: break-word; -webkit-nbsp-mode: space; =
line-break: after-white-space;"><div>If the dump was taken with pd_dump =
-Fd and pg_restore -j has no effect on restore time, that=E2=80=99s a =
good clue.</div><div>You can start with testing deferring checkpoints, =
by setting wal_max_size =3D 1TB and checkpoint_timeout =3D 10h, and see =
how this affects the pg_restore (should be limited by WAL write =
throughput). &nbsp;Perhaps increase wal_buffers to 128MB. &nbsp;The idea =
being to identify (by elimination) the write chock-point, before =
starting to tune for it.</div><div><br></div><div>Irrelevant for your =
problem, you should set the *_io_concurrency to 200 since you use =
SSDs.</div><div>pg_restore rebuilds indices so also make sure the =
settings relevant to index building are set appropriately (see =
max_parallel_* and *_io_concurrency)</div><div>
<br class=3D"Apple-interchange-newline"><span style=3D"caret-color: =
rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: Monaco; font-size: 18px; =
font-style: normal; font-variant-caps: normal; font-weight: 400; =
letter-spacing: normal; orphans: auto; text-align: start; text-indent: =
0px; text-transform: none; white-space: normal; widows: auto; =
word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: =
none; display: inline !important; float: none;">Kiriakos Georgiou</span>
</div>
<div><br><blockquote type=3D"cite"><div>On Dec 5, 2025, at 5:30=E2=80=AFAM=
, MentionTheElephant - MentionTheElephant at gmail.com =
&lt;mentiontheelephant_at_gmail_com_xpdkqvpqqa@simplelogin.co&gt; =
wrote:</div><br =
class=3D"Apple-interchange-newline"><div><div>Hello,<br><br>I would =
greatly appreciate your insight into an issue where pg_restore<br>runs =
significantly slower than expected, even though the =
underlying<br>storage shows very high random write throughput. I am =
trying to<br>understand which PostgreSQL mechanisms or system layers I =
should<br>investigate next in order to pinpoint the bottleneck and =
improve<br>restore performance.<br><br>The central question is: What =
should I examine further to understand<br>why checkpoint processing =
becomes the dominant bottleneck during<br>restore, despite fsync=3Doff, =
synchronous_commit=3Doff, and excellent<br>random write =
latency?<br><br>Below is a detailed description of the environment, the =
behavior<br>observed, the steps I have already taken, and the research =
performed<br>so far.<br><br>During pg_restore, execution time remains =
extremely long: around 2+<br>hours using a custom-format dump and over 4 =
hours using directory<br>format. The machine consistently demonstrates =
high random write<br>performance (median latency ~5 ms, ~45k random =
write IOPS), yet<br>PostgreSQL logs show very long checkpoints where the =
write phase<br>dominates (hundreds to thousands of seconds). Checkpoints =
appear to<br>stall the entire restore process.<br><br>I have tested =
multiple combinations of dump formats (custom and<br>directory) and =
parallel jobs (j =3D 1, 12, 18). The restore duration<br>barely changes. =
This strongly suggests that the bottleneck is not<br>client-side =
parallelism but internal server behavior=E2=80=94specifically =
the<br>checkpoint write phase.<br><br>Example log excerpts show =
checkpoint write times consistently in the<br>range of 600=E2=80=931100 =
seconds, with large numbers of buffers written (from<br>hundreds of =
thousands to over 1.6 million). Sync times remain<br>negligible because =
fsync is disabled, reinforcing the suspicion that<br>PostgreSQL's =
internal buffer flushing and write throttling mechanisms<br>are the =
source of slowdown, not WAL or filesystem sync.<br><br>Given =
that:<br><br>* Storage is fast,<br>* fsync and synchronous commits are =
disabled,<br>* full_page_writes is off,<br>* wal_level is minimal,<br>* =
autovacuum is off,<br>* the restore is the only workload,<br><br>I am =
trying to determine what further PostgreSQL internals or Linux<br>I/O =
mechanisms may explain why these checkpoints are taking orders =
of<br>magnitude longer than the device=E2=80=99s raw write =
characteristics would<br>suggest.<br><br>I am particularly looking for =
guidance on:<br><br>* Whether backend or checkpointer write throttling =
may still be<br>limiting write concurrency even during bulk =
restore,<br>* Whether XFS on Hyper-V VHDX + LVM + battery-backed SSD =
could<br>introduce any serialization invisible to raw I/O tests,<br>* =
Whether certain parameters (e.g., =
effective_io_concurrency,<br>maintenance_io_concurrency, wal_writer =
settings, combine limits,<br>io_uring behavior) could unintentionally =
reduce write throughput,<br>* Whether parallel pg_restore is inherently =
constrained by global<br>buffer flushing behavior,<br>* Any other =
PostgreSQL mechanisms that could cause prolonged<br>checkpoint write =
durations even with crash-safety disabled.<br><br>Below are the =
configuration values and environment details referenced =
above.<br><br>Machine:<br>Hyper-V VM<br>24 vCPU<br>80 GB RAM<br>Ubuntu =
24.04.3 (kernel 6.8.0-88)<br>PostgreSQL 18.1<br><br>Database =
size:<br>~700 GB across two tablespaces on separate disks (freshly =
restored)<br><br>Storage layout:<br>Each disk is its own VHDX<br>LVM on =
battery-backed SSD array<br>XFS for PGDATA<br>Barriers =
disabled<br><br>Random write performance (steady state):<br>Median =
latency: 5.1 ms<br>IOPS: ~45.6k<br><br>Restore tests:<br>pg_restore =
custom format: ~2h+<br>pg_restore directory format: ~4h+<br>Parallelism =
tested with j =3D 1, 12, 18, 24<br><br>Representative checkpoint log =
entries:<br>(write phases ranging 76=E2=80=931079 seconds, buffer writes =
up to 1.6M)<br><br>postgresql.conf (relevant parts):<br>shared_buffers =3D=
 20GB<br>work_mem =3D 150MB<br>maintenance_work_mem =3D =
8GB<br>effective_io_concurrency =3D 1<br>maintenance_io_concurrency =3D =
1<br>io_max_combine_limit =3D 512kB<br>io_combine_limit =3D =
1024kB<br>io_method =3D io_uring<br><br>fsync =3D =
off<br>synchronous_commit =3D off<br>wal_sync_method =3D =
fdatasync<br>full_page_writes =3D off<br>wal_compression =3D =
lz4<br><br>checkpoint_timeout =3D 60min<br>checkpoint_completion_target =
=3D 0.9<br>max_wal_size =3D 80GB<br>min_wal_size =3D =
10GB<br><br>effective_cache_size =3D 65GB<br>autovacuum =3D =
off<br>max_locks_per_transaction =3D 256<br><br>If anyone has =
encountered similar behavior or can recommend specific<br>PostgreSQL =
subsystems, kernel settings, or I/O patterns worth<br>investigating, I =
would be very grateful for advice. My main goal is to<br>understand why =
checkpoint writes are so slow relative to the<br>hardware=E2=80=99s =
demonstrated capabilities, and how to safely accelerate the<br>restore =
workflow.<br><br>Thank you in advance for any =
guidance.<br><br><br><br></div></div></blockquote></div><br></body></html>=

--Apple-Mail=_44D8BD1B-A556-4AFD-815E-EBD55B026AC7--