Introduction to PostgreSQL Indexes (dlt.github.io)

Essential reading. More in-depth than an introduction, but without being overly impenetrable except to those dealing with the internals.

This looks really awesome for Postgres

For general B Tree index resources this has been my got to site for years https://use-the-index-luke.com/

Linking to the postgresql docs since they are very well written and surprisingly enjoyable to read.

https://www.postgresql.org/docs/current/indexes-intro.html

The section on multi-column indexes mirrors how I was taught and how I’ve generally handled such indexes in the past. But is it still true for more recent PG versions? I had an index and query similar to the third example, and IIRC PG was able to use an index, though I believe it was a bitmap index scan.

I am also unsure of the specific perf tradeoffs between index scan types in that case, but when I saw that happen in the EXPLAIN plan it was enough for me to call into question what had been hardcoded wisdom in my mind for quite some time.

Further essential reading is the classic Use The Index, Luke [0] site, and the book is a great buy for the whole team.

0: https://use-the-index-luke.com/

It would be nice to see out-of-the-box support in PostgreSQL for what's known as incremental view maintenance. It's very much an index in that it gets updated automatically when the underlying data changes, but it supports that for arbitrary views - not just special-cased like ordinary database indexes.

Related, Use the Index Luke

https://use-the-index-luke.com/

Is there a use-the-index-luke for MongoDB...?

I love this style of writing. Simple, humble and direct transfer of knowledge.

Good timing for this article. The multi-column index advice was always confusing because the "leading column" rules had real performance implications, but bitmap index scans made it less catastrophic than the textbooks suggested.

Skip scan in PG 18 changes a lot of that conventional wisdom. Worth updating the mental model for anyone who learned indexing on older versions.

The whole btree vs hash discussion is interesting. Many people assume "ID" columns should be hash, but iirc the default btree works best for those. Also treelike structures are fundamentally better for nearly-sequential value insertion.

The blog post that this links to comes to the opposite conclusion though, showing hash winning the benchmarks.