6 ms·

Analyzing the Limits of Connection Scalability in Postgres

112 points by ozgune 6y ago

jeff-davis 6y ago
The linked post is slightly outdated. Related discussion on a subsequent post: https://news.ycombinator.com/item?id=24887676 https://news.ycombinator.com/item?id=24887676
- ozgune 6y ago
  Minor clarification note. 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.
anarazel 6y ago
Author here, happy to answer questions & criticism.
- polskibus 6y ago
  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?
  - anarazel 6y ago
    > 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: https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/improving-postgres-connection-scalability-snapshots/ba-p/1806462 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.
    - polskibus 6y ago
      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.
      - anarazel 6y ago
        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.
      - taffer 6y ago
        OTOH this article [1] argues that clustered indexes are overrated and actually worse than heap tables for most use cases. [1] https://use-the-index-luke.com/sql/clustering/index-organized-clustered-index https://use-the-index-luke.com/sql/clustering/index-organize...
1996 6y ago
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.
- qeternity 6y ago
  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.
  - 1996 6y ago
    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.
- taffer 6y ago
  > 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?
TedShiller 6y ago
Funny that Microsoft of all companies is talking about limits in other software
- boulos 6y ago
  https://blogs.microsoft.com/blog/2019/01/24/microsoft-acquires-citus-data-re-affirming-its-commitment-to-open-source-and-accelerating-azure-postgresql-performance-and-scale/ https://blogs.microsoft.com/blog/2019/01/24/microsoft-acquir...

112 points by ozgune 6y ago

The linked post is slightly outdated. Related discussion on a subsequent post: https://news.ycombinator.com/item?id=24887676 https://news.ycombinator.com/item?id=24887676

ozgune 6y ago
Minor clarification note. 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.

anarazel 6y ago

Author here, happy to answer questions & criticism.

polskibus 6y ago
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?
- anarazel 6y ago
  > 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: https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/improving-postgres-connection-scalability-snapshots/ba-p/1806462 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.
  - polskibus 6y ago
    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.
    - anarazel 6y ago
      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.
    - taffer 6y ago
      OTOH this article [1] argues that clustered indexes are overrated and actually worse than heap tables for most use cases. [1] https://use-the-index-luke.com/sql/clustering/index-organized-clustered-index https://use-the-index-luke.com/sql/clustering/index-organize...

1996 6y ago

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.

qeternity 6y ago
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.
- 1996 6y ago
  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.
taffer 6y ago
> 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?

TedShiller 6y ago

Funny that Microsoft of all companies is talking about limits in other software

boulos 6y ago
https://blogs.microsoft.com/blog/2019/01/24/microsoft-acquires-citus-data-re-affirming-its-commitment-to-open-source-and-accelerating-azure-postgresql-performance-and-scale/ https://blogs.microsoft.com/blog/2019/01/24/microsoft-acquir...