How to reclaim all expired row space

Post date: Oct 17, 2013 3:54:53 PM

A VACUUM FULL will reclaim all expired row space, but is a very expensive operation and may take an unacceptably long time to finish on large, distributed Greenplum Database tables. If you do get into a situation where the free space map has overflowed, it may be more timely to recreate the table with a CREATE TABLE AS statement and drop the old table. A VACUUM FULL is not recommended in Greenplum Database.

Option 1.

ALTER TABLE sales SET WITH (REORGANIZE=TRUE);

This leaves any indexes etc. in tact but rewrites the underlying table and, hence, eliminates all dead data.

Option 2

SET gp_auto_stats_mode = none;

CREATE TABLE xyz AS SELECT * FROM abc <ADD YOUR DISTRIBUTION AND STORAGE OPTIONS HERE>;

DROP TABLE abc;

ALTER TABLE xyz RENAME TO abc;

ANALYZE abc;

ALTER TABLE abc ADD PRIMARY KEY (<YOUR_PK_COLUMN_NAME>);

This only writes the data one time.

Option 3

Create temp table t1 as select * from <Table Name>;

Truncate <Table Name>;

set gp_autostats_mode = none;

Insert into <Table Name> select * from t1;

Drop table t1;

analyze <Table Name>;

This only writes the data 2 times.