Performance Optimization
Overview
A collection of performance optimization projects focused on database query tuning, API latency reduction, and frontend bundle optimization that resulted in significant improvements to user experience and infrastructure costs.
Problem
As the platform grew, we encountered several performance bottlenecks:
- Dashboard load times exceeding 5 seconds
- API endpoints timing out under load
- Database CPU consistently above 80%
- Frontend bundle size over 2MB
What I Built
Database Query Optimization
The Problem: Dashboard queries were taking 3-5 seconds due to:
- Missing indexes on frequently filtered columns
- N+1 queries in ORM layer
- Inefficient JOINs on large tables
- No query result caching
Analysis Process:
Loading diagram...
Key Optimizations:
- Index Strategy
sql
-- Before: Full table scan on status filter
-- After: Composite index on common filter patterns
CREATE INDEX CONCURRENTLY idx_applications_status_created
ON applications(status, created_at DESC)
WHERE deleted_at IS NULL;
-- Result: Query time 2.3s → 45ms
- N+1 Resolution
python
# Before: N+1 query pattern
applications = Application.objects.filter(job_id=job_id)
for app in applications:
print(app.candidate.name) # N additional queries
# After: Eager loading
applications = Application.objects.filter(job_id=job_id).select_related('candidate')
# Result: 1 query instead of N+1
- Materialized View for Dashboard
sql
CREATE MATERIALIZED VIEW dashboard_stats AS
SELECT
job_id,
COUNT(*) as total_applications,
COUNT(*) FILTER (WHERE status = 'pending') as pending_count,
-- ... more aggregations
FROM applications
GROUP BY job_id;
-- Refresh every 5 minutes
CREATE INDEX ON dashboard_stats(job_id);
-- Result: Dashboard query 3.2s → 12ms
API Latency Reduction
The Problem: Key API endpoints had p95 latencies > 2 seconds
Analysis:
| Endpoint | p95 Before | Bottleneck |
|---|---|---|
| /api/applications | 2.1s | Database queries |
| /api/candidates/search | 3.5s | Search indexing |
| /api/reports/generate | 8.2s | Sync processing |
Solutions Implemented:
Loading diagram...
- Response Caching
python
@cache_response(timeout=60, key_prefix='applications')
def get_applications(request, job_id):
# Cache hit: 5ms, Cache miss: 200ms
return Application.objects.filter(job_id=job_id)
- Search Optimization
python
# Before: LIKE queries on multiple columns
# After: PostgreSQL full-text search with GIN index
# Create search index
CREATE INDEX idx_candidates_search ON candidates
USING GIN (to_tsvector('english', name || ' ' || email || ' ' || skills));
# Query using FTS
SELECT * FROM candidates
WHERE to_tsvector('english', name || ' ' || email || ' ' || skills)
@@ plainto_tsquery('english', 'python react');
# Result: 3.5s → 120ms
- Async Report Generation
python
# Before: Synchronous generation blocking request
# After: Queue-based async generation
@api.post('/reports/generate')
def generate_report(request):
task = generate_report_task.delay(request.data)
return {'task_id': task.id, 'status': 'processing'}
@api.get('/reports/{task_id}')
def get_report_status(request, task_id):
task = AsyncResult(task_id)
if task.ready():
return {'status': 'complete', 'url': task.result}
return {'status': 'processing', 'progress': task.info.get('progress', 0)}
Frontend Bundle Optimization
The Problem:
- Initial bundle: 2.3MB
- Time to Interactive: 8.5s on 3G
- Lighthouse score: 42
Optimizations:
Loading diagram...
- Code Splitting by Route
typescript
// Before: All routes in main bundle
import Dashboard from './pages/Dashboard';
import Applications from './pages/Applications';
// After: Dynamic imports
const Dashboard = lazy(() => import('./pages/Dashboard'));
const Applications = lazy(() => import('./pages/Applications'));
- Tree Shaking Improvements
typescript
// Before: Import entire library
import _ from 'lodash';
const result = _.map(data, fn);
// After: Import only what's needed
import map from 'lodash/map';
const result = map(data, fn);
- Image Optimization
typescript
// Implemented responsive images with Next.js Image
<Image
src={candidate.avatar}
width={48}
height={48}
placeholder="blur"
loading="lazy"
/>
Results
Database Performance
| Metric | Before | After | Improvement |
|---|---|---|---|
| Dashboard load | 5.2s | 450ms | 91% |
| Avg query time | 180ms | 25ms | 86% |
| DB CPU usage | 82% | 35% | 57% |
API Latency
| Endpoint | p95 Before | p95 After | Improvement |
|---|---|---|---|
| /applications | 2.1s | 180ms | 91% |
| /candidates/search | 3.5s | 120ms | 97% |
| /reports/generate | 8.2s | 250ms* | 97% |
*Response time for queuing; actual generation is async
Frontend Performance
| Metric | Before | After | Improvement |
|---|---|---|---|
| Bundle size | 2.3MB | 380KB | 83% |
| TTI (3G) | 8.5s | 2.8s | 67% |
| Lighthouse | 42 | 89 | 112% |
Monitoring Setup
Dashboards Created:
- Query performance (slow queries, query volume)
- API latency percentiles by endpoint
- Cache hit rates
- Bundle size tracking in CI
Alerts:
- p95 latency > 500ms for critical endpoints
- Cache hit rate < 80%
- Database CPU > 70%
Tech Stack
- Database: PostgreSQL with pg_stat_statements
- Caching: Redis
- Search: PostgreSQL FTS, later Elasticsearch
- Monitoring: Datadog APM, pg_stat_statements
- Frontend: React, Next.js, webpack-bundle-analyzer
What I Owned
- Performance audit and prioritization
- Database query analysis and optimization
- Caching strategy implementation
- Frontend bundle optimization
- Performance monitoring setup
Lessons Learned
- Measure first: Don't optimize without data
- Low-hanging fruit: Indexes and caching solve 80% of issues
- Profile production: Staging doesn't have real data volumes
- Monitor continuously: Performance regresses without vigilance
- User-centric metrics: TTI matters more than bundle size
Links
- Performance audit template: Available on request
- Query optimization guide: Internal documentation