Re: No control over max.num. WAL files

On Wed, May 25, 2011 at 01:37:47PM +0100, Simon Riggs wrote:
>
> That's the way SQLServer and Oracle work, but not PostgreSQL. We can
> clear down WAL files even during a long running transaction.
>
> For us, "unneeded" means prior to the second-to-last checkpoint record.

Well, they're obviously not getting cleared down, so they must be
needed.  I know how Postgres is supposed to work in these cases, but
in my experience you cannot rely on the OP's calculation to provide
you with a true maximum.  Pathological conditions result in a lot of
WAL segments hanging around.

What I really suspect is that this has to do with the way I/O
scheduling works, particularly in the presence of the bgwriter.  But I
don't feel comfortable suggesting particular reasons for what I've
experienced in production.  What I _can_ tell you is that, when I've
had to do large restores like this, I wanted plenty of overhead for
WAL.  ISTR dedicating 40G to WAL one time for a case like this.

A

--
Andrew Sullivan
ajs@crankycanuck.ca