Savvi Studio

Cursor Implementation Patterns

Purpose: Detailed cursor implementation strategies
Last Updated: 2024-11-26

Overview

This guide covers the three main cursor implementation patterns: offset-based, ID-based (keyset), and value-based cursors.

For complete code examples, see Cursor Pagination Examples.

Offset-Based Cursors

When to Use

  • Small datasets (< 1000 rows)
  • Simple pagination requirements
  • Order doesn't matter
  • Prototyping/development

Implementation

See: Example 5 - Offset-Based Cursor

SQL Pattern

SELECT * FROM nodes
ORDER BY id
LIMIT $1 OFFSET $2

Pros and Cons

Pros:

  • Simple to implement
  • Easy to understand
  • Works with any query
  • No special indexes needed

Cons:

  • Performance degrades with large offsets (O(n))
  • Inconsistent with concurrent modifications
  • Not suitable for very large datasets
  • Skips/duplicates possible with data changes

ID-Based Cursors (Keyset)

When to Use

  • Large datasets (thousands+ rows)
  • Stable ordering by ID
  • Performance-critical applications
  • Production systems

Implementation

See: Example 1 - Basic Node Pagination

SQL Pattern

SELECT * FROM nodes
WHERE id > $1
ORDER BY id
LIMIT $2

Index Requirements

-- Simple index on ID column
CREATE INDEX idx_nodes_id ON nodes(id);

Pros and Cons

Pros:

  • Constant O(log n) performance with index
  • Consistent with concurrent modifications
  • Scalable to billions of rows
  • Predictable resource usage

Cons:

  • Requires indexed column
  • Order must include unique column
  • More complex than offset
  • Can't jump to arbitrary page

Value-Based Cursors

When to Use

  • Sorting by non-unique column (created_at, name, etc.)
  • Custom sort orders
  • Complex sorting requirements

Implementation

See: Example 6 - Value-Based Cursor

SQL Pattern

SELECT * FROM nodes
WHERE (created_at, id) > ($1, $2)
ORDER BY created_at, id
LIMIT $3

Index Requirements

-- Composite index required
CREATE INDEX idx_nodes_created_at_id 
  ON nodes(created_at, id);

Pros and Cons

Pros:

  • Works with any sortable column
  • Efficient with composite index (O(log n))
  • Handles duplicates correctly
  • Flexible sorting

Cons:

  • Requires composite index
  • More complex cursor logic
  • Tuple comparison support needed
  • Index maintenance overhead

Error Handling

Invalid Cursor

See: Example 7 - Cursor Error Handling

Handle corrupted or invalid cursors gracefully by starting from the beginning.

Expired Cursor

Set expiration timestamps on cursors to prevent stale data issues:

function decodeCursor(cursor: string): CursorData | null {
  const decoded = /* decode */;
  const age = Date.now() - decoded.timestamp;
  if (age > 3600000) { // 1 hour
    throw new Error('Cursor expired');
  }
  return decoded;
}

Fallback Strategy

Always provide fallback to first page if cursor is invalid.

Testing

Unit Tests

See: Example 8 - Test Cursor Pagination

Test cursor encoding/decoding and pagination logic.

Integration Tests

Test pagination across multiple pages with real data.

Best Practices

✅ Do

  • Use ID-based cursors for large datasets
  • Include version in cursor for compatibility
  • Validate cursor before using
  • Set maximum page size
  • Document cursor format
  • Add cursor expiration
  • Test pagination boundaries

❌ Don't

  • Expose cursor structure to clients
  • Store sensitive data in cursors
  • Use offset for large datasets
  • Modify cursor format without versioning
  • Skip cursor validation
  • Allow unbounded page sizes
  • Assume cursor is always valid

Next steps: See Performance Guide for optimization strategies.