Skip to content
Published ― 11 min read

Building Pagination with Offset and Cursor Technique

Imagine building a Twitter/X feed where millions of posts stream in real time, and users demand a seamless scroll through an endless cascade of content. A naive approach to loading this data could grind your servers to a halt or leave users staring at a spinning loader. How do you deliver a fast, reliable experience at scale? In this post, we dive into offset-based and cursor-based pagination, dissecting their mechanics, system design trade-offs, and practical implementations in Node.js with an abstracted database layer. We’ll also integrate them with a React frontend, revealing why cursor-based pagination is the go-to for dynamic, high-traffic systems.

Why Pagination Matters

Imagine scrolling through a Twitter/X timeline with thousands of posts streaming in. Without pagination, loading every post at once would crash the browser or slow the app to a crawl. Pagination breaks data into manageable chunks, balancing backend performance with frontend usability. But choosing the right approach is key. Offset-based pagination is simple but struggles with large or dynamic datasets, while cursor-based pagination is more complex but built for scale and speed. Let’s break them down, with a focus on why cursor-based shines for systems like social media feeds.

Offset-Based Pagination: The Easy but Limited Choice

How It Works

Offset-based pagination is like jumping to a specific page in a book. You tell the server, “Skip the first 20 records and grab the next 10.” In database terms:

SELECT * FROM posts
ORDER BY id ASC
LIMIT 10 OFFSET 20;

This fetches records 21–30, assuming 10 items per page. Users see “Page 3” with navigation buttons.

The Downside

The catch: the database scans all rows up to the offset, even if it doesn’t return them. For small datasets, this is fine. But with 100,000 posts in a social media feed, fetching page 1000 (offset 9990) means scanning 9990 rows just to skip them. This slows queries, spikes resource usage, and frustrates users. Plus, in a real-time system where new posts arrive constantly, offsets can shift, causing duplicates or skipped posts.

System Design Perspective

From a system design angle, offset-based pagination is:

  • Stateful: Offsets require tracking, complicating distributed systems.
  • Inefficient: Large offsets hammer the database, increasing CPU and I/O load.
  • Inconsistent: Frequent data changes (e.g., new posts) break pagination, leading to unreliable results.

Pros and Cons

  • Pros:
    • Super easy to implement.
    • Great for fixed-page UIs (e.g., “Page 1, 2, 3”).
    • Fine for small, static datasets.
  • Cons:
    • Performance tanks with large offsets.
    • Struggles with dynamic, real-time data.
    • Not scalable for high-traffic systems.

Node.js Implementation

Let’s implement offset-based pagination using Express.js and an abstracted db variable from ../db, acting like an ORM.

const express = require('express')
const db = require('../db')
 
const app = express()
 
app.get('/api/posts', async (req, res) => {
  try {
    const limit = parseInt(req.query.limit) || 10
    const page = parseInt(req.query.page) || 1
    const offset = (page - 1) * limit
 
    const posts = await db.findAll({
      table: 'posts',
      limit,
      offset,
      order: [['id', 'ASC']],
    })
 
    const total = await db.count({ table: 'posts' })
    const totalPages = Math.ceil(total / limit)
 
    res.json({
      data: posts,
      pagination: {
        page,
        limit,
        total,
        totalPages,
      },
    })
  } catch (error) {
    console.error(error)
    res.status(500).json({ error: 'Something went wrong' })
  }
})
 
app.listen(3000, () => console.log('Server running on port 3000'))

How It Works:

  • Query Params: limit (e.g., 10) and page (e.g., 3).
  • Offset: (page - 1) * limit skips records.
  • Response: Data plus metadata (current page, total pages).

Example Request:

GET /api/posts?page=2&limit=10

Returns records 11–20.

This works for small apps but falters with dynamic, real-time data like social media feeds.

Cursor-Based Pagination: The Real-Time System Champion

How It Works

Cursor-based pagination is like using a bookmark to track your place. You say, “Give me the next 10 records after this one.” The “cursor” is a unique, sortable field—often an id or timestamp. In SQL:

SELECT * FROM posts
WHERE id > 100
ORDER BY id ASC
LIMIT 10;

Here, 100 is the cursor (the last id from the previous batch). The database jumps to the right spot, assuming an index on id.

Why It Shines: A System Design Perspective

Cursor-based pagination is a system designer’s dream for real-time systems like Twitter/X feeds, where thousands of posts stream in and users expect a seamless scrolling experience. Here’s why it excels:

  1. Database Efficiency:

    • Unlike offsets, which scan all prior rows, cursor-based queries use an index to start at the cursor. This keeps query times constant—whether fetching the first 10 messages or the 100,000th.
    • Example: Fetching messages after id=99,000 is as fast as starting at id=1, thanks to indexed lookups. This slashes CPU and I/O usage, keeping response times sub-second.
    • For feeds with millions of posts, this efficiency prevents database bottlenecks, ensuring smooth performance.

  2. Scalability:

    • Cursor-based pagination is stateless—the client sends the cursor (e.g., in the query string), so the server doesn’t track sessions. This simplifies load balancing and scaling in distributed systems.
    • In sharded databases, common for high-throughput apps like Twitter/X, cursors (e.g., globally unique IDs) work across shards without complex logic.
    • This supports millions of users scrolling feeds simultaneously.

  3. Resilience to Real-Time Updates:

    • Social media feeds are dynamic, with new posts arriving constantly. Offset-based pagination struggles here, as new records shift offsets, causing skips or duplicates. Cursors, tied to a unique field like id, ensure stable pagination, delivering a consistent view even as new posts flood in.
    • This is critical for Twitter/X, where users expect new posts without losing their place in the feed.

  4. Seamless UX Integration:

    • Cursors enable infinite scrolling, where new posts appear at the top while users scroll through older ones. This mimics the Twitter/X feed experience, keeping the UI fluid and engaging.
    • The cursor’s simplicity allows client-side caching or bookmarking, reducing redundant API calls and enhancing UX.

  5. Predictable Performance:

    • With proper indexing, query performance is consistent, regardless of dataset size. This predictability is crucial for real-time systems, where latency spikes ruin the experience.
    • For feeds, where users expect instant post loading, cursors deliver reliable performance.

  6. Cost Efficiency:

    • By minimizing database load, cursor-based pagination reduces infrastructure costs—a big win for platforms handling massive, real-time traffic.
    • Example: A Twitter/X feed with millions of active users can serve posts without triggering expensive, long-running queries.

Trade-Offs

  • Complexity: Managing cursors adds backend and frontend logic.
  • Cursor Field: Requires a unique, sortable field (e.g., id or timestamp). Composite cursors (e.g., timestamp + id) handle edge cases but increase complexity.
  • UI Fit: Best for infinite scrolling, less intuitive for fixed-page UIs.

Node.js Implementation

Here’s cursor-based pagination with the db variable:

const express = require('express')
const db = require('../db')
 
const app = express()
 
app.get('/api/posts', async (req, res) => {
  try {
    const limit = parseInt(req.query.limit) || 10
    const cursor = req.query.cursor ? parseInt(req.query.cursor) : null
 
    const where = cursor ? { id: { gt: cursor } } : {}
 
    const posts = await db.findAll({
      table: 'posts',
      where,
      limit,
      order: [['id', 'ASC']],
      attributes: ['id', 'content', 'timestamp', 'username'],
    })
 
    const nextCursor = posts.length === limit ? posts[posts.length - 1].id : null
 
    res.json({
      data: posts,
      nextCursor,
    })
  } catch (error) {
    console.error(error)
    res.status(500).json({ error: 'Something went wrong' })
  }
})
 
app.listen(3000, () => console.log('Server running on port 3000'))

How It Works:

  • Query Params: limit (e.g., 10) and cursor (e.g., 100).
  • Where Clause: Filters records where id > cursor.
  • Response: Data and nextCursor (or null if done).
  • System Design Note: Stateless cursors and indexed queries ensure scalability and low latency, ideal for real-time feed systems.

Example Requests:

  1. GET /api/posts?limit=10
    Returns posts 1–10, nextCursor: 10.
  2. GET /api/posts?limit=10&cursor=10
    Returns posts 11–20.

Frontend Integration: Bringing It to Life with React

Pagination needs to shine on the frontend. Let’s integrate both approaches in a React app, styled for a Twitter/X feed interface.

Offset-Based Pagination in React

For fixed-page navigation:

import React, { useState, useEffect } from 'react'
 
export function Feed() {
  const [posts, setPosts] = useState([])
  const [page, setPage] = useState(1)
  const [totalPages, setTotalPages] = useState(1)
 
  useEffect(() => {
    fetchPosts(page)
  }, [page])
 
  async function fetchPosts(pageNum) {
    try {
      const response = await fetch(`/api/posts?page=${pageNum}&limit=10`)
      const { data, pagination } = await response.json()
      setPosts(data)
      setTotalPages(pagination.totalPages)
    } catch (error) {
      console.error('Fetch error:', error)
    }
  }
 
  return (
    <div>
      <h2>Social Feed</h2>
      <ul>
        {posts.map((post) => (
          <li key={post.id}>
            <strong>{post.username}</strong>: {post.content} <em>({new Date(post.timestamp).toLocaleTimeString()})</em>
          </li>
        ))}
      </ul>
      <div>
        <button onClick={() => setPage((p) => Math.max(p - 1, 1))} disabled={page === 1}>
          Previous
        </button>
        <span>
          {' '}
          Page {page} of {totalPages}{' '}
        </span>
        <button onClick={() => setPage((p) => Math.min(p + 1, totalPages))} disabled={page === totalPages}>
          Next
        </button>
      </div>
    </div>
  )
}

This offers a paginated feed, but it’s clunky for real-time systems.

Cursor-Based Pagination in React

For infinite scrolling:

import React, { useState, useEffect, useCallback } from 'react'
 
function Feed() {
  const [posts, setPosts] = useState([])
  const [cursor, setCursor] = useState(null)
  const [hasMore, setHasMore] = useState(true)
 
  const loadMore = useCallback(async () => {
    if (!hasMore) return
    try {
      const response = await fetch(`/api/posts?cursor=${cursor || ''}&limit=10`)
      const { data, nextCursor } = await response.json()
      setPosts((prev) => [...prev, ...data])
      setCursor(nextCursor)
      setHasMore(!!nextCursor)
    } catch (error) {
      console.error('Fetch error:', error)
    }
  }, [cursor, hasMore])
 
  useEffect(() => {
    loadMore()
  }, [])
 
  useEffect(() => {
    const handleScroll = () => {
      if (window.innerHeight + window.scrollY >= document.body.offsetHeight - 100 && hasMore) {
        loadMore()
      }
    }
    window.addEventListener('scroll', handleScroll)
    return () => window.removeEventListener('scroll', handleScroll)
  }, [loadMore, hasMore])
 
  return (
    <div>
      <h2>Social Feed</h2>
      <ul>
        {posts.map((post) => (
          <li key={post.id}>
            <strong>{post.username}</strong>: {post.content} <em>({new Date(post.timestamp).toLocaleTimeString()})</em>
          </li>
        ))}
      </ul>
      {hasMore && <p>Loading more posts...</p>}
    </div>
  )
}

This mimics a Twitter/X feed, loading older posts as users scroll, with new posts appearing at the top.

Real-World Example: Twitter/X Social Media Feed

Let’s dive into a Twitter/X social media feed, where thousands of posts stream in as users share updates in real time. Users want to scroll through their timeline, potentially spanning millions of posts, while new content keeps flowing.

  • Offset-Based: Works for small feeds with a few hundred posts. Users could click through pages, and small offsets keep queries manageable. But in a busy feed with 1,000,000 posts, fetching page 1000 (offset 999,000) is a disaster—queries take seconds, database costs spike, and new posts shift offsets, causing skipped or repeated content. This disrupts the real-time experience.
  • Cursor-Based: The ideal choice for a Twitter/X feed. With thousands of posts arriving constantly, cursor-based pagination ensures every query is fast, using indexed lookups to fetch the next batch of posts, even deep in the feed history. Infinite scrolling lets users browse older posts while new ones load seamlessly at the top, delivering a fluid, real-time UX. Cursors tied to unique IDs handle the influx of new posts without losing track, ensuring no duplicates or skips.

Why Cursor-Based Wins Here:

  • Performance: Fetching the 100,000th post is as fast as the first, keeping the feed responsive for millions of users.
  • Scalability: Stateless cursors and lightweight queries handle massive, real-time traffic across distributed servers.
  • UX: Infinite scrolling aligns with social media expectations, letting users dive into history without missing new posts.
  • Reliability: Cursors ensure stable pagination despite rapid data updates, critical for real-time systems.

System Design Note: The stateless cursor and indexed query ensure fast, scalable performance, while sorting by timestamp and id provides a consistent view of the feed, even with rapid post updates.

System Design Considerations

Pagination is a system design challenge. Here’s how the approaches compare:

  • Database Load:
    • Offset: High for large offsets, scanning all prior rows.
    • Cursor: Low, using indexed lookups, ideal for real-time systems.
  • Scalability:
    • Offset: Struggles in distributed systems due to stateful offsets.
    • Cursor: Scales effortlessly with stateless, shard-friendly cursors.
  • Consistency:
    • Offset: Prone to skips/duplicates with frequent updates.
    • Cursor: Stable, anchored to unique fields, perfect for live data.
  • Caching:
    • Offset: Tough to cache due to shifting offsets.
    • Cursor: Cache-friendly, as cursors are stable.

Cursor-based pagination’s lightweight, stateless design makes it a go-to for real-time systems like social media feeds, handling dynamic data with ease.

When to Use What

Here’s your guide to picking the right approach:

Key Factors

  • UI Needs: Fixed pages? Offsets. Auto-updating feeds? Cursors.
  • Performance: Real-time systems like live chats need cursor-based speed.
  • Data Updates: Frequent new records? Cursors handle volatility best.

Comparison Table

FeatureOffset-BasedCursor-Based
PerformancePoor for large offsetsExcellent with indexing
ComplexitySimpleModerate
UI FlexibilityFixed pagesInfinite Scrolling
ScalabilityLimitedHigh
Database LoadHigh for large offsetsLow
Data ConsistencyProne to inconsistenciesStable

Decision Tree

Is your dataset large or frequently updated?
├── Yes: Use cursor-based pagination
└── No: Is simplicity more important than performance?
    ├── Yes: Use offset-based pagination
    └── No: Use cursor-based for future-proofing

Best Practices

  • Indexing: Index cursor fields (e.g., id, timestamp) for fast queries.
  • Validation: Sanitize query parameters to prevent errors or attacks.
  • Caching: Use CDNs or server-side caching to reduce database hits.
  • Testing: Simulate large, dynamic datasets to benchmark performance.
  • Frontend: Debounce scroll events for cursor-based pagination to avoid rapid API calls.

Wrapping Up

Pagination may seem like a small piece of code, but its impact on performance and user experience is profound. Offset-based pagination offers a quick solution for small apps or simple UIs, but it falters under the demands of real-time systems. Cursor-based pagination, with its scalability, efficiency, and resilience to rapid updates, is the clear choice for dynamic platforms like Twitter/X feeds. Its system design advantages—fast queries, statelessness, and consistent data delivery—make it essential for modern applications.

Spin up a JavaScript server, integrate it with a React frontend, and experiment with both approaches to see their impact on your data.

If you found this article helpful, please let me know on Twitter!