Developers love indexes. They are the performance cheat code. Slap an index on a slow column and watch queries fly. But indexes are not free. Every write becomes a write plus an index update. Every index consumes disk space. Every index the query planner considers adds planning time. The wrong index is worse than no index.
An index is a trade-off. You are buying read speed with write speed and disk space.
The B-Tree is the default index type for a reason. It is balanced, it handles range queries efficiently, and it works for equality, inequality, and sorting. But it is not the only tool. Hash indexes are faster for exact equality but useless for ranges. GiST and GIN indexes power full-text search and geometric queries. BRIN indexes compress sequential data for massive tables. Choose the index for the query pattern, not the column name.
-- B-Tree: the workhorse
CREATE INDEX idx_users_email ON users(email);

-- Partial index: only index what you query
CREATE INDEX idx_posts_published ON posts(created_at)
    WHERE published = true;

-- Expression index: index the transformed value
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Covering index: includes all columns the query needs
CREATE INDEX idx_posts_covering ON posts(author_id, created_at)
    INCLUDE (title, slug);

-- BRIN for time-series data (tiny, fast sequential scans)
CREATE INDEX idx_logs_brin ON logs(created_at)
    USING BRIN WITH (pages_per_range = 128);
Composite indexes are ordered. The column order matters more than most developers realize. A composite index on (status, created_at) can filter by status, or by status and created_at, but not by created_at alone. The leading column must be the most selective one — the one that eliminates the most rows. If your queries filter by created_at without status, that index is dead weight.
-- Index: (status, created_at)
-- These queries CAN use the index:
SELECT * FROM posts WHERE status = 'published';
SELECT * FROM posts WHERE status = 'published' AND created_at > '2026-01-01';

-- This query CANNOT use the index efficiently:
SELECT * FROM posts WHERE created_at > '2026-01-01';
-- The database cannot skip the leading column.
-- It will scan the entire index or fall back to a sequential scan.
Covering indexes are the performance secret nobody talks about. When an index contains all columns a query needs, the database never touches the table. It reads only the index. For high-traffic read queries, this is transformative. A query that previously required random disk I/O on a million-row table now reads sequentially from a compact index.
Index maintenance is ongoing work. Use EXPLAIN ANALYZE to verify that queries actually use your indexes. Monitor pg_stat_user_indexes for unused indexes and drop them. Rebuild indexes periodically to reclaim space from bloat. An unmaintained index collection is technical debt that compounds silently until your database chokes on writes.
Indexing is not a one-time optimization. It is a conversation between your schema and your query patterns. Start with the queries. Understand what the database must do to answer them. Then build the minimum set of indexes that makes those queries fast. Anything more is waste. Anything less is pain.