---image: "Math and Computation/Databases/Postgres/assets/chap_2_indexes.png" publish: true description: "Why Postgres builds tiny, specific cheat sheets to avoid ever reading an entire book." --- ![[chap_2_indexes.png]] # Chapter 2: The Mighty Indexes (Building Cheat Sheets) Reading a whole book just to find one name is the opposite of lazy. If you ask Postgres to find "Sarah", and the only thing you give it is a massive `users` [[Table]] with ten million rows... the elephant sighs heavily, starts at page one, and reads every single line. This is called a `Sequential Scan`, and the elephant absolutely despises doing it. To avoid this mind-numbing labor, Postgres lets you build **Indexes**. An Index is simply a tiny, highly specialized Cheat Sheet. Instead of storing all of your data, the index just stores _one specific thing_ you care about, along with a map directly to the page where the rest of the actual data lives. Because there are many different ways to be lazy, there are many different flavors of Cheat Sheets: - **[[BTree|B-Tree]]**: The classic alphabetized index at the back of a textbook. Perfect for `<, >, =`. - **[[Hash]]**: A magical dictionary where knowing the exact word drops you instantly on the precise page. Perfect for rapid exact `= ` lookups. - **[[GiST]]**: A cheat sheet for people who draw overlapping circles and boxes (Geographic bounding boxes or full-text search). - **[[SPGiST|SP-GiST]]**: Similar to GiST, but for data that doesn't overlap perfectly (like phone numbers or IP addresses). - **[[GIN]]**: The index of a cookbook showing which recipes contain the ingredient "Salt". Perfect for arrays and JSON blobs where one row contains many things. - **[[BRIN]]**: The absolute laziest index. It just looks at the spine of a massive cabinet and says "Prices between $10 and $50 are somewhere in here". It's tiny, vague, but extremely cheap to build. Each index is a trade-off: they require some effort to write/update, but they save immense amounts of effort when reading. The elephant is happy to write a quick sticky note today if it saves him a 10-mile hike tomorrow.