Database Indexing: Why Queries Slow Down as Tables Grow (And the Fix)

A database index is a separate data structure that the database maintains to speed up data retrieval. Without an index on a column, the database performs a full table scan — reading every row to find matching records. With an index, the database can find matching records in O(log n) time using a B-tree structure (or O(1) for hash indexes). The index stores column values sorted in order with pointers to the actual table rows. Adding an index speeds up reads but adds overhead to writes (the index must be updated on INSERT, UPDATE, DELETE) and uses storage space.

Index columns that appear in WHERE clauses, JOIN conditions, and ORDER BY clauses on large tables. The primary key is automatically indexed. Foreign keys should almost always be indexed. Columns used in equality conditions (WHERE email = ?) are great index candidates. Columns with high cardinality (many distinct values) benefit most from B-tree indexes. Low-cardinality columns (boolean, small category set) benefit less and might do better with partial indexes or no index. Use EXPLAIN or EXPLAIN ANALYZE on slow queries to see if a full table scan is happening — that's your indicator that an index is needed.

Yes. Every index adds write overhead — when you INSERT, UPDATE, or DELETE a row, every index on that table must be updated. A table with 15 indexes pays 15x the write overhead compared to one with no indexes. For read-heavy tables (reporting, analytics), many indexes are fine. For write-heavy tables (logging, event streams, high-volume transactional data), excess indexes meaningfully slow down writes. The solution: regularly audit your indexes using pg_stat_user_indexes in PostgreSQL or similar tools in other databases — look for indexes that are rarely used and remove them.