Robert Haas wrote:
> On Tue, Sep 1, 2009 at 9:29 PM, Alvaro
> Herrera<alvherre@commandprompt.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
limit100);
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
limit100);
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
limit100);
UPDATE 100
regression=# vacuum shrink_test;
VACUUM
regression=# select max(ctid) from shrink_test; max
----------(333,33)
(1 row)
