postgres数据库表空间收缩之pg_squeeze，pg_repack
目录
下半年一直忙于NP的sybase，通过大家的共同努力，NP年底比较稳定。很久没有弄过pg相关的知识了，最近经常看到有人问如何用工具自动清理pg的坏元组。
除了我们经常手动使用vacuum之外，生产环境还有两个比较常用的工具一个是pg_squeeze,另外一个是pg_repack
pg_squeeze1.2
项目地址：https://github.com/cybertec-postgresql/pg_squeeze
原理
pg_squeeze是一个扩展，它从表中删除未使用的空间，并且可以选择根据特定索引对元组进行排序，一般当一个表膨胀时一般使用vacuum full或者cluster进行表重建，在这一过程中会加排他锁，导致该表无法进行读写，只有等整个过程完成后才可以进行正常使用
优点
相比pg_repack或者pg_reorg，pg_squeeze不需要建触发器，所以在重组时对原表的DML几乎没有性能影响。pg_squeeze支持自动重组，可以设置定时清理时间以及根据空闲空间比例来进行清理表。该过程会自动启动worker进程，将数据复制到重组表，然后加锁，切换filenode。
安装
1、下载安装包后，解压后修改MakeFile，在MakeFile中加入pg_config
PG_CONFIG =/home/thunisoft5/arterybase/5.0/bin/pg_config
2、安装
make && make install
3、修改postgresql.conf配置文件
wal_level = logical
max_replication_slots = 1 # 大于等于1
shared_preload_libraries = 'pg_squeeze'
4、重启数据库
使用
1、创建扩展
postgres=# create extension pg_squeeze;
CREATE EXTENSION
postgres=# dx
List of installed extensions
Name | Version | Schema | Description
------------+---------+------------+------------------------------------------------
pg_squeeze | 1.2 | squeeze | A tool to remove unused space from a relation.
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
2、安装完成后会有一个对应的系统表
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)
4、给表test创建squeeze任务
--需要在表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)
5、启动和关闭pg_squeeze进程
select squeeze.start_worker();
select squeeze.stop_worker();
6、验证
--更新数据
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)
删除200w数据
--会自动清理
postgres=# dt+ test
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+-------+-------+-------------
public | test | table | sa | 85 MB |
(1 row)
如果执行vacuum full表还会变小吗？
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)
执行vacuum full后表的大小没有实质性改变，说明pg_squeeze清理比较彻底。
pgstattuple
pgstattuple返回一个关系的物理长度、"死亡"元组的百分比以及其他信息。
| 列 | 类型 | 描述 |
|---|---|---|
| table_len | bigint | 物理关系长度，以字节计 |
| tuple_count | bigint | 存活元组的数量 |
| tuple_len | bigint | 存活元组的总长度，以字节计 |
| tuple_percent | float8 | 存活元组的百分比 |
| dead_tuple_count | bigint | 死亡元组的数量 |
| dead_tuple_len | bigint | 死亡元组的总长度，以字节计 |
| dead_tuple_percent | float8 | 死亡元组的百分比 |
| free_space | bigint | 空闲空间总量，以字节计 |
| free_percent | float8 | 空闲空间的百分比 |
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)
临时处理
还可以手动“压缩”表，而无需注册，跳过任何时间和膨胀检查。
功能签名： 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);
监控方式
'squeeze.log'表在每个成功压缩的表中包含一个条目。
'squeeze.errors'包含在压缩期间发生的错误。这里报告的一个常见问题是有人更改了正在处理表的定义（例如，添加或删除的列）。
注意事项
pg_squeeze需要使用logical replication，所以需要设置足够的slots，而且必须注意可能与standby或者使用了逻辑复制功能争抢slots，要保证slots够用。
pg_squeeze可以自动收缩，对于比较繁忙的数据库，建议不要在业务高峰期启用，避免带来性能风险
首先，确保您的表具有主键或唯一约束。这是处理“ pg_squeeze”工作时其他事务可能进行的更改所必需的。
squeeze1.2和低版本的区别
新版本的squeeze有个更好的功能是：
- squeeze.tables表可以指定schedule：也就是指定气你的时间范围。你可以放到晚上来运行。
低版本pg_squeeze支持时间间隔的
-
task_interval：表示检查表膨胀是否超过阀值的时间间隔
-
first_check：表示第一次检查时间
相对来说直接在晚上定时执行vacuum full的方式更加简便
pg_repack
自述文件：和pg_squeeze一样pg_repack也是一个扩展，可以从表和索引中消除膨胀，并且可以选择恢复簇索引的物理顺序，与cluster和vacuum full不同，该工具可以在线工作，并且在处理过程中不需要在表上面持有排它锁（vacuum full工作需要access exclusive lock，导致任何操作都不能执行），pg_repack的启动效率很高，其性能与直接使用cluster相当
pg_repack老版本叫pg_reorg
原理
pg_repack原理和vacuum full原理类似，都是新建一个文件，然后将老文件拷贝过来，然后进行文件切换。不阻塞读写的秘诀就是新建文件和拷贝的过程是在线做的，在没有完成拷贝之前，原来的文件还是可以读写，只有在切表的一瞬间会有影响。
源库的数据文件一直在变化，pg_repack是如何拷贝的呢？表文件分为两部分，一部分是基础数据，一部分是增量数据，基础数据的拷贝是正常拷贝，增量数据是通过创建触发器来捕获该表上的读写操作来实现，基础数据拷贝完之后再将触发器捕获的增量sql进行应用，完成切换。
具体步骤：
- 创建一个日志表来记录对原始表所做的更改
- 在原始表上添加触发器，将INSERT，UPDATE和DELETE记录到我们的日志表中
- 创建一个新表，包含旧表中所有的行
- 在这个新表上建立索引
- 将日志表中产生的所有更改应用到新表中
- 使用系统目录交换表，包括索引和Toast表
- 删除原始表
当然我们在执行过程中从pg_stat_activity中也可以看到一些
-
执行过程中会给对应的表加上
ACCESS SHARE MODE -
然后执行数据拷贝的工作：INSERT INTO repack.table_16588 SELECT n_id,c_name FROM ONLY public.repack_test
-
最后创建索引：CREATE UNIQUE INDEX index_16595 ON repack.table_16588 USING btree (n_id) TABLESPACE pg_default
安装
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;
使用方法
选项：
| 参数 | 描述 |
|---|---|
| -a, --all | 重新包装所有数据库 |
| -t, --table=TABLE | 仅重新包装特定表 |
| -I, --parent-table=TABLE | 重新打包特定的父表及其继承者 |
| -c, --schema=SCHEMA | 仅在特定架构中重新打包表 |
| -s, --tablespace=TBLSPC | 将重新打包的表移动到新表空间 |
| -S, --moveidx | 将重新打包的索引也移动到TBLSPC |
| -o, --order-by=COLUMNS | 按列而不是集群键排序 |
| -n, --no-order | 真空吸尘而不是吸尘 |
| -N, --dry-run | 打印将重新包装的内容并退出 |
| -j, --jobs=NUM | 每个表使用这么多并行作业 |
| -i, --index=INDEX | 仅移动指定的索引 |
| -x, --only-indexes | 仅移动指定表的索引 |
| -T, --wait-timeout=SECS | 超时以取消冲突中的其他后端 |
| -D, --no-kill-backend | 超时时不要杀死其他后端 |
| -Z, --no-analyze | 最后不要分析 |
| -k, --no-superuser-check | 跳过客户端中的超级用户检查 |
| -C, --exclude-extension | 不要重新打包属于特定扩展名的表 |
连接选项：
| 参数 | 描述 |
|---|---|
| -d, --dbname=DBNAME | 数据库连接 |
| -h, --host=HOSTNAME | 数据库服务器主机或套接字目录 |
| -p, --port=PORT | 数据库服务器端口 |
| -U, --username=USERNAME | 连接的用户名 |
| -w, --no-password | 从不提示输入密码 |
| -W, --password | 强制输入密码提示 |
通用选项：
| 参数 | 描述 |
|---|---|
| -e, --echo | 回显查询 |
| -E, --elevel=LEVEL | 设置输出消息级别 |
| --help | 显示此帮助，然后退出 |
| --version | 输出版本信息，然后退出 |
测试
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)
系统表
repack.primary_keys
- indrelid代表表的oid，第二列indexrelid代表主键或者唯一索引的oid
repack.tables
- tables表记录了创建trigger以及捕获的相关语句，语句按一条条的record进行记录
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)
在线pg_repack
repack数据库
[thunisoft5@localhost bin]$ pg_repack -p 8543 -d postgres --no-order --jobs 8 --elevel=info
repack模式
pg_repack -p 8543 -d postgres --schema=public --no-order --jobs 8 --elevel=info
repack表和索引
pg_repack -p 8543 -d postgres --no-order --table public.repack_test --elevel=info
repack所有索引
pg_repack -p 8543 -d postgres --no-order --only-indexes --table public.repack_test --elevel=info
repack指定索引
pg_repack -p 8543 -d postgres --index public.repack_test_pkey --elevel=info
pg_repack限制
1、无法重组临时表
2、不能通过gist索引集群表
3、如果使用1.1.8或者更早的版本，则在运行pg_repack时，切勿尝试在目标表上面执行任何ddl命令。许多情况下，pg_repack会失败并正确回滚，但是在早期版本中，有一些情况可能会导致数据损坏
总结
pg_squeeze和pg_repack都需要表有主键或者非空唯一约束才行
pg_repack重组时，触发器会带来一定的开销，对被重组的表，有一定的DML性能影响。
pg_squeeze不需要建触发器，所以在重组时对原表的DML几乎没有性能影响。
pg_squeeze支持自动的重组，即通过设置阈值、比较用户表与阈值，自动启动WORKER进程，将数据复制到重组表，最后加锁，切换FILENODE。
pg_squeeze需要清理的表都需要在squeeze.tables表中插入对应的记录，并且可以对不同的表设置阈值和清理时间段。pg_repack可以针对库，schema以及表和索引分别清理
两个工具都可圈可点，pg_squeeze对系统的性能影响更小一些。当然也可以在晚上系统空闲时间直接使用vacuum full的方式来清理。