I proved that you can reclaim on disk space after a DROP COLUMN with toast
tables:
test=# create table toast_test(a text, b text);
CREATE TABLE
test=# insert into toast_test values (repeat('XXXXXXXXXX', 1000000),
repeat('XXXXXXXXXX', 1000000));
INSERT 246368 1
test=# insert into toast_test values (repeat('XXXXXXXXXX', 1000000),
repeat('XXXXXXXXXX', 1000000));
INSERT 246371 1
Gives:
-rw------- 1 chriskl users 8192 Aug 30 15:46 246363
-rw------- 1 chriskl users 475136 Aug 30 15:47 246365
-rw------- 1 chriskl users 16384 Aug 30 15:46 246367
test=# alter table toast_test drop a;
ALTER TABLE
test=# update toast_test set b = b;
UPDATE 2
Gives:
-rw------- 1 chriskl users 8192 Aug 30 15:46 246363
-rw------- 1 chriskl users 475136 Aug 30 15:48 246365
-rw------- 1 chriskl users 16384 Aug 30 15:46 246367
test=# vacuum full toast_test;
VACUUM
test=# checkpoint;
CHECKPOINT
Gives:
-rw------- 1 chriskl users 8192 Aug 30 15:48 246363
-rw------- 1 chriskl users 237568 Aug 30 15:48 246365
-rw------- 1 chriskl users 16384 Aug 30 15:48 246367
Seems to halve the space used which is what you'd expect.
Chris
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
