"Pomarede Nicolas" <npomarede@corp.free.fr> writes:
> 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.
Try running vacuum more frequently. Once per day isn't very frequent for
vacuum, every 60 or 30 minutes isn't uncommon. For your situation you might
even consider running it continuously in a loop.
> I see in the 8.3 list of coming changes that the FSM will try to re-use pages
> in a better way to help truncating empty pages. Is this correct ?
There are several people working on improvements to vacuum but it's not clear
right now exactly what we'll end up with. I think most of the directly vacuum
related changes wouldn't actually help you either.
The one that would help you is named "HOT". If you're interested in
experimenting with an experimental patch you could consider taking CVS and
applying HOT and seeing how it affects you. Or if you see an announcement that
it's been comitted taking a beta and experimenting with it before the 8.3
release could be interesting. Experiments with real-world databases can be
very helpful for developers since it's hard to construct truly realistic
benchmarks.
> So, I would like to truncate the table when the number of rows reaches 0 (just
> after the table was processed, and just before some new rows are added).
>
> Is there an easy way to do this under psql ? For example, lock the table, do a
> count(*), if result is 0 row then truncate the table, unlock the table (a kind
> of atomic 'truncate table if count(*) == 0').
>
> Would this work and what would be the steps ?
It would work but you may end up keeping the lock for longer than you're happy
for. Another option to consider would be to use CLUSTER instead of vacuum full
though the 8.2 CLUSTER wasn't entirely MVCC safe and I think in your situation
that might actually be a problem. It would cause transactions that started
before the cluster (but didn't access the table before the cluster) to not see
any records after the cluster.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com