> Database locks is usual issue. Avoidable if planned better.
I've often seen problems because of N+1 querying in particular, but maybe that's just the majority of codebases that I've seen.
> Memory is second. In 15 years CPU has never been a real concern.
In most stacks that I've worked in, memory seems to be the main cause of problems: be it a Ruby application, a PHP one, Python one or Java.
Though perhaps that's because larger servers are expensive and development environments in particular tend to be on the more conservative side of things. Either that, or the apps that I've worked with are good examples of Wirth's law and refuse to even run with less than 2 GB of RAM (though maybe that's more along the lines of commentary about enterprise Java apps in particular). When your development/test server has something like 8 GB of RAM or your computer has 16 GB of RAM and you need to run 7 or 8 of those apps (as well as IDE instances), you run into problems.
CPU is generally not an issue for most applications, though various databases and data stores instead. Perhaps it's batch migrations or just lots of in-database processing, or honestly just most cases where you use ElasticSearch - not only does it seem to eat as much RAM as you'll give it, but the same seems to apply to the CPU (especially when used as the data store for something like Skywalking APM).
On the bright side: most of the modern tech stacks are generally reasonable and there are often micro frameworks (or just more lightweight ones) to be found, which can be suitable for some use cases. Don't want Rails, use Sinatra. Don't want Laravel, use Lumen. Don't want Django, use Flask. Don't want Spring Boot, use Quarkus. That said, Node with Express.js and .NET with ASP.NET are pretty lightweight out of the box, which is nice. Of course, if you go down this route, you might end up with something that is better suited to a bare API, instead of fancy features like server side rendering/templating etc.