Postgres indexing basics every full-stack dev should know

The first time a query that ran in two milliseconds locally took eight seconds in production, I learned more about Postgres indexing than any tutorial taught me. The data grew, the sequential scan that was free on a thousand rows became brutal on a few million, and nobody had added an index because nobody had needed one yet. Postgres indexing is one of those skills that sits right at the line between frontend and backend work, and every full-stack developer should have a working grasp of it. You do not need to be a database administrator. You need a handful of ideas and the ability to read a query plan.

What an index actually does

An index is a separate, sorted data structure that lets Postgres find rows without scanning the whole table. The default is a B-tree, and the mental model that works is a book index. Without one, finding every mention of a topic means reading every page. With one, you flip to the back, find the entry, and jump straight to the right pages. That is the difference between a sequential scan and an index scan, and at scale it is the difference between milliseconds and seconds.

The cost is that indexes are not free. Every index you add has to be updated on every insert, update, and delete to the table, and it takes disk space. So the goal is not to index everything. It is to index the columns your queries actually filter and sort on, and to leave the rest alone. An unused index is pure overhead that slows down your writes for no benefit.

When to add one

Add an index when you have a query that filters or joins on a column and that query is either slow or run often against a table that is going to grow. Foreign keys are a classic case people forget. Postgres does not automatically index the referencing side of a foreign key, so joins and lookups on it can quietly do full scans until you add the index yourself.

The columns in your WHERE clauses, your JOIN conditions, and your ORDER BY are the candidates. The trap is adding indexes speculatively for queries you do not run. Let the real query patterns guide you, and check that the index is actually being used before you congratulate yourself.

• Index columns you filter, join, and sort on in real queries
• Index foreign key columns, because Postgres does not do it for you
• Do not index low-cardinality columns like a boolean flag, the scan is often cheaper

Composite indexes and column order

When a query filters on more than one column, a composite index covering both can be far better than two separate ones. But the order of columns inside the index matters a great deal, and this is where people get tripped up. A B-tree index on two columns is sorted by the first column, then by the second within each value of the first. So an index on a status column followed by a created-at column helps a query that filters by status and then sorts by date. It does little for a query that only filters by date, because the index is not sorted by date at the top level.

The rule of thumb is to put the column you filter for equality first, and the column you range over or sort by second. Get the order wrong and Postgres may ignore the index entirely. I have seen a perfectly reasonable looking composite index do nothing because the columns were in the order someone typed them rather than the order the query needed.

Read the query plan, do not guess

The single most useful thing you can learn here is EXPLAIN ANALYZE. Put it in front of your query and Postgres tells you exactly how it plans to run it and how long each step actually took. You are looking for whether it chose a sequential scan or an index scan, and whether its row estimates are close to reality. A sequential scan on a big table where you expected an index to kick in is your signal that something is off, often the column order or a function wrapped around the column that prevents the index from being used.

This turns indexing from guesswork into something you can verify. Add an index, run EXPLAIN ANALYZE, confirm the plan changed and the time dropped. If the plan did not change, the index is wrong or unnecessary, and you just saved yourself from carrying dead weight into production.

The mistakes that keep showing up

A few patterns defeat indexes silently. Wrapping an indexed column in a function in your WHERE clause, like lowercasing it for a comparison, usually means the plain index cannot be used, and you need an expression index instead. Leading wildcards in a LIKE pattern cannot use a standard B-tree. And adding so many indexes that your write performance quietly degrades is its own trap, because each one is maintained on every write.

You do not need to master query planning to be effective here. Understand that indexes are sorted shortcuts, add them for the columns your real queries touch, get composite column order right, and read the plan to confirm. That alone will keep most full-stack apps fast well past the point where the naive version would have fallen over.