When `Skip()` Lies: The Hidden Pagination Bug Killing Production APIs

On a fateful 3 AM call, a production API outage revealed a hidden pagination bug that was silently killing customer APIs. Our main endpoint, supporting dozens of front-end applications, started throwing HTTP 500 errors under real traffic. The logs indicated "Request Entity Too Large" errors from SQL Server, despite flawless local testing with 10,000 records.

The investigation uncovered an EF Core pagination pattern that wreaked havoc on production API performance. The code used OFFSET/FETCH pagination, which created a subquery with ROW_NUMBER() to slice the result set. As traffic grew, these queries stacked up, consuming increasing amounts of tempdb memory until SQL Server choked on the memory requirements.

The root cause lay in the inefficiency of OFFSET-based pagination. Every time a page request was made, SQL Server had to scan the entire table, apply row numbers, and filter the results. This O(n) work repeated on every request, making the database perform exponentially worse as more requests came in.

To fix the problem, keyset pagination was implemented. This approach remembers the last fetched ID and uses a WHERE clause to fetch the next page of results. The generated SQL became much simpler, involving just an index seek without any subqueries or window functions. Additionally, the data was projected to DTOs (Data Transfer Objects) before pagination, eliminating the need to fetch unnecessary columns.

By making these changes, the API avoided the catastrophic failure that had plagued it in production. The new keyset pagination method ensured that the database could efficiently handle large amounts of traffic without running into memory or performance issues.