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

On Tue, 8 May 2007, Guillaume Cottenceau wrote:

> Pomarede Nicolas <npomarede 'at' corp.free.fr> writes:
>
>> Hello to all,
>>
>> I have a table that is used as a spool for various events. Some
>> processes write data into it, and another process reads the resulting
>> rows, do some work, and delete the rows that were just processed.
>>
>> As you can see, with hundreds of thousands events a day, this table
>> will need being vaccumed regularly to avoid taking too much space
>> (data and index).
>>
>> Note that processing rows is quite fast in fact, so at any time a
>> count(*) on this table rarely exceeds 10-20 rows.
>>
>>
>> For the indexes, a good way to bring them to a size corresponding to
>> the actual count(*) is to run 'reindex'.
>>
>> 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.
>
> As far as I know, you probably need to increase your
> max_fsm_pages, because your pg is probably not able to properly
> track unused pages between subsequent VACUUM's.
>
> http://www.postgresql.org/docs/8.2/interactive/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-FSM
>
> Have you investigated this? It seems that you already know about
> the FSM stuff, according to your question about FSM and 8.3.
>
> You can also run VACUUM ANALYZE more frequently (after all, it
> doesn't lock the table).

thanks, but max FSM is already set to a large enough value (I'm running a
vacuum analyze every day on the whole database, and set max fsm according
to the last lines of vacuum, so all pages are stored in the FSM).

Nicolas