表膨胀清理的常用工具

原创 已于 2025-04-29 12:48:37 修改 · 867 阅读 · 20 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#数据库 #postgresql #表膨胀清理

于 2025-04-29 12:27:38 首次发布

Postgres SQL 实现的MVCC的机制不同于 oracle，mysql innodb 的 undo机制。表上所用的更新和删除等操作的行为，都不会实际的删除或修改，而是标记为死元祖（dead rows or dead tuples），也因此，在大表进行长事务增删改的时候，表的磁盘使用空间会逐渐变大，并且，表的读写性能会随着表膨胀的程度加深而逐渐下降。

那么表和索引的膨胀会造成两个后果，第一是磁盘空间的占用，比如，某个几百G的大表delete删除后，并不会释放磁盘空间，并且在删除的过程中还会引发wal日志的膨胀，而数据库服务器的磁盘空间并不是无限的，第二个就是会使得表的查询和写入性能下降，也就是查询速度降低或者插入/更新速度明显下降。

因此，我们在数据库的使用过程中，应该避免表膨胀，至少是将表膨胀控制在一个合理的，可接受的范围内，完全的避免表膨胀是确定无疑的不可能。

postgresql数据库对于表膨胀这个问题是有几种处理方式

第一，是在postgresql的主配置文件内定义autovacuum，也就是让postgresql数据库自己决定何时治理表膨胀。

第二，手动vacuum 治理表膨胀。

第三，CLUSTER命令治理表膨胀。

第四，利用外部插件，例如pg_repack、pgcompacttable、pg_sequence治理表膨胀。

第五，recreate table or reindex : 相当于重建表和索引。

对于大表来说，根据表的数据规模大小，很多时候vacuum或者CLUSTER的时候都是几个小时甚至十几个小时，而在此期间表被锁住是无法接受的，读写都有问题，锁表会造成业务的中断。

一、 pg_repack部署及使用

pg_repack的处理方式是创建一张新表，再将历史数据从原表中拷贝一份到新表。在拷贝过程中为了避免表被锁定，会创建了一个额外的日志表来记录原表的改动，并添加了一个涉及INSERT、UPDATE、DELETE操作的触发器将变更记录同步到日志表。当原始表中的数据全部导入到新表中，索引重建完毕以及日志表的改动全部完成后，pg_repack会用新表替换旧表，并将原旧表Drop掉。此工具过程简单且靠谱，但需要额外的磁盘空间来报错临时创建的中间表。

1.1 安装pg_repack

[postgres@jdcloud ~]$ unzip pg_repack-1.5.1.zip

[postgres@jdcloud ~]$ cd /postgresql/soft/pg_repack-1.5.1

[postgres@jdcloud pg_repack-1.5.1]$ make

[postgres@jdcloud pg_repack-1.5.1]$ make install

1.2 准备环境

postgres=# create database mydb;

CREATE DATABASE

postgres=# \c mydb

mydb=# create extension pgstattuple ;

CREATE EXTENSION

mydb=# create extension pg_repack;

CREATE EXTENSION

mydb=# create table test (id int,sex varchar(2),name varchar(8),now_address text,address text);

CREATE TABLE

mydb=# insert into test values(generate_series(1,10000),repeat( chr(int4(random()*26)+65),1),repeat( chr(int4(random()*26)+65),6),repeat( chr(int4(random()*26)+65),30),repeat( chr(int4(random()*26)+65),30));

INSERT 0 10000

mydb=# alter table test add primary key (id);

ALTER TABLE

mydb=# create index on test(name,now_address,address);

CREATE INDEX

1.3 模拟修改数据

#!/bin/bash

#version:  1.0

#function: bulk update data

for((i=1;i<=10000;i++)); 

do  

    a=`tr -dc A-Z[ < /dev/urandom | head -c1`

    psql -Upostgres -h 127.0.0.1 -d mydb -c "update  test set name=repeat( chr(int4(random()*26)+65),6),now_address=repeat( chr(int4(random()*26)+65),30),address=repeat( chr(int4(random()*26)+65),30)  where sex='$a';" >>/dev/null

    if [ $? == 0 ]

       then

           echo "$a is ok" >>update.log

       else

           echo "$a is close" >>update.err.log

       exit 1;

    fi

done

1.4 膨胀再现

由于我们表的基数是10000，autovacuum_vacuum_threshold是50 ， autovacuum_vacuum_scale_factor 是 0.2，所以触发 auto_vacuumn 的阀值是 50 + 10000 * 0.2 = 2050。

10000条数据的更新，达到阀值，所以触发 auto vacuum。

表数量没变，但是大小膨胀了几十倍。

1.5 pg_repack使用

[postgres@jdcloud pg_repack-1.5.1]$ pg_repack -h 127.0.0.1 -p 5432 -U postgres -W  -d mydb -t public.test -j 2  -k -D

Password:

NOTICE: Setting up workers.conns

INFO: repacking table "public.test"

LOG: Initial worker 0 to build index: CREATE INDEX index_16432 ON repack.table_16426 USING btree (name, now_address, address)

LOG: Initial worker 1 to build index: CREATE UNIQUE INDEX index_16481 ON repack.table_16426 USING btree (id)

LOG: Command finished in worker 1: CREATE UNIQUE INDEX index_16481 ON repack.table_16426 USING btree (id)

LOG: Command finished in worker 0: CREATE INDEX index_16432 ON repack.table_16426 USING btree (name, now_address, address)

postgres= SELECT * FROM pgstattuple('public.test');

表大小恢复到初始状态1104KB。

二、pgcompacttable部署及使用

pgcompacttable利用了PostgreSQL的一个有趣特性：在执行INSERT和UPDATE操作时，会将所有新版本的行移到表最开始的可用空间。此为pgcompacttable工具的关键，因为如果从末端反向开始更新所有行，最终所有可用空间被这些行填充，并将表尾部的空间全部释放以便让定期vacuum进行truncate。这样一来，pgcompacttable通过批量更新和vacuum强制移动，最终整个表被重新整理，达到压缩的效果。此工具对磁盘空间要求低，且性能影响可控。

pgcompacttable可以对database级别、schema级别、table级别进行压缩。

2.1 安装pgcompacttable

--依赖库安装，pgcompacttable是用Perl编写的，需要Perl DBI库

# yum install perl-Time-HiRes perl-DBI perl-DBD-Pg –y

--解压软件

[postgres@jdcloud soft]$ unzip pgcompacttable-master.zip

[postgres@jdcloud soft]$ cd pgcompacttable-master

[postgres@jdcloud pgcompacttable-master]$ ls

bin  LICENSE  README.md

[postgres@jdcloud pgcompacttable-master]$ cd bin

[postgres@jdcloud bin]$ ls

pgcompacttable

2.2 准备环境

postgres=# \c mydb

mydb=# create extension pgstattuple ;

CREATE EXTENSION

mydb=# create table test (id int primary key,sex varchar(2),name varchar(8),now_address text,address text);

CREATE TABLE

mydb=# insert into test values(generate_series(1,10000),repeat( chr(int4(random()*26)+65),1),repeat( chr(int4(random()*26)+65),6),repeat( chr(int4(random()*26)+65),30),repeat( chr(int4(random()*26)+65),30));

INSERT 0 10000

mydb=# create index on test(name,now_address,address);

CREATE INDEX

2.3 模拟修改数据

#!/bin/bash

#version:  1.0

#function: bulk update data

for((i=1;i<=10000;i++)); 

do  

    a=`tr -dc A-Z[ < /dev/urandom | head -c1`

    psql -Upostgres -h 127.0.0.1 -d mydb -c "update  test set name=repeat( chr(int4(random()*26)+65),6),now_address=repeat( chr(int4(random()*26)+65),30),address=repeat( chr(int4(random()*26)+65),30)  where sex='$a';" >>/dev/null

    if [ $? == 0 ]

       then

           echo "$a is ok" >>update.log

       else

           echo "$a is close" >>update.err.log

       exit 1;

    fi

done

2.4 膨胀再现

由于我们表的基数是10000，autovacuum_vacuum_threshold是50 ， autovacuum_vacuum_scale_factor 是 0.2，所以触发 auto_vacuumn 的阀值是 50 + 10000 * 0.2 = 2050。

10000条数据的更新，达到阀值，所以触发 auto vacuum。

表数量没变，但是大小膨胀了几十倍。

2.5 pgcompacttable使用

[postgres@jdcloud bin]$ ./pgcompacttable -h 127.0.0.1 -U postgres -d mydb -t test

[Fri Mar 28 12:43:32 2025] (mydb) Connecting to database

[Fri Mar 28 12:43:32 2025] (mydb) Postgres backend pid: 7128

[Fri Mar 28 12:43:32 2025] (mydb) Handling tables. Attempt 1

[Fri Mar 28 12:43:32 2025] (mydb:public.test) Statistics: 5057 pages (6306 pages including toasts and indexes), it is expected that ~96.090% (4859 pages) can be compacted with the estimated space saving being 37.962MB.

[Fri Mar 28 12:43:55 2025] (mydb:public.test) Reindex: public.test_pkey, initial size 57 pages(456.000KB), has been reduced by 47% (216.000KB), duration 0 seconds.

[Fri Mar 28 12:43:56 2025] (mydb:public.test) Reindex: public.test_name_now_address_address_idx, initial size 1186 pages(9.266MB), has been reduced by 91% (8.500MB), duration 0 seconds.

[Fri Mar 28 12:43:56 2025] (mydb:public.test) Processing results: 134 pages left (267 pages including toasts and indexes), size reduced by 38.461MB (47.180MB including toasts and indexes) in total.

[Fri Mar 28 12:43:56 2025] (mydb) Processing complete.

[Fri Mar 28 12:43:56 2025] (mydb) Processing results: size reduced by 38.461MB (47.180MB including toasts and indexes) in total.

[Fri Mar 28 12:43:56 2025] (mydb) Disconnecting from database

[Fri Mar 28 12:43:56 2025] Processing complete: 1 retries to process has been done

[Fri Mar 28 12:43:56 2025] Processing results: size reduced by 38.461MB (47.180MB including toasts and indexes) in total, 38.461MB (47.180MB) mydb.

三、pg_squeeze部署及使用

pg_squeeze是一个扩展，它从表中删除未使用的空间，并且可以选择根据特定索引对元组进行排序，一般当一个表膨胀时一般使用vacuum full或者cluster进行表重建，在这一过程中会加排他锁，导致该表无法进行读写，只有等整个过程完成后才可以进行正常使用。

pg_squeeze就是来解决这个问题的，它只会在最后切换filenode的过程中加锁，其他过程中是不影响读写的。

pg_squeeze的优点

相比pg_repack或pg_reorg，pg_squeeze不需要建触发器，所以在重组时对原表的DML几乎没有性能影响。安装

3.1 安装pg_sequence

解压文件，安装即可。

[postgres@jdcloud soft]$ unzip pg_squeeze-master.zip

[postgres@jdcloud soft]$ cd pg_squeeze-master

[postgres@jdcloud pg_squeeze-master]$ make

[postgres@jdcloud pg_squeeze-master]$ make install

修改postgresql.conf 参数文件并重启生效

#将pg_squeeze添加到现有库中。

shared_preload_libraries = 'pg_squeeze'

修改wal_level为logical

3.2 准备环境

mydb=# create extension pg_squeeze;

CREATE EXTENSION

mydb=# create table test (id int primary key,sex varchar(2),name varchar(8),now_address text,address text);

CREATE TABLE

mydb=# insert into test values(generate_series(1,10000),repeat( chr(int4(random()*26)+65),1),repeat( chr(int4(random()*26)+65),6),repeat( chr(int4(random()*26)+65),30),repeat( chr(int4(random()*26)+65),30));

INSERT 0 10000

mydb=# create index on test(name,now_address,address);

CREATE INDEX

3.3 模拟修改数据

#!/bin/bash

#version:  1.0

#function: bulk update data

for((i=1;i<=10000;i++)); 

do  

    a=`tr -dc A-Z[ < /dev/urandom | head -c1`

    psql -Upostgres -h 127.0.0.1 -d mydb -c "update  test set name=repeat( chr(int4(random()*26)+65),6),now_address=repeat( chr(int4(random()*26)+65),30),address=repeat( chr(int4(random()*26)+65),30)  where sex='$a';" >>/dev/null

    if [ $? == 0 ]

       then

           echo "$a is ok" >>update.log

       else

           echo "$a is close" >>update.err.log

       exit 1;

    fi

done

3.4 膨胀再现

由于我们表的基数是10000，autovacuum_vacuum_threshold是50 ， autovacuum_vacuum_scale_factor 是 0.2，所以触发 auto_vacuumn 的阀值是 50 + 10000 * 0.2 = 2050。

10000条数据的更新，达到阀值，所以触发 auto vacuum。

表数量没变，但是大小膨胀了几十倍。

3.5 pg_squeeze使用

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

 squeeze_table

---------------

(1 row)

开发常用工具大汇总
Z_xuanyi的博客
03-09 891
1.Visual Studio Visual Studio Productivity Power tool：Visual Studio专业版（及以上）的扩展，具有丰富的功能，如快速查找，导航解决方案，可搜索的附加参考对话框等 ReSharper：提高.NET开发人员生产力的工具，提高代码质量，通过提供快速修复消除错误，等等 MZ-Tools：它可以在方法、文件、项目、解决方案或项目组、选定的文本，文件组合或项目组合中找到字符串。结果示于下面的结果窗口中，比由Microsoft IDE提供的要更方便。
dav_pg12_表膨胀系列一 优化
最新发布
Dav_2099的博客
04-26 302
调整 autovacuum_vacuum_cost_limit 和 autovacuum_vacuum_cost_delay 控制清理对IO的影响。日志检查：确保日志中记录 autovacuum 活动（配置 log_autovacuum_min_duration = 0 可记录所有操作）。设置 idle_in_transaction_session_timeout 参数，控制长事务的存活时间。增大 autovacuum_max_workers。对于大表，建议使用分区，可以加快vacuum的速度。
Pg_squeeze 安装使用
高矮
03-25 1722
插件版本： OS：Ubuntu 16.04 Postgresql：9.6 目的： 为了回收和再利用通过表更新或者删除行所占用的磁盘空间，我们要对表进行定时的清理，利用VACUUM FULL这种形式会出现较长时间锁表情况，导致该表无法进行读写，只有等整个过程完成后才可以进行正常使用，因此考虑采用Pg_squeeze这一扩展插件，解决如上问题 Pg_squeeze工作原理： 安装： ...
【亲测免费】 推荐开源项目：pg_squeeze - 数据库优化利器
gitblog_00006的博客
05-24 523
推荐开源项目：pg_squeeze - 数据库优化利器 1、项目介绍 pg_squeeze 是一个针对 PostgreSQL 的扩展插件，其主要功能是高效清理表中的无用空间，并能按特定索引对数据进行排序，类似于并行的 CLUSTER 命令。它旨在替代 pg_repack 扩展，提供更简单和高效的数据库维护方案。 2、项目技术分析 pg_squeeze 的核心特性包括： 纯服务器端实现：无需客户端...
postgres数据库表空间收缩之pg_squeeze，pg_repack
u012736152的专栏
01-08 2978
#数据库表空间收缩之pg_squeeze，pg_repack 文章目录pg_squeeze1.2原理优点安装使用pgstattuple临时处理监控方式注意事项squeeze1.2和低版本的区别pg_repack原理安装使用方法测试系统表在线pg_repackrepack数据库repack模式repack表和索引repack所有索引repack指定索引pg_repack限制 下半年一直忙于NP的sybase，通过大家的共同努力，NP年底比较稳定。很久没有弄过pg相关的知识了，最近经常看到有人问如何用工具自动清
高效优化PostgreSQL数据库：pg_squeeze扩展推荐
gitblog_00490的博客
10-10 625
高效优化PostgreSQL数据库：pg_squeeze扩展推荐 项目介绍 pg_squeeze 是一个强大的PostgreSQL扩展，旨在优化数据库表的空间利用率，并支持在不影响常规读写操作的情况下对表进行排序。该扩展通过移除表中的未使用空间，并根据特定索引对元组进行排序，从而提升数据库的整体性能。pg_squeeze 的设计目标是替代传统的 pg_repack 扩展，提供更简洁、高效的解决方案...
pg_squeeze 项目常见问题解决方案
gitblog_00683的博客
11-13 938
pg_squeeze 项目常见问题解决方案 项目基础介绍和主要编程语言 pg_squeeze 是一个用于自动清理 PostgreSQL 数据库表中未使用空间的扩展。它通过逻辑解码来捕获并发更改，从而在不影响读写操作的情况下，对表进行压缩和排序。该项目的主要编程语言是 C 语言，因为它需要与 PostgreSQL 数据库服务器紧密集成。 新手使用项目时的注意事项及解决方案 1. 安装和配置问题 问题...
数据日志文件清理工具
05-19
数据日志文件清理工具是一种专门用于管理和优化数据库系统中日志文件的应用程序。在数据库运行过程中，日志文件会不断增长，记录所有的事务操作，以确保数据的一致性和可恢复性。然而，随着时间的推移，这些日志文件...
一键清理工具：优化Windows 10系统性能
膨胀清理工具的使用 文件中提到的“清理膨胀的Windows 10”是一个批量处理工具，用于自动化地执行清理任务。这表明存在专门的工具和方法来帮助用户清理系统，而不是手动寻找和删除文件。 ### 描述知识点：杀死膨胀...
【揭秘】注册表清理工具工作原理：用友OBA深度解析
本文首先对注册表清理工具进行了概述，并深入探讨了注册表的基础知识，包括其逻辑结构、主要键值和数据项，以及注册表膨胀的原因和对系统性能的影响。接着，详细分析了清理工具的理论基础，阐述了清理工具的目标、...
pg_squeeze简单使用
weixin_33688840的博客
03-02 412
概述一般当一个表膨胀时一般使用vacuum full或者cluster进行表重建，在这一过程中会加排他锁，导致该表无法进行读写，只有等整个过程完成后才可以进行正常使用。pg_squeeze就是来解决这个问题的，它只会在最后切换filenode的过程中加锁，其他过程中是不影响读写的。安装下面说下安装pg_squeeze需要注意的地方：1.先配置好PG_CO...
pg_squeeze
一名数据库爱好者的专栏
04-09 818
os: ubuntu 16.04 db: postgresql 10.6 pg_squeeze is an extension that removes unused space from a table and optionally sorts tuples according to particular index (as if CLUSTER [2] command was executed...
pg_squeeze：PostgreSQL自动清理膨胀空间的扩展
gitblog_00123的博客
10-11 486
pg_squeeze：PostgreSQL自动清理膨胀空间的扩展 项目介绍 pg_squeeze是Cybertec PostgreSQL团队开发的一个PostgreSQL数据库扩展，旨在自动化清理表中的未使用空间，并可选地根据特定索引对元组进行排序，如同并发执行CLUSTER命令一样。它设计用于替代pg_repack扩展，通过完全在服务器端实现功能并利用PostgreSQL数据库服务器的最新改进，...
Postgresql:pg_dump与pg_squeeze并行执行引起的死锁
高矮
07-11 832
组件版本： Postgresql:10.15 pg_squeeze:1.2 问题 PG数据库发生死锁，导致表不能正常查询 原因 如标题，我们在晚上对数据库表同时做pg_dump与pg_squeeze，导致数据库表发生死锁 为什么 因为pd_dump在执行时会对当前正在备份的表产生一个AccessShareLock锁，这时候对表进行查询写入是没问题的，但是如果对表做一些其它类似DDL的操作，DDL操作是要等AccessShareLock释放掉之后才会执行的，这就是造成我们线上表死锁的主要原因，因为pg_s.
pg_squeeze 项目教程
gitblog_00493的博客
10-11 640
pg_squeeze 项目教程 1. 项目的目录结构及介绍 pg_squeeze 是一个用于自动清理 PostgreSQL 表中未使用空间的扩展。以下是该项目的目录结构及其介绍： pg_squeeze/ ├── github/workflows/ │ └── expected ├── sql/ │ ├── pg_squeeze--1.2--1.3.sql │ ├── pg_squeez...
postgres收缩工具两种工具的使用对比
weixin_44847119的博客
08-08 1195
squeeze.tables: pg_squeeze任务安排表squeeze.tasks: pg_squeeze任务正在进行时的状态squeeze.log: pg_squeeze任务日志记录表squeeze.errors: pg_squeeze任务报错信息记录表。
pg_squeeze安装及简单使用
Silence Ray的博客
06-22 1465
环境：redhat 7.4 postgresql 12.3 pg_squeeze 1.2 pg_squeeze下载地址：https://github.com/cybertec-postgresql/pg_squeeze/ 介绍：pg_squeeze是一个扩展，它从表中删除未使用的空间，并且可以选择根据特定索引对元组进行排序，一般当一个表膨胀时一般使用vacuum full或者cluster进行表重建，在这一过程中会加排他锁，导致该表无法进行读写，只有等整个过程完成后才可以进行正常使用。 pg_sque..
pg_squeeze插件安装使用
wangbo_py的博客
12-25 1102
pg_squeeze插件安装使用 获取代码 在这个链接里可以选择需要下载的pg_squeeze版本（’-b’后的参数为选择的版本分支 ） git clone -b REL1_2 https://github.com/cybertec-postgresql/pg_squeeze.git 进入代码目录中，修改MakeFile，添加PG_CONFIG位置 cd pg_squeeze vi Makefi...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

king_harry

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值