News for hackers. Stuff that matters.
Re:Improving Postgres Connection Scalability: Snapshots by fulafel on Tuesday October 27, 2020 @05:46AM (#24905804)
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.)
Re:Improving Postgres Connection Scalability: Snapshots by anarazel on Tuesday October 27, 2020 @01:30PM (#24909707)
> But even for 4500 connections idle you'd still want 500 cores and a monster IO subsystem?

Entirely depends on your workload:

These days it's very common to have the hot working set fit into the database server's buffer cache - in which case there will be little IO. And even in cases where you have a lot of IO - a decent NVMe SSD can do several 100k IOPS (with some doing more than 1M IOPS), with per IO latencies in the mid two digit microsecond range.

And because common queries can be processed within fractions of a millisecond, often 500 busy connections can be handled by a few cores.

Re:Improving Postgres Connection Scalability: Snapshots by fulafel on Wednesday October 28, 2020 @01:26AM (#24915528)
> And because common queries can be processed within fractions of a millisecond, often 500 busy connections can be handled by a few cores.

If even 500 (out of 4500) connections are busy, what's the catch? Isn't this just a lot of scheduled CPU work trying to run concurrently when it would be faster if queued? Fast NVMe IO, or the fact that the queries are a few ms each, are just factors that makes the workload more CPU intensive (vs IO bound), the scenario would be more plausible if you had slow disks :)

I guess being bottlenecked by the network can be one scenario if your DB and app aren't close and/or your queries involve bulk data like GIS shapes...

Re:Improving Postgres Connection Scalability: Snapshots by thdxr on Tuesday October 27, 2020 @09:11AM (#24907039)
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.
Re:Improving Postgres Connection Scalability: Snapshots by anarazel on Tuesday October 27, 2020 @01:14PM (#24909489)
> 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.

Which RDBMS implement a protocol with tagged queries? I don't think that's common. From my, quite fallible, memory, the DB protocols supporting pipelining all just do in-order query execution. As I mentioned before, postgres does support that.

The TCP connection piece of a database connection isn't really expensive - that's the transaction and query execution state. These days (used to be different) it's not that hard to write an application handling 10s-100s of thousands of TCP connections, as long as most of those aren't continually sending data.

Obviously that's where the process-per-connection limitations for postgres come in - having that several 100k processes isn't really a good option. Not even just because of the process overhead, but even just the context switch and process scheduler overheads become more and more debilitating.

But even if postgres' connection model were switched to many-connections-per-process/thread - you still need to have the per-connection state somewhere; obviously transactional semantics need to continue to work. And the per-connection transaction state is where the snapshot scalability limitation the article is talking about was. So the snapshot scalability issue needed to be fixed before a connection model switch really made sense.

Re:Improving Postgres Connection Scalability: Snapshots by fulafel on Tuesday October 27, 2020 @11:07AM (#24908127)
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.