Re: WAL file size vs. data file size

On Oct 27, 2011, at 8:44 AM, Tom Lane wrote:

> Ben Chobot <bench(at)silentmedia(dot)com> writes:
>> Today I tried to restore a 70GB database with the standard "pg_dump -h old_server <∑> | psql -h new_server <∑>" method. I had 100GB set aside for WAL files, which I figured surely would be enough, because all of the data, including indices, is only 70GB. So I was a bit surprised when the restore hung mis-way because my pg_xlogs directory ran out of space.
>
>> Is it expected that WAL files are less dense than data files?
>
> Yes, that's not particularly surprising ... but how come they weren't
> getting recycled? Perhaps you had configured WAL archiving but it was
> broken?

It's because I'm archiving wal files into Amazon's S3, which is slooooooooooow. PG is recycling as fast as it can, but when a few MB of COPY rows seem to ballon up to a few hundred MB of WAL files, it has a lot to archive before it can recycle. It'll be fine for steady state but it looks like it's just going to be a waste for this initial load.

What's the expected density ratio? I was always under the impression it would be about 1:1 when doing things like COPY, and have never seen anything to the contrary.