Capstone: Performance and Production Checklist
Measure the query with representative data and inspect its plan. Consider indexes on foreign keys and filters, but avoid adding indexes without evidence. Confirm that the application uses bound parameters, a bounded connection pool, transaction handling, and least-privilege roles. Ensure backups can be restored and schema migrations can be deployed safely.
A production-ready database is not just a schema. It also requires monitoring, access control, backup and recovery procedures, capacity planning, and a clear operational owner. Document assumptions so future developers can safely change the model.
Performance review commands
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.id, c.title, COUNT(e.user_id) AS enrollment_count
FROM courses AS c
LEFT JOIN enrollments AS e ON e.course_id = c.id
GROUP BY c.id, c.title
ORDER BY enrollment_count DESC, c.id
LIMIT 20;💡 Run EXPLAIN ANALYZE in an appropriate environment because it executes the query. Compare actual rows and timings, and repeat tests with realistic table sizes and statistics.
Key Takeaways
- Performance work should start with measurement.
- Production readiness includes security, backups, monitoring, and migrations.
- Document operational assumptions and recovery procedures.
Knowledge Check
What is the best first step when a query is slow?