"Pomarede Nicolas" <npomarede@xxxxxxxxxxxx> 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
