12 ms·
Show HN: Supavisor – a Postgres connection pooler written in Elixir
hey hn, supabase ceo here
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
supavisor can be run as a cluster or a single node/binary. It’s handling 90%+ of the throughput of pgbouncer on a local machine (running pgbench)
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.
- techn00 4y agoWhat tool did you use for the diagram?
- kiwicopple 4y agoit's custom, built with figma. we 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
- techn00 4y agothat would be awesome, thank you
- mildmustard 4y agoSupabase team member here. 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.
- krainboltgreene 4y agoI am so glad this exists, great work.
- chasers 4y agoThanks!
- deleted 4y ago[deleted]
- dijonmusters 4y agoThis is so cool! Have been needing something like this for a long time! The Supabase team smashed it on this one!
- chasers 4y agoContributor here. Happy to answer any questions. 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 https://github.com/ostinelli/syn
- deleted 4y ago[deleted]
- egorr 4y agoHi, i was running these million connections benchmarks, happy to answer if you have any questions regarding this:) In short, that was pretty easy to do, only things we had to tweak to make it work were some elixir and linux limits
- abc3sts 4y agoI've had a great time contributing to this project, using Elixir to create a multi-tenant connection pooler. It's still under development, but we're excited to gather feedback and improve it. Elixir has been an awesome language to work with — let us know what you think!
- alexdanilowicz 4y agoI’m not too familiar with Elixir. Why did you choose it and why do you say it’s awesome?
- PKop 4y agoRuns on the Erlang Virtual Machine, also known as the BEAM. Erlang was built for scalability and fault tolerance of telephone switches by Ericsson in the 80's. 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 https://youtu.be/JvBT4XBdoUE
- mxgrn 4y agoThis is a great video for introducing Elixir/BEAM.
- chasers 4y agoBinary pattern matching is great for working with protocols. Erlang process monitoring makes it very easy to handle client disconnects. And Elixir makes using Erlang a bit more approachable.
- abc3erl 4y agoI completely agree with chasers's points. Moreover, Elixir's excellent concurrency support and its fault-tolerant nature make it well-suited for handling a high number of connections, ensuring our connection pooler's reliability.
- jhgg 4y agoat work we run pgbouncer on 4 vCPU (GCP c2-standard-4) machines, at 60% load, and they do ~15k TPS at peak, however we only peak at ~45k connections per node. 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.
- chasers 4y agoNice! Yeah really our first go at it so I’m sure we have room to improve. PgBouncer was on the same instance I’m assuming?
- jhgg 4y agonah we run a pool of common pgbouncers that route to the various postgres instances we run. they also run google's cloud sql proxy as well. it's actually haproxy -> pgbouncer -> cloud sql proxy -> postgres. 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.
- chasers 4y agoHa! Wait but why Google Cloud SQL proxy too? 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.
- jhgg 4y agocloud sql proxy makes connecting to cloud managed postgres instances pretty simple/easy. honestly, we dont have a "great" reason other than, we set it up a long time ago, and it's continued to work haha. so pgbouncer connects to cloud sql proxy, which does the connection pooling concern.
- egorr 4y agoHey, from what we’ve seen up to 50k per node should be ok for supavisor on 4 cores as well. And TPS is here mainly to demonstrate that we not only create dummy connections but that these connections are really used. And 20k TPS is just the maximum amount that database node was able to handle. To bump tps we had to spin up more db replicas. We want to run these tests as well at some point, but now were just verifying that we can connect millions of clients and with some tangible tps but not overcomplicating the setup too much.
- losvedir 4y agoLooking at the repo's mix file it seems to use poolboy for pooling. Given that dashbit and José helped out, was nimble_pool considered?
- chasers 4y agoWe discussed this. Don't remember exactly but @abc3erl had a lot of experience with poolboy. 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/pull/4 https://github.com/supabase/supavisor/issues/7 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.
- phamilton 4y agoFeature request that would make me consider moving off of pgbouncer: make prepared statements work in transaction pooling mode. 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.
- chasers 4y agoCan your client use the `binary_parameters` param? e.g. https://blog.bullgare.com/2019/06/pgbouncer-and-prepared-statements/ 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.
- phamilton 4y agoI don't believe that provides the performance improvements we desire. 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.
- chasers 4y agoYeah what you're looking for is "unnamed prepared statements". If your client supports this then you can use PgBouncer and get the throughput benefits. 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.
- phamilton 4y ago
- koolba 4y ago> 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 Is there a specific HTTP API to target for this or something brand new that will cover the desired use cases?
- chasers 4y agoUnknown at the moment but we're definitely open to some prior art if there's an implementation you appreciate. Naively, POSTing a query string and params would go a long ways I think.
- jeff_lynn 4y ago[dead]
- victorbjorklund 4y agoPerhaps a dumb question but can you run it as a library in an existing elixir app to pool the connections of the app?
- chasers 4y agoYou could bring it in as an application but you already get pooling with Ecto via Postgrex and DBConnection. If you're using Elixir there's really no need for separate connection pool until you have LOTS of nodes.
- Dowwie 4y agoWhy not call this SupaPool? Supavisor is very BEAMie. 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?
- chasers 4y agoIt is very BEAMy because we love the BEAM. All connections are supervised :D 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 https://github.com/supabase/supavisor/wiki/Metrics
- vladsanchez 4y agoMy issue with poolers has always been routing. This solution works in a vanilla cluster but not in a multi-node one. 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.
- chasers 4y agoNot yet but we definitely are going to do that.