Connection pooling with PgBouncer in Supabase - Mini Project: Build & Apply

Connection pooling with PgBouncer

📖 Scenario: You are managing a Supabase project that uses PostgreSQL as its database. To improve database performance and handle many client connections efficiently, you want to set up connection pooling using PgBouncer. PgBouncer acts like a middleman that keeps a pool of database connections ready to use, so your app doesn't have to open a new connection every time.

🎯 Goal: Set up a basic PgBouncer configuration for your Supabase PostgreSQL database to enable connection pooling with sensible defaults.

📋 What You'll Learn

Create a PgBouncer configuration dictionary with required settings

Add a pool mode setting to control how connections are reused

Define the maximum number of client connections PgBouncer will accept

Complete the configuration by specifying the authentication type

💡 Why This Matters

🌍 Real World

Connection pooling with PgBouncer helps Supabase projects handle many users efficiently by reusing database connections instead of opening new ones each time.

💼 Career

Understanding PgBouncer configuration is valuable for cloud engineers and backend developers who optimize database performance and scalability.

1

Create the initial PgBouncer configuration dictionary

Create a dictionary called pgbouncer_config with these exact entries: 'listen_addr': '127.0.0.1', 'listen_port': 6432, and 'auth_file': '/etc/pgbouncer/userlist.txt'.

Supabase

# Your code here

Hint

Think of pgbouncer_config as a settings box. Start by adding the address PgBouncer listens on, the port number, and the path to the authentication file.

2

Add the pool mode setting

Add a new entry to the pgbouncer_config dictionary with key 'pool_mode' and value 'session'.

Supabase

pgbouncer_config = {
    'listen_addr': '127.0.0.1',
    'listen_port': 6432,
    'auth_file': '/etc/pgbouncer/userlist.txt'
}
# Your code here

Hint

The pool_mode controls how PgBouncer reuses connections. 'session' means one client connection uses one server connection until it disconnects.

3

Set the maximum client connections

Add a new entry to the pgbouncer_config dictionary with key 'max_client_conn' and value 100.

Supabase

pgbouncer_config = {
    'listen_addr': '127.0.0.1',
    'listen_port': 6432,
    'auth_file': '/etc/pgbouncer/userlist.txt',
    'pool_mode': 'session'
}
# Your code here

Hint

The max_client_conn limits how many client connections PgBouncer will accept at once. 100 is a common safe number.

4

Complete the configuration with authentication type

Add a new entry to the pgbouncer_config dictionary with key 'auth_type' and value 'md5'.

Supabase

pgbouncer_config = {
    'listen_addr': '127.0.0.1',
    'listen_port': 6432,
    'auth_file': '/etc/pgbouncer/userlist.txt',
    'pool_mode': 'session',
    'max_client_conn': 100
}
# Your code here

Hint

The auth_type tells PgBouncer how to check passwords. 'md5' is a secure and common choice.