PostgreSQL Indexing and Query Performance
Most applications get slow because of a handful of queries doing far more work than they need to. Here is how to find and fix them.
By Uttam Thapa · · Database
⚡ Executive Summary (TL;DR)
Applications rarely become slow because of the framework. They become slow because a handful of queries do far more work than they need to. This covers what actually moves the needle in PostgreSQL: reading the query plan before adding a single index, indexing only what you filter, join and sort on, why column order in a composite index decides whether it is used at all, and how to spot the N+1 pattern an ORM will happily hide from you.
Figure 1: The query plan is the only reliable starting point. Everything before reading it is guesswork.
Introduction
Most applications do not become slow because of the framework. They become slow because of the database, and usually because of a small number of queries doing far more work than they need to.
This article covers the PostgreSQL fundamentals I rely on when building production applications: how indexes actually work, how to read a query plan, and the patterns that cause the most damage.
How an Index Actually Helps
Without an index, PostgreSQL performs a sequential scan. It reads every row in the table and checks whether it matches.
With a suitable index, it walks a B-tree instead, which turns a linear scan into a small number of lookups.
No index -> read 500,000 rows -> return 12
With index -> traverse B-tree -> return 12
This is why an index on a column used in a WHERE clause can change a query from hundreds of milliseconds to under one.
Reading a Query Plan
Before optimising anything, look at what the database is actually doing.
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_email = 'someone@example.com';
What to Look For
- Seq Scan on a large table usually means a missing index.
- Index Scan means an index is being used.
- Rows removed by filter tells you how much work was wasted.
- A large gap between estimated and actual rows suggests stale statistics.
Guessing at indexes without reading the plan usually results in indexes that are never used.
Indexes Are Not Free
Every index has to be updated on insert, update and delete.
- Read performance improves.
- Write performance decreases.
- Disk usage increases.
- Backups grow.
This is why indexing every column is not a strategy. Index the columns you actually filter, join and sort on.
A Duplicate Index Trap
In ORMs it is easy to declare an index twice without noticing. A unique constraint already creates an index, so adding a separate index declaration on the same column produces two indexes doing the same job.
The result is double the write cost for no read benefit. Most ORMs will warn about this, and the warning is worth acting on.
Composite Indexes and Column Order
A composite index covers multiple columns, but the order matters.
INDEX (status, created_at)
Works for: WHERE status = 'paid'
Works for: WHERE status = 'paid' ORDER BY created_at
Does not: WHERE created_at > '2026-01-01'
An index can be used from the leftmost column onwards. Filtering only on a later column will not use it.
The practical rule is to put the column used for equality first, and the column used for ranges or ordering second.
The N+1 Query Problem
This is the single most common performance issue in application code.
1 query -> fetch 50 orders
50 queries -> fetch the customer for each order
-----------------------------------------------
51 queries for one page
The fix is to fetch related data in one query, using a join or the ORM equivalent. Most ORMs provide an include or relation loading option specifically for this.
It is worth logging query counts per request in development. An endpoint quietly issuing fifty queries is easy to miss until it is in production.
SELECT Only What You Need
Selecting every column is convenient and often wasteful.
- More data crosses the network.
- Large text or JSON columns are fetched when unused.
- Covering indexes become impossible.
On a list view that displays four fields, select four fields.
Pagination That Scales
Offset pagination is simple and degrades badly.
OFFSET 100000 LIMIT 20
-> the database still reads 100,020 rows
Cursor pagination uses the last seen value instead.
WHERE created_at < '2026-07-01' ORDER BY created_at DESC LIMIT 20
This stays fast regardless of depth, because the index does the seeking. Offset pagination is fine for small datasets and a poor default for growing ones.
Transactions Where They Matter
Any operation that changes more than one row and must be all-or-nothing belongs in a transaction.
- Creating an order and decrementing stock.
- Transferring a value between two records.
- Writing a record and its related rows.
Without a transaction, a failure halfway through leaves the database in a state your application does not expect, and those bugs are very hard to trace after the fact.
Connection Limits
Managed PostgreSQL instances have a connection cap that is lower than most people expect. A serverless deployment can exhaust it quickly, because each instance opens its own connections.
The usual solutions are a connection pooler in front of the database, or limiting pool size per instance. This is worth checking before load rather than during it.
Key Takeaways
- ✓Read the query plan before adding indexes.
- ✓Index what you filter, join and sort on, not everything.
- ✓Watch for duplicate indexes created through ORM declarations.
- ✓Column order in composite indexes determines whether they are used.
- ✓Fix N+1 queries by loading relations in a single query.
- ✓Prefer cursor pagination once data volume grows.
- ✓Wrap multi-row changes in transactions.
Almost all database performance work is finding the few queries doing unnecessary work, rather than optimising everything at once.
Frequently asked questions
How do I know which index to add in PostgreSQL?
Read the query plan first. EXPLAIN ANALYZE tells you which step is expensive and why; adding indexes without it usually produces indexes that are never used but still slow every write.
Does adding more indexes make a database faster?
Reads, sometimes. Writes, never — every index must be updated on insert and update. Index what you filter, join and sort on, and remove the rest.
Why is my composite index not being used?
Almost always column order. A composite index can only be used from the leftmost column inwards, so an index on (a, b) helps a query filtering on a, but not one filtering only on b.
Home · Projects · Blog · Services · Résumé · Contact