We have a relatively low-volume Postgres database with continuous archiving set up to compress each WAL segment and send it to S3. Because it's a low-volume system, it hits an
archive_timeout every 10 minutes or so and archives the mostly-unused WAL segment, which used to compress very well as it was mostly just zeroes.
However, Postgres recycles its WAL segments to avoid the cost of allocating new files at each WAL switch, which is useful in a high-load situation but it means that after a burst of heavier-than-normal activity our WAL segment files are now full of junk from previous segments and do not compress very well at all. We're storing a lot of copies of all of this junk.
Is there a way to reduce the amount of space we're using to keep our WAL archive? Some suboptimal possibilities:
Prevent Postgres from recycling the WAL segments somehow, so it starts out with a zeroed file each time. The docs do not indicate that there is an option for doing this but I might have missed it.
Have Postgres zero the WAL segment file when it starts/finishes using it. Again, the docs do not seem to suggest this is possible.
Externally zero or remove some of the WAL segment files while they're not in use. Is there a safe way to determine which files this is?
Zero the unused portion of the segment before archiving it using the output from
pg_xlogdumpto find where the junk starts. Possible, although I don't fancy it. At least by doing this in the archive command you can be sure that Postgres isn't going to reuse the file.
Only archive the used portion of the segment file, again by interpreting the output of
pg_xlogdumpsomehow, and then pad it with zeroes during restore. Also sounds possible although I don't really fancy it.