postgresql|数据库|pg_repack插件的部署和使用

一，

表和索引的膨胀现象

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

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

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

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

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

第二，手动vacuum 治理表膨胀

第三，CLUSTER命令治理表膨胀

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

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

• 如果选择重建表的话 是类似于 create table tab_new as select * from tab_old, 然后在 创建相关索引，最后进行表名的 rename 切换。还需注意表的权限：需要重新赋权。
• 另外这个也是需要应用系统的维护窗口时间的。
• 如果选择重建索引的话， 类似于 reindex CONCURRENTLY index_name, 需要注意的是需要2倍的索引存储空间，进行online的索引重建。

CLUSTER背后的代码与VACUUM (FULL)相同，只是增加了一个排序。因此，CLUSTER存在和VACUUM (FULL)一样的问题:

• CLUSTER以ACCESS EXCLUSIVE模式锁定表，锁定期间阻塞所有操作，VACUUM (FULL)也是一样的
• 需要二倍于表的空间进行操作

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

pg_repack 锁表的时间相对vacuum或者cluster来说是比较少的，大概是vacuum的锁表时间的20%，因此，pg_repack 是一个比较好的选择，但pg_repack 的使用仍然是推荐在业务低峰期使用，虽然锁表时间大幅减少。

repack 实际上是创建了一张临时表， 并在原始表上创建触发器捕获数据变化，同步到临时表中， 并在临时表中重新创建索引，最后进行临时表和原始表的切换。
工作原理和mysql 的 pt-online-schema-change 的工具是十分类似的.

下面将就pg_repack 的部署和基本使用做一个介绍

二，

表膨胀治理的时机

在表膨胀治理之前，我们需要了解哪些表需要治理，在表膨胀治理之后，我们需要清楚的知道，具体治理了多少表膨胀

监控数据库级别的膨胀：Show database bloat - PostgreSQL wiki

SQL语句如下：

这个SQL语句比较简略，主要关注上图标识的这两行，wastedbytes的值越大，表明表膨胀越严重

SELECT
  current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
  ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::float/otta END)::numeric,1) AS tbloat,
  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes,
  iname, /*ituples::bigint, ipages::bigint, iotta,*/
  ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::float/iotta END)::numeric,1) AS ibloat,
  CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
FROM (
  SELECT
    schemaname, tablename, cc.reltuples, cc.relpages, bs,
    CEIL((cc.reltuples*((datahdr+ma-
      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta,
    COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
  FROM (
    SELECT
      ma,bs,schemaname,tablename,
      (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
      (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
    FROM (
      SELECT
        schemaname, tablename, hdr, ma, bs,
        SUM((1-null_frac)*avg_width) AS datawidth,
        MAX(null_frac) AS maxfracsum,
        hdr+(
          SELECT 1+count(*)/8
          FROM pg_stats s2
          WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
        ) AS nullhdr
      FROM pg_stats s, (
        SELECT
          (SELECT current_setting('block_size')::numeric) AS bs,
          CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
          CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
        FROM (SELECT version() AS v) AS foo
      ) AS constants
      GROUP BY 1,2,3,4,5
    ) AS foo
  ) AS rs
  JOIN pg_class cc ON cc.relname = rs.tablename
  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
  LEFT JOIN pg_index i ON indrelid = cc.oid
  LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
ORDER BY wastedbytes DESC

监控表级别的膨胀：

https://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/table_bloat.sql

注：稍作修改，屏蔽了系统库

该SQL语句执行完后，重点关注上图标识的地方就行了，blooat_pct 越大，表明表膨胀越严重

/* WARNING: executed with a non-superuser role, the query inspect only tables and materialized view (9.3+) you are granted to read.
* This query is compatible with PostgreSQL 9.0 and more
*/
SELECT current_database(), schemaname, tblname, bs*tblpages AS real_size,
  (tblpages-est_tblpages)*bs AS extra_size,
  CASE WHEN tblpages > 0 AND tblpages - est_tblpages > 0
    THEN 100 * (tblpages - est_tblpages)/tblpages::float
    ELSE 0
  END AS extra_pct, fillfactor,
  CASE WHEN tblpages - est_tblpages_ff > 0
    THEN (tblpages-est_tblpages_ff)*bs
    ELSE 0
  END AS bloat_size,
  CASE WHEN tblpages > 0 AND tblpages - est_tblpages_ff > 0
    THEN 100 * (tblpages - est_tblpages_ff)/tblpages::float
    ELSE 0
  END AS bloat_pct, is_na
  -- , tpl_hdr_size, tpl_data_size, (pst).free_percent + (pst).dead_tuple_percent AS real_frag -- (DEBUG INFO)
FROM (
  SELECT ceil( reltuples / ( (bs-page_hdr)/tpl_size ) ) + ceil( toasttuples / 4 ) AS est_tblpages,
    ceil( reltuples / ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff,
    tblpages, fillfactor, bs, tblid, schemaname, tblname, heappages, toastpages, is_na
    -- , tpl_hdr_size, tpl_data_size, pgstattuple(tblid) AS pst -- (DEBUG INFO)
  FROM (
    SELECT
      ( 4 + tpl_hdr_size + tpl_data_size + (2*ma)
        - CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END
        - CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE ceil(tpl_data_size)::int%ma END
      ) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages,
      toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, fillfactor, is_na
      -- , tpl_hdr_size, tpl_data_size
    FROM (
      SELECT
        tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples,
        tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages,
        coalesce(toast.reltuples, 0) AS toasttuples,
        coalesce(substring(
          array_to_string(tbl.reloptions, ' ')
          FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor,
        current_setting('block_size')::numeric AS bs,
        CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,
        24 AS page_hdr,
        23 + CASE WHEN MAX(coalesce(s.null_frac,0)) > 0 THEN ( 7 + count(s.attname) ) / 8 ELSE 0::int END
           + CASE WHEN bool_or(att.attname = 'oid' and att.attnum < 0) THEN 4 ELSE 0 END AS tpl_hdr_size,
        sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 0) ) AS tpl_data_size,
        bool_or(att.atttypid = 'pg_catalog.name'::regtype)
          OR sum(CASE WHEN att.attnum > 0 THEN 1 ELSE 0 END) <> count(s.attname) AS is_na
      FROM pg_attribute AS att
        JOIN pg_class AS tbl ON att.attrelid = tbl.oid
        JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
        LEFT JOIN pg_stats AS s ON s.schemaname=ns.nspname
          AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname
        LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
      WHERE NOT att.attisdropped
        AND tbl.relkind in ('r','m')
				AND schemaname not IN('pg_catalog','information_schema','repack')
      GROUP BY 1,2,3,4,5,6,7,8,9,10
      ORDER BY 2,3
    ) AS s
  ) AS s2
) AS s3
-- WHERE NOT is_na
--   AND tblpages*((pst).free_percent + (pst).dead_tuple_percent)::float4/100 >= 1
ORDER BY schemaname, tblname;

 监控索引级别的膨胀：

https://github.com/ioguix/pgsql-bloat-estimation/blob/master/btree/btree_bloat.sql

注：稍作修改，屏蔽了系统库

-- WARNING: executed with a non-superuser role, the query inspect only index on tables you are granted to read.
-- WARNING: rows with is_na = 't' are known to have bad statistics ("name" type is not supported).
-- This query is compatible with PostgreSQL 8.2 and after
SELECT current_database(), nspname AS schemaname, tblname, idxname, bs*(relpages)::bigint AS real_size,
  bs*(relpages-est_pages)::bigint AS extra_size,
  100 * (relpages-est_pages)::float / relpages AS extra_pct,
  fillfactor,
  CASE WHEN relpages > est_pages_ff
    THEN bs*(relpages-est_pages_ff)
    ELSE 0
  END AS bloat_size,
  100 * (relpages-est_pages_ff)::float / relpages AS bloat_pct,
  is_na
  -- , 100-(pst).avg_leaf_density AS pst_avg_bloat, est_pages, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples, relpages -- (DEBUG INFO)
FROM (
  SELECT coalesce(1 +
         ceil(reltuples/floor((bs-pageopqdata-pagehdr)/(4+nulldatahdrwidth)::float)), 0 -- ItemIdData size + computed avg size of a tuple (nulldatahdrwidth)
      ) AS est_pages,
      coalesce(1 +
         ceil(reltuples/floor((bs-pageopqdata-pagehdr)*fillfactor/(100*(4+nulldatahdrwidth)::float))), 0
      ) AS est_pages_ff,
      bs, nspname, tblname, idxname, relpages, fillfactor, is_na
      -- , pgstatindex(idxoid) AS pst, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples -- (DEBUG INFO)
  FROM (
      SELECT maxalign, bs, nspname, tblname, idxname, reltuples, relpages, idxoid, fillfactor,
            ( index_tuple_hdr_bm +
                maxalign - CASE -- Add padding to the index tuple header to align on MAXALIGN
                  WHEN index_tuple_hdr_bm%maxalign = 0 THEN maxalign
                  ELSE index_tuple_hdr_bm%maxalign
                END
              + nulldatawidth + maxalign - CASE -- Add padding to the data to align on MAXALIGN
                  WHEN nulldatawidth = 0 THEN 0
                  WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign
                  ELSE nulldatawidth::integer%maxalign
                END
            )::numeric AS nulldatahdrwidth, pagehdr, pageopqdata, is_na
            -- , index_tuple_hdr_bm, nulldatawidth -- (DEBUG INFO)
      FROM (
          SELECT n.nspname, i.tblname, i.idxname, i.reltuples, i.relpages,
              i.idxoid, i.fillfactor, current_setting('block_size')::numeric AS bs,
              CASE -- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?)
                WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8
                ELSE 4
              END AS maxalign,
              /* per page header, fixed size: 20 for 7.X, 24 for others */
              24 AS pagehdr,
              /* per page btree opaque data */
              16 AS pageopqdata,
              /* per tuple header: add IndexAttributeBitMapData if some cols are null-able */
              CASE WHEN max(coalesce(s.null_frac,0)) = 0
                  THEN 8 -- IndexTupleData size
                  ELSE 8 + (( 32 + 8 - 1 ) / 8) -- IndexTupleData size + IndexAttributeBitMapData size ( max num filed per index + 8 - 1 /8)
              END AS index_tuple_hdr_bm,
              /* data len: we remove null values save space using it fractionnal part from stats */
              sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) AS nulldatawidth,
              max( CASE WHEN i.atttypid = 'pg_catalog.name'::regtype THEN 1 ELSE 0 END ) > 0 AS is_na
          FROM (
              SELECT ct.relname AS tblname, ct.relnamespace, ic.idxname, ic.attpos, ic.indkey, ic.indkey[ic.attpos], ic.reltuples, ic.relpages, ic.tbloid, ic.idxoid, ic.fillfactor,
                  coalesce(a1.attnum, a2.attnum) AS attnum, coalesce(a1.attname, a2.attname) AS attname, coalesce(a1.atttypid, a2.atttypid) AS atttypid,
                  CASE WHEN a1.attnum IS NULL
                  THEN ic.idxname
                  ELSE ct.relname
                  END AS attrelname
              FROM (
                  SELECT idxname, reltuples, relpages, tbloid, idxoid, fillfactor, indkey,
                      pg_catalog.generate_series(1,indnatts) AS attpos
                  FROM (
                      SELECT ci.relname AS idxname, ci.reltuples, ci.relpages, i.indrelid AS tbloid,
                          i.indexrelid AS idxoid,
                          coalesce(substring(
                              array_to_string(ci.reloptions, ' ')
                              from 'fillfactor=([0-9]+)')::smallint, 90) AS fillfactor,
                          i.indnatts,
                          pg_catalog.string_to_array(pg_catalog.textin(
                              pg_catalog.int2vectorout(i.indkey)),' ')::int[] AS indkey
                      FROM pg_catalog.pg_index i
                      JOIN pg_catalog.pg_class ci ON ci.oid = i.indexrelid
                      WHERE ci.relam=(SELECT oid FROM pg_am WHERE amname = 'btree')
                      AND ci.relpages > 0
                  ) AS idx_data
              ) AS ic
              JOIN pg_catalog.pg_class ct ON ct.oid = ic.tbloid
              LEFT JOIN pg_catalog.pg_attribute a1 ON
                  ic.indkey[ic.attpos] <> 0
                  AND a1.attrelid = ic.tbloid
                  AND a1.attnum = ic.indkey[ic.attpos]
              LEFT JOIN pg_catalog.pg_attribute a2 ON
                  ic.indkey[ic.attpos] = 0
                  AND a2.attrelid = ic.idxoid
                  AND a2.attnum = ic.attpos
            ) i
            JOIN pg_catalog.pg_namespace n ON n.oid = i.relnamespace
            JOIN pg_catalog.pg_stats s ON s.schemaname = n.nspname
                                      AND s.tablename = i.attrelname
                                      AND s.attname = i.attname
																			AND schemaname not IN('pg_catalog','information_schema','repack')
            GROUP BY 1,2,3,4,5,6,7,8,9,10,11
      ) AS rows_data_stats
  ) AS rows_hdr_pdg_stats
) AS relation_stats
ORDER BY nspname, tblname, idxname;

三，

pg_repack的部署

pg_repack现在支持到postgresql-16版本，下载地址：https://github.com/reorg/pg_repack/releases/tag/ver_1.5.0

下载下来的压缩包上传到服务器后，进入解压目录

先安装编译依赖：

yum install openssl openssl-devel readline readline-devel -y

然后编译三连就好了：

make && make install

编译日志如下：

[root@centos10 pg_repack-ver_1.5.0]# make
make[1]: Entering directory `/root/pg_repack-ver_1.5.0/bin'
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 pg_repack.o pgut/pgut.o pgut/pgut-fe.o  -L/data/pgsql/lib   -Wl,--as-needed -Wl,-rpath,'/data/pgsql/lib',--enable-new-dtags  -L/data/pgsql/lib -lpq -L/data/pgsql/lib -lpgcommon -lpgport -lpthread -lz -lreadline -lrt -lcrypt -ldl -lm -o pg_repack
make[1]: Leaving directory `/root/pg_repack-ver_1.5.0/bin'
make[1]: Entering directory `/root/pg_repack-ver_1.5.0/lib'
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -DREPACK_VERSION=1.5.0 -I. -I./ -I/data/pgsql/include/server -I/data/pgsql/include/internal  -D_GNU_SOURCE   -c -o repack.o repack.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -DREPACK_VERSION=1.5.0 -I. -I./ -I/data/pgsql/include/server -I/data/pgsql/include/internal  -D_GNU_SOURCE   -c -o pgut/pgut-spi.o pgut/pgut-spi.c
( echo '{ global:'; gawk '/^[^#]/ {printf "%s;\n",$1}' exports.txt; echo ' local: *; };' ) >exports.list
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -Wl,--version-script=exports.list -o pg_repack.so repack.o pgut/pgut-spi.o -L/data/pgsql/lib    -Wl,--as-needed -Wl,-rpath,'/data/pgsql/lib',--enable-new-dtags  
sed 's,REPACK_VERSION,1.5.0,g' pg_repack.sql.in \
| sed 's,relhasoids,false,g'> pg_repack--1.5.0.sql;
sed 's,REPACK_VERSION,1.5.0,g' pg_repack.control.in > pg_repack.control
make[1]: Leaving directory `/root/pg_repack-ver_1.5.0/lib'
make[1]: Entering directory `/root/pg_repack-ver_1.5.0/regress'
make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/root/pg_repack-ver_1.5.0/regress'
[root@centos10 pg_repack-ver_1.5.0]# echo $?
0
[root@centos10 pg_repack-ver_1.5.0]# make install
make[1]: Entering directory `/root/pg_repack-ver_1.5.0/bin'
/usr/bin/mkdir -p '/data/pgsql/bin'
/usr/bin/install -c  pg_repack '/data/pgsql/bin'
make[1]: Leaving directory `/root/pg_repack-ver_1.5.0/bin'
make[1]: Entering directory `/root/pg_repack-ver_1.5.0/lib'
/usr/bin/mkdir -p '/data/pgsql/lib'
/usr/bin/mkdir -p '/data/pgsql/share/extension'
/usr/bin/mkdir -p '/data/pgsql/share/extension'
/usr/bin/install -c -m 755  pg_repack.so '/data/pgsql/lib/pg_repack.so'
/usr/bin/install -c -m 644 .//pg_repack.control '/data/pgsql/share/extension/'
/usr/bin/install -c -m 644  pg_repack--1.5.0.sql pg_repack.control '/data/pgsql/share/extension/'
make[1]: Leaving directory `/root/pg_repack-ver_1.5.0/lib'
make[1]: Entering directory `/root/pg_repack-ver_1.5.0/regress'
make[1]: Nothing to be done for `install'.
make[1]: Leaving directory `/root/pg_repack-ver_1.5.0/regress'
[root@centos10 pg_repack-ver_1.5.0]# 
[root@centos10 pg_repack-ver_1.5.0]# cd
[root@centos10 ~]# whereis pg_repack
pg_repack: /data/pgsql/bin/pg_repack

登录postgresql的命令行，激活插件，这里是哪个数据库需要此插件就切换到哪个数据库内：

比如，我需要在名为test的数据库内使用此插件：

\c test
create extension pg_repack;

此插件激活后，将会看到一个名为repack的scheme和一系列相关函数和两个视图：

四，

 pg_repack的表膨胀治理能力测试

1，

创建新库和大表

新库名称为test，大表的创建语句如下：

大表名称为testpg，数据量级为2000w，只有两列数据

create or replace function gen_id(  
 a date,  
 b date  
)   
returns text as $$  
select lpad((random()*99)::int::text, 3, '0') ||   
    lpad((random()*99)::int::text, 3, '0') ||   
    lpad((random()*99)::int::text, 3, '0') ||   
    to_char(a + (random()*(b-a))::int, 'yyyymmdd') ||   
    lpad((random()*99)::int::text, 3, '0') ||   
    random()::int ||   
    (case when random()*10 >9 then 'xy' else (random()*9)::int::text end ) ;  
$$ language sql strict;



CREATE SEQUENCE test START 1;
create table if not exists testpg (
	"id" int8 not null DEFAULT nextval('test'::regclass),
	CONSTRAINT "user_vendorcode_pkey" PRIMARY KEY ("id"),
	"suijishuzi" VARCHAR ( 255 ) COLLATE "pg_catalog"."default"
);


insert into testpg SELECT generate_series(1,20000000) as xm, gen_id('1949-01-01', '2023-10-16') as num;

2，

查看大表大小和该表的膨胀情况，SQL语句如下：

SELECT
table_schema,
table_name,
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
table_schema,
table_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
pg_total_relation_size(table_name) AS total_size
FROM (
SELECT table_schema,('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.tables
) AS all_tables
ORDER BY total_size DESC
) AS pretty_sizes where table_schema not in ('pg_catalog','information_schema','repack');

输出如下：

使用上面的第二个查询表膨胀的语句，结果如下：

3，

大量更新testpg这个表，人为制造表膨胀：

UPDATE testpg set suijishuzi='123456789' WHERE suijishuzi like'%1%'

查看表大小和膨胀率：

可以看到，膨胀率达到了45%

4，

pg_repack表膨胀治理：

[root@centos10 ~]# su - postgres -c "/data/pgsql/bin/pg_repack -d test  -t public.testpg"
INFO: repacking table "public.testpg"

在膨胀治理期间，可以正常的对该表读写，治理完毕后，查看表大小和表膨胀率：

可以看到，膨胀治理完全成功，

观察磁盘使用，可以看到，符合本次膨胀治理的结果：

[root@centos10 ~]# du -sh /data/pgsql/data/
4.0G	/data/pgsql/data/
[root@centos10 ~]# du -sh /data/pgsql/data/
2.7G	/data/pgsql/data/

未完待续！！！