Course contentsShow
← System Design
Lesson 1897 of 1,91945. API Design and VersioningFree lesson

Performance Considerations and Limits

Setting max page sizes, optimizing queries with filters/sorts, and preventing expensive operations.

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 WHERE clauses before LIMIT/OFFSET to 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.