Part 27: Performance Pitfalls of High-Offset Queries
Deep-dive into disk I/O, cache thrashing, and database crashes caused by deep pagination bots.
1. Why Offset Queries Thrash Memory
When a search engine scraper requests ?page=50000:
1. The database reads 500,000 index pages into the buffer pool.
2. Cold pages displace hot cache data, causing cache pollution.
3. Disk I/O spikes to 100%.
2. Remediation Checklist
- Limit maximum allowable offset (e.g. restrict users to the first 100 pages).
- Switch to keyset pagination for API consumers.
Key Takeaways
- Prevent bots from hammering deep offset pages.
- Enforce strict API limits on maximum offset values.
In Part 28, we will learn the SQL Order of Execution: Why You Can't Use Aliases in WHERE Clauses!