Do I need to REINDEX and VACUUM a table after deleting lots of rows?

Question

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.

Solution

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 here (routine re-index) and here (routine vacuuming).

Comments

[Frank Heikens]

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.