LIMIT and OFFSET pagination in PostgreSQL - Deep Dive

Overview - LIMIT and OFFSET pagination

What is it?

LIMIT and OFFSET are commands in SQL used to control how many rows a query returns and where to start returning rows from. LIMIT sets the maximum number of rows to show, while OFFSET skips a number of rows before starting to return results. This helps break large sets of data into smaller, manageable pages. It is commonly used to show results page by page in applications.

Why it matters

Without LIMIT and OFFSET, queries would return all matching rows at once, which can be slow and overwhelming when dealing with large data. This would make websites and apps slow or crash when showing lists like products or messages. LIMIT and OFFSET solve this by letting you fetch just a small part of the data at a time, improving speed and user experience.

Where it fits

Before learning LIMIT and OFFSET, you should understand basic SQL SELECT queries and how to filter data with WHERE. After mastering pagination, you can learn about more advanced techniques like keyset pagination or cursor-based pagination for better performance with very large datasets.

Mental Model

Core Idea

LIMIT and OFFSET let you slice a big list of data into smaller pages by choosing how many items to show and where to start.

Think of it like...

Imagine a book with many pages. LIMIT is like deciding how many lines you want to read on a page, and OFFSET is like skipping a certain number of lines before you start reading. This way, you read the book bit by bit instead of all at once.

┌───────────────┐
│ Full dataset  │
│  [Row 1]      │
│  [Row 2]      │
│  [Row 3]      │
│  ...          │
│  [Row N]      │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ OFFSET = 2    │  Skip first 2 rows
│ LIMIT = 3     │  Show next 3 rows
│  [Row 3]      │
│  [Row 4]      │
│  [Row 5]      │
└───────────────┘

Build-Up - 7 Steps

1

Foundation

Basic SELECT query review

Concept: Understand how to retrieve data from a table using SELECT.

A SELECT query asks the database to give you rows from a table. For example, SELECT * FROM products; returns all rows and columns from the products table.

Result

All rows from the products table are shown.

Knowing how to get data is the first step before learning how to limit or paginate it.

2

Foundation

Introducing LIMIT to restrict rows

Concept: LIMIT sets the maximum number of rows returned by a query.

Adding LIMIT 5 to a query like SELECT * FROM products LIMIT 5; returns only the first 5 rows from the products table, no matter how many rows exist.

Result

Only 5 rows are shown instead of all rows.

LIMIT helps control how much data you get, which is useful for showing small chunks instead of everything.

3

Intermediate

Using OFFSET to skip rows

🤔Before reading on: do you think OFFSET skips rows before or after LIMIT is applied? Commit to your answer.

Concept: OFFSET tells the database to skip a number of rows before starting to return results.

For example, SELECT * FROM products OFFSET 10 LIMIT 5; skips the first 10 rows and then returns the next 5 rows. This is useful for showing pages beyond the first one.

Result

Rows 11 to 15 from the products table are shown.

Understanding OFFSET lets you jump to any part of the data, enabling page navigation.

4

Intermediate

Combining LIMIT and OFFSET for pagination

🤔Before reading on: do you think LIMIT or OFFSET controls the number of rows returned? Commit to your answer.

Concept: LIMIT and OFFSET together let you fetch a specific page of data by skipping some rows and limiting how many to show.

To get page 3 of a list with 10 items per page, use OFFSET 20 LIMIT 10 (skip first 20 rows, show next 10). This pattern is common in apps showing lists page by page.

Result

Page 3 of data with 10 rows is shown.

Combining these commands creates a simple but powerful way to paginate data.

5

Intermediate

Ordering data for consistent pagination

🤔Before reading on: do you think pagination works well without ordering? Commit to your answer.

Concept: Using ORDER BY ensures rows are returned in a consistent order, which is important for reliable pagination.

For example, SELECT * FROM products ORDER BY id OFFSET 10 LIMIT 5; orders rows by id before skipping and limiting. Without ORDER BY, the order can be random and pages may overlap or miss rows.

Result

Rows are shown in a stable order, making pagination predictable.

Ordering is essential to avoid confusing or repeated data when paging through results.

6

Advanced

Performance issues with large OFFSET values

🤔Before reading on: do you think OFFSET is fast even with very large numbers? Commit to your answer.

Concept: OFFSET can slow down queries when skipping many rows because the database still processes all skipped rows internally.

For example, OFFSET 100000 LIMIT 10 makes the database scan and discard 100,000 rows before returning 10. This can cause delays and high resource use on big tables.

Result

Query runs slower as OFFSET grows larger.

Knowing OFFSET's cost helps you choose better pagination methods for large datasets.

7

Expert

Alternatives to OFFSET for efficient pagination

🤔Before reading on: do you think keyset pagination is simpler or more complex than OFFSET? Commit to your answer.

Concept: Keyset pagination uses a WHERE clause with a cursor value instead of OFFSET to fetch the next page efficiently.

Instead of OFFSET, you use something like WHERE id > last_seen_id ORDER BY id LIMIT 10. This avoids scanning skipped rows and is faster for large tables.

Result

Pagination is faster and more scalable on big datasets.

Understanding keyset pagination reveals why OFFSET is not always the best choice in production.

Under the Hood

When a query with OFFSET and LIMIT runs, the database engine scans rows in the specified order. It skips the number of rows given by OFFSET by reading and discarding them internally, then collects the next LIMIT rows to return. This means OFFSET does not jump directly to a row but processes all skipped rows, which can be costly for large OFFSET values.

Why designed this way?

LIMIT and OFFSET were designed as simple, intuitive ways to paginate results using standard SQL syntax. OFFSET provides a straightforward way to skip rows without needing complex state or cursors. However, this simplicity trades off performance on large datasets, which led to alternative methods like keyset pagination.

┌───────────────┐
│ Query starts  │
│ with ORDER BY │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Scan rows in  │
│ order         │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Skip OFFSET   │  ← Rows are read and discarded here
│ rows          │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Collect LIMIT │  ← Rows returned to user
│ rows          │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does OFFSET improve query speed by jumping directly to a row? Commit yes or no.

Common Belief: OFFSET makes queries faster by jumping directly to the starting row.

Reality: OFFSET actually makes queries slower as it reads and discards all skipped rows internally before returning results.

Why it matters: Believing OFFSET is fast can lead to slow, unresponsive applications when paginating large datasets.

Quick: Can you paginate reliably without ORDER BY? Commit yes or no.

Common Belief: You can paginate without ordering rows because the database returns rows in a fixed order.

Reality: Without ORDER BY, the row order is not guaranteed and can change, causing repeated or missing rows across pages.

Why it matters: Skipping ORDER BY leads to confusing user experiences with inconsistent pagination.

Quick: Does LIMIT alone control which rows are skipped? Commit yes or no.

Common Belief: LIMIT alone can be used to get any page of data by just changing its value.

Reality: LIMIT only controls how many rows are returned, not which rows are skipped; OFFSET is needed to skip rows.

Why it matters: Misunderstanding this causes incorrect queries that always return the first rows, not the desired page.

Quick: Is keyset pagination always more complex than OFFSET? Commit yes or no.

Common Belief: Keyset pagination is too complex and not worth using over OFFSET.

Reality: Keyset pagination is more efficient and often simpler in practice for large datasets, though it requires a cursor value.

Why it matters: Ignoring keyset pagination can cause performance problems in real-world applications.

Expert Zone

1

OFFSET counts rows after ORDER BY is applied, so the order affects which rows are skipped.

2

Using OFFSET with large values can cause the database to scan many rows, increasing CPU and memory usage.

3

Keyset pagination requires stable, unique ordering columns to work correctly and avoid missing or duplicated rows.

When NOT to use

Avoid OFFSET pagination for very large datasets or real-time data where performance and consistency matter. Instead, use keyset pagination or cursor-based methods that rely on indexed columns and WHERE clauses for efficient navigation.

Production Patterns

In production, OFFSET and LIMIT are often used for small to medium datasets or admin tools. For user-facing apps with large data, keyset pagination is preferred. Also, caching pages and using indexed ORDER BY columns are common practices to improve speed.

Connections

Cursor-based pagination

Builds-on and improves OFFSET pagination

Knowing OFFSET pagination helps understand why cursor-based methods use a cursor value instead of skipping rows, improving performance.

Data streaming

Similar pattern of processing data in chunks

LIMIT and OFFSET pagination is like streaming data in small pieces, which helps manage memory and responsiveness in both databases and network data.

Memory paging in operating systems

Shares the idea of dividing large data into pages

Understanding how OS memory paging works helps grasp why databases paginate results to handle large data efficiently.

Common Pitfalls

#1 Skipping ORDER BY causes inconsistent pagination

Wrong approach:

SELECT * FROM products OFFSET 10 LIMIT 5;

Correct approach:

SELECT * FROM products ORDER BY id OFFSET 10 LIMIT 5;

Root cause:

Not realizing that without ORDER BY, row order is unpredictable and changes between queries.

#2 Using OFFSET with very large values causes slow queries

Wrong approach:

SELECT * FROM products ORDER BY id OFFSET 1000000 LIMIT 10;

Correct approach:

SELECT * FROM products WHERE id > last_seen_id ORDER BY id LIMIT 10;

Root cause:

Believing OFFSET skips rows instantly instead of scanning them internally.

#3 Trying to get page 3 by only changing LIMIT

Wrong approach:

SELECT * FROM products LIMIT 30;

Correct approach:

SELECT * FROM products OFFSET 20 LIMIT 10;

Root cause:

Misunderstanding that LIMIT controls count, not starting position.

Key Takeaways

LIMIT controls how many rows a query returns, while OFFSET controls how many rows to skip before starting to return rows.

Always use ORDER BY with LIMIT and OFFSET to ensure consistent and predictable pagination results.

OFFSET pagination can become slow with large skip values because the database still processes all skipped rows internally.

For large datasets, consider keyset pagination as a more efficient alternative to OFFSET pagination.

Understanding LIMIT and OFFSET is essential for building user-friendly, performant applications that display data in pages.

Practice

1. What does the LIMIT clause do in a PostgreSQL query?

easy

A. It sorts the rows in ascending order.

B. It controls how many rows the query returns.

C. It skips a number of rows before returning results.

D. It groups rows based on a column.

Solution

1. Step 1: Understand the role of LIMIT

   The LIMIT clause restricts the number of rows returned by a query to a specified count.

2. Step 2: Differentiate from OFFSET

   OFFSET skips rows, but LIMIT controls the count of rows returned after skipping.

3. Final Answer:

   It controls how many rows the query returns. -> Option B

4. Quick Check:

   LIMIT = row count control [OK]

Hint: LIMIT sets max rows returned, OFFSET skips rows first [OK]

Common Mistakes:

• Confusing LIMIT with OFFSET
• Thinking LIMIT sorts rows
• Assuming LIMIT groups rows

2. Which of the following is the correct syntax to get 10 rows starting from the 6th row in PostgreSQL?

easy

A. SELECT * FROM table LIMIT 10 OFFSET 5;

B. SELECT * FROM table LIMIT 5 OFFSET 10;

C. SELECT * FROM table LIMIT 6 OFFSET 10;

D. SELECT * FROM table OFFSET 10 LIMIT 6;

Solution

1. Step 1: Understand OFFSET and LIMIT order

   OFFSET 5 skips first 5 rows, LIMIT 10 returns next 10 rows.

2. Step 2: Check syntax correctness

   PostgreSQL allows LIMIT then OFFSET; LIMIT 10 OFFSET 5 is correct.

3. Final Answer:

   SELECT * FROM table LIMIT 10 OFFSET 5; -> Option A

4. Quick Check:

   LIMIT 10 OFFSET 5 = 10 rows after skipping 5 [OK]

Hint: Use LIMIT first, then OFFSET for correct syntax [OK]

Common Mistakes:

• Swapping LIMIT and OFFSET values
• Using OFFSET larger than total rows
• Incorrect order of LIMIT and OFFSET

3. Given the table employees with 15 rows ordered by id, what rows will this query return?

SELECT * FROM employees ORDER BY id LIMIT 5 OFFSET 10;

medium

A. Rows with id 11 to 15

B. Rows with id 6 to 10

C. Rows with id 1 to 5

D. Rows with id 10 to 14

Solution

1. Step 1: Understand OFFSET 10

   OFFSET 10 skips the first 10 rows (id 1 to 10).

2. Step 2: LIMIT 5 after skipping

   LIMIT 5 returns the next 5 rows, which are id 11 to 15.

3. Final Answer:

   Rows with id 11 to 15 -> Option A

4. Quick Check:

   OFFSET 10 + LIMIT 5 = rows 11-15 [OK]

Hint: OFFSET skips, LIMIT picks next rows [OK]

Common Mistakes:

• Counting OFFSET as inclusive
• Mixing row numbers with id values
• Assuming OFFSET starts at 1

4. Identify the error in this query:

SELECT * FROM products LIMIT OFFSET 5 10;

medium

A. LIMIT and OFFSET order is reversed.

B. OFFSET value must come before LIMIT value.

C. Syntax error: LIMIT must have a number before OFFSET.

D. No error, query is valid.

Solution

1. Step 1: Analyze LIMIT and OFFSET syntax

   LIMIT must be followed by a number, then OFFSET with its number.

2. Step 2: Check given query

   Query has 'LIMIT OFFSET 5 10' which is invalid syntax; LIMIT lacks a number before OFFSET.

3. Final Answer:

   Syntax error: LIMIT must have a number before OFFSET. -> Option C

4. Quick Check:

   LIMIT needs number before OFFSET [OK]

Hint: Always put LIMIT number before OFFSET number [OK]

Common Mistakes:

• Writing OFFSET before LIMIT number
• Omitting numbers after LIMIT or OFFSET
• Assuming order doesn't matter

5. You want to display page 3 of a product list, showing 20 products per page. Which query correctly uses LIMIT and OFFSET?

hard

A. SELECT * FROM products LIMIT 40 OFFSET 20;

B. SELECT * FROM products LIMIT 60 OFFSET 20;

C. SELECT * FROM products LIMIT 20 OFFSET 60;

D. SELECT * FROM products LIMIT 20 OFFSET 40;

Solution

1. Step 1: Calculate OFFSET for page 3

   Page 3 means skipping first 2 pages: 2 * 20 = 40 rows to skip.

2. Step 2: Set LIMIT to page size

   LIMIT is number of rows per page, so LIMIT 20.

3. Step 3: Form the query

   Use LIMIT 20 OFFSET 40 to get page 3 products.

4. Final Answer:

   SELECT * FROM products LIMIT 20 OFFSET 40; -> Option D

5. Quick Check:

   Page 3 offset = 40, limit = 20 [OK]

Hint: OFFSET = (page-1)*page_size, LIMIT = page_size [OK]

Common Mistakes:

• Using OFFSET as page number
• Swapping LIMIT and OFFSET values
• Calculating OFFSET incorrectly