A connection pooler can hand a server connection to a client for the duration of a whole session or only for the duration of a transaction. Compare these two pooling modes and explain what stops working in the transaction-scoped one.
answer
- Session mode = bound until disconnect, safe, low ratio
- Transaction mode = bound BEGIN..COMMIT, high ratio
- Breaks: SET, temp tables, named prepares, advisory locks, cursors
- Transaction-scoped SET / locks are the fix
- Long transactions pin a scarce shared backend
basics
~20 sSession pooling binds a server connection to a client for its whole session - safe, but the multiplexing ratio is poor. Transaction pooling returns the server connection after each commit or rollback, allowing far more clients per server connection, but anything session-scoped breaks: session settings, temporary tables, session-level prepared statements, advisory locks, and cursors held across transactions.
solid answer
~60 s**Session pooling**: a client is given a server connection at connect time and keeps it until it disconnects. Everything behaves like a direct connection, so no application changes are needed - but a mostly idle client still occupies a server session, so the concurrency ratio is roughly one to one. **Transaction pooling**: a server connection is assigned at BEGIN and released at COMMIT or ROLLBACK. Thousands of client connections can share tens of server connections, which is why it exists. The cost is that consecutive transactions from the same client may land on different backends, so **anything session-scoped is unsafe**: - session `SET` parameters (timeouts, search path, role, timezone); - temporary tables; - session-level prepared statements - a classic breakage with drivers that prepare implicitly; - session-level advisory locks and asynchronous notification subscriptions; - cursors and any statement sequence spanning transactions. To use it, an application must make every transaction self-contained: no session state, no autocommit-mode statements assuming continuity, prepared statements disabled or protocol-level-per-transaction, and timeouts set per transaction rather than per session.
code
sql · 13 lines-- unsafe under transaction pooling: applies to an arbitrary shared backend
SET statement_timeout = '5s';
SELECT pg_advisory_lock(42);
CREATE TEMPORARY TABLE staging AS SELECT ...;
PREPARE find_user AS SELECT * FROM users WHERE id = $1;
-- safe: everything scoped to one transaction
BEGIN;
SET LOCAL statement_timeout = '5s';
SELECT pg_advisory_xact_lock(42);
CREATE TEMPORARY TABLE staging ON COMMIT DROP AS SELECT ...;
SELECT * FROM users WHERE id = $1; -- parameterised, no named prepare
COMMIT;go deeper
Know the two modes by their binding granularity - whole session versus one transaction - and that the transaction-scoped mode packs far more clients onto fewer server sessions.
Derive the breakages from the binding: settings, temp tables, prepared statements, advisory locks, cursors, and name the transaction-scoped replacements.
Discuss the application contract transaction mode imposes, driver-level prepared-statement handling, and why long transactions are disproportionately harmful there.
Decide the topology: which endpoints run in which mode, how migrations and admin tooling get a session-mode path, and what constraints the mode imposes on service design.
## Why modes exist An external pooler sits between many application clients and a database that can only afford a modest number of server sessions. How aggressively it can share those server sessions depends on how long it must keep one bound to a given client. That binding granularity is the *pooling mode*. ## Session mode The pooler assigns a server connection when the client connects and holds it until the client disconnects. Semantically the client sees a normal, private database session, so every feature works unchanged. What it buys: connection *setup* is amortised - clients can connect and disconnect quickly without paying handshake and backend-creation cost each time - and the pooler enforces an upper bound plus a queue, so the database never sees more sessions than configured. What it does not buy: density. A client holding an idle connection still occupies a server session. If your problem is thousands of mostly idle clients, session mode does not solve it. ## Transaction mode The pooler assigns a server connection at the start of a transaction and takes it back at COMMIT or ROLLBACK. Between transactions the client holds no backend at all. Because typical OLTP transactions are milliseconds long while clients are idle most of the time, this yields large multiplexing ratios - thousands of client connections over tens of server ones. For serverless or very high instance-count deployments it is often the only workable arrangement. The price is that the *session* abstraction is now a fiction. Two consecutive transactions from one client may execute on different backends, and a backend may serve unrelated clients in between. ## What breaks, concretely **Session parameters.** `SET` outside a transaction applies to whichever backend happened to serve it, then that backend goes back into the shared set - both losing the setting for you and leaking it to others. Use the transaction-scoped variant of `SET` if the engine has one, or set defaults at role/database level. **Temporary tables.** Created in one transaction, they are invisible from another backend afterwards - and the pooler must clean them up. Multi-step flows that stage data in temp tables need real tables with a scope key, or a single transaction. **Prepared statements.** A named prepared statement lives in one backend. A driver that transparently prepares and then reuses by name will hit "prepared statement does not exist" or, worse, a name collision. Mitigations: disable server-side prepares, use protocol-level prepare-and-execute within a single transaction, or run the pooler in a mode that tracks and re-prepares statements per backend where supported. **Session-level advisory locks and notification subscriptions.** These are bound to a backend and survive transaction end, so in transaction mode they are held by an arbitrary shared session - a correctness and availability hazard. Use transaction-scoped locks instead. **Cursors held across transactions**, and any statement sequence that assumes continuity between transactions, including client-side identity retrieval that reads a session-scoped "last inserted value" after the inserting transaction has ended. **Autocommit-style multi-statement flows.** Anything that relies on "my next statement lands on the same backend" is unsafe by definition. ## Statement mode The extreme variant returns the connection after every single statement, so multi-statement transactions are impossible. It exists for pure autocommit workloads and is rarely appropriate. ## Choosing Use **session mode** when the application relies on session features, when you mainly want to bound and reuse connections, or when you cannot audit the application's session usage. Use **transaction mode** when client count vastly exceeds affordable server sessions, and be explicit that it imposes an application contract: transactions must be self-contained, short, and free of session state. Long transactions are especially harmful here because they pin a scarce shared backend. A practical hybrid many teams run: transaction mode for the main OLTP path, plus a separate session-mode endpoint for the migration runner, admin tooling, and anything using advisory locks or temp tables. ## Interview framing The strong answer states the binding granularity first (connect-to-disconnect versus BEGIN-to-COMMIT), then derives the breakages from that single fact rather than reciting a list. Mentioning prepared statements and advisory locks specifically signals real operational exposure.
- Why do server-side prepared statements often fail under transaction pooling, and what are the options?A named prepared statement is created inside one backend, but the next transaction may be routed to a different backend that has never seen that name, producing errors or name collisions. The options are to disable server-side prepares in the driver, to use the protocol's prepare-and-execute within a single transaction so preparation and use always share a backend, or to run a pooler that tracks prepared statements and transparently re-prepares them per backend where that feature exists.
- Why are long-running transactions particularly damaging under transaction pooling?In transaction mode the server connection is held for the entire transaction, so a slow transaction occupies one of a deliberately small set of shared backends and every waiting client queues behind it. The same transaction on a direct connection would only affect its own session. It also compounds the usual harms of long transactions - retained locks and an old snapshot that blocks cleanup of dead row versions.
Session mode is renting a desk for the whole day; transaction mode is a hot desk you take for one meeting - you must not leave anything in the drawers, because someone else sits there next.
saying these in an interview costs you the question
- Assuming transaction pooling is transparent to the application
- Using session-level SET or advisory locks behind a transaction-mode pooler
- Believing temporary tables persist between transactions from the same client
- Thinking statement mode is simply a faster version of transaction mode rather than one that forbids multi-statement transactions