>>> On Thu, Sep 6, 2007 at 3:25 PM, in message
<1189110327.9243.17.camel@dogma.ljc.laika.com>, Jeff Davis <pgsql@j-davis.com>
wrote:
> On Thu, 2007-09-06 at 12:08 +0100, Heikki Linnakangas wrote:
>> With file-based log shipping, you can get down to 1 second, by using the
>> archive_timeout setting. It will produce a lot of log files with very
>> little content in them, but they will compress well.
>
> I tried doing a couple pg_switch_xlog() in quick succession, and the WAL
> files that represent less than a second of actual data don't seem much
> more compressible than the rest.
Agreed. We kick out a WAL file once per hour (if they don't fill first)
and have found that a compressed WAL file during normal activity averages
a little over 4 MB. During the nightly VACUUM ANALYZE we get a few over
10 MB. The interesting thing is that about the time that these WAL files
would get reused, even when the system is idle, they are at the VACUUM
ANALYZE size again. Note how the first three 18:00 file sizes echo at the
next morning, before the users are in and working. 5293110 2007-08-22 17:14 000000010000001800000044.gz 5205720
2007-08-2217:14 000000010000001800000045.gz 5104595 2007-08-22 17:14 000000010000001800000046.gz 3747524 2007-08-22
17:14000000010000001800000047.gz 3118762 2007-08-22 17:14 000000010000001800000048.gz 3342918 2007-08-22 17:14
000000010000001800000049.gz4600438 2007-08-22 17:14 00000001000000180000004A.gz 2715708 2007-08-22 17:15
00000001000000180000004B.gz2865803 2007-08-22 17:15 00000001000000180000004C.gz10111700 2007-08-22 18:00
00000001000000180000004D.gz115616302007-08-22 18:00 00000001000000180000004E.gz11619590 2007-08-22 18:00
00000001000000180000004F.gz7165231 2007-08-22 18:00 000000010000001800000050.gz 4012164 2007-08-22 18:00
000000010000001800000051.gz4590502 2007-08-22 18:00 000000010000001800000052.gz 3617203 2007-08-22 18:01
000000010000001800000053.gz3552210 2007-08-22 18:01 000000010000001800000054.gz 4006261 2007-08-22 18:01
000000010000001800000055.gz3538293 2007-08-22 18:01 000000010000001800000056.gz 4291776 2007-08-22 18:02
000000010000001800000057.gz4837268 2007-08-22 18:02 000000010000001800000058.gz 3948408 2007-08-22 19:02
000000010000001800000059.gz2714635 2007-08-22 20:02 00000001000000180000005A.gz 4989698 2007-08-22 21:02
00000001000000180000005B.gz5290729 2007-08-22 22:02 00000001000000180000005C.gz 5203306 2007-08-22 23:02
00000001000000180000005D.gz5101976 2007-08-23 00:02 00000001000000180000005E.gz 3745125 2007-08-23 01:02
00000001000000180000005F.gz3119218 2007-08-23 02:02 000000010000001800000060.gz 3340691 2007-08-23 03:02
000000010000001800000061.gz4599279 2007-08-23 04:02 000000010000001800000062.gz10110899 2007-08-23 05:02
000000010000001800000063.gz115558952007-08-23 06:02 000000010000001800000064.gz11587646 2007-08-23 07:02
000000010000001800000065.gz