Hacker News Reader

Comment by comboy | Hacker News Reader

I've never really been limited by CPU when running postgres (few TB instances). The bottleneck is always IO. Do others have different experience? Plus there's elegance and a feeling of being in control when you know query is associated with specific process which you can deal with and monitor just like any other process.

But I'm very much clueless about internals, so this is a question rather than an opinion.

View on HN

I see postgres become CPU bound regularly: Lots of hash joins, copy from or to CSV, index or materialized view rebuild. Postgis eats CPU. Tds_fdw tends to spend a lot of time doing charset conversion, more than actually networking to mssql.

I was surprised when starting with postgres. Then again, I have smaller databases (A few TB) and the cache hit ratio tends to be about 95%. Combine that with SSDs, and it becomes understandable.

Even so, I am wary of this change. Postgres is very reliable, and I have no problem throwing some extra hardware to it in return. But these people have proven they know what they are doing, so I'll go with their opinion.

I've also definitely seen a lot of CPU bounding on postgres.

It's not just CPU - memory usage is also higher. In particular, idle connections still consume signficant memory, and this is why PostgreSQL has so much lower connection limits than eg. MySQL. Pooling can help in some cases, but pooling also breaks some important PostgreSQL features (like prepared statements...) since poolers generally can't preserve session state. Other features (eg. notify) are just incompatible with pooling. And pooling cannot help with connections that are idle but inside a transaction.

That said, many of these things are solvable without a full switch to a threaded model (eg. by having pooling built-in and session-state-aware).

> solvable without a full switch to a threaded model (eg. by having pooling built-in and session-state-aware).

Yeeeeesssss, but solving that is solving the hardest part of switching to a threaded model. It requires the team to come terms with the global state and encapsulating session state in a non-global struct.

> That said, many of these things are solvable without a full switch to a threaded model (eg. by having pooling built-in and session-state-aware).

The thing is that that's a lot easier with threads. Much of the session state lives in process private memory (prepared statements etc), and it can't be statically sized ahead of time. If you move all that state into dynamically allocated shared memory, you've basically paid all the price for threading already, except you can't use any tooling for threads.

I've generally had buffer-cache hit rates in the 99.9% range, which ends up being minimal read I/O. (This is on AWS Aurora, where these bo disk cache and so shared_buffers is the primary cache, but an equivalent measure for vanilla postgres exists.)

In those scenarios,there's very little read I/O. CPU is the primary bottleneck. That's why we run up as many as 10 Aurora readers (autoscaled with traffic).

>I've never really been limited by CPU when running postgres (few TB instances). The bottleneck is always IO.

Throw a few NVMe drives at it and it might.

Throw a ridiculous amount of RAM at it is more correct assessment. NVMe reads are still an “I/O” and that is slow. And for at least 10 years buying enough RAM to have all off the interesting parts of OLTP psql database either in shared_buffers or in the OS-level buffer cache is completely feasible.

> NVMe reads are still an “I/O” and that is slow

It's orders of magnitude faster than SAS/SATA SSDs and you can throw 10 of them into 1U server. It's nowhere near "slow" and still easy enough to be CPU bottlenecked before you get IO bottlenecked.

But yes, pair of 1TB RAM servers gotta cost you less than half year's worth of developer salary

an array of modern SSDs can get to a similar bandwidth to RAM, albeit with significantly worse latency still. It's not that hard to push the bottleneck elsewhere in a lot of workloads. High performance fileservers, for example, need pretty beefy CPUs to keep up.

Depends on your queries.

If you push a lot of work into the database including JSON and have a lot of buffer memory...CPU can easily be limiting.

With modern SSDs that can push 1M IOPs+, you can get into a situation where I/O latency starts to become a problem, but in my experience, they far outpace what the CPU can do. Even the I/O stack can be optimized further in some of these cases, but often it comes with the trade off of shifting more work into the CPU.

Postgres uses lots of cpu and memory if you have many connections and especially clients that come and go frequently. Pooling and bouncers help with that. That experience should better come out of the box, not by bolting on tools around it.

Reply on news.ycombinator.com