public inbox for [email protected]help / color / mirror / Atom feed
Re: wal segment size 5+ messages / 3 participants [nested] [flat]
* Re: wal segment size @ 2025-12-17 17:58 Laurenz Albe <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Laurenz Albe @ 2025-12-17 17:58 UTC (permalink / raw) To: Greg Sabino Mullane <[email protected]>; Colin 't Hart <[email protected]>; +Cc: PostgreSQL General <[email protected]> On Wed, 2025-12-17 at 12:21 -0500, Greg Sabino Mullane wrote: > On Wed, Dec 17, 2025 at 11:10 AM Colin 't Hart <[email protected]> wrote: > > Thanks Laurenz, that confirms what I was assuming. Archiving is via pgbackrest > > to a backup server, over SSH. Approx 750ms to archive each segment is crazy -- > > I'll check compression parameters too. > > Switch to archive-async = on. When doing that, the typical time drops to 10ms or less. > Also use a compress-type of lz4 or zst, which perform way better than the default gz. > If you are encrypting, that's a bottleneck you just have to deal with, no shortcuts there. :) I second that. Asynchronous archiving in pgBackRest tends to work around the problem. > tl;dr try other things before messing with the WAL size. The current size can work very > well even on very large and very, very busy systems. On the other hand, 16MB on a very busy system is somewhat ridiculous. A somewhat bigger segment size may be appropriate. Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: wal segment size @ 2025-12-19 08:26 Andrew <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Andrew @ 2025-12-19 08:26 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; Colin 't Hart <[email protected]>; PostgreSQL General <[email protected]> As an oracle dba new to Postgres, I’m used to the concept of context switches and latch issues with regards to transaction log switches. Does Postgres have a similar mechanism with latching etc when it switches to a new wal segment that is alleviated when increasing the size of the wal segments? Regards Andrew Sent from my iPhone > On 17 Dec 2025, at 18:58, Laurenz Albe <[email protected]> wrote: > > On Wed, 2025-12-17 at 12:21 -0500, Greg Sabino Mullane wrote: >>> On Wed, Dec 17, 2025 at 11:10 AM Colin 't Hart <[email protected]> wrote: >>> Thanks Laurenz, that confirms what I was assuming. Archiving is via pgbackrest >>> to a backup server, over SSH. Approx 750ms to archive each segment is crazy -- >>> I'll check compression parameters too. >> >> Switch to archive-async = on. When doing that, the typical time drops to 10ms or less. >> Also use a compress-type of lz4 or zst, which perform way better than the default gz. >> If you are encrypting, that's a bottleneck you just have to deal with, no shortcuts there. :) > > I second that. Asynchronous archiving in pgBackRest tends to work around the problem. > >> tl;dr try other things before messing with the WAL size. The current size can work very >> well even on very large and very, very busy systems. > > On the other hand, 16MB on a very busy system is somewhat ridiculous. > A somewhat bigger segment size may be appropriate. > > Yours, > Laurenz Albe > > ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: wal segment size @ 2025-12-19 13:09 Laurenz Albe <[email protected]> parent: Andrew <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Laurenz Albe @ 2025-12-19 13:09 UTC (permalink / raw) To: Andrew <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; Colin 't Hart <[email protected]>; PostgreSQL General <[email protected]> On Fri, 2025-12-19 at 09:26 +0100, Andrew wrote: > As an oracle dba new to Postgres, I’m used to the concept of context switches and latch issues > with regards to transaction log switches. Does Postgres have a similar mechanism with latching > etc when it switches to a new wal segment that is alleviated when increasing the size of the > wal segments? Not really. PostgreSQL doesn't reuse WAL segments in a circular fashion like Oracle does. At the end of a checkpoint, it creates new, empty WAL segments for future use, so if there is a need to switch to a new segment, there is no need to wait for anything. Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: wal segment size @ 2025-12-19 13:25 Colin 't Hart <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Colin 't Hart @ 2025-12-19 13:25 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: Andrew <[email protected]>; Greg Sabino Mullane <[email protected]>; PostgreSQL General <[email protected]> What's the behaviour when pg_resetwal is used to change the WAL segment size? This note is worrying to me: -- While pg_resetwal will set the WAL starting address beyond the latest existing WAL segment file, some segment size changes can cause previous WAL file names to be reused. It is recommended to use -l together with this option to manually set the WAL starting address if WAL file name overlap will cause problems with your archiving strategy. -- Why can a segment size change cause previous WAL file names to be reused? Do we need to take a new backup immediately after changing the WAL segment size? Thanks, Colin On Fri, 19 Dec 2025 at 14:09, Laurenz Albe <[email protected]> wrote: > On Fri, 2025-12-19 at 09:26 +0100, Andrew wrote: > > As an oracle dba new to Postgres, I’m used to the concept of context > switches and latch issues > > with regards to transaction log switches. Does Postgres have a similar > mechanism with latching > > etc when it switches to a new wal segment that is alleviated when > increasing the size of the > > wal segments? > > Not really. PostgreSQL doesn't reuse WAL segments in a circular fashion > like Oracle does. > At the end of a checkpoint, it creates new, empty WAL segments for future > use, so if there > is a need to switch to a new segment, there is no need to wait for > anything. > > Yours, > Laurenz Albe > ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: wal segment size @ 2025-12-19 14:48 Laurenz Albe <[email protected]> parent: Colin 't Hart <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Laurenz Albe @ 2025-12-19 14:48 UTC (permalink / raw) To: Colin 't Hart <[email protected]>; +Cc: Andrew <[email protected]>; Greg Sabino Mullane <[email protected]>; PostgreSQL General <[email protected]> On Fri, 2025-12-19 at 14:25 +0100, Colin 't Hart wrote: > What's the behaviour when pg_resetwal is used to change the WAL segment size? > > This note is worrying to me: > -- > While pg_resetwal will set the WAL starting address beyond the latest existing WAL segment file, some segment size changes can cause previous WAL file names to be reused. It is recommended to use -l together with this option to manually set the WAL starting address if WAL file name overlap will cause problems with your archiving strategy. > -- > Why can a segment size change cause previous WAL file names to be reused? > > Do we need to take a new backup immediately after changing the WAL segment size? I think that is supposed to mean that the new WAL numbering scheme might produce the same WAL segment name as a WAL segment name had long ago, so you might overwrite that earlier segment in the WAL archive, which could prevent you from recovering from an old backup that needs the overwritten WAl segment to recover. I'm not sure how likely that is to happen. It never harms to run an extra backup. The main thing is that you shut down PostgreSQL cleanly before running "pg_resetwal" and that you only change the WAL segment size, nothing else. Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-12-19 14:48 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-12-17 17:58 Re: wal segment size Laurenz Albe <[email protected]> 2025-12-19 08:26 ` Andrew <[email protected]> 2025-12-19 13:09 ` Laurenz Albe <[email protected]> 2025-12-19 13:25 ` Colin 't Hart <[email protected]> 2025-12-19 14:48 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox