Optimize Database Queries for React Pagination: LIMIT vs Cursor-Based

When building React apps that display large datasets, pagination is essential for performance and user experience.

But the way you handle pagination in your database queries can make or break your app's speed, especially as your data grows. Let's explore the two main approaches and when to use each.

The LIMIT/OFFSET Approach

This is the most straightforward pagination method. You use LIMIT to specify how many records to fetch and OFFSET to skip records from previous pages.

Your SQL query might look something like the following:

-- Page 1: First 20 users
SELECT * FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 0;

-- Page 2: Next 20 users  
SELECT * FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 20;

-- Page 50: Skip 980 records, get next 20
SELECT * FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 980;

And in React, if you were pulling this data to render, this typically looks like:

const [currentPage, setCurrentPage] = useState(1);
const pageSize = 20;

const fetchUsers = async (page) => {
  const offset = (page - 1) * pageSize;
  const response = await api.get(`/users?limit=${pageSize}&offset=${offset}`);
  return response.data;
};

Pros:

• Simple to implement and understand
• Easy to jump to any page number
• Works well with traditional page-based UI components

Cons:

• Performance degrades significantly with large offsets (deep pagination)
• Inconsistent results when data is frequently updated (items can appear twice or be skipped)
• Database has to scan through all skipped records

If you're looking for simplicity, in exchange for a slight performance bottleneck, this is my preferred approach.

The Cursor-Based Approach

Instead of using page numbers, cursor-based pagination uses a "cursor" (usually a unique field like ID or timestamp) to mark where the next page should start.

-- First request: Get first 20 users
SELECT * FROM users ORDER BY created_at DESC LIMIT 20;

-- Next request: Get 20 users created before the last cursor
SELECT * FROM users 
WHERE created_at < '2024-01-15 10:30:00' 
ORDER BY created_at DESC 
LIMIT 20;

In React:

const [cursor, setCursor] = useState(null);
const [users, setUsers] = useState([]);

const fetchMoreUsers = async () => {
  const url = cursor 
    ? `/users?limit=20&cursor=${cursor}`
    : `/users?limit=20`;
  
  const response = await api.get(url);
  setUsers(prev => [...prev, ...response.data.users]);
  setCursor(response.data.nextCursor);
};

Pros:

• Consistent performance regardless of dataset size
• Stable results even when data is being added/removed
• Perfect for infinite scroll patterns
• Database can use indexes efficiently

Cons:

• Can't jump to arbitrary page numbers
• Slightly more complex to implement
• Requires a reliable, sortable cursor field

When to Use Each Approach

Use LIMIT/OFFSET when:

• You have relatively small datasets (under 100k records)
• Users need to jump to specific page numbers
• You're building traditional pagination with page numbers
• Data doesn't change frequently

Use Cursor-Based when:

• You're dealing with large datasets (100k+ records)
• Performance is critical
• You're implementing infinite scroll or "load more" patterns
• Data is frequently updated
• You want consistent user experience

Performance Impact

The difference becomes dramatic with scale. For a table with 1 million records:

• LIMIT 20 OFFSET 100000 might take 500ms+
• Cursor-based query typically stays under 10ms

Implementation Tips

For cursor-based pagination, ensure your cursor field is:

• Indexed for fast lookups
• Unique and sortable (timestamp + ID works well)
• Stable (doesn't change after creation)

Consider using compound cursors for complex sorting:

-- For sorting by popularity then date
WHERE (popularity, created_at, id) < (cursor_popularity, cursor_date, cursor_id)

The Bottom Line

Start with LIMIT/OFFSET for simplicity if your dataset is small and growth is predictable. As your app scales, migrate to cursor-based pagination for better performance and user experience.

Many successful apps use cursor-based pagination behind the scenes while still showing page numbers in the UI by estimating page positions.