From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Guillaume Cottenceau <gc@mnc.ch>
Cc: pgsql-performance@postgresql.org
Subject: Re: truncate a table instead of vaccum full when count(*) is 0
Date: Tue, 08 May 2007 12:19:06 +0100
Message-ID: <46405CAA.4030202@enterprisedb.com> (raw)
In-Reply-To: <871whro9r4.fsf@meuh.mnc.lan>
References: <Pine.LNX.4.64.0705081125140.20675@localhost>
<46404BEA.5060009@enterprisedb.com>
<871whro9r4.fsf@meuh.mnc.lan>
Guillaume Cottenceau wrote:
> Heikki, is there theoretical need for frequent VACUUM when
> max_fsm_pages is large enough to hold references of dead rows?
Not really, if you don't mind that your table with 10 rows takes
hundreds of megabytes on disk. If max_fsm_pages is large enough, the
table size will reach a steady state size and won't grow further. It
depends on your scenario, it might be totally acceptable.
> VACUUM documentation says: "tuples that are deleted or obsoleted
> by an update are not physically removed from their table; they
> remain present until a VACUUM is done".
>
> Free Space Map documentation says: "the shared free space map
> tracks the locations of unused space in the database. An
> undersized free space map may cause the database to consume
> increasing amounts of disk space over time, because free space
> that is not in the map cannot be re-used".
>
> I am not sure of the relationship between these two statements.
> Are these deleted/obsoleted tuples stored in the FSM and actually
> the occupied space is reused before a VACUUM is performed, or is
> something else happening? Maybe the FSM is only storing a
> reference to diskspages containing only dead rows, and that's the
> difference I've been missing?
FSM stores information on how much free space there is on each page.
Deleted but not yet vacuumed tuples don't count as free space. If a page
is full of dead tuples, it's not usable for inserting new tuples, and
it's not recorded in the FSM.
When vacuum runs, it physically removes tuples from the table and frees
the space occupied by them. At the end it updates the FSM.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
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: heikki@enterprisedb.com, gc@mnc.ch
Subject: Re: truncate a table instead of vaccum full when count(*) is 0
In-Reply-To: <46405CAA.4030202@enterprisedb.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