[Python] Prepared statements not working with Psycopg3 + PgBouncer (transaction pooling)

I'm running into unexpected behavior when trying to use prepared statements with TimescaleDB through PgBouncer in transaction pooling mode.

According to the Psycopg3 docs on prepared statements and resources from TimescaleDB like this article, this should work.

However, in practice:

• With a direct connection to PgBouncer's port: prepared statements work as expected.
• With a NullConnectionPool required for pooling with PgBouncer: prepared statements don’t seem to be created or reused at all, even when explicitly setting prepare=True.

Here are two minimal examples that demonstrate the issue.

Example 1: Direct Connection + PgBouncer (works)


# client.py
import psycopg


class DBClient:
def __init__(self, server, name, username, password, port, *args, **kwargs):
self.server = server
self.name = name
self.username = username
self.password = password
self.port = port
self._connection: psycopg.Connection | None = None

@property
def connection(self) -> psycopg.Connection:
if not self._connection or self._connection.closed:
self._connection = psycopg.connect(
host=self.server,
dbname=self.name,
user=self.username,
password=self.password,
port=self.port,
prepare_threshold=0,
)
return self._connection

def close_connection(self):
if self._connection and not self._connection.closed:
self._connection.close()
self._connection = None


# data.py
import pandas as pd
from client import DBClient

def get_data(start_date):
db = DBClient("host", "mydb", "user", "pass", 5432)
query = "SELECT date, value FROM my_schema.my_table WHERE date >= %s"
params = [start_date]

with db.connection.cursor() as cursor:
result = cursor.execute(query, params, prepare=True)
rows = result.fetchall()
columns = [desc[0] for desc in result.description]

df = pd.DataFrame(rows, columns=columns)
return df

Example 2: NullConnectionPool + PgBouncer (does not work)


# client.py
from contextlib import contextmanager
from psycopg_pool import NullConnectionPool


class DBClient:
def __init__(self, server, name, username, password, port, *args, **kwargs):
self.pool = NullConnectionPool(
f"host={server} dbname={name} user={username} password={password} port={port}",
min_size=0,
max_size=20,
)

@contextmanager
def get_connection(self):
if not self.pool:
raise RuntimeError("Connection pool not initialized")

conn = self.pool.getconn()
try:
yield conn
finally:
self.pool.putconn(conn)

@contextmanager
def get_cursor(self, **cursor_kwargs):
with self.get_connection() as conn:
with conn.cursor(**cursor_kwargs) as cursor:
yield cursor

def close_pool(self):
if self.pool:
self.pool.close()
self.pool = None


# data.py
import pandas as pd
from client import DBClient

def get_data(start_date):
db = DBClient("host", "mydb", "user", "pass", 5432)
query = "SELECT date, value FROM my_schema.my_table WHERE date >= %s"
params = [start_date]

with db.get_cursor() as cursor:
result = cursor.execute(query, params, prepare=True)
rows = result.fetchall()
columns = [desc[0] for desc in result.description]

df = pd.DataFrame(rows, columns=columns)
return df


In Example 1, the prepared statement is created and used correctly. In Example 2, despite passing prepare=True, no prepared statements are created or reused.

Since using a single persistent connection in a backend service is not a viable option, I’d like to know:

• Is this expected behavior with TimescaleDB, PgBouncer, or Psycopg3?
• Can prepared statements be reliably used when connecting through PgBouncer in transaction pooling mode?

Does anyone know how to solve this issue and properly use prepared statements with TimescaleDB and PgBouncer?

Continue reading...