Indexes

You've designed your schema and written your queries. The application works perfectly with test data. Then it goes live, traffic grows, and queries that returned results in milliseconds now take several seconds. Before you reach for more hardware, look at a simpler and far more powerful lever first: a database index.

Indexes are one of the highest-leverage tools in system design — a single well-placed index can turn a 5-second query into a 5-millisecond one. They are also one of the most common things AI coding agents overlook. AI agents produce schemas and queries that are logically correct but routinely omit the indexes that make those schemas perform at scale. Understanding indexes is not optional: it is how you catch what the AI missed before it reaches production.

The Book Analogy#

An index in a database works exactly like the index at the back of a non-fiction book. Without an index, finding every reference to "load balancing" in a 600-page book means reading every page. With an index, you look up "load balancing" alphabetically, get a list of page numbers, and jump directly to each one.

A database works the same way. Without an index, a query like WHERE user_id = 42 on a million-row table requires the database to read every row and check each one against the condition — a sequential scan. With an index on user_id, the database looks up the value in the index and jumps directly to the matching rows.

The reason tables need indexes comes down to how they store data. Unlike a spreadsheet where you might keep rows sorted, a database table has no inherent row order. New rows are written into the next available space on disk — they don't slot into a sorted position. The result is that the table's physical layout is an unsorted heap with no organizational structure that supports searching. An index is the structure you add to make searching efficient. It is maintained separately from the table and stays in sorted order, giving the database a fast path to specific rows without reading the entire table.

How a B-Tree Index Works#

The most common index type in relational databases (PostgreSQL, MySQL, SQLite) is the B-Tree. When you run CREATE INDEX without any additional options, a B-Tree index is created by default.

The diagram above is a simplified illustration with 3 levels and only 3 entries per node. In practice, each B-Tree node holds hundreds of entries, which is why the tree stays remarkably shallow — typically 4–5 levels — even for tables with millions of rows.

How a search works in practice: To find user_id = 42, the database starts at the root, reads the branch node entries to determine which subtree contains 42, and traverses down to the appropriate leaf node. The entire path is 4–5 node reads for a million-row table. The root and upper branch nodes are typically held in the database's buffer cache — its in-memory pool of recently accessed disk pages — so in practice the search often requires only 1–2 physical disk reads. Compare this to reading every row in a million-row table.

How Indexes Speed Up Reads#

The performance difference between an indexed and an unindexed query on a large table can be dramatic — often 100× to 1,000× faster for selective lookups.

PostgreSQL's query planner chooses between these two strategies automatically based on table statistics. You can see its decision with EXPLAIN:

-- Without index
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

Seq Scan on orders  (cost=0.00..18340.00 rows=5 width=64)
  Filter: (user_id = 42)

-- After adding an index
CREATE INDEX orders_user_id_idx ON orders (user_id);
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

Index Scan using orders_user_id_idx on orders  (cost=0.43..20.51 rows=5 width=64)
  Index Cond: (user_id = 42)

The estimated cost drops from 18340 to 20.51 — roughly a 900× improvement. The cost values in EXPLAIN output are not milliseconds; they are an arbitrary unit representing the planner's estimated work, roughly proportional to the number of disk page reads. What matters is the relative difference: the index scan costs a tiny fraction of the sequential scan.

Indexes also speed up several other operations you might not expect:

• JOIN operations: The database uses the index to find matching rows in the joined table, rather than scanning the entire joined table for each row in the driving table.
• ORDER BY clauses: A B-Tree is already sorted, so the database can read leaf nodes in order without a separate sort step.
• WHERE on UPDATE and DELETE: The database must locate rows before modifying them. An unindexed filter on a large table means a full scan before any row is touched.

The Cost: Indexes Slow Down Writes#

Indexes are not free. Every time you insert, update, or delete a row, every index on that table must also be updated. This is write amplification: one logical write to your data becomes multiple physical writes across all your indexes.

In PostgreSQL, the main table storage is called the heap — an unsorted file where rows are stored in no particular order. When you write a row, the database writes to the heap first, then must update every index that covers any column in that row.

The practical rule: A table with 8 indexes incurs roughly 8× more index write I/O compared to a table with 1 index. This matters most for write-heavy workloads — event logs, audit trails, message queues, telemetry ingestion. On these tables, over-indexing can significantly degrade write throughput under load, sometimes more than the read benefit justifies.

Index Types#

Most of the time, a standard B-Tree index is all you need. But PostgreSQL offers specialized index types suited to different access patterns — and knowing when to reach for them is a mark of a careful engineer.

A note on partial indexes: These are underused but extremely effective. Consider a jobs table with 10 million rows, where 9.9 million are marked completed = TRUE. Nearly all queries target the 100,000 unfinished jobs. A partial index — CREATE INDEX ON jobs (created_at) WHERE completed = FALSE — contains entries for only those 100,000 rows. It is 99% smaller than a full index and far faster to scan. AI agents almost never suggest partial indexes; you have to recognize the pattern yourself from the table's access characteristics.

Composite Indexes: Column Order Matters#

A composite index covers more than one column: CREATE INDEX ON orders (user_id, status, created_at). These are among the most powerful optimization tools, but they only work if you understand one critical rule: the leftmost prefix rule.

Column ordering principle: Place the most selective equality-filter column first, followed by other equality filters, then range-filter columns such as dates or prices. Consider the query WHERE user_id = 42 AND created_at > '2024-01-01'. The index (user_id, created_at) is far more effective than (created_at, user_id) — because the equality constraint on user_id eliminates all other users' rows before the range scan on created_at even begins. Reversing the order forces the database to scan every row where created_at > '2024-01-01' across all users, then filter by user_id afterward — a much larger scan.

Selectivity: Why Column Choice Matters as Much as Column Order#

Even with perfect column ordering, an index on the wrong column provides no benefit. Selectivity describes how precisely an index condition narrows the matching rows. A highly selective condition matches very few rows: WHERE email = 'alice@example.com' returns exactly one row from a million-row table. A low-selectivity condition matches most of the table: WHERE is_active = TRUE might match 95% of rows.

Cardinality — the number of distinct values in a column — is the key indicator of selectivity potential:

The query planner estimates selectivity using statistics it collects automatically when you run ANALYZE (or when the autovacuum daemon runs in the background). When the planner estimates that a condition matches more than roughly 5–10% of the table, it typically prefers a sequential scan over an index scan. The reason is physical: a sequential scan reads heap pages in order, one after another, which is efficient for spinning disks and easy for the OS to prefetch. An index scan on a large fraction of rows jumps between many scattered heap pages, which generates far more random I/O than the sequential scan it was meant to replace. The exact threshold varies by hardware and PostgreSQL configuration, but the underlying principle is constant.

The low-cardinality trap: Adding an index on is_active adds write overhead to every INSERT and UPDATE — but the planner ignores it for WHERE is_active = TRUE because returning 95% of the table through random index lookups is slower than a sequential scan. The index wastes space and slows writes without helping any read. This is the most common over-indexing mistake, and it is one AI agents make regularly by indexing columns that appear in WHERE clauses without considering how many rows those conditions actually match.

When NOT to Index#

More indexes is not always better. Each index is a maintenance burden: more write amplification, more storage consumed, and more work for VACUUM. Before adding an index, verify that the query planner will actually use it.

What AI Agents Get Wrong About Indexes#

AI coding agents produce syntactically correct schemas and queries — but they consistently miss the indexes that make those schemas perform in production. The failure patterns are predictable.

Other common AI index omissions:

• No composite index for multi-column filters. A table always queried as WHERE tenant_id = ? AND status = 'active' needs a composite index on (tenant_id, status). AI agents generate the schema without reasoning about access patterns — identifying and specifying the right composite indexes is your responsibility.

• No partial index for small, frequently-queried subsets. A jobs table almost always queried with WHERE completed = FALSE benefits from a partial index — CREATE INDEX ON jobs (created_at) WHERE completed = FALSE. AI agents generate full indexes or no indexes at all; partial indexes require you to recognize the pattern from the table's usage, not just its columns.

• No covering index for hot read paths. A query that always fetches (id, name, email) from a users table benefits from a covering index — CREATE INDEX ON users (id) INCLUDE (name, email) — enabling an index-only scan that never touches the heap. AI agents rarely identify this optimization because it requires understanding the specific columns a query projects, not just the columns it filters on.

Finding Missing Indexes in Production#

When queries are slow, the first question to ask is: "Are my queries actually using indexes?" PostgreSQL gives you the tools to answer it directly.

EXPLAIN ANALYZE shows the actual execution plan and per-node timing for any query. Add ANALYZE to get real execution times rather than estimates:

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

Seq Scan on orders  (cost=0.00..18340.00 rows=5 width=64)
                    (actual time=0.05..234.12 rows=5 loops=1)
  Filter: (user_id = 42)
  Rows Removed by Filter: 999995

Key signals to look for in the output:

• Seq Scan with a Filter: clause on a large table — the filter is not using any index; this is the clearest signal that an index is missing
• Rows Removed by Filter: 999995 — the engine examined nearly a million rows to return 5; a missing index on a highly selective column
• High actual time on a simple lookup — almost always a missing index or stale statistics (run ANALYZE tablename to refresh the planner's statistics)
• Index Scan — an index is being used; check the cost to confirm it's substantially lower than a sequential scan would be
• Index Only Scan — the best possible outcome: the query was answered entirely from the index without touching the main table at all

After adding the index, run EXPLAIN again to confirm the plan changed:

CREATE INDEX orders_user_id_idx ON orders (user_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

Index Scan using orders_user_id_idx on orders  (cost=0.43..20.51 rows=5 width=64)
                                                (actual time=0.04..0.09 rows=5 loops=1)
  Index Cond: (user_id = 42)

Finding unused indexes — indexes that silently add write overhead without helping any read:

-- Indexes that have never been used since the last statistics reset
SELECT schemaname, relname AS table, indexrelname AS index, idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname, indexrelname;

An index with times_used = 0 after weeks of representative production traffic is a strong candidate for removal — it consumes storage and slows every write without benefiting any query. Allow at least several days of production workload before drawing conclusions, since these statistics reset on server restart.

Finding slow queries using pg_stat_statements:

pg_stat_statements tracks cumulative execution statistics for every distinct query pattern, making it easy to identify which queries are costing the most time across all their invocations.

-- pg_stat_statements must be listed in shared_preload_libraries in postgresql.conf
-- and the server restarted before this extension can be created.
-- Ask your DBA or check your cloud provider's documentation to enable it.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 queries by total execution time across all calls
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Prioritize by total execution time rather than per-call time. A query that takes 50ms but runs 100,000 times per day accumulates 5,000 seconds of database work — far more impactful than a 5-second query that runs twice a week. This is where you find the indexes that make the biggest real-world difference.

Summary#

The engineers who get the most out of AI coding tools are not the ones who accept generated schemas without review. They are the ones who understand access patterns well enough to recognize which indexes the AI missed — and add them before the system reaches production.

Sources:

• PostgreSQL Documentation: Indexes
• PostgreSQL Documentation: Using EXPLAIN
• Use The Index, Luke: The Anatomy of an Index
• Use The Index, Luke: DML and Indexes
• pgmustard: Understanding PostgreSQL Index Scans