PG空间回收之pg_squeeze

郑全

说起空间回收，一般我们都会想到vacuum full，但这个 vacuum full ，要锁表，实际生产中，一定要当心，不要在业务繁忙时使用，如果担心锁表影响业务，可以使用 pg_repack,pg_squeeze，pg_repack要使用触发器，对DML性能有一定影响，我们这里选择 pg_squeeze。


一、安装
1.下载软件
    https://github.com/cybertec-postgresql/pg_squeeze
    如果不能直接联网，可以选择 zip 文件下载后再安装

2.安装
   见本身的readme   
   [color=var(--color-prettylights-syntax-constant)]cd pg_squeeze
  make
  make install


3. 修改postgresql.conf配置文件
   wal_level = logical
   max_replication_slots = 1 # ... or add 1 to the current value
   shared_preload_libraries = 'pg_squeeze' # ... or add the library to the existing ones.
4.重启数据库使配置生效
   pg_ctl stop
   pg_ctl start

二、使用：
     1.创建扩展
      CREATE EXTENSION pg_squeeze;
   2.表解读
      安装完成后会有一个对应的系统表
           

1. postgres=# \d squeeze.tables
   
2.                                          Table "squeeze.tables"
   
3.       Column      |       Type       | Collation | Nullable |                  Default                  
   
4. ------------------+------------------+-----------+----------+--------------------------------------------
   
5. id               | integer          |           | not null | nextval('squeeze.tables_id_seq'::regclass)
   
6. tabschema        | name             |           | not null |
   
7. tabname          | name             |           | not null |
   
8. clustering_index | name             |           |          |
   
9. rel_tablespace   | name             |           |          |
   
10. ind_tablespaces  | name[]           |           |          |
    
11. free_space_extra | integer          |           | not null | 50
    
12. min_size         | real             |           | not null | 8
    
13. vacuum_max_age   | interval         |           | not null | '01:00:00'::interval
    
14. max_retry        | integer          |           | not null | 0
    
15. skip_analyze     | boolean          |           | not null | false
    
16. schedule         | squeeze.schedule |           | not null |
    
17. Indexes:
    
18.     "tables_pkey" PRIMARY KEY, btree (id)
    
19.     "tables_tabschema_tabname_key" UNIQUE CONSTRAINT, btree (tabschema, tabname)
    
20. Check constraints:
    
21.     "tables_free_space_extra_check" CHECK (free_space_extra >= 0 AND free_space_extra < 100)
    
22.     "tables_min_size_check" CHECK (min_size > 0.0::double precision)
    
23. Referenced by:
    
24.     TABLE "squeeze.tables_internal" CONSTRAINT "tables_internal_table_id_fkey" FOREIGN KEY (table_id) REFERENCES squeeze.tables(id) ON DELETE CASCADE
    
25.     TABLE "squeeze.tasks" CONSTRAINT "tasks_table_id_fkey" FOREIGN KEY (table_id) REFERENCES squeeze.tables(id) ON DELETE CASCADE
    
26. Triggers:
    
27.     tables_internal_trig AFTER INSERT ON squeeze.tables FOR EACH ROW EXECUTE FUNCTION 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.创建测试表
      

1. --创建表
   
2. postgres=# create table test(n_id int,c_name varchar(300),primary key(n_id));
   
3. CREATE TABLE
   
4. 
   
5. --初始化数据
   
6. postgres=# insert into test select generate_series(1,4000000),'zhangsan';
   
7. INSERT 0 4000000
   
8. --查看表大小：169MB
   
9. postgres=# \dt+ test
   
10.                   List of relations
    
11. Schema | Name | Type  | Owner |  Size  | Description
    
12. --------+------+-------+-------+--------+-------------
    
13. public | test | table | sa    | 169 MB |
    
14. (1 row)

复制代码

     4、给表test创建squeeze任务
     向squeeze.tables插入一条数据，定期清理test表，每天的0点、2点、6点的10、30和50分别执行1次，空闲空间超过10%就会对表进行重建.
     

1. insert into squeeze.tables (tabschema, tabname, schedule, free_space_extra) values ('public', 'test', ('{10,30,50}', '{00,02,06}', NULL, NULL, NULL), '10');

复制代码

  “minutes”（0到59）和“hours”（0到23）指定了一天内检查的时间，而“days_of_month”（1到31）、“months”（1到12）和“days_of_week”（0到7，其中0和7都代表星期日）确定了检查的日期。如果“minute”、“hour”和“month”都与当前时间戳匹配，则进行检查，而NULL值分别表示任何分钟、小时和月份。至于“days_of_month”和“days_of_week”，至少有一个需要与当前时间戳匹配，或者两者都为NULL才会进行检查。    5、启动和关闭pg_squeeze进程
        select squeeze.start_worker();     select squeeze.stop_worker();
    6、验证
        

1. --更新数据
   
2. postgres=# update test set c_name = '张三-1' where n_id <2000000;
   
3. UPDATE 1999999
   
4. 
   
5. --更新后表大小
   
6. postgres=# \dt+ test
   
7.                   List of relations
   
8. Schema | Name | Type  | Owner |  Size  | Description
   
9. --------+------+-------+-------+--------+-------------
   
10. public | test | table | sa    | 253 MB |
    
11. (1 row)
    
12. 
    
13. --查看空闲空间已经30
    
14. postgres=# select * from squeeze.tables_internal;
    
15. table_id | class_id | class_id_toast |    free_space    |       last_task_created       | last_task_finished
    
16. ----------+----------+----------------+------------------+-------------------------------+--------------------
    
17.         2 |    16528 |              0 | 30.2095497833996 | 2021-01-05 20:57:10.874252+08 |
    
18. (1 row)
    
19. 
    
20. --启动pg_squeeze
    
21. postgres=# select squeeze.start_worker();
    
22. start_worker
    
23. --------------
    
24.         53433
    
25. (1 row)
    
26. 
    
27. --清理完成后查看表大小：
    
28. postgres=# \dt+ test
    
29.                   List of relations
    
30. Schema | Name | Type  | Owner |  Size  | Description
    
31. --------+------+-------+-------+--------+-------------
    
32. public | test | table | sa    | 169 MB |
    
33. (1 row)
    
34. --处理的结束时间last_task_finished时间已经更新了
    
35. postgres=# select * from squeeze.tables_internal;
    
36. table_id | class_id | class_id_toast | free_space |       last_task_created       |      last_task_finished      
    
37. ----------+----------+----------------+------------+-------------------------------+-------------------------------
    
38.         2 |          |                |            | 2021-01-05 20:57:10.874252+08 | 2021-01-05 20:57:10.916349+08
    
39. (1 row)

复制代码

    删除200w数据

1. postgres=# delete from test where n_id<2000000;

复制代码

   7.临时处理
      手动“压缩”表，而无需注册，跳过任何时间和膨胀检查
      语法：  squeeze.squeeze_table(tabchema name, tabname name, clustering_index name, rel_tablespace name, ind_tablespaces name[])
      执行：
      SELECT squeeze.squeeze_table('public', 'test', null, null, null);


     之后再去检查：SELECT pg_size_pretty(pg_total_relation_size('test'));
  


注意事项

     pg_squeeze需要使用logical replication，所以需要设置足够的slots，而且必须注意可能与standby或者使用了逻辑复制功能争抢slots，要保证slots够用。

     pg_squeeze可以自动收缩，对于比较繁忙的数据库，建议不要在业务高峰期启用，避免带来性能风险

     首先，确保您的表具有主键或唯一约束。这是处理“ pg_squeeze”工作时其他事务可能进行的更改所必需的。