A slow Postgres query is usually not "we need Redis." It is a sequential scan on a table that grew, a sort that spilled to disk, or an index that does not match the WHERE you wrote. Indexing that matches your queries is cheaper than a new datastore and more honest than "the database is the problem."
This is the order we use on product engineering when an API's p95 climbs. It pairs with PostgreSQL vs MongoDB (why you picked a relational store) and API rate limiting (so a hot key cannot melt the primary while you fix the plan).
EXPLAIN First, Indexes Second
Copy the SQL from APM or logs. Run EXPLAIN (ANALYZE, BUFFERS) on a snapshot that resembles production size. Staging with 200 rows will lie.
Look for:
- Seq Scan on a large table when you expected a point lookup
- Sort + external merge (work_mem too small or missing index for
ORDER BY) - Rows estimates off by 10-100ร (statistics, or a predicate the planner cannot use)
- Nested loops that made sense at 1k rows and do not at 10M
Do not add five indexes because a blog listed "always index foreign keys." Index the columns in the predicates and joins you measured. Then drop the ones whose idx_scan stays zero.
btree vs gin (The Decision You Actually Need)
| Index | Good at | Skip when |
|---|---|---|
| btree (default) | Equality, ranges, ORDER BY that matches the sort, unique constraints |
You need full-text or JSONB containment as the main filter |
| gin | JSONB @>, arrays, full-text tsvector |
You thought it would speed up WHERE id = |
| brin | Huge, naturally ordered time-series append | Random UUID primary keys |
| hash | Equality only, rare | You wanted a unique constraint (use btree) |
Most product apps are btree on (tenant_id, created_at DESC) and btree on the columns you filter in the admin UI. GIN appears when you search JSONB or implement search without Elasticsearch.
Partial indexes (WHERE deleted_at IS NULL) are how you keep a hot subset small. Expression indexes (LOWER(email)) are how you stop storing a second column "for search."
Composite order matters: (tenant_id, status, created_at) helps WHERE tenant_id = $1 AND status = $2 ORDER BY created_at DESC. It does not help a query that only filters status.
What Startups Skip
- No
tenant_idleading column on a multi-tenant table, then wondering why "list my orders" seq-scans the world - Indexing UUID
idonly and filtering onemailin support tools SELECT *plus overlay networks so the index is never index-only and heap fetches dominate- Creating indexes CONCURRENTLY in a migration that still holds a lock because someone wrapped it in a transaction
- Copying Mongo "flexible schema" into JSONB, then filtering with
->on an unindexed key
If you are on JSONB because the document shape changes weekly, index the keys you query now, and accept that new keys need new indexes. That is still cheaper than pretending Mongo will save you from modeling - the tradeoff is in Postgres vs Mongo.
Write Cost Is Real
Every index slows INSERT/UPDATE. A table with twelve overlapping btrees is a write amplifier. After a week in production, query pg_stat_user_indexes and drop the dead weight.
Reindex after a bulky bulk load. Autovacuum is not optional; if you turned it down to "go faster," you bought bloat.
Migrations belong in CI that actually runs against a copy of prod shape, not a hope that CREATE INDEX is instant on 80GB.
A Practical Sequence
Hour 1: Slowest query, EXPLAIN ANALYZE, note the filter.
Hour 2: One matching btree or gin; deploy with CREATE INDEX CONCURRENTLY.
Day 2: Watch p95 and index size. If the plan did not change, you indexed the wrong columns.
Week 2: Remove unused indexes; add the tenant-leading composite you actually list on.
Do not start with a "covering index" that includes every column you select. Prove the lookup is the bottleneck first.
FAQ
Should every foreign key be indexed?
If you JOIN or DELETE through it, yes. If it is a write-only audit pointer you never query, no.
Is uuid a bad primary key?
Random UUIDs fragment btree inserts. Fine at small scale. At large insert volume, consider uuidv7 or a bigint identity and keep UUID as an external id.
When is Redis the right next step?
When you have a hot computed key you can invalidate, not when SELECT lacks an index. Cache after the plan is sane.
The Bottom Line
Postgres indexing that matches your queries is EXPLAIN, then the smallest btree or gin that serves that predicate, then dropping the rest. Indexes are not a personality.
If p95 is high and the plan is a seq scan, fix the plan. A new database will seq-scan too.
Need a production Postgres plan in the SOW, not a rumour about Redis? See product engineering or get a free estimate.