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.