Pagination

Offset vs Cursor Performance

-- Offset (slow at high pages)
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 100000;
-- Scans 100,000 rows!

-- Cursor (constant)
SELECT * FROM products WHERE id > 50000 ORDER BY id LIMIT 20;
-- Uses index

Spring Data

// Cursor-based
ScrollPosition position = ScrollPosition.keyset();
Slice<Product> slice = repository.findByOrderById(position, Limit.of(20));

Key Points

• Understanding Pagination Performance is essential for production systems
• Always consider scalability and maintainability
• Test thoroughly before deploying to production
• Monitor performance and set up alerting

Common Patterns

1. Validation: Always validate input at the boundary
2. Error Handling: Use structured error responses
3. Logging: Log key events for debugging
4. Testing: Unit, integration, and load tests
5. Documentation: Keep docs updated with code changes

Types

• Offset: Simple, but slow for large offsets
• Cursor: Consistent, better performance
• Keyset: Composite key ordering

Response Format

{
  "data": [...],
  "pagination": {
    "page": 1,
    "pageSize": 20,
    "total": 100,
    "hasMore": true
  }
}

Best Practices

• Default page size: 20-50
• Maximum page size: 100
• Use cursor for large datasets

Key Points

• Understanding Pagination Performance is essential for production systems
• Always consider scalability and maintainability
• Test thoroughly before deploying to production
• Monitor performance and set up alerting

Common Patterns

1. Validation: Always validate input at the boundary
2. Error Handling: Use structured error responses
3. Logging: Log key events for debugging
4. Testing: Unit, integration, and load tests
5. Documentation: Keep docs updated with code changes