Back
•14 min read

Database Optimization for SaaS: PostgreSQL Performance Guide

How I reduced query times from 5s to 50ms through proper indexing and optimization.

The 3 Golden Rules

1. Index Everything You Query

-- Foreign keys
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- Filter columns
CREATE INDEX idx_posts_status ON posts(status);

-- Composite indexes
CREATE INDEX idx_orders_customer_date 
  ON orders(customer_id, created_at DESC);

2. Analyze Your Queries

EXPLAIN ANALYZE
SELECT * FROM posts 
WHERE user_id = 'xxx' 
  AND status = 'published'
ORDER BY created_at DESC
LIMIT 20;

-- Look for "Seq Scan" → needs index
-- Want to see "Index Scan" or "Index Only Scan"

3. Monitor Slow Queries

-- Enable pg_stat_statements
CREATE EXTENSION pg_stat_statements;

-- Find slowest queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Real Impact: Before & After

❌ Before Optimization

  • • Query time: 4.2s
  • • No indexes on filters
  • • SELECT * everywhere
  • • Users complaining

✓ After Optimization

  • • Query time: 48ms
  • • Proper composite indexes
  • • Select only needed columns
  • • Happy users, scaled to 50K

Common Mistakes to Avoid

  1. Not indexing foreign keys: Always index columns used in JOINs
  2. Using SELECT *: Only select columns you need
  3. Missing LIMIT: Always paginate large result sets
  4. N+1 queries: Use JOINs instead of multiple queries
  5. No query monitoring: You can't optimize what you don't measure

Need Database Optimization?

Expert PostgreSQL and Supabase performance optimization for production applications.

Optimize My Database →