合 PG使用插件pg_squeeze收缩膨胀的表和索引
Tags: PGPostgreSQL插件表膨胀膨胀索引膨胀收缩pg_squeeze
简介
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 19 | -- 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 cat >> /var/lib/pgsql/14/data/postgresql.conf <<"EOF" wal_level = logical max_replication_slots = 10 shared_preload_libraries = 'pg_squeeze' EOF 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}')); |