On Mon, Nov 8, 2010 at 2:06 PM, Jason Long <mailing.lists@xxxxxxxxxxxxxxxx> wrote: > Every night when there is no activity I do a full vacuum, a reindex, and > then dump a nightly backup. > > Is this optimal with regards to performance? autovacuum is set to the > default. In the general case this seems way overkill. Do you suffer from a lot of churn daily? That is, are there bunches of updates? One thing you lose when running vacuum full is the space in the file that is pre-allocated but empty. If you do lots of updates and inserts, you'll be allocating pages and growing the underlying files to hold your data. If you leave the unused space there, it is much faster for postgres just to fill it. The key is keeping that unused space from growing beyond reason... the trick is defining for your own use case what "within reason" means. As for re-index, don't bother. Unless you have some degenerate case (something like a queue) where you always insert values at the tail end of the index and delete from the front end of the index, and let autovacuum do its work, you should remain in a fairly steady state. There are queries you can run against the database to detect how bloated your indexes are after a while, and then reindex if necessary. I find that some of my data needs a reindex about every 4 to 6 months, while others never benefit. I *never* run a vacuum full. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
