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

51–60 of 94 posts

Re: Postgres Postmaster does not scale

#51

Post by namibj »

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.

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.

Re: Postgres Postmaster does not scale

#52

Post by the_mitsuhiko »

rsanek wrote: post #46
the_mitsuhiko wrote: post #42

I'm not working at this company but I found that these types of problems can often be simplified in the architecture. > Most meetings start on the hour, some on the half, but most on the full. It sounds obvious to say it aloud, but the implication of this has rippled through our entire media processing infrastructure. When you can control when it happens, you can often jitter things. For instance the naive approach o…

They mention that they implemented jitter later in the post.

But my reading of their jitter is a very narrow one for the actual connection to the database. They are still doing most of the work on the minute.

Re: Postgres Postmaster does not scale

#53

Post by TheDong »

direwolf20 wrote: post #48
TheDong wrote: post #43

Earlier quoted context omitted.

> 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 If you happen to have moreutils installed, you can do that with pee echo $NUM_PAGES | sudo pee 'cat > /proc/sys/vm/nr_hugepages'

why not write sh -c then?

Because 'sh' isn't in moreutils and is harder to remember, next question.

Why use something portable when you can use cat pee?

Re: Postgres Postmaster does not scale

#54

Post by ants_a »

citrin_ru wrote: post #49
paulkre wrote: post #28

Can’t believe they needed this investigation to realize they need a connection pooler. It’s a fundamental component of every large-scale Postgres deployment, especially for serverless environments.

In serverless world for sure but in old-school architecture it's common to use persistent connections to a database which make connection pooler less essential. Also the last time I did check (many years ago admittedly) connection poolers didn't play well with server-size prepared statements and transactions.

pgbouncer added support for prepared statements a couple years back.

Re: Postgres Postmaster does not scale

#55

Post by wreath »

darkwater wrote: post #38
evanelias wrote: post #3

Earlier quoted context omitted.

Also check out ProxySQL [1][2], it's an extremely powerful and battle-tested proxy. Originally it was only for MySQL/MariaDB, where it is very widely used at scale, even despite MySQL already having excellent built-in scalable threaded connection management. But ProxySQL also added Postgres support too in 2024 and that has become a major focus. [1] https://proxysql.com/ [2] https://github.com/sysown/proxysql

+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.

Re: Postgres Postmaster does not scale

#56

Post by IsTom »

namibj wrote: post #51
IsTom wrote: post #47

Earlier quoted context omitted.

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

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?

Re: Postgres Postmaster does not scale

#57

Post by ants_a »

PunchyHamster wrote: post #34

> We record millions of meetings every week. My first thought was "why even use big databases, you have perfect workload to shard it between a bunch of instances and as a bonus any downtime would only affect smaller part of customers"

This is not a big database usecase. It just needs one to not do silly things like opening a new database session for every query when it's well documented that this is expensive.

Re: Postgres Postmaster does not scale

#58

Post by nextaccountic »

thayne wrote: post #29
atherton94027 wrote: post #7

I'm a bit confused here, do they have a single database they're writing to? Wouldn't it be easier and more reliable to shard the data per customer?

Sharding is often not easy. Depending on the application, it may add significant complexity to the application. For example, what do you do if you have data related to multiple customers? How do you handle customers of significantly different sizes? And that is assuming you have a solution for things like balancing, and routing to the correct shard.

deja vu

did you comment exactly the same things some months ago?

Re: Postgres Postmaster does not scale

#59

Post by iamleppert »

Why do you need a connection to a database during the meeting? Doesn't it make more sense to record the meeting data to some local state first, and then serialize it to database at the end of the meeting or when a database connection is available? Or better yet, have a lightweight API service that can be scaled horizontally that is responsible for talking to the database and maintains its own pool of connections.

They probably don't even need a database anyway for data that is likely write once, read many. You could store the JSON of the meeting in S3. It's not like people are going back in time and updating meeting records. It's more like a log file and logging systems and data structures should be enough here. You can then take that data and ingest it into a database later, or some kind of search system, vector database etc.

Database connections are designed this way on purpose, it's why connection pools exist. This design is suboptimal.

Re: Postgres Postmaster does not scale

#60

Post by Twirrim »

paulkre wrote: post #28

Can’t believe they needed this investigation to realize they need a connection pooler. It’s a fundamental component of every large-scale Postgres deployment, especially for serverless environments.

Pooling connections somewhere has been fundamental for several decades now.

Fun quick anecdote: a friend of mine worked at an EA subsidiary when Sim City (2013) was released, to great disaster as the online stuff failed under load. Got shifted over to the game a day after release to firefight their server stuff. He was responsible for the most dramatic initial improvement when he discovered the servers weren't using connection pooling, and instead were opening a new connection on almost every single query, using up all the connections on the back end DB. EA's approach had been "you're programmers, you could build the back end", not accepting games devs accurately telling them it was a distinct skill set.

Post reply on HN