PostgreSQL Indexes: What They Actually Buy You

The starting point

Indexes are often described as making database queries faster, which is true but incomplete. An index gives PostgreSQL another way to locate rows without scanning the entire table. The database must maintain that structure when rows change, and the query planner must decide whether using it is actually cheaper than another strategy. Thinking in those terms prevents the common mistake of adding indexes everywhere just because a column appears in a query.

Indexes optimize access patterns

Suppose a table contains a large number of articles and public pages repeatedly ask for rows with status equal to published. An index on status can make finding matching rows cheaper in some workloads. But if almost every row is published, the selectivity may be poor and a sequential scan can still be reasonable. The index only helps when it matches the actual distribution and query pattern.

Composite indexes add another dimension. If a query filters by status and orders by publication time, an index designed around that access pattern can be more useful than two unrelated single-column indexes. The correct design comes from observing the query, not from memorizing a list of columns that are usually indexed.

Indexes are not free

Every index consumes storage and creates additional work when rows are inserted, updated, or deleted. A database with dozens of speculative indexes can therefore become harder to write to while providing little benefit to reads. The planner also has more possible strategies to consider.

For a small portfolio database, the right approach is usually conservative: index obvious lookup and ordering paths, then measure when the dataset becomes large enough for performance concerns to become real.