# API Pagination Cursor vs Offset

# API Pagination: Cursor vs Offset - A Developer's Guide to Scalable Data Retrieval

## Metadata

```json
{
  "seo_title": "API Pagination: Cursor vs Offset Comparison for Developers",
  "meta_description": "Deep dive into cursor-based and offset pagination for APIs. Learn implementation patterns, performance trade-offs, and best practices with TypeScript examples.",
  "keywords": [
    "API pagination",
    "cursor-based pagination",
    "offset pagination",
    "REST API best practices",
    "TypeScript pagination",
    "database pagination",
    "API performance optimization",
    "keyset pagination"
  ],
  "tags": [
    "API Design",
    "TypeScript",
    "Backend Development",
    "Database Optimization",
    "REST API",
    "Performance",
    "Scalability"
  ]
}
```

## The Pagination Problem in 2026

As APIs continue to power increasingly data-intensive applications, pagination remains one of the most critical—and frequently misunderstood—aspects of API design. Whether you're building a social media feed, an e-commerce product catalog, or a SaaS dashboard, you'll inevitably face the challenge of efficiently serving large datasets to clients.

The stakes are higher than ever. Modern applications demand real-time data consistency, sub-second response times, and the ability to handle millions of concurrent users. A poorly implemented pagination strategy can lead to inconsistent results, degraded performance at scale, and frustrated users experiencing duplicate or missing records.

Consider a typical scenario: You're building an API that serves a feed of user-generated content. Users are constantly creating new posts, and your API needs to paginate through potentially millions of records. With traditional offset-based pagination, a user scrolling through page 50 might see duplicates if new content was added at the beginning of the dataset. Worse, they might miss content entirely. These aren't edge cases—they're everyday realities in production systems.

The fundamental challenge lies in maintaining a consistent view of data while it's actively changing. Offset pagination, the traditional approach taught in most tutorials, calculates position using `LIMIT` and `OFFSET` SQL clauses. While intuitive, this method suffers from several critical flaws that become apparent at scale:

**Performance degradation**: As offset values increase, databases must scan and skip more rows, leading to exponentially slower queries. Fetching page 1,000 means the database processes and discards 999 pages worth of data.

**Consistency issues**: When records are inserted or deleted between page requests, users experience "page drift"—seeing duplicate items or missing content entirely.

**Resource inefficiency**: Large offsets consume significant database resources, impacting overall system performance and increasing infrastructure costs.

Cursor-based pagination emerged as the solution to these problems, offering O(1) lookup complexity regardless of position in the dataset. Instead of counting rows, cursor pagination uses a unique identifier (the cursor) to mark position, allowing the database to seek directly to the next set of results.

However, cursor pagination isn't a silver bullet. It trades the simplicity and flexibility of offset pagination for performance and consistency. Understanding when and how to implement each approach is crucial for building robust, scalable APIs.

## Modern TypeScript Implementation

Let's implement both approaches with production-ready TypeScript code, highlighting the architectural differences and trade-offs.

### Offset-Based Pagination

```typescript
interface OffsetPaginationParams {
  page: number;
  limit: number;
}

interface OffsetPaginationResponse<T> {
  data: T[];
  pagination: {
    page: number;
    limit: number;
    total: number;
    totalPages: number;
    hasNext: boolean;
    hasPrevious: boolean;
  };
}

class OffsetPaginator<T> {
  async paginate(
    query: any, // Your ORM query object
    params: OffsetPaginationParams
  ): Promise<OffsetPaginationResponse<T>> {
    const { page = 1, limit = 20 } = params;
    
    // Validate inputs
    if (page < 1 || limit < 1 || limit > 100) {
      throw new Error('Invalid pagination parameters');
    }

    const offset = (page - 1) * limit;
    
    // Execute count and data queries in parallel
    const [total, data] = await Promise.all([
      query.count(),
      query.offset(offset).limit(limit).execute()
    ]);

    const totalPages = Math.ceil(total / limit);

    return {
      data,
      pagination: {
        page,
        limit,
        total,
        totalPages,
        hasNext: page < totalPages,
        hasPrevious: page > 1
      }
    };
  }
}
```

### Cursor-Based Pagination

```typescript
interface CursorPaginationParams {
  cursor?: string;
  limit: number;
  direction?: 'forward' | 'backward';
}

interface CursorPaginationResponse<T> {
  data: T[];
  pageInfo: {
    hasNext: boolean;
    hasPrevious: boolean;
    startCursor: string | null;
    endCursor: string | null;
  };
}

class CursorPaginator<T extends { id: string; createdAt: Date }> {
  private encodeCursor(id: string, createdAt: Date): string {
    const cursor = JSON.stringify({ id, createdAt: createdAt.toISOString() });
    return Buffer.from(cursor).toString('base64url');
  }

  private decodeCursor(cursor: string): { id: string; createdAt: Date } {
    const decoded = Buffer.from(cursor, 'base64url').toString('utf-8');
    const { id, createdAt } = JSON.parse(decoded);
    return { id, createdAt: new Date(createdAt) };
  }

  async paginate(
    queryBuilder: any,
    params: CursorPaginationParams
  ): Promise<CursorPaginationResponse<T>> {
    const { cursor, limit = 20, direction = 'forward' } = params;

    if (limit < 1 || limit > 100) {
      throw new Error('Invalid limit');
    }

    let query = queryBuilder;

    if (cursor) {
      const { id, createdAt } = this.decodeCursor(cursor);
      
      if (direction === 'forward') {
        // Fetch records after cursor
        query = query.where(
          'createdAt', '<', createdAt
        ).orWhere((qb: any) => 
          qb.where('createdAt', '=', createdAt).where('id', '<', id)
        );
      } else {
        // Fetch records before cursor
        query = query.where(
          'createdAt', '>', createdAt
        ).orWhere((qb: any) => 
          qb.where('createdAt', '=', createdAt).where('id', '>', id)
        );
      }
    }

    // Fetch one extra to determine if there are more results
    const data = await query
      .orderBy('createdAt', 'DESC')
      .orderBy('id', 'DESC')
      .limit(limit + 1)
      .execute();

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

    return {
      data: results,
      pageInfo: {
        hasNext,
        hasPrevious: !!cursor,
        startCursor: results.length > 0 
          ? this.encodeCursor(results[0].id, results[0].createdAt)
          : null,
        endCursor: results.length > 0
          ? this.encodeCursor(
              results[results.length - 1].id,
              results[results.length - 1].createdAt
            )
          : null
      }
    };
  }
}
```

### Express API Implementation

```typescript
import express from 'express';

const app = express();

// Offset pagination endpoint
app.get('/api/posts/offset', async (req, res) => {
  try {
    const page = parseInt(req.query.page as string) || 1;
    const limit = parseInt(req.query.limit as string) || 20;

    const paginator = new OffsetPaginator();
    const result = await paginator.paginate(
      db.posts.query(),
      { page, limit }
    );

    res.json(result);
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

// Cursor pagination endpoint
app.get('/api/posts/cursor', async (req, res) => {
  try {
    const cursor = req.query.cursor as string | undefined;
    const limit = parseInt(req.query.limit as string) || 20;

    const paginator = new CursorPaginator();
    const result = await paginator.paginate(
      db.posts.query(),
      { cursor, limit }
    );

    res.json(result);
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});
```

## Common Pitfalls and How to Avoid Them

### 1. Missing Database Indexes

**Problem**: Cursor pagination relies on efficient lookups by cursor fields. Without proper indexes, performance gains disappear.

**Solution**: Create composite indexes on cursor fields:

```sql
CREATE INDEX idx_posts_cursor ON posts(created_at DESC, id DESC);
```

### 2. Non-Unique Cursor Fields

**Problem**: Using only timestamps as cursors can cause inconsistent results when multiple records share the same timestamp.

**Solution**: Always combine timestamps with a unique identifier (like ID) to ensure deterministic ordering.

### 3. Exposing Internal IDs in Cursors

**Problem**: Plain cursors reveal database structure and can be manipulated.

**Solution**: Always encode cursors using base64 or encryption, and validate decoded values.

### 4. Ignoring Timezone Issues

**Problem**: Timestamp-based cursors can fail across timezones or during DST transitions.

**Solution**: Store and compare all timestamps in UTC.

### 5. Not Handling Deleted Records

**Problem**: Cursors pointing to deleted records cause errors.

**Solution**: Implement graceful fallback logic when cursor records don't exist.

## Best Practices

1. **Choose based on use case**: Use offset for admin panels with page numbers; use cursor for infinite scroll and feeds.

2. **Implement rate limiting**: Protect pagination endpoints from abuse, especially with cursor pagination where position validation is harder.

3. **Cache count queries**: For offset pagination, cache total counts with short TTLs to reduce database load.

4. **Set reasonable limits**: Cap maximum page size (typically 100) to prevent resource exhaustion.

5. **Document cursor format**: Make it clear that cursors are opaque tokens that shouldn't be constructed manually.

6. **Provide both when possible**: Offer both pagination styles for different client needs.

7. **Monitor performance**: Track P95/P99 latencies for different page positions to identify degradation.

## Frequently Asked Questions

**Q: Can I implement random access with cursor pagination?**

A: No, cursor pagination is inherently sequential. If you need random page access (like "jump to page 50"), offset pagination is required. Consider hybrid approaches for specific use cases.

**Q: How do I handle sorting by multiple fields with cursors?**

A: Include all sort fields in your cursor. For example, sorting by `score` then `createdAt` requires encoding both values and using them in your WHERE clause conditions.

**Q: What's the performance difference at scale?**

A: Offset pagination degrades linearly with page number. Fetching page 10,000 with 20 items per page means skipping 199,980 rows. Cursor pagination maintains constant time regardless of position.

**Q: Can I convert between cursor and offset pagination?**

A: Not reliably. Cursors represent specific records, not positions. Converting would require additional queries and defeat the purpose of cursor pagination.

**Q: How do I implement backward pagination with cursors?**

A: Reverse your sort order and comparison operators. If forward uses `WHERE created_at < cursor`, backward uses `WHERE created_at > cursor`, then reverse the results array.

**Q: Should I expose total count with cursor pagination?**

A: Generally no. Calculating total count requires a full table scan, negating cursor pagination's performance benefits. If needed, provide approximate counts or cache aggressively.

**Q: How do I handle cursor expiration?**

A: Implement cursor validation with timestamps. Reject cursors older than a reasonable threshold (e.g., 24 hours) to prevent stale data issues and encourage fresh queries.

---

Choosing between cursor and offset pagination isn't about finding the "best" approach—it's about matching the technique to your specific requirements. Offset pagination offers simplicity and flexibility for traditional page-based interfaces, while cursor pagination provides consistency and performance for real-time feeds and infinite scroll. By understanding the trade-offs and implementing both correctly, you'll build APIs that scale gracefully and provide excellent user experiences.

**Word Count: 1,789 words**