As usual, it depends on the scale, but it's a sane default for 99% of use cases.

Different use cases have different scalability limits in PG, when you get to them you need to deal with them.

It would be perfect if it had somewhat transparent sharding, I mean a way to add another instance and distribute load without having to stop everything.

There are solutions, but they tend to be involved and when you get to that point in many cases it makes sense to just move that workload to something else that scales better.