Performance Considerations and Limits
You'll learn how to protect your API and database from expensive queries by setting sensible boundaries and optimizing query execution.
Why Limits Matter
Without constraints, a single API request could ask for all million records at once, crash your database, or tie up resources for minutes. Just as a restaurant won't let you order the entire menu at once, APIs need guardrails to remain responsive and stable under load.
Setting Maximum Page Sizes
Always enforce a maximum page size (e.g., max_page_size=100). If a client requests ?limit=10000, cap it:
requested_limit = min(user_limit, max_page_size)
This prevents memory exhaustion when materializing large result sets and keeps response times predictable.
Optimizing Queries with Filters and Sorts
Ensure database indexes exist for all filterable and sortable fields. Without an index on created_at, sorting by date forces a full table scan—disastrous at scale.
- Filter first, paginate second: Apply
WHEREclauses beforeLIMIT/OFFSETto reduce the working set - Validate sort fields: Only allow sorting on indexed columns; reject arbitrary field names
- Use query timeouts: Cancel queries exceeding a threshold (e.g., 5 seconds)
Preventing Expensive Operations
- Block full scans: Reject requests without filters on massive tables
- Rate limit complex queries: Track cost (filters + sorts) per user
- Cache frequent queries: Expensive aggregations or reports should be pre-computed or cached
Key Takeaway: Protecting API performance means enforcing maximum page sizes, ensuring queries leverage indexes on filtered/sorted fields, and blocking or throttling operations that could overwhelm your backend resources.