How Database Connection Pools Affect Bulk Reads

If you run exports, backfills, or replication snapshots against a database behind a pooler, this page explains what the pool does to those long reads. A connection pool is a set of open connections that a proxy or library keeps and hands out again. As of November 2026 the pool mode decides which connection a bulk read gets, how long it keeps it, and which session features survive.

What does a bulk read need from a connection?

A bulk read is a long, stateful use of one connection, which is why pools treat it differently from a web request that borrows one for milliseconds. A table scan borrows a connection for an hour and leans on four things that exist only there, a cursor, a snapshot, an open transaction, and any prepared statement the driver cached.

Postgres DECLARE creates a cursor that, without WITH HOLD, “can only be used within the current transaction” per the DECLARE reference, and FETCH FORWARD 10000 pulls each batch. A REPEATABLE READ transaction “only sees data committed before the transaction began” per the isolation docs, which keeps those batches consistent. The PREPARE docs add that “Prepared statements only last for the duration of the current database session.”

A pool that moves clients between server connections has to keep the reader on one connection or break one of these four.

How transaction pooling pins a connection for the whole read

A transaction-mode pooler cannot release a bulk read early, so the read occupies one pooled connection from first fetch to last. PgBouncer’s config reference defines the mode in one line, “Server is released back to pool after transaction finishes.” A cursor-driven export that fetches for an hour holds its server connection for an hour. With default_pool_size at its default of 20, five such jobs take a quarter of the pool.

Amazon RDS Proxy calls the same effect pinning. Its pinning page lists declared cursors, SET, PREPARE, LISTEN, advisory locks, and any statement over 16 KB as triggers. It warns that “Pinning reduces the effectiveness of connection reuse,” and a pinned client stays on one database connection until its session ends.

ProxySQL does it for MySQL under the name multiplexing. Its multiplexing doc says that while a transaction is active, multiplexing is disabled until commit or rollback. LOCK TABLES, GET_LOCK(), user variables, and text-protocol PREPARE also switch it off, some of them for good. The budget is the same for all three poolers, one pooled connection per concurrent bulk reader.

Which session features break behind a transaction pooler?

Only the features a bulk read uses outside a transaction break. PgBouncer’s features page carries the compatibility table, and the rows below combine it with the RDS Proxy pinning page and ProxySQL’s multiplexing doc.

The prepared-statement row is newer than the rest. PgBouncer’s release notes for 1.21.0 say tracking them raised throughput “anywhere from 15% to 250%, depending on the workload.” The setting defaults to off. Managed poolers inherit the same table, since Neon and Supabase both run PgBouncer in transaction mode.

How many connections should a bulk reader use?

Fewer than most people open, because Postgres throughput peaks near two active connections per core and then falls. The PostgreSQL wiki gives the formula (core_count * 2) + effective_spindle_count for active connections and says it “has held up pretty well across a lot of benchmarks for years.”

Each extra connection costs less memory than the folklore says, but many idle ones cost CPU. Andres Freund measured a read-only connection at under 8 MiB, and his follow-up for Microsoft found the real limit was snapshot computation beside thousands of idle sessions. PostgreSQL 14 shipped the fix, and its notes say it “also improves performance when there are many idle sessions.”

Consistent parallel reads across several pooled connections

Parallel readers need one snapshot, and a pool hands each worker a different connection with its own. Postgres fixes this with snapshot export. The admin functions page says snapshots “are exported with the pg_export_snapshot function” and imported with SET TRANSACTION, which the SET TRANSACTION docs allow only at REPEATABLE READ or above and before the transaction’s first query.

-- coordinator, keep this transaction open until every worker has started
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT pg_export_snapshot();
-- returns something like 00000003-0000001B-1

-- each worker, on its own pooled connection
BEGIN ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT '00000003-0000001B-1';
DECLARE shard CURSOR FOR SELECT * FROM orders WHERE id BETWEEN 1 AND 5000000;
FETCH FORWARD 10000 FROM

-- coordinator, keep this transaction open until every worker has started
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT pg_export_snapshot();
-- returns something like 00000003-0000001B-1

-- each worker, on its own pooled connection
BEGIN ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT '00000003-0000001B-1';
DECLARE shard CURSOR FOR SELECT * FROM orders WHERE id BETWEEN 1 AND 5000000;
FETCH FORWARD 10000 FROM

-- coordinator, keep this transaction open until every worker has started
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT pg_export_snapshot();
-- returns something like 00000003-0000001B-1

-- each worker, on its own pooled connection
BEGIN ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT '00000003-0000001B-1';
DECLARE shard CURSOR FOR SELECT * FROM orders WHERE id BETWEEN 1 AND 5000000;
FETCH FORWARD 10000 FROM

The coordinator’s transaction is one more long transaction the pool has to carry. MySQL has START TRANSACTION WITH CONSISTENT SNAPSHOT, InnoDB only at REPEATABLE READ per the manual, but no snapshot export, so parallel MySQL shards can disagree.

Where connection pools and bulk reads go wrong

Three failures account for most bulk-read incidents behind a pool, and each has an exact string to search for.

sorry, too many clients already

Postgres raises this with SQLSTATE 53300 when every max_connections slot is taken, and the source carries the message verbatim. Postgres 16 reserves slots for members of pg_use_reserved_connections through the new reserved_connections setting, so grant that role to the export user.

ALTER SYSTEM SET reserved_connections = 5;
GRANT pg_use_reserved_connections TO

ALTER SYSTEM SET reserved_connections = 5;
GRANT pg_use_reserved_connections TO

ALTER SYSTEM SET reserved_connections = 5;
GRANT pg_use_reserved_connections TO

prepared statement S_1 does not exist

This is the classic transaction-pooling symptom. The JDBC driver names server-side statements S_1, S_2, and so on per its server-prepare page, and the pooler later routes the client to a server connection that never saw the PREPARE. PgBouncer’s FAQ says to set max_prepared_statements to a non-zero value on 1.21 or later, or disable client-side preparation with prepareThreshold=0 on JDBC.

[pgbouncer]
pool_mode = transaction
max_prepared_statements = 200

[pgbouncer]
pool_mode = transaction
max_prepared_statements = 200

[pgbouncer]
pool_mode = transaction
max_prepared_statements = 200

cursor does not exist after the first fetch

The cursor was declared in one transaction and fetched in another, usually because autocommit was on. The pgjdbc query docs state the rule for cursor-based fetching, “The Connection must not be in autocommit mode.” The driver’s defaultRowFetchSize of 0 means it otherwise collects the whole result at once.

conn.setAutoCommit(false);
Statement st = conn.createStatement();
st.setFetchSize(10000);
ResultSet rs = st.executeQuery("SELECT * FROM events");

conn.setAutoCommit(false);
Statement st = conn.createStatement();
st.setFetchSize(10000);
ResultSet rs = st.executeQuery("SELECT * FROM events");

conn.setAutoCommit(false);
Statement st = conn.createStatement();
st.setFetchSize(10000);
ResultSet rs = st.executeQuery("SELECT * FROM events");

How the main poolers and drivers handle long reads

Every pooler below documents a limit that touches bulk reads. Versions are from each project’s changelog or tag as of November 2026, and the last PgCat tag is more than two years old.

Pgpool-II’s failure is the quiet one. Its docs say clients beyond num_init_children “are blocked (not rejected with an error, like PostgreSQL),” so a batch job over the limit hangs instead of erroring.

Related guides

Checkpointing and integrity verification cover what a bulk read does once a run has to pause or prove itself. Full load vs incremental vs CDC explains when a bulk read is the right tool at all. Postgres logical replication needs a replication connection instead of a pooled one, and WAL archiving is the other consumer of a long-held snapshot.

Frequently asked questions

What is a database connection pool?

A connection pool is a set of open database connections that a proxy or library keeps and hands out again and again. Clients skip the cost of opening a new connection, and the database sees a fixed, small number of backends instead of one per client. Pools live inside the application, as in HikariCP, or in front of the server, as in PgBouncer.

Does connection pooling make large queries faster?

Not by itself. A pool removes connection setup cost and caps concurrency, which protects throughput when many clients compete. A single bulk read still runs on one server connection at the speed the database and network allow. Pools help mainly by stopping a flood of parallel readers from pushing the server past the point where throughput falls.

What is the difference between session pooling and transaction pooling?

Session pooling gives a client one server connection for as long as the client stays connected, so every session feature works. Transaction pooling returns the server connection to the pool as soon as each transaction ends. Far more clients can share a few connections that way, but features that live outside a transaction break, such as WITH HOLD cursors, SET, and LISTEN.

Can you use server-side cursors through PgBouncer in transaction mode?

Yes, as long as the DECLARE and every FETCH happen inside one open transaction, because PgBouncer only returns the server connection when the transaction finishes. A WITH HOLD cursor is the exception. It is meant to outlive its transaction, and PgBouncer’s compatibility table marks it as never supported in transaction mode.

Why does a long-running query fail with prepared statement S_1 does not exist?

The client prepared the statement on one server connection and a transaction pooler later handed it a different one, where that name was never prepared. Postgres prepared statements last only for the session that created them. Fixes are to set max_prepared_statements above zero on PgBouncer 1.21 or later, to disable client-side preparation, or to switch that pool to session mode.

How many connections should a bulk read job open?

Start from the PostgreSQL wiki formula, core count times two plus effective spindle count, and treat it as a ceiling for active connections rather than a target. A four-core server with one disk lands near 10. Beyond that point extra readers add contention, and the wiki warns that throughput falls past that knee.

How do you read a table in parallel without the readers seeing different data?

In Postgres, open one REPEATABLE READ transaction, call pg_export_snapshot, and have every worker start its own REPEATABLE READ transaction with SET TRANSACTION SNAPSHOT using that identifier. All workers then see the same committed state. In MySQL, each InnoDB worker starts with START TRANSACTION WITH CONSISTENT SNAPSHOT, which only works at REPEATABLE READ.

What does Lost connection to MySQL server during query mean on a big export?

The server stopped waiting for the client to read the next block of a large result set. The MySQL manual ties the during-query form to queries that send millions of rows and says to raise net_read_timeout from its default of 30 seconds to 60 or more. Streaming the result row by row keeps the client draining and avoids the timeout.