Connection pooling with PgBouncer in Supabase - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

How does PgBouncer improve database connection handling?

PgBouncer is used as a connection pooler for PostgreSQL databases. What is the main way PgBouncer improves database connection handling?

A It encrypts all database traffic to improve security between clients and the database.

B It reduces the number of active database connections by reusing existing connections for multiple client requests.

C It automatically scales the database server vertically to handle more connections.

D It caches query results to speed up repeated queries without hitting the database.

💡 Hint

Think about how connection pooling helps manage limited database connections efficiently.

PgBouncer maintains a pool of open connections to the database and reuses them for multiple client requests. This reduces overhead and avoids opening too many connections at once.

❓ Configuration

intermediate

Identify the correct PgBouncer pool_mode setting for transaction pooling

PgBouncer supports different pooling modes. Which pool_mode value enables transaction pooling, where a server connection is assigned to a client only during a transaction?

A "transaction"

B "statement"

C "connection"

D "session"

💡 Hint

Transaction pooling assigns connections only during transactions, not for the whole session.

The transaction pool_mode assigns a server connection to a client only during the transaction, freeing it afterward for others.

❓ Architecture

advanced

What happens if PgBouncer is configured with pool_mode=statement and a client sends multiple statements in one transaction?

Consider PgBouncer configured with pool_mode=statement. A client sends multiple SQL statements inside a single transaction block. What is the expected behavior?

A PgBouncer assigns a new server connection for each statement, potentially breaking transaction integrity.

B PgBouncer holds one server connection for the entire transaction, ensuring all statements run together.

C PgBouncer queues statements and executes them sequentially on a single connection after the transaction ends.

D PgBouncer rejects the transaction because statement pooling does not support transactions.

💡 Hint

Think about how statement pooling handles connections per statement, not per transaction.

In statement pooling mode, PgBouncer assigns a server connection per statement, so multiple statements in a transaction may run on different connections, breaking transaction atomicity.

❓ security

advanced

Which PgBouncer authentication method securely stores user passwords hashed with SCRAM-SHA-256?

PgBouncer supports multiple authentication methods. Which method securely stores user passwords hashed with SCRAM-SHA-256 for authentication?

A auth_user with MD5 hashed passwords

B auth_file with plain text passwords

C auth_query with SCRAM-SHA-256 hashed passwords in the database

D auth_hba with trust authentication

💡 Hint

SCRAM-SHA-256 hashes are stored in the database and require querying for authentication.

Using auth_query, PgBouncer queries the database to verify SCRAM-SHA-256 hashed passwords securely stored there.

✅ Best Practice

expert

What is the recommended PgBouncer pool_mode for a high-concurrency web application with many short queries?

You manage a high-concurrency web app with many short queries per user session. Which PgBouncer pool_mode setting best balances performance and transaction integrity?

A "session" to keep connections assigned for the whole user session

B "none" to disable pooling and connect directly to the database

C "statement" to assign connections per statement for maximum reuse

D "transaction" to assign connections only during transactions, freeing them between transactions

💡 Hint

Consider how to maximize connection reuse without breaking transaction boundaries.

Transaction pooling assigns connections only during transactions, allowing high reuse while preserving transaction integrity, ideal for many short queries.