postgres=# \d squeeze.tables
                                            Table "squeeze.tables"
      Column      |         Type          | Collation | Nullable |                  Default                   
------------------+-----------------------+-----------+----------+--------------------------------------------
 id               | integer               |           | not null | nextval('squeeze.tables_id_seq'::regclass)
 tabschema        | name                  |           | not null | 
 tabname          | name                  |           | not null | 
 clustering_index | name                  |           |          | 
 rel_tablespace   | name                  |           |          | 
 ind_tablespaces  | name[]                |           |          | 
 schedule         | time with time zone[] |           | not null | 
 free_space_extra | integer               |           | not null | 50
 min_size         | real                  |           | not null | 8
 vacuum_max_age   | interval              |           | not null | '01:00:00'::interval
 max_retry        | integer               |           | not null | 0
 skip_analyze     | boolean               |           | not null | false
Indexes:
    "tables_pkey" PRIMARY KEY, btree (id)
    "tables_tabschema_tabname_key" UNIQUE CONSTRAINT, btree (tabschema, tabname)
Check constraints:
    "tables_free_space_extra_check" CHECK (free_space_extra >= 0 AND free_space_extra < 100)
    "tables_min_size_check" CHECK (min_size > 0.0::double precision)
Referenced by:
    TABLE "squeeze.tables_internal" CONSTRAINT "tables_internal_table_id_fkey" FOREIGN KEY (table_id) REFERENCES squeeze.tables(id) ON DELETE CASCADE
    TABLE "squeeze.tasks" CONSTRAINT "tasks_table_id_fkey" FOREIGN KEY (table_id) REFERENCES squeeze.tables(id) ON DELETE CASCADE
Triggers:
    tables_internal_trig AFTER INSERT ON squeeze.tables FOR EACH ROW EXECUTE PROCEDURE squeeze.tables_internal_trig_func()

squeeze.tables表字段说明

• tabschema：表的模式名。
• tabname：表名。
• clustering_index：表示重建表时，表数据的物理顺序按照该索引进行聚簇。
• rel_tablespace：表示表重建时，移动到哪个表空间中。
• ind_tablespace：这个一个二维数组，表示索引和表空间的映射关系。
• schedule：vacuum在一天中运行的时间范围
• free_space_extra：表示空闲空间超过多少时就会对表进行重建，默认是50。
• min_size：表必须占用的最小磁盘空间（兆字节）才有资格进行处理，默认值为8。
• vacuum_max_age：当进行一次vacuum后，认为fsm是有效的最大时间，默认1小时。
• max_retry：当重建表失败时最大的重新尝试的次数，默认是0.
• skip_analyse：跳过对表进行analyse，默认是false。

3、创建测试表

--创建表
postgres=# create table test(n_id int,c_name varchar(300),primary key(n_id));
CREATE TABLE

--初始化数据
postgres=# insert into test select generate_series(1,4000000),'zhangsan';
INSERT 0 4000000
--查看表大小：169MB
postgres=# \dt+ test
                  List of relations
 Schema | Name | Type  | Owner |  Size  | Description 
--------+------+-------+-------+--------+-------------
 public | test | table | sa    | 169 MB | 
(1 row)

--需要在表squeeze.tables插入一条记录。添加后，将定期检查表的统计信息。只要满足‘压缩’的太偶见，就会将‘任务’添加到队列中，任务按照创建爱女顺序依次处理
--schedule标识该任务在晚上八点到24点执行，并且free_space_extra表示空闲空间超过10时就会对表进行重建
postgres=# insert into squeeze.tables (tabschema, tabname, schedule, free_space_extra) values ('public', 'test', '{20:00, 24:00}', '10');
INSERT 0 1

--如果需要取消注册表，只需要从‘squeeze.tables’表删除响应的行即可
--查看任务

postgres=# select * from squeeze.tables;
 id | tabschema | tabname | clustering_index | rel_tablespace | ind_tablespaces |         schedule          | free_space_extra | min_size | vacuum_max_age | max_retry | skip_analyze 
----+-----------+---------+------------------+----------------+-----------------+---------------------------+------------------+----------+----------------+-----------+--------------
  2 | public    | test    |                  |                |                 | {20:00:00+08,24:00:00+08} |               10 |        8 | 01:00:00       |         0 | f
(1 row)

select squeeze.start_worker();
select squeeze.stop_worker();

--更新数据
postgres=# update test set c_name = '张三-1' where n_id <2000000;
UPDATE 1999999

--更新后表大小
postgres=# \dt+ test
                  List of relations
 Schema | Name | Type  | Owner |  Size  | Description 
--------+------+-------+-------+--------+-------------
 public | test | table | sa    | 253 MB | 
(1 row)

--查看空闲空间已经30
postgres=# select * from squeeze.tables_internal;
 table_id | class_id | class_id_toast |    free_space    |       last_task_created       | last_task_finished 
----------+----------+----------------+------------------+-------------------------------+--------------------
        2 |    16528 |              0 | 30.2095497833996 | 2021-01-05 20:57:10.874252+08 | 
(1 row)

--启动pg_squeeze
postgres=# select squeeze.start_worker();
 start_worker 
--------------
        53433
(1 row)

--清理完成后查看表大小：
postgres=# \dt+ test
                  List of relations
 Schema | Name | Type  | Owner |  Size  | Description 
--------+------+-------+-------+--------+-------------
 public | test | table | sa    | 169 MB | 
(1 row)
--处理的结束时间last_task_finished时间已经更新了
postgres=# select * from squeeze.tables_internal;
 table_id | class_id | class_id_toast | free_space |       last_task_created       |      last_task_finished       
----------+----------+----------------+------------+-------------------------------+-------------------------------
        2 |          |                |            | 2021-01-05 20:57:10.874252+08 | 2021-01-05 20:57:10.916349+08
(1 row)

--会自动清理
postgres=# \dt+ test
                  List of relations
 Schema | Name | Type  | Owner | Size  | Description 
--------+------+-------+-------+-------+-------------
 public | test | table | sa    | 85 MB | 
(1 row)

postgres=# vacuum full test;
VACUUM
postgres=# \dt+ test
                  List of relations
 Schema | Name | Type  | Owner | Size  | Description 
--------+------+-------+-------+-------+-------------
 public | test | table | sa    | 84 MB | 
(1 row)

postgres=# create extension pgstattuple;
CREATE EXTENSION

postgres=# select * from pgstattuple('test');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percen
t | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+------------------
--+------------+--------------
  88563712 |     2000001 |  74000037 |         83.56 |                0 |              0 |                  
0 |     260960 |         0.29
(1 row)

SELECT squeeze.squeeze_table('public', 'test', null, null, null);

'squeeze.log'表在每个成功压缩的表中包含一个条目。
'squeeze.errors'包含在压缩期间发生的错误。这里报告的一个常见问题是有人更改了正在处理表的定义（例如，添加或删除的列）。

wget https://github.com/reorg/pg_repack/archive/ver_1.4.4.zip

[thunisoft5@localhost pg_repack-ver_1.4.4]$ make && make install

create extension pg_repack;

postgres=# create table repack_test(n_id int,c_name varchar(3000));
CREATE TABLE
--初始化数据
postgres=#  insert into repack_test select generate_series(1,4000000),'张三';
INSERT 0 4000000
--使用pg_stattuple查看表情况
postgres=# select * from pgstattuple('repack_test');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percen
t | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+------------------
--+------------+--------------
 177127424 |     4000000 | 140000000 |         79.04 |                0 |              0 |                  
0 |     522008 |         0.29
(1 row)
--查看表大小
postgres=# select pg_size_pretty(pg_total_relation_size('repack_test') );
 pg_size_pretty 
----------------
 169 MB
(1 row)
--查看表文件路径
postgres=# select pg_relation_filepath('repack_test');
 pg_relation_filepath 
----------------------
 base/13214/16588
(1 row)

--表必须有主键或者唯一约束，这快和pg_squeeze一样
[thunisoft5@localhost bin]$ pg_repack -p 8543 -d postgres --no-order --table repack_test
WARNING: relation "public.repack_test" must have a primary key or not-null unique keys
--添加主键
postgres=# alter table repack_test add primary key(n_id);
ALTER TABLE

--更新200w数据
postgres=# update repack_test set c_name = '张三-1' where n_id <=2000000;
UPDATE 2000000
更新后表达小变大了
postgres=# select pg_size_pretty(pg_total_relation_size('repack_test') );
 pg_size_pretty 
----------------
 425 MB
(1 row)

--再次执行pg_repack
[thunisoft5@localhost bin]$ pg_repack -p 8543 -d postgres --no-order --table repack_test --elevel=info
INFO: repacking table "public.repack_test"
--更新后查看表大小，表已经缩小了
postgres=#  select pg_size_pretty(pg_total_relation_size('repack_test') );
 pg_size_pretty 
----------------
 255 MB
(1 row)
--并且数据文件的路径也发生了变化
postgres=# select pg_relation_filepath('repack_test');
 pg_relation_filepath 
----------------------
 base/13214/16659
(1 row)

postgres=# select * from repack.primary_keys limit 10;
 indrelid | indexrelid 
----------+------------
      826 |        828
     1136 |       1137
     1213 |       2697
     1247 |       2703
     1249 |       2658
     1255 |       2690
     1259 |       2662
     1260 |       2677
     1261 |       2694
     1262 |       2672
(10 rows)

[thunisoft5@localhost bin]$ pg_repack -p 8543 -d postgres --no-order --jobs 8  --elevel=info

 pg_repack -p 8543 -d postgres --schema=public --no-order --jobs 8  --elevel=info

pg_repack -p 8543 -d postgres --no-order --table public.repack_test --elevel=info

pg_repack -p 8543 -d postgres --no-order --only-indexes --table public.repack_test --elevel=info

 pg_repack -p 8543 -d postgres  --index public.repack_test_pkey --elevel=info