PG使用插件pg_squeeze解决表和索引的膨胀问题
Tags： PGpg_squeezePostgreSQL插件索引膨胀膨胀表膨胀
简介
PostgreSQL中大量更新或者删除记录后，加上autovacuum参数未做优化或设置不当，会导致表及索引膨胀。生产环境除了手动使用vacuum之外，还有两个比较常用的工具：一个是pg_repack，另外一个是pg_squeeze。
使用pg_repack或pg_squeeze对表做重组时，比vacuum full对系统的影响小，且性能更高。
安装pg_squeeze
GitHub：https://github.com/cybertec-postgresql/pg_squeeze
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | -- yum安装（PG源就有） yum list pg_squeeze* yum install -y pg_squeeze_14 -- 编译安装 wget https://github.com/cybertec-postgresql/pg_squeeze/archive/refs/tags/REL1_5_0.tar.gz tar -zxvf REL1_5_0.tar.gz cd pg_squeeze-REL1_5_0/ make && make install vi /var/lib/pgsql/14/data/postgresql.conf wal_level = logical max_replication_slots = 10 shared_preload_libraries = 'pg_squeeze' pg_ctl restart CREATE EXTENSION pg_squeeze; |
过程：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | postgres=# create extension pg_squeeze ; CREATE EXTENSION postgres=# set search_path to 'squeeze'; SET postgres=# \dt List of relations Schema | Name | Type | Owner ---------+-----------------+-------+---------- squeeze | errors | table | postgres squeeze | log | table | postgres squeeze | tables | table | postgres squeeze | tables_internal | table | postgres squeeze | tasks | table | postgres (5 rows) postgres=# \dn List of schemas Name | Owner ---------+---------- public | postgres repack | postgres squeeze | postgres (3 rows) |
pg_repack使用例子
对test数据库下的foo和bar表做在线vacuum full
1 | $ pg_repack --no-order --table foo --table bar test |
转移foo表的索引到tbs表空间
1 | $ pg_repack -d test --table foo --only-indexes --tablespace tbs |
pg_squeeze使用例子
以注册表方式crontab定期运行“squeeze挤压”
1 2 | INSERT INTO squeeze.tables (tabschema, tabname, schedule) VALUES ('public', 'foo', ('{30}', '{22}', NULL, NULL, '{3, 5}')); |
手工对public模式下的pgbench_accounts表“squeeze挤压”
1 | SELECT squeeze.squeeze_table('public', 'pgbench_accounts', null, null, null); |
pg_squeeze测试
创建测试表
1 2 | create table public.test(id int primary key); insert into public.test select generate_series(1,2000000); |
查看表的大小
1 2 3 4 5 6 7 | postgres=# SELECT pg_size_pretty(pg_total_relation_size('test')); pg_size_pretty ---------------- 112 MB (1 row) postgres=# |
向squeeze.tables插入一条数据，定期清理test表，每天的0点、2点、6点的10、30和50分别执行1次，空闲空间超过10%就会对表进行重建.
1 2 3 4 5 6 7 8 9 10 11 12 | postgres=# insert into squeeze.tables (tabschema, tabname, schedule, free_space_extra) values ('public', 'test', ('{10,30,50}', '{00,02,06}', NULL, NULL, NULL), '10'); INSERT 0 1 postgres=# select * from squeeze.tables; id | tabschema | tabname | clustering_index | rel_tablespace | ind_tablespaces | free_space_extra | min_size | vacuum_max_age | max_retry | skip_analyze | schedule ----+-----------+---------+------------------+----------------+-----------------+------------------+----------+----------------+-----------+--------------+------------------------ 1 | public | test | | | | 10 | 8 | 01:00:00 | 0 | f | ("{0,59}","{0,23}",,,) (1 row) postgres=# -- schedule的几个列表示：分钟、小时、天、月、周 |
“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才会进行检查。
启动pg_squeeze的进程需要调用
1 | SELECT squeeze.start_worker(); |
关闭命令:SELECT squeeze.stop_worker();
查看当前表的膨胀情况
1 2 3 4 5 | postgres=# select * from squeeze.tables_internal; table_id | last_task_finished ----------+-------------------- 1 | (1 row) |
删除test表数据：
1 2 3 4 5 6 7 8 9 10 11 12 13 | postgres=# SELECT pg_size_pretty(pg_total_relation_size('test')); pg_size_pretty ---------------- 112 MB (1 row) postgres=# delete from test where id < 800000; DELETE 799999 postgres=# SELECT pg_size_pretty(pg_total_relation_size('test')); pg_size_pretty ---------------- 112 MB (1 row) |
再次查看表的膨胀情况
1 | select * from squeeze.tables_internal; |
pg_repack与pg_squeeze对比
1.支持的PostgreSQL版本
pg_repack 1.4.5 PostgreSQL >= 9.1
pg_repack 1.4.6 PostgreSQL >= 9.4
pg_squeeze PostgreSQL >= 9.4
2.重组表的方式
pg_repack 基于触发器方式实现，对被重组的表，有一定的DML性能影响。
pg_squeeze 基于逻辑复制槽实现，重组时对原表的DML几乎没有性能影响，可能有复制槽争用，注意设置复制槽参数。
3.其它
pg_repack和pg_squeeze都需要表有主键或者非空唯一约束。
pg_squeeze目前支持的功能比pg_repack更灵活些，除了可手工或自动处理，也可以设置参数降低对后端进程的影响。
pg_squeeze当前版本1.3.1测试发现pg_show_plans不能同时使用。
参考
https://www.cnblogs.com/hxlasky/p/16951464.html
https://www.xmmup.com/pgshiyongchajianpg_repackjiejuebiaohesuoyindepengzhangwenti.html
