免费注册
帮助文档(华北一、二)

  • ● 方法一:

    vacuum full table 注意,vacuum full不能回收索引的膨胀空间。vacuum full 加载的锁与DDL锁类似,是排它锁。建议在没有业务的时候执行,不要堵塞业务。 使用vacuum full回收垃圾的建议操作流程:

    1 记录下表的索引;

    2 删除索引;

    3 vacuum full 表;

    4 重建索引。

    示例:

     

    dev=# create table test(id int , name text);

    NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.

    HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.

    CREATE TABLE

    dev=# insert into test select generate_series(1,100000000), 'test';

    INSERT 0 100000000

    dev=# create index idx_test on test(id);

    CREATE INDEX

    dev=# update test set name='nice';

    UPDATE 100000000

    查看表格数据:

    dev=# select pg_size_pretty(pg_relation_size('test'));

     pg_size_pretty

    ----------------

     8401 MB

    (1 row)

    查看索引数据:

    dev=# select pg_size_pretty(pg_relation_size('idx_test'));

     pg_size_pretty

    ----------------

     6377 MB

    (1 row)

    先回收表数据(此方法不能回收索引数据):

    dev=# vacuum full test ;

    VACUUM

    dev=# select pg_size_pretty(pg_relation_size('test'));

     pg_size_pretty

    ----------------

     4200 MB

    (1 row)

    Time: 4.278 ms

    dev=# select pg_size_pretty(pg_relation_size('idx_test'));

     pg_size_pretty