Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Odyssey: Scalable PostgreSQL Connection Pooler

github.com

Post reply on HN
Print view View original

31–40 of 54 posts

Re: Odyssey: Scalable PostgreSQL Connection Pooler

#31

Post by manigandham »

derefr wrote: post #23

If we are able to control/modify the protocol at both ends, not just in the middle, would this still be the optimal solution? I've been thinking for a while that connection-oriented DBMS protocols seem like a good candidate for being carried as flows over a single-socket multiplexed protocol, like HTTP/2-over-TCP is today. (I say "over TCP" because connections between app servers and databases are long-lived and stab…

You should look at R2DBC then: https://r2dbc.io/

"R2DBC (Reactive Relational Database Connectivity) is an endeavor to bring a reactive programming API to SQL databases."

It already has client and driver implementations for several major DBs. Whether the databases themselves pick it up remains to be seen and will probably takes years.

Re: Odyssey: Scalable PostgreSQL Connection Pooler

#32

Post by hinkley »

derefr wrote: post #23

If we are able to control/modify the protocol at both ends, not just in the middle, would this still be the optimal solution? I've been thinking for a while that connection-oriented DBMS protocols seem like a good candidate for being carried as flows over a single-socket multiplexed protocol, like HTTP/2-over-TCP is today. (I say "over TCP" because connections between app servers and databases are long-lived and stab…

I think there's a circular argument that connections are expensive, queries are supposed to cheap, so anything you can pay for once at connection creation time should be done then, which then guarantees that creating connections is expensive.

How much of that work has to be done for each session sharing one connection?

I'm a bit spoiled. The code I work on at work and the hobby project I'm designing share a common quality, in that they are both read-mostly. It is not going to be overly difficult for me to segregate read and write traffic and use separate connection pools - possibly separate servers. Just making that change is going to save me a considerable amount of overhead for resource management, including connections.

Re: Odyssey: Scalable PostgreSQL Connection Pooler

#34

Post by hinkley »

> Odyssey has sophisticated asynchronous multi-threaded architecture which is driven by custom made coroutine engine: machinarium

Let me find my space helmet. The voice in my head is crying 'architectural astronaut'.

We are forgetting again to ask the important question of "what else did you try, and why didn't it work so you had to write your own?" I think I get the 'why' for pooling. I know a guy at work who struggles a bit with query rate (query speed is great... until it hits the pool limits). I don't know the 'why' for the technology it's built on top of. Lacking that, I see someone wanting to write machinarium and then building something on top of it.

Which doesn't sound like a bad thing at all, until you find a feature the 'thing on top of it' gets wrong which is consistent with the thing the primary authors are most invested in. You can't have that feature because machinarium doesn't want you to have it. Close ticket.

Form always triumphs over function when the architect is invested in their solution and not the user. Making a library that embodies that solution, at least in my experience, vastly amplifies that investment. That also telegraphs the investment, which if you know to look for can tell you 'buyer beware'. Buyer beware.

Machinarium sounds a lot like libuv: https://github.com/libuv/libuv/wiki/Projects-that-use-libuv

Have I got that wrong?

Re: Odyssey: Scalable PostgreSQL Connection Pooler

#35

Post by takeda »

evanelias wrote: post #29
takeda wrote: post #18

Earlier quoted context omitted.

The reason for it is because PostgreSQL wants to concentrate on correctness and do one thing and do it well. They will add a feature if there is a good reason for it and they find an elegant solution. Having said that pooler is needed in all databases not just PostgreSQL. An evidence for it is just recently released MySQL proxy service in AWS. Here's[1] another database independent pool sizing guide with demo video o…

> Having said that pooler is needed in all databases not just PostgreSQL. An evidence for it is just recently released MySQL proxy service in AWS. That's a bit apples and oranges though. The main use-case for that AWS MySQL proxy is high-concurrency Lambda/serverless applications, which conceptually have no other possible means of persistent connections or application-side connection pooling. MySQL's thread-per-conn…

PostgreSQL also can also handle thousands of connections (if you have enough RAM) the problem is that it won't be as performant, due to how concurrency works.

You get increased performance until a certain point (typically 2*number of true cores + more if database waits for disk), after that your performance starts degrading due to overhead. That's how time slicing works. Thread-per-conn can make the overhead smaller but it is still there. Aurora PostgreSQL in AWS has threaded connection pool, but you still get a better performance if you place pgbouncer in front of it and reduce number of connections.

HikariCP[1] talks about it and is database independent.

[1] https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-...

Edit: looks like the video in the article is no longer there, but found one on youtube: https://www.youtube.com/watch?v=xNDnVOCdvQ0

Re: Odyssey: Scalable PostgreSQL Connection Pooler

#36

Post by SergeAx »

hinkley wrote: post #34

> Odyssey has sophisticated asynchronous multi-threaded architecture which is driven by custom made coroutine engine: machinarium Let me find my space helmet. The voice in my head is crying 'architectural astronaut'. We are forgetting again to ask the important question of "what else did you try, and why didn't it work so you had to write your own?" I think I get the 'why' for pooling. I know a guy at work who strugg…

Disclaimer: I worked at Yandex in 2006-2007.

Yandex is a very BIG company in terms of users, requests, data stored etc. It's surely bigger than Twitter, I believe it is bigger than Netflix. It is also algorithms company (like "not content company") So, when Yandex doing something, is mostly doing it because all other options were failed on their load.

Yandex also has a very extensive expertise in C/C++. There is a ClickHouse, there is a CatBoost. I would trust them in their domain.

Re: Odyssey: Scalable PostgreSQL Connection Pooler

#37

Post by PeterZaitsev »

SergeAx wrote: post #36
hinkley wrote: post #34

> Odyssey has sophisticated asynchronous multi-threaded architecture which is driven by custom made coroutine engine: machinarium Let me find my space helmet. The voice in my head is crying 'architectural astronaut'. We are forgetting again to ask the important question of "what else did you try, and why didn't it work so you had to write your own?" I think I get the 'why' for pooling. I know a guy at work who strugg…

Disclaimer: I worked at Yandex in 2006-2007. Yandex is a very BIG company in terms of users, requests, data stored etc. It's surely bigger than Twitter, I believe it is bigger than Netflix. It is also algorithms company (like "not content company") So, when Yandex doing something, is mostly doing it because all other options were failed on their load. Yandex also has a very extensive expertise in C/C++. There is a Cl…

I respect Yandex a lot. They are fantastic at doing 80% of what Google, Uber, Amazon (combined) do with like 10% of resources - people and otherwise. So their software tends to be super efficient.

Re: Odyssey: Scalable PostgreSQL Connection Pooler

#39

Post by pepemon »

SergeAx wrote: post #36
hinkley wrote: post #34

> Odyssey has sophisticated asynchronous multi-threaded architecture which is driven by custom made coroutine engine: machinarium Let me find my space helmet. The voice in my head is crying 'architectural astronaut'. We are forgetting again to ask the important question of "what else did you try, and why didn't it work so you had to write your own?" I think I get the 'why' for pooling. I know a guy at work who strugg…

Disclaimer: I worked at Yandex in 2006-2007. Yandex is a very BIG company in terms of users, requests, data stored etc. It's surely bigger than Twitter, I believe it is bigger than Netflix. It is also algorithms company (like "not content company") So, when Yandex doing something, is mostly doing it because all other options were failed on their load. Yandex also has a very extensive expertise in C/C++. There is a Cl…

Oh well-well, elaborate please about their scale. Bigger than Twitter, bigger then Netflix. Just give us some proof about that.

Re: Odyssey: Scalable PostgreSQL Connection Pooler

#40

Post by pepemon »

PeterZaitsev wrote: post #37
SergeAx wrote: post #36

Earlier quoted context omitted.

Disclaimer: I worked at Yandex in 2006-2007. Yandex is a very BIG company in terms of users, requests, data stored etc. It's surely bigger than Twitter, I believe it is bigger than Netflix. It is also algorithms company (like "not content company") So, when Yandex doing something, is mostly doing it because all other options were failed on their load. Yandex also has a very extensive expertise in C/C++. There is a Cl…

I respect Yandex a lot. They are fantastic at doing 80% of what Google, Uber, Amazon (combined) do with like 10% of resources - people and otherwise. So their software tends to be super efficient.

They are fantastic at loosing their best talent to the western counterparts. Nothing fantastic at all for a big company within other reaches.
Post reply on HN