Database Performance: Indexing Strategies in PostgreSQL

Your app is fast in development with 10 users. In production with 100,000 users, it crawls. The culprit is usually missing indexes.

The Library Analogy

Imagine a library with 1 million books.

• Without Index: To find "Harry Potter", you check every single book on every shelf. (Full Table Scan).
• With Index: You go to the catalog card, find "H", then "Harry Potter", and get the exact shelf number. (Index Scan).

Creating an Index

CREATE INDEX idx_users_email ON users(email);

Now, searching by email is O(log n) instead of O(n).

Composite Indexes

If you often query by two columns:

SELECT * FROM users WHERE last_name = 'Doe' AND first_name = 'John';

You need a multi-column index:

CREATE INDEX idx_users_names ON users(last_name, first_name);

Order matters! This index helps search by last_name alone, but NOT by first_name alone.

EXPLAIN ANALYZE

Don't guess. Ask Postgres.

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

It will tell you exactly how it planned to execute the query and how long it took.