How to Learn PostgreSQL Indexes (Without Memorizing Charts You'll Forget)

You look up B-tree vs GIN for the third time this month. The Stack Overflow answer makes sense. You pick an index, ship the migration, close the tab.

Two sprints later someone asks why that query is slow, and the chart in your head is blank again.

That is the real indexing problem. Not picking the first index. Keeping the mental model after you stop staring at docs.

Why charts evaporate

Index types are easy to skim and hard to retain because they feel similar until one query proves they aren't.

B-tree covers the equality and range lookups you write every day. Hash is niche. GIN shows up for arrays and full-text. GiST appears in geospatial and other "weird shape" data. BRIN is for huge append-only tables where you care about cheap coverage, not perfect selectivity.

Reading that paragraph once does almost nothing. Your brain files it under "I saw this" and moves on. Next time you open EXPLAIN ANALYZE, you still guess.

The fix is not a prettier cheat sheet. It is a loop: save the example, pull it back later, answer from memory, then check yourself.

Save the examples that actually teach you

When an index finally clicks, it is usually because of one concrete query, not a table of tradeoffs.

Keep those moments:

• the EXPLAIN plan where a sequential scan flipped to an index scan after you added the right column order
• the GIN index that made an @> array check usable
• the composite index that only helped once the leftmost columns matched the WHERE clause
• the note you wrote after reading why an index on a low-cardinality boolean barely moved the needle

Dumping those into a bookmarks folder fails for the same reason Watch Later fails. The save is frictionless. The revisit never happens.

I use Recl for this. Share a Postgres docs page, a blog post, or a YouTube walkthrough of EXPLAIN, and it lands as a short summary with key takeaways instead of another unread link. The point is not collecting more Postgres tabs. It is having something you can quiz yourself on next week.

Build a tiny index checklist you can recite

Skip the giant matrix. Build five questions you can answer cold:

1. Is this lookup equality, range, or "contains"?
2. Do I need the leftmost columns of a composite index to match the filter?
3. Is the column selective enough that an index beat a seq scan?
4. Am I filtering on an expression (LOWER(email)) without an expression index?
5. Will writes get painful if I add another index to a hot table?

If you can answer those without opening a tab, you are ahead of most people who "know" indexing from a one-hour course.

Write your answers in plain language next to a real query from your app. Vague notes like "use GIN for JSON" fade. "This tags @> ARRAY['urgent'] filter needed GIN; B-tree did nothing useful" sticks.

Practice with your own slow queries

Tutorial schemas are tidy. Your production-ish staging DB is not.

Pick three slow queries from the last month. For each one:

• run EXPLAIN (ANALYZE, BUFFERS)
• write one sentence on what the planner chose
• change one index (or the query shape)
• save both plans and the one-line conclusion

That archive becomes more valuable than any generic indexing guide, because it is tied to tables you actually maintain.

If you learn from conference talks or long docs pages, same rule: capture the example, not the vibe. Recl's library sorts saves into topics, so your Postgres notes do not drown next to random frontend bookmarks.

Put recall on a schedule

You will forget the difference between index-only scans and bitmap heap scans. Everyone does. The trick is getting a nudge before the next incident review.

Spaced repetition sounds heavy until you shrink it. One push the next day asking whether a composite index helps a filter on the second column alone. Another a few days later. A short weekly quiz on what you saved.

That is what Recl's recall flow is for: a nudge like "Still remember this one?", a quick check, then you move on. You are not maintaining Anki decks for every CREATE INDEX blog you skimmed. You are reopening the exact saves that mattered.

Ask-your-library helps too. When you are mid-PR and cannot remember why you avoided indexing that JSONB path last time, you can ask from your own notes instead of re-Googling the same thread.

A week-long practice plan that fits real work

Day 1: Save three index explanations tied to real queries (docs, posts, or talks). Read the summaries once.

Day 2: Without looking, answer: B-tree vs GIN for your most common filter shape. Check yourself.

Day 3: Add or adjust one index in staging. Save the before/after EXPLAIN.

Day 4: Explain composite index column order out loud in one minute. If you stall, reopen the save.

Day 5: Skip. Letting a little forgetting happen is part of the point.

Day 6 or 7: Do a five-minute quiz on the week's saves. Delete anything that was noise.

Repeat next week with partial indexes, covering indexes, or REINDEX tradeoffs. Small loops beat annual cram sessions.

What "knowing indexes" actually looks like

It is not reciting every PostgreSQL index type on command. It is recognizing the shape of a filter, knowing which question to ask next, and having your past examples close enough that you do not start from zero.

Memorizing charts fails because charts are someone else's compression of the idea. Your own slow query, your own plan, your own one-line note: that is the version that survives a busy month.

If your learning life is scattered across docs tabs, saved Reddit threads, and half-watched database talks, put the keepers somewhere that brings them back. Recl is built for that loop: save what matters, get the gist, then recall it on a schedule so the next indexing decision is yours again, not a frantic search.

Asutosh builds Recl, a save-and-recall app for the links, videos, and notes you mean to learn from. More at recl.app.