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.

HopelessN00b
  • 53,385
  • 32
  • 133
  • 208
cowgod
  • 3,460
  • 6
  • 27
  • 20
  • 2
    You might want to consider partitioning, see http://www.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 08:55

1 Answers1

17

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

HopelessN00b
  • 53,385
  • 32
  • 133
  • 208
hmallett
  • 2,425
  • 14
  • 26