Re: truncate a table instead of vaccum full when count(*) is 0

Heikki Linnakangas <heikki 'at' enterprisedb.com> writes:

> Pomarede Nicolas wrote:
> > But for the data (dead rows), even running a vacuum analyze every
> > day is not enough, and doesn't truncate some empty pages at the end,
> > so the data size remains in the order of 200-300 MB, when only a few
> > effective rows are there.
>
> For a table like that you should run VACUUM much more often than once
> a day. Turn on autovacuum, or set up a cron script etc. to run it
> every 15 minutes or so.

Heikki, is there theoretical need for frequent VACUUM when
max_fsm_pages is large enough to hold references of dead rows?

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?

--
Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company
Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36