Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I am running a PostgreSQL database that has several tables which store logging information. This information is for reporting purposes only and gets dumped to a file and deleted from the database if older than 30 days.

There can be millions of rows deleted, and we have been running a REINDEX each time after deletion.

Is this sufficient, or should we also be running a VACUUM or VACUUM ANALYZE? Or is the REINDEX not necessary and we should instead just run a VACUUM or VACUUM ANALYZE?

We are using PostgreSQL 8.2.3, which I believe does not allow auto-vacuuming.

share|improve this question
2  
You might want to consider partitioning, see postgresql.org/docs/9.0/static/ddl-partitioning.html ; then you can just drop partitions and avoid vacuuming. –  alex Sep 23 '10 at 20:07
3  
Version 8.2.3 does have auto_vacuum, see the manual, but you should update asap. Current 8.2-version is 8.2.17. You're 14 patches behind, including a couple of security patches. An upgrade to 8.4 or even 9.0 is a better idea, auto_vacuum has been improved. –  Frank Heikens Sep 24 '10 at 8:55

1 Answer 1

You should do a VACUUM ANALYZE as the VACUUM will allow space used by the deleted data to be reused and prevent transaction wraparound, and the ANALYZE will update planner statistics which should lead to better query plans for your reporting queries.

A REINDEX is not theoretically required, but you may find that it results in better performance as the index is contiguous.

The relevant documentation pages for 8.2 are http://www.postgresql.org/docs/8.2/static/routine-reindex.html and http://www.postgresql.org/docs/8.2/static/routine-vacuuming.html

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.