Supavisor FAQ

Supavisor runs the shared pooler. This FAQ covers how Supavisor behaves. For how pooling works, the dedicated pooler, client connections, and connection limits, see Connection pooling and limits. For connection strings and which one to use, see Connect to your database.

Supavisor: Transaction mode vs. Session mode?

When a client forms a direct connection with Postgres, it usually makes a few queries but may not use the connection the entire time. In transaction mode, a client is allowed to make a single query before being sent back to the figurative “waiting room”. This prevents greedy or sedentary clients from hoarding connections. In most cases, this increases query throughput and is optimal.

In session mode, once the pooler assigns a direct connection, it stays with that client until voluntarily surrendered.

This behavior mirrors a direct connection, allowing greedy clients to monopolize the pool. This raises a question: what is the purpose of session mode?

Depending on your application’s configurations, having the pooler manage a queue of patient clients is preferable to the alternative of constantly polling the database to check for an available connection. Session mode can queue clients for up to a minute. If this isn’t particularly relevant to your application design, then the primary benefit is that it is IPv4 compatible. Also, unlike transaction mode, it supports prepared statements.

In the context of Supavisor, what does “pool size” mean?

“Pool size” refers to the maximum number of direct connections the pooler can maintain per unique user, database, and mode combination. You can adjust it in the project connect page to strike a balance between efficient resource utilization and accommodating peak traffic.

What is the “user+db+mode” combination?

Postgres is not a database. It is a Relational Database Management System (RDMS). Within it, you can spawn Postgres databases. In Supabase, it is a common pattern to use the default database called postgres, but you could create more:

1
CREATE DATABASE postgres;
2
CREATE DATABASE another_database;

Similarly, a database can have many database users, but most people rely on the default user postgres.

1
CREATE USER postgres WITH PASSWORD 'super-secret-password';
2
CREATE USER some_new_user WITH PASSWORD 'password';

The modes are transaction (port 6543) and session (port 5432) mode.

The “user+database+mode” combinations are formed from the above variables and are used within the connection string:

1
postgres://[USER].shfmmplnqscentnakbkl:[password]@aws-0-ca-central-1.pooler.supabase.com:[MODE]/[DATABASE]

When a distinct combination connects to your database, a new direct connection pool will be created. That means if you have two combinations connecting and the “Pool Size” is set to 120, each combination will have permission to form 120 connections. This can become problematic if collectively they exhaust all available direct connections.

Does Supavisor immediately establish the max pool size?

No, it doesn’t. See why:

• In transaction mode, the pooler will only add connections if there are not enough connections to service all pending queries simultaneously. If 10 clients connect, but a single direct connection can comfortably accommodate them all, the pooler will not create more.
• If necessary, it can create as many connections to prevent queries from pending up to the limit specified by the “Pool Size”.
• In session mode, it will immediately create a new a direct connection for a new client unless the “Pool Size” is reached
• In transaction mode, once a direct connection is established, the pooler will keep it available for reuse for another client. However, if the connection remains unused for 5 minutes, the pooler will close it to free up database resources. In session mode, the connection will be immediately closed.
• If a client is connected to the pooler, then at least 1 hot connection will be sustained, even if no client needs it.

How to change pool size

In the Dashboard’s Database Settings, you can configure Supavisor’s “Pool Size”:

You can also change the pool size for PostgREST’s (DB API) internal pooler at the bottom of the application settings.

Do all services on Supabase use Supavisor?

Supabase Storage uses Supavisor internally. The other servers that communicate with Postgres (PostgREST, Realtime, and Auth) all rely on internal application poolers.

Supavisor is primarily intended for users who do not want to rely on the Supabase Client libraries and instead prefer to work with external ORMs, such as Prisma, Drizzle, and Psycopg.

Whether to change Supavisor’s pool size?

In an ideal scenario, Postgres would support an unlimited number of direct connections, but there’s a limit to how many it can handle. If Supavisor uses most of the available connections, you risk depriving other servers, such as Auth, from accessing your database. Still, as much as possible, you want to give your pooler freedom to grow its pool as needed to service demand.

It’s important to note that the Storage server also uses Supavisor as a unique “user+db+mode” combination, so the pool size you set for your general application will apply to it, too.

As a rule of thumb, if you’re using the DB REST API or multiple app-based “user+db+mode” combinations, try to keep the pooler’s usage under 40% of available connections. Otherwise, you can cautiously increase usage to around 80%. These percentages are flexible and depend on your application’s usage and setup. Monitor connection usage to determine the optimal allocation without depriving other servers of necessary connections.

Can Supavisor really support a million connections?

It depends.

In the article “Supavisor: Scaling Postgres to 1 Million Connections” we gave Supavisor the capacity to connect to a million clients simultaneously. The pooler triaged the clients’ queries to 400 database connections.

What is important is that the database processing these queries had 64vCPUs and 256GB of memory. It could process the queries fast enough to ensure that there was never a noticeable bottleneck. The clients never waited long enough to timeout. If the clients were asking to run inefficient queries, like the one below, a severe backlog of pending client queries would have formed:

1
-- do nothing for 60 seconds
2
select pg_sleep(60);

A backlog could also happen if there are not enough direct connections available. In our setup, 400 connections were enough to handle a million clients, ensuring a smooth flow of requests. But if we had only 1 direct connection, the queue would’ve moved too slowly and clients would expire their requests.

Scaling to this level only works when the pooler is operating in transaction mode. In Session mode, hypothetically, the 400 clients that first accessed the direct connections could keep them, even if they are no longer executing queries. The pending 999,600 clients waiting for a turn would then be starved of direct connections.

Related topics

• Supavisor documentation
• Supavisor and connection terminology explained
• Monitor Supavisor Postgres connections
• How to change max database connections
• Updating connection pool settings in the Dashboard

Go to the GitHub discussion