Show HN: Supavisor – a Postgres connection pooler written in Elixir (github.com)
this is a postgres connection pooler. it’s similar to pgbouncer, but built with Elixir and specifically designed for multi-tenancy.
it’s still under development, but it’s at a stage where we can gather a feedback from the community and you can try it yourself. we aren’t using this in production yet, but aiming to deploy it for a subset of databases in the next 2 months.
We have the following benchmarks (details in the readme):
- Elixir Cluster maintaining 400 connections to a single Postgres database
- 1_000_000 clients connecting to the Elixir cluster
- Sending 20_000 transactions per second
- Consuming 7.8G RAM and ~50% CPU on a 64vCPU machine
we will place this in front of all supabase databases. It will eventually be able to handle multiple types of connections: traditional TCP connections, and HTTP connections for developers who are connecting to Postgres in serverless environments using Prisma, Kysely, Drizzle, etc
the proxy will serve as a connection buffer while we scale databases: scaling up compute with zero-downtime, and for scale-to-zero - triggering a server restart when a connection is initiated
finally, i want to shout out to Jose and the Dashbit/elixir team. They were extremely helpful with the design & architecture. they have been valuable partners, and elixir continues to be an amazing language for tools like this and our Realtime server.
52 comments
[ 2.9 ms ] story [ 84.9 ms ] threadwe have a library of assets that our design team use. I can ask if they can open source it if you would find it useful
We made the figma file 2 days ago - so we’re still figuring out the wider diagram system.
But we’re planning on publishing it in the figma community as it might be useful for others.
I’ll update on here once we do.
An interesting bit... the syn library helps us guarantee there is only one connection pool alive on the cluster at a time and gives us callbacks to resolve the conflict if two get spun up.
https://github.com/ostinelli/syn
Give this a watch, "The Soul of Erlang and Elixir". Covers a lot of what's nice about the underlying platform.
https://youtu.be/JvBT4XBdoUE
Erlang process monitoring makes it very easy to handle client disconnects.
And Elixir makes using Erlang a bit more approachable.
however, i think that you may have a lot left on the table in terms of optimizations that can be made. these numbers seem far under the performance of what i know beam to be capable.
PgBouncer was on the same instance I’m assuming?
everything but postgres runs on one machine. we use haproxy to load balance replica connections across different pgbouncer backends for replicas of the same db.
If you are interested in connecting over this it would be great to connect… chase@supabase.com.
We want to do load balancing too. Makes so much sense to do load balancing in the proxy.
so pgbouncer connects to cloud sql proxy, which does the connection pooling concern.
We made sure to do local benchmarks along the way and there wasn't a meaningful difference.
Really the discussion was around doing as little message passing as possible to not copy data.
See:
https://github.com/supabase/supavisor/pull/4
https://github.com/supabase/supavisor/issues/7
So there are definite gains there but if we want to do things across a cluster we're going to be copying data. The decision at the time was the benefits of clustering outweighed the perf gains considering we were basically already as fast as PgBouncer. This could change.
We've found that we can double our throughput by using prepared statements, but can't do so because we use pgbouncer in transaction pooling mode.
Our newer systems (on Rust) bypass pgbouncer in favor of an in process pool so we get to take advantage of prepared statements there, but our legacy systems would benefit a lot from it.
e.g. https://blog.bullgare.com/2019/06/pgbouncer-and-prepared-sta...
Elixir's Ecto can (and that Go example) so if you use Elixir (or Go) you can use prepared statements with PgBouncer (or Supavisor!) in transaction mode.
The main reason prepared statements improves throughout is that it caches query plans.
As I understand it, binary_parameters does not do that. Each query is parsed and evaluated individually (perhaps equivalent to unnamed prepared statements) and so nothing is saved when you execute the same query again.
Maybe binary_parameters is a different thing. Need to look into it more but this is a good one to add to the docs. And maybe officially support named prepared statements natively if lots of clients don't support unnamed prepared statements but definitely seems like we'd have to do much more accounting.
Anyways, I'm probably wrong here but it looks like I'm about to figure this out now :D
So if Postgres is doing query plan caching by session then caches would be built up as that query hits other db connections. In theory, this should be better.
A prepared statement can be executed with either a generic plan or a custom plan. A generic plan is the same across all executions, while a custom plan is generated for a specific execution using the parameter values given in that call. Use of a generic plan avoids planning overhead, but in some situations a custom plan will be much more efficient to execute because the planner can make use of knowledge of the parameter values. (Of course, if the prepared statement has no parameters, then this is moot and a generic plan is always used.)
By default (that is, when plan_cache_mode is set to auto), the server will automatically choose whether to use a generic or custom plan for a prepared statement that has parameters. The current rule for this is that the first five executions are done with custom plans and the average estimated cost of those plans is calculated. Then a generic plan is created and its estimated cost is compared to the average custom-plan cost. Subsequent executions use the generic plan if its cost is not so much higher than the average custom-plan cost as to make repeated replanning seem preferable.
https://www.postgresql.org/docs/current/sql-prepare.html
"If successfully created, a named prepared-statement object lasts till the end of the current session, unless explicitly destroyed. An unnamed prepared statement lasts only until the next Parse statement specifying the unnamed statement as destination is issued."
https://www.postgresql.org/docs/current/protocol-flow.html#P...
Conclusion: I still want named prepared statements to take advantage of generic plans.
Ah `binary_parameters` is just this Go lib thing. Ecto just sends unnamed prepared statements where (TiL) query plans are not cached.
It would be nice to have a wider scope for prepared statements.
I know it's more complicated, but I imagine pgbouncer could detect "prepare foo as..." and "execute foo(...)" and track whether a given session has had "foo" prepared. If not, rerun the "prepare foo as ..." on a session if "execute foo(...)" is received. That's essentially what the in-process version does.
There would be some complications around conflicts for a given named prepared statement, but that could be solved with some requirements on clients (e.g. use source hash in prepared statement names).
Should we take this discussion into a GitHub issue?
FWIW, we are running a modified patch.
Is there a specific HTTP API to target for this or something brand new that will cover the desired use cases?
Naively, POSTing a query string and params would go a long ways I think.
If you're using Elixir there's really no need for separate connection pool until you have LOTS of nodes.
Question about telemetry.
These are the metrics emitted by Ecto:
- :idle_time - the time the connection spent waiting before being checked out for the query
- :queue_time - the time spent waiting to check out a database connection - :query_time - the time spent executing the query
- :decode_time - the time spent decoding the data received from the database
- :total_time - the sum of (queue_time, query_time, and decode_time)
To what extent are these metrics distorted by use of a Supavisor connection pooler? How could Supavisor compensate for that?
Might be a little too close to "supervisor" though.
> To what extent are these metrics distorted by use of a Supavisor connection pooler? How could Supavisor compensate for that?
You mean if you have an Elixir app and using Supavisor for connection pooling? They would be affected as much as they would be if you used PgBouncer.
For the metrics Supavisor is exposing, it has those Ecto metrics for the interactions with the management database.
Supavisor doesn't use any libs for handling the wire protocol. It handles the wire protocol directly. We are exposing some similar ones: https://github.com/supabase/supavisor/wiki/Metrics
Can Supavisor route Reads across multiple nodes and Writes to just the Primary like pgpool does? If so, how, is it documented, share a link?
Thanks in advance.