VACUUM FULL memory requirements

From: David Schnur <dnschnur(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: VACUUM FULL memory requirements
Date: 2009-12-14 14:56:30
Message-ID: 50000b2e0912140656k124a8a8bw987b67487d991c7a@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello,

I have a weekly task set up to VACUUM FULL a fairly large (~300M row ~50GB)
database. The intent is to free up disk space from especially-large inserts
that aren't covered by the regular reclamation from a daily VACUUM.
Recently, I've been getting the following error:

(OperationalError) out of memory
DETAIL: Failed on request of size 330.

The PG version is 8.3.5; can't upgrade to 8.4 at this time. The machine is
running a reasonably patched RHEL5, and should have at least 1-2GB RAM free
at the time VACUUM is run, plus some additional GB of swap.

Some googling reveals others with the same problem, including one earlier
mailing list discussion that recommended using CLUSTER instead. As I
understand it, though, CLUSTER requires at least as much free disk space as
the size of the largest table, which occupies most of the total size of the
database.

Is there any documentation on the memory & disk usage requirements of the
more intensive commands, like VACUUM, VACUUM FULL, CLUSTER, etc.? I would
find this very useful, since, regarding my VACUUM v.s. CLUSTER decision, for
example, I seem to be groping around without really understanding the
situation. I've always been under the impression that VACUUM FULL required
few resources besides time.

I realize that the long-term solution is probably to partition the table,
but in the short-term, is there anything I can do about this? Thanks very
much,

David

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Kevin Grittner 2009-12-14 17:04:37 Re: VACUUM FULL memory requirements
Previous Message Thorne, Francis 2009-12-14 13:54:12 Re: AIX 5.3 Out of Memory Error - 64-bit