From: Markus Schaber <schabi@logix-tt.com>
To: Matthew T. O'Connor <matthew@zeut.net>
Cc: Michael Riess <mlriess@gmx.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: Autovacuum / full vacuum
Date: Tue, 17 Jan 2006 15:30:14 +0100
Message-ID: <43CCFF76.8020801@logix-tt.com> (raw)
In-Reply-To: <43CCFA7E.1060307@zeut.net>
References: <dqigaf$1b98$1@news.hub.org>
<43CCC711.504@familyhealth.com.au>
<dqih4p$1clg$1@news.hub.org>
<43CCFA7E.1060307@zeut.net>
Hi, Matthew,
Matthew T. O'Connor wrote:
> I'm sure he did, but just because you don't have enough FSM space to
> capture all everything from your "burst", that doesn't mean that space
> can't be reclaimed. The next time a regular vacuum is run, it will once
> again try to fill the FSM with any remaining free space it finds in the
> table. What normally happens is that your table will never bee 100%
> free of dead space, normally it will settle at some steady state size
> that is small percentage bigger than the table will be after a full
> vacuum. As long as that percentage is small enough, the effect on
> performance is negligible.
This will work if you've a steady stream of inserts / updates, but not
if you happen to have update bulks that exhaust the FSM capacity. The
update first fills up all the FSM, and then allocates new pages for the
rest. Then VACUUM comes and refills the FSM, however, the FSM does not
contain enough free space for the next large bulk update. The same is
for deletes and large bulk inserts, btw.
So your table keeps growing steadily, until VACUUM FULL or CLUSTER comes
along to clean up the mess.
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
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: schabi@logix-tt.com, matthew@zeut.net, mlriess@gmx.de
Subject: Re: Autovacuum / full vacuum
In-Reply-To: <43CCFF76.8020801@logix-tt.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