we 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.
Handling 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.
Using 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.
The 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.
What 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.
We 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 . 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 :)
What 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.
Can 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/. I thought the war was lost for postgres, but it seems to keep going. License issues? (Disclosure, I manage 'a few' mysql vms).
27 comments
[ 6.7 ms ] story [ 57.6 ms ] threadSo, 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.
Wow this is very bad. This actually happens in typical Postgres setups?
as per:
https://www.pgpool.net/docs/latest/en/html/runtime-in-memory...
0 - https://docs.pgdog.dev/features/connection-pooler/prepared-s...
1 - https://www.pgpool.net/docs/4.7/en/html/
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 :)
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.