PG数据库插入唯一键约束导致表膨胀的问题

前几天在学习红石PG社区的一篇技术文章，里面讲了PG数据库插入唯一键约束导致表膨胀的问题。内容如下：

因为唯一性约束而插入失败，给表和索引带来的膨胀问题，在互联网上已经非常出名了。然而，这些讨论有时缺乏一个清晰、实际的例子，通过测量来说明影响。尽管这个问题很熟悉，但我们仍然经常会在实际的应用程序中看到这样的设计模式，或者更确切地说，这种反模式。开发者通常依赖唯一性约束，来防止将重复值插入表中。虽然这种方法简单明了、用途广泛且通常被认为是有效的，但在 PostgreSQL 中，不幸的是，由于唯一约束冲突而失败的插入，总是会给表和索引带来膨胀。在高流量系统上，这种不必要的膨胀会显著增加磁盘 I/O 和 autovacuum 运行的频率。在本文中，我们旨在再次强调这个问题，并提供一个带有测量的简单示例来说明它。我们提供了简单的改进建议，以帮助缓解此问题，并减少 autovacuum 工作负载和磁盘 I/O。

防止重复的两种方法

在 PostgreSQL 中，有两种主要的方法使用唯一性约束，来防止出现重复值：

1. 标准插入命令 (INSERT INTO table)

通常的 INSERT INTO table 命令，尝试将数据直接插入到表中。如果插入会出现重复值，则会失败并显示 “duplicate key value violates unique constraint” 错误。由于该命令没有指定任何重复检查，因此 PostgreSQL 会在内部立即插入新行，然后才开始更新索引。当它遇到唯一索引冲突时，它会引发错误并删除新添加的行。索引更新的顺序由其关系 ID 决定，因此索引膨胀的程度取决于索引的创建顺序。如果不断地发生 “unique constraint violation” 错误，表和一些索引都会累积已删除的记录，从而产生膨胀，并且由此产生的写入操作会增加磁盘 I/O，而不会获得任何有用的结果。

2. 冲突感知插入 (INSERT INTO table … ON CONFLICT DO NOTHING)

INSERT INTO table ON CONFLICT DO NOTHING 命令的行为不太一样。由于它指定了可能会发生冲突，因此 PostgreSQL 在尝试插入数据之前，会首先检查可能的重复项。如果找到重复项，PostgreSQL 将执行指定的操作（在本例中为 “DO NOTHING”），并且不会发生错误。此子句是在 PostgreSQL 9.5 中引入的，但是某些应用程序要么还在较旧的 PostgreSQL 版本上运行，要么在升级数据库时保留了旧代码。因此，这种冲突处理选项，通常并未得到充分利用。

看了社区的文章的介绍，我咨询红石PG的技术人员，现在的PG版本是否还存在插入唯一键约束导致表膨胀的问题。技术人员建议我亲自测试一下自己的PG数据库版本。

下面介绍一下我的操作环境，我的数据库版本为17.6，操作系统为CentOS Stream release 9，在PG数据库要安装扩展pageinspect。如下所示

[postgres@mytest ~]$ psql

psql (17.6)

输入 "help" 来获取帮助信息.

postgres=# \c scott;

您现在已经连接到数据库 "scott",用户 "postgres".

scott=# \dx;

                                     已安装扩展列表

   名称     | 版本  |  架构模式  |                         描述

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

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

pageinspect | 1.12  | public     | inspect the contents of database pages at

a low level

pg_partman  | 5.2.4 | public     | Extension to manage partitioned tables by

time or ID

pgstattuple | 1.5   | public     | show tuple-level statistics

plpgsql     | 1.0   | pg_catalog | PL/pgSQL procedural language

(4 行记录)

test=# CREATE TABLE mytable (id INT NOT NULL, value TEXT NOT NULL, CONSTRAINT ux_mytable_id UNIQUE (id) DEFERRABLE INITIALLY DEFERRED);

CREATE TABLE

test=# INSERT INTO mytable VALUES (1, 'test');

INSERT 0 1

test=# INSERT INTO mytable VALUES (1, 'test2');

ERROR:  duplicate key value violates unique constraint "ux_mytable_id"

DETAIL:  Key (id)=(1) already exists.

test=# SELECT * FROM heap_page_items(get_raw_page('mytable', 0));

lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |         t_data

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

 1 |   8152 |        1 |     33 |    759 |      0 |        0 | (0,1)  |           2 |       2306 |     24 |        |       | \x010000000b74657374

 2 |   8112 |        1 |     34 |    760 |      0 |        0 | (0,2)  |           2 |       2050 |     24 |        |       | \x010000000d7465737432

(2 rows)

从上面的实例输出的信息可以看出虽然只成功插入一条数据，但页面条目中显示有两条。

test=# SELECT * FROM bt_page_items('ux_mytable_id', 1);

itemoffset | ctid  | itemlen | nulls | vars |          data           | dead | htid  | tids

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

         1 | (0,1) |      16 | f     | f    | 01 00 00 00 00 00 00 00 | f    | (0,1) |

         2 | (0,2) |      16 | f     | f    | 01 00 00 00 00 00 00 00 | f    | (0,2) |

(2 rows)

ux_mytable_id为约束名

从上面的案例中可以看出在PG17.6中还是会有插入唯一键约束导致表膨胀的问题。这种问题产生的原因是什么？实际上导致PG数据库的原因比较多，比如长时间的空白事务、索引膨胀等。但这些膨胀的最终的解决方案，可以通过VACUUM和重建索引等解决。PG数据库允许表膨胀和索引膨胀、事务ID膨胀，这种膨胀是由它的数据架构和设计逻辑决定的，比如MVCC、通过指针更新数据、全页写等。好在PG数据库开启了autovacuum，但作为DBA还是关注一下的，毕竟膨胀是PG数据库与生俱来的天性。明天开始就是国庆长假了，为了避免交通拥挤，我提前两天回到了河南老家，著下引文，算是2025年9月最后一文了，祝大家国庆中秋双节快乐。