I 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.
There 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.
FWIW 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
Currently it's our experiment to build pooling-based PostgreSQL sharding.
Some questions on auth. We have one cluster with a large number of databases, all with automatically provisioned and rotating credentials for service roles using postgres password auth. Currently, clients connect directly to the db cluster (there's a tcp lb but no pgbouncer etc)
In this case, I'd like for services to continue authenticating as previously without changing authentication method, just switching address.
Should I use password_passthrough/auth_query? Does odyssey need pg credentials itself? Is storage_db referring to the db where odyssey stores internal data for itself...?
I guess I don't get why a connection pooler would need to handle auth, much like an HTTPS proxy doesn't need API tokens itself.
Thanks 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?
Maybe a dumb question but does it lock the postgres connection down to one pool client (no sharing) when the client has a transaction in progress? How does it compare to pgbouncer in this regard?
Everything 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).
Just 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)
I'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 :)
We'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.
Does 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).
I 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?
If 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.
Probably 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.
Question 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?
We are building a solution for this problem at Splitgraph [0] – it sounds like we could probably help with your use case, assuming this is for analytical (OLAP) data. You can get it to work yourself with our open source code [1], but our (private beta, upcoming public) SaaS will put all your schemas on a more scalable “data delivery network,” which incidentally, happens to be implemented with PgBouncer + rewriting + ephemeral instances. We also have private repositories / fine-grained ACL shipping shortly.
In a local engine (just a Postgres DB managed by Splitgraph client to add extra stuff), there is no PgBouncer or batteries-included authN/Z, but we use Foreign Data Wrappers to accomplish the same query resolving. Our goal is for you to have the ability to do everything locally as an individual, with the SaaS becoming useful for “multiplayer” (teams and orgs).
On Splitgraph, every dataset – and every version of every dataset – has an address. Think of it like tagged Docker images. The address either points to an immutable “data image” (in which case we can optionally download objects required to resolve a query on-the-fly, although loading up-front is possible too) or to a live data source (in which case we proxy directly to it via FDW translation). This simple idea of _addressable data products_ goes a long way – for example, it means that computing a diff is now as simple as joining across two tables (one with the previous version, one with the new).
Please excuse the Frankenstein marketing site – we’re in the midst of redesign / rework of info architecture while we build out our SaaS product.
Feel free to reach out if you’ve got questions. And if you have a business case, we have spots available in our private pilot. My email is in my profile – mention HN :)
[+] [-] Tostino|4 years ago|reply
Still, I am glad there is effort put into project even if I selfishly wish it was done in-core.
[+] [-] x4m|4 years ago|reply
[+] [-] anarazel|4 years ago|reply
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.
[+] [-] Znafon|4 years ago|reply
[+] [-] riyadparvez|4 years ago|reply
[+] [-] x4m|4 years ago|reply
If you are interested in connection pooling maybe you will find interesing SPQR too https://github.com/pg-sharding/spqr Currently it's our experiment to build pooling-based PostgreSQL sharding.
[+] [-] 3np|4 years ago|reply
In this case, I'd like for services to continue authenticating as previously without changing authentication method, just switching address.
Should I use password_passthrough/auth_query? Does odyssey need pg credentials itself? Is storage_db referring to the db where odyssey stores internal data for itself...?
I guess I don't get why a connection pooler would need to handle auth, much like an HTTPS proxy doesn't need API tokens itself.
[+] [-] hn_throwaway_99|4 years ago|reply
[+] [-] kerblang|4 years ago|reply
[+] [-] jerrysievert|4 years ago|reply
[+] [-] whitepoplar|4 years ago|reply
[+] [-] x4m|4 years ago|reply
[+] [-] sudhirj|4 years ago|reply
[+] [-] SOLAR_FIELDS|4 years ago|reply
[+] [-] colesantiago|4 years ago|reply
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/
[1] https://clickhouse.com/
[+] [-] x4m|4 years ago|reply
[+] [-] the-alchemist|4 years ago|reply
It's an impressive piece of engineering. The best column-oriented DB in the open source space, I would say.
[+] [-] zaius|4 years ago|reply
[+] [-] unknown|4 years ago|reply
[deleted]
[+] [-] sandGorgon|4 years ago|reply
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-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/ which also have CVE security scans 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-pgboun...
[+] [-] x4m|4 years ago|reply
[+] [-] jordanthoms|4 years ago|reply
[+] [-] r1b|4 years ago|reply
[+] [-] ggregoire|4 years ago|reply
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|4 years ago|reply
[+] [-] x4m|4 years ago|reply
[+] [-] phamilton|4 years ago|reply
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.
[+] [-] claytonjy|4 years ago|reply
[+] [-] x4m|4 years ago|reply
[+] [-] sojournerc|4 years ago|reply
Provides good back pressure and hasn't fallen down yet. Any reason not to do it this way?
[+] [-] manquer|4 years ago|reply
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.
[+] [-] TedShiller|4 years ago|reply
[+] [-] david-wb|4 years ago|reply
[+] [-] x4m|4 years ago|reply
[+] [-] unknown|4 years ago|reply
[deleted]
[+] [-] barefeg|4 years ago|reply
[+] [-] hn_throwaway_99|4 years ago|reply
[+] [-] lazyant|4 years ago|reply
[+] [-] x4m|4 years ago|reply
[+] [-] derefr|4 years ago|reply
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:
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?
[+] [-] chatmasta|4 years ago|reply
In a local engine (just a Postgres DB managed by Splitgraph client to add extra stuff), there is no PgBouncer or batteries-included authN/Z, but we use Foreign Data Wrappers to accomplish the same query resolving. Our goal is for you to have the ability to do everything locally as an individual, with the SaaS becoming useful for “multiplayer” (teams and orgs).
On Splitgraph, every dataset – and every version of every dataset – has an address. Think of it like tagged Docker images. The address either points to an immutable “data image” (in which case we can optionally download objects required to resolve a query on-the-fly, although loading up-front is possible too) or to a live data source (in which case we proxy directly to it via FDW translation). This simple idea of _addressable data products_ goes a long way – for example, it means that computing a diff is now as simple as joining across two tables (one with the previous version, one with the new).
Please excuse the Frankenstein marketing site – we’re in the midst of redesign / rework of info architecture while we build out our SaaS product.
Feel free to reach out if you’ve got questions. And if you have a business case, we have spots available in our private pilot. My email is in my profile – mention HN :)
[0] https://www.splitgraph.com/connect
[1] examples: https://github.com/splitgraph/splitgraph/tree/master/example...