Обсуждение: How do I vacuum safely? And how often should I reindex a table?

Hello all,

If any of these are answered in the docs please RTFM me
with a pointer.

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

2: no reads or writes happen durring the vacuum

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.

4: is there any way to figure out how much wasted space
   there is in a table, the number of rows marked as
   garbage would be enough.

now on to my index/primary key questions

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?

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

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



Thanks

marc

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