Python psycopg2: hHow to Set Timeouts in psycopg2 Using 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
A connect_timeout of 5 to 10 seconds is a reasonable default for most applications. For connections over slow networks or to remote cloud databases, you may want to increase this value.
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()
# Set statement timeout to 5 seconds (5000 milliseconds)
cursor.execute("SET statement_timeout = %s", ("5000",))
# This query intentionally runs for 10 seconds to trigger the timeout
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.
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:
# WRONG: 5 milliseconds - almost every query will time out
cursor.execute("SET statement_timeout = %s", ("5",))
# CORRECT: 5000 milliseconds = 5 seconds
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
# WRONG: no exception handling - application crashes on timeout
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...")
Always handle psycopg2.OperationalError when timeouts are configured. An unhandled timeout exception will terminate your application or leave database connections in an inconsistent state.
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.