Vacuum VS Vacuum Analyze

"Matt Friedman" <matt@daart.ca> writes:

I currently running vacuum nighly using cron and once in a while I run
vacuum analyze (as postgres).
Any reason why I wouldn't just simply run vacuum analyze each night?

If you can spare the cycles, you might as well make every vacuum a
vacuum analyze.

regards, tom lane

On Friday, March, 2001-03-23 at 17:42:37, Tom Lane wrote:

"Matt Friedman" <matt@daart.ca> writes:

I currently running vacuum nighly using cron and once in a while I run
vacuum analyze (as postgres).
Any reason why I wouldn't just simply run vacuum analyze each night?

If you can spare the cycles, you might as well make every vacuum a
vacuum analyze.

I have experienced that vacuum, especially vacuum analyze on heavily
used database sometimes seems to last forever. A very quick_and_dirty
hack is to run it twice: first time I run simple vacuum, but before that
I drop all the indices. After recreating of indices I run vacuum analyze.

The whole process runs lightning fast (the longest process is to
recreate the indices). The only problem is not to allow users to add
anything to the database, because it may end up in broken unique-key
indices. My solution to that is... temporary shutdown of services using
the database (those are helper services for my WWW application) which
simply makes my application refuse to work. The whole process is
scheduled for a deep night (about 4:00 AM) so hardly anybody can notice
;-) (it takes approx. 5 minutes)

The other solution would be not to drop the unique indices (but I don't
know the speed penalty in this case).

Question is: have I misspotted something? Is this routine of any danger
that I fail to notice?

regards

--
Marek P�tlicki <marpet@buy.pl>

[ Charset ISO-8859-2 unsupported, converting... ]

On Friday, March, 2001-03-23 at 17:42:37, Tom Lane wrote:

"Matt Friedman" <matt@daart.ca> writes:

I currently running vacuum nighly using cron and once in a while I run
vacuum analyze (as postgres).
Any reason why I wouldn't just simply run vacuum analyze each night?

If you can spare the cycles, you might as well make every vacuum a
vacuum analyze.

I have experienced that vacuum, especially vacuum analyze on heavily
used database sometimes seems to last forever. A very quick_and_dirty
hack is to run it twice: first time I run simple vacuum, but before that
I drop all the indices. After recreating of indices I run vacuum analyze.

The whole process runs lightning fast (the longest process is to
recreate the indices). The only problem is not to allow users to add
anything to the database, because it may end up in broken unique-key
indices. My solution to that is... temporary shutdown of services using
the database (those are helper services for my WWW application) which
simply makes my application refuse to work. The whole process is
scheduled for a deep night (about 4:00 AM) so hardly anybody can notice
;-) (it takes approx. 5 minutes)

You are seeing two things here. First, pre-7.1 vacuum analyze locked
the table for the analyze portion as well as the vacuum, which was not
needed. 7.1 will lock table only for vacuum, analyze will run with very
light lock.

The second part is that index updating is very slow in vacuum. We how
to address this in 7.2. We ran out of time in 7.1.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026