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.
Great article that matches my experiences: running with max_connections=100000 is not an issue when you have more than enough ram, the problem is the idle connections that you are tempted to tolerate (after all, you've got lots of RAM, so why bother?) linearly decrease the TPS even if nothing is done
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.
> 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.
Can't you queue and serialize those writes in a middle layer so that you only need a small amount of connections?
Yea, normally you'd want some type of broker / middle-layer to accept the incoming data from the devices and then send it onward into the database. That broker would also authenticate / validate the devices / data etc. And it can do stuff like batch the input, for efficiency.
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.
Indeed, that's what I currently have, but for this database I'm looking at alternatives (ex: clickhouse) that could get everything and "send it onward" on a schedule.
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.
If you’re handling that many connections, does pgbouncer really add that much complexity? Surely at that scale, pgbouncer is less complex than alternative scaling options (replicas, caching, etc). We run it in production and hardly consider ourselves experts in pgbouncer.
Before pgbouncer, I was running with 6000 active connections, each of them doing insertions. I fail to see how replicas can help if your input comes from that many clients doing writes, each already having an internal pool.
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.
Did Microsoft follow up your research with some Postgresql patches? 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?
> Did Microsoft follow up your research with some Postgresql patches?
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:
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.
Thank you for your work on Postgresql. I'd like to pick your brain a bit more if you don't mind :)
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.
I think it would be neat to have - but personally I think there are higher priorities (asynchronous IO, faster executor, better buffer mapping, different connection model, built in automatic failover). And I don't think any of my colleagues was planning to work on the topic. A good implementation would be a lot of work, with an uncertain outcome at best.
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.
https://news.ycombinator.com/item?id=24887676