PostgreSQL的MVCC（多版本并发控制）机制和表膨胀的解决方法

结论：PG数据库导致表膨胀的原因是逻辑删除的历史数据清除不及时

表及索引的膨胀
主流的MVCC机制如下：
1、以Oracle和Innodb为例，写如新数据时，把旧数据转移到一个单独的地方，如回滚段中，如果其他人读取数据时，从回滚段中把数据读取出来，可以有效避免膨胀。
2、以SQL Server为代表的，把旧版本的的数据写入专门的临时表空间中，新数据写入日志中，然后去更改数据，也可以有效地避免膨胀。
3、PG修改时，旧数据不删除，只是标注为无效（逻辑删除，在某个时间再去清理）

Oracle和Innodb的undo实现形式：
Oracle最经典的错误就是回滚段快照过旧，意思是回滚段中的数据还在被某些事务使用，但是数据在回滚段中已经被删除了，Innodb也有这样的问题。
如果在一个超大事务执行过程中，事务失败了，它会回滚，产生大量cpu和IO占用（因为要从undo segment回滚段，导入data segment中）。

PG的优点
1、无论进行多少操作，事务回滚都会立即完成（秒级回滚），Oracle使用了回滚段，如果Oracle宕机时很多事务正在运行，再次启动后，需要把之前的事务回滚（具体的一会儿再说），当没有回滚完成时数据行上仍然有锁，业务不能正常操作，如果需要回滚的业务量很大，情况会很坏。
2、可以放心执行超大事务不必像Oracle和Innodb那样需要经常保证回滚段不会被用完，也不用像Oracle那样会遇到ORA-1555错误的困扰。
PG的缺点：
1、旧版本数据需要清理，在8.3版本引入了autovacuum，采用多进程架构，支持多表的同时操作。
2、旧版本的数据清理不及时会导致性能下降。
3、空间持续上涨，存储没有得到有效的利用。

原因：
表及索引膨胀是指随着时间推移，表和索引的物理存储结构逐渐变得不连续或者产生空洞，导致数据库性能下降的问题。这种情况通常由以下几个原因引起：
1、更新和删除操作频繁（vacuum会清理它留下来的历史版本）：
当表中的行经常被更新或删除时，PostgreSQL 的MVCC（多版本并发控制）机制（历史数据回收不及时）可能会导致数据页面的分裂和空洞的产生。例如，当更新或删除一行后，原来的数据页可能留下了空洞或者变得不再紧凑。
2、自动化的 VACUUM 过程不足够频繁：
PostgreSQL 使用 VACUUM 进程来回收被删除行所占用的空间，并且重新组织数据以减少表和索引的膨胀。如果 VACUUM 过程不足够频繁，或者数据库负载很高，可能会导致表和索引的膨胀问题积累。
3、长时间运行的事务：
长时间运行的事务可以阻碍 VACUUM 进程的正常工作，从而使得表和索引无法及时进行空间的回收和重新组织。
4、未正确设置和优化参数：
PostgreSQL 的参数设置（如 autovacuum 相关参数、vacuum_cost_delay、maintenance_work_mem 等）可能影响 VACUUM 进程的执行效率和频率，进而影响表和索引的膨胀问题的处理。
解决方法：
定期监控数据库的性能指标，如表和索引的空间利用率，以及 VACUUM 进程的运行情况。根据监控结果调整数据库配置和优化策略。

常见问题及处理流程

表膨胀查询SQL（安装表膨胀系数倒叙排序）
 SELECT
  current_database() AS db, schemaname, tablename, 
  ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
  CASE WHEN relpages < otta THEN
    CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END
    ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint)
      ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END
  END AS totalwastedbytes
FROM (
  SELECT
    nn.nspname AS schemaname,
    cc.relname AS tablename,
    COALESCE(cc.reltuples,0) AS reltuples,
    COALESCE(cc.relpages,0) AS relpages,
    COALESCE(bs,0) AS bs,
    COALESCE(CEIL((cc.reltuples*((datahdr+ma-
      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) 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
     pg_class cc
  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> $$information_schema$$
  LEFT JOIN
  (
    SELECT
      ma,bs,foo.nspname,foo.relname,
      (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
        ns.nspname, tbl.relname, hdr, ma, bs,
        SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,
        MAX(coalesce(null_frac,0)) AS maxfracsum,
        hdr+(
          SELECT 1+count(*)/8
          FROM pg_stats s2
          WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname
        ) AS nullhdr
      FROM pg_attribute att
      JOIN pg_class tbl ON att.attrelid = tbl.oid
      JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
      LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
      AND s.tablename = tbl.relname
      AND s.inherited=false
      AND s.attname=att.attname,
      (
        SELECT
          (SELECT current_setting($$block_size$$)::numeric) AS bs,
            CASE WHEN SUBSTRING(SPLIT_PART(v, $$ $$, 2) FROM $$#"[0-9]+.[0-9]+#"%$$ for $$#$$)
              IN ($$8.0$$,$$8.1$$,$$8.2$$) THEN 27 ELSE 23 END AS hdr,
          CASE WHEN v ~ $$mingw32$$ OR v ~ $$64-bit$$ THEN 8 ELSE 4 END AS ma
        FROM (SELECT version() AS v) AS foo
      ) AS constants
      WHERE att.attnum > 0 AND tbl.relkind=$$r$$
      GROUP BY 1,2,3,4,5
    ) AS foo
  ) AS rs
  ON cc.relname = rs.relname AND nn.nspname = rs.nspname
  LEFT JOIN pg_index i ON indrelid = cc.oid
  LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml order by tbloat desc limit 5;


#查看当前会话的客户端ID和服务器ID
select inet_server_addr(),inet_client_addr();

#执行SQL文件的命令是
登录到服务器
\i /path/to/your/sqlfile.sql

postgres=# select inet_server_addr(),inet_client_addr();
 inet_server_addr | inet_client_addr 
------------------+------------------
 192.168.6.109    | 192.168.6.108
(1 row)

postgres=# \i //home/postgres/sql/table.sql
    db    | schemaname | tablename | tbloat | wastedbytes | totalwastedbytes 
----------+------------+-----------+--------+-------------+------------------
 postgres | pg_catalog | pg_class  |    1.2 |       16384 |            16384
 postgres | pg_catalog | pg_class  |    1.2 |       16384 |            16384
 postgres | pg_catalog | pg_class  |    1.2 |       16384 |            16384
 postgres | pg_catalog | pg_proc   |    1.0 |           0 |                0
 postgres | pg_catalog | pg_proc   |    1.0 |           0 |                0
(5 rows)
postgres=# 

PostgreSQL 数据库膨胀统计信息

列名	描述
db	表示数据库名，即这些统计信息所属的数据库。
schemaname	表示模式（schema）名，即表所属的模式名。
tablename	表示表名，即被统计的表的名称。
tbloat	表示表的膨胀比例。这是一个浮点数，指示表的实际物理大小相比于理想大小的倍数。例如，tbloat 为 1.2 表示表的实际大小是理想大小的1.2倍。
wastedbytes	表示浪费的字节数。这是一个整数，表示由于膨胀而浪费的字节数量。
totalwastedbytes	表示总的浪费字节数。这也是一个整数，与每个表相关联，表示由于膨胀而总共浪费的字节数量。

postgres=# \i //home/postgres/sql/table.sql
    db    | schemaname | tablename | tbloat | wastedbytes | totalwastedbytes 
----------+------------+-----------+--------+-------------+------------------
 postgres | pg_catalog | pg_class  |    1.2 |       16384 |            16384
 postgres | pg_catalog | pg_class  |    1.2 |       16384 |            16384
 postgres | pg_catalog | pg_class  |    1.2 |       16384 |            16384
 postgres | pg_catalog | pg_proc   |    1.0 |           0 |                0
 postgres | pg_catalog | pg_proc   |    1.0 |           0 |                0
(5 rows)
postgres=#

参数说明：

• 数据库：postgres
• 模式：pg_catalog
• 表名：pg_class
• tbloat：1.2，表 pg_class 的实际大小是其理想大小的1.2倍。
• wastedbytes：16384，表 pg_class 由于膨胀而浪费了 16384 字节的空间。
• totalwastedbytes：16384，表 pg_class 总共由于膨胀而浪费了 16384 字节的空间。

这些统计信息对于数据库管理员（DBA）和开发人员来说是优化数据库性能和空间利用的重要参考，可以帮助识别哪些表或索引可能需要进行定期的 VACUUM 或 REINDEX 操作，以减少空间浪费并提升查询性能。

postgres=# vacuum full pg_class;
VACUUM
postgres=# \i //home/postgres/sql/table.sql
    db    | schemaname |   tablename    | tbloat | wastedbytes | totalwastedbytes 
----------+------------+----------------+--------+-------------+------------------
 postgres | pg_catalog | pg_description |    1.0 |        8192 |             8192
 postgres | pg_catalog | pg_proc        |    0.9 |           0 |                0
 postgres | pg_catalog | pg_type        |    0.9 |           0 |                0
 postgres | pg_catalog | pg_type        |    0.9 |           0 |                0
 postgres | pg_catalog | pg_proc        |    0.9 |           0 |                0
(5 rows)

tbloat小于1是因为：
1、PostgreSQL 中的自动化过程（如自动化VACUUM）可能会导致表的物理大小比逻辑大小小。这可能是因为后台进程定期清理并释放了不再需要的空间。
2、如果表没有经历大量的更新和删除操作，它可能没有明显的膨胀，因此实际大小可能接近于逻辑大小，甚至小于逻辑大小。
索引膨胀

postgres=# \i /home/postgres/sql/index.sql
    db    | schemaname |    tablename     | wastedbytes |         iname          | ibloat | totalwastedbytes 
----------+------------+------------------+-------------+------------------------+--------+------------------
 postgres | pg_catalog | pg_ts_config_map |           0 | pg_ts_config_map_index |    2.5 |            24576
 postgres | pg_catalog | pg_language      |           0 | pg_language_name_index |    2.0 |             8192
 postgres | pg_catalog | pg_am            |           0 | pg_am_name_index       |    2.0 |             8192
 postgres | pg_catalog | pg_am            |           0 | pg_am_oid_index        |    2.0 |             8192
 postgres | pg_catalog | pg_language      |           0 | pg_language_oid_index  |    2.0 |             8192
(5 rows)

postgres=#

解决方法：

在线重建索引

=# CREATE INDEX CONCURRENTLY 索引名 ON 表名 USING btree (字段名);
使用vacuum full清理历史版本的数据，整理内存碎片。
=# vacuum FULL 查出的表;  
注意：影响业务，谨慎操作；