数据库年龄，精准诊断新模式

PostgreSQL（后简称PG）中，每张表都是有年龄的。那么就像人一样，上了年纪是容易出事的。

数据库年龄的含义

PG数据库年龄其实指的是数据库的事务ID（后面简称xid），PG的MVCC事务依赖于比较xid值的大小来实现：如果一个行版本的插入xid大于当前事务的xid，它就是"属于未来的"，那么就不应该对当前事务可见。

如上图，xid类型是uint32，可以理解为一个没有端点的环。一个长时间（超过40亿个事务）运行的集簇会遭受到事务ID回卷问题，即xid计数器回卷到0（注）。

一旦一个行版本创建时被分配了一个特定的普通 XID，该行版本将成为接下来20亿个事务的"过去"（与我们谈论的具体哪个普通 XID 无关）。如果在20亿个事务之后该行版本仍然存在，它将突然变得好像在未来。

为了避免发生这种情况，PostgreSQL会在数据库年龄离20亿还差100w的时候就会自动变成只读，只允许超级用户以单用户模式登录进来执行清理操作。所以，有必要提前清理每个数据库中的表。

这个清理动作由autovacuum进程来周期性地实施。VACUUM会把行标记为冻结，这表示它们是被一个在足够远的过去提交的事务所插入，从MVCC的角度来看，效果就是该插入事务对所有当前和未来事务来说当然都是可见的。

注：事实上，0,1和2是PG保留的3个xid，普通事务ID是从3开始的，回卷也是回到3，为了便于理解，PG手册中没有过多说明。

初步分析

告警的意思是当前业务库的年龄已经超过了5亿。这个不合理，PG的autovacuum_freeze_max_age参数，控制需要被冻结的行的最大年龄，默认值是2亿，即对于年龄超过2亿的行，PG的autovacuum进程（自动回收清理垃圾的进程）会自动扫描全表，把老的行给冻结掉。所以生产库运行的半年来，年龄一直维持在2亿附近，那么为什么突然autovacuum就没有效果了呢？继续分析。

定位高龄表

登录生产环境的业务库，从系统表pg_class中查看当前年龄最大的表有哪些。从下面的结果来看，年龄超过5亿的表有3张，而且名字一样，从命名方式来看，应该是临时表。进一步查看表定义，通过schema确认，这3张表都是临时表。

确认异常发生时点

找到问题点之后，回到监控，查看数据年龄是从什么时间开始出现异常的。查看zabbix中数据库年龄的监控，发现数据库的年龄是从12月21号凌晨3点左右开始逐渐升高，到告警发生已经持续升高了4天。照这个速度，如果不解决问题，20天左右就会耗尽xid，导致PG强制关闭。

临时表问题

那么问题就集中在这个临时表上了，临时表比较特殊，PG的autovacuum进程并不会回收临时表的垃圾，也不会收集临时表的统计信息。

正常情况下，临时表用完就会随着连接的断开而被销毁掉。而这3张表应该是3个不同的连接创建的，且已经保留了4天，那么有可能是应用使用的连接池，或者连接泄漏了。在数据库确认了下，确实有一些比较古老的连接存在。

这些临时表应该是其中某几个连接创建的，与项目组确认之后，他们没有使用连接池，并且这些临时表已经没人使用了，于是决定drop掉这些临时表。从监控看到，临时表drop掉之后，数据库年龄就恢复正常了。

根本原因

我们分析，有下面2个可能的原因：

应用SQL中使用完临时表没有及时清理

应用程序存在连接泄露的bug

解决方案

创建临时表时加上on commit drop，即临时表在事务提交后就会被drop掉（注）

应用程序需要检查有无连接泄漏的问题

注：PG临时表的生命周期默认情况下，是会话级的，即连接断开时才会清理表定义。

临时表在一个事务块结束时的行为有三种选项：

PRESERVE ROWS在事务结束时不采取特殊的动作。这是默认行为。

DELETE ROWS在每一个事务块结束时将删除临时表中的所有行。实质上，在每一次提交时会完成一次自动的TRUNCATE。

PRESERVE ROWS在事务结束时不采取特殊的动作。这是默认行为。