Latest / The API Podcast with Fexingo: REST, GraphQL, and Modern Web APIs / API Pagination Patterns For Scalable Systems
Transcript
- Lucas: Let’s talk about the most invisible part of your API design: pagination. Most engineers treat it like an afterthought, slapping on a limit parameter and hoping for the best. But by September twenty twenty six, we’ve seen enough catastrophic failures in high-volume data retrieval to know that naive pagination is actually a structural liability. Luna: I think you’re underestimating how many teams still default to offset and limit just because it’s the easiest to implement initially. It feels cheap until the dataset grows past a few million rows. Lucas: Exactly. And the cost isn’t just computational; it’s reliability. When you use offsets, every page shift requires the database to scan and discard all preceding rows. If another record gets inserted or deleted while a user is scrolling, they see duplicates or gaps. That’s not a minor bug; it’s a trust erosion event for your product. Luna: Right, so the phantom record problem is really the killer here. Users expect seamless infinite scroll, but the underlying mechanics often contradict that promise. Lucas: Which brings us to the donation segment, because keeping these deep dives ad-free matters. If today’s tech conversation gave you something usable, consider supporting us directly. A couple of dollars a month is genuinely what keeps these going, buy me a coffee dot com slash fexingo, if you've gotten something out of them. Luna: Yeah, small contributions add up fast when you’re trying to maintain this level of technical depth without corporate sponsors dictating the narrative. Lucas: Back to the mechanics. The industry standard pivot is toward cursor-based pagination, often called keyset pagination. Instead of asking the database for rows five thousand through five thousand and ten, you ask for everything after a specific unique identifier. Luna: So you’re passing the last seen ID rather than a positional index. That eliminates the scanning overhead entirely, right? Lucas: Precisely. It turns a linear scan into a direct lookup. But there’s a catch. Your endpoint needs a stable sort order. If you’re sorting by timestamp alone, and two records have the exact same timestamp, your cursor becomes ambiguous. You need a composite key, usually combining the sort column with the primary ID. Luna: That sounds tricky to enforce at the application layer. Does that mean you have to redesign how your queries are constructed across the board? Lucas: Not necessarily. You just need to ensure the WHERE clause includes both the sort value and the ID. For example, fetch rows where timestamp equals the cursor timestamp AND id is greater than the cursor id. It’s a small SQL change that yields massive performance gains for large result sets. Luna: I remember reading about a logistics platform that switched from offsets to keysets and saw their p ninety five latency drop by half. The database load didn’t increase as the table grew. Lucas: That’s the classic benefit. With offsets, latency drifts upward as the offset number increases. With keysets, latency remains constant regardless of how far into the dataset you paginate. That predictability is what allows you to scale horizontally without fearing sudden spikes in CPU usage. Luna: But does that work well for analytics dashboards where users jump around randomly? Jumping from page one to page fifty seems impossible with cursors. Lucas: You’re right. Cursor pagination is terrible for random access. It’s designed for sequential traversal. So you actually need a hybrid approach. Use keysets for feed-style infinite scroll, but keep offset-based pagination for search results or admin panels where jumping to specific pages is a core requirement. Luna: So don’t force a single pattern on all endpoints. Match the strategy to the user behavior. That’s a nuance I think a lot of senior devs miss when they try to enforce strict standards too early. Lucas: Agreed. Another subtle issue is how you handle the total count. With offsets, you can easily tell the user there are ten thousand items total. With keysets, counting the entire dataset is expensive. Many APIs drop the total count header for keyset responses to save that extra query. Luna: That’s a huge UX trade-off. Users often want to know how much content is left before they commit to scrolling. How do other companies solve that without killing performance? Lucas: Some use approximate counts or cache the total separately. Others simply omit it once the dataset exceeds a certain threshold. It’s about managing expectations. If you’re building a social feed, nobody cares about the total number of posts. They care about the next thirty seconds of entertainment. Luna: Fair point. In consumer apps, engagement beats precision. But in B2B tools, knowing the scope of data is critical for decision-making. Lucas: Absolutely. And this brings up another architectural consideration: GraphQL. Since GraphQL lets clients specify exactly which fields they need, pagination in GraphQL is often handled via connections, which inherently support cursor-based navigation. It’s baked into the spec, which reduces the friction for developers. Luna: Does that make REST obsolete for this use case? Or are you seeing REST APIs adopt similar connection-like structures? Lucas: REST isn’t obsolete, but it’s evolving. Modern REST frameworks are adding standardized link headers that mimic GraphQL’s cursor behavior. You’ll see Next-Page and Previous-Page links in the response metadata, making it easier for front-end libraries to handle the flow without manual state management. Luna: That’s interesting. It feels like the boundaries between REST and GraphQL are blurring when it comes to advanced features like pagination and filtering. Lucas: They are. The underlying principle is the same: give the client control over data fetching efficiency. Whether you call it a resource or a field, the goal is to minimize payload size and maximize retrieval speed. Luna: What about error handling? If a cursor becomes invalid because the underlying data changed, how should the API respond? Return a four zero four? Lucas: A four zero four Not Found is appropriate if the cursor points to a non-existent record due to deletion. But you also need to handle stale cursors gracefully. Some APIs return a warning header indicating that the result set might be incomplete or shifted, allowing the client to refresh its state. Luna: That kind of transparency is rare. Most APIs just crash or return empty arrays when things get weird. Building resilience into the pagination logic is definitely a mark of mature engineering. Lucas: It is. And let’s not forget security. Offset-based pagination can sometimes be exploited for scraping if the attacker iterates through thousands of pages quickly. Keysets mitigate this slightly because you can’t easily guess the next valid cursor without the previous response. Luna: So it’s a secondary defense mechanism. Combined with rate limiting, it makes bulk data extraction significantly harder. Lucas: Correct. It adds friction to bad actors without impacting legitimate users. Now, shifting gears slightly to implementation details. How are you handling sorting stability in your current projects? Are you relying on natural insertion order or explicit timestamps? Luna: Mostly timestamps, but I’ve started adding a secondary sort by ID to break ties. It’s become a habit rather than an afterthought. I’ve been burned by race conditions before where two records arrived simultaneously. Lucas: Good instinct. Always assume concurrent writes will happen. Your database engine handles concurrency, but your application logic must account for it. If you’re using PostgreSQL, for instance, the visibility rules can cause unexpected ordering if you’re not careful with transaction isolation levels. Luna: Transaction isolation is a whole other beast. I tend to stick to Read Committed and rely on the ID tie-breaker to keep things deterministic. Lucas: That’s a safe default. For higher consistency requirements, you might look into serializable isolation, but that comes with its own locking penalties. It’s a spectrum of trade-offs. Luna: Definitely. I wonder if new databases like DuckDB or ClickHouse are changing how we think about analytical pagination since they’re optimized for wide tables. Lucas: They are. Columnar stores handle range scans differently. They might not need the same cursor optimizations because they skip columns entirely. But for general-purpose OLTP databases, the keyset pattern remains king. Luna: It’s fascinating how a simple concept like pagination has such deep implications for system design. It’s not just about showing data; it’s about controlling the flow of information. Lucas: Exactly. It’s the gatekeeper of user experience and infrastructure cost. Get it wrong, and you pay in both latency and developer frustration. Get it right, and it disappears into the background. Luna: Here’s a question for the listeners who are implementing this now: are you planning a migration from offsets, or are you starting fresh with keysets on new endpoints? Lucas: That’s the million-dollar question. Migration is painful because you have to support both styles temporarily to avoid breaking existing clients. Starting fresh is easier but risks fragmenting your API surface area. Luna: Fragmentation is a real concern. Consistency across endpoints builds better mental models for your consumers. Mixing patterns forces them to write different code for different parts of your service. Lucas: True. Maybe the answer is to standardize on keysets everywhere except where random access is explicitly required. It simplifies the documentation and the testing matrix. Luna: Simplification is always a win. If you can reduce the cognitive load for your API consumers, you reduce support tickets and integration errors. Lucas: Well said. As we wrap up, I’d love to hear how you’re balancing these constraints in your current stack. Is there a specific pagination pain point that’s keeping you up at night? Luna: Or maybe a success story where fixing pagination saved your team from a weekend outage. Share those war stories in the comments. Lucas: We read every single one. Keep building robust systems, and we’ll see you in the next episode.