【PostgreSQL】数据表膨胀(Table Bloat)的模拟和检测

PostgreSQL 数据表膨胀（Table Bloat）是指数据库表或索引在数据更新（如插入、删除、更新）后，所占用的磁盘空间增加，但实际有效数据量并未显著增长，导致存储空间的浪费。这种现象在 PostgreSQL 中比较常见，尤其是在高并发DML的更新操作下。

制造表膨胀场景

安装sysbench(以centos7系统为例)

sysbench 是一个开源的数据库性能测试工具，它可以用来评估不同类型的数据库管理系统（DBMS）的性能。sysbench 提供了多种测试场景，包括CPU、内存、磁盘I/O、数据库操作等。对于数据库压测，sysbench 提供了专门的数据库测试模块，可以模拟多种数据库操作，如插入、更新、删除、查询等。

# 1. 准备基础软件
yum -y install make automake libtool pkgconfig libaio-devel
yum -y install mariadb-devel openssl-devel mysql-devel
yum -y install postgresql-devel
# 2. 编译安装
export SYSBENCH_HOME=/data/software/sysbench-1.0.16
wget https://github.com/akopytov/sysbench/archive/1.0.16.tar.gz
tar zxf 1.0.16.tar.gz
cd sysbench-1.0.16
./autogen.sh
./configure --with-pgsql --prefix=${SYSBENCH_HOME}
make -sj
make install

安装完成，验证：

sysbench --version
sysbench 1.0.15

使用sysbench制造表膨胀

创建测试用户和数据库

export PG_HOST=1.pg.test_group.com
export PG_PORT=5432

psql -h $PG_HOST -p $PG_PORT -U postgres -d postgres <<EOF
CREATE DATABASE sbtest;
CREATE USER sbtest WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE sbtest TO sbtest;
EOF

prepare阶段

下面命令为数据库sbtest中的10个表（sbtest1至sbtest10）每个表生成50万行数据，schema是默认的public。数据是由一个名为parallel_prepare.lua的脚本准备的，该脚本可在${SYSBENCH_HOME}/share/sysbench/tests/include/oltp_legacy路径下找到。

export SYSBENCH_HOME=/data/software/sysbench-1.0.16
export PG_HOST=1.pg.test_group.com
export PG_PORT=5432

sysbench  \
--db-driver=pgsql  \
--oltp-table-size=500000 \
--oltp-tables-count=10  \
--threads=1  \
--pgsql-host=${PG_HOST} \
--pgsql-port=${PG_PORT} \
--pgsql-user=sbtest  \
--pgsql-password=password  \
--pgsql-db=sbtest  \
${SYSBENCH_HOME}/share/sysbench/tests/include/oltp_legacy/parallel_prepare.lua \
run

workload阶段

下面命令将从名为${SYSBENCH_HOME}/share/sysbench/tests/include/oltp_legacy/oltp.lua的LUA脚本生成OLTP工作负载，针对50万行的10个表，使用64个工作线程在PG_HOST（主节点）上运行60秒。每2秒报告一次中间统计数据（–report-interval=2）。

export SYSBENCH_HOME=/data/software/sysbench-1.0.16
export PG_HOST=1.pg.test_group.com
export PG_PORT=5432

sysbench  \
--db-driver=pgsql  \
--report-interval=2  \
--oltp-table-size=500000  \
--oltp-tables-count=10  \
--threads=64  \
--time=60  \
--pgsql-host=${PG_HOST} \
--pgsql-port=${PG_PORT} \
--pgsql-user=sbtest  \
--pgsql-password=password  \
--pgsql-db=sbtest  \
${SYSBENCH_HOME}/share/sysbench/tests/include/oltp_legacy/oltp.lua \
run

监控表膨胀

编写 SQL 查询来监控表的膨胀情况

SELECT relname AS TABLE_NAME,
	pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) AS table_size,
	n_dead_tup,
	n_live_tup,
	(n_dead_tup * 100 / (n_live_tup + n_dead_tup))AS dead_tup_ratio
FROM pg_stat_user_tables 
WHERE n_dead_tup<>0 order by n_dead_tup LIMIT 10;

可输出如下结果

 table_name | table_size | n_dead_tup | n_live_tup | dead_tup_ratio 
------------+------------+------------+------------+----------------
 sbtest1    | 123 MB     |      16227 |     500000 |              3
 sbtest10   | 123 MB     |      16316 |     500000 |              3
 sbtest8    | 123 MB     |      16333 |     500000 |              3
 sbtest5    | 123 MB     |      16399 |     500000 |              3
 sbtest4    | 123 MB     |      16426 |     500000 |              3
 sbtest3    | 123 MB     |      16450 |     500000 |              3
 sbtest9    | 123 MB     |      16599 |     500000 |              3
 sbtest7    | 123 MB     |      16601 |     500000 |              3
 sbtest6    | 123 MB     |      16702 |     500000 |              3
 sbtest2    | 123 MB     |      16827 |     500000 |              3
(10 rows)

处理表膨胀

使用 pg_repack 或 pg_reorg 工具

如果你发现表确实存在膨胀问题，可以使用 pg_repack 或 pg_reorg 这样的工具来重新组织表和索引，以回收空间。这些工具可以在不锁定表的情况下工作，对生产环境影响较小。

调整 autovacuum 参数

PostgreSQL 的 autovacuum 参数可以帮助自动回收不再需要的空间。合理调整这些参数可以减少表膨胀的发生。例如，可以调整 autovacuum_vacuum_scale_factor 和 autovacuum_vacuum_threshold 参数。

定期手动维护

定期执行 VACUUM 或 VACUUM FULL 命令可以帮助回收空间。VACUUM FULL 会创建一个新的表文件来替换旧的表，这在生产环境中可能需要谨慎使用，因为它会锁表。