From: | Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com>, Greg Stark <gsstark(at)mit(dot)edu>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Simon Riggs <simon(at)2ndquadrant(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: remove flatfiles.c |
Date: | 2009-09-02 22:55:19 |
Message-ID: | 4A9EF7D7.6020508@cheapcomplexdevices.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Robert Haas wrote:
> On Tue, Sep 1, 2009 at 9:29 PM, Alvaro
> Herrera<alvherre(at)commandprompt(dot)com> wrote:
>> Ron Mayer wrote:
>>> Greg Stark wrote:
>>>> That's what I want to believe. But picture if you have, say a
>>>> 1-terabyte table which is 50% dead tuples and you don't have a spare
>>>> 1-terabytes to rewrite the whole table.
>>> Could one hypothetically do
>>> update bigtable set pk = pk where ctid in (select ctid from bigtable order by ctid desc limit 100);
>>> vacuum;
>>> and repeat until max(ctid) is small enough?
>> I remember Hannu Krosing said they used something like that to shrink
>> really bloated tables. Maybe we should try to explicitely support a
>> mechanism that worked in that fashion. I think I tried it at some point
>> and found that the problem with it was that ctid was too limited in what
>> it was able to do.
>
> I think a way to incrementally shrink large tables would be enormously
> beneficial. Maybe vacuum could try to do a bit of that each time it
> runs.
Yet when I try it now, I'm having trouble making it work.
Would you expect the ctid to be going down in the psql session
shown below? I wonder why it isn't.
regression=# create table shrink_test as select * from tenk1;
SELECT
regression=# delete from shrink_test where (unique2 % 2) = 0;
DELETE 5000
regression=# create index "shrink_test(unique1)" on shrink_test(unique1);
CREATE INDEX
regression=# select max(ctid) from shrink_test;
max
----------
(333,10)
(1 row)
regression=# update shrink_test set unique1=unique1 where ctid in (select ctid from shrink_test order by ctid desc limit 100);
UPDATE 100
regression=# vacuum shrink_test;
VACUUM
regression=# select max(ctid) from shrink_test;
max
----------
(333,21)
(1 row)
regression=# update shrink_test set unique1=unique1 where ctid in (select ctid from shrink_test order by ctid desc limit 100);
UPDATE 100
regression=# vacuum shrink_test;
VACUUM
regression=# select max(ctid) from shrink_test;
max
----------
(333,27)
(1 row)
regression=# update shrink_test set unique1=unique1 where ctid in (select ctid from shrink_test order by ctid desc limit 100);
UPDATE 100
regression=# vacuum shrink_test;
VACUUM
regression=# select max(ctid) from shrink_test;
max
----------
(333,33)
(1 row)
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2009-09-02 23:22:27 | Re: community decision-making & 8.5 |
Previous Message | Alvaro Herrera | 2009-09-02 22:54:36 | Re: remove flatfiles.c |