A database query requests data or changes from a database. Query performance becomes a scalability problem when growing tables turn millisecond operations into seconds or minutes, making production systems unusable despite successful tests with sample data. section 4.5.1 section 5.4.4
Protect query performance with these practices:
- Eliminate N+1 queries by loading related data through joins, batch loading, or ORM eager-loading features such as Rails includes, Django select_related, and Django prefetch_related. section 4.5.1
- Create indexes for columns used in filtering, joining, and ordering, then review actual query patterns to balance lookup speed against storage and write-maintenance costs. section 4.5.1 section 5.4.4
- Remove indexes temporarily during large bulk copies when write overhead dominates, and rebuild them after the data migration completes. section 5.4.4
- Measure production behavior rather than staging behavior, using slow-query logs, APM data, statistics views, and representative data volumes. section 5.4.4 section 6.2.5
Budget queries by user impact
A streaming service gives the continue-watching query a 30 ms p95 budget because users see it immediately after opening the app. Recommendations receive 100 ms, while viewing-history aggregation runs asynchronously. Individual budgets expose the query that consumes the endpoint's latency allowance before data growth affects users. section 6.2.5
Start with critical-path endpoints: list every query, measure each p95 using production data, subtract processing, serialization, and network time from the endpoint budget, and assign the remaining time across queries. Alert when a query exceeds its allowance, then add indexes, restructure it, batch requests, or introduce caching. At larger scale, choose partitioning from workload measurements and account for the expertise required to operate the architecture. section 6.2.5 section 4.5.1 section 4.5.6
