postgresql表膨胀处理之pgcompacttable部署及使用

环境：

1）redhat-release：CentOS Linux release 7.6.1810 (Core)

2）database version：postgresql 14.6

一、添加pgstattuple

pgcompacttable工具使用过程中需要依赖pgstattuple，因此需先添加pgstattuple。如果是源码安装的postgresql,则源码里包含了postgresql-contrib，因此，进行编译及安装即可。

--编译

cd /postgresql/soft/postgresql-14.6/contrib/pgstattuple

make

make install

本次实验没有编译也可以直接使用。

--在所需要使用的数据库里添加pgstattuple。

[postgres@tencent ~]$ psql -d testdb -Utest

psql (14.6)

Type "help" for help.

testdb=# select * from pg_available_extensions where name like 'pgstat%';

name | default_version | installed_version | comment

-------------+--------------+---------------+--------------------

pgstattuple | 1.5 | | show tuple-level statistics

(1 row)

testdb=#create extension pgstattuple;

CREATE EXTENSION

testdb=# select * from pg_available_extensions where name like 'pgstat%';

name | default_version | installed_version | comment

-------------+-----------------+-------------------+-----------------------------

pgstattuple | 1.5 | 1.5 | show tuple-level statistics

(1 row)

安装完成后，installed_version显示安装的版本

--查看pgstattuple可使用的函数

testdb=# \dxS+ pgstattuple

Objects in extension "pgstattuple"

Object description

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

function pg_relpages(regclass)

function pg_relpages(text)

function pgstatginindex(regclass)

function pgstathashindex(regclass)

function pgstatindex(regclass)

function pgstatindex(text)

function pgstattuple_approx(regclass)

function pgstattuple(regclass)

function pgstattuple(text)

(9 rows)

二、部署pgcompacttable

安装前需要安装pgstattuple插件，因为要基于该插件去查找膨胀的表，所以如果有大表的情况下，因为pgstattuple会扫全表，所以会比较耗时，这个在使用的时候一定要注意一下。

原理：

从表的头部填充新的行，在普通vacuum时候，截断表末尾的空page,达到收缩空间的效果。不需要占用额外的空间，使得表文件更加紧凑。而且不会加比较重的锁。对性能影响较小。

--安装依赖包

[root@tencent ~]# yum install perl-Time-HiRes perl-DBI perl-DBD-Pg –y

--上传解压安装包

上传pgcompacttable-master.zip 到/postgresql/soft目录，unzippgcompacttable-master.zip

--查看文件

[postgres@tencent soft]$ cd pgcompacttable-master/

[postgres@tencent pgcompacttable-master]$ ls

bin README.md

[postgres@tencent pgcompacttable-master]$ cd bin

[postgres@tencent bin]$ ls

Pgcompacttable

三、准备测试环境

3.1 创建表

--创建表

testdb=#create table test (id in,sex char(2),name varchar(10),now_address text,address text);

--插入数据

testdb=# 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

--创建索引

testdb=# create index on test(id,sex);

CREATE INDEX

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

CREATE INDEX

--查看表大小

testdb=# select pg_size_pretty(pg_relation_size('test'));

pg_size_pretty

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

1072 kB

(1 row)

testdb=# select count(*) from test;

count

-------

10000

(1 row)

3.2 模拟修改数据

创建脚本：

[postgres@tencent scripts]$ vi test_mod.sh

#!/bin/bash

#version: 1.0

#function: bulk update data

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

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

psql -Utest -h 127.0.0.1 -d testdb -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;

done