Python psycopg2: How to Set Timeouts in psycopg2 Using Python

Learn how to configure connection, statement, and socket-level timeouts in psycopg2 to prevent hanging queries and ensure responsive PostgreSQL database operations in Python.

When your Python application communicates with a PostgreSQL database through psycopg2, operations can sometimes hang indefinitely due to slow queries, network issues, or database locks. Setting timeouts ensures your application fails gracefully instead of waiting forever, preserving responsiveness and system resources.

This guide covers the three main types of timeouts you can configure in psycopg2, connection timeout, statement timeout, and socket-level read/write timeout, with practical examples and best practices.

What Are Timeouts in psycopg2?

A timeout in psycopg2 defines the maximum duration allowed for a specific database operation to complete. If the operation exceeds this limit, an exception is raised, giving your application the opportunity to handle the failure, retry, or alert the user. There are three key timeout categories:

Timeout Type	What It Controls	Where It's Set
Connection timeout	How long to wait when establishing a connection	`connect_timeout` parameter in `psycopg2.connect()`
Statement timeout	How long a single SQL query can run on the server	`SET statement_timeout` SQL command
Socket timeout	How long to wait for network-level read/write operations	`options` parameter or `keepalives` settings

Setting the Connection Timeout

The connection timeout controls how long the client waits for PostgreSQL to accept the connection before giving up. This is especially important when the database server is under heavy load, unreachable, or behind a flaky network.

Pass the connect_timeout parameter (in seconds) to psycopg2.connect():

import psycopg2
from psycopg2 import OperationalError

try:
    conn = psycopg2.connect(
        dbname="mydb",
        user="postgres",
        password="secret",
        host="localhost",
        port="5432",
        connect_timeout=10  # Wait up to 10 seconds for a connection
    )
    print("Connected successfully")
except OperationalError as e:
    print(f"Connection failed: {e}")

Output (successful connection):

Connected successfully

If the server is unreachable and 10 seconds elapse, psycopg2 raises an OperationalError:

Connection failed: could not connect to server: Connection timed out

Setting the Statement Timeout

The statement timeout limits how long a single SQL query is allowed to execute on the PostgreSQL server. If the query exceeds the timeout, the server cancels it and psycopg2 raises an exception. The value is specified in milliseconds.

import psycopg2
from psycopg2 import OperationalError

conn = None
cursor = None

try:
    conn = psycopg2.connect(
        dbname="mydb",
        user="postgres",
        password="secret",
        host="localhost",
        port="5432"
    )
    cursor = conn.cursor()

    cursor.execute("SET statement_timeout = %s", ("5000",))

    cursor.execute("SELECT pg_sleep(10)")
    print("Query executed successfully")

except OperationalError as e:
    print(f"Query cancelled: {e}")

finally:
    if cursor is not None:
        cursor.close()
    if conn is not None:
        conn.close()

Output:

Query cancelled: canceling statement due to statement timeout

The pg_sleep(10) call simulates a long-running query. Since the statement timeout is set to 5 seconds, PostgreSQL cancels the query and psycopg2 receives the error.

Setting Statement Timeout at Connection Time

Instead of executing a separate SET command after connecting, you can pass the statement timeout directly through the options parameter:

import psycopg2
from psycopg2 import OperationalError

try:
    conn = psycopg2.connect(
        dbname="mydb",
        user="postgres",
        password="secret",
        host="localhost",
        port="5432",
        options="-c statement_timeout=5000"  # 5 seconds in milliseconds
    )
    cursor = conn.cursor()
    cursor.execute("SELECT pg_sleep(10)")
    print("Query executed successfully")

except OperationalError as e:
    print(f"Query cancelled: {e}")

finally:
    if 'cursor' in locals() and cursor is not None:
        cursor.close()
    if 'conn' in locals() and conn is not None:
        conn.close()

Output:

Query cancelled: canceling statement due to statement timeout

This approach is cleaner because the timeout is applied to the entire session from the moment the connection is established.

Warning

The statement_timeout value is in milliseconds, not seconds. Setting it to 5 means 5 milliseconds, which will cause almost every query to fail. A common mistake is confusing the unit:

cursor.execute("SET statement_timeout = %s", ("5",))

cursor.execute("SET statement_timeout = %s", ("5000",))

Setting Socket-Level Read/Write Timeouts

Socket-level timeouts control how long the client waits for data to arrive from or be sent to the server at the network layer. These are useful for detecting situations where the connection is silently dropped (e.g., a firewall kills an idle connection).

PostgreSQL supports TCP keepalive parameters that you can configure through psycopg2:

import psycopg2
from psycopg2 import OperationalError

try:
    conn = psycopg2.connect(
        dbname="mydb",
        user="postgres",
        password="secret",
        host="localhost",
        port="5432",
        keepalives=1,             # Enable TCP keepalives
        keepalives_idle=30,       # Seconds before sending a keepalive probe
        keepalives_interval=10,   # Seconds between keepalive probes
        keepalives_count=5        # Number of failed probes before closing
    )
    print("Connected with keepalive settings")

    cursor = conn.cursor()
    cursor.execute("SELECT 1")
    result = cursor.fetchone()
    print(f"Query result: {result[0]}")

except OperationalError as e:
    print(f"Connection/query failed: {e}")

finally:
    if 'cursor' in locals() and cursor is not None:
        cursor.close()
    if 'conn' in locals() and conn is not None:
        conn.close()

Output:

Connected with keepalive settings
Query result: 1

Here is what each parameter does:

Parameter	Description	Default
`keepalives`	`1` to enable, `0` to disable TCP keepalives	OS default
`keepalives_idle`	Seconds of inactivity before sending the first keepalive probe	OS default (often 7200)
`keepalives_interval`	Seconds between subsequent keepalive probes	OS default (often 75)
`keepalives_count`	Number of unanswered probes before the connection is considered dead	OS default (often 9)

With the settings above, if the server becomes unreachable, the client detects it within approximately 30 + (10 × 5) = 80 seconds instead of potentially waiting for hours.

Combining Multiple Timeouts

In production applications, you typically want to set all three timeout types together for comprehensive protection:

import psycopg2
from psycopg2 import OperationalError


def get_connection():
    """Create a database connection with comprehensive timeout settings."""
    return psycopg2.connect(
        dbname="mydb",
        user="postgres",
        password="secret",
        host="localhost",
        port="5432",
        connect_timeout=10,                      # 10s to establish connection
        options="-c statement_timeout=30000",     # 30s max per query
        keepalives=1,                             # Enable TCP keepalives
        keepalives_idle=30,
        keepalives_interval=10,
        keepalives_count=5
    )


try:
    conn = get_connection()
    cursor = conn.cursor()

    cursor.execute("SELECT version()")
    version = cursor.fetchone()
    print(f"PostgreSQL version: {version[0]}")

except OperationalError as e:
    print(f"Database operation failed: {e}")

finally:
    if 'cursor' in locals() and cursor is not None:
        cursor.close()
    if 'conn' in locals() and conn is not None:
        conn.close()

Output:

PostgreSQL version: PostgreSQL 16.3 on x86_64-pc-linux-gnu, compiled by gcc ...

Common Mistake: Not Handling the Timeout Exception

Setting a timeout is only half the solution. If you don't catch the resulting exception, your application will crash:

import psycopg2

conn = psycopg2.connect(
    dbname="mydb", user="postgres", password="secret",
    host="unreachable-server", port="5432",
    connect_timeout=5
)

psycopg2.OperationalError: could not connect to server: Connection timed out

The correct approach is to always wrap timeout-prone operations in a try/except block:

import psycopg2
from psycopg2 import OperationalError

try:
    conn = psycopg2.connect(
        dbname="mydb", user="postgres", password="secret",
        host="unreachable-server", port="5432",
        connect_timeout=5
    )
except OperationalError:
    print("Could not connect to the database. Retrying or using fallback...")

When to Use Each Timeout Type

Scenario	Recommended Timeout
Database server might be down or unreachable	`connect_timeout`
Queries might run too long due to missing indexes or large datasets	`statement_timeout`
Network connections might drop silently (cloud/VPN environments)	`keepalives` settings
General production deployment	All three combined

Properly configuring timeouts in psycopg2 is a straightforward but essential step toward building resilient Python applications that interact with PostgreSQL. By combining connection, statement, and socket-level timeouts, you protect your application from hanging operations, wasted resources, and poor user experiences.