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).
--
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