Web Dev
Four Postgres Indexes I Wish I'd Understood Earlier
A 2.4s query became 11ms after one index. Here's how I reason about B-tree, partial, composite, and GIN indexes now.
Edwine Nyakundi
Full stack engineer · Aug 28, 2026 · 11 min read
For my first two years writing backends, indexes were something I added when things felt slow. That's roughly the same strategy as adding salt until food tastes right — it works, but you learn nothing.
Read the plan before you touch anything
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title FROM posts
WHERE author_id = $1 AND published = true
ORDER BY created_at DESC
LIMIT 20;A Seq Scan over a large table with a tiny LIMIT is the classic signal. The planner is reading everything to hand you twenty rows.
Composite order matters
Put equality columns first, then the range or sort column. The index below serves the filter and the ORDER BY in one pass.
CREATE INDEX posts_author_published_created_idx
ON posts (author_id, published, created_at DESC);Partial indexes are underrated
If 95% of queries only look at published rows, don't index the drafts. Smaller index, better cache hit rate.
CREATE INDEX posts_published_created_idx
ON posts (created_at DESC)
WHERE published = true;- B-tree: the default, good for equality and ranges.
- Partial: same as above but scoped by a WHERE clause.
- Composite: multi-column, order is not cosmetic.
- GIN: arrays, JSONB, and full-text search.
Measure before and after, every time. An index you can't justify with a plan is just write amplification you haven't noticed yet.
Related posts
Why Your React App Re-renders (And How to Actually Find Out)
Memoizing everything is not a performance strategy. Here's how I profile a slow React page and fix the real cause.
Jun 11, 2026 · 10 min read
Handling Large File Uploads with Node.js Streams
Buffering a 2GB upload into memory is a great way to meet your on-call rotation. Streams fix it in about thirty lines.
Jul 19, 2026 · 8 min read
Building a Type-Safe API Layer in React with TypeScript
Stop sprinkling `any` across your fetch calls. Here's the small, boring pattern I use on every production app to keep server data honest.
Sep 12, 2026 · 9 min read
Newsletter
Get new posts in your inbox
Deep dives on TypeScript, Postgres, and engineering careers — roughly twice a month. No courses to sell, no spam.