WAL still kept in pg_xlog even long after heavy workload is done

Hello,

We're encountering some problems with WAL growth in production with
PostgreSQL 9.6.3 and 9.6.2. From what I know a WAL file can either be
recycled(and would be reused) or deleted.
We'd like to have better control over the amount of WAL that is kept around.
There were a few occasions where we had to resize partitions because
pg_xlog grew as much as it did.

According to the docs [1] there are some parameters in GUC (postgresql.conf) about this.
The parameters I've been able to identify are the following:

* wal_keep_segments
* max_wal_size
* min_wal_size

Our WAL grows a lot around the time of our product upgrades (that is,
when we upgrade to a new version of our database, so not a Pg upgrade,
just a newer version of our db schema, plpgsql code etc).
As part of this upgrade, we add new columns or have some large UPDATEs
on tables as big as 300M (but in one case we also have one with 1.5B rows).

I am seeing the following int he docs [3]

min_wal_size (integer)
As long as WAL disk usage stays below this setting, old WAL files are
always recycled for future use at a checkpoint, rather than removed.
This can be used to ensure that enough WAL space is reserved to handle
spikes in WAL usage, for example when running large batch jobs. The default
is 80 MB. This parameter can only be set in the postgresql.conf file or
on the server command line.

This sounds very familiar because, that's essentially what we're doing. There
are some large jobs that cause a lot of workload and changes and generate a lot of WAL.

So far, the way I interpret this is min_wal_size is the amount of WAL
recycled (that is kept around to be reused) and max_wal_size is the
total amount of WAL allowed to be kept on disk.

I would also like to interpret the default values of min_wal_size and max_wal_size.
So if I run the following query:

psql -c "select name, setting from pg_settings where name like '%wal_size';"

I get the following:

max_wal_size|2097152
min_wal_size|1048576

Do these two values look ok?

Both these values were generated by pgtune [4], but it seems like pgtune
thinks they're expressed by default in KB.
Looking at the PostgreSQL code, it seems to me that these two are
expressed in MB, at least that's what I understand when I see
GUC_UNIT_MB in the source code [6].

So maybe the pgtune fork we're using has a bug in the sense that it
produces an incorrect value for those two parameters? (should be in MB
but is expressed in KB, therefore much higher than what it should be).

Another question is, how can I use any of the checkpoint settings
to control the WAL that is kept around?

* checkpoint_timeout
* checkpoint_completion_target
* checkpoint_flush_after
* checkpoint_warning

=========

I actually tried something with these settings on a test environment.
I've used the following settings:

checkpoint_timeout = 40s
min_wal_size = 600MB
max_wal_size = 900MB

Then I've created a db named x1 and ran this on it four or five times.

pgbench -i -s 70 x1

The pg_xlog directory grew to 2.2G and after a few minutes, it decreased to 2.0G
After about 40 minutes it decreased to 1.4G and it's not going any lower.
I was expecting pg_xlog's size to be 600MB after the first WAL removal had run.
Should I expect that the size will eventually drop to 600MB or will it just sit there at 1.4G?

=========

Other thoughts:

I have looked a bit at Pg internals too, I'm seeing four functions
there that are responsible for removing WAL: XLogArchiveIsReady,
RemoveXlogFile, RemoveOldXlogFiles, XLOGfileslop.
All of these belong to /src/backend/access/transam/xlog.c

The only place in the code that seems to take a decision about how much
WAL to recycle and how much to remove is the function XLOGfileslop [2].

It seems like XLOGfileslop is an estimate for the number of WAL to keep
around(recycled WAL). Both max_wal_size and min_wal_size are used inside
XLOGfileslop.

As far as checkpoint_* GUC settings go, they seem to be involved as well.
So far, the only thing I know about checkpoints is that between
checkpoints, many WAL are created. The amount of WAL between checkpoints
can vary. I don't have a good understanding about the interplay between
checkpoints and WAL.

I'd be grateful for any thoughts on how to improve this, and better control
the amount of WAL kept in pg_xlog.

Thank you,
Stefan

[1] https://www.postgresql.org/docs/9.6/static/wal-configuration.html
[2] https://github.com/postgres/postgres/blob/0c5803b450e0cc29b3527df3f352e6f18a038cc6/src/backend/access/transam/xlog.c#L2258
[3] https://www.postgresql.org/docs/9.6/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-CHECKPOINTS
[4] https://github.com/kmatt/pgtune
[5] https://github.com/kmatt/pgtune/blob/master/pgtune#L560
[6] https://github.com/postgres/postgres/blob/f49842d1ee31b976c681322f76025d7732e860f3/src/backend/utils/misc/guc.c#L2268

Stefan Petrea
System Engineer

stefan(dot)petrea(at)tangoe(dot)com