October 25th, 2025. My SaaS application was finally feature-complete. Nine major features, multi-tenant architecture, progressive learning—everything worked. But it was slow. Really slow.
Dashboard queries were crawling. Lists that should load instantly were taking multiple seconds. Alpha testers were starting to ask “Is the site broken?”
I wasn’t just worried about performance. I was worried about survival.
I spent two days optimizing queries, rewriting RLS policies, adding caching. Nothing made a dent. The frustration was mounting—5 days from planned alpha launch and I couldn’t figure out why a technically-correct database was performing so poorly.
Then I ran one diagnostic query that made my stomach drop.
89 foreign keys. Zero indexes.
This is the story of a Postgres “feature” that nobody tells you about—the 2 weeks it nearly killed my alpha launch—and the 4-minute fix that saved everything.
The Stakes: When Slow Means Dead
Research shows every 100ms of latency costs 1% of conversions. At 2-3 second load times, I wasn’t just delivering a bad experience—I was destroying the first impressions that would make or break my alpha.
The immediate stakes:
– Alpha testers questioning if the platform was stable enough to recommend
– Lost credibility with early users who’d given me their trust
– If this persisted into full launch: industry research suggests 40% bounce rate at 3-second load times
– The difference between “this is promising” and “this feels broken”
– Database CPU at 80-90% (expensive and getting worse)
– 5 days from planned launch, completely blocked
What those 2 weeks actually cost:
– 40+ hours debugging slow queries, profiling code, blaming RLS policies
– 1 week delayed launch (missed my internal deadline)
– Lost opportunity to be building features instead
– Credibility damage with early testers
The technical debt was becoming business debt. And I didn’t know why.
—
The Investigation: Following the Slow Queries
By late October, my platform had real complexity: 30+ tables, 80+ RLS policies, hundreds of foreign key relationships. Simple operations that should be instant were painfully slow—loading lists, fetching dashboard data, filtering by `org_id`.
I started with `pg_stat_statements` to find the culprits:
```sql
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
```Every slow query had the same pattern—filtering by foreign keys:
```sql
SELECT * FROM user_data
WHERE org_id = '...' AND resource_id = '...';
```Should be instant. I had those columns. I had RLS policies. Why was this slow?
I checked the execution plan:
```sql
EXPLAIN ANALYZE
SELECT * FROM user_data
WHERE org_id = 'abc-123' AND resource_id = 'xyz-789';
```The result made my stomach sink:
```
Seq Scan on user_data (cost=0.00..2847.23 rows=15 width=1024)
Filter: ((org_id = 'abc-123') AND (resource_id = 'xyz-789'))
Rows Removed by Filter: 12,834
Execution Time: 843.271 ms
```Sequential scan. Postgres was reading every single row in the table, then filtering. On a query that should take milliseconds.
But I had foreign keys on `org_id` and `resource_id`. Shouldn’t those be indexed?
—
The Revelation: Foreign Keys Don’t Auto-Index
Here’s what I learned that day, the hard way:
Postgres automatically creates indexes for PRIMARY KEYs and UNIQUE constraints.
Postgres does NOT automatically create indexes for foreign keys.
Let me say that again, because this single misunderstanding cost me 2 weeks of my life:
FOREIGN KEYS ≠ INDEXES
When you write this:
```sql
CREATE TABLE user_data (
id UUID PRIMARY KEY, -- ✅ Automatically indexed
org_id UUID REFERENCES organizations(id), -- ❌ NOT indexed!
resource_id UUID REFERENCES resources(id), -- ❌ NOT indexed!
created_at TIMESTAMPTZ
);
```Postgres creates the foreign key constraint (referential integrity), but it does **not** create an index on `org_id` or `resource_id`.
Why? Because Postgres can’t assume how you’ll query the data. Maybe you never filter by foreign keys. Maybe you always join in a specific direction. So it leaves the decision to you.
The problem? I didn’t know I needed to make that decision. I assumed “foreign key” meant “indexed for queries.” It doesn’t.
—
The Diagnostic: How Bad Was It?
I wrote a query to find every foreign key without an index:
```sql
SELECT
c.conrelid::regclass AS table_name,
a.attname AS column_name
FROM pg_constraint c
JOIN pg_attribute a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid
WHERE c.contype = 'f'
AND NOT EXISTS (
SELECT 1 FROM pg_index i
WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)
);
```The result hit me like a freight train:
89 rows.
89 foreign keys across 32 tables. Zero indexes.
Every join, every filter, every RLS policy check was doing full table scans. No wonder everything was slow.
—
The Fix: The Index Apocalypse
October 25, 2025. 7:51 AM. I knew what I had to do.
One migration. 89 indexes. I called it “The Index Apocalypse.”
```sql
-- The Index Apocalypse Migration
-- Purpose: Fix performance by indexing all foreign key columns
-- Organization-scoped tables
CREATE INDEX idx_users_org_id ON users(org_id);
CREATE INDEX idx_resources_org_id ON resources(org_id);
CREATE INDEX idx_documents_org_id ON documents(org_id);
-- Resource-scoped tables
CREATE INDEX idx_user_data_resource_id ON user_data(resource_id);
CREATE INDEX idx_tasks_resource_id ON tasks(resource_id);
-- Multi-column indexes for common query patterns
CREATE INDEX idx_user_data_org_resource ON user_data(org_id, resource_id);
-- Soft-delete queries
CREATE INDEX idx_resources_org_archived ON resources(org_id, archived_at);
-- Total: 89 indexes across 32 tables
```I ran the migration. My hand literally hovered over the Enter key for a moment, wondering if I was about to make things worse.
Migration time: 4 minutes.
—
The Results: From Disaster to Launch-Ready
Before (No Indexes)
```sql
Seq Scan on user_data
Execution Time: 843.271 ms
Rows Removed by Filter: 12,834
```After (With Indexes)
```sql
Index Scan using idx_user_data_org_resource
Execution Time: 2.847 ms
```843ms → 2.8ms
That’s 296x faster for a single query.
Real-world impact:
– Dashboard load: 2-3 seconds → 120ms
– Resource lists: 1+ second → 45ms
– Data queries: 850ms → 12ms
Average improvement: 20-40x faster. Some complex joins with multiple foreign keys? 100x faster.
The difference was night and day. The platform went from “Is this broken?” to “Wow, this is fast.”
—
Why This Matters for Multi-Tenant SaaS
If you’re building multi-tenant SaaS with Row-Level Security, this is absolutely critical.
RLS policies run on every single query:
```sql
CREATE POLICY resources_org_isolation ON resources
USING (org_id = get_user_org_id());
```Without an index on `org_id`, this policy forces a sequential scan on every query. Even simple SELECT statements become painfully slow.
The cost: Multi-tenant isolation without indexes = expensive infrastructure + slow user experience = churn.
The business lesson: Security features without performance optimization aren’t actually secure—because users will leave before they experience your security.
—
The Pattern: What to Index
After this expensive lesson, here’s my checklist for every new table:
Always index these:
1. Foreign key columns – Every single one
2. Columns in RLS policies – Especially `org_id` in multi-tenant apps
3. Columns in WHERE clauses – If you filter by it frequently, index it
4. Columns in ORDER BY – Sorting without indexes kills performance
Consider multi-column indexes:
```sql
-- For: WHERE org_id = X AND resource_id = Y
CREATE INDEX idx_table_org_resource ON table(org_id, resource_id);
```Don’t over-index: Each index costs storage, write performance, and maintenance. Rule of thumb: If it appears in WHERE/JOIN/ORDER BY frequently, index it. Otherwise, don’t.
—
How to Check Your Database Right Now
Run this diagnostic query:
```sql
SELECT
c.conrelid::regclass AS table_name,
a.attname AS column_name
FROM pg_constraint c
JOIN pg_attribute a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid
WHERE c.contype = 'f'
AND NOT EXISTS (
SELECT 1 FROM pg_index i
WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)
);
```If you get results, you have missing indexes.
Then find your slow queries:
```sql
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC;
```Use `EXPLAIN ANALYZE` on the slow ones to verify you’re seeing sequential scans.
—
Lessons Learned
1. Postgres assumptions are dangerous
I assumed foreign keys were indexed. They’re not. Always verify.
2. RLS needs indexes
Row-Level Security is useless—actually worse than useless—without proper indexes. It adds overhead to every query.
3. Multi-tenant = index org_id everywhere
In multi-tenant architecture, `org_id` appears in almost every query. Index it on every table. No exceptions.
4. Performance is a business problem
Users don’t care that your SQL is technically correct if the page takes 3 seconds to load. Fast queries = better conversion = revenue.
5. Add indexes early
Adding indexes to empty tables is instant. Adding them to tables with millions of rows takes hours and locks the table. Do it during initial development.
6. Measure everything
Use `EXPLAIN ANALYZE` before and after. Prove the improvement with data.
—
Your Checklist
For every new table you create:
```sql
CREATE TABLE new_table (
id UUID PRIMARY KEY,
org_id UUID REFERENCES organizations(id),
resource_id UUID REFERENCES resources(id),
created_at TIMESTAMPTZ,
archived_at TIMESTAMPTZ
);
-- ✅ Index foreign keys
CREATE INDEX idx_new_table_org_id ON new_table(org_id);
CREATE INDEX idx_new_table_resource_id ON new_table(resource_id);
-- ✅ Index RLS columns + common filters
CREATE INDEX idx_new_table_org_archived ON new_table(org_id, archived_at);
-- ✅ Index sort columns
CREATE INDEX idx_new_table_created ON new_table(created_at DESC);
-- ✅ Create RLS policies
CREATE POLICY new_table_org_isolation ON new_table
FOR ALL TO authenticated
USING (org_id = get_user_org_id());
```—
Final Thoughts
89 missing indexes. 2 weeks of debugging. One diagnostic query to find them all. 4 minutes to fix.
The irony? Postgres gives you all the tools to diagnose this. I just didn’t know to look. Now every table I create gets indexed immediately—foreign keys, RLS columns, sort fields, everything.
The 4-minute migration that added 89 indexes saved weeks of future optimization work and prevented a launch disaster.
For founders: Performance isn’t just an engineering problem. It’s a conversion problem, a retention problem, and a credibility problem. That “technically correct” database that’s too slow to use? It’s costing you customers. Invest in performance early.
For engineers: Postgres won’t auto-index your foreign keys. But you should. Your users (and your database CPU) will thank you.
—
Building multi-tenant SaaS with Postgres? I learned this lesson the hard way so you don’t have to. Check your database now before your users notice.
Request alpha access at https://stratum.chandlernguyen.com/request-invitation
—
Still learning that “it works” and “it works fast” are different goals—and only one of them ships.
—