From: Greg Smith <gsmith@gregsmith.com>
To: Gauri Kanekar <meetgaurikanekar@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Replication Syatem
Date: Tue, 29 Apr 2008 04:37:07 -0400 (EDT)
Message-ID: <Pine.GSO.4.64.0804290347560.20575@westnet.com> (raw)
In-Reply-To: <7e4ba9550804282246p73c62fc6y5e7cb7dda55a6b50@mail.gmail.com>
References: <7e4ba9550804280638ie81dc23w472785c83cd38fcf@mail.gmail.com>
<a2de01dd0804280658t68ac22dsf182d304a94de5a2@mail.gmail.com>
<7e4ba9550804280705q43c51e66l104a5ff9272bbd4d@mail.gmail.com>
<20080428162243.GN2970@commandprompt.com>
<7e4ba9550804282246p73c62fc6y5e7cb7dda55a6b50@mail.gmail.com>
On Tue, 29 Apr 2008, Gauri Kanekar wrote:
> We do vacuum full, as vacuum verbose analyse dont regain space for us.
Ah, now we're getting to the root of your problem here. You expect that
VACUUM should reclaim space.
Whenever you UPDATE a row, it writes a new one out, then switches to use
that version. This leaves behind the original. Those now unused rows are
what VACUUM gathers, but it doesn't give that space back to the operating
system.
The model here assumes that you'll need that space again for the next time
you UPDATE or INSERT a row. So instead VACUUM just keeps those available
for database reuse rather than returning it to the operating system.
Now, if you don't VACUUM frequently enough, this model breaks down, and
the table can get bigger with space that may never get reused. The idea
is that you should be VACUUMing up now unneeded rows at about the same
rate they're being re-used. When you don't keep up, the database can
expand in space that you don't get back again. The right answer to this
problem is not to use VACUUM FULL; it's to use regular VACUUM more often.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gsmith@gregsmith.com, meetgaurikanekar@gmail.com
Subject: Re: Replication Syatem
In-Reply-To: <Pine.GSO.4.64.0804290347560.20575@westnet.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