Connection pooling with PgBouncer in Supabase - Step-by-Step Execution

Process Flow - Connection pooling with PgBouncer

Client sends DB request

↓

PgBouncer receives request

↓

Check for free DB connection

↓

Use free connection

↓

Send request to DB

↓

DB processes request

↓

Return result to client

↓

Connection returned to pool

PgBouncer manages a pool of database connections. It reuses free connections for new client requests or queues requests if none are free, improving efficiency.

Execution Sample

Supabase

Client -> PgBouncer -> DB
PgBouncer pools connections
Reuse or queue requests

This flow shows how PgBouncer handles client requests by reusing or queuing connections to the database.

Process Table

Step	Client Request	PgBouncer Action	DB Connection State	Result Sent to Client
1	Request 1	Assign free connection	1 connection in use	No
2	Request 2	Assign free connection	2 connections in use	No
3	Request 3	No free connection, queue request	2 connections in use, 1 queued	No
4	DB finishes Request 1	Return connection to pool	1 connection in use, 1 free	Send result for Request 1
5	Process queued Request 3	Assign freed connection	2 connections in use	No
6	DB finishes Request 2	Return connection to pool	1 connection in use, 1 free	Send result for Request 2
7	DB finishes Request 3	Return connection to pool	0 connections in use, 2 free	Send result for Request 3

💡 All requests processed, connections returned to pool

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6	After Step 7
DB Connections In Use	0	1	2	2	1	2	1	0
Queued Requests	0	0	0	1	1	0	0	0

Key Moments - 3 Insights

Why does PgBouncer queue a request instead of opening a new connection?

PgBouncer limits the number of active DB connections to avoid overload. When all connections are busy (see Step 3 in execution_table), it queues requests to wait for a free connection.

What happens when a DB connection finishes processing a request?

The connection is returned to the pool (Step 4), making it available for queued or new requests, improving resource reuse.

How does connection pooling improve performance?

By reusing existing connections instead of opening new ones for each request, PgBouncer reduces overhead and speeds up request handling (visible in Steps 1, 2, 5).

Visual Quiz - 3 Questions

Look at the execution_table, how many connections are in use after Step 2?

A 2

B 1

C 0

D 3

💡 Hint

Check the 'DB Connection State' column for Step 2 in execution_table

At which step does PgBouncer queue a request because no connections are free?

A Step 1

B Step 3

C Step 5

D Step 7

💡 Hint

Look for 'queue request' in the 'PgBouncer Action' column in execution_table

If PgBouncer had 3 connections instead of 2, what would happen at Step 3?

A Request 2 would be queued

B Request 3 would still be queued

C Request 3 would be assigned a free connection immediately

D No requests would be processed

💡 Hint

Refer to how connection limits affect queuing in execution_table and variable_tracker

Concept Snapshot

Connection pooling with PgBouncer:
- PgBouncer keeps a set number of DB connections open.
- Client requests reuse these connections if free.
- If none free, requests queue until a connection frees.
- This reduces DB load and speeds up handling.
- Pool size limits max concurrent DB connections.

Full Transcript

Connection pooling with PgBouncer works by managing a fixed number of database connections. When a client sends a request, PgBouncer checks if a free connection is available. If yes, it assigns the connection to the request and sends it to the database. If no connections are free, PgBouncer queues the request until a connection becomes available. When the database finishes processing a request, the connection returns to the pool for reuse. This process reduces the overhead of opening new connections and improves performance by reusing existing ones. The execution table shows how connections are assigned, queued, and freed step-by-step. Key moments include understanding why requests queue and how connections return to the pool. The visual quiz tests understanding of connection usage and queuing behavior. Overall, PgBouncer helps manage database load efficiently by pooling connections.

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