Thread: Re: [GENERAL] Vacuum and Transactions
> Bruce Momjian <pgman@candle.pha.pa.us> writes: > >> In 7.2, VACUUM will not require an exclusive lock. > > > Care to elaborate on that? How are you going to do it? > > Uh, have you not been paying attention to pg-hackers for the > last two months? > > I am assuming here that concurrent VACUUM will become the default > kind of vacuum, and the old style will be invoked by some other > syntax (VACUUM FULL ..., maybe). OK, I just talked to Tom on the phone and here is his idea for 7.2. He says he already posted this, but I missed it. His idea is that in 7.2 VACUUM will only move rows within pages. It will also store unused space locations into shared memory to be used by backends needing to add rows to tables. Actual disk space compaction will be performed by new a VACUUM FULL(?) command. The default VACUUM will not lock the table but only prevent the table from being dropped. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
At 05:59 PM 7/6/01 -0400, Bruce Momjian wrote: > >OK, I just talked to Tom on the phone and here is his idea for 7.2. He >says he already posted this, but I missed it. > >His idea is that in 7.2 VACUUM will only move rows within pages. It >will also store unused space locations into shared memory to be used by >backends needing to add rows to tables. Actual disk space compaction >will be performed by new a VACUUM FULL(?) command. > >The default VACUUM will not lock the table but only prevent the table >from being dropped. Would 7.2 maintain performance when updating a row repeatedly (update, commit)? Right now performance goes down in a somewhat 1/x manner. It's still performs ok but it's nice to have things stay blazingly fast. If not will the new vacuum restore the performance? Or will we have to use the VACUUM FULL? Thanks, Link.
Lincoln Yeoh <lyeoh@pop.jaring.my> writes: > Would 7.2 maintain performance when updating a row repeatedly (update, > commit)? You'll still need to VACUUM to get rid of the obsoleted versions of the row. The point of the planned 7.2 changes is to make VACUUM cheap and nonintrusive enough so that you can run it frequently on tables that are seeing continual updates. regards, tom lane
> You'll still need to VACUUM to get rid of the obsoleted versions of the > row. The point of the planned 7.2 changes is to make VACUUM cheap and > nonintrusive enough so that you can run it frequently on tables that are > seeing continual updates. If it becomes non-intrusive, then why not have PostgreSQL run VACUUM automatically when certain conditions (user-configurable, load, changes per table, etc.) are met. All the sys admin would need to do is put the VACCUUM FULL in a cron job. Chris
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes: > If it becomes non-intrusive, then why not have PostgreSQL run VACUUM > automatically That might happen eventually, but I'm not all that eager to convert the postmaster into a (half-baked) substitute for cron. My experience as a dbadmin is that you need various sorts of routinely-run maintenance tasks anyway; VACUUM is only one of them. So you're gonna need some cron tasks no matter what. If we try to make the postmaster responsible for this sort of thing, we're going to end up reimplementing cron. I think that's a waste of effort. regards, tom lane