It's the amount of memory a connection has to do work per sorting/hashing operation. https://www.postgresql.org/docs/12/runtime-config-resource.h...
> There's no way for example to say: I want all the connections from username "myapp" to have 10MB and those from user "reporting" to have 100MB.
Yes, you can with
`ALTER ROLE myapp SET work_mem TO '10mb';`
and
`ALTER ROLE reporting SET work_mem TO '100mb';
Some days it's nice to have a lucky 10k problem (https://xkcd.com/1053) in place of a c10k problem (http://www.kegel.com/c10k.html). Thanks.
Managed databases in the cloud are a bit like ORMs: they significantly reduce the amount of knowledge needed 99% of the time. Sadly, when the 1% of the time hits this also means that nobody on the team has built up the DBA skills required for the big hairy problem. I see this pattern repeated all the time when consulting for startups that just had their first real traction and now the magic database box is no longer working as before.
But I don't see this model much. I mostly hear of companies struggling to develop internal expertise or going without.
Why?
I've pretty much completely changed careers now, but I found that this model did work well. It involved a lot of constant research and occasionally a huge amount of stress, but I loved it!
It does exist (I'm one of them), and it's much more common on expensive databases like Microsoft SQL Server and Oracle. With free databases, it's relatively inexpensive to throw more hardware at the problem. When licensing costs $2,000 USD per CPU core or higher, there's much more incentive for companies to bring an expert in quickly to get hardware costs under control.
Managers don't like paying for optimization consultants, but it's the easiest way to both improve current performance and scale a master.
Note that Oracle positions MySQL as a replacement for SQL Server, as "moderate-performance databases." :)
Source: DBA.
https://metrixdata360.com/pricing-series/1500-price-increase...
I got you fam: https://ottertune.com/
You have bought your 1000 hps car and are driving it as-is since you left the car dealer's parking place. Nobody told you to release the hand brakes.
I call this the Kubernetes effect. 99% of the time, people think it's easy. Then the 1% hits and they have to completely rebuild it from the ground up. For us it was cluster certificate expiration and etcd corruption, and some other problems we couldn't ever pin down.
The other effect (which I'm sure has a name) is when they depend on it for a long time, but then a problem starts to occur, and because they can't figure out a way to solve it, they move to a completely different tech ("We had problems with updating our shards so we moved to a different database") and encounter a whole new problem and repeat the cycle.
Number of Connections * work_mem is usually going to eat up the biggest chunk of your PostgreSQL's memory. AFAIK, the configuration is extremely coarse. There's no way for example to say: I want all the connections from username "myapp" to have 10MB and those from user "reporting" to have 100MB. And it can't be adjusted on the fly, per query.
Being able to set aside 200GB of memory to be used, as needed, by all connections (maybe with _some_ limits to prevent an accident), would solve a lot of problems (and introduce a bunch of them, I know).
Since they're on RDS, I can't help but point out that I've seen DB queries on baremetal operate orders of magnitude faster. 10 minute to 1 second type thing. I couldn't help but wonder if they'd even notice the disk-based sorting on a proper (yes, I said it) setup.
(1) https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Serv...