Tag Archives: performance

PgBouncer, Prepared Statements, and Picking the Right Pool Mode

PgBouncer is the standard answer when an application opens more PostgreSQL connections than the server can usefully service. A process-per-request runtime and a serverless function that dials the database directly get there by completely different routes and land in the same place. The part that goes wrong is the pool mode, because the aggressive setting is the one everybody wants and it silently changes the semantics your code was written against.

Three Modes, One Real Decision

Session pooling assigns a server connection for the life of the client connection. It is safe and it buys you very little, since a worker process holding a connection for the length of a request is the situation you were trying to fix.

Transaction pooling assigns a server connection for the length of a transaction and returns it to the pool at commit. This is the mode that delivers the numbers people install PgBouncer for, and it is the mode with consequences.

Statement pooling returns the connection after every individual statement, which forbids multi-statement transactions entirely. It exists for specific workloads and is almost never what you want.

What Transaction Mode Takes Away

The rule is that anything living outside a transaction stops being reliable, because between two statements you may be on a different backend. That is a longer list than it first appears:

SET / RESET at session scope
LISTEN / NOTIFY
advisory locks taken outside a transaction
WITH HOLD cursors
temporary tables
session-scoped GUCs set by a connection hook

Session-level SET is the one that catches real applications. A framework bootstrap that sets search_path or timezone once on connect works perfectly in development against a direct connection, and in production the setting lands on whichever backend happened to serve that statement. Every later query gets a different backend without it. The failure is intermittent and nearly impossible to reproduce on demand.

Advisory locks are worse, because the failure is silent rather than noisy. A lock taken with pg_advisory_lock() outside a transaction belongs to a session you no longer control. The transaction-scoped variant, pg_advisory_xact_lock(), is released at commit and behaves correctly under transaction pooling. If you use advisory locks for job coordination, that one function name is the difference between working and quietly running the same job twice.

Prepared Statements Are Fixed, with Conditions

For years the answer to prepared statements under transaction pooling was that you could not use them. PgBouncer 1.21, released in October 2023, changed that. Set max_prepared_statements to a non-zero value and PgBouncer tracks named prepared statements itself, rewriting them to internal names and re-preparing them on whichever backend a client lands on:

[pgbouncer]
pool_mode = transaction
max_prepared_statements = 200

The value is the size of an LRU cache of statements kept on each server connection, so it wants to be at least as large as the number of distinct statements a typical request issues. Zero disables the feature and restores the old behavior.

The condition attached is important. This only works for protocol-level prepared statements, the ones sent through the extended query protocol. A literal PREPARE foo AS SELECT ... sent as a plain text query is invisible to PgBouncer and still breaks, because from the outside it is just another statement.

For PHP specifically, this means checking what PDO is actually doing. With ATTR_EMULATE_PREPARES left on, PDO interpolates parameters client-side and sends plain SQL, so there are no real prepared statements and nothing to break. Turn emulation off and you get genuine protocol-level prepares, which is what you want for both correctness and plan reuse, and which is exactly the case max_prepared_statements exists to handle.

$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION,
]);

How I Choose

Start with transaction mode, then go looking for the session state your application depends on rather than waiting for it to surface. Grep for LISTEN, for pg_advisory_lock, for temporary tables, and for anything setting a GUC on connect. Move search_path out of a connection hook and into the connection string, where PgBouncer passes it through as part of the startup parameters and it survives correctly.

Keep a second pool in session mode on a different port for the small number of things that genuinely need a stable session, which is usually a migration runner and whatever handles LISTEN. Two pools with clear rules beats one pool with exceptions nobody remembers.

And size the pool against what PostgreSQL can actually do, since the point of pooling is to keep the database out of the region where it spends more time context switching than working. A pool larger than the database can service just moves the queue.

Fanning Out 140 DNS Queries per Host Without Melting the Server

A blacklist check is not one DNS query. Checking a single IP against our source list means somewhere north of 140 DNS lookups, and the checker runs continuously across a large number of hosts. The naive implementation of that is a loop calling getaddrinfo(), and it is catastrophically slow for a reason that has nothing to do with CPU.

DNS Is Almost Entirely Waiting

A DNSBL lookup is a small UDP packet out and a small UDP packet back. The work is negligible. What dominates is latency, and the distribution is ugly: most zones answer in tens of milliseconds, a few take hundreds, and some fraction never answer at all and burn your full timeout. Serialized, 140 lookups at an average of 60ms is over eight seconds for one host, and the tail is far worse than the average.

The instinct is a thread per query. That works until it doesn’t. Threads are expensive relative to the work being done, and a thread blocked on a 5 second DNS timeout is a thread doing nothing while holding a stack. Scale that across concurrent hosts and you are context-switching more than resolving.

One Socket, Many Queries in Flight

DNS over UDP is already designed for multiplexing. Every query carries a 16-bit ID, and the response echoes it back. That gives you everything you need to run hundreds of queries over a single socket and demultiplex the answers as they arrive:

//
// send all queries first, then collect
//
for (auto const &source : this->m_sources)
{
    uint16_t id = this->_next_query_id();

    this->m_pending[id] = { source.id, now_ms() + source.timeout_ms };
    this->_send_query(id, source, _host);
}

Then a single epoll_wait() loop reads whatever comes back, looks up the ID, and retires that entry. The whole batch costs one socket and one thread, and wall clock becomes the slowest response rather than the sum of all of them.

Two details make this correct rather than merely fast. The query ID has to be random, not sequential, because a predictable ID plus a known source port is the cache-poisoning setup from 2008. And you have to verify that the response actually matches the question you asked, since the socket is unconnected and will happily hand you a packet from anyone.

Timeouts Are the Hard Part

Nothing tells you a UDP query failed. The absence of a response is the signal, which means every in-flight query needs a deadline and something has to sweep for expired ones. Checking all 140 pending entries on every loop iteration is fine at this scale. At larger scale, keep them in a structure ordered by deadline so the sweep only touches what has actually expired.

int64_t now = now_ms();

for (auto it = this->m_pending.begin(); it != this->m_pending.end(); )
{
    if (it->second.deadline <= now)
    {
        this->_record_timeout(it->second.source_id);
        it = this->m_pending.erase(it);
    } else
    {
        ++it;
    }
}

The timeout value itself is a judgment call with real consequences. Too short and you record false negatives for slow-but-working zones, which quietly degrades your results. Too long and one dead source sets the floor for every check. Per-source timeouts, tuned from that source’s observed behavior, beat one global number.

The Failure Modes That Only Show Up in Production

Source port exhaustion is the first one. If you open a socket per query, you will run out of ephemeral ports under load, and the symptom is intermittent bind() failures that look like nothing else. A small pool of sockets with many queries multiplexed over each avoids it entirely.

Rate limiting is the second. Public DNSBL operators will throttle or block you, and they are right to. Anything running at volume belongs on a paid data feed or a local mirror, and the checker needs per-source concurrency caps so one aggressive batch cannot trip a limit that then poisons results for every subsequent check.

The third one took me longest to appreciate. A source that returns NXDOMAIN and a source that never answers look similar in a naive implementation, and they mean completely different things. The first is a confirmed “not listed”. The second is “no data”, and recording it as “not listed” turns an outage at one provider into a silent gap in your results. Track them as distinct outcomes from the start, because retrofitting that distinction after the fact means every historical row is ambiguous.