connection_pooling

28 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Database Max Connections allocation strategy

Pool size allocation should be based on concurrent peak connection usage, not generic percentages. For example, if only 80 connections were used in a week period with Database Max Connections set to 500, you could realistically allocate 420 connections (minus a reasonable buffer) to service more demand. The allocation depends on which Supabase products you use and their extent of usage.

Database client connections chart monitoring

For Teams and Enterprise plans, Advanced Telemetry charts are available in the Dashboard. The Database client connections chart displays historical connection data broken down by connection type: Postgres (direct connections from your application), PostgREST (connections from the PostgREST API layer), Reserved (administrative connections for Supabase services), Auth (connections from Supabase Auth service), Storage (connections from Supabase Storage service), and Other roles (miscellaneous database connections). The chart shows a reference line for your compute size's maximum connection limit.

Grafana Dashboard for monitoring connections

Supabase offers a Grafana Dashboard that records and visualizes over 200 project metrics, including connections. The Client Connections graph displays connections for both Supavisor and Postgres. Setup instructions are available in the metrics documentation.

Query live connections with pg_stat_activity

Use pg_stat_activity to observe live connections and determine if idle clients are hogging connection slots. The following SQL query retrieves all live connections: SELECT pg_stat_activity.pid as connection_id, ssl, datname as database, usename as connected_role, application_name, client_addr as IP, query, query_start, state, backend_start FROM pg_stat_ssl JOIN pg_stat_activity ON pg_stat_ssl.pid = pg_stat_activity.pid;

Supavisor pool size configuration

Supavisor pool size can be changed in the Connection pooling section of the Database Settings page. The general rule is: if heavily using the PostgREST database API, keep the pool size below 40% of the Database Max Connections. Otherwise, you can allocate up to 80% to the pool. This leaves adequate room for the Authentication server and other utilities.

pg_stat_activity column descriptions

The pg_stat_activity query returns these columns: connection_id (connection id), ssl (indicates if SSL is in use), database (name of the connected database, usually postgres), usename (role of the connected user), application_name (name of the connecting application), IP (IP address of the connecting server), query (last query executed by the connection), query_start (time when the last query was executed), state (querying state: active or idle), backend_start (timestamp of the connection's establishment).

Database role identification and sources

Different database roles indicate different sources: supabase_admin (used by Supabase for monitoring and by Realtime), authenticator (Data API/PostgREST), supabase_auth_admin (Auth), supabase_storage_admin (Storage), supabase_replication_admin (synchronizes Read Replicas), postgres (Supabase Dashboard and External Tools like Prisma, SQLAlchemy, PSQL), and custom roles defined by user (External Tools like Prisma, SQLAlchemy, PSQL).

Postgres.js transaction vs session pooler

Use the Transaction pooler when connecting from a platform with transient connections, such as serverless functions. Use the Session pooler when you have a long-lived connection.

Logical replication requires direct connection not connection pooler

When setting up logical replication to an external database, you must use a direct connection to your Supabase database, not the Connection Pooler connection.

Dashboard and Client queries have 60 second max timeout

Dashboard and Client library queries have a maximum configurable timeout of 60 seconds. For longer transactions, use Supavisor or direct connections.

Transaction mode (port 6543) recommended for serverless apps

For serverless functions like Supabase Edge, Vercel, and AWS Lambda, use transaction mode (port 6543) as it handles more connections than session mode or direct connections.

Supavisor transaction mode does not support prepared statements

Supavisor in transaction mode (port 6543) does not support prepared statements, which Prisma attempts to create automatically. To resolve this incompatibility, add pgbouncer=true to the Prisma connection string to disable prepared statements.

Prisma default connection pool size calculation

By default, Prisma creates num_cpus * 2 + 1 connections. Significantly increasing the connection_limit past this default can cause server strain and overwhelm the server hosting Prisma.

Adjust Prisma pool_timeout parameter

When Prisma is unable to allocate connections fast enough from the connection pool, increase the pool_timeout parameter in the Prisma configuration to give the pooler more time to allocate connections.

Max client connections in session mode (port 5432)

In session mode (port 5432), the max amount of clients is restricted to the Pool Size value configured in Database Settings. This limit applies per unique database-role+database combination, even if the pooler can theoretically handle more connections.

Prisma connection_limit for serverless setups

For serverless setups, begin with connection_limit=1, as serverless setups typically do not need many connections. Increase cautiously if needed to avoid reaching max connections.

Direct database connections for trusted servers and tools

For direct Postgres connections from trusted servers, workers, or tools, use a connection string from Supabase. Keep database credentials secret and use the right connection method for your environment. The Data API can be disabled if your app only uses direct connections.

Max client connections reached error in Supavisor

The 'Max client connections reached' error occurs when the number of connections to Supavisor exceeds the allowed limit of your compute add-on. This error indicates that you have hit the maximum concurrent connections permitted by your current subscription tier.

Supavisor connection failed eaddrnotavail error

The error 'Connection failed {:error, :eaddrnotavail} to db.xxx.supabase.co:5432' indicates that Supavisor cannot connect to the customer database. This is usually caused when the target database is unable to respond.

Supavisor connection failed nxdomain error

The error 'Connection failed {:error, :nxdomain} to db.xxx.supabase.co:5432' indicates that Supavisor cannot connect to the customer database. This is usually caused when the target database is unable to respond.

Supavisor connection closed during authentication

The error 'Connection closed when state was authentication' occurs when either the database does not exist or the user does not have the correct credentials for authentication.

Supavisor worker_not_found subscribe error

The error 'Subscribe error: {:error, :worker_not_found}' is emitted when a client tries to connect to the database but Supavisor does not have the necessary information to route the connection. This can occur because it takes time for project information to propagate to Supavisor. The resolution is to try reconnecting to the database.

Supavisor erpc timeout between nodes

The error 'Subscribe error: {:error, {:badrpc, {:error, {:erpc, :timeout}}}}' is a timeout error that occurs when communication between different Supavisor nodes takes longer than expected. The resolution is to try reconnecting to the database.

Supavisor client terminates before database connection complete

The error 'Terminating with reason :client_termination when state was :busy' occurs when a client terminates the connection before the connection with the database is completed.

Supavisor GSSAPI negotiation error with gssencmode

The error 'Error: received invalid response to GSSAPI negotiation: S' occurs due to the `gssencmode` parameter not being set to disabled. This needs to be corrected to resolve the error.

Where to find Supavisor logs in Supabase Dashboard

Supavisor logs are available under 'Pooler Logs' in the Supabase Dashboard at the path: dashboard/project/_/logs/pooler-logs.

Postgres password special character encoding

Special symbols in Postgres passwords must be percent-encoded when used in a connection string. For example, a password with 'p=word' becomes 'p%3Dword' in the connection string like postgresql://postgres.projectref:••••••••@aws-0-us-east-1.pooler.supabase.com:6543/postgres

Supavisor connection pooling for Prisma serverless

For serverless or auto-scaling environments, Prisma requires two connection strings. Use Supavisor Transaction Mode (port 6543) with `pgbouncer=true` parameter for DATABASE_URL in the application. Use Supavisor Session Mode (port 5432) or direct connection for DIRECT_URL in Prisma migrations. In IPv6 environments or with IPv4 Add-On, a direct connection string can be used instead of Supavisor Session mode.

Give your agent this brain