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.

Practice

1. What is the main purpose of using PgBouncer with a Supabase database?

easy

A. To increase the size of the database storage

B. To encrypt data between the app and database

C. To backup the database automatically

D. To reuse database connections and reduce load on the database

Solution

1. Step 1: Understand PgBouncer's role

   PgBouncer is a tool that manages and reuses database connections to avoid opening new ones repeatedly.

2. Step 2: Identify the benefit

   Reusing connections reduces the load on the database and improves app speed by avoiding connection overhead.

3. Final Answer:

   To reuse database connections and reduce load on the database -> Option D

4. Quick Check:

   Connection reuse = reduce load [OK]

Hint: PgBouncer saves time by reusing connections [OK]

Common Mistakes:

• Thinking PgBouncer encrypts data
• Confusing PgBouncer with backup tools
• Assuming it increases storage size

2. Which of the following is the correct way to specify the pooling mode in PgBouncer's configuration file?

easy

A. pool_mode = transaction

B. pooling-mode: session

C. poolMode = statement

D. pool_mode = connect

Solution

1. Step 1: Recall PgBouncer config syntax

   PgBouncer uses key = value format, with underscores in keys.

2. Step 2: Identify correct pooling mode syntax

   The correct key is pool_mode and valid values include transaction, session, and statement.

3. Final Answer:

   pool_mode = transaction -> Option A

4. Quick Check:

   Correct key and value format = pool_mode = transaction [OK]

Hint: Look for key = value with underscores [OK]

Common Mistakes:

• Using hyphens instead of underscores
• Capitalizing keys incorrectly
• Using invalid pooling mode names

3. Given this PgBouncer config snippet:

pool_mode = transaction
max_client_conn = 100
default_pool_size = 20

What happens when 50 clients connect simultaneously and each runs a short transaction?

medium

A. PgBouncer queues all clients until connections free up, causing delays

B. PgBouncer rejects connections after 20 clients because of pool size

C. PgBouncer allows all 50 clients to share 20 server connections efficiently

D. Each client gets a dedicated server connection, so 50 connections open

Solution

1. Step 1: Understand transaction pooling mode

   In transaction mode, PgBouncer assigns server connections only during active transactions, allowing reuse.

2. Step 2: Analyze connection limits

   default_pool_size = 20 means max 20 server connections per database/user pool, but clients can exceed this by sharing connections.

3. Final Answer:

   PgBouncer allows all 50 clients to share 20 server connections efficiently -> Option C

4. Quick Check:

   Transaction pooling shares connections = PgBouncer allows all 50 clients to share 20 server connections efficiently [OK]

Hint: Transaction pooling shares connections among clients [OK]

Common Mistakes:

• Assuming one client = one server connection
• Thinking pool size limits client connections
• Confusing session pooling with transaction pooling

4. You configured PgBouncer with pool_mode = transaction but your app experiences errors with prepared statements. What is the likely cause?

medium

A. PgBouncer does not support prepared statements in any mode

B. Transaction pooling reuses connections between clients, causing prepared statement conflicts

C. Transaction pooling is required for prepared statements to work

D. The max_client_conn setting is too low

Solution

1. Step 1: Understand transaction pooling behavior

   In transaction mode, PgBouncer assigns server connections only during transactions and reuses them for other clients after reset.

2. Step 2: Identify cause of errors

   Prepared statements fail because the connection state (including prepares) is reset (DISCARD ALL) between transactions/clients.

3. Final Answer:

   Transaction pooling reuses connections between clients, causing prepared statement conflicts -> Option B

4. Quick Check:

   Transaction pooling = resets between transactions [OK]

Hint: Transaction pooling reuses connections, watch prepared statement issues [OK]

Common Mistakes:

• Believing PgBouncer supports prepared statements in transaction mode
• Confusing transaction and session pooling effects
• Blaming max_client_conn for statement errors

5. You want to optimize a Supabase app with many short queries and avoid connection overhead. Which PgBouncer pooling mode should you choose and why?

hard

A. Transaction pooling, because it reuses connections only during transactions for best performance

B. Session pooling, because it keeps connections open for each client session

C. Statement pooling, because it assigns a connection per SQL statement

D. No pooling, to avoid any connection sharing issues

Solution

1. Step 1: Consider app query pattern

   Many short queries mean frequent connections and disconnections, so reusing connections efficiently is key.

2. Step 2: Evaluate pooling modes

   Transaction pooling assigns connections only during transactions, allowing reuse and reducing overhead without tying connections to sessions.

3. Step 3: Compare other modes

   Session pooling holds connections longer, increasing resource use; statement pooling is too strict and can cause errors; no pooling wastes resources.

4. Final Answer:

   Transaction pooling, because it reuses connections only during transactions for best performance -> Option A

5. Quick Check:

   Short queries + reuse = transaction pooling [OK]

Hint: Use transaction pooling for many short queries [OK]

Common Mistakes:

• Choosing session pooling for short queries
• Thinking statement pooling is best for performance
• Avoiding pooling altogether