Introduction to Indexes

Indexes are one of the most important concepts in database performance. Without indexes, PostgreSQL has to scan every single row in a table to find what you are looking for — that is called a sequential scan. With the right index, PostgreSQL can jump directly to the data it needs.

How Indexes Work

Think of a database index like the index at the back of a textbook. Instead of reading the entire book to find information about "photosynthesis," you look it up in the index and go directly to page 247.

An index creates a separate data structure (usually a B-tree) that stores a copy of the indexed columns along with pointers to the actual rows. When you search on an indexed column, PostgreSQL uses the index to find the rows instantly instead of scanning the whole table.

Creating an Index

The basic syntax for creating an index:

CREATE INDEX idx_students_email ON students (email);

This creates an index on the email column. Now queries that search by email will be much faster:

-- This query will use the index
SELECT * FROM students WHERE email = 'alice@example.com';

When to Create Indexes

Not every column needs an index. Good candidates are:

Columns used in WHERE clauses frequently
Columns used in JOIN conditions
Columns used in ORDER BY
Columns with high cardinality (many unique values)

Bad candidates for indexing:

Small tables (under a few thousand rows)
Columns with few unique values (like a boolean column)
Columns that are rarely queried

The Cost of Indexes

Indexes are not free. They have downsides:

Storage — Each index takes up disk space
Write performance — Every INSERT, UPDATE, or DELETE must also update the indexes
Maintenance — Indexes can become fragmented and need occasional reindexing

The trade-off is usually worth it for read-heavy workloads. But do not go overboard indexing every column.

Viewing Indexes

To see indexes on a table:

\d students

Or query the system catalog:

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'students';

Dropping an Index

If an index is no longer needed:

DROP INDEX idx_students_email;

Always monitor your indexes. Unused indexes waste space and slow down writes. PostgreSQL provides statistics to help you identify which indexes are actually being used.