Thread: How do I vacuum safely? And how often should I reindex a table?
How do I vacuum safely? And how often should I reindex a table?
From
marc@oscar.eng.cv.net (Marc Spitzer)
Date:
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