You watched CMU's intro to Database sytems, right? It's really good and thorough. It will save you of some common pitfalls and can help you navigate the trade-offs.
I really have liked the current Optimizer series. So much amazing info.
I hope some company that heavily relies on Postgres sponsors a project to replace the optimizer with a more modern implementation. I feel like for any large applications with olap style queries (even if on the OLTP database) will benefit incredibly from that work. SQL Servers optimizer was light-years ahead of Postgres when I worked with it last over a decade ago.
Sadly, most of those corporations would rather spend millions in proprietary solutions than give back 100k to the community. I've seen it first hand many times. They only give money if they absolutely have to.
I also recommend "Database Internals" to people. An invaluable book, the logical next read after "Designing Data-Intensive Applications".
It's the best and most approachable resource on distributed systems and database consistency / performance tradeoffs I have found.
I hope some company that heavily relies on Postgres sponsors a project to replace the optimizer with a more modern implementation. I feel like for any large applications with olap style queries (even if on the OLTP database) will benefit incredibly from that work. SQL Servers optimizer was light-years ahead of Postgres when I worked with it last over a decade ago.