是的，你没看错。空闲事务可能导致大量表膨胀，而清理过程可能无法解决这一问题。膨胀会导致性能下降，并可能通过死元组不断侵占磁盘空间。
本博客深入探讨了空闲事务如何导致表膨胀、为什么这会带来问题以及避免这种情况的实用策略。

什么是表膨胀？

当未使用或过时的数据（称为死元组）在表和索引中累积时，PostgreSQL 中的表就会膨胀。PostgreSQL 使用多版本并发控制 (MVCC)机制来维护数据一致性。每次更新或删除都会创建一行的新版本，而旧版本则会保留，直到通过自动清理过程或手动清理将其清理干净。
当这些死元组堆积起来且未被移除时，膨胀就会成为问题，从而增加表和索引的大小。表越大，查询速度越慢，导致数据库性能下降和存储成本增加。

空闲事务如何导致表膨胀

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

1. 空闲：连接已打开，但是没有事务正在运行。
2. 事务中空闲：事务已打开（例如，通过BEGIN ），但尚未提交或回滚。

1. 自动真空泵堵塞

Autovacuum 是 PostgreSQL 进程，负责清理死元组并回收空间。但是，autovacuum 无法移除对打开的事务仍然可见的死元组。当事务处于“事务中空闲”状态时，它会保留数据库的快照，防止删除可能仍需要访问的行。

例如：

• 表被更新，产生了死元组。
• 自动清理被触发但无法删除这些元组，因为空闲事务保存着引用它们的快照。
• 死元组仍然存在，导致表膨胀。

2. 长期运行的空闲事务

长时间存在的空闲事务会加剧问题。这些事务会持有锁或快照，从而阻止自动清理或手动VACUUM操作等清理过程。这会产生连锁反应：

• 死元组不断累积。
• 由于 PostgreSQL 必须扫描膨胀的表，因此查询性能下降。
• 存储使用量不必要地增加。

3. 锁争用

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

4. 索引膨胀

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

为何空闲交易会带来问题

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

1.浪费的资源：

• 臃肿的表会占用更多的磁盘空间和内存，从而增加成本。
• 较大的表需要更多的 I/O，从而减慢查询速度。

2.性能下降：

• 随着数据库扫描膨胀的表和索引，查询执行时间会增加。
• 自动清理和分析等维护任务需要更长时间才能完成。

3.交易 ID 重叠风险增加：

• 在 PostgreSQL 中，XID（事务 ID）回绕是因为事务 ID 存储为 32 位整数，这意味着它们最终会在大约 20亿个事务后“回绕”。如果不通过常规VACUUM操作解决此问题，则可能导致数据损坏，因为较旧的行会不可见或新事务无法继续。

4.关键操作的阻塞：

• 由于空闲事务持有的锁或快照，手动清理或维护任务可能会失败。

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

幸运的是，通过主动监控、配置和应用程序设计，可以避免闲置交易和由此导致的膨胀。以下是一些策略：

1. 监控空闲交易

第一步是识别空闲或处于事务空闲状态的会话。使用以下查询查找延迟的事务：

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

• 状态：显示交易是否处于空闲状态或交易中空闲状态。
• 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_vacuum_threshold ：降低此值以更快地触发清理。
• autovacuum_vacuum_scale_factor：减少此参数以根据更新行的较小百分比触发清理。此参数与上述参数结合使用，以确保按表大小比例处理表。

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 性能的隐形杀手 - 立即采取行动，保持数据库健康且高效。
#PG证书#PG考试#PostgreSQL培训#PostgreSQL考试#PostgreSQL认证