What’s the best way to connect to a Fly Postgres cluster from serverless edge functions (e.g. Cloudflare workers, deno functions etc)?
I imagine connecting directly to the database would give me issues with connections and performance at scale (not sure if you can even connect directly to a db with Cloudflare workers yet), and if it hits a read only node for write requests, it would need to make another request which could increase latency drastically for writes if the invoked edge function is far away from my databases.
Should I install pgbouncer and connect to that instead, or spin up a seperate app with something like postgrest and hit the postgrest endpoint, or just make my own layer in front of my db in a seperate app?
I’ve heard of Prisma Data Proxy, but I don’t want to add another uncertain cost to my project
You are right in that it can be a problem. For example you can’t connect directly to a Postgres database from a Cloudflare Worker. On the other hand, other serverless providers work differently, so you can connect directly from e.g AWS Lambda, or from Deno. Or from a Fly app, naturally! It all depends on how the compute works behind the scenes (e.g V8, Nodejs).
If you do want to connect using a Cloudflare Worker, you would indeed need some kind of proxy like postgrest. That would provide a http URL you could call. Since you can of course make http sub-requests within a Worker.
So that takes care of the can I connect. But then you face the other problem you mention: how many times can I connect. Since each connection adds overhead. Yet serverless can scale, near infinitely. Again, how you deal with that depends on the compute platform and how much load you expect (as it may not become a problem, at low load). For some, like AWS Lambda (and Fly!) the app instance will be re-used and so the number of connections will be fewer than you might think. However under severe load, like if you have like 1000 Lambdas running concurrently, you would end up with 1000 connections. AWS introduced a managed service called AWS RDS Proxy partly because of that issue, connecting to a database (AWS RDS) from Lambda. So you would need to replicate that proxy yourself. Which again means managing some kind of additional app/server. Whether a paid option like Prisma, or running your own. That would connect to the database.
For the latency question, that’s another thing to consider. You lose many of the benefits of edge/serverless compute if to do anything useful the request has to interact with a database far away, and the latency simply moves somewhere else. If you haven’t, it may be worth taking a look at Multi-region PostgreSQL · Fly Docs Fly can do nifty things with replaying requests that can massively help reduce latency for writes.