Supavisor and Connection Terminology Explained

[TheOtherBrian1]

I'll be the first to admit that the official naming conventions in the Postgres community can be a bit confusing, so here's a basic rundown. It's a bit long, so feel free to just jump to the portion relevant to you:

Clients:

This is any server trying to connect to the pooler or database. This can be confusing because the term client in other scenarios also means users connecting through a frontend.

Client connections:

A single application server can create multiple connections. A client connection represents any connection established by a server to the pooler.

Direct/database connections:

This represents a direct connection to the database.

Your application servers can directly connect using the db connection string:

postgresql://postgres:[PASSWORD]@db.[PROJECT REF].supabase.co:5432/postgres

When connecting through the pooler, it will establish db/direct connections on behalf of clients. It then forwards/triages requests from client connections to db/direct connections.

max_connections:

Configured with the max_connections system variable, it represents how many direct/database connections postgres will tolerate. You can view your instance's settings by running this SQL:

SHOW max_connections;

To know more about configuring this value, check out monitoring connections troubleshooting guide.

Pool size:

The number of direct connections Supavisor is permitted to request from the database to manage each database/role/mode combination.

Transaction mode:

Transaction mode gives the pooler permission to share direct connections among multiple clients. It is used when the pooler connection string is listening on port 6543:

#example transaction mode string
postgres://postgres.obfwhevidiamwdwki:[YPASSWORD]@aws-0-ca-central-1.pooler.supabase.com:**6543**/postgres

Postgres connections use the Postgres Wire Protocol (PWP) rather than HTTP. PWP acts like a websocket: once a connection is made, it stays open and active until the client disconnects.

If too many connections are held by idle or greedy clients, other application servers won’t be able to connect to your database. Transaction mode helps avoid this problem by allowing clients to access the database connections only when they are running a query. This reduces the chances of hitting the maximum direct connection limit.

Session mode:

session mode restricts the pooler, forcing it to grant an underlying direct connection exclusively to a single client connection.

#example session mode string | uses port 5432
postgres://postgres.obfwhevidiamwdwki:[YPASSWORD]@aws-0-ca-central-1.pooler.supabase.com:**5432**/postgres

Session mode behaves nearly identically to a standard direct connection, which makes one wonder: what is the point?

One benefit is that if you need long-lasting connections in an IPv4-only environment and do not want to enable the IPv4 Add-On, session mode resolves this issue.

Another clear benefit is queueing. If all direct connections were taken, without the pooler, the n+1 client that tried to connect would be rejected. It would have to constantly poll the database to see if a slot became available. The pooler in session mode will queue a client for up to a minute, so it doesn't have to constantly poll while waiting for a slot.

[LakhveerChahal]

I have a doubt around Transaction mode.
Does it support manual commits? I am assuming that if two subsequent queries are not guaranteed to be executed on same db connection (in Transaction mode), then it won't be possible to support manual commits. But I'm not sure if my assumption is correct.

[TheOtherBrian1]

By manual commits, I'm assuming you mean starting a transaction with the BEGIN; keyword and then manually concluding it with either the COMMIT; or ROLLBACK;, e.g.

BEGIN;

-- query 1;

-- wait some time

-- query 2;

COMMIT;

Supavisor will support that behavior, but it's bad practice.

Explanation

Supavisor in transaction mode (port 6543) sits in front of your database as a proxy and acts as a connection load balancer. Clients (your app servers) create connections with it, thinking that it is actually the Postgres database. It maintains a pool of direct connections and forwards incoming queries to whichever one is available.

Although Supavisor may have 200+ client connections, if you set a pool size of 20, it'll only have 20 direct connections to manage all the incoming queries.

This isn't so bad. Connection sharing usually works because most connections sit idle. Say an app server runs a 20ms query, does some work, then calls an external API. The whole process can take 400ms, but the connection is idle for 380ms. Supavisor will notice the direct connection is idle and reassign it to a waiting client efficiently.

In my experience, developers use manual commits because they want to:

• Make an initial query
• Wait for some external IO operation to complete, such as waiting for a user to type a message or an API to return a result
• Use that response to send and validate another final query before committing.

While waiting for the commit or next query part of the transaction, the connection isn't doing any real work, but it's stuck in an idle-in-transaction state. The pooler cannot reassign it to waiting clients until a commit/rollback is finalized.

If you use manual commits, you’ll need to ensure your transactions stay fast. Otherwise, you risk having the entire pool size tied up by idle-in-transaction queries.

Also, in these scenarios, I recommend configuring Postgres's idle_in_transaction_session_timeout as an insurance policy against accidentally leaving transactions open.

ALTER ROLE postgres SET idle_in_transaction_session_timeout TO '3s';  --kills idle in transaction queries after 3s

[LakhveerChahal]

Thanks for such a detailed explanation. That answers my question perfectly.
I use manual commits to maintain the atomicity of an API request, in case it performs the DML operations in multiple query executions (they will be generally fast).

I had one more question though.

If an app server is a serverless function which establishes a connection with Supavisor when it first spins up and tries to hold that connection till it dies off (~15 mins in case of an AWS Lambda function), can Supavisor close that connection on its own if the connection remains idle?
If yes, is there any idle timeout parameter in Supavisor that governs that behaviour?

[LakhveerChahal]

@TheOtherBrian1 Can you please help me with the question above? I would really appreciate it.

[TheOtherBrian1]

@LakhveerChahal, sorry for the delayed response

If an app server is a serverless function which establishes a connection with Supavisor when it first spins up and tries to hold that connection till it dies off (~15 mins in case of an AWS Lambda function), can Supavisor close that connection on its own if the connection remains idle?

In transaction mode, Supavisor will maintain the client connection, but because no query has been issued, it's not going to hold up Supavisor's direct connections with the database, which is the primary concern.