502ms → 0.13ms#
I told this story briefly in the scaling post
— our APIs were slow, we almost spun up more pods, and then one CREATE INDEX statement fixed everything. This post is the full version. What indexes actually are, how to find missing ones, and why this should be your first move before any infrastructure scaling.
The Phone Book Analogy#
A database without an index on the column you’re querying is like a phone book with no alphabetical ordering. Looking up “Rodriguez”? Read every single entry until you find it.
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;
Without an index, PostgreSQL reports:
Seq Scan on orders (cost=0.00..45123.00 rows=50 width=120)
Filter: (user_id = 123)
Rows Removed by Filter: 999950
Execution Time: 502.789 ms
Sequential scan. One million rows scanned. 999,950 thrown away. 50 matches found. Half a second.
CREATE INDEX idx_orders_user_id ON orders (user_id);
Now:
Index Scan using idx_orders_user_id on orders (cost=0.42..8.44 rows=50 width=120)
Index Cond: (user_id = 123)
Execution Time: 0.134 ms
Same query. Same data. 250x faster. One line of SQL.
How B-Trees Work (The 30-Second Version)#
The default PostgreSQL index is a B-tree — a balanced tree that keeps data sorted. Instead of scanning every row, the database traverses 3-4 levels of the tree to find matches. It’s like using a book’s index to jump to the right page instead of reading the entire book.
B-trees handle: =, <, >, <=, >=, BETWEEN, ORDER BY, and LIKE 'prefix%'. That covers 90% of queries.
For arrays, JSONB, or full-text search, PostgreSQL has GIN indexes. For huge time-series tables with naturally ordered data, BRIN indexes are tiny and fast. But B-tree is the default and the right choice most of the time.
Composite Indexes: Column Order Matters#
An index on multiple columns:
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
This helps queries filtering on user_id, or user_id AND status. It does not help queries filtering only on status. The index is usable if the query filters on a left prefix of the columns.
Think of it like a phone book sorted by last name, then first name. You can look up “Rodriguez” (last name only). You can look up “Rodriguez, Aaron” (both). You can’t efficiently look up just “Aaron” (first name only) — the book isn’t sorted that way.
Strategy: put the most selective column first (the one that narrows results the most), or the column you most frequently query alone.
Partial Indexes: The 90% You’re Not Using#
Most developers don’t know about partial indexes. They index only a subset of rows:
-- Only index active orders (ignore the 90% that are completed)
CREATE INDEX idx_active_orders ON orders (user_id) WHERE status = 'active';
If 90% of your orders are completed and you only ever query active ones, a partial index is 10x smaller and 10x faster than a full index. Massively underused.
Good candidates for partial indexes: WHERE deleted_at IS NULL (soft deletes), WHERE status = 'pending', WHERE is_active = true — any boolean or status column with skewed distribution.
The N+1 Query: The Silent Killer#
// BAD: 1 query for users + 100 queries for orders
const users = await db.query("SELECT * FROM users LIMIT 100");
for (const user of users) {
const orders = await db.query("SELECT * FROM orders WHERE user_id = $1", [
user.id,
]);
}
// 101 database round trips
// GOOD: 2 queries total
const users = await db.query("SELECT * FROM users LIMIT 100");
const userIds = users.map((u) => u.id);
const orders = await db.query("SELECT * FROM orders WHERE user_id = ANY($1)", [
userIds,
]);
// 2 database round trips, join in application code
N+1 is the number one database performance killer. ORMs make it easy to trigger accidentally because they hide the SQL — you don’t realize each user.orders access fires a separate query. Always check query logs during development.
The Foreign Key Gotcha#
Here’s something that catches many developers: PostgreSQL does not automatically create indexes on foreign key columns. You add REFERENCES users(id) to orders.user_id, and PostgreSQL enforces the relationship but doesn’t index it. JOINs and cascading deletes on that column will be slow until you add the index yourself.
-- The constraint
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);
-- The index (PostgreSQL won't create this for you)
CREATE INDEX idx_orders_user_id ON orders (user_id);
Always index your foreign keys. Always.
The EXPLAIN ANALYZE Workflow#
Before and after every performance investigation:
- Find slow queries — check Prometheus
for high-latency endpoints, or enable
log_min_duration_statementin PostgreSQL - Run EXPLAIN ANALYZE — look for
Seq Scanon large tables, highRows Removed by Filter - Add the index — target the columns in
WHERE,JOIN ON, andORDER BY - Run EXPLAIN ANALYZE again — confirm it’s using the index
- Check for regressions — indexes slow down writes, so verify INSERT/UPDATE performance
The red flags in EXPLAIN output:
Seq Scanon a table with more than a few thousand rowsRows Removed by Filterclose to total row count (scanned everything, kept almost nothing)Sortwithout an index (PostgreSQL sorts in memory or on disk, both slower than pre-sorted index)
When Not to Index#
Every index has a cost:
- Slows down writes (INSERT/UPDATE/DELETE must update the index)
- Consumes disk space
- Must be maintained by VACUUM
Don’t index every column. Index the columns that appear in WHERE, JOIN ON, and ORDER BY of frequent, slow queries. Check pg_stat_user_indexes to find unused indexes eating resources.
And don’t index low-cardinality columns (like status with 3 possible values) on their own — the query planner will just seq scan anyway because the index isn’t selective enough. Use them in partial indexes or composite indexes instead.
The Connection to Scaling#
In the scaling post , I listed the golden rule: “always optimize before you scale.” This is what optimization looks like. Before adding pods, before adding read replicas, before making the database instance bigger — run EXPLAIN ANALYZE on your slow queries. The fix might be one line.
