Indexing Strategies

what an index actually is

A B-tree index (the default, and the right choice for the large majority of cases) is a sorted structure that lets the database find rows matching a condition without scanning every row — the SQL equivalent of using a book's index instead of reading cover to cover. It costs extra disk space and slows down every INSERT/UPDATE/DELETE on the indexed columns, since the index has to be kept in sync.

when an index helps

Good candidate	Why
Columns in `WHERE` clauses, especially on large tables	turns a full table scan into a targeted lookup
Foreign key columns	speeds up joins, and speeds up checking the constraint itself on delete
Columns in `ORDER BY`	can let the database skip a sort step entirely

An index on a column with very low cardinality (e.g. a boolean, or a status column with 3 possible values on a huge table) often isn't worth it — the planner may reasonably decide a sequential scan is cheaper than jumping around via the index anyway. Don't index everything by default; index based on actual query patterns.

composite indexes and column order

CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

-- this index is used by:
SELECT * FROM orders WHERE customer_id = 5;                    -- yes, leading column
SELECT * FROM orders WHERE customer_id = 5 AND status = 'paid'; -- yes, both columns

-- this index is NOT used by:
SELECT * FROM orders WHERE status = 'paid';                     -- no — status isn't the leading column

A composite index is only useful as a left-to-right prefix match, the same way a phone book sorted by (last name, first name) is useless for finding everyone named "John" but perfect for finding everyone named "Smith." Column order should match your most common query patterns, leading column first.

partial and expression indexes

-- partial: index only the rows that matter, smaller and cheaper to maintain
CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending';

-- expression: index the result of a function, not the raw column
CREATE INDEX idx_users_lower_email ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';  -- uses the index; a plain WHERE email = ... would not

Both are PostgreSQL-specific strengths worth knowing exist by name: a partial index skips rows that will never be queried by that condition, and an expression index makes case-insensitive/normalized lookups fast without a separate always-lowercase column.

unique indexes vs. UNIQUE constraints

In PostgreSQL, UNIQUE constraints and primary keys are implemented as unique indexes under the hood — there's no separate mechanism. CREATE UNIQUE INDEX directly is equivalent, and is how you'd add a partial-unique constraint (e.g. "email must be unique among active users"), which the plain constraint syntax can't express.

indexes are not free

Every index slows down writes to its table and takes disk space, and an unused index is pure cost with no benefit. PostgreSQL's pg_stat_user_indexes view shows how often each index is actually used — worth checking periodically on a mature schema rather than assuming every index someone added years ago is still earning its keep. See EXPLAIN ANALYZE for confirming whether a specific query is actually using the index you expect.

where to go from here

Query Planning & EXPLAIN ANALYZE — confirming an index is actually being used.
Query Optimization — the broader checklist indexing is one part of.