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.
Thanks! 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.
AGPL 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.
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.
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 :)
It's not unique to postgres, as others have said; the same thing can happen with e.g. MySQL poolers/proxies/etc., since the behavior of the connection can be changed dynamically and it persists for the lifetime of the connection.
Example: legacy client A connects to MySQL via the bouncer and says 'I want all of our conversations to use latin-1, not utf-8'. This changes the character set that MySQL parses queries with and returns responses in. The legacy client does some queries and then disconnects.
Now a new client connects to MySQL, and the bouncer just assigns it to the still-open connection from before. The new client is fully UTF-8 compatible and since this is the default for our database it doesn't explicitly say so; it just assumes that UTF-8 is the way to go. Unfortunately, the database server is still thinking in latin-1, meaning that if this new client sends UTF-8 data it will be parsed as latin-1; latin-1 is a subset of UTF-8, meaning that queries will actually work fine unless they need to use a character outside of latin-1, in which case they will get an error, or corrupted data, from the server.
The only solutions around this are:
1. Ensure that every client is using the same settings; if your database is for a single app that uses the same ORM, then this is automatic.
2. Ensure that every client is always explicit about everything it might need to change e4very time, so that every UTF-8 client explicitly sets UTF-8 connections even when that's the default; clients that need utf8mb4 ask for it explicitly and clients that can't handle it ask for something else. One way of ensuring this happens is to configure the server (or the bouncer) to use defaults which are not valid for anyone, or which are going to cause errors frequently and not rarely (e.g. setting the default character set to 7-bit swedish, which would cause frequent errors).
3. Use a bouncer which can either disallow these changes or detect and revert them after the original client has disconnected. I'm not sure if this exists for MySQL at least.
4. Use separate bouncers for each application that might be different (extension of #1); in other words, instead of having a bouncer or set of bouncers for each pool of database servers, you have them for each application; your web app gets one, your legacy reporting tool gets one, your ODBC connector gets one, and so on.
It's kind of a huge mess in theory; in practice, a lot of installations fall into the #1 case so it never matters, but that makes the occasional instance where it does matter extremely difficult to debug.
> 3. Use a bouncer which can either disallow these changes or detect and revert them after the original client has disconnected. I'm not sure if this exists for MySQL at least.
Recently ran into a related bug in duckdb. They implemented a basic http connection pooler (described as a parking lot) but there are edge cases where broken connections get returned to the pool then the next thing tries to use a busted connection and fails.
I was thinking of something that only exists at the pool level.
Every new version of your app has the potential to change behavior in a way that would affect the previous version if the connection was recycled during a progressive rollout.
But I don’t think I would want to create a real database user for every version of the app.
I suppose the connection pooler could map versioned users to the same real user, and use separate pools, but a dedicated UA field is probably better.
Yes, 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
You'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!
From 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.
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).
Maybe 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.
I 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.
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.
Looked it up, says 'Will be released as an open source project' but announcement was in July 2025 and no public repo yet which is strange, so time will tell if they will stick to this.
We are already moving Neki customers into production. Nobody else is even close. PlanetScale doesn't spend our days yapping about what we are going to do. We just do it.
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.
Maybe 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)?
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.
As said above, SQL is a set based language and one shouldn’t write a cursor to deal with individual rows unless no other way.
And if writing a server side cursor, probably better to write a stored procedure /function and put the cursor and its logic in it, and then call that rather than handle in application
two different things are getting called "cursor" here. queryset.iterator() isn't server-side row-by-row processing, it's one set-based query streamed to the client in chunks so a big export doesn't materialize in memory all at once, "get it in one go" is exactly the OOM it avoids.
the honest knock on the streaming kind isn't "bad design", it's that it holds a portal open server-side, which is why it can't survive transaction pooling. so the pooler-friendly fix isn't one giant fetch, it's keyset pagination (where id > last limit n), stateless and constant memory, which is what we moved those paths to.
0 - https://docs.pgdog.dev/features/connection-pooler/prepared-s...
1 - https://www.pgpool.net/docs/4.7/en/html/
reply