PostgreSQL – Zeroing WAL Segments

postgresql

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_xlogdump to 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_xlogdump somehow, and then pad it with zeroes during restore. Also sounds possible although I don't really fancy it.

Best Answer

Starting in version 9.4, it now automatically zeroes the tail end of the WAL file. (Actually it is just mostly zero, there are some block headers that don't get zeroed, but still the result is very compressible).

In version 9.2, there is a program named pg_clearxlogtail you can use. You can add it into your archive_command before the compression step.

If you are using 9.3, you are out of luck.

Note that checkpoints do not inherently cause log file switches. It is probably archive_timeout which is causing the switches.

Related Solutions

Postgresql streaming replication and wal archive

Further testing revealed that the different WAL files were mostly those that had been rotated but not yet written to. The new slave would try to archive these (possibly fixed in 9.2) but they wouldn't be used for recovery.

The other problem is the overwriting. We disabled the overwrite (instead storing with a new filename so we at least keep the WAL just in case) and everything works... both failover and later restore.

NOTE: my earlier answer here that was completely wrong. See the edit log if you need to see how wrong.

PostgreSQL Backup – Decrease WAL File Size

If I recall correctly, PostgreSQL now zeroes the end of the WAL segment after an archive timeout, before archiving the file. (Update): This is true for 9.4; for prior versions you still need to use pg_clearxlogtail utility to zero the end of any WAL segment as part of your archive script before you gzip your archive files.

In 9.4 and above you just need to gzip the file, with no need for pg_clearxlogtail.

The change was made commit 9a20a9b, but it's only in tag REL9_4_BETA1, so the change will first appear in 9.4:

commit 9a20a9b
Author: Heikki Linnakangas <heikki.linnakangas@iki.fi>
Date:   Mon Jul 8 11:23:56 2013 +0300

    Improve scalability of WAL insertions.

    This patch replaces WALInsertLock with a number of WAL insertion slots,
    allowing multiple backends to insert WAL records to the WAL buffers
    concurrently. This is particularly useful for parallel loading large amounts
    of data on a system with many CPUs.

    This has one user-visible change: switching to a new WAL segment with
    pg_switch_xlog() now fills the remaining unused portion of the segment with
    zeros. This potentially adds some overhead, but it has been a very common
    practice by DBA's to clear the "tail" of the segment with an external
    pg_clearxlogtail utility anyway, to make the WAL files compress better.
    With this patch, it's no longer necessary to do that.

BTW, you should really be using an archive timeout, rather than manually switching xlogs. Consider having a streaming replica for additional protection.

Update: As Jeff noted, pg_clearxlogtail hasn't been updated for 9.3. The incompatibility is very simple though; the definition of XLogRecPtr changed from

typedef struct XLogRecPtr
{
        uint32          xlogid;                 /* log file #, 0 based */
        uint32          xrecoff;                /* byte offset of location in log file */
} XLogRecPtr;

typedef uint64 XLogRecPtr;

when support for platforms with compilers that don't support 64-bit long integers was removed from PostgreSQL in 9.3. So all we need to do is add a macro or function that tests equality in a way that works for both.

The following patch adds support for 9.3, but is completely untested so use entirely at your own risk. If it eats your data then sets fire to your house, well, that's how things are. Patch here, as an attachment to the linked pgfoundry issue.

Best Answer

Related Solutions

Postgresql streaming replication and wal archive

PostgreSQL Backup – Decrease WAL File Size

Related Question