Using SQLAlchemy with Supabase

[TheOtherBrian1]

Deploying to auto-scaling servers:

If you are deploying to:

• edge functions
• serverless functions
• horizontally auto-scaling deployments

It is recommended that you connect with the pooler in transaction mode (port 6543), which can be found in the Database Settings:

# Example transaction mode string:
postgres://[db-user].[project-ref]:[db-password]@aws-0-[aws-region].pooler.supabase.com:6543

When using transaction mode, you should use the NullPool setting:

from sqlalchemy.pool import NullPool

con = sqlalchemy.create_engine(url, client_encoding='utf8', poolclass=NullPool)

When relying on Supavisor, it's important to pick an adequate pool size. This guide can walk you through the process:

• https://github.com/orgs/supabase/discussions/21566

Deploying to stationary servers

For stationary servers, such as VMs and long-running containers, it is recommended to use your direct connection string, which can be found in the Database Settings

# Example DB string:
postgresql://postgres:[PASSWORD]@db.[PROJECT REF].supabase.co:5432/postgres

The connection maps to an IPv6 address, and cannot operate in an IPv4 environment.

Checking IPv6 Support:

The majority of services are IPv6 compatible. However, there are a few prominent services that only accept IPv4 connections:

• Retool
• Vercel
• GitHub Actions
• Render

If you're still unsure if your network supports IPv6, you can run this cURL command on your deployment server:

curl -6 https://ifconfig.co/ip

If the command returns an IPv6 address, the network is IPv6 compatible.

If your deployment environment is not IPv6 compatible, then consider:

• Using the Supavisor pooler in session (port 5432)
• Enabling the IPv4 Add-On if you're on a pro or above plan

Choosing an internal pool size

Key Pool Settings:

• pool_size: This sets the maximum number of permanent connections in the pool. SQLAlchemy will create connections as needed up to this limit.
  max_overflow: Allows creating additional connections beyond pool_size for temporary bursts in demand. These temporary connections close after use.

# Example configurations
engine = create_engine(
    "postgresql+psycopg2://me@localhost/mydb", pool_size=20, max_overflow=15
)

As a rule of thumb, if you're using the Supabase Database REST Client, try to limit the connections used by your deployment to 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.

How to monitor live connections

Connection usage can be monitored with a Supabase Grafana Dashboard. It provides realtime visibility of over 200 database metrics, such as graphs of CPU, EBS, and active direct/pooler connections. It can be extremely useful for monitoring and debugging instances.

You can check our GitHub repo for setup instructions for local deployments or free cloud deployments on Fly.io. For a complete explainer on connection monitoring, you can check out this guide

[JichaoS]

Could you clarify on this statement?

When using an external pooler, you should use the NullPool setting:

Why should we use the nullpool setting? I'd imagine having some client side pooling can help with limiting connection counts.

[TheOtherBrian1]

"Why should we use the nullpool setting?"

Serverside poolers can retire unused connections after a period of idleness. If these events are not acknowledged by SQLAlchemy, it may throw a server closed the connection unexpectedly error when it tries to query. Hypothetically, instead of NullPool, you could try setting pre_ping to True.

This article about PgBouncer, which Supavisor is modeled on, helps explain the rationale and best practices.

"I'd imagine having some client side pooling can help with limiting connection counts."

NullPool does not have a configuration to prevent the total amount of requests it will send, but Supavisor is able to queue pending queries. Because SQLAlchemy will destroy these connections, the risk of overwhelming the pooler is extremely low.

I recommend setting up connection monitoring tools, so you can have better visibility over your connection demands. I wrote a guide that can walk you through the process.

[ManuelAngel99]

Hi @TheOtherBrian1

I have some questions about the recommended practices for sqlalchemy with supabase for a typical fastapi docker backend+autoscaler setup:

Context:
We use a kubernetes cluster to deploy our backend with autoscaling enabled, at any given point in time, depending on the demand that we are experiencing, there may be between 5 (normal) and 50 (extreme) total instances running depending on the application load.

Since the number of running instances can be very high and every instance has several uvicorn workers, connecting directly to the database seems like a bad idea in this scenario, especially if we consider that there are background workers+postgREST clients connecting to postgres as well. We also need to be able to connect to the server using ipv4. To avoid reaching the connection limit, we configured supavisor to operate in transaction mode and followed the guidelines above on how to set up sqlalchemy for this case.

However, using the nullPool results in poor query performance compared to the default QueuePool. Running some dummy benchmarks I have measured that for the same benchmark query (after warmup), the average execution time is about 200ms when using the default pooler vs 800ms with NullPool. This latency discrepanct may be exaggerated by the fact that I am in Europe and the region for our supabase depolyment is us-east-1, but nonetheless it should be considered.

How would you procede in this case? As you mentioned in your response above, I understand that using the QueuePool can be dangerous as the connection can be retired after some period of idleness, but taking a 4x latency increase is also undesirable. Could using pre_ping=True and pool_recycle=60 be the solution for the problem? Could you give us more insights on how does supavisor retire unused connections?

Thanks in advance!

[ahmed-3li]

Yes, I am experiencing the same poor performance with NullPool. The query execution time has increased by 4 times.

[TheOtherBrian1]

@ManuelAngel99

Could using pre_ping=True and pool_recycle=60 be the solution for the problem? Could you give us more insights on how does supavisor retire unused connections?

Hypothetically, that should work. Supavisor in transaction mode closes unused direct connections after 5 minutes, so pool_recycle will likely function if set to 300.

"This latency discrepanct may be exaggerated by the fact that I am in Europe and the region for our supabase depolyment is us-east-1"

This is just an aside, but read replicas are now available in the Dashboard. As long as you're operating on a small instance or above, you could deploy one to the U.S.

[ManuelAngel99]

@TheOtherBrian1 Great! I'll setup a queue pool with a 180 pool recycle then. As for the NullPool, do you have any idea of why it increases the latency so much? @ahmed-3li has also reported a 4x latency increase in query processing time

[TheOtherBrian1]

@ManuelAngel99 The overhead of recreating the connection could possibly explain why NullPool increased latency

[ManuelAngel99]

Hi @TheOtherBrian1,

I understand where you are coming from, but the increase in latency is significant.

For reference, I benchmarked the same query using SQLAlchemy (with and without NullPool) and PostgREST. The query:

SELECT * FROM user_organizations WHERE user_id = 123456789;

Here are the results:

Backend	Latency
PostgREST	0.15s
SQLAlchemy (with pool recycle, once warmed up)	0.41s
SQLAlchemy (NullPool)	3.97s

As you can see, the latency that we get when using Null Pool makes supavisor unusable. As your mentioned, issue seems to be related to the overhead of recreating the connection. We get similar latencies on the first query we execute when using sqlalchemy without NullPool.