38 Implementing Pagination with limit and offset

Pagination is one of the main pillars in building efficient web applications and backend APIs. With data continually growing in the database, it is virtually impossible to load the entire dataset at once in a single request. That is why pagination techniques are used to deliver data partially, usually in the form of pages that are easy for the client to digest.

One of the simplest and most commonly used pagination methods is the combination of limit and offset. This article will break down 38 implementations of pagination using limit and offset from the perspective of queries, APIs, as well as several variations and optimizations that can be applied in the real world.

What Are limit and offset?

• limit: Determines the maximum number of rows returned by the query.
• offset: Determines how many rows are skipped before the query starts fetching data.

For example, suppose we have a user table like the following:

SQL query:

1SELECT * FROM users LIMIT 2 OFFSET 2;

Result:

Why Is Pagination Important?

1. Improves performance: Limits the amount of data fetched.
2. UX/Responsiveness: Data is delivered incrementally, resulting in a better user experience.
3. Resource Friendly: Reduces server load and bandwidth usage.
4. Data navigation control: Makes it easier for users to browse and search through data.

Pagination Flow Diagram

Let’s look at the data flow with simple pagination:

flowchart LR
A[Client Request] --> B{Include limit & offset?}
B -- Yes --> C[Query DB with LIMIT/OFFSET]
C --> D[Return data + meta info]
B -- No --> E[Return error/default limit]

flowchart LR
A[Client Request] --> B{Include limit & offset?}
B -- Yes --> C[Query DB with LIMIT/OFFSET]
C --> D[Return data + meta info]
B -- No --> E[Return error/default limit]

Basic Implementation: Express.js API + PostgreSQL

 1// Example GET users endpoint with limit-offset pagination
 2app.get('/users', async (req, res) => {
 3    // Get parameters from the query string with defaults
 4    let limit = parseInt(req.query.limit, 10) || 10;
 5    let offset = parseInt(req.query.offset, 10) || 0;
 6    
 7    const result = await client.query('SELECT * FROM users LIMIT $1 OFFSET $2', [limit, offset]);
 8    res.json({
 9        page_size: limit,
10        offset: offset,
11        data: result.rows,
12    });
13});

That simple! However, pagination implementation does not stop here.

38 Implementations / Variations of Limit-Offset Pagination

Below are 38 practical techniques, variations, and—to broaden the scope—best practices across various scenarios using limit and offset:

1. Pagination on a Standard SQL Query

1SELECT * FROM orders LIMIT 25 OFFSET 50;

2. Validating the limit and offset Parameters

Always validate and constrain their values:

1limit = Math.max(1, Math.min(50, limit));
2offset = Math.max(0, offset);

3. Pagination in a RESTful API

A standard endpoint:

1GET /posts?limit=20&offset=60

4. Returning Metadata Information

Always send total_count, current_page, next_offset, and so on:

1{
2  "total": 480,
3  "page_size": 20,
4  "offset": 40,
5  "next_offset": 60,
6  "data": [...]
7}

5. Implementation in an ORM (Sequelize)

1User.findAll({ limit: 10, offset: 3 })

6. Counting the Total Number of Rows

1SELECT COUNT(*) FROM orders;

7. Pagination in GraphQL

It is usually implemented with first and skip, but the core idea is the same.

1query {
2  users(first: 10, skip: 30) { id, username }
3}

8. Pagination in MongoDB

1db.users.find().skip(20).limit(10)

9. Implementation with DataTables (datatable.js)

It often automatically sends the start (offset) and length (limit) parameters.

10. Pagination on a Flutter/Dart Client

Send an HTTP GET with limit and offset in the query params.

11. Combining with Sorting

Make sure to include an ORDER BY so that the results are consistent.

1SELECT * FROM products ORDER BY id ASC LIMIT 10 OFFSET 30;

12. Custom Default Value

Set a maximum limit so the server is not overloaded.

13. Next/Prev Buttons in the UI

Offset = (page - 1) * limit
Next offset = offset + limit
Previous offset = offset - limit

14. Caching and Pagination Keys in the Backend

Cache using a key that includes the limit-offset pair, for example: users:10:30.

15. PRG (Post-Redirect-Get)

After submitting a form, redirect with the limit and offset parameters in the query string.

16. Limit-Offset in Prisma (Node.js ORM)

1prisma.user.findMany({ skip: 10, take: 20 });

17. Implementation in Laravel (PHP)

1User::skip($offset)->take($limit)->get();

18. Pagination in Python’s Django ORM

1User.objects.all()[offset:offset+limit]

19. Pagination on GQL Firebase

For small queries, use limit/offset; for large ones, use keyset pagination.

20. Combining Filtering + Pagination

Combine WHERE with LIMIT/OFFSET in the query.

21. Displaying Last Page Information

Total pages = ceil(total / limit)

22. Displaying the Current Number of Rows

Display: “Showing 21–40 of 480”

23. Limiting Data Per Page in the Backend

Example: a maximum limit of 100 per page.

24. Reverse Pagination

Sort DESC and compute the offset from the end.

25. Printing All Data via Batch Pagination

Fetch data page by page and combine the results.

26. SSR Pagination in Next.js

Fetch data in server-side props using limit-offset.

27. Pagination in Excel Export

Export data incrementally using batching.

28. Infinite Scroll

When approaching the end of the data, fetch the next batch (offset ++).

29. Nested Pagination (Nested Data)

For example: comments per post with limit-offset per post.

30. Pagination with Multiple Joins

Combine multiple tables, but keep the main pagination on the primary table.

31. Optimizing Large Offsets

A large offset means slow queries. Use keyset or cursor pagination as an alternative.

32. SQL Subquery for Efficiency

1SELECT * FROM (SELECT ... LIMIT 100000 OFFSET 100000) sub;

33. Pagination on Aggregation Queries

Constrain the aggregate columns with limit-offset.

34. Support for Sorting by Multiple Fields

ORDER BY multiple fields + limit-offset.

35. Returning a has_next Boolean in the Metadata

1has_next = (offset + limit) < total

36. Navigating to a Specific Page

offset = (page_number - 1) * limit

37. Pagination in CLI Tools

For example, the psql and mysql CLIs support fetching with limit and offset.

38. Rate Limiting per Page

Combine pagination and rate limiting: 1 request/page per second.

Challenges of Limit-Offset Pagination

• Performance Penalty on Large Offsets:
  The larger the offset, the worse the performance. The database still has to count and skip over all the rows before the offset, then fetch the limit.

• Inconsistent Data Availability:
  If there are inserts/deletes between two fetches, data can be “skipped” or duplicated.

• Alternatives:
  Cursor/Seek Pagination, UUID-based, or Keyset Pagination.

Simulation and Case Study: Product API

Product List Simulation (limit: 10, offset: 30):

 1GET /api/products?limit=10&offset=30
 2
 3Response:
 4{
 5  "total": 145,
 6  "limit": 10,
 7  "offset": 30,
 8  "has_next": true,
 9  "data": [
10    // list of products id 31-40
11  ]
12}

Summary Table: Pros & Cons of Limit-Offset Pagination

Conclusion

Pagination with limit and offset is a powerful and flexible technique that remains a mainstay across many technology stacks. However, always understand its pros and cons, and choose the variation that fits your application’s needs from the list of 38 implementations above. For enterprise-scale needs or very large datasets, consider migrating to cursor-based pagination for better performance and consistency.

Do you have other tips on limit-offset pagination? Share them in the comments! 🚀