6

I have a huge (~150 GBytes) postgresql database whose performance has dropped during the last weeks. In order to improve performance, I intend to perform FULL VACUUM, CLUSTER and ANALYZE.

In which order should I perform these operations? I don't mind the time it takes to perform, just the performance improvements of the database.

andschar
  • 103
  • 3
Adam Matan
  • 12,504
  • 19
  • 54
  • 73

1 Answers1

10

You should never run VACUUM FULL, if that's what you are talking about.

You should run VACUUM and ANALYZE regularly, on all databases. The easiest thing to do here is to just run VACUUM ANALYZE as one command, which will do both of them - that will also be faster.

Unless, that is, you can use autovacuum. If you're on 8.3 or newer, that would be the recommendation in most cases. Then you don't end up running it unnecessarily.

You shouldn't run CLUSTER on everything in the database, just because you can. If you have specific tables, run it there. But most workloads don't need a regular CLUSTER at all - and certainly not on all tables.

The tables that you do run CLUSTER on don't need VACUUM.

Magnus Hagander
  • 2,227
  • 15
  • 9
  • a. "you should never run VACUUM FULL" - Why? b. I cluster specific tables, of course. – Adam Matan Aug 26 '09 at 16:25
  • VACUUM FULL is only intended to recover from situations where you haven't run VACUUM often enough. During normal operations, it shouldn't be needed. And in most cases, you can use CLUSTER instead of VACUUM FULL and it'll be faster.. – Magnus Hagander Aug 27 '09 at 17:28