Thread: Does VACUUM reorder tables on clustered indices

Does VACUUM reorder tables on clustered indices

From
frank church
Date:

Does VACUUMing reorder tables on clustered indices or is it only the CLUSTER
command that can do that?


/ r church




----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.



Re: Does VACUUM reorder tables on clustered indices

From
Martin Marques
Date:
On Sun, 18 Dec 2005, frank church wrote:

>
> Does VACUUMing reorder tables on clustered indices or is it only the CLUSTER
> command that can do that?

Cluster does that. Vacuum only cleans dead tuples from the tables.

-- 18:02:25 up 4 days,  9:57,  5 users,  load average: 1.59, 1.57, 1.62
---------------------------------------------------------
Lic. Martín Marqués         |   SELECT 'mmarques' ||
Centro de Telemática        |        '@' || 'unl.edu.ar';
Universidad Nacional        |   DBA, Programador,    del Litoral             |   Administrador
---------------------------------------------------------

Re: Does VACUUM reorder tables on clustered indices

From
Alvaro Herrera
Date:
Martin Marques escribió:
> On Sun, 18 Dec 2005, frank church wrote:
> 
> >
> >Does VACUUMing reorder tables on clustered indices or is it only the 
> >CLUSTER
> >command that can do that?
> 
> Cluster does that. Vacuum only cleans dead tuples from the tables.

Note that while reordering, CLUSTER also gets rid of dead tuples, so if
you cluster you don't need to vacuum.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.


Re: Does VACUUM reorder tables on clustered indices

From
Tom Lane
Date:
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Note that while reordering, CLUSTER also gets rid of dead tuples, so if
> you cluster you don't need to vacuum.

Just for the record, that behavior is seriously broken: it violates
MVCC if any of the deleted tuples are still visible to anyone else.
        regards, tom lane


Re: Does VACUUM reorder tables on clustered indices

From
Alvaro Herrera
Date:
Tom Lane escribió:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Note that while reordering, CLUSTER also gets rid of dead tuples, so if
> > you cluster you don't need to vacuum.
> 
> Just for the record, that behavior is seriously broken: it violates
> MVCC if any of the deleted tuples are still visible to anyone else.

Well, FWIW, the fact that the CLUSTER command exists at all is broken
IMHO ...  but meanwhile, are you suggesting that CLUSTER should be fixed
to retain tuples that are visible to running transactions?

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


Re: Does VACUUM reorder tables on clustered indices

From
Andrew Sullivan
Date:
On Sun, Dec 18, 2005 at 10:08:22PM -0500, Tom Lane wrote:
> 
> Just for the record, that behavior is seriously broken: it violates
> MVCC if any of the deleted tuples are still visible to anyone else.

Does it remove tuples that VACUUM FULL wouldn't?  I always thought it
did essentially the same thing?

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what 
you told them to.  That actually seems sort of quaint now.    --J.D. Baldwin


Re: Does VACUUM reorder tables on clustered indices

From
Tom Lane
Date:
Andrew Sullivan <ajs@crankycanuck.ca> writes:
> On Sun, Dec 18, 2005 at 10:08:22PM -0500, Tom Lane wrote:
>> Just for the record, that behavior is seriously broken: it violates
>> MVCC if any of the deleted tuples are still visible to anyone else.

> Does it remove tuples that VACUUM FULL wouldn't?

Yes.  CLUSTER works on SnapshotNow, so it will remove committed-dead
tuples even if there are still open transactions that could see them.
Of course, said transactions couldn't be actively using the table
while the CLUSTER runs, because it takes an exclusive table lock.
But they *could* look at it afterwards.  Offhand I think you'd only
be likely to notice the difference if the open transactions were
SERIALIZABLE --- in READ COMMITTED mode, by the time they could look
at the clustered table, they'd likely be using a snapshot that postdates
the DELETE.

[ experiments a bit... ]  Hmm.  Actually, it's far worse than I
thought.  It looks like CLUSTER puts the tuples into the new table with
its own xid, which means that concurrent serializable transactions will
see the new table as completely empty!

<< session 1 >>

regression=# select * from int4_tbl;    f1
-------------          0     123456    -123456 2147483647-2147483647
(5 rows)

regression=# create index fooi on int4_tbl(f1);
CREATE INDEX
regression=# begin isolation level serializable;
BEGIN
regression=# select 2+2; -- establish transaction snapshot?column?
----------       4
(1 row)

<< session 2 >>

regression=# delete from int4_tbl where f1 = -123456;
DELETE 1
regression=# cluster fooi on int4_tbl;
CLUSTER

<< back to session 1 >>

regression=# select * from int4_tbl;f1
----
(0 rows)

regression=# commit;
COMMIT
regression=# select * from int4_tbl;    f1
--------------2147483647          0     123456 2147483647
(4 rows)

        regards, tom lane


Re: Does VACUUM reorder tables on clustered indices

From
"Jim C. Nasby"
Date:
On Sun, Dec 18, 2005 at 07:01:39PM -0300, Alvaro Herrera wrote:
> Martin Marques escribi?:
> > On Sun, 18 Dec 2005, frank church wrote:
> > 
> > >
> > >Does VACUUMing reorder tables on clustered indices or is it only the 
> > >CLUSTER
> > >command that can do that?
> > 
> > Cluster does that. Vacuum only cleans dead tuples from the tables.
> 
> Note that while reordering, CLUSTER also gets rid of dead tuples, so if
> you cluster you don't need to vacuum.

It also does a REINDEX...
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


Re: Does VACUUM reorder tables on clustered indices

From
Date:
Hi,

Utilize <b>CLUSTER;</b> (after vacuum) to reorder the data.

Regards


----- Original Message ----- 
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: "Martin Marques" <martin@bugs.unl.edu.ar>; "frank church" 
<pgsql@adontendev.net>; <pgsql-sql@postgresql.org>
Sent: Tuesday, December 20, 2005 10:41 PM
Subject: Re: [SQL] Does VACUUM reorder tables on clustered indices


> On Sun, Dec 18, 2005 at 07:01:39PM -0300, Alvaro Herrera wrote:
>> Martin Marques escribi?:
>> > On Sun, 18 Dec 2005, frank church wrote:
>> >
>> > >
>> > >Does VACUUMing reorder tables on clustered indices or is it only the
>> > >CLUSTER
>> > >command that can do that?
>> >
>> > Cluster does that. Vacuum only cleans dead tuples from the tables.
>>
>> Note that while reordering, CLUSTER also gets rid of dead tuples, so if
>> you cluster you don't need to vacuum.
>
> It also does a REINDEX...
> -- 
> Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
> Pervasive Software      http://pervasive.com    work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
> 



Re: Does VACUUM reorder tables on clustered indices

From
"Jim C. Nasby"
Date:
On Wed, Dec 21, 2005 at 12:34:12AM +0100, ipv@tinet.org wrote:
> Hi,
> 
> Utilize <b>CLUSTER;</b> (after vacuum) to reorder the data.

Why would you vacuum when cluster is just going to wipe out the dead
tuples anyway?

> >>Note that while reordering, CLUSTER also gets rid of dead tuples, so if
> >>you cluster you don't need to vacuum.
> >
> >It also does a REINDEX...
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


Re: Does VACUUM reorder tables on clustered indices

From
Chris Browne
Date:
jnasby@pervasive.com ("Jim C. Nasby") writes:
> On Wed, Dec 21, 2005 at 12:34:12AM +0100, ipv@tinet.org wrote:
>> Hi,
>> 
>> Utilize <b>CLUSTER;</b> (after vacuum) to reorder the data.
>
> Why would you vacuum when cluster is just going to wipe out the dead
> tuples anyway?

There is one reason to VACUUM before running CLUSTER...

That is that VACUUM will be *guaranteed* to draw all the pages into memory.

Subsequently, you can be certain that the pages are in cache, and that
the CLUSTER should need to do minimal I/O to read data into memory.

If I'm considering clustering the Slony-I "sl_log_1" table, forcing it
into memory *is* something I'll consider doing in order to minimize
the time that would-be writers are blocked from writing...
-- 
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/multiplexor.html
Never criticize anybody until  you have walked  a mile in their shoes,
because by that time you will be a mile away and have their shoes.
-- email sig, Brian Servis


Re: Does VACUUM reorder tables on clustered indices

From
Bruce Momjian
Date:
Chris Browne wrote:
> jnasby@pervasive.com ("Jim C. Nasby") writes:
> > On Wed, Dec 21, 2005 at 12:34:12AM +0100, ipv@tinet.org wrote:
> >> Hi,
> >> 
> >> Utilize <b>CLUSTER;</b> (after vacuum) to reorder the data.
> >
> > Why would you vacuum when cluster is just going to wipe out the dead
> > tuples anyway?
> 
> There is one reason to VACUUM before running CLUSTER...
> 
> That is that VACUUM will be *guaranteed* to draw all the pages into memory.
> 
> Subsequently, you can be certain that the pages are in cache, and that
> the CLUSTER should need to do minimal I/O to read data into memory.
> 
> If I'm considering clustering the Slony-I "sl_log_1" table, forcing it
> into memory *is* something I'll consider doing in order to minimize
> the time that would-be writers are blocked from writing...

Why don't you just do SELECT * FROM tab WHERE col != 'lkjasdflkjadsf'. 
That should pull things into memory without the VACUUM overhead.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Does VACUUM reorder tables on clustered indices

From
Andrew Sullivan
Date:
On Wed, Dec 21, 2005 at 06:36:45PM -0500, Chris Browne wrote:
> If I'm considering clustering the Slony-I "sl_log_1" table, forcing it
> into memory *is* something I'll consider doing in order to minimize
> the time that would-be writers are blocked from writing...

Given what Tom Lane recently reported (and assuming I understood his
remarks), I think it's a Mighty Bad Idea to CLUSTER sl_log_1.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.                --Brad Holland