pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDoes VACUUM reorder tables on clustered indices
16+ messages / 9 participants
[nested] [flat]
* Does VACUUM reorder tables on clustered indices
@ 2005-12-18 21:12 frank church <pgsql@adontendev.net>
0 siblings, 1 reply; 16+ messages in thread
From: frank church @ 2005-12-18 21:12 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does VACUUM reorder tables on clustered indices
@ 2005-12-18 21:41 Martin Marques <martin@bugs.unl.edu.ar>
parent: frank church <pgsql@adontendev.net>
0 siblings, 1 reply; 16+ messages in thread
From: Martin Marques @ 2005-12-18 21:41 UTC (permalink / raw)
To: frank church <pgsql@adontendev.net>; +Cc: pgsql-sql
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
---------------------------------------------------------
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does VACUUM reorder tables on clustered indices
@ 2005-12-18 22:01 Alvaro Herrera <alvherre@commandprompt.com>
parent: Martin Marques <martin@bugs.unl.edu.ar>
0 siblings, 2 replies; 16+ messages in thread
From: Alvaro Herrera @ 2005-12-18 22:01 UTC (permalink / raw)
To: Martin Marques <martin@bugs.unl.edu.ar>; +Cc: frank church <pgsql@adontendev.net>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does VACUUM reorder tables on clustered indices
@ 2005-12-19 03:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 2 replies; 16+ messages in thread
From: Tom Lane @ 2005-12-19 03:08 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Martin Marques <martin@bugs.unl.edu.ar>; frank church <pgsql@adontendev.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does VACUUM reorder tables on clustered indices
@ 2005-12-19 04:29 Alvaro Herrera <alvherre@commandprompt.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 16+ messages in thread
From: Alvaro Herrera @ 2005-12-19 04:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Martin Marques <martin@bugs.unl.edu.ar>; frank church <pgsql@adontendev.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does VACUUM reorder tables on clustered indices
@ 2005-12-19 13:25 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 16+ messages in thread
From: Andrew Sullivan @ 2005-12-19 13:25 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does VACUUM reorder tables on clustered indices
@ 2005-12-19 15:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 16+ messages in thread
From: Tom Lane @ 2005-12-19 15:19 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does VACUUM reorder tables on clustered indices
@ 2005-12-20 21:41 Jim C. Nasby <jnasby@pervasive.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 1 reply; 16+ messages in thread
From: Jim C. Nasby @ 2005-12-20 21:41 UTC (permalink / raw)
To: Martin Marques <martin@bugs.unl.edu.ar>; frank church <pgsql@adontendev.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does VACUUM reorder tables on clustered indices
@ 2005-12-20 23:34 ipv@tinet.org
parent: Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 16+ messages in thread
From: ipv@tinet.org @ 2005-12-20 23:34 UTC (permalink / raw)
To: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does VACUUM reorder tables on clustered indices
@ 2005-12-21 00:33 Jim C. Nasby <jnasby@pervasive.com>
parent: ipv@tinet.org
0 siblings, 1 reply; 16+ messages in thread
From: Jim C. Nasby @ 2005-12-21 00:33 UTC (permalink / raw)
To: ipv@tinet.org; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does VACUUM reorder tables on clustered indices
@ 2005-12-21 23:36 Chris Browne <cbbrowne@acm.org>
parent: Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 2 replies; 16+ messages in thread
From: Chris Browne @ 2005-12-21 23:36 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does VACUUM reorder tables on clustered indices
@ 2005-12-22 04:07 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Chris Browne <cbbrowne@acm.org>
1 sibling, 0 replies; 16+ messages in thread
From: Bruce Momjian @ 2005-12-22 04:07 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-sql
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does VACUUM reorder tables on clustered indices
@ 2005-12-22 18:59 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Chris Browne <cbbrowne@acm.org>
1 sibling, 0 replies; 16+ messages in thread
From: Andrew Sullivan @ 2005-12-22 18:59 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does VACUUM reorder tables on clustered indices
@ 2005-12-30 03:00 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 16+ messages in thread
From: Bruce Momjian @ 2005-12-30 03:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andrew Sullivan <ajs@crankycanuck.ca>; PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Tom, has this bug been addressed or documented?
---------------------------------------------------------------------------
Tom Lane wrote:
> 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
>
--
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does VACUUM reorder tables on clustered indices
@ 2005-12-30 03:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 16+ messages in thread
From: Tom Lane @ 2005-12-30 03:14 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Andrew Sullivan <ajs@crankycanuck.ca>; PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom, has this bug been addressed or documented?
No. Please add a TODO:
* Make CLUSTER preserve recently-dead tuples per MVCC requirements
I have not tested, but I suspect the table-rewriting variants of ALTER TABLE
have the same problem.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Does VACUUM reorder tables on clustered indices
@ 2005-12-30 03:25 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 16+ messages in thread
From: Bruce Momjian @ 2005-12-30 03:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andrew Sullivan <ajs@crankycanuck.ca>; PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom, has this bug been addressed or documented?
>
> No. Please add a TODO:
>
> * Make CLUSTER preserve recently-dead tuples per MVCC requirements
>
> I have not tested, but I suspect the table-rewriting variants of ALTER TABLE
> have the same problem.
Added.
--
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2005-12-30 03:25 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-18 21:12 Does VACUUM reorder tables on clustered indices frank church <pgsql@adontendev.net>
2005-12-18 21:41 ` Martin Marques <martin@bugs.unl.edu.ar>
2005-12-18 22:01 ` Alvaro Herrera <alvherre@commandprompt.com>
2005-12-19 03:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-19 04:29 ` Alvaro Herrera <alvherre@commandprompt.com>
2005-12-19 13:25 ` Andrew Sullivan <ajs@crankycanuck.ca>
2005-12-19 15:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-30 03:00 ` Bruce Momjian <pgman@candle.pha.pa.us>
2005-12-30 03:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-30 03:25 ` Bruce Momjian <pgman@candle.pha.pa.us>
2005-12-20 21:41 ` Jim C. Nasby <jnasby@pervasive.com>
2005-12-20 23:34 ` ipv@tinet.org
2005-12-21 00:33 ` Jim C. Nasby <jnasby@pervasive.com>
2005-12-21 23:36 ` Chris Browne <cbbrowne@acm.org>
2005-12-22 04:07 ` Bruce Momjian <pgman@candle.pha.pa.us>
2005-12-22 18:59 ` Andrew Sullivan <ajs@crankycanuck.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox