PostgreSQL offers various timeout settings to help manage and optimize database operations by controlling the duration of certain processes. These timeouts are crucial for ensuring the stability and performance of your system, particularly in environments with high traffic or complex queries. Let's review each of them.
statement_timeout
statement_timeout sets a limit on the maximum amount of time that a single query is allowed to execute. If the query exceeds this time limit, PostgreSQL will automatically terminate the query and return an error.
ERROR: canceling statement due to statement timeoutIf multiple SQL statements appear in a single simple-Query message, the timeout is applied to each statement separately. statement_timeout effectively preventing long-running queries from consuming too many resources or causing performance issues in your database.
lock_timeout
lock_timeout controls how long a transaction will wait to acquire a lock on a database object, such as a table or a row, before giving up and returning an error.
ERROR: canceling statement due to lock timeoutIn Postgres, a transaction waiting to acquire a lock on a resource will block incoming transactions that need a conflicting lock on the same resource. For transactions acquring heavyweight locks such as running DDL statements, it's recommnended to set lock_timeout. A common practice is to create a separate Postgres user to run the DDL and set a short lock_timeout on that user.
ALTER ROLE ddl_user SET lock_timeout = 10000; -- 10 secondsidle_in_transaction_session_timeout
idle_in_transaction_session_timeout controls the maximum amount of time that a session can remain idle while inside a transaction. If a session stays idle within a transaction for longer than the specified timeout period, PostgreSQL will automatically terminate the session and roll back the ongoing transaction.
ERROR: terminating connection due to idle-in-transaction timeoutImagine you have an application that occasionally leaves transactions open while waiting for user input or performing some non-database-related processing. If a transaction is left open and idle for too long, it might hold locks on tables or rows, preventing other transactions from accessing those resources. By setting idle_in_transaction_session_timeout, you can automatically terminate these idle sessions, ensuring that resources are not held up unnecessarily. Even when no significant locks are held, an open transaction prevents vacuuming away recently-dead tuples that may be visible only to this transaction; so remaining idle for a long time can contribute to table bloat.
idle_session_timeout
idle_session_timeout controls the maximum amount of time a session can remain idle before it is automatically terminated. Unlike idle_in_transaction_session_timeout, which applies only to sessions that are idle while inside a transaction, idle_session_timeout applies to any session that is idle, whether or not it is within a transaction.
ERROR: terminating connection due to idle session timeoutBe careful when you are using connection pooler or other middleware, as such a layer may not react well to unexpected connection closure. idle_session_timeout is usually set for the interactive sessions. It's a good practice
to create a separate Postgres user for interactive processing and set idle_session_timeout accordingly.
ALTER ROLE interactive_user SET idle_session_timeout = 600000; -- 10 minutestransaction_timeout
The upcoming Postgres 17 release will introduce a new transaction_timeout. From the doc:
Terminate any session that spans longer than the specified amount of time in a transaction. The limit applies both to explicit transactions (started with BEGIN) and to an implicitly started transaction corresponding to a single statement.
A typical web service consists of 3 main components:
- Web server
- Application server
- Database server
To prevent long-running connections, you usually set a connection timeout on both the web server and the application server. It's a waste to process the transaction when web server/application server already terminate the connection. Before the introduction of transaction_timeout, there is no reliable way to prevent long-transactions. Even you set both statement_timeout and idle_in_transaction_session_timeout, the transaction will still
be open if it consists of short statements and short pauses in between.
You might be wondering why it has taken PostgreSQL so long to introduce a straightforward transaction_timeout feature. Well, better late than never! And by the way, MySQL doesn’t have this feature either.
the
