We audited and optimized every database query in the application.
Key improvements
N+1 queries resolved
The post listing page was making 1 query per post to fetch related data. Now it's a single query with proper joins:
-- Before: N+1 (1 + N queries)
SELECT * FROM posts WHERE project_id = ?;
-- Then for each post:
SELECT count(*) FROM subscribers WHERE project_id = ?;
-- After: single query
SELECT p.*, count(s.id) as subscriber_count
FROM posts p
LEFT JOIN subscribers s ON s.project_id = p.project_id
WHERE p.project_id = ?
GROUP BY p.id;Composite indexes
Added indexes for the most common access patterns — filtering by project + status + date.
Connection pooling
Tuned pool sizes for concurrent usage during peak hours.
Results
Average query time: 12ms (down from 45ms)
P95 query time: 28ms (down from 180ms)
Especially noticeable for projects with 100+ posts