Fermi Estimates on Postgres Performance
citusdata.com
citusdata.com
This post just notes some back of the envelope math I do in my head in terms of what to expect with a moderate box (r3.2xl or so). It is not the limit of how far a single node PG can go and I'm sure you can go much higher with a very well tuned PG on larger boxes :)
>Ability to aggregate 1-2 million rows per second on a single core
This almost makes it sound as if increasing to N cores will increase the ops by N * rows per second. But it won't, not even close, not when you are reading from the same hard drive/SSD card. If you are basing your calculation on max I/O output, then additional CPU cores will make little to no difference or worse would decrease performance.
In practice for plans involving seqscans and such you'll very commonly hit CPU bottlenecks before you hit IO limitations. Especially if you aggregate with a few aggregates. We (the pg community) are working on making that less of a bottleneck, but it's still a common occurrence.
The point here is that you have multiple constraints, including IO and CPU, and you can roughly calculate which ones you'll hit first.
I think you missed the part where OP said,
"In general, you should aim to have a cache hit ratio of 99% or higher"
You sound like you have PTSD from under powered hardware and a badly configured database.
Today is Enrico Fermi's birthday: https://en.wikipedia.org/wiki/Enrico_Fermi