Indexes

If WHERE email = ? is your login lookup and it's slow, you probably forgot an index. Or the table is tiny and you're imagining things 🤔

What an index is (FE analogy)

Think of a book index: instead of scanning every page for "pagination", you jump to the page number.

A database index is a separate structure (often a B-tree) that maps column values → row locations. Speeds up lookups, filters, joins, and sorts - at the cost of extra storage and slower writes (index must update too).

Creating one

CREATE INDEX idx_contacts_email ON contacts(email);

CREATE INDEX idx_contacts_company_id ON contacts(company_id);

-- composite: useful when you filter by both
CREATE INDEX idx_deals_stage_owner ON deals(stage, owner_id);

Unique indexes also enforce uniqueness:

CREATE UNIQUE INDEX idx_contacts_email_unique ON contacts(email);
-- (UNIQUE constraint on the column already creates one under the hood)

When you want one

Good candidates:

• columns in WHERE / JOIN … ON that you hit constantly (email, company_id, owner_id)
• foreign keys (joining parents/children)
• sort + filter combos for list pages (stage, updated_at)

Skip / wait on:

• tiny tables (full scan is fine)
• columns that almost never appear in filters
• low-selectivity flags alone (is_active on a table that's 95% active) - sometimes useful in composites, rarely alone

List page mental model

SELECT id, title, amount, stage
FROM deals
WHERE stage = 'negotiation'
  AND owner_id = 3
ORDER BY updated_at DESC
LIMIT 20;

An index like (stage, owner_id) or (owner_id, stage, updated_at) can turn "scan the whole deals table" into "jump to the matching slice". Exact best index depends on query patterns - measure when it matters.

EXPLAIN (peek under the hood)

EXPLAIN QUERY PLAN
SELECT * FROM contacts WHERE email = 'ada@example.com';

Look for USING INDEX … vs SCAN contacts. You don't need to memorize every line - just notice whether you're scanning the whole table.

Soft-delete gotcha

WHERE deleted_at IS NULL AND company_id = 10

Partial indexes (Postgres loves them; SQLite supports them too) can index only "live" rows:

CREATE INDEX idx_contacts_company_live
ON contacts(company_id)
WHERE deleted_at IS NULL;

Handy for CRMs where most queries ignore deleted rows.

Takeaway

Indexes are how backends keep your infinite scroll feeling infinite instead of "loading…". When someone says "we need an index on that FK", they're usually protecting a screen you built.

Basics done. Time to build something shaped like a product: CRM schema design.