Index Fundamentals

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

Explain the core ideas behind Index Fundamentals
Run the sample SQL or command and verify its output
Apply the technique to the Tutorial Platform and production scenarios

Build the mental model

An index can find rows without scanning the entire table, but every insert, update, and delete must also maintain it. Primary keys and unique constraints create indexes, but PostgreSQL does not automatically index the referencing side of a foreign key. Composite column order should follow real predicates and sorting needs.

Connect it to a real scenario

The published feed filters true rows and sorts by date and ID, so use a partial composite index. Index the lesson foreign key together with lesson number for lookups. Validate every index with EXPLAIN and its write/storage cost.

Do not index without a query pattern
Evaluate indexes on foreign-key child columns
Use partial and composite indexes to match the workload

Try the working example

sql

CREATE INDEX lessons_tutorial_number_idx
  ON app.lessons (tutorial_id, lesson_number);

CREATE INDEX tutorials_published_feed_idx
  ON app.tutorials (published_at DESC, tutorial_id DESC)
  WHERE is_published = true;

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'app';

You should see

You have workload-specific indexes for lesson lookup and the published feed.

5-minute try-it

Propose indexes for user-email lookup, enrollments by user, and incomplete progress; explain each column order.

One important caution

Indexing every column slows writes, consumes storage, and gives the planner unnecessary choices.

PostgreSQL — Indexes — PostgreSQL Global Development Group

Easy traps

Indexing every column slows writes, consumes storage, and gives the planner unnecessary choices.
Validate sample code on a local or test database with recoverable backups before applying it to production data.

Exercise

Propose indexes for user-email lookup, enrollments by user, and incomplete progress; explain each column order.

You'll know it worked when: You have workload-specific indexes for lesson lookup and the published feed.