From: Scott Marlowe <smarlowe@g2switchworks.com>
To: adey <adey11@gmail.com>
Cc: Joel Stevenson <joelstevenson@mac.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: vacuumdb exclude tables option?
Date: Thu, 10 Aug 2006 09:16:59 -0500
Message-ID: <1155219418.20252.137.camel@state.g2switchworks.com> (raw)
In-Reply-To: <1c66bda80608092101m272dc6d3pd0145d4fd640cc3@mail.gmail.com>
References: <p06230915c0ffd2f2d63b@192.168.0.9>
<1155148097.20252.106.camel@state.g2switchworks.com>
<1c66bda80608092101m272dc6d3pd0145d4fd640cc3@mail.gmail.com>
On Wed, 2006-08-09 at 23:01, adey wrote:
> Does autovacuum replace the need for a FULL vacuum please (to recover
> free space, etc)?
In most cases, yes. The idea is that autovacuum should put the database
into a "steady state" where there is some % of each table that is free
space and being recycled without the table growing or shrinking due to
vacuuming.
But it doesn't always work out that way. If you're updating the table
very quickly, then you might outrun auto vacuum and get into a sticky
situation.
The somewhat bad situation is that your FSM settings are high enough to
store all the empty bits for reuse, but there's a LOT of them in some
often updated table. That's a bit bothersome.
The worst case scenario is that your FSM settings are not high enough,
and you've not got a table which has an increasing number of empty
spaces in it, even though the amount of data is steady state.
Generally, there are autovacuum settings and fsm settings that will keep
this from happening, but once you've got bloat, the only fix is vacuum
full, cluster, or reindex. I.e. something that copies the data into a
new file for you. All three of those are nasty in terms of the locks
they have to take out, and they block other access on the table while
running.
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: smarlowe@g2switchworks.com, adey11@gmail.com, joelstevenson@mac.com
Subject: Re: vacuumdb exclude tables option?
In-Reply-To: <1155219418.20252.137.camel@state.g2switchworks.com>
References: <p06230915c0ffd2f2d63b@192.168.0.9> <1155148097.20252.106.camel@state.g2switchworks.com> <1c66bda80608092101m272dc6d3pd0145d4fd640cc3@mail.gmail.com> <1155219418.20252.137.camel@state.g2switchworks.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