11 ms·
Scalable PostgreSQL Connection Pooler
- claytonjy 5y agoDoes anyone know if you can specify parent roles (group-style non-login roles)? That's something I've wanted from pgbouncer, so instead of making settings at the user level (applications) I could make them at the group level (e.g. apiv2-readonly).
- x4m 5y agoWe are going to implement config inheritance. This should bring somewhat resemblant functionality.
- david-wb 5y agoHow can I use this from a nodejs app?
- x4m 5y agoOdyssey stands before the databases and packs thousands of NodeJS connection into few tens of real server connections.
- whitepoplar 5y agoEverything that comes out of Yandex is seemingly great. It feels like a lighter, leaner FAANG and as a result, it feels like their software is more usable by small teams (as compared to what the actual FAANGs put out).
- x4m 5y agoI heard FAANG is now MANGA :)
- Tostino 5y agoI appreciate the work going on to make high quality poolers for Postgres...I just really wish the work was done in-core so we could have built in connection pooling, or the work done to make grabbing a new connection as cheap as if you were using a pooler. It sucks to have to add complexity to your stack to fix a "deficiency" in Postgres' design. Still, I am glad there is effort put into project even if I selfishly wish it was done in-core.
- Znafon 5y agoIt is being worked on: https://www.postgresql.org/message-id/flat/ac873432-31cf-d5e4-0b80-b5ac95cfe385%40postgrespro.ru https://www.postgresql.org/message-id/flat/ac873432-31cf-d5e... !
- funny_falcon 5y agoBut it is quite limited at the moment: - connection is bound to backend process it were passed to, - backend process can perform only one transaction simultaneously, - therefore if backend process performs long transaction, all other connections on same backend process are waiting for. Even pgbouncer in transaction mode performs better since it could switch incoming connections to different outgoing connections. There will be no good builtin connection pooler until PostgreSQL adopts some kind of intra-process concurrency - threads or green-threads (preemptive coroutines). And I say, most likely it will never happen due to all extensions written for PostgreSQL.
- anarazel 5y agoThere has been some work towards that - things did get cheaper in 14. Note that you very well might still want a separately run pooler for some workloads - having local poolers on application servers can be good for latency.
- saurik 5y agoYeah: the latency of establishing a new connection fundamentally requires at least one network round trip, and likely a few (and so I tend to have my servers going through two layers of pgbouncer, one on each side of the network); and using in-process pools couples your pool state to your front end code updates... so, since you are going to want to build this connection pooling mechanism anyway--and then are likely going to want to be able to take advtanage off application-specific capability limitations (such as not supporting connection state outside of a transaction) to further improve performance--it doesn't really make much sense to prioritize this inside the database server itself.
- colesantiago 5y agoHas anyone used Yandex Cloud [0] in production?, How does it compare to DigitalOcean, AWS, GCP and others? Also the tech from Yandex such as Clickhouse [1] is really interesting, as they recently spun it out of Yandex. [0] https://cloud.yandex.com/en/ https://cloud.yandex.com/en/ [1] https://clickhouse.com/ https://clickhouse.com/
- zaius 5y agoLast time I tried they wouldn’t take a US credit card, which made it too big of a challenge to consider
- hodgesrm 5y agoAltinity.Cloud accepts credit card payments. Disclaimer: I work at Altinity.
- deleted 5y ago[deleted]
- x4m 5y agoWe are also developing managed databases for US\EU market https://double.cloud/ https://double.cloud/
- ddorian43 5y agoDo you have a plan to support dedicated servers? Ie: I put ssh keys and you manage the cluster.
- x4m 5y agoI'm not actually aware of all detailed plans. My job is to advance PostgreSQL ecosystem. But I'll pass a question to someone with broader view.
- d0uble 5y agoThat sounds like DBA as a Service. No, we don't plan to support that in the near future. One thing that is definitely on our roadmap and may be relevant is BYOA (bring your own account) when hosts of the cluster are running in your AWS/Azure/GCP account, not ours.
- derefr 5y agoQuestion for anyone who knows a lot about Postgres connection pooling: We want to move to using a connection pooling infrastructure component, but so far, we haven't been able to figure out how to do so given our setup. We've got a wacky data model where we have N Postgres schemas, each of which has the same tables (but with different data, obviously.) They're not tenants; more like separate "datasets." Picture something like: each schema is a digraph, with tables like "vertices", "edges", "edge_labels", etc. (Not what we're doing, but it's close.) In theory, we could solve our problem by just sticking all the "vertices" tables together as list-partitions of one mega "vertices" table keyed by dataset_id; but 1. it's kind of incoherent — the data makes no sense when combined together like that, it's only valid when considered in isolation; and 2. it's very operationally convenient for us to manage datasets as separate schemas, re: security, naming, onlining/offlining data, etc. Also, while each user request is only going to interact with a single dataset (schema), a given user might care about a lot of datasets, and make requests about many of them at arbitrary times—so, as far as I can tell, we don't have the sort of stable load per dataset that would allow us to hold separate connection pools per dataset. And, since there's no way to make a schema name into a bind variable, fully-qualifying our queries means generating dynamic SQL strings, which is both expensive on the business layer, and on the SQL query planner, which can't just recognize+unify its plans from the query's history from other schemas. Right now, the way we query the data, is that in our business layer, before each query, we have a middleware that injects a statement like this: SET search_path TO 'name_of_dataset', 'public'; We then run a non-fully-schema-qualified query, and it finds whichever tables that have been made visible to it. Our queries currently don't run in transactions (i.e. we're using JDBC auto-commit on the client side), because they're by-and-large just single (complex) SELECT statements. So the middleware-generated statement above (for now) runs as a separate statement, in a separate single-statement transaction, on the same connection, that runs the query. So per-statement connection-pooling would break everything, as our queries would be being routed to backend connections "primed" with the wrong search_path. And I get the feeling that transaction-level connection pooling won't work for us either (at least for now), because our whole operational problem currently is that our HTTP requests acquire DB connections and then sit on them while other things are processed, depleting the connection pool; and if we turned off JDBC auto-commit, our search_path-injecting middleware (which injects its statement on connection-pool checkout) would just end up starting a DB transaction at the beginning of each of those HTTP requests, where the business layer would then be sitting around with an idle in transaction DB connection, mapping directly to an open pgBouncer backend connection, completely destroying any wins connection-pooling would gain us. We'd be right back where we started. So, in other words, we want/need per-statement pooling; but we need it to allow us to also specify the search path per statement. I've seen that at least pgBouncer has an explicit WONTFIX for this requirement, since in their minds it conflicts with their "can't tell it apart from a regular Postgres session" session-state semantics. Should we bite the bullet and move to putting everything in one schema + list-partitioning + an explicit dataset_id column, so that we can parameterize out the dataset per-query using a bind variable? Or something else?
- x4m 5y agoFWIW I maintain Odyssey and will be happy to answer any question regarding it. Actually there was just a CVE-triggered release... I really hope to release more often with more new functionality. If you are interested in connection pooling maybe you will find interesing SPQR too https://github.com/pg-sharding/spqr https://github.com/pg-sharding/spqr Currently it's our experiment to build pooling-based PostgreSQL sharding.
- hn_throwaway_99 5y agoThanks very much for posting this. Odyssey is analogous to pgbouncer, correct? I haven't set up a connection pooler yet for our postgres instance but was planning to do it eventually with pgbouncer - are you able to comment how Odyssey compares?
- x4m 5y agoConnection pooler allows you to get more transactions per second on the same hardware. All it does - pack a lot of connections into handful of concurrently running PG backends. In theory all it does - relay bytes between sockets. But there are workloads when it's CPU intensive, for example lots of concurrent TLS handshakes. And pgbouncer is single-threaded! Odyssey is more scalable. PgBouncer is a great software, actually. We built Odyssey merely to create competitor for PgBouncer. But then started to add functionality that we needed for cloud installations. E.g. Odyssey computes transaction time quantiles, when PgBouncer computes average transaction time.
- whitepoplar 5y agoHow does Odyssey compare to other high-performance poolers like pgagroal?
- x4m 5y agoDepends on how you measure performance :) pgagroal utilizes a lot of linux processes to track each connection state, we use coroutines packed into small number of processes. Actually if you have a bottleneck in multithreaded pooler - something really goes wrong. The database should consume most of resources. I'd be happy to help tuning Odyssey to someone who will bachmark both poolers (in fact there's only one number - number of worker processes..well, maybe pool_size too). pgagroal claims performance superiority over all poolers [0]. I doubt that Odyssey was used in transaction pooling mode in those experiments. [0] https://github.com/agroal/pgagroal/blob/master/doc/PERFORMANCE.md https://github.com/agroal/pgagroal/blob/master/doc/PERFORMAN...
- sudhirj 5y agoFor those wondering why use a connection pooler at all with Postgres, I wrote a (really long) post about it: https://sudhir.io/understanding-connections-pools/ https://sudhir.io/understanding-connections-pools/
- SOLAR_FIELDS 5y agoThis is a great write up that really helped me understand the issue in depth. Thanks for sharing.
- barefeg 5y agoWhat is the use case for this?
- hn_throwaway_99 5y agoProbably the biggest downside in my mind with postgres (which is otherwise an amazing product!) is that it doesn't scale connections well, as there is 1 process per connection. So teams often put a connection pooler in front of postgres - end clients connect to that connection pooler, which forwards on requests to postgres. AFAIK pgbouncer is the 'de facto standard' for postgres connection pooling, so I asked the author how Odyssey compares.
- sandGorgon 5y agois the Dockerfile not production ready ? https://github.com/yandex/odyssey/blob/master/docker/Dockerfile https://github.com/yandex/odyssey/blob/master/docker/Dockerf... i mean i see valgrind, vim, etc in there. this would be a very fat dockerfile. It seems that way - https://github.com/yandex/odyssey/issues/29#issuecomment-764828337 https://github.com/yandex/odyssey/issues/29#issuecomment-764... >But it's hard to "bless" some "official" as image: no one from active contributors uses Odyssey in Docker to maintain it thoroughly. OTOH pgbouncer docker images are rock-solid in production. Very quickly updated to track upstream. e.g. the Bitnami ones - https://hub.docker.com/r/bitnami/pgbouncer/ https://hub.docker.com/r/bitnami/pgbouncer/ which also have CVE security scans https://quay.io/repository/bitnami/pgbouncer?tab=tags https://quay.io/repository/bitnami/pgbouncer?tab=tags Microsoft releases an officially supported k8s sidecar of pgbouncer - https://hub.docker.com/_/microsoft-azure-oss-db-tools-pgbouncer-sidecar https://hub.docker.com/_/microsoft-azure-oss-db-tools-pgboun...
- x4m 5y agoYes, that's a problem. We use Docker only for developement. And mostly because Odyssey is based on epoll and just will not compile on MacOS.
- sandGorgon 5y agoso im not sure what you meant by that - because MacOS compatibility is generally unrelated to production (which is almost always on Linux). Am I understanding this wrong ? do you have production workloads that use Odyssey on MacOS ?
- x4m 5y agoNo. It's just about developer experience. PostgreSQL core developemnt under MacOS is just so great. You can develop PG patches completely without internet connection on a very old MacBook Air. And everything will be so blazingly fast. For Odyssey development MacOS user needs VM, Linux box or Docker.
- r1b 5y agoWhy might I want to use this or pgbouncer instead of, say, the connection pooling built into ORMs like SQLAlchemy?
- PhoenixReborn 5y agoThe ORM connection pooler is client-side - so each instance of your application using SQLAlchemy will have its own connection pool. This may become unwieldy as you scale up the number of app servers/containers/processes. In contrast, connection poolers like Odyssey or pgbouncer live external to your applications, meaning that: 1. You can connect to them from any application written in any language (not just Python) 2. They provide a global pool for all of the applications connecting and help to not overload Postgres.
- djbusby 5y agoWhy not python? Works on my machine.
- x4m 5y agoYou might want to have many app backends, each with it's own Alchemy pool. This setup would create a lot of Postgres connections. When the network flaps you need to reinstall lots of TLS connections. Maybe 100ms of cpu for each handshake. Also each connection may cost you a fork with lots of following CoW, also up to 100ms of cpu. And also Postgres gives you maximum TPS throughput when you run some hundreds of connections, not thousands. And you might want to isolate one microservice from another (in terms of DB throughput utilization). Poolers can help here too.
- ggregoire 5y agoJust to be sure, Odyssey (or pgBouncer) requires to run on the same box than Postgres, right? So if you are on AWS, you would have to migrate from RDS to running Postgres on a EC2 and install the pooler on the same EC2? Which comes with some big drawbacks (you obviously lose all the benefits of using RDS) but also some benefits (can install every pg extension that you need, have access to every pg settings, etc)
- speedyapoc 5y agoI run pgBouncer on an EC2 instance, connecting to my database on RDS. Does not have to be on the same box.
- ggregoire 5y agoOh nice. Thanks for the quick answer!
- x4m 5y agoI'd certainly recommend to run pooler on the same node to avoid double encryption. Reestablishing 2x connections, dealing with 2x restarts of VM - is kind of a downside too. However it's not strictly neccesary, Odyssey can run even in another AZ :)
- phamilton 5y agoWe've run in both of these configurations connecting to Aurora: 1) Run a pgbouncer ASG with a load balancer in front for applications to connect to. 2) Run pgbouncer on all our hosts and application connect to localhost. (2) was significantly cheaper once we switched over. All our hosts are 24xlarge so there was plenty of application traffic to make pgbouncer meaningful on the same host.
- jordanthoms 5y agoWe’ve been using this for a while after we found pgbouncer became a bottleneck - it’s so much faster and has been totally reliable. Thanks!
- sojournerc 5y agoI lean on client-side pooling these days. It works and is easier to reason about for me, e.g. this client process gets 2 connections. Specifically knex has client pooling. Provides good back pressure and hasn't fallen down yet. Any reason not to do it this way?
- manquer 5y agoIf you have lot of client sources (say in each micro service) different languages and frameworks connecting to same server ( perhaps different db/schema ). It is easier then to manage it in one place . Also dynamic scaling of server pools etc you need change the pooler config instead of every application. And postgres connection management(open/close) is expensive, if you can handle it outside the the db infra it can be beneficial ( only some client libs will pool their connections and others won't). Even with client pooling support in the library, each container/forked runner will have its own connection, and that can be quickly few hundreds. Finally AFAIK most libraries don't handle master failover or server restart without crashing the connection, pooler will usually keep the upstream open for some time.
- lazyant 5y agoWhat's a good way to have a HA setup so we don't have a single point of failure (hearbeat etc)?
- x4m 5y agoI think there's a lot of different HA solutions - Patroni, Stolon, Pacemaker to name few.
- TedShiller 5y agoCustom poolers are very common even in extremely large deployments with Oracle for example
- deleted 5y ago[deleted]