Best way to connect Fly app to a Supabase database? Connection pooling on which side?

Python
1

Hi there!

I’m running a flask service with 3 machines in a fly App.
It must connect to a Supabase database.

On the flask side, I’m currently using psycopg2.pool.ThreadedConnectionPool(.) connecting to a Session pooler on the supabase side.
Is this correct? Or Am I doubling the pooling mechanism?

What would be the most robust way? (not necessarily the way with the lowest latency)

On the supabase side there are 3 options:

  • Direct connection
  • Transaction pooler (Dedicated Pooler)
  • Session pooler (Shared Pooler)

Thanks in advance!

4

The Supabase Connect dialogue box gives help for each of the three options. Which is closest to your case?

5

So, I would take out “Direct connection”, bcs “Ideal for applications with persistent, long-lived connections, such as those running on virtual machines or long-standing containers.” not my case.

Now between:

  • Transaction pooler “Ideal for stateless applications like serverless functions where each interaction with Postgres is brief and isolated.” Seems to be my case.
  • Session pooler “Only recommended as an alternative to Direct Connection, when connecting via an IPv4 network.”

I guess I should go for the “Transaction pooler”.
But then: Should I still use psycopg2.pool.ThreadedConnectionPool(.) on the flask side?
Or does the “Transaction pooler” already implement a pooler and therefore I shouldn’t use any pooler on the flask side?

6

I would probably go for Transaction Pooler and then monitor your app. If you don’t have a problem, then I’d say it’s not worth bothering about.

If you are sufficiently advanced in your enterprise that you are bumping into a scaling problem, you could create a clone of prod, run some load testing tools on it, and see if swapping your connection method makes a difference.

7

If you like, put your case here, and then folks who have made these decisions before can share what option they went with. I went with Direction Connection for a long-running PHP app sitting in Supervisor. It seems to be pretty happy and stable, but it is not under any sort of load yet.

8

So to complete what I wrote the initial post:

  • 3 machines running serverless Flask applications on fly.io
  • The Flask apps need to access a database (currently a managed postgres on Supabase).
  • Requests from clients are answered very fast (<100ms). (very small payloads, no heavy computations to run for each request, very fast SQL queries).
  • On @before_request() I would like to avoid a full connection process, to speed things up.

Questions:

  1. Should I use a psycopg2.pool.ThreadedConnectionPool on the Flask-side? No matter the type of connection on Supabase (“Direct connection”, “Transaction pooler”, “Session pooler”).
  2. Which connection type on Supabase should I use (among the 3 above).
1 Like