How Do PostgreSQL Indices Work, Anyways?

You've probably created a hundred indexes in your career. Maybe a thousand. You ran EXPLAIN ANALYZE, saw "Index Scan" instead of "Seq Scan," pumped your fist, and moved on. But do you actually know what's happening underneath? Because once you do, a lot of things about PostgreSQL performance start to make a lot more sense. And some of the pain points you've been fighting start to feel less like mysteries and more like, well, physics. It's a tree. Obviously. The default index type in PostgreSQL is a B-tree. You knew that. But let's talk about what that actually means for your data. When you create an index on, say, a timestamp column, PostgreSQL builds a balanced tree structure where each node contains keys and pointers. The leaf nodes point to actual heap tuples (your rows on disk). The internal nodes just help you navigate. Think of it like a phone book. (Do people still know what phone books are? I'm aging myself.) The key thing to understand: the index is a separate data structure f