Thread

1. Seeking guidance on extremely slow pg_restore despite strong I/O performance

   MentionTheElephant <mentiontheelephant@gmail.com> — 2025-12-05T10:30:38Z

   Hello,
   
   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.
   
   The central question is: What should I examine further to understand
   why checkpoint processing becomes the dominant bottleneck during
   restore, despite fsync=off, synchronous_commit=off, and excellent
   random write latency?
   
   Below is a detailed description of the environment, the behavior
   observed, the steps I have already taken, and the research performed
   so far.
   
   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.
   
   I have tested multiple combinations of dump formats (custom and
   directory) and parallel jobs (j = 1, 12, 18). The restore duration
   barely changes. This strongly suggests that the bottleneck is not
   client-side parallelism but internal server behavior—specifically the
   checkpoint write phase.
   
   Example log excerpts show checkpoint write times consistently in the
   range of 600–1100 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.
   
   Given that:
   
   * 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,
   
   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’s raw write characteristics would
   suggest.
   
   I am particularly looking for guidance on:
   
   * 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.
   
   Below are the configuration values and environment details referenced above.
   
   Machine:
   Hyper-V VM
   24 vCPU
   80 GB RAM
   Ubuntu 24.04.3 (kernel 6.8.0-88)
   PostgreSQL 18.1
   
   Database size:
   ~700 GB across two tablespaces on separate disks (freshly restored)
   
   Storage layout:
   Each disk is its own VHDX
   LVM on battery-backed SSD array
   XFS for PGDATA
   Barriers disabled
   
   Random write performance (steady state):
   Median latency: 5.1 ms
   IOPS: ~45.6k
   
   Restore tests:
   pg_restore custom format: ~2h+
   pg_restore directory format: ~4h+
   Parallelism tested with j = 1, 12, 18, 24
   
   Representative checkpoint log entries:
   (write phases ranging 76–1079 seconds, buffer writes up to 1.6M)
   
   postgresql.conf (relevant parts):
   shared_buffers = 20GB
   work_mem = 150MB
   maintenance_work_mem = 8GB
   effective_io_concurrency = 1
   maintenance_io_concurrency = 1
   io_max_combine_limit = 512kB
   io_combine_limit = 1024kB
   io_method = io_uring
   
   fsync = off
   synchronous_commit = off
   wal_sync_method = fdatasync
   full_page_writes = off
   wal_compression = lz4
   
   checkpoint_timeout = 60min
   checkpoint_completion_target = 0.9
   max_wal_size = 80GB
   min_wal_size = 10GB
   
   effective_cache_size = 65GB
   autovacuum = off
   max_locks_per_transaction = 256
   
   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’s demonstrated capabilities, and how to safely accelerate the
   restore workflow.
   
   Thank you in advance for any guidance.
   
   
   
   

2. Re: Seeking guidance on extremely slow pg_restore despite strong I/O performance

   pg254kl@georgiou.vip — 2025-12-05T20:27:38Z

   If the dump was taken with pd_dump -Fd and pg_restore -j has no effect on restore time, that’s a good clue.
   You can start with testing deferring checkpoints, by setting wal_max_size = 1TB and checkpoint_timeout = 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 AM, MentionTheElephant - MentionTheElephant at gmail.com <mentiontheelephant_at_gmail_com_xpdkqvpqqa@simplelogin.co> wrote:
   > 
   > Hello,
   > 
   > 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.
   > 
   > The central question is: What should I examine further to understand
   > why checkpoint processing becomes the dominant bottleneck during
   > restore, despite fsync=off, synchronous_commit=off, and excellent
   > random write latency?
   > 
   > Below is a detailed description of the environment, the behavior
   > observed, the steps I have already taken, and the research performed
   > so far.
   > 
   > 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.
   > 
   > I have tested multiple combinations of dump formats (custom and
   > directory) and parallel jobs (j = 1, 12, 18). The restore duration
   > barely changes. This strongly suggests that the bottleneck is not
   > client-side parallelism but internal server behavior—specifically the
   > checkpoint write phase.
   > 
   > Example log excerpts show checkpoint write times consistently in the
   > range of 600–1100 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.
   > 
   > Given that:
   > 
   > * 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,
   > 
   > 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’s raw write characteristics would
   > suggest.
   > 
   > I am particularly looking for guidance on:
   > 
   > * 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.
   > 
   > Below are the configuration values and environment details referenced above.
   > 
   > Machine:
   > Hyper-V VM
   > 24 vCPU
   > 80 GB RAM
   > Ubuntu 24.04.3 (kernel 6.8.0-88)
   > PostgreSQL 18.1
   > 
   > Database size:
   > ~700 GB across two tablespaces on separate disks (freshly restored)
   > 
   > Storage layout:
   > Each disk is its own VHDX
   > LVM on battery-backed SSD array
   > XFS for PGDATA
   > Barriers disabled
   > 
   > Random write performance (steady state):
   > Median latency: 5.1 ms
   > IOPS: ~45.6k
   > 
   > Restore tests:
   > pg_restore custom format: ~2h+
   > pg_restore directory format: ~4h+
   > Parallelism tested with j = 1, 12, 18, 24
   > 
   > Representative checkpoint log entries:
   > (write phases ranging 76–1079 seconds, buffer writes up to 1.6M)
   > 
   > postgresql.conf (relevant parts):
   > shared_buffers = 20GB
   > work_mem = 150MB
   > maintenance_work_mem = 8GB
   > effective_io_concurrency = 1
   > maintenance_io_concurrency = 1
   > io_max_combine_limit = 512kB
   > io_combine_limit = 1024kB
   > io_method = io_uring
   > 
   > fsync = off
   > synchronous_commit = off
   > wal_sync_method = fdatasync
   > full_page_writes = off
   > wal_compression = lz4
   > 
   > checkpoint_timeout = 60min
   > checkpoint_completion_target = 0.9
   > max_wal_size = 80GB
   > min_wal_size = 10GB
   > 
   > effective_cache_size = 65GB
   > autovacuum = off
   > max_locks_per_transaction = 256
   > 
   > 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’s demonstrated capabilities, and how to safely accelerate the
   > restore workflow.
   > 
   > Thank you in advance for any guidance.
   > 
   > 
   > 
   
   

3. Re: Seeking guidance on extremely slow pg_restore despite strong I/O performance

   Jeff Janes <jeff.janes@gmail.com> — 2025-12-08T00:53:40Z

   On Fri, Dec 5, 2025 at 5:30 AM MentionTheElephant <
   MentionTheElephant@gmail.com> wrote:
   
   
   > The central question is: What should I examine further to understand
   > why checkpoint processing becomes the dominant bottleneck during
   > restore, despite fsync=off, synchronous_commit=off, and excellent
   
   random write latency?
   >
   
   I don't see any evidence that checkpoint is the dominant bottleneck.
   Checkpoints are not (usually) meant to be fast.  They pace themselves to
   finish in the allotted time, while causing the least amount of drama while
   they are underway.  The whole system shouldn't freeze up while waiting for
   a checkpoint to run.
   
   > Checkpoints appear to
   
   > stall the entire restore process.
   >
   
   What makes it appear that way? Is it just that they take a long time to
   finish, or is there some other evidence you haven't described?
   
   
   > Example log excerpts show checkpoint write times consistently in the
   > range of 600–1100 seconds, with large numbers of buffers written (from
   > hundreds of thousands to over 1.6 million).
   
   
   You told it to shoot for checkpoint_timeout * checkpoint_completion_target
   = 3600 * 0.9 = 3240 seconds.  If anything, it is weird that it takes so
   little time, compared to what you authorized it to take.  As for the number
   of buffers, it writes as many as it needs to write to get the job done.
   Are the checkpoints driven by time, or by WAL volume?
   
   The time reported for the write phase includes not only time spent doing
   the writes, but also time spent sleeping so as to pace the checkpoint to
   finish on schedule.  Perhaps those times should be reported separately.
   
   I think that you have misinterpreted the evidence you have on hand, and are
   now looking for an explanation for something which isn't true in the first
   place.
   
   What is the percent IO wait (called "wa") reported by `top` or `vmstat`
   while the restore is running?
   
   If you run with --verbose, you can see each step as it starts them. Which
   steps take the most time?
   
   Cheers,
   
   Jeff