8 ms·
Why we built yet another Postgres connection pooler
- Aejkatappaja 2mo ago[flagged]
- icevl 2mo agoPostgres features still "just work" behind the pooler.
- s8kur 2mo ago[flagged]
- rastignack 2mo agoAnd why it should be done upstream with a thread pool model and an internal scheduler as MS SQL server does.
- huflungdung 2mo ago[dead]
- dwedge 2mo agoThe SET implementation reads like a cleaner version of how ProxySQL does the same thing, which is nice to see
- kardianos 2mo agoWhat posgresql needs is a new wire format. (pgwire4) I've been slopping together a POC to probe the edges of what can be done as just an extension. So far I have a framed protocol with inline cancellation, named parameters, out-of-query text language selector, ad-hoc pg/PLSQL execution with cache (no need for prepare), multiple result sets, streaming large results, and more flexible bulk upload. In other words, with this extension you can query: ``` select * from T1; select * from T2; ``` And return them both in PG/PLSQL or straight SQL. The existing pgwire3 protocol is one of the worst things to work with in postgresql.
- khurs 2mo agoHave you raised it main developers and gained feedback?
- ransom1538 2mo agoCan someone explain it to me like I am 5. Why did postgres win vs mysql? I don't know many companies at scale that use postgres. Slack, youtube, etc all use a mysql based sharding system https://vitess.io/ https://vitess.io/. I thought the war was lost for postgres, but it seems to keep going. License issues? (Disclosure, I manage 'a few' mysql vms).
- Maledictus 2mo agoMySQL is owned by the lawn mover* and barely kept alive. * https://www.youtube.com/watch?v=-zRN7XLCRhc https://www.youtube.com/watch?v=-zRN7XLCRhc
- re-thc 2mo ago> Why did postgres win vs mysql? They did? By social media? According to a lot of social media devs Java is also “dead”. Having said that the issue is MySql / Mariadb is moving more and more behind commercial products e.g. Galera and Heatwave. Postgres continues to be the open community effort. But hence the divide you see. Large companies. Real traffic use pragmatic solutions to make money. The tutorial developers and hype does whatever.
- anticorporate 2mo agoI don't understand the idea of "winning" in this context. If your situation doesn't require specialized features of a particular database, then it doesn't really matter. Just pick one. There's too much premature optimization in the world. If your situation does require something that one database or another excels at, then be grateful that there isn't really such thing as a winner and you can pick the one that works best for your context.
- frollogaston 2mo agoMaybe it's because Postgres is easier to write extensions for? I do think MySQL is still used more widely, but since people on HN and other hacker/tech circles write extensions, you hear about Postgres more. Those extensions also increase Postgres's actual usage, like I know people who only know about Postgres because of PostGIS. Regardless of popularity, idk, Postgres feels nicer to use for me. n.b. PgDog isn't an extension
- mctwo 2mo agoThis is a really neat project, congrats on the launch!
- mmakeev 2mo agowe moved our django app behind pgbouncer transaction pooling a few days ago and the surprise wasn't SET so much as queryset.iterator(). it relies on server side cursors, which don't survive being pooled, so we had to disable it everywhere and let it fall back to client side. also had to move statement_timeout out of the app's connection options into the pooler's own connect query, since libpq startup params just get silently ignored behind it.
- levkk 2mo agoHandling cursors is tough - they are very much session-level objects, so even if we, say, pinned your client while it uses that cursor, which would work, that would decrease the performance of connection pooling overall. So, what's better, breaking your app initially so you know to remove that feature, or letting it work silently while the connection pool isn't 100% in transaction mode? Tough call.
- Maledictus 2mo agoWhich one did you pick?
- levkk 2mo agoPin the client temporarily. I'm assuming that our users know what they are doing. I could be wrong, but so far so good.
- rst 2mo agoMaybe don't reassign the sesssion to a different client so long as there's a cursor open (the way that most poolers have a mode that won't reassign a session that has a transaction open)?
- mmakeev 2mo ago[dead]
- HackerThemAll 2mo agoUsing server-side cursors is a sign of bad design. You use the PostgreSQL server's resources as a cache when you're fetching and processing stuff row by row, which is very bad. Just get the data you requested in the first place in one go and do your stuff in your app. Or process all data on the server and then get the processed data in one batch as well.
- petters 2mo ago> Since connection poolers reuse connections between clients, the connection state of one client “leaks” into the connection state of another. Wow this is very bad. This actually happens in typical Postgres setups?
- nijave 2mo agopgbouncer has different pool modes you can pick from that have some impact over what's possible to leak https://www.pgbouncer.org/features.html https://www.pgbouncer.org/features.html
- hnarn 2mo ago”typical Postgres setups” do not include connection poolers to begin with, this is not a Postgres issue.
- vizzier 2mo agoby definition connection poolers re-use connections so it it can happen with any connection pooling setup, PG or no. in pgbouncer the connection is reset via a customisable command [0] which should reset the connection to a clean state. [0] https://www.pgbouncer.org/config.html#server_reset_query https://www.pgbouncer.org/config.html#server_reset_query
- llimllib 2mo agoYes, as a consequence of how aggressively transparent to the postgres wire protocol pgbouncer wants to be. This article does a good job explaining it: https://www.augusteo.com/blog/how-pgbouncer-works https://www.augusteo.com/blog/how-pgbouncer-works
- McGlockenshire 2mo agoYou'll see this kind of fun in other databases that support "persistent connections." When you start up, you have absolutely no idea what the state of the database is. If a previous process errored out, you might find yourself in the middle of a broken transaction for example. Did the last session do some weird SET magic to make things work? Did it create temporary tables? Well guess what, it's all still there!
- 2mo ago
- jauntywundrkind 2mo agoClickhouse also just put out a fun article on scaling pgbouncer too, talking about scaling out so_reuseport while not having to shard so harshly (a major limitation pgdog here is addressing via rewrite), https://clickhouse.com/blog/pgbouncer-clickhouse-managed-postgres https://clickhouse.com/blog/pgbouncer-clickhouse-managed-pos... https://news.ycombinator.com/item?id=48814152 https://news.ycombinator.com/item?id=48814152
- merb 2mo agoWell tbf pgdog looks extremely amazing on paper and goes way beyond multi threading. The notify/listen fix and automatic query routing to read replicas and auto sharding might bringt Postgres finally closer to vitess
- khurs 2mo ago>might bringt Postgres finally closer to vitess Supabase are launching a Vitess for Postgresql, they have hired the original creator of Vitess for it https://supabase.com/blog/multigres-vitess-for-postgres https://supabase.com/blog/multigres-vitess-for-postgres
- tpetry 2mo agoAnd Planetscale (who is the current primary maintainer of vitess) is developing a Vitess for PostgreSQL. There will be a couple of production-grade PG vitess solutions the next months.
- inigyou 2mo agoDoesn't this NOTIFY performance fix mean that it isn't transactional any more?
- levkk 2mo agoFrom the strictest CAP theorem definition, that's correct, it is not. But, it's pretty close. I know that in the database world, that's not a good answer, but in practice, it will deliver the vast majority of messages, so maybe that's good enough? We'll see. We show that it's possible to come close without breaking the DB or the app, but I suspect, it's not quite yet at the level you'd expect from a _durable_ work queue, e.g., Kafka. Not going to replace that one anytime soon.
- Maledictus 2mo agoPlease document this, if that's not the case already. Thank you for open sourcing this!
- khurs 2mo agoAny plans to add Query Caching for Selects? as per: https://www.pgpool.net/docs/latest/en/html/runtime-in-memory-query-cache.html https://www.pgpool.net/docs/latest/en/html/runtime-in-memory...
- levkk 2mo agoI don't think so. Too much trouble. Caching is a really hard problem without context, and the context, typically, is app-specific.
- khurs 2mo agoThanks for the reply and good work on pgDog!!
- HackerThemAll 2mo agoI hope not. It introduces a lot more problems than it solves.
- babayega2 2mo agoIs there a pooler handling schema switching in PostgreSQL? like something in front of django-tenant ?
- danielsmori 2mo ago[flagged]
- Technical_Plant 2mo ago[flagged]
- 27183 2mo agoIt's awesome to see AGPL instead of the horrible BSL variants that have been going around.
- alecco 2mo agoIt is quite telling that most cloud companies stay away from AGPL. Their business model is antisocial.
- slopinthebag 2mo agoYou mean the licence is anti-business and pro-social.
- alecco 2mo agoAGPL only closes the SaaS loophole and forces them to publish their changes to the code. It's not anti-business at all. You could argue the BSL is anti-business. But IMHO even that one is only a reaction to abuse. Database companies investing millions in research, development, and maintenance for some trillion dollar corporation to take it and make billions off it without giving back a single penny.
- levkk 2mo agoThanks! We try to be very open and explicit about why we chose AGPL. Personally, I like it because it's an extension of GPL, which is a huge reason why I was able to self-teach programming. Just trying to give back.
- AdieuToLogic 2mo agoThe fact that PgDog supports prepared statements[0] is a compelling feature in and of itself. This was a limitation of older versions of pgpool-II[1] thus disqualifying it in efforts where it otherwise could have been beneficial. 0 - https://docs.pgdog.dev/features/connection-pooler/prepared-statements/#how-it-works https://docs.pgdog.dev/features/connection-pooler/prepared-s... 1 - https://www.pgpool.net/docs/4.7/en/html/ https://www.pgpool.net/docs/4.7/en/html/
- abrookewood 2mo agoQuick note to say that the article describing WHY you are different and why it matters was very well written. Congrats on the launch!
- rubenvanwyk 2mo agoWhat really interests me most is the sharding and the possibility of using this for multitenancy - is the hooks / plugin architecture sufficient so you can run a small sidecar to add shards or tenants to the TOML file dynamically? Would be a game changer.
- apt-get 2mo agoWe found it pretty easy to build a little k8s controller for our own purposes to do this -- see https://news.ycombinator.com/item?id=48478994 https://news.ycombinator.com/item?id=48478994 . You probably don't need to implement this as a plugin or hook, pgdog supports dynamic reload of its configuration without dropping existing connections. Although I'm the type to shy away from adding extra layers in my architectures when I can help it, pgdog has been an absolute breeze to use :)
- levkk 2mo agoYes. The plugin architecture supports this currently. That being said, you can use any number of sharding functions we support to do this too.
- fernando-ram 2mo ago[flagged]