8 ms·

I do not understand why people aren't clamouring against postgres's connection model. I might be missing something but as I understand it, Postgres had chosen

by thdxr 6y ago

I do not understand why people aren't clamouring against postgres's connection model. I might be missing something but as I understand it, Postgres had chosen to couple the concept of a connection and a unit of concurrency for simplicity of implementation. However this means that even if your server can theoretically handle 5000 concurrent reads you will never get there because opening 5000 connections isn't practical. You'll likely hit a memory limit. Why is this generally accepted as ok? Decoupling concurrency from connections seems possible via pipelining commands over a single connection. Is it just too late for a project as mature as Postgres?

cperciva 6y ago
I don't know anything about Postgres in this regard, but one common reason for not pipelining requests is fairness: You don't want one client to be able to starve others out by being very noisy.
- anarazel 6y ago
  The postgres protocol does support pipelining of queries - and it can be a huge boon in latency sensitive workloads. They'll get processed in-order on the server side, with results being sent back while the next query is being processed. The biggest weakness around pipelining is that a fair number of drivers don't support it yet, including the C client interface that is part of postgres (there's a patch being reviewed right now adding it there). The common jdbc driver, .net, and a few other popular ones do support it though.
  - goatinaboat 6y ago
    It would be amazing to see Postgres do this over TDS.
    - anarazel 6y ago
      I've not seen any real efforts to add support for different protocols to postgres. Do you really think just adding protocol level support for e.g. TDS would be useful, if the the SQL dialect still was postgres's? If so - why? While I am employed by MS, I just work on PG, and I have long before starting at MS. So I just have the open source hacker's perspective on this, not any MS perspective.
anarazel 6y ago
> I do not understand why people aren't clamouring against postgres's connection model. There are people wanting to change that - including me, the author of the blog post. I explained in an earlier blog post ([1]) why I chose to work on making snapshots more scalable at this time: > Lastly, there is the aspect of wanting to handle many tens of thousands of connections, likely by entirely switching the connection model. As outlined, that is a huge project / fundamental paradigm shift. That doesn’t mean it should not be tackled, obviously. > Addressing the snapshot scalability issue first thus seems worthwhile, promising significant benefits on its own. > But there’s also a more fundamental reason for tackling snapshot scalability first: While e.g. addressing some memory usage issues at the same time, as switching the connection model would not at all address the snapshot issue. We would obviously still need to provide isolation between the connections, even if a connection wouldn’t have a dedicated process anymore. > However this means that even if your server can theoretically handle 5000 concurrent reads you will never get there because opening 5000 connections isn't practical. You'll likely hit a memory limit. It's quite possible to have 5000 connections, even leaving poolers aside. When using huge_pages=on, a connection has an overhead of < 2MiB ([2]). Obviously 10GiB isn't peanuts, but it's also not a crazy amount. > Why is this generally accepted as ok? Postgres is an open source project. It's useful in a lot of cases. It's not in some others - partially due to non-fundamental limitations. There's a fairly limited set of developers - we can only work on so many things at a time... > Decoupling concurrency from connections seems possible via pipelining commands over a single connection. Could you expand on what you mean here? > Is it just too late for a project as mature as Postgres? No. It's entirely doable to decouple processes and connections. It however definitely is a large project, with some non-trivial prerequisites. [1] https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/analyzing-the-limits-of-connection-scalability-in-postgres/ba-p/1757266 https://techcommunity.microsoft.com/t5/azure-database-for-po... [2] https://blog.anarazel.de/2020/10/07/measuring-the-memory-overhead-of-a-postgres-connection/ https://blog.anarazel.de/2020/10/07/measuring-the-memory-ove... EDIT: formatting woes
- dmw_ng 6y ago
  There is a much better reference somewhere (possibly from Ingres times, or later), but here is Stonebraker describing how PostgreSQL ended up with connection-per-process in 1986: > DBMS code must run as a sparate process from the application programs that access the database in order to provide data protection. The process structure can use one DBMS process per application program (i.e., a process-per-user model [STON81]) or one DBMS process for all application programs (i.e., a server model). The server model has many performance benefits (e.g., sharing of open file descriptors and buffers and optimized task switching and message send- ing overhead) in a large machine environment in which high performance is critical. However, this approach requires that a fairly complete special-purpose operating system be built. In constrast, the process-per-user model is simpler to implement but will not perform as well on most conventional operating systems. We decided after much soul searching to implement POSTGRES using a process-per-user model architecture because of our limited programming resources. POSTGRES is an ambitious undertaking and we believe the additional complexity introduced by the server architecture was not worth the additional risk of not getting the system running. Our current plan then is to implement POSTGRES as a process-per-user model on Unix 4.3 BSD. (THE DESIGN OF POSTGRES, https://dsf.berkeley.edu/papers/ERL-M85-95.pdf https://dsf.berkeley.edu/papers/ERL-M85-95.pdf ) There is another reference directly related to Postgres or PostgreSQL that made it even more clear, I expect it was probably later on. In effect it indicated someone involved in the project had strong intentions of getting to adding threading "real soon now". I'll update the comment if I figure out where that's from. Threads were still a research thing by the mid 80s, so its absence from such an old design is easy to understand. Pthreads wasn't even standardized until 1996, although several unices (e.g. SunOS) already had popular implementations long before that.
fulafel 6y ago
Napkin time... 5 TB RAM would still leave 1 GB per connection. Getting enough CPU and IO in a box for 5000 concurrent queries would be much harder than the RAM. I think core counts for conventional x86 servers top out around 128-256 cores (not counting multi box custom interconnect systems that appear to software as SSI)
- anarazel 6y ago
  It's extremely rare for workloads that need high connection counts to utilize every connection to the extent that they're practically never idle. To the contrary, usually the majority are idle - latency alone leads to that, given the fast queries such workloads commonly have. Not to speak of the applications holding those connections usually also doing other stuff than sending pipelined queries (including just waiting for incoming requests themselves).
  - fulafel 6y ago
    The question was originally about 5k concurrent reads. But even for 4500 connections idle you'd still want 500 cores and a monster IO subsystem? (There are middlewares to handle the idle connection pooling more efficiently though.)
    - thdxr 6y ago
      Think you're mixing up parallelism and concurrency. Other databases with connections that support pipelining can easily get to high concurrency even with a single core. The key is the cpu can kick off 100 queries to a single connection and just wake up when a response is available.
      - deleted 6y ago
        [deleted]
      - fulafel 6y ago
        But where this kind of concurrency without corresponding parallelism make sense? There's no point in trying to do thousands of concurrent reads if there's no available parallelism on the same scale, especially keeping transaction / isolation level snapshots open, it's just an overloaded system. Then you want to do backpressure with a maybe some queuing.
voganmother42 6y ago
May be worth noting that pgbouncer is one method of dealing with large connection counts
viraptor 6y ago
> Decoupling concurrency from connections seems possible via pipelining commands over a single connection. Is it just too late for a project as mature as Postgres? This is commonly done using http://www.pgbouncer.org/ http://www.pgbouncer.org/ While it's not built-in, it's a really common solution and can achieve high number of connections. It comes with some issues though (no server-side prepared statements). And yes, it's not exactly the same as pipelining on the server itself since it's still limited by the number of bouncer connections, but there's usually enough idle time to sacrifice some latency for throughput. There's also https://www.pgpool.net/mediawiki/index.php/Main_Page https://www.pgpool.net/mediawiki/index.php/Main_Page but I'm not familiar with the details.
- fabian2k 6y ago
  I always see people recommending external pools like this here on HN. I'm wondering a bit about that as many frameworks/libraries that use Postgres already implement an internal connection pool. Is this recommendation generally assuming that multiple applications will access the server? Or are there other reasons to prefer an external pooler over internal pooling in your application?
  - throwaway189262 6y ago
    Its for IMO, crap languages that don't have proper threading support. Every language with native decent threading implementation has its own connection pool in the client or as an add-on library
  - hans_castorp 6y ago
    > Is this recommendation generally assuming that multiple applications will access the server That's very often a reason to use external pools, yes. Or just think of a single web application that runs on multiple nodes to distribute the load on the application servers. You either carefully configure each connection pool or you use an external pooler to which each node connects to (rather than directly to the database)
    - 6y ago
hans_castorp 6y ago
FWIW: Oracle also uses one process per connection on Unix/Linux. I think since Oracle 12 this can be changed during installation, but it's still the default. When using connection pools, this isn't really such a problem in the majority of the cases.
- voganmother42 6y ago
  In oracle its been an option for a long time(since atleast 10g, I think before), shared vs dedicated servers in their language, and in my experience pretty rare to use shared servers.
  - hans_castorp 6y ago
    Shared servers represent a built-in connection pool. They still use one process for each connection. Oracle uses a thread model in Windows (one "orcle" process, each connection is a thread), but not on Unix/Linux.
- aeyes 6y ago
  Oracle doesn't handle TCP connections, the Oracle Net Listener (a separate process) is responsible for that. It supports connection pooling. I'm no expert on how this scales compared to Postgres as it is hard to find benchmarks, thanks to Oracle licensing terms.
deleted 6y ago
[deleted]
throwaway189262 6y ago
It's kinda bad, but fine if you have low latency from servers to DB. And, if your other servers are very careful about not holding transactions open. That's a big one. You can't be sloppy and hang a connection open with transactions running. This is bad for slow languages where every transaction takes 10+ms. And it's bad if your language doesn't support connection pools/threading. In practice that means you can get full performance with fast threaded languages like Java, C#, Go, Rust that can use connection pooling. But performance will suffer if language is slow or doesn't have good enough threading to do connection pooling. So yeah, it's a bad-ish design that works fine if you're using a fast threaded language. If you're trying to squeeze max performance out of Ruby or something you're going to be disappointed in other ways anyway
mjibson 6y ago
I'll guess: money. Postgres is decades old and was designed when the internet was smaller. Doing a large, fundamental change like this requires an already experienced person (or maybe more than one) to devote a lot of time designing and implementing some solution. This time costs money. So some company must be willing to employ or pay some people to work full-time on this for months. Anyone qualified to work on this should be very expensive, so full costs to pay experts for months of their time would be in the ~$100-200k level. Much outside the donate-a-cup-of-coffee-each-month range, and outside of any small startup's budget, too. This suggests that the various companies employing people to work on Postgres-related stuff (like Microsoft, perhaps due to their purchase of Citus) have more lucrative work they'd rather do instead of improve this at the design level. This problem is now perhaps larger than open source is designed to handle because of how expensive it is to fix. Very few people (zero in this case) are willing to freely donate months of their life to improve a situation that will enrich other companies. Regarding the difficulty of doing this: the blog post here describes how the concurrency and transaction safety model is related to connections, so any connection-related work must also be aware of transaction safety (very scary).
- anarazel 6y ago
  > This suggests that the various companies employing people to work on Postgres-related stuff (like Microsoft, perhaps due to their purchase of Citus) have more lucrative work they'd rather do instead of improve this at the design level. Well I - the author of this post, employed by MS - did just work quite a while on improving connection scalability. And, as I outlined in the precursor blog post, improving snapshot scalability practically is a prerequisite of changing the connection model to handle significantly larger numbers of connections... Trying to fix things like this in one fell swoop instead of working incrementally tends to not work in my experience. It's much more likely to succeed if a large project can be chopped up into individually beneficial steps.

by thdxr 6y ago

cperciva 6y ago

I don't know anything about Postgres in this regard, but one common reason for not pipelining requests is fairness: You don't want one client to be able to starve others out by being very noisy.

anarazel 6y ago
The postgres protocol does support pipelining of queries - and it can be a huge boon in latency sensitive workloads. They'll get processed in-order on the server side, with results being sent back while the next query is being processed. The biggest weakness around pipelining is that a fair number of drivers don't support it yet, including the C client interface that is part of postgres (there's a patch being reviewed right now adding it there). The common jdbc driver, .net, and a few other popular ones do support it though.
- goatinaboat 6y ago
  It would be amazing to see Postgres do this over TDS.
  - anarazel 6y ago
    I've not seen any real efforts to add support for different protocols to postgres. Do you really think just adding protocol level support for e.g. TDS would be useful, if the the SQL dialect still was postgres's? If so - why? While I am employed by MS, I just work on PG, and I have long before starting at MS. So I just have the open source hacker's perspective on this, not any MS perspective.

anarazel 6y ago

> I do not understand why people aren't clamouring against postgres's connection model. There are people wanting to change that - including me, the author of the blog post. I explained in an earlier blog post ([1]) why I chose to work on making snapshots more scalable at this time: > Lastly, there is the aspect of wanting to handle many tens of thousands of connections, likely by entirely switching the connection model. As outlined, that is a huge project / fundamental paradigm shift. That doesn’t mean it should not be tackled, obviously. > Addressing the snapshot scalability issue first thus seems worthwhile, promising significant benefits on its own. > But there’s also a more fundamental reason for tackling snapshot scalability first: While e.g. addressing some memory usage issues at the same time, as switching the connection model would not at all address the snapshot issue. We would obviously still need to provide isolation between the connections, even if a connection wouldn’t have a dedicated process anymore. > However this means that even if your server can theoretically handle 5000 concurrent reads you will never get there because opening 5000 connections isn't practical. You'll likely hit a memory limit. It's quite possible to have 5000 connections, even leaving poolers aside. When using huge_pages=on, a connection has an overhead of < 2MiB ([2]). Obviously 10GiB isn't peanuts, but it's also not a crazy amount. > Why is this generally accepted as ok? Postgres is an open source project. It's useful in a lot of cases. It's not in some others - partially due to non-fundamental limitations. There's a fairly limited set of developers - we can only work on so many things at a time... > Decoupling concurrency from connections seems possible via pipelining commands over a single connection. Could you expand on what you mean here? > Is it just too late for a project as mature as Postgres? No. It's entirely doable to decouple processes and connections. It however definitely is a large project, with some non-trivial prerequisites. [1] https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/analyzing-the-limits-of-connection-scalability-in-postgres/ba-p/1757266 https://techcommunity.microsoft.com/t5/azure-database-for-po... [2] https://blog.anarazel.de/2020/10/07/measuring-the-memory-overhead-of-a-postgres-connection/ https://blog.anarazel.de/2020/10/07/measuring-the-memory-ove... EDIT: formatting woes

dmw_ng 6y ago
There is a much better reference somewhere (possibly from Ingres times, or later), but here is Stonebraker describing how PostgreSQL ended up with connection-per-process in 1986: > DBMS code must run as a sparate process from the application programs that access the database in order to provide data protection. The process structure can use one DBMS process per application program (i.e., a process-per-user model [STON81]) or one DBMS process for all application programs (i.e., a server model). The server model has many performance benefits (e.g., sharing of open file descriptors and buffers and optimized task switching and message send- ing overhead) in a large machine environment in which high performance is critical. However, this approach requires that a fairly complete special-purpose operating system be built. In constrast, the process-per-user model is simpler to implement but will not perform as well on most conventional operating systems. We decided after much soul searching to implement POSTGRES using a process-per-user model architecture because of our limited programming resources. POSTGRES is an ambitious undertaking and we believe the additional complexity introduced by the server architecture was not worth the additional risk of not getting the system running. Our current plan then is to implement POSTGRES as a process-per-user model on Unix 4.3 BSD. (THE DESIGN OF POSTGRES, https://dsf.berkeley.edu/papers/ERL-M85-95.pdf https://dsf.berkeley.edu/papers/ERL-M85-95.pdf ) There is another reference directly related to Postgres or PostgreSQL that made it even more clear, I expect it was probably later on. In effect it indicated someone involved in the project had strong intentions of getting to adding threading "real soon now". I'll update the comment if I figure out where that's from. Threads were still a research thing by the mid 80s, so its absence from such an old design is easy to understand. Pthreads wasn't even standardized until 1996, although several unices (e.g. SunOS) already had popular implementations long before that.

fulafel 6y ago

Napkin time... 5 TB RAM would still leave 1 GB per connection. Getting enough CPU and IO in a box for 5000 concurrent queries would be much harder than the RAM. I think core counts for conventional x86 servers top out around 128-256 cores (not counting multi box custom interconnect systems that appear to software as SSI)

anarazel 6y ago
It's extremely rare for workloads that need high connection counts to utilize every connection to the extent that they're practically never idle. To the contrary, usually the majority are idle - latency alone leads to that, given the fast queries such workloads commonly have. Not to speak of the applications holding those connections usually also doing other stuff than sending pipelined queries (including just waiting for incoming requests themselves).
- fulafel 6y ago
  The question was originally about 5k concurrent reads. But even for 4500 connections idle you'd still want 500 cores and a monster IO subsystem? (There are middlewares to handle the idle connection pooling more efficiently though.)
  - thdxr 6y ago
    Think you're mixing up parallelism and concurrency. Other databases with connections that support pipelining can easily get to high concurrency even with a single core. The key is the cpu can kick off 100 queries to a single connection and just wake up when a response is available.
    - deleted 6y ago
      [deleted]
    - fulafel 6y ago
      But where this kind of concurrency without corresponding parallelism make sense? There's no point in trying to do thousands of concurrent reads if there's no available parallelism on the same scale, especially keeping transaction / isolation level snapshots open, it's just an overloaded system. Then you want to do backpressure with a maybe some queuing.

voganmother42 6y ago

May be worth noting that pgbouncer is one method of dealing with large connection counts

viraptor 6y ago

> Decoupling concurrency from connections seems possible via pipelining commands over a single connection. Is it just too late for a project as mature as Postgres? This is commonly done using http://www.pgbouncer.org/ http://www.pgbouncer.org/ While it's not built-in, it's a really common solution and can achieve high number of connections. It comes with some issues though (no server-side prepared statements). And yes, it's not exactly the same as pipelining on the server itself since it's still limited by the number of bouncer connections, but there's usually enough idle time to sacrifice some latency for throughput. There's also https://www.pgpool.net/mediawiki/index.php/Main_Page https://www.pgpool.net/mediawiki/index.php/Main_Page but I'm not familiar with the details.

fabian2k 6y ago
I always see people recommending external pools like this here on HN. I'm wondering a bit about that as many frameworks/libraries that use Postgres already implement an internal connection pool. Is this recommendation generally assuming that multiple applications will access the server? Or are there other reasons to prefer an external pooler over internal pooling in your application?
- throwaway189262 6y ago
  Its for IMO, crap languages that don't have proper threading support. Every language with native decent threading implementation has its own connection pool in the client or as an add-on library
- hans_castorp 6y ago
  > Is this recommendation generally assuming that multiple applications will access the server That's very often a reason to use external pools, yes. Or just think of a single web application that runs on multiple nodes to distribute the load on the application servers. You either carefully configure each connection pool or you use an external pooler to which each node connects to (rather than directly to the database)
  - 6y ago

hans_castorp 6y ago

FWIW: Oracle also uses one process per connection on Unix/Linux. I think since Oracle 12 this can be changed during installation, but it's still the default. When using connection pools, this isn't really such a problem in the majority of the cases.

voganmother42 6y ago
In oracle its been an option for a long time(since atleast 10g, I think before), shared vs dedicated servers in their language, and in my experience pretty rare to use shared servers.
- hans_castorp 6y ago
  Shared servers represent a built-in connection pool. They still use one process for each connection. Oracle uses a thread model in Windows (one "orcle" process, each connection is a thread), but not on Unix/Linux.
aeyes 6y ago
Oracle doesn't handle TCP connections, the Oracle Net Listener (a separate process) is responsible for that. It supports connection pooling. I'm no expert on how this scales compared to Postgres as it is hard to find benchmarks, thanks to Oracle licensing terms.

deleted 6y ago

[deleted]

throwaway189262 6y ago

It's kinda bad, but fine if you have low latency from servers to DB. And, if your other servers are very careful about not holding transactions open. That's a big one. You can't be sloppy and hang a connection open with transactions running. This is bad for slow languages where every transaction takes 10+ms. And it's bad if your language doesn't support connection pools/threading. In practice that means you can get full performance with fast threaded languages like Java, C#, Go, Rust that can use connection pooling. But performance will suffer if language is slow or doesn't have good enough threading to do connection pooling. So yeah, it's a bad-ish design that works fine if you're using a fast threaded language. If you're trying to squeeze max performance out of Ruby or something you're going to be disappointed in other ways anyway

mjibson 6y ago

I'll guess: money. Postgres is decades old and was designed when the internet was smaller. Doing a large, fundamental change like this requires an already experienced person (or maybe more than one) to devote a lot of time designing and implementing some solution. This time costs money. So some company must be willing to employ or pay some people to work full-time on this for months. Anyone qualified to work on this should be very expensive, so full costs to pay experts for months of their time would be in the ~$100-200k level. Much outside the donate-a-cup-of-coffee-each-month range, and outside of any small startup's budget, too. This suggests that the various companies employing people to work on Postgres-related stuff (like Microsoft, perhaps due to their purchase of Citus) have more lucrative work they'd rather do instead of improve this at the design level. This problem is now perhaps larger than open source is designed to handle because of how expensive it is to fix. Very few people (zero in this case) are willing to freely donate months of their life to improve a situation that will enrich other companies. Regarding the difficulty of doing this: the blog post here describes how the concurrency and transaction safety model is related to connections, so any connection-related work must also be aware of transaction safety (very scary).

anarazel 6y ago
> This suggests that the various companies employing people to work on Postgres-related stuff (like Microsoft, perhaps due to their purchase of Citus) have more lucrative work they'd rather do instead of improve this at the design level. Well I - the author of this post, employed by MS - did just work quite a while on improving connection scalability. And, as I outlined in the precursor blog post, improving snapshot scalability practically is a prerequisite of changing the connection model to handle significantly larger numbers of connections... Trying to fix things like this in one fell swoop instead of working incrementally tends to not work in my experience. It's much more likely to succeed if a large project can be chopped up into individually beneficial steps.