Connection pools are useful for managing a large number of temporary connections.
A "connection pool" is a system (external to Postgres) which manage connections, rather than PostgreSQL's native system. Supabase uses PgBouncer for connection pooling.
When a client makes a request, PgBouncer "allocates" and available connection to the client. When the client transaction or session is completed the connection is returned to the pool and is free to be used by another client.
Pool Mode determines how PgBouncer handles a connection.
When a new client connects, a connection is assigned to the client until it disconnects. Afterward, the connection is returned back to the pool.
All PostgreSQL features can be used with this option.
This is the suggested option for serverless functions. A connection is only assigned to the client for the duration of a transaction. Two consecutive transactions from the same client could be executed over two different connections.
Some session-based PostgreSQL features such as prepared statements are not available with this option. A comprehensive list of incompatible features can be found here.
This is the most granular option. Connections are returned to the pool after every statement. Transactions with multiple statements are not allowed. This is best used when
AUTOCOMMIT is in use.