Increasing WAL Segment Size?

Question

Is it possible to increase the WAL segment size in an existing PostgreSQL 11 database? What are the disadvantages of having larger WAL segment sizes?

The reason for wanting to increase it from 16 MB is to increase the speed of transferring WAL backups when using WAL-E. 16 MB WAL files were transferring at a speed of about 20 MB/s (using SSDs in striped mirrored pairs) which is really slow for database sizes larger than 1 TB.

After increasing the WAL segment size, do we typically want to change the values for min_wal_size and max_wal_size?

Answer 1

You can choose a WAL segment size with initdb when you create a new cluster. So you would have to dump and restore the data, which means down time. Jeff Janes' answer explains that this can also be done with pg_resetwal.

The only disadvantage of a larger WAL segment size I can think of is that the amount of WAL you lose when the file system with pg_wal on it is lost will increase. But that is a small problem and can be worked around with pg_receivewal.

I cannot think of a reason to change max_wal_size or min_wal_size because of a different WAL segment size, but perhaps a value of wal_buffers that matches the segment size would be beneficial.

Answer 2

After shutting down the server cleanly, you can use pg_resetwal to change the wal segment size. However, I think that doing so will invalidate any streaming replicas you have set up, as well as create a discontinuity in any wal archive you might have, and so force a new base backup.

I don't think I would do this myself. I would just upgrade to v12, and create the new server with the desired size. Why create two discontinuities when you can have just one?

After increasing the WAL segment size, do we typically want to change the values for min_wal_size and max_wal_size?

min_wal_size needs to be at least twice the new segment size, so if it isn't already that high you will have to change it. Other than that, I don't see a reason to change them unless they weren't optimal to start with.