Ask HN: Indepth Introduction to RDBMS (Or Postgres) Internals

I remember reading about B-Trees and B+ Trees in CS classes and how their structures allow good (amortized?) disk reads , making them good for indices.

But in the last ~10 years, storage technology has changed a lot and RDBMS like postgres have been pushing the limits of what was thought to be achievable using traditional databases.

While new fangled, distributed DBMS' are getting better, they have a lot of tradeoffs and it is very hard to understand what I am trading off for what advantages I get from something like say, CockroachDB.

The thing that I find frustrating is I still don't understand a lot about how RDBMS' work (table layout on disk/ssds, indices, joins).

Over my past 5+ or so years of work experience, I have learnt from first hand and my teams' experience that RDBMS are quite capable in the right hands if one knows what one is doing.

It is hard to understand tradeoffs, performance, guarantees in an Internet rife with "X is infinitely scalable" filler content (no blaming any db/product as marketing is as important as engg.), but as a developer, My long-term value is , in some ways, proportional to how well I know systems.

To that effect, can anyone recommend resources that offer in-depth explanations on DBMS' internals (indexing, table layouts in memory and on disk, how joins are processed, transactions ,etc) ?