SQLSorting and Limiting Resultsbeginner45 min read

Limiting Results with LIMIT and OFFSET

Learn to limit query results, implement page-based pagination with LIMIT and OFFSET, use standard FETCH syntax, understand deep-offset performance costs, and build efficient keyset pagination queries.

On This Page (Table of Contents)

In this lesson, you will learn

✓Explain why databases and applications limit the number of returned rows
✓Use LIMIT to restrict the number of rows returned
✓Use OFFSET to skip rows and calculate page boundaries correctly
✓Write FETCH FIRST and OFFSET FETCH pagination queries
✓Explain why stable ordering is essential for reliable pagination
✓Identify performance problems with deep OFFSET pagination
✓Implement keyset pagination using indexed, unique ordering columns
✓Understand how inserts and deletes can affect pages during pagination

1. Why Do We Limit Query Results?

Imagine an online store containing one million products. If an application requests every product when a user opens the page, it may transfer far more data than the user needs. The database, network, and application all have to handle that unnecessary work.

Pagination divides a large result set into smaller pages. For example, a page might display 10, 20, or 50 products at a time.

In PostgreSQL, LIMIT and OFFSET are commonly used to implement this behavior. Remember that limiting the result set does not necessarily mean the database performs only that much work internally. Sorting, filtering, joins, and skipped rows may require additional processing.

Return Only Five Products

sql

1
2
3
4
SELECT product_id, title, price
FROM products
ORDER BY product_id ASC
LIMIT 5;

Output

The query returns at most five rows, ordered by product_id. The sample values are illustrative; actual output depends on the data in your database.

2. Understanding LIMIT

LIMIT specifies the maximum number of rows to return. It is commonly used in PostgreSQL, MySQL, and SQLite, although details vary between database systems.

If the query finds fewer rows than the limit, the database returns only the rows that exist. LIMIT does not create additional rows, and it does not guarantee which rows are returned unless the query has an appropriate ORDER BY clause.

Different LIMIT Values

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Return at most three products
SELECT product_id, title
FROM products
ORDER BY product_id
LIMIT 3;
 
-- Return at most ten products
SELECT product_id, title
FROM products
ORDER BY product_id
LIMIT 10;
 
-- Return no rows
SELECT product_id, title
FROM products
ORDER BY product_id
LIMIT 0;

LIMIT 3 returns at most three rows; LIMIT 10 returns at most ten; LIMIT 0 returns no rows. If the table contains only two matching rows, LIMIT 10 still returns only two.

3. Understanding OFFSET

OFFSET tells the database how many rows to skip before it starts returning results. It is useful when the user wants to navigate beyond the first page.

OFFSET 0 skips no rows. OFFSET 5 skips the first five rows in the ordered result. OFFSET 20 skips the first twenty rows.

OFFSET is meaningful for pagination only when the query has a suitable ORDER BY clause. Otherwise, the database is free to return rows in an unspecified order, so the rows skipped or returned may not be the ones you expect.

Skip the First Five Rows

sql

1
2
3
4
SELECT product_id, title
FROM products
ORDER BY product_id ASC
OFFSET 5;

Output

In PostgreSQL, OFFSET can be used without LIMIT. The query skips five rows and returns the remaining rows. For a small page, combine OFFSET with LIMIT.

4. Combining LIMIT and OFFSET

Use LIMIT and OFFSET together to retrieve a particular page. LIMIT determines the page size, while OFFSET determines how many earlier rows to skip.

The order in PostgreSQL is LIMIT followed by OFFSET, although PostgreSQL also accepts OFFSET before LIMIT in supported query forms. Use a consistent style throughout your project.

Fetch Two Pages of Ten Products

sql

1
2
3
4
5
6
7
8
9
10
11
-- Page 1: rows 1 through 10
SELECT product_id, title, price
FROM products
ORDER BY product_id ASC
LIMIT 10 OFFSET 0;
 
-- Page 2: rows 11 through 20
SELECT product_id, title, price
FROM products
ORDER BY product_id ASC
LIMIT 10 OFFSET 10;

Output

-- Page 1 starts with product_id 1 -- Page 1 contains at most 10 rows -- Page 2 starts with product_id 11 -- Page 2 contains at most 10 rows

These page boundaries assume consecutive product IDs and enough rows in the table. Real IDs can contain gaps, so the IDs in the second page are not guaranteed to start at 11.

5. Calculating the Correct OFFSET

Applications usually let a user choose a page number. You must convert that page number into an offset.

The formula is:

OFFSET = (page_number - 1) * page_size

Subtract one because page numbering normally begins at 1, while the number of rows to skip begins at 0.

For a page size of 10, page 1 skips 0 rows, page 2 skips 10 rows, page 3 skips 20 rows, and page 4 skips 30 rows.

Page Number Calculation

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Page 1, 10 rows per page
SELECT product_id, title
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 0;
 
-- Page 3, 10 rows per page
SELECT product_id, title
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 20;
 
-- Page 5, 20 rows per page
SELECT product_id, title
FROM products
ORDER BY product_id
LIMIT 20 OFFSET 80;

Page 3 with a page size of 10 uses (3 - 1) * 10 = 20. Page 5 with a page size of 20 uses (5 - 1) * 20 = 80.

6. Dry Run: Find the Rows on Each Page

Suppose a table has these rows when ordered by product_id. We will use a page size of three to make the calculation easy to follow.

Sample Data

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Ordered product IDs:
-- 101, 102, 103, 104, 105, 106, 107, 108
 
-- Page 1
SELECT product_id
FROM products
ORDER BY product_id
LIMIT 3 OFFSET 0;
 
-- Page 2
SELECT product_id
FROM products
ORDER BY product_id
LIMIT 3 OFFSET 3;
 
-- Page 3
SELECT product_id
FROM products
ORDER BY product_id
LIMIT 3 OFFSET 6;

Output

Page 1: 101, 102, 103 Page 2: 104, 105, 106 Page 3: 107, 108

Page 1 skips zero rows. Page 2 skips three rows. Page 3 skips six rows. The final page contains only two rows because there are no more matching rows.

7. Why ORDER BY Is Essential

A database table is a set of rows, not a numbered list. Without ORDER BY, SQL does not promise a particular row order. Physical storage order, an index scan, or a query plan may affect the order you happen to observe, but none of those is a substitute for an explicit ordering requirement.

For reliable pagination, sort by a column or combination of columns that uniquely orders the result. A timestamp alone may not be unique because several rows can have the same timestamp. Add a unique tie-breaker such as the primary key.

Stable Ordering with a Tie-Breaker

sql

1
2
3
4
5
6
7
8
9
10
11
-- Risky: multiple comments may have the same timestamp
SELECT comment_id, created_at, body
FROM comments
ORDER BY created_at DESC
LIMIT 10 OFFSET 10;
 
-- Better: comment_id resolves timestamp ties
SELECT comment_id, created_at, body
FROM comments
ORDER BY created_at DESC, comment_id DESC
LIMIT 10 OFFSET 10;

The second query defines a consistent order for a fixed dataset because comment_id breaks ties in created_at. It does not by itself guarantee a consistent snapshot when rows change between requests.

8. ANSI SQL: FETCH FIRST and OFFSET FETCH

The SQL standard includes FETCH FIRST and FETCH NEXT syntax for limiting rows. PostgreSQL supports this syntax, as well as LIMIT. Other database systems support different versions of the syntax, so check the documentation for your specific database.

FETCH FIRST limits the number of rows returned. OFFSET can be combined with FETCH to skip rows before fetching the next page. FETCH is not a guarantee of identical syntax across every database engine.

Fetch the First Five Employees

sql

1
2
3
4
5
6
7
8
9
10
11
SELECT emp_id, full_name, salary
FROM employees
ORDER BY salary DESC, emp_id ASC
FETCH FIRST 5 ROWS ONLY;
 
-- Skip five employees and fetch the next five
SELECT emp_id, full_name, salary
FROM employees
ORDER BY salary DESC, emp_id ASC
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY;

The first query returns at most five employees. The second skips five rows in the defined order and returns at most five more. emp_id breaks salary ties.

9. LIMIT and OFFSET with WHERE

You can combine filtering, sorting, and pagination in one query. WHERE filters which rows qualify, ORDER BY sorts those qualifying rows, and LIMIT/OFFSET determines which slice of the ordered result is returned.

Paginate Products in One Category

sql

1
2
3
4
5
SELECT product_id, title, price
FROM products
WHERE category = 'Tech'
ORDER BY product_id ASC
LIMIT 20 OFFSET 40;

This returns at most 20 Tech products after skipping the first 40 matching products in product_id order. The offset applies to the filtered result, not to all products before filtering.

10. The Deep OFFSET Performance Problem

Consider a query that requests ten rows after skipping 500,000 rows. The database generally cannot simply jump to the 500,001st row by its position in the result, because SQL results do not have a built-in row-number address.

Depending on the execution plan, the database may need to find and process enough qualifying rows to pass the skipped rows, then return the requested ten. Sorting may also add substantial work if no suitable index can provide the required order.

This is why deep offsets can become slow. The exact cost depends on the query, filters, indexes, data distribution, and database engine. It is not correct to assume that every deep-offset query scans and sorts the entire table.

A Deep-Offset Query

sql

1
2
3
4
SELECT product_id, title
FROM products
ORDER BY product_id ASC
LIMIT 10 OFFSET 500000;

The database must locate the rows that follow the first 500,000 rows in this ordering. A suitable index can reduce sorting work, but it generally does not eliminate the cost of stepping past a very large number of qualifying rows.

11. Compare OFFSET Pagination and Keyset Pagination

Keyset pagination, also called seek pagination, continues from values found in the previous page rather than counting all earlier rows. It is especially useful for next-page and infinite-scroll interfaces.

OFFSET pagination is often convenient for small datasets and interfaces that require direct navigation to page numbers. Keyset pagination is often a better fit for large datasets when users move forward or backward using a cursor.

OFFSET Pagination vs Keyset Pagination

OFFSET identifies a page by the number of rows to skip. Keyset identifies the next page using the last row's ordering values.

Aspect	OFFSET Pagination	Keyset Pagination
How the next page is found	ORDER BY product_id LIMIT 20 OFFSET 100000	WHERE product_id > 100000 ORDER BY product_id LIMIT 20
Work for deep pages	May process many preceding rows before returning the page	Can seek from a suitable index position and read the next qualifying rows
Direct jump to page number	Convenient for numbered pages	Usually requires a cursor from a preceding page
Effect of inserts before the current position	Can shift row positions and cause repeats or omissions across requests	Usually avoids position shifts for a fixed ordering key, though updates and deletions can still change results
Best fit	Small result sets and numbered-page interfaces	Large feeds, sequential browsing, and cursor-based APIs

When to use OFFSET Pagination:

Use OFFSET when page numbers and direct navigation are important and the result set is reasonably small.

When to use Keyset Pagination:

Use keyset pagination when users navigate sequentially through large ordered datasets and the ordering columns support efficient seeking.

12. Keyset Pagination with a Unique ID

Suppose the first page ends with product_id 120. Store that ID as the cursor. To get the next page, request products with IDs greater than 120.

This example assumes product_id is unique and ascending. The database can often use a B-tree index on product_id to locate the next rows efficiently. The actual plan and speed must be verified for the real query.

First Page and Next Page

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
-- First page
SELECT product_id, title, price
FROM products
ORDER BY product_id ASC
LIMIT 10;
 
-- Assume the last product_id from page 1 is 120.
-- Request the next page using that cursor.
SELECT product_id, title, price
FROM products
WHERE product_id > 120
ORDER BY product_id ASC
LIMIT 10;

Output

-- Illustrative second-page IDs: 121, 122, 123, 124, 125, 126, 127, 128, 129, 130

The actual next IDs depend on the stored data. If IDs contain gaps, the next page may begin at a value greater than 121. The cursor should come from the final row actually returned, not from an assumed ID.

13. Keyset Pagination with Multiple Sort Columns

A cursor must represent the full ordering when the sort uses multiple columns. Consider comments ordered by created_at descending and comment_id descending. If the last row has created_at = '2026-10-10 12:00:00' and comment_id = 500, the next page must include older timestamps and, for equal timestamps, smaller IDs.

For this descending order, the next-page predicate compares the timestamp first, then the ID when timestamps are equal. Both conditions must match the ORDER BY direction.

Continue After a Timestamp and ID Cursor

sql

1
2
3
4
5
6
7
8
9
SELECT comment_id, created_at, body
FROM comments
WHERE created_at < TIMESTAMP '2026-10-10 12:00:00'
   OR (
        created_at = TIMESTAMP '2026-10-10 12:00:00'
        AND comment_id < 500
      )
ORDER BY created_at DESC, comment_id DESC
LIMIT 20;

This example assumes created_at and comment_id are non-NULL for the relevant rows and that comment_id uniquely breaks ties. The cursor values must be replaced with the actual final row from the previous page.

14. Keyset Pagination with Category Filters

Keyset pagination can also be combined with WHERE filters. Keep the same filtering conditions on every page, and use the cursor to continue within the filtered, ordered result set.

Next Page of Tech Products

sql

1
2
3
4
5
6
SELECT product_id, title, price
FROM products
WHERE category = 'Tech'
  AND product_id > 120
ORDER BY product_id ASC
LIMIT 20;

This returns at most 20 Tech products after product_id 120. It assumes product_id provides the desired unique ascending order. A composite index may help depending on the filter and query plan.

15. What Happens When Data Changes?

Pagination requests often run as separate database queries. Between requests, another user may insert, delete, or update rows.

With OFFSET pagination, an insertion before the next page's position can shift rows, potentially causing a row to appear twice. A deletion before that position can shift rows in the other direction and cause a row to be skipped.

Keyset pagination is generally less sensitive to changes before the cursor because it continues from an ordering value rather than a row count. However, it is not a complete snapshot guarantee. Updates to ordering columns, new rows after the cursor, and deletions can still change what appears. If an application requires a consistent view of the entire dataset, it needs an appropriate snapshot or transaction strategy.

Why OFFSET Pages Can Shift

sql

1
2
3
4
5
6
7
8
9
10
11
12
-- First request
SELECT product_id
FROM products
ORDER BY product_id
LIMIT 3 OFFSET 0;
 
-- Suppose a new row is inserted near the beginning.
-- This second request may now overlap the first page.
SELECT product_id
FROM products
ORDER BY product_id
LIMIT 3 OFFSET 3;

The exact overlap depends on where the new row is inserted and which rows were previously returned. The example illustrates why stable ordering alone does not freeze a changing dataset.

16. NULL Values and Pagination Cursors

Keyset pagination is easiest when its ordering columns are NOT NULL and include a unique tie-breaker. SQL comparisons involving NULL usually evaluate to UNKNOWN, so a simple predicate such as created_at < cursor_timestamp will not include rows whose created_at is NULL.

If nullable sort columns are required, explicitly define how NULL values are ordered and design the cursor predicate to match that order. Do not assume a normal less-than or greater-than comparison handles NULL values automatically.

Prefer Non-NULL Cursor Columns

sql

1
2
3
4
5
6
7
-- A simple cursor works cleanly when product_id is unique
-- and NOT NULL.
SELECT product_id, title
FROM products
WHERE product_id > 120
ORDER BY product_id ASC
LIMIT 20;

A primary key is commonly a good cursor because it is unique and non-NULL. More complex sorting requirements need a matching cursor predicate.

17. Supporting Pagination with Indexes

An index can help the database locate rows and produce them in the requested order without sorting every qualifying row. For example, a B-tree index on product_id may help a query ordered by product_id.

For filtered pagination, an index that begins with the filter column and continues with the ordering columns may help. The right index depends on selectivity, existing indexes, table size, and the query plan. Indexes consume storage and add work to inserts, updates, and deletes, so do not create them without a reason.

Inspect the Query Plan in PostgreSQL

sql

1
2
3
4
5
6
EXPLAIN
SELECT product_id, title
FROM products
WHERE product_id > 120
ORDER BY product_id ASC
LIMIT 20;

EXPLAIN displays the planned operations. EXPLAIN ANALYZE actually executes the query and reports measured execution details, so use it thoughtfully, especially for expensive queries or statements with side effects.

18. Choosing the Right Pagination Strategy

There is no single pagination strategy that is best for every application. Choose based on the user interface, dataset size, ordering requirements, and consistency needs.

LIMIT and OFFSET vs Keyset Pagination

Use the simplest strategy that satisfies performance and navigation requirements.

Aspect	LIMIT and OFFSET	Keyset Pagination
Numbered pages	Naturally supports page numbers	Usually needs cursor navigation
Very deep result sets	Can become expensive	Often more efficient with suitable indexes
Implementation	Simple page number and page size	Requires storing and validating cursor values
Stable ordering	Required for meaningful page boundaries	Required for correct cursor continuation
Consistency during changes	Row positions may shift between requests	Less sensitive to changes before the cursor, but not a snapshot by itself

When to use LIMIT and OFFSET:

Choose OFFSET for manageable datasets, numbered pages, and cases where random page access matters.

When to use Keyset Pagination:

Choose keyset pagination for large ordered result sets, infinite scrolling, and sequential API navigation.

19. Common Mistakes

Avoid these mistakes when implementing pagination in SQL.

Mistakes and Better Alternatives

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Mistake 1: No explicit ordering
SELECT product_id, title
FROM products
LIMIT 10 OFFSET 10;
 
-- Better
SELECT product_id, title
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 10;
 
 
-- Mistake 2: Wrong page calculation for page 3
-- LIMIT 10 OFFSET 30;
 
-- Correct for page 3 with page size 10
SELECT product_id, title
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 20;
 
 
-- Mistake 3: Assuming IDs are consecutive
-- Do not calculate the next cursor as last_id + 1
-- unless consecutive IDs are an explicit requirement.
 
-- Better: compare against the last returned cursor
SELECT product_id, title
FROM products
WHERE product_id > 120
ORDER BY product_id
LIMIT 10;

Always define the ordering, calculate offsets carefully, and use the actual last returned ordering values for keyset cursors.

20. Guided Practice

Try these exercises before reading the solutions. Assume the example tables and columns exist in your database.

Practice Tasks

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Task 1:
-- Return the first 5 employees ordered by emp_id.
 
-- Task 2:
-- Return page 4 of products with 10 products per page.
 
-- Task 3:
-- Return the next 10 products after product_id 250
-- using keyset pagination.
 
-- Task 4:
-- Return the next 5 highest-paid employees after
-- skipping the first 5, with emp_id as the tie-breaker.
 
-- Task 5:
-- Write a query using FETCH FIRST to return
-- at most 3 employees ordered by salary descending.

Attempt each task independently. Check that the ordering is deterministic and that the page offset or cursor matches the task.

21. Practice Solutions

Compare your answers with these solutions. The sample schema assumes employees has emp_id, full_name, and salary, while products has product_id, title, and price.

Solutions

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- Task 1: First 5 employees
SELECT emp_id, full_name
FROM employees
ORDER BY emp_id ASC
LIMIT 5;
 
-- Task 2: Page 4, 10 products per page
-- OFFSET = (4 - 1) * 10 = 30
SELECT product_id, title
FROM products
ORDER BY product_id ASC
LIMIT 10 OFFSET 30;
 
-- Task 3: Next 10 products after product_id 250
SELECT product_id, title
FROM products
WHERE product_id > 250
ORDER BY product_id ASC
LIMIT 10;
 
-- Task 4: Employees ranked by salary
SELECT emp_id, full_name, salary
FROM employees
ORDER BY salary DESC, emp_id ASC
LIMIT 5 OFFSET 5;
 
-- Task 5: Standard FETCH syntax
SELECT emp_id, full_name, salary
FROM employees
ORDER BY salary DESC, emp_id ASC
FETCH FIRST 3 ROWS ONLY;

For Task 3, product_id must be unique and the cursor value must be the last product_id from the previous page. For Task 4, emp_id resolves ties in salary.

22. Multiple-Choice Questions

Test your understanding of result limits and pagination. Each question has one best answer.

Question 1: LIMIT

sql

1
2
3
4
SELECT *
FROM products
ORDER BY product_id
LIMIT 7;

Output

A. Always returns exactly seven rows B. Returns at most seven rows C. Skips seven rows D. Deletes seven rows

Correct answer: B. LIMIT specifies a maximum, not a guarantee that seven matching rows exist.

23. Quiz Answer Key

Review the correct answers and explanations after attempting the questions.

Questions 2–10

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
-- Q2. What does OFFSET 20 do?
-- A. Returns 20 rows
-- B. Skips 20 rows before returning results
-- C. Deletes 20 rows
-- D. Sorts by 20 columns
-- Answer: B
 
-- Q3. What is the offset for page 5 with page size 10?
-- A. 40
-- B. 50
-- C. 5
-- D. 15
-- Answer: A, because (5 - 1) * 10 = 40
 
-- Q4. Which clause defines the result order?
-- A. LIMIT
-- B. OFFSET
-- C. ORDER BY
-- D. WHERE
-- Answer: C
 
-- Q5. Why add a unique tie-breaker to ORDER BY?
-- A. To create more rows
-- B. To resolve ties in the sort order
-- C. To remove NULL values
-- D. To avoid all database locks
-- Answer: B
 
-- Q6. What is a common problem with deep OFFSET pagination?
-- A. It always deletes rows
-- B. It may process many skipped rows
-- C. It always ignores indexes
-- D. It always returns duplicates in one query
-- Answer: B
 
-- Q7. What does keyset pagination commonly use?
-- A. The last row's ordering values
-- B. A random row number
-- C. DROP TABLE
-- D. Only SELECT *
-- Answer: A
 
-- Q8. Is keyset pagination always O(1)?
-- A. Yes
-- B. No; performance depends on the query, indexes,
--    filters, and data distribution
-- C. Only in MySQL
-- D. Only when LIMIT is 1
-- Answer: B
 
-- Q9. Does ORDER BY alone freeze a dataset between requests?
-- A. Yes
-- B. No
-- C. Only with text columns
-- D. Only with LIMIT 10
-- Answer: B
 
-- Q10. Which syntax is supported by PostgreSQL?
-- A. LIMIT and OFFSET
-- B. FETCH FIRST ... ROWS ONLY
-- C. Both A and B
-- D. Neither
-- Answer: C

The key ideas are the page-offset formula, deterministic ordering, and the difference between row-count pagination and cursor-based pagination.

24. Interview Questions and Answers

Use these concise answers to prepare for SQL interviews. Be ready to explain the reasoning and write the query yourself.

Fundamental Questions

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
-- Q1. What is LIMIT?
-- It restricts the maximum number of rows returned.
 
-- Q2. What is OFFSET?
-- It skips a number of rows before returning results.
 
-- Q3. What is pagination?
-- Dividing a result set into smaller pages.
 
-- Q4. How do you calculate an offset?
-- OFFSET = (page_number - 1) * page_size.
 
-- Q5. Why use ORDER BY with pagination?
-- Without it, row order is not guaranteed.
 
-- Q6. Why should ORDER BY include a unique tie-breaker?
-- Equal sort values otherwise leave the relative order
-- of tied rows unspecified.
 
-- Q7. What is deep-offset pagination?
-- Requesting a page after skipping a large number of rows.
 
-- Q8. Why can deep OFFSET be slow?
-- The database may need to process many rows that
-- will ultimately be discarded.
 
-- Q9. What is keyset pagination?
-- Pagination based on the last row's ordering values.
 
-- Q10. What is another name for keyset pagination?
-- Seek pagination or cursor-based pagination.
 
-- Q11. When is OFFSET useful?
-- For simple pagination and direct numbered-page access.
 
-- Q12. When is keyset pagination useful?
-- For sequential navigation through large result sets.
 
-- Q13. Does LIMIT 10 guarantee exactly 10 rows?
-- No. It returns at most 10 rows.
 
-- Q14. Can OFFSET be used without LIMIT in PostgreSQL?
-- Yes. It skips rows and returns the remaining rows.
 
-- Q15. What does FETCH FIRST do?
-- It limits the number of rows returned using
-- standard SQL-style syntax.
 
-- Q16. Does an index eliminate all pagination costs?
-- No. Index usefulness depends on the query and plan.
 
-- Q17. Can new rows affect OFFSET pagination?
-- Yes. Inserts or deletes can shift page boundaries.
 
-- Q18. Does keyset pagination guarantee a snapshot?
-- No. Updates, deletes, and new rows can still affect
-- results between requests.
 
-- Q19. What should a keyset cursor contain?
-- Enough ordering values to uniquely identify the
-- continuation position in the ordered result.
 
-- Q20. How do you investigate a slow pagination query?
-- Inspect the query plan, filters, ordering, indexes,
-- and the number of rows processed.

Interviewers often expect you to compare OFFSET with keyset pagination and explain why ORDER BY and appropriate indexes matter.

25. Lesson Summary

LIMIT controls how many rows are returned, while OFFSET controls how many rows are skipped. Together they provide a straightforward way to implement numbered pages.

Always use an explicit ORDER BY and add a unique tie-breaker so that the ordering is deterministic for a fixed dataset. Remember that rows can still move between pages when the underlying data changes.

Deep OFFSET pagination can become expensive because the database may need to process many preceding rows. Keyset pagination avoids counting through those preceding rows by continuing from the last row's ordering values. It is often preferable for large, sequentially browsed datasets when the query and indexes support efficient seeking.

PostgreSQL supports both LIMIT/OFFSET and FETCH FIRST syntax. Other database systems differ in their supported syntax, so verify the target engine before using a query in production.

26. Common Mistakes to Remember

1. Using LIMIT and OFFSET without ORDER BY and expecting consistent pages.

2. Forgetting to subtract one when calculating the page offset.

3. Assuming LIMIT guarantees exactly the requested number of rows.

4. Assuming deep OFFSET always scans and sorts the entire table, regardless of the execution plan.

5. Claiming keyset pagination is always constant-time or always faster for every query.

6. Using a cursor that omits tie-breaker columns from a multi-column ORDER BY.

7. Assuming IDs are consecutive and calculating the next cursor as last_id + 1.

8. Assuming ORDER BY alone guarantees a consistent snapshot across separate requests.

9. Assuming nullable cursor columns behave like ordinary values in comparison predicates.

10. Assuming every database supports identical LIMIT, OFFSET, and FETCH syntax.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Key takeaways

LIMIT restricts the maximum number of rows returned by a query.
OFFSET skips rows before returning the requested page.
Use ORDER BY with a unique tie-breaker to make pagination ordering deterministic.
The page offset formula is (page number - 1) * page size.
Large offsets can require the database to process and discard many rows.
Keyset pagination continues from the last row's ordering values instead of counting from the beginning.
Keyset pagination can be more efficient for sequential navigation when suitable indexes exist.
Neither LIMIT nor pagination guarantees a consistent snapshot across separate requests.
FETCH syntax is standardized, but supported forms and placement vary between database systems.

Was this lesson helpful?

Found an error? Report an issue