1 deadlock_timeout

Sets the time to wait on a lock before checking for deadlock

This is the amount of time to wait on a lock before performing deadlock detection. Deadlock detection is relatively expensive, so the server will not run this it every time it waits on a lock. We optimistically assume that deadlocks are infrequent in production applications and only wait a short while before starting deadlock detection. Increasing this value reduces the time wasted on useless deadlock detection, but slows down the reporting of true deadlock errors. If the value is specified without units, it is in milliseconds. The default is 1 second (1s), which is probably the smallest value you want in practice. On a highly loaded server, you may need to increase it. The ideal setting for this value should be more than your usual transaction time, which reduces the chance of starting a deadlock check before the lock is released. Only super users can change this setting.

2 lock_timeout （+ v9.3)

ets the maximum allowed duration of any wait for a lock:Maximum time to wait for a lock

If any statement waits longer than the specified amount of time while attempting to acquire a lock on a table, index, row, or other database object, the statement is aborted. This time limit is applied independently to each lock acquisition attempt. The limit is applied to both explicit lock requests (such as LOCK TABLE or SELECT FOR UPDATE without NOWAIT) and implicitly acquired locks. If the value is specified without units, it is in milliseconds. A value of zero (the default) will disable the timeout.

Unlike statement_timeout, this timeout only occurs while waiting for a lock. Note that if statement_timeout is non-zero, it makes no sense to set lock_timeout to the same or greater value, as the transaction timeout will always be the first to be triggered. If log_min_error_statement is set to ERROR or lower, timeout statements will be logged.

3 statement_timeout

Sets the maximum allowed duration of any statement:  The maximum allowed time for any SQL statement

Aborts any statement that uses more than the specified amount of time. If log_min_error_statement is set to ERROR or lower, statements are also logged if they time out. If no units are available when the value is specified, it is in milliseconds. A value of zero (the default) will disable the timeout.

The timeout is calculated from the time the command arrives at the server until it is completed by the server. In the extended query protocol, the timeout starts running when any message related to the query (parse, bind, execute, describe) arrives, and can be canceled by an execution completion or synchronization message.

We do not recommend setting statement_timeout in postgresql.conf as it affects all sessions.

4 authentication_timeout

Sets the maximum allowed time to complete client authentication

5 tcp_user_timeout （+v12)

Specifies the amount of time that transmitted data can remain in an unacknowledged state before the TCP connection is forced to close. If no units are available when the value is specified, it is in milliseconds. The value 0 (default) indicates that the operating system default is selected. This parameter is supported only on systems that support TCP_USER_TIMEOUT; on other systems, it must be zero. This parameter is ignored and always reads zero in sessions connected via Unix-domain sockets.

It is not supported on Windows and must be zero.

6 idle_session_timeout (+v14)

Sets the maximum allowed idle time between queries, when not in a transaction

Terminates any idle session (i.e., waiting for a client query) for longer than the specified time, but not in an open transaction. If the value is not specified in units, it is in milliseconds. Zero (default) Timeout is disabled.

Unlike in the case of open transactions, idle sessions without transactions do not impose significant costs on the server, so there is less need to enable this timeout than idle_in_transaction_session_timeout.

Be careful when enforcing this timeout on connections established through connection pooling software or other middleware, as such a layer may not respond well to unexpected connection closures. It may be helpful to enable this timeout only for interactive sessions, perhaps applying it only to specific users.

postgres=# alter system set idle_session_timeout=20000;
ALTER SYSTEM
postgres=# show idle_session_timeout ;
 idle_session_timeout
----------------------
 0
(1 row)

postgres=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=# show idle_session_timeout ;
 idle_session_timeout
----------------------
 20s
(1 row)

And then in another session:

postgres=# select 1 test;
 test
------
    1
(1 row)
-- 中断22秒再执行：
postgres=# \watch 22
Fri 11 Oct 2024 08:57:39 AM CST (every 22s)

 test
------
    1
(1 row)

FATAL:  terminating connection due to idle-session timeout
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

We'll notice that at the 20 second mark, the connection will be terminated.

This is a feature that comes with PG14.

The setting above is global. It can also be set to the current session level, see:

postgres=# set idle_session_timeout=10000;
SET
postgres=# select 1 test2;
 test2
-------
     1
(1 row)

postgres=# \watch 11
Fri 11 Oct 2024 09:01:50 AM CST (every 11s)

 test2
-------
     1
(1 row)

FATAL:  terminating connection due to idle-session timeout
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

Kind of interesting haha. As a side question, if it's not a command line client like psql, if it's a jdbc application, how do you set it up, and I'll leave it to the reader to think about it.

Of course, we can also use the script below to explicitly kill idle connections that you think are “timing out”:

SELECT
    pg_terminate_backend(pid)
FROM
    pg_stat_activity
WHERE
    state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') 
AND now() - query_start > '00:00:10';

Or a little more complicated:

WITH inactive_connections AS (
    SELECT
        pid,
        rank() over (partition by client_addr order by backend_start ASC) as rank
    FROM 
        pg_stat_activity
    WHERE
        pid <> pg_backend_pid( )
    AND
        application_name !~ '(?:psql)|(?:pgAdmin.+)'
    AND
        datname = current_database() 
    AND
        usename = current_user 
    AND
        state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') 
    AND
        current_timestamp - state_change > interval '60 minutes' 
)
SELECT
    pg_terminate_backend(pid)
FROM
    inactive_connections 
WHERE
    rank > 1;

7 idle_in_transaction_session_timeout （+v9.6)

Sets the maximum allowed duration of any idling transaction

Terminates any session with an open transaction that has been idle for more than the amount of time specified by this parameter. This allows any locks held by the session to be released and the connection slots it holds to be reused, and it also allows tuples visible only to this transaction to be cleaned up. See route-vacuuming for more information on this.

If the value is specified without units, it is in milliseconds. value 0 (default) disables the timeout.

8 transaction_timeout （+v17)

Sets the maximum allowed duration of any transaction within a session (not a prepared transaction)

Terminates any session in a transaction that exceeds the specified time. This limit applies both to explicit transactions (starting with BEGIN) and to implicitly started transactions corresponding to a single statement. If the value is not specified in units, it is in milliseconds. A value of zero (the default) disables the timeout.

Longer timeouts are ignored if ' transaction_timeout ' is less than or equal to idle_in_transaction_session_timeout or statement_timeout.

Setting ' transaction_timeout ' in postgresql.conf is not recommended as it affects all sessions.

Prepared transactions are not subject to this timeout.

This means that we can set this parameter for a specific connection session. This should be useful on the application development side. This is because the transaction_timeout may not be the same for each application. Compared to idle_session_timeout , this parameter is more powerful.