Pagination: Offset vs Cursor Explained with Benchmarks

2026-01-27

TL;DR

Offset pagination is simple but slow on large datasets. Cursor pagination is fast and consistent. Use offset for small datasets, cursor for anything over 100k rows.

My API was timing out on page 1000 of search results. Each request took 8 seconds. Users complained. The database was melting under the load of OFFSET 50000 queries scanning millions of rows just to skip them.

I switched to cursor-based pagination and page 1000 loaded in 45ms. Same data, different approach, 177x faster.

Most developers default to offset pagination because it's simple. But it doesn't scale. Here's when to use each approach, with benchmarks and complete implementations.

The Three Pagination Approaches

1. Offset-Based Pagination

Skip N rows, return the next page:

-- Page 1
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 0;

-- Page 2
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 20;

-- Page 50
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 980;

API:

GET /api/posts?page=1&limit=20
GET /api/posts?page=2&limit=20
GET /api/posts?page=50&limit=20

2. Cursor-Based Pagination

Use last seen ID as cursor:

-- First page
SELECT * FROM posts ORDER BY id DESC LIMIT 20;

-- Next page (last ID was 1045)
SELECT * FROM posts WHERE id < 1045 ORDER BY id DESC LIMIT 20;

-- Next page (last ID was 1025)
SELECT * FROM posts WHERE id < 1025 ORDER BY id DESC LIMIT 20;

API:

GET /api/posts?limit=20
GET /api/posts?cursor=1045&limit=20
GET /api/posts?cursor=1025&limit=20

3. Keyset Pagination

Use last seen value for sorting column:

-- First page
SELECT * FROM posts ORDER BY created_at DESC, id DESC LIMIT 20;

-- Next page (last created_at was 2025-01-15 10:30:00, id was 1045)
SELECT * FROM posts
WHERE created_at < '2025-01-15 10:30:00'
   OR (created_at = '2025-01-15 10:30:00' AND id < 1045)
ORDER BY created_at DESC, id DESC
LIMIT 20;

API:

GET /api/posts?limit=20
GET /api/posts?cursor=2025-01-15T10:30:00Z:1045&limit=20

Performance Benchmarks

I tested all three on a table with 1 million posts:

Offset Pagination Performance

-- Page 1: Fast
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 0;
-- Time: 12ms

-- Page 50: Still fast
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 980;
-- Time: 45ms

-- Page 1000: Slow
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 19980;
-- Time: 890ms

-- Page 10000: Very slow
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 199980;
-- Time: 8,234ms

Why it's slow: Database scans and skips 199,980 rows just to return 20.

Cursor Pagination Performance

-- First page
SELECT * FROM posts ORDER BY id DESC LIMIT 20;
-- Time: 8ms

-- Page 50
SELECT * FROM posts WHERE id < 999020 ORDER BY id DESC LIMIT 20;
-- Time: 9ms

-- Page 1000
SELECT * FROM posts WHERE id < 980020 ORDER BY id DESC LIMIT 20;
-- Time: 11ms

-- Page 10000
SELECT * FROM posts WHERE id < 800020 ORDER BY id DESC LIMIT 20;
-- Time: 12ms

Why it's fast: Index scan from cursor position. Constant time regardless of page.

Performance Comparison Chart

Page	Offset Time	Cursor Time	Speedup
1	12ms	8ms	1.5x
50	45ms	9ms	5x
1000	890ms	11ms	81x
10000	8,234ms	12ms	686x

Cursor pagination performance is constant regardless of page number.

Complete Implementations

Offset Pagination (Express)

app.get('/api/posts', async (req, res) => {
    const page = parseInt(req.query.page) || 1;
    const limit = parseInt(req.query.limit) || 20;
    const offset = (page - 1) * limit;

    // Get total count (expensive on large tables)
    const [{ total }] = await db.query(
        'SELECT COUNT(*) as total FROM posts'
    );

    // Get page data
    const posts = await db.query(
        'SELECT * FROM posts ORDER BY created_at DESC LIMIT ? OFFSET ?',
        [limit, offset]
    );

    res.json({
        data: posts,
        pagination: {
            page,
            limit,
            total,
            totalPages: Math.ceil(total / limit),
            hasNext: page < Math.ceil(total / limit),
            hasPrev: page > 1
        }
    });
});

// Client usage
const response = await fetch('/api/posts?page=1&limit=20');
const { data, pagination } = await response.json();

console.log(`Page ${pagination.page} of ${pagination.totalPages}`);

Pros:

Simple to implement
Can jump to any page
Users see total pages
Familiar UX (page numbers)

Cons:

Slow on large offsets
COUNT(*) is expensive
Inconsistent during writes (items can shift between pages)
Memory intensive for database

Cursor Pagination (Express)

app.get('/api/posts', async (req, res) => {
    const cursor = req.query.cursor;
    const limit = parseInt(req.query.limit) || 20;

    let query = 'SELECT * FROM posts';
    let params = [];

    if (cursor) {
        query += ' WHERE id < ?';
        params.push(cursor);
    }

    query += ' ORDER BY id DESC LIMIT ?';
    params.push(limit + 1); // Fetch one extra to check if there's more

    const posts = await db.query(query, params);

    const hasMore = posts.length > limit;
    const data = hasMore ? posts.slice(0, limit) : posts;

    const nextCursor = hasMore ? data[data.length - 1].id : null;

    res.json({
        data,
        pagination: {
            nextCursor,
            hasMore,
            limit
        }
    });
});

// Client usage
let cursor = null;
const allPosts = [];

while (true) {
    const url = cursor
        ? `/api/posts?cursor=${cursor}&limit=20`
        : '/api/posts?limit=20';

    const response = await fetch(url);
    const { data, pagination } = await response.json();

    allPosts.push(...data);

    if (!pagination.hasMore) break;
    cursor = pagination.nextCursor;
}

Pros:

Fast regardless of position
No expensive COUNT(*)
Consistent results during writes
Scales to millions of rows

Cons:

Can't jump to specific page
No total count
URL parameters less intuitive
Can't implement "go to last page"

Keyset Pagination (Advanced)

For sorting by non-unique columns:

app.get('/api/posts', async (req, res) => {
    const limit = parseInt(req.query.limit) || 20;

    let query = 'SELECT * FROM posts';
    let params = [];

    if (req.query.cursor) {
        // Decode cursor: "timestamp:id"
        const [timestamp, id] = req.query.cursor.split(':');

        query += ` WHERE created_at < ?
                   OR (created_at = ? AND id < ?)`;
        params.push(timestamp, timestamp, id);
    }

    query += ' ORDER BY created_at DESC, id DESC LIMIT ?';
    params.push(limit + 1);

    const posts = await db.query(query, params);

    const hasMore = posts.length > limit;
    const data = hasMore ? posts.slice(0, limit) : posts;

    const nextCursor = hasMore
        ? `${data[data.length - 1].created_at}:${data[data.length - 1].id}`
        : null;

    res.json({
        data,
        pagination: {
            nextCursor,
            hasMore,
            limit
        }
    });
});

// Requires compound index
// CREATE INDEX idx_posts_created_id ON posts(created_at DESC, id DESC);

Why the complex WHERE clause:

-- If last post was created_at='2025-01-15 10:30:00', id=1045
-- We want posts where:

-- 1. created_at is earlier
WHERE created_at < '2025-01-15 10:30:00'

-- 2. OR same created_at but smaller id
OR (created_at = '2025-01-15 10:30:00' AND id < 1045)

This handles ties in the timestamp correctly.

The Consistency Problem with Offset

// User on page 2, viewing posts 21-40
GET /api/posts?page=2&limit=20

// Posts returned: IDs 980-961

// New post gets created (ID 1001)

// User clicks page 3
GET /api/posts?page=3&limit=20

// Posts returned: IDs 960-941
// But post 961 was on previous page!
// User sees duplicate

New writes shift results. With offset pagination:

New items cause duplicates
Deleted items cause gaps
Inconsistent user experience

With cursor pagination:

Cursor points to specific item
New items don't affect next page
Consistent iteration

Implementing Bidirectional Cursor Pagination

Support both next and previous:

app.get('/api/posts', async (req, res) => {
    const { cursor, direction = 'next', limit = 20 } = req.query;

    let query = 'SELECT * FROM posts';
    let params = [];

    if (cursor) {
        if (direction === 'next') {
            query += ' WHERE id < ?';
            params.push(cursor);
        } else {
            // Going backwards
            query += ' WHERE id > ?';
            params.push(cursor);
        }
    }

    // Order depends on direction
    query += direction === 'next'
        ? ' ORDER BY id DESC LIMIT ?'
        : ' ORDER BY id ASC LIMIT ?';

    params.push(parseInt(limit) + 1);

    let posts = await db.query(query, params);

    // If going backwards, reverse results
    if (direction === 'prev') {
        posts = posts.reverse();
    }

    const hasMore = posts.length > limit;
    const data = hasMore ? posts.slice(0, limit) : posts;

    res.json({
        data,
        pagination: {
            nextCursor: data.length > 0 ? data[data.length - 1].id : null,
            prevCursor: data.length > 0 ? data[0].id : null,
            hasNext: direction === 'next' ? hasMore : data.length > 0,
            hasPrev: direction === 'prev' ? hasMore : data.length > 0
        }
    });
});

// Usage
// Next page
GET /api/posts?cursor=1045&direction=next&limit=20

// Previous page
GET /api/posts?cursor=1025&direction=prev&limit=20

Cursor Encoding

Encode cursor to hide implementation details:

function encodeCursor(data) {
    // Base64 encode JSON
    return Buffer.from(JSON.stringify(data)).toString('base64');
}

function decodeCursor(cursor) {
    return JSON.parse(Buffer.from(cursor, 'base64').toString());
}

app.get('/api/posts', async (req, res) => {
    let whereClause = '';
    let params = [];

    if (req.query.cursor) {
        const { created_at, id } = decodeCursor(req.query.cursor);

        whereClause = ` WHERE created_at < ?
                        OR (created_at = ? AND id < ?)`;
        params = [created_at, created_at, id];
    }

    const posts = await db.query(
        `SELECT * FROM posts ${whereClause}
         ORDER BY created_at DESC, id DESC
         LIMIT ?`,
        [...params, limit + 1]
    );

    const hasMore = posts.length > limit;
    const data = hasMore ? posts.slice(0, limit) : posts;

    const nextCursor = hasMore
        ? encodeCursor({
            created_at: data[data.length - 1].created_at,
            id: data[data.length - 1].id
          })
        : null;

    res.json({
        data,
        pagination: { nextCursor, hasMore }
    });
});

// Cursor looks like: eyJjcmVhdGVkX2F0IjoiMjAyNS0wMS0xNVQxMDozMDowMC4wMDBaIiwiaWQiOjEwNDV9
// Opaque to clients

Infinite Scroll Implementation

// React component with cursor pagination
function InfinitePostList() {
    const [posts, setPosts] = useState([]);
    const [cursor, setCursor] = useState(null);
    const [hasMore, setHasMore] = useState(true);
    const [loading, setLoading] = useState(false);

    const loadMore = async () => {
        if (loading || !hasMore) return;

        setLoading(true);

        const url = cursor
            ? `/api/posts?cursor=${cursor}&limit=20`
            : '/api/posts?limit=20';

        const response = await fetch(url);
        const { data, pagination } = await response.json();

        setPosts(prev => [...prev, ...data]);
        setCursor(pagination.nextCursor);
        setHasMore(pagination.hasMore);
        setLoading(false);
    };

    useEffect(() => {
        loadMore();
    }, []);

    return (
        <div>
            {posts.map(post => (
                <PostCard key={post.id} post={post} />
            ))}

            {hasMore && (
                <button onClick={loadMore} disabled={loading}>
                    {loading ? 'Loading...' : 'Load More'}
                </button>
            )}
        </div>
    );
}

GraphQL Relay-Style Pagination

const { GraphQLObjectType, GraphQLString, GraphQLList, GraphQLBoolean } = require('graphql');

const PostType = new GraphQLObjectType({
    name: 'Post',
    fields: {
        id: { type: GraphQLString },
        title: { type: GraphQLString },
        content: { type: GraphQLString }
    }
});

const PageInfoType = new GraphQLObjectType({
    name: 'PageInfo',
    fields: {
        hasNextPage: { type: GraphQLBoolean },
        hasPreviousPage: { type: GraphQLBoolean },
        startCursor: { type: GraphQLString },
        endCursor: { type: GraphQLString }
    }
});

const PostConnectionType = new GraphQLObjectType({
    name: 'PostConnection',
    fields: {
        edges: {
            type: new GraphQLList(new GraphQLObjectType({
                name: 'PostEdge',
                fields: {
                    node: { type: PostType },
                    cursor: { type: GraphQLString }
                }
            }))
        },
        pageInfo: { type: PageInfoType }
    }
});

const QueryType = new GraphQLObjectType({
    name: 'Query',
    fields: {
        posts: {
            type: PostConnectionType,
            args: {
                first: { type: GraphQLInt },
                after: { type: GraphQLString }
            },
            resolve: async (_, { first = 20, after }) => {
                let query = 'SELECT * FROM posts';
                let params = [];

                if (after) {
                    const id = decodeCursor(after);
                    query += ' WHERE id < ?';
                    params.push(id);
                }

                query += ' ORDER BY id DESC LIMIT ?';
                params.push(first + 1);

                const posts = await db.query(query, params);

                const hasNextPage = posts.length > first;
                const nodes = hasNextPage ? posts.slice(0, first) : posts;

                return {
                    edges: nodes.map(node => ({
                        node,
                        cursor: encodeCursor(node.id)
                    })),
                    pageInfo: {
                        hasNextPage,
                        hasPreviousPage: !!after,
                        startCursor: nodes.length > 0 ? encodeCursor(nodes[0].id) : null,
                        endCursor: nodes.length > 0 ? encodeCursor(nodes[nodes.length - 1].id) : null
                    }
                };
            }
        }
    }
});

// GraphQL query
query {
    posts(first: 20, after: "cursor_value") {
        edges {
            node {
                id
                title
            }
            cursor
        }
        pageInfo {
            hasNextPage
            endCursor
        }
    }
}

Filtering with Cursor Pagination

app.get('/api/posts', async (req, res) => {
    const { cursor, limit = 20, category, author } = req.query;

    let query = 'SELECT * FROM posts WHERE 1=1';
    let params = [];

    // Add filters
    if (category) {
        query += ' AND category = ?';
        params.push(category);
    }

    if (author) {
        query += ' AND author_id = ?';
        params.push(author);
    }

    // Add cursor
    if (cursor) {
        query += ' AND id < ?';
        params.push(cursor);
    }

    query += ' ORDER BY id DESC LIMIT ?';
    params.push(parseInt(limit) + 1);

    const posts = await db.query(query, params);

    const hasMore = posts.length > limit;
    const data = hasMore ? posts.slice(0, limit) : posts;

    res.json({
        data,
        pagination: {
            nextCursor: hasMore ? data[data.length - 1].id : null,
            hasMore
        }
    });
});

// Usage with filters
GET /api/posts?category=tech&author=123&limit=20
GET /api/posts?category=tech&author=123&cursor=1045&limit=20

// Cursor works with filters

Search Results Pagination

Special case: relevance sorting:

app.get('/api/search', async (req, res) => {
    const { q, cursor, limit = 20 } = req.query;

    // Full-text search with score
    let query = `
        SELECT *, MATCH(title, content) AGAINST(? IN BOOLEAN MODE) as score
        FROM posts
        WHERE MATCH(title, content) AGAINST(? IN BOOLEAN MODE)
    `;
    let params = [q, q];

    if (cursor) {
        // Cursor format: score:id
        const [score, id] = cursor.split(':');
        query += ` AND (score < ? OR (score = ? AND id < ?))`;
        params.push(parseFloat(score), parseFloat(score), parseInt(id));
    }

    query += ' ORDER BY score DESC, id DESC LIMIT ?';
    params.push(limit + 1);

    const results = await db.query(query, params);

    const hasMore = results.length > limit;
    const data = hasMore ? results.slice(0, limit) : results;

    const nextCursor = hasMore
        ? `${data[data.length - 1].score}:${data[data.length - 1].id}`
        : null;

    res.json({
        data,
        pagination: {
            nextCursor,
            hasMore,
            query: q
        }
    });
});

When Offset Pagination Is Fine

Don't overthink it. Offset works for:

Small datasets (< 10,000 rows):

// This is fine
SELECT * FROM users ORDER BY name LIMIT 20 OFFSET 180;
// 200 total users, page 10 loads in 15ms

Admin panels:

// Low traffic, small datasets, need page numbers
app.get('/admin/users', async (req, res) => {
    const page = parseInt(req.query.page) || 1;
    const limit = 50;
    const offset = (page - 1) * limit;

    const [users, [{ total }]] = await Promise.all([
        db.query('SELECT * FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?', [limit, offset]),
        db.query('SELECT COUNT(*) as total FROM users')
    ]);

    res.render('admin/users', {
        users,
        page,
        totalPages: Math.ceil(total / limit)
    });
});

When you need page numbers in UI:

<div class="pagination">
    <a href="?page=1">1</a>
    <a href="?page=2">2</a>
    <a href="?page=3" class="active">3</a>
    <a href="?page=4">4</a>
    <a href="?page=5">5</a>
</div>

Cursor pagination can't show page numbers.

Database Indexes for Pagination

For cursor pagination:

-- Index on cursor column
CREATE INDEX idx_posts_id ON posts(id DESC);

-- For keyset with created_at
CREATE INDEX idx_posts_created_id ON posts(created_at DESC, id DESC);

-- With filters
CREATE INDEX idx_posts_category_id ON posts(category, id DESC);

Verify index usage:

EXPLAIN SELECT * FROM posts WHERE id < 1000 ORDER BY id DESC LIMIT 20;

-- Should show "Using index" not "Using filesort"

My Decision Framework

Use Offset when:

Dataset < 10,000 rows
Need page numbers in UI
Admin panels / internal tools
Users need to jump to specific pages
Simplicity is more important than performance

Use Cursor when:

Dataset > 100,000 rows
Public API
Infinite scroll UI
Mobile apps
Need consistent results during writes
Performance matters

Use Keyset when:

Sorting by non-unique columns
Need fast pagination on sorted data
Have compound indexes

Real-World Performance

My production API (2M posts):

Before (offset):

Page 1: 23ms
Page 100: 245ms
Page 1000: 8,234ms
Page 10000: timeout

After (cursor):

Page 1: 12ms
Page 100: 14ms
Page 1000: 15ms
Page 10000: 16ms

Constant time. Problem solved.

The Bottom Line

Pagination choice matters for performance:

Start with offset for simplicity. It works fine for small datasets.

Switch to cursor when you hit performance issues or have > 100k rows.

Index your cursor columns. Performance depends on indexes.

Use keyset pagination when sorting by non-unique columns.

Encode cursors to hide implementation details and allow changes.

Don't cargo-cult cursor pagination for small datasets. Offset is simpler. But don't ignore performance issues on large datasets - cursor pagination is the solution.

I wasted weeks optimizing offset queries before realizing the approach was fundamentally wrong for large datasets. Cursor pagination solved it in an afternoon.

Pick the right tool. Page 1000 shouldn't take 8 seconds.