PostgreSQL provides a variety of timeout settings to help control the duration of database operations and optimize system performance and stability, especially in high-concurrency or complex query environments. The following are several common timeout settings and their uses.
Statement timeout
statement_timeout sets the maximum execution time for a single query. If the query exceeds this time limit, PostgreSQL will automatically cancel the query and return an error:
ERROR: canceling statement due to statement timeout
If a query contains multiple SQL statements, the timeout will be applied to each statement separately. This setting helps prevent long-running queries from consuming too many resources and ensures that the database runs efficiently.
Transaction timeout
PostgreSQL 17 introduces a new transaction_timeout setting that limits the maximum duration of transactions, applicable to both explicit transactions (started with BEGIN) and implicit transactions (transactions consisting of a single statement). This feature automatically terminates transactions that exceed the set time, no matter how short the statements contained in the transaction are.
A typical web service usually consists of three core components:
- Web server
- Application server
- Database server
To avoid long-lasting connections, connection timeouts are usually set on web servers and application servers. But when the web or application server has terminated the connection while the database is still processing the transaction, it is actually a waste of resources. Before PostgreSQL introduced transaction_timeout, there was no effective mechanism to prevent long-running transactions. Even if statement_timeout and idle_in_transaction_session_timeout are set at the same time, if the transaction contains short statements and intermittent waits, the transaction may still remain active for a long time.
You may wonder why PostgreSQL introduced such a basic feature as transaction_timeout so late. Fortunately, it is finally here! By the way, MySQL does not have a similar feature yet.
Lock timeout
lock_timeout controls the maximum time a transaction waits for a lock on a database object (such as a table or row). After the timeout, PostgreSQL cancels the transaction and returns an error:
ERROR: canceling statement due to lock timeout
In Postgres, transactions waiting to acquire locks may block other transactions that need the same resource. For operations that need to acquire heavyweight locks (such as DDL statements), you can set a shorter lock_timeout to ensure that resources are released in time. For example:
-- Set the lock timeout for the user to 5 seconds
ALTER ROLE admin_user SET lock_timeout = 5000;
Idle session timeout
idle_session_timeout applies to any idle session and controls the maximum amount of time a session can be idle before being terminated. If a session does not perform any operations for a specified period of time, PostgreSQL will terminate the connection:
ERROR: terminating connection due to idle session timeout
When using connection pools or other middleware, it is important to be aware that this setting may cause unexpected termination of the connection. This setting is often used for interactive sessions, and it is recommended to set a reasonable timeout value for such users, for example:
-- Set idle session timeout to 10 minutes
ALTER ROLE admin_user SET idle_session_timeout = 600000;
-- Set idle session timeout to 10 minutes
ALTER ROLE admin_user SET idle_session_timeout = '10min';
Idle session timeout in transaction
idle_in_transaction_session_timeout controls the maximum time a transaction can remain idle. After the timeout, PostgreSQL automatically terminates the session and rolls back any unfinished transactions:
ERROR: terminating connection due to idle-in-transaction timeout
Imagine you have an application that occasionally opens a transaction while waiting for user input or performing some non-database related processing. If a transaction is open and idle for too long, it might hold locks on tables or rows, preventing other transactions from accessing those resources.
This setting is for applications that might keep transactions open while waiting for user input or processing non-database tasks. Keeping an idle transaction open for a long time might block other transactions or cause table bloat, so by setting this timeout you can prevent resources from being held up unnecessarily.
Reference
Community
Go to Chat2DB website
🙋 Join the Chat2DB Community
🐦 Follow us on X
📝 Find us on Discord
Top comments (0)