Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Postgres Postmaster does not scale

recall.ai

Post reply on HN
Print view View original

81–90 of 94 posts

Re: Postgres Postmaster does not scale

#81

Post by namibj »

vbezhenar wrote: post #65
namibj wrote: post #51

Earlier quoted context omitted.

Much of the time in a transaction can reasonably be non-db-cpu time, be it io wait or be it client CPU processing between queries. Note I'm not talking about transactions that run >10 seconds, just ones with the queries themselves technically quite cheap. At 10% db-CPU-usage, you get a 1 second transaction from just 100ms of CPU.

In a properly optimized database absolute majority of queries will hit indices and most data will be in memory cache, so majority of transactions will be CPU or RAM bound. So increasing number of concurrent transactions will reduce throughput. There will be few transactions waiting for I/O, but if majority of transactions are waiting for I/O, it's either horrifically inefficient database or very non-standard usage.

If you aren't hitting IO (I don't mean HDDs) on a large fraction of queries you either skipped a cache in front of the DB or your data is very small or you spent too much on RAM and too little on your NVMe being not a bottleneck.

Re: Postgres Postmaster does not scale

#82

Post by namibj »

IsTom wrote: post #56
namibj wrote: post #51

Earlier quoted context omitted.

Much of the time in a transaction can reasonably be non-db-cpu time, be it io wait or be it client CPU processing between queries. Note I'm not talking about transactions that run >10 seconds, just ones with the queries themselves technically quite cheap. At 10% db-CPU-usage, you get a 1 second transaction from just 100ms of CPU.

That many long-running transactions seem like a pretty unusual workload to me and potentially running into isolation issues. I can see running a few of these, but not a lot, especially at the same time?

A few (single digits, maybe low double digits) per database CPU core isn't necessarily too much, of course the pattern can be conflict-heavy, where it would be problematic, but this is not about absolute concurrency but about concurrency needed to saturate CPU.

Re: Postgres Postmaster does not scale

#83

Post by namibj »

vbezhenar wrote: post #78
CodesInChaos wrote: post #73

Earlier quoted context omitted.

Your arguments make sense for concurrent queries (though high-latency storage like S3 is becoming increasingly popular, especially for analytic loads). But transactions aren't processing queries all the time. Often the application will do processing between sending queries to the database. During that time a transaction is open, but doesn't do any work on the database server.

It is bad application architecture. Database work should be concentrated in minimal transactional units and connection should be released between these units. All data should be prepared before unit start and additional processing should take place after transaction ended. Using long transactions will cause locks, even deadlocks and generally should be avoided. That's my experience at least. Sometimes business transa…

Your database usage should not involve application-focused locks, MVCC will restart your transaction if needed to resolve concurrency.

Re: Postgres Postmaster does not scale

#84

Post by thayne »

wang_li wrote: post #79
thayne wrote: post #20

Earlier quoted context omitted.

Or echo $NUM_PAGES | sudo tee /proc/sys/vm/nr_hugepages I've always found it odd that there isn't a standard command to write stdin to a file that doesn't also write it to stdout. Or that tee doesn't have an option to supress writing to stdout.

It’s not an option or feature because it’s built into the shell. Just slap a > /dev/null on the end.

That works, but it feels wasteful to needlessly copy it, and it is verbose.

Re: Postgres Postmaster does not scale

#85

Post by hu3 »

IsTom wrote: post #68
hu3 wrote: post #64

Earlier quoted context omitted.

I disagree. If that was the case, pgBouncer wouldn't need to exist. The problem of resource usage for many connections is real.

It's about queueing work, not running all these queries at the same time. You can run pgbouncer or you can have a pool on your backend. Having more connections won't make it go faster, so that really seems like a low-priority thing for postgres to me. Even if you integrated pooling into postgres the overhead of auth would be still taking time for small queries anyway.

that's too simplistic.

there are many reasons to need something like pgbouncer.

1) not all workloads lend themselves to backend pools

2) not all backends can afford pooling

3) you don't always control your backend

4) you might have a lot of different backends connecting to the database. it's much simpler to just tell them to connect to pgbouncer.

5) you don't want to trust backend team to not bring postgresql down with many simultaneolus connections.

6) backend pools often misbehave

people don't wake up one day and just add another cog to their infrastructure randomly.

Re: Postgres Postmaster does not scale

#86

Post by hinkley »

PunchyHamster wrote: post #35
hinkley wrote: post #23

Earlier quoted context omitted.

When one customer is 50 times bigger than your average customer then sharding doesn't do much.

It does if you have thousands of customers.

Orders of complexity don’t give a tinker’s damn about how many other customers you have.

Re: Postgres Postmaster does not scale

#88

Post by jstrong »

IsTom wrote: post #47
jstrong wrote: post #31

Earlier quoted context omitted.

can't believe postgres still uses a process-per-connection model that leads to endless problems like this one.

You can't process significantly many more queries than you've got CPU cores at the same time anyway.

redis is single-threaded but handles lots of connections (i.e. > 500) with much better performance vs. postgres. there's zero chance someone building postgres in 2025 would do one process per connection, I don't think there's any argument that it's a good design for performance. it's just a long-ago design choice that would be difficult to change now.

Re: Postgres Postmaster does not scale

#89

Post by moomoo11 »

sgt wrote: post #25
moomoo11 wrote: post #14

maybe this is silly but these days cloud resources are so cheap. just loading up instances and putting this stuff into memory and processing it is so fast and scalable. even if you have billions of things to process daily you can just split if needed. you can keep things synced across databases easily and keep it super duper simple.

It's not really my experience that cloud resources are very cheap.

I guess I’m talking about a company actually making money.

I would much rather spend 5k per month to make 1 million, keeping things extremely simple.

Re: Postgres Postmaster does not scale

#90

Post by darkwater »

wreath wrote: post #55
darkwater wrote: post #38

Earlier quoted context omitted.

+1 to ProxySQL, especially in RDS environments with huge monoliths attached that open a shitload of threads. RDS has fixed max_connections depending on the instance size so if you don't want to pay $$$$ for bigger but underused instances - and while you are trying to get the devs update all the hundreds old dependencies in the monolith to improve it, ProxySQL - can save your day. It did, for me. And yes, it's a self-…

You can change the max_connections in RDS though. The default is insanely high and I have no idea what it is that way. 4vCPU instances running with 5k max connections iirc, I have never seen an instance this size handle more than 100-200 concurrent connections on a CPU bound workload.

There is still a max capped on the instance size [1] (well, RAM available) although now that I'm checking it again I swear it was different last time I read it. Anyway I did totally had issues with big replicated monoliths with their own connection pool (i.e. old Rails) maxing out connections on RDS.

[1] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_...

Post reply on HN