A Pooling Proxy

Why this stage exists

Each connection to a database costs the server something: memory for its session, a slot under --max-connections, and in PostgreSQL a whole operating-system process. A web application with fifty workers, each holding a connection that is idle 99% of the time, wastes almost all of it. A connection pooler sits in the middle. Clients connect to it as if it were the database. It keeps a few connections to the real server and lends them out.

droid-pool is a second program, in the spirit of PgBouncer, and building it is the payoff for the last stages. It speaks the protocol on both sides, so it is built almost entirely from code you already have: the poll loop, the buffers, the reader that cuts bytes into messages, the writer that builds them. What is new is the bookkeeping, which client holds which connection.

Session mode: lend for as long as the client stays

The simplest pooling is session mode. A client is given a server connection when it first needs one, and keeps it until it leaves. Everything between the two is forwarded unchanged, in both directions, message by message. The pooler does not need to understand the messages, only to find where each one ends.

The pool has a size. When every connection is lent and another client needs one, that client waits, in order. Its query simply gets no answer yet, as a statement waiting for a lock gets none. When someone leaves, the first waiting client is given their connection.

Reset before you lend again

A connection carries its last client's state. Maybe a transaction was left open by a client that crashed. Maybe there are prepared statements named p, or an isolation level that was changed. If the pool lends that connection unchanged, the next client inherits all of it: it is suddenly inside someone else's transaction and sees their uncommitted rows. So before lending a connection again, the pool resets it. It sends ROLLBACK if the connection's last ReadyForQuery said T or E, then DEALLOCATE ALL, then the default isolation level, each as a Query of its own, and reads the answers itself. They must be separate Queries: sent together, they would run as one implicit transaction (section 96), and SET TRANSACTION is refused inside a transaction. The next client finds a connection as good as new.

Notice how the pool knows whether a transaction is open: from the status byte of the last ReadyForQuery the server sent on that connection. Section 96 made the server report it after every answer. The pool doesn't have to parse SQL to know where a transaction begins and ends.

The admin connection

How does anyone see what the pool is doing? PgBouncer answers queries on a virtual database named pgbouncer. droid-pool does the same with pool: a client whose StartupMessage names that database gets no server connection, and SHOW POOLS returns one row of counts.

$psql -h 127.0.0.1 -p 6432 -U droid pool
pool=> show pools;
 cl_active | cl_waiting | sv_active | sv_idle
-----------+------------+-----------+---------
         2 |          1 |         2 |       0
(1 row)
 

Output contract for this stage

These are the exact lines and messages the tests check. The full definition is on the output contract page, section 101.

The admin connection is numbered like any client, so it moves every later client's number by one, but it is never counted in SHOW POOLS.

What you should see when done

$droid --server --port 5433 --db shop.db &
[SERVER] listening on 127.0.0.1:5433
$droid-pool --port 6432 --server-port 5433 --pool-size 2
[POOL] listening on 127.0.0.1:6432
[POOL] client 1 connected
[POOL] server 1 connected
[POOL] client 1 uses server 1
[POOL] client 2 connected
[POOL] client 1 closed
[POOL] client 1 released server 1
[POOL] client 2 uses server 1

Common bugs in this stage

• Forwarding by read instead of by message. A read can end in the middle of a message. Cut whole messages on both sides, exactly as the server does.
• Opening a server connection for every client. That is a proxy, not a pool. Open lazily, up to the size, and lend.
• Forgetting the reset. The next client lands in the last one's transaction.
• Forwarding the reset's answers. The client never asked for them.
• One Query for the whole reset. It becomes an implicit transaction, and the SET is refused inside it.
• Dying with the server. A lost server connection costs the client holding it, not the pooler.
• Using the server's BackendKeyData. The client talks to the pool; its process id is the pool's client number.

Language notes

The page above is the same for every language. These are the only language-specific hints.

• C: build droid-pool from the same server.c and wire.c with a different main; a ServerLink array of --pool-size entries and a FIFO of waiting clients.
• C++: the same sources compiled into a second executable; a std::deque<int> of waiting clients.
• Rust: a second binary in the same crate (src/bin/droid-pool.rs), sharing the wire module.
• Zig: a second b.addExecutable in build.zig, importing the same wire module.