14

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.

2
  • 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, 2010 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. Sep 24, 2010 at 8:55

1 Answer 1

18

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .