PostgreSQL generates many WAL files

Question

We have PostgreSQL server running with a couple of databases around 6.6 GiB in total. Growing about 10 Mb every day.

I have activated WAL archiving using a very basic setup:

wal_level = archive
archive_mode = on
archive_command = 'rsync -a %p barman@backupserver:/var/lib/barman/company/incoming/%f'

Yet I see the WAL size growing very fast.

20160921T210006 - Wed Sep 21 21:15:22 2016 - Size: 6.6 GiB - WAL Size: 14.5 GiB  
20160920T115237 - Tue Sep 20 12:07:44 2016 - Size: 6.6 GiB - WAL Size: 7.4 GiB

More then double of our total size of databases. So many update are not/should be done. What could be the problem and how can we find the reason?

Answer 1

That's quite expected on a heavily updated database. Every update generates a log entry for the table and for each of table indexes if any indexed column was updated (if it could not perform a HOT update).

You can limit the amount of generated WAL's somewhat by using wal_compression configuration option (available since PostgreSQL 9.5). And you can further (or instead) reduce amount of WAL with external compression tool, something like:

archive_command = 'xz -1 --force %p && rsync -a %p.xz barman@backupserver:/var/lib/barman/company/incoming/%f.xz'

You'd need a corresponding restore_command which does the opposite.