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.