Connection pooling with PgBouncer in Supabase

Introduction

Connection pooling helps manage many database requests efficiently by reusing a few connections. PgBouncer is a tool that does this for PostgreSQL databases.

When your app has many users connecting to the database at the same time.

When you want to reduce the time it takes to open new database connections.

When your database has a limit on how many connections it can handle.

When you want to improve app performance by managing database load better.

Syntax

Supabase

[databases]
mydb = host=localhost port=5432 dbname=mydb user=myuser password=mypass

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = session
max_client_conn = 100
default_pool_size = 20

The [databases] section defines which databases PgBouncer will connect to.

The [pgbouncer] section sets how PgBouncer listens and manages connections.

Examples

Basic setup using session pooling mode, where each client connection gets a dedicated server connection during the session.

Supabase

[databases]
mydb = host=localhost port=5432 dbname=mydb user=myuser password=mypass

[pgbouncer]
pool_mode = session

Transaction pooling mode, where server connections are assigned only during a transaction, improving efficiency for short queries.

Supabase

[databases]
mydb = host=localhost port=5432 dbname=mydb user=myuser password=mypass

[pgbouncer]
pool_mode = transaction

Statement pooling mode, where each statement uses a server connection, best for very short queries but with some limitations.

Supabase

[databases]
mydb = host=localhost port=5432 dbname=mydb user=myuser password=mypass

[pgbouncer]
pool_mode = statement

Sample Program

This is a complete PgBouncer configuration for a Supabase PostgreSQL database. It uses transaction pooling to efficiently manage connections. It listens on all network interfaces on port 6432 and logs connections and disconnections.

Supabase

[databases]
supabase = host=db.supabase.co port=5432 dbname=supabase user=supabase_user password=secret_password

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 100
default_pool_size = 20
log_connections = 1
log_disconnections = 1

Important Notes

Always secure your PgBouncer auth_file with proper permissions to protect passwords.

Choose pool_mode based on your app's query patterns for best performance.

Monitor PgBouncer logs to understand connection usage and troubleshoot issues.

Summary

PgBouncer helps reuse database connections to improve app speed and reduce load.

Configure PgBouncer with your database details and choose a pooling mode.

Use transaction pooling for most apps to balance performance and compatibility.