What is a database index and why does it matter?

An index is a data structure that lets the database find rows quickly without scanning the entire table like a book's index vs reading every page.

SQLPostgreSQLPerformance

Overview

Without an index, a query that filters by a column (e.g. `WHERE email = 'x'`) scans every row in the table an O(n) sequential scan. An index is a separate data structure (typically a B-tree) that lets the database jump directly to matching rows O(log n).

Key Points

•PostgreSQL automatically creates an index on PRIMARY KEY and UNIQUE columns.
•Add indexes on columns you filter by frequently (WHERE), sort by (ORDER BY), or join on.
•Indexes speed up reads but slow down INSERT/UPDATE/DELETE (the index must be maintained).
•Too many indexes waste storage and hurt write performance index for your actual query patterns.
•Use `EXPLAIN ANALYZE` to see if a query uses an index or falls back to a sequential scan.

Examples

Create an index

sql

-- Simple index on one column
CREATE INDEX idx_users_email ON users(email);

-- Composite index for multi-column queries
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

-- Partial index (only index rows matching a condition)
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

Check if a query uses an index

sql

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

-- Look for "Index Scan" (good) vs "Seq Scan" (full table scan)

Drop an index

sql

DROP INDEX IF EXISTS idx_users_email;

Pro Tip

Don't add indexes speculatively. Start with no extra indexes, measure slow queries with `EXPLAIN ANALYZE` or `pg_stat_statements`, then add indexes targeted at your actual slow queries.