Configuration changes in 9.5: transaction log size

If you downloaded 9.5 after yesterday's release, you might have noticed some changes to postgresql.conf, especially if you copied over you favorite 9.4 config and it refused to start up.  Particularly, the parameter checkpoint_segments is gone, and has been replaced by min_wal_size and max_wal_size.  What does this mean?

Well, checkpoint_segments was the old way we had for users to determine how "big" the transaction log should be.  Users with a large server or a lot of transactions per second would set it high, and users with small VMs and a slow database could set it low.  This had some problems, though:

1. The default setting worked for pretty much nobody, so you always had to adjust it.
2. The WAL*  always used the maximum space available, even if it wasn't needed.
3. Figuring out the largest size your WAL could be required some math and a knowledge of version-specific PostgreSQL internals.

The last was the most confusing part for users; the calculation for maximum WAL size was:

   ( ( checkpoint_segments * 2 ) + 1 )  * 16MB ) +  ( wal_keep_segments * 16MB )

... which meant that people generally sized it by picking an arbitrary number and then adjusting up or down based on feedback.

The new parameters are way simpler:

• min_wal_size: the minimum size the transaction log will be;
• max_wal_size: the maximum size the transaction log will be (but see below)

This means that your transaction log on disk shouldn't ever be larger than ( max_wal_size + wal_keep_segments ).  It is a "soft" limit though; if PostgreSQL gets really behind, or if archiving is failing, it will get higher than max_wal_size.

However, that isn't the really cool part.  Heikki did an amazing thing, in that the WAL is sized dynamically based on how much was used during the previous cycles.  So you can set max_wal_size to some high value (default is 1GB),  and not worry about PostgreSQL using a bunch of extra disk space if it's not needed.  This means that we can set a default which will be "good enough" for 80% of our users, and we have.  This makes me do a little happy dance.

The other 20% may want to tune, still though, so here's some guidelines:

• if you know your database write traffic is "bursty", with long periods of inactivity followed by furious writes, increase min_wal_size;
• if you do bulk loads larger than 1GB, increase max_wal_size to the size of a bulk load;
• if you write more than 1GB of data every 10 minutes, increase max_wal_size;
• if you get "checkpoints occurring to frequently" error messages, try increasing both parameters incrementally.

Most users won't need to touch those parameters at all, though.  Which is as it should be.

* WAL == Write Ahead Log == Transaction Log == XLog