Re: How do I vacuum safely? And how often should I reindex

On Mon, 2001-12-17 at 12:25, Marc Spitzer wrote:

> I have some questions about vaccum analize, and here they are:
>
> 1: to vaccume analize a db I just vaccume analize the tables
> one at a time

Or you could run it on the entire database by not specifying a table.

> 2: no reads or writes happen durring the vacuum

The vacuum it'self does quite a bit of re-organizing.

> 3: does vacuum wait to aquire the lock or just fail, is
> it nessarsary to get the lock programaticly and then
> turn it loos oe will it take care of it self.

According to the docs, VACUUM aquires an ACCESS EXCLUSIVE LOCK. You can
see this out yourself by firing up two psql sessions and in the first:
BEGIN; LOCK foo; In the second, VACUUM foo; it'll block waiting for a
lock on table foo. Go back to the first and COMMIT, and the second will
immediately start vacuuming. However this is slated to change in 7.2

> 1: how do I figure out how much space is wasted in my indexs
> and primary keys? Now I just keep an eye on disk usage,
> is there a better way?

Indices are wasted only if you never use them. Otherwise they make a
pretty huge difference. Given the price of GB's recently, the better
way is to throw hardware at it.

> 2: how often should I reindex my db, are there any standards
> or accepted practices out there

I recreate my indices every week, or after any extensive updates or
deletes. Again, I believe that 7.2 has some new stuff to deal with this
issue.

> and how will 7.2 be different, I remember a background vacuum
> being discused, what other great things are there waiting in
> the wings

Er... RTFM: http://developer.postgresql.org/todo.php stuff that's
slated for 7.2 has a - in front of it.

--
Andrew G. Hammond mailto:drew(at)xyzzy(dot)dhs(dot)org
http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F
613-389-5481
5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me