I found this one during load testing. Response times fell apart under concurrent requests, PgBouncer's connection pool was healthy the whole time, and the actual cost turned out to be Postgres scanning a table that had outgrown what a full read could handle cheaply. Once I understood what was actually happening, adding the right index fixed it without touching a single line of application code.

By default, Postgres answers most queries with a sequential scan - it reads every row in the table, top to bottom, checking each one against your filter.

The cost of that grows in direct proportion to table size. On a few thousand rows, that's a few milliseconds and you'd never notice. On tens of millions of rows, the same scan can take seconds, and it's paying that cost fresh on every single request - not once, every time.

An index gives Postgres a shortcut. Most indexes in Postgres are B-trees, a structure organized so that looking something up takes roughly the same small number of steps whether the table has a thousand rows or a hundred million.

That's the actual reason indexing scales so much better than scanning: the search cost barely grows as the table grows, instead of growing right alongside it.

The default B-tree index handles the bulk of real-world cases - filtering on equality, ranges, sorting - and it's what you get unless you ask for something else. But there are situations where a B-tree just won't help.

If you're filtering on a JSONB column or doing full-text search, you want a GIN index instead. A B-tree can't efficiently search inside a JSON blob or match against text tokens; GIN is built specifically for that kind of "does this contain X" lookup.

This is a common source of confusion - someone adds a normal index, the query still shows a sequential scan, and it looks like indexing "didn't work," when really it was the wrong type of index for the query.

For very large, append-only tables where a column (like a timestamp) naturally correlates with the order rows were inserted, a BRIN index is worth knowing about - it's far smaller than a B-tree because it only stores the min/max value per block of rows rather than indexing every row individually.

If you index two columns together - say organization and status - Postgres builds one structure sorted first by organization, then by status within each organization.

That index works great for queries filtering on organization alone, or organization and status together. What it won't do efficiently is help a query that filters on status by itself, because Postgres can't jump to a status value without already knowing which organization it belongs to - the leading column has to be part of the filter for the index to be useful.

This trips people up constantly. They add a composite index, then write a query that only filters on the second column, and can't figure out why the planner is still doing a sequential scan.

The general rule of thumb: put the column you filter on most often, or most selectively, first.

Normally, even with an index pointing you to the right rows, Postgres still has to go back to the actual table to fetch any columns the index doesn't contain.

If you include those extra columns directly in the index definition, Postgres can sometimes answer the query using the index alone, skipping that extra step entirely. It's a nice optimization for read-heavy queries, but it only pays off cleanly if the table is being vacuumed regularly - otherwise Postgres still has to double-check the table anyway.

Sometimes most of your rows share a value you rarely query. If 90% of orders are completed and you almost always query for active or pending ones, indexing the whole column wastes space on rows you'll never look up that way.

A partial index - one that only covers the subset you actually care about - stays smaller and gets picked by the planner more reliably, since its odds of matching a real query are better.

The obvious win is read speed - turning a scan that grows with table size into a lookup that barely grows at all.

Less obvious: an index on a column you sort by can let Postgres skip a separate sorting step entirely, and if you're already enforcing uniqueness on a column, that constraint is backed by an index doing double duty. This kind of query-level tuning is exactly the sort of thing that separates a system that just works from one built for scale - the same discipline we bring to backend development work for clients whose products live or die on response times.

This is the part that's easy to gloss over.

Every index has to be kept up to date on every insert, update, or delete that touches its columns - not just the table itself. A table with eight indexes pays that update cost eight times over on every write. If a table is write-heavy, piling on indexes "just in case" is a real, measurable hit to throughput, not a theoretical one.

Indexes also take up real disk space - a wide composite index on a large table can end up close to the size of the table itself. And because Postgres doesn't immediately reclaim space from deleted or updated rows, both tables and their indexes accumulate dead space over time if they're not vacuumed regularly, which slowly degrades performance until you clean it up.

There's one more practical gotcha: creating an index on a live production table locks writes to it by default while the index builds. On any table with real traffic, you want CREATE INDEX CONCURRENTLY instead - it takes longer to build, but it doesn't block writes while it does. Mistakes like this are cheap to make and expensive to fix in production, which is why teams doing serious SaaS development tend to bake this kind of review into the migration process rather than catching it after an outage.

Don't guess.

Run EXPLAIN ANALYZE on the query that's actually slow and look for a sequential scan on a large table — that's your signal.

If your project has pg_stat_statements enabled, use it to find which queries cost the most time in total, not just which ones run most often - a query that runs rarely but takes ten seconds can matter more than one that runs constantly at five milliseconds.

It's also worth occasionally checking which indexes are actually being used - Postgres tracks this, and an index that's never being touched by any query is pure overhead with no upside.

After adding an index, don't assume it's working just because the migration ran - check the query plan again and confirm Postgres is actually choosing it.

None of the internals above change how you write the index in Django or FastAPI - that part is still just a migration. This is the kind of groundwork that shows up in Python development projects far more often than people expect, long after the initial build is "done."

Django

class Order(models.Model): organization = models.ForeignKey(Organization, on_delete=models.CASCADE) status = models.CharField(max_length=20) class Meta: indexes = [ models.Index(fields=["organization", "status"]), ]

SQLAlchemy

from sqlalchemy import Column, Integer, String, Index class Order(Base): __tablename__ = "orders" id = Column(Integer, primary_key=True) organization_id = Column(Integer) status = Column(String(20)) __table_args__ = ( Index("ix_orders_org_status", "organization_id", "status"), )

One thing worth knowing: neither Django's nor Alembic's default migration tooling uses CONCURRENTLY. If you're adding an index to a large table that's already live in production, you'll want to write that specific migration by hand with raw SQL rather than relying on the framework's default behavior. It's a small detail, but it's exactly the sort of thing that separates a custom web application that stays fast under real traffic from one that only performed well in staging.