Re: How do I vacuum safely? And how often should I reindex - Mailing list pgsql-admin

From Andrew G. Hammond
Subject Re: How do I vacuum safely? And how often should I reindex
Date
Msg-id 1008637363.4653.2.camel@xyzzy
Whole thread Raw
In response to How do I vacuum safely? And how often should I reindex a table?  (marc@oscar.eng.cv.net (Marc Spitzer))
List pgsql-admin
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@xyzzy.dhs.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

Attachment

pgsql-admin by date:

Previous
From: Sanjay Bhatia
Date:
Subject: Resetting connection errors and incomplete restores.
Next
From: "Yuri A. Kabaenkov"
Date:
Subject: compilation troubles