pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-admin@postgresql.org
Subject: Re: Unexplained growth of tables
Date: Sun, 23 Jul 2006 11:09:28 -0400
Message-ID: <873bcstl1z.fsf@wolfe.cbbrowne.com> (raw)
References: <BF337097BDD9D849A2F4B818DDB2798702949D@stash.stackdump.local>

After a long battle with technology, kraj@illumen.com ("Benjamin Krajmalnik"), an earthling, wrote:
> Any assistance or guidance will be deeply appreciated.  I am pulling
> hairs on this one.

It needs to be vacuumed a lot more aggressively.  If you're using
pg_autovacuum, the defaults seem to be not quite aggressive enough.

Mind you, now that the table has grown to huge size, it won't be cut
down to size by a mere VACUUM ANALYZE.

To draw it back down to size, you need to run VACUUM FULL, which will
cut the number of tuples down to size, and then reindex the table,
which will "do a number" on the indices.

Alternatively, "CLUSTER [some index] on [table];" will reorganize the
whole thing at once, reshaping the table to conform with the specified
index, and regenerating all indices, at the cost that it (at present,
up to 8.1; I'm not sure about 8.2) doesn't follow MVCC and keep old,
but still-visible obsolete copies of tuples.

CLUSTER is the fast answer, assuming you haven't any old transactions
you need to keep in flight...
-- 
(format nil "~S@~S" "cbbrowne" "gmail.com")
http://linuxfinances.info/info/slony.html
The difference between a  child and a hacker  is the amount he  flames
about his toys.  -- Ed Schwalenberg



view thread (7+ messages)  latest in thread

Message-ID: <873bcstl1z.fsf@wolfe.cbbrowne.com>
Permalink:  ../873bcstl1z.fsf@wolfe.cbbrowne.com/
Also on:    postgresql.org/message-id/873bcstl1z.fsf@wolfe.cbbrowne.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: cbbrowne@acm.org
  Subject: Re: Unexplained growth of tables
  In-Reply-To: <873bcstl1z.fsf@wolfe.cbbrowne.com>
  References: <BF337097BDD9D849A2F4B818DDB2798702949D@stash.stackdump.local> <873bcstl1z.fsf@wolfe.cbbrowne.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox