[译]空闲事务导致表膨胀？等等,什么?

原文地址：https://stormatics.tech/blogs/idle-transactions-cause-table-bloat-wait-what

原文作者：Umair Shahid

发布时间：2025/1/9

是的，你没有看错。空闲事务可能会导致大量的表膨胀，而 vacuum进程可能无法解决这个问题。表膨胀会导致性能下降，并且死元组会不断占用磁盘空间。

本文将深入探讨空闲事务是如何导致表膨胀的，为什么这是一个问题，以及避免它的实用策略。

什么是表膨胀？

PostgreSQL中的表膨胀发生在未使用或过时的数据（称为死元组）在表和索引中积累时。PostgreSQL使用多版本并发控制（Multi-Version Concurrency Control， MVCC）机制来维护数据一致性。每次更新或删除都会创建行的新版本，将旧版本留在后面，直到通过autovacuum进程或手动vacuuming。

当这些死元组堆积起来而不被删除时，膨胀就会成为问题，从而增加了表和索引的大小。表越大，查询越慢，从而导致数据库性能下降和更高的存储成本。

空闲事务如何导致表膨胀

在PostgreSQL中，空闲事务是指连接到数据库但不主动发出查询的会话。空闲事务有两种主要状态：

• Idle ：打开连接，但没有正在运行的事务。
• Idle in Transaction：事务已经打开（例如，通过BEGIN），但是既没有提交也没有回滚。

1、Autovacuum阻塞

Autovacuum是PostgreSQL进程，负责清理死元组和回收空间，然而autovacuum不能删除打开的事务中仍然可见的死元组。当事务处于“idle in transaction”状态时，它保留数据库的快照，从而防止删除可能仍需要访问的行。例如：

1. 表被更新，生成死元组。
2. 触发Autovacuum，但不能删除这些元组，因为空闲事务持有引用它们的快照。
3. 死元组仍然存在，导致表膨胀。

2、长时间运行的空闲事务

长时间的空闲事务会加剧这个问题，这些事务持有锁或快照，阻塞了autovacuum 甚至手动VACUUM 操作等清理过程。这造成了一个级联效应：

1. 死元组不断累积。
2. 查询性能下降，因为PostgreSQL必须扫描膨胀的表。
3. 存储使用不必要地增加。

3、锁争用

空闲事务还可以持有表上的锁，从而阻止其他事务有效地完成插入、更新或删除等操作。如果事务被强制重试或延迟，这个锁争用可能会导致更多的死元组。

4、索引膨胀

死元组不仅影响表，还影响索引。当行被更新或删除时，相应的索引项被标记为无效，但不会立即删除。空闲事务可能会延迟这种清理，导致索引膨胀，从而降低查询性能

为什么空闲事务有问题

除了表膨胀之外，空闲事务还会导致一系列问题：

1、资源浪费

膨胀的表会消耗更多的磁盘空间和内存，从而增加成本。

较大的表需要更多的I/O，从而降低查询速度。

2、性能降低

查询执行时间随着数据库扫描膨胀的表和索引而增加。

像autovacuum和analyze这样的维护任务需要更长的时间才能完成。

3、增加事务ID回卷的风险

在PostgreSQL中，由于事务ID被存储为32-bit integers，所以会发生XID (transaction ID) 回卷，这意味着它们最终会在大约20亿个事务之后“封装”。如果不能通过常规的VACUUM操作来解决这个问题，就可能导致数据损坏，使旧的行看起来不可见，或者使新的事务无法继续进行。

4、严重的操作引发的阻塞

手动vacuuming或维护任务可能由于空闲事务持有锁或快照而失败。

如何避免空闲事务和防止表膨胀

幸运的是，可以通过主动监控、配置和应用程序设计来避免空闲事务和产生的膨胀。以下是一些策略：

1、监控空闲事务

第一步是识别空闲或事务中空闲会话。使用下面的查询来查找空闲的事务：

SELECT 
    pid, 
    usename AS username, 
    state, 
    state_change, 
    query
FROM 
    pg_stat_activity
WHERE 
    state IN ('idle', 'idle in transaction');
复制

state：显示事务是空闲还是处于空闲状态。

state_change：指示事务何时进入当前状态。

使用此信息识别长时间运行的空闲会话并采取纠正措施。

2、设置空闲事务超时

PostgreSQL提供了idle_in_transaction_session_timeout参数来自动终止长时间空闲的事务。这可以防止长时间运行的空闲事务持有锁和快照。

在postgresql.conf中全局设置此参数：

idle_in_transaction_session_timeout = '5min'
复制

或者将其应用于特定的角色或数据库：

ALTER ROLE my_user SET idle_in_transaction_session_timeout = '5min';
ALTER DATABASE my_database SET idle_in_transaction_session_timeout = '5min';
复制

当达到这个超时时间时，PostgreSQL会自动终止空闲事务并报错。

3、实现连接池

使用连接池（如PgBouncer或Pgpool-II）来管理和限制到数据库的连接数。确保连接池：

• 连接被有效地重用。
• 空闲会话非必要不要保持打开状态。
• 应用程序仅在需要时打开事务。

4、优化应用程序的逻辑

大多数空闲事务是由糟糕的应用程序设计引起的。确保你的请求：

• 总是在完成事务后立即发出COMMIT或ROLLBACK。
• 避免不必要地启动事务（例如，避免在没有立即查询的情况下启动BEGIN）。
• 不使用时关闭数据库连接。

5、优化Autovacuum

当空闲事务阻塞autovacuum时，调优autovacuum 参数可确保更积极地触发清理。可以考虑以下调整：

• autovacuum_vacuum_threshold：降低该值可以更快触发vacuuming 。
• autovacuum_vacuum_scale_factor：将其减小以基于更小百分比的更新行触发vacuuming 。这与上述参数一起使用，以确保表按照其大小的比例进行处理。

例如:

ALTER TABLE my_table SET (
    autovacuum_vacuum_threshold = 50,
    autovacuum_vacuum_scale_factor = 0.1
);
复制

6、使用监控工具

利用PostgreSQL监控视图（如pg_stat_activity、pg_stat_user_tables）或第三方工具（如pgAdmin或pgBadger）跟踪空闲事务和表膨胀。

7、终止有问题的会话

如果有必要，您可以手动终止阻塞关键操作的空闲事务。使用以下查询终止空闲事务：

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND state_change < NOW() - INTERVAL '10 minutes';
复制

结论

空闲事务一开始可能看起来无害，但它们可能会导致表膨胀、阻塞维护任务和不必要地消耗资源，从而悄无声息导致严重的性能问题。通过监视、超时和应用程序优化主动管理空闲事务对于维护高性能的PostgreSQL数据库至关重要。

通过采用这些最佳实践，您可以防止空闲事务对数据库造成严重破坏，并确保系统干净、高效和可扩展。不要让空闲事务成为PostgreSQL性能的无声杀手，现在就行动起来，保持数据库的高可用和高性能。
– / END / –

可以通过下面的方式联系我

• 微信公众号：@墨竹札记
• 墨天轮：@墨竹
• 微信：wshf395062788
• PGFans：@墨竹

如果这篇文章为你带来了灵感或启发，就请帮忙点赞、收藏、转发；如果文章中不严谨或者错漏之处，请及时评论指正。非常感谢！