Thread: Vacuum questions
Question on vacuuming. When you do a vacuum <table>, are the "freed" tuples available only the table, or to the entire db, or to the entire cluster? The reason I'm asking is that we are getting ready to preform a major upgrade to our application that involves adding some new fields to almost every table and populating them. This is causing our test conversion db's to double in size (we are assuming that every update is causing a delete and re-insert behind the scenes due to the populating of the new columns). Anyway, we are working on trying to find the fastest way to recover the space. We have also had one test occurrence where after the upgrade, a vacuum full would not recover the space. However, when we bounced the postmaster, and then performed a vacuum full, the space was recovered. Any ideas on what might cause this? This happened on a newly restored db that was converted and then vacuumed full. There would have been no connections to the db after the conversion. PG 7.3.4 RH 2.1 Thanks, Chris
"Chris Hoover" <revoohc@sermonaudio.com> writes: > We have also had one test occurrence where after the upgrade, a vacuum > full would not recover the space. However, when we bounced the > postmaster, and then performed a vacuum full, the space was recovered. > Any ideas on what might cause this? Most likely, you had an open transaction lurking that was old enough that it could still "see" the deleted data, and so VACUUM couldn't safely reclaim the data. VACUUM's decisions about this are cluster-wide; so even though you'd recently created the database in question, a long-running transaction in another database under the same postmaster could still cause the problem. regards, tom lane
Tom, Thanks so much for the possible reason the vacuum fulls are not running. But, how about the first question. When you run a vacuum <table>, who has access to the freed tuples? (only the table, the db, or the entire cluster)??? Thanks, Chris
On Wed, 2005-03-16 at 11:55, Chris Hoover wrote: > Tom, > > Thanks so much for the possible reason the vacuum fulls are not running. > > But, how about the first question. When you run a vacuum <table>, who > has access to the freed tuples? (only the table, the db, or the entire > cluster)??? the table