Postgres 超时 (Timeout) 详解

原文地址 https://www.bytebase.com/blog/postgres-timeout/

PostgreSQL 提供各种超时 (Timeout) 设置，通过控制某些进程的持续时间来帮助管理和优化数据库操作。这些超时对于确保系统的稳定性和性能至关重要，尤其是在高流量或复杂查询的环境中。让我们一一回顾。

（一）语句超时 (statement_timeout)
statement_timeout 设置了单个查询允许执行的最长时间限制。如果查询超过了这个时间限制，PostgreSQL 将自动终止查询并返回错误信息。

ERROR:  canceling statement due to statement timeout
错误：由于语句超时而取消语句

如果单个 simple-Query 消息中出现多个 SQL 语句，则超时将分别应用于每个语句。statement_timeout 可有效防止长时间运行的查询占用过多资源或导致数据库出现性能问题。

（二）锁超时 (lock_timeout)
lock_timeout 控制一个事务为获得数据库对象（如表或行）上的锁而等待的时间，然后才会放弃并返回错误。

ERROR:  canceling statement due to lock timeout
错误：由于锁超时而取消语句

在 Postgres 中，等待获取资源锁的事务会阻塞需要在同一资源上获取冲突锁的传入事务。对于获取重量级锁（如运行 DDL 语句）的事务，建议设置 lock_timeout。常见的做法是创建一个单独的 Postgres 用户来运行 DDL，并为该用户设置一个较短的 lock_timeout。

ALTER ROLE ddl_user SET lock_timeout = 10000; -- 10 秒

（三）事务会话空闲超时 (idle_in_transaction_session_timeout)
idle_in_transaction_session_timeout 控制会话在事务中空闲的最长时间。如果会话在事务中的空闲时间超过指定的超时时间，PostgreSQL 将自动终止会话并回滚正在进行的事务。

ERROR:  terminating connection due to idle-in-transaction timeout
错误：由于事务中的空闲超时而终止连接

假设您有一个应用程序，在等待用户输入或执行一些与数据库无关的处理时，偶尔会让事务处于进行状态。如果一个事务处于进行和空闲状态的时间过长，它可能会持有表或行上的锁，从而阻止其他事务访问这些资源。通过设置 idle_in_transaction_session_timeout 可以自动终止这些空闲会话，确保资源不会被不必要地占用。即使没有重要的锁，一个进行中的事务会阻止清理（ vacumm）已经被删除，但在当前事务仍然可见的元组；因此，长时间处于空闲状态会导致表膨胀。

（四）空闲会话超时 (idle_session_timeout)
idle_session_timeout 控制会话在被自动终止前的最长空闲时间。与 idle_in_transaction_session_timeout 不同的是，idle_session_timeout 只适用于在事务中处于空闲状态的会话，而 idle_session_timeout 则适用于任何处于空闲状态的会话，无论它是否在事务中。

ERROR:  terminating connection due to idle session timeout
错误：由于空闲会话超时而终止连接

在使用连接池或其他中间件时要小心，因为这样的层可能不会对意外的连接关闭做出很好的反应。好的做法是为交互式处理创建一个单独的 Postgres 用户，并相应地设置 idle_session_timeout。

ALTER ROLE interactive_user SET idle_session_timeout = 600000; -- 10 分钟

（五）事务超时 (transaction_timeout)

即将发布的 Postgres 17 版本将引入新的 transaction_timeout。从文档中可以看到
在一个事务中终止任何超过指定时间的会话。该限制既适用于显式事务（以 BEGIN 开始），也适用于与单条语句相对应的隐式事务。
一个典型的网络服务由 3 个主要部分组成：

• 网络服务器
• 应用程序服务器
• 数据库服务器

为防止长时间连接，通常会在网络服务器和应用程序服务器上设置连接超时。当网络服务器/应用服务器已经终止连接时，再处理事务就太浪费了。在引入事务超时（transaction_timeout）之前，没有防止长时间事务的可靠方法。即使同时设置了 statement_timeout 和 idle_in_transaction_session_timeout，如果事务是由短语句和中间的短暂停顿组成的，那么该事务仍然是开放的。
你可能想知道，为什么 PostgreSQL 花了这么长时间才推出一个直接的事务超时功能 – 迟到总比不到好 😁！顺便说一句，MySQL 也没有这个功能 😜。

参考资料：
官方文档 (https://www.postgresql.org/docs/current/runtime-config-client.html)
pgsql-hackers 关于引入 transaction_timeout 的讨论 (https://www.postgresql.org/message-id/flat/f508267d1ba8f0bfd7b93181d10511dc%40oss.nttdata.com#2506da45ff92aaea65c30996fbf19c85)

---

💡 更多资讯，请关注 Bytebase 公号：Bytebase