Optimizing column layout has been enough for me to leave OLAP workloads in Postgres a few times when I otherwise would've had to spin up something dedicated for them.

Lots of things in PGSQL benefit from thinking about how your use case is manipulating the underlying B-tree, if you work in performance sensitive stuff the devil is really in the details. And having to rebuild indexes that have decayed over time is painful.

Erm. Isn't this kind of a schema specific question ?

I mean, sometimes I see schemas that are so goddam awful that the answer to your question would be "anything, literally anything".

Other times its the old story of failing to use or misconfiguring indexes.

And then, yes you end up in the realms of Postgres specific stuff. What you do there clearly depends on what you're looking at.

But the point is, I could probably count on one hand the number of times the problem has been "the storage" or "the database". Most of the time its "the developer". :D