Обсуждение: 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