Analyzing the Limits of Connection Scalability in Postgres
techcommunity.microsoft.com
techcommunity.microsoft.com
Andres wrote two blog posts on Postgres' connection scalability behavior. Part 1 analyzed current bottlenecks in Postgres 13 (this blog post) and Part 2 described the work done in Postgres 14 to make improvements on the biggest bottleneck (linked from Jeff's comment above).
Part 2 hit Hacker News first, so the appearance on HN was a bit out of order.
pgbouncer does help, but brings in unneeded complexity.
postgres 14 should offer an "alternate" port doing essentially what pgbouncer does, next to the regular port.
Typical usecase: a large fleet of IOT devices with a write-only access to a central database, feeding new data every second. No visibility of the current transactions is needed.
I tried various other things, like an unlogged table on the input side, but nothing helped much.
I have tested with and without pgbouncer, and the case isn't as clear cut as it seems. It helps a bit, but it adds complexity.
This complexity is not readily apparent, but you have to separately manage the userlist, you must not get your auth types out of sync (md5/scram), and so on...
The only reason pgbouncer stays is that I don't want to mess with it again, but it's death by a thousand paper cuts, enough to complexify deployment and updates to a point where I'm thinking of calling it quit and using a different database in front of postgres.
Can't you queue and serialize those writes in a middle layer so that you only need a small amount of connections?
If you actually need that many incoming connections, for now you'd probably want to use pgbouncer (or oddysey or ...) in transaction or statement pooling mode.
It's just a glorified bulk insert with extra moving parts, but if it works around the issue, why not?
BTW Have you played with oddysey? It doesn't seem essentially different (or better) from pgbouncer.
pgbouncer is single threaded, oddysey isn't.
Yes. I had committed them to the development branch before starting to write the post. There's a second post describing the concrete improvements that were committed:
https://techcommunity.microsoft.com/t5/azure-database-for-po...
There's more to do, of course, but in my opinion this addresses the biggest issues.
> What is the MS policy/plans for enhancing Postgresql? On one hand MS sells competitor (Sql Server), on the other profits from Postgresql managed instances. How does it work out for Postgresql?
A few colleagues and I, all with a pre-MS history of contributing to Postgres, spend the majority of our time developing / maintaining open source Postgres. There's resulting features / patches in PG13 (released) and PG14 (in development). Committed work includes, spilling hash-joins, collation versioning, faster recovery / replication, connection scalability improvements among others.
What is you view on Postgresql not having a true clustered index like ms sql has? I mean that the table is maintained sorted by PK all the time (table is stored as btree). Do you think there's a possibility of MS sponsoring it? Our experience with Postgresql shows that as the biggest drawback for many popular workloads compared to other RDBMSes.
There have been a good number of efficiency improvements in the last releases to postgres' btree implementation in the last few releases. Not the same as what you're looking for, obviously, but it might still help.
[1] https://use-the-index-luke.com/sql/clustering/index-organize...