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 :)
Yes, 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.
so 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 ?
No. 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.
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 somet…
This may sound naive, but implementing your own pooler is very easy e.g. in Go pgproto3 already does all packet-parsing for you. Transaction poolers are looking on ReadyForQuery packet and it's "in trnsaction" property like this [0]. All you need - is stick server connection on new ParameterStatus[1] packet for "SET search_path" instead of ReadyForQuery. [0] https://github.com/pg-sharding/spqr/blob/358f816cd8a964a9c9…
It's not enough to intercept `set search_path`, in fact any arbitrary function could change it, but Postgres doesn't report it back, unlike some other gucs.
This may sound naive, but implementing your own pooler is very easy e.g. in Go pgproto3 already does all packet-parsing for you. Transaction poolers are looking on ReadyForQuery packet and it's "in trnsaction" property like this [0]. All you need - is stick server connection on new ParameterStatus[1] packet for "SET search_path" instead of ReadyForQuery. [0] https://github.com/pg-sharding/spqr/blob/358f816cd8a964a9c9…
It's not enough to intercept `set search_path`, in fact any arbitrary function could change it, but Postgres doesn't report it back, unlike some other gucs.
Hmm, yes. ParameterStatus is not sent when search_path is changed. But I think it would be trivial to patch PostgreSQL to send it.
I bet one even can push such patch through commitfest.
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.
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 w…
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.
Yeah: 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.
Why might I want to use this or pgbouncer instead of, say, the connection pooling built into ORMs like SQLAlchemy?
The 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…
Has 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/ [1] https://clickhouse.com/
Only used Clickhouse, although not through their Cloud offering.
It's an impressive piece of engineering. The best column-oriented DB in the open source space, I would say.
Why might I want to use this or pgbouncer instead of, say, the connection pooling built into ORMs like SQLAlchemy?
You would only see the real value in these poolers in dire times. Screwing up apps or deploys leaking connections is really easy. If that happens you will bring down the whole DB. With these poolers you will not only be able to limit that but also enforce per user (service based if every service has it's own creds) limits. These tools are useful beyond certain scale and you will only realize there true value once you are working in distributed micro-service world specially when they are connecting to same DB.