Robert Haas wrote: > On Tue, Sep 1, 2009 at 9:29 PM, Alvaro > Herrera<alvhe...@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 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) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
