FETCH FIRST for SQL standard pagination in PostgreSQL - Deep Dive

Overview - FETCH FIRST for SQL standard pagination

What is it?

FETCH FIRST is a SQL command used to limit the number of rows returned by a query. It helps you get only the first few results from a larger set, which is useful when you want to see a sample or page through data. This command is part of the SQL standard and works in many databases, including PostgreSQL. It is often used together with ORDER BY to control which rows appear first.

Why it matters

Without a way to limit results, queries could return huge amounts of data, making applications slow and hard to use. FETCH FIRST solves this by letting you get just a small, manageable chunk of data at a time. This is especially important for websites or apps that show data in pages, so users don’t have to wait for everything to load at once.

Where it fits

Before learning FETCH FIRST, you should understand basic SQL SELECT queries and how ORDER BY works to sort data. After mastering FETCH FIRST, you can learn about OFFSET for skipping rows and more advanced pagination techniques like keyset pagination or cursor-based pagination.

Mental Model

Core Idea

FETCH FIRST tells the database to stop returning rows after reaching a set number, like saying 'just give me the first N items' from a list.

Think of it like...

Imagine you are at a bakery with a long line of pastries. You only want to buy the first 5 pastries on the tray, so you tell the baker, 'Please give me the first 5.' FETCH FIRST works the same way with rows in a database query.

┌───────────────┐
│ Full dataset  │
│ (many rows)   │
└──────┬────────┘
       │
       ▼
┌─────────────────────────┐
│ SELECT ... ORDER BY ...  │
│ FETCH FIRST N ROWS ONLY  │
└──────────┬──────────────┘
           │
           ▼
┌─────────────────┐
│ Result: N rows   │
│ (first N rows)  │
└─────────────────┘

Build-Up - 6 Steps

1

Foundation

Basic SELECT and ORDER BY

Concept: Understanding how to select data and order it is essential before limiting results.

A simple query to get data looks like this: SELECT * FROM employees; To control which rows come first, you add ORDER BY: SELECT * FROM employees ORDER BY salary DESC; This sorts employees by salary from highest to lowest.

Result

The query returns all employees sorted by salary, highest first.

Knowing how ORDER BY works is key because FETCH FIRST limits rows after sorting, so the order decides which rows you get.

2

Foundation

Why limit rows with FETCH FIRST

Concept: FETCH FIRST limits the number of rows returned to avoid overwhelming results.

If you only want the top 3 highest-paid employees, you add FETCH FIRST 3 ROWS ONLY: SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 3 ROWS ONLY; This returns just 3 rows, the highest salaries.

Result

Only 3 rows are returned, the top 3 salaries.

Limiting rows improves performance and user experience by showing only what is needed.

3

Intermediate

Using FETCH FIRST with OFFSET for pagination

🤔Before reading on: do you think FETCH FIRST alone can skip rows to get the second page? Commit to yes or no.

Concept: OFFSET skips a number of rows before FETCH FIRST limits the next set, enabling pagination.

To get the second page of 3 rows, skip the first 3 with OFFSET: SELECT * FROM employees ORDER BY salary DESC OFFSET 3 FETCH FIRST 3 ROWS ONLY; This skips the top 3 and returns the next 3.

Result

Rows 4 to 6 by salary order are returned.

Combining OFFSET and FETCH FIRST lets you move through data pages, not just the first chunk.

4

Intermediate

FETCH FIRST syntax variations and compatibility

🤔Before reading on: do you think FETCH FIRST syntax is exactly the same in all SQL databases? Commit to yes or no.

Concept: FETCH FIRST syntax is part of the SQL standard but can vary slightly across databases.

In PostgreSQL, you can write: FETCH FIRST 5 ROWS ONLY or LIMIT 5 (PostgreSQL specific) Some databases use FETCH FIRST, others use LIMIT or TOP. Knowing the standard helps write portable queries.

Result

You understand that FETCH FIRST is standard but alternatives exist.

Recognizing syntax differences prevents errors when moving between database systems.

5

Advanced

Performance considerations with FETCH FIRST

🤔Before reading on: do you think FETCH FIRST always makes queries faster? Commit to yes or no.

Concept: FETCH FIRST can improve performance but depends on indexing and query plan.

If the query uses ORDER BY on indexed columns, FETCH FIRST can quickly return results. But if sorting requires scanning many rows, FETCH FIRST still needs to process them before limiting. Example: SELECT * FROM large_table ORDER BY non_indexed_column FETCH FIRST 10 ROWS ONLY; This may be slow because sorting all rows is needed first.

Result

FETCH FIRST speeds up queries only when sorting is efficient.

Understanding how FETCH FIRST interacts with indexes helps write fast queries.

6

Expert

Advanced pagination: keyset vs OFFSET/FETCH FIRST

🤔Before reading on: do you think OFFSET with FETCH FIRST is the best way to paginate large datasets? Commit to yes or no.

Concept: OFFSET with FETCH FIRST can be inefficient for large pages; keyset pagination is a better alternative.

OFFSET skips rows by counting, which gets slower as OFFSET grows. Keyset pagination uses WHERE conditions to fetch next pages based on last seen values: SELECT * FROM employees WHERE salary < last_salary ORDER BY salary DESC FETCH FIRST 3 ROWS ONLY; This avoids scanning skipped rows and is faster for big data.

Result

Keyset pagination is more efficient for deep pages than OFFSET/FETCH FIRST.

Knowing when to switch from OFFSET to keyset pagination prevents slow user experiences on large datasets.

Under the Hood

When a query with FETCH FIRST runs, the database engine executes the query plan, including sorting if ORDER BY is present. It then streams rows to the client but stops sending rows once the FETCH FIRST limit is reached. Internally, the engine may still process all rows to sort them unless indexes or optimizations reduce work. FETCH FIRST acts as a hard stop on the output stream, preventing extra data transfer.

Why designed this way?

FETCH FIRST was designed as part of the SQL standard to provide a uniform way to limit query results across different databases. Before this, databases had their own ways (like LIMIT or TOP), causing portability issues. The standard balances simplicity and flexibility, allowing easy pagination and sampling without complex syntax.

┌───────────────┐
│ Query Parser  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Query Planner │
│ (decides plan)│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Execution     │
│ - Scan rows   │
│ - Sort rows   │
│ - Apply FETCH │
│   FIRST limit │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Return rows   │
│ (stop after N)│
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does FETCH FIRST guarantee the same rows every time without ORDER BY? Commit to yes or no.

Common Belief: FETCH FIRST always returns the same rows if the data doesn't change.

Reality: Without ORDER BY, the rows returned by FETCH FIRST can be arbitrary and may change between queries.

Why it matters: Relying on FETCH FIRST without ORDER BY can cause inconsistent results and bugs in applications.

Quick: Does OFFSET with FETCH FIRST always perform well on large tables? Commit to yes or no.

Common Belief: OFFSET with FETCH FIRST is efficient for all pagination needs.

Reality: OFFSET causes the database to scan and skip rows, which slows down as OFFSET grows large.

Why it matters: Using OFFSET for deep pages can cause slow queries and poor user experience.

Quick: Is FETCH FIRST a PostgreSQL-only feature? Commit to yes or no.

Common Belief: FETCH FIRST is unique to PostgreSQL and not standard SQL.

Reality: FETCH FIRST is part of the SQL standard and supported by many databases, though syntax varies.

Why it matters: Knowing FETCH FIRST is standard helps write portable SQL across systems.

Quick: Does FETCH FIRST reduce the amount of data the database processes internally? Commit to yes or no.

Common Belief: FETCH FIRST makes the database process fewer rows internally.

Reality: FETCH FIRST limits output rows but the database may still process all rows for sorting or filtering.

Why it matters: Assuming FETCH FIRST always improves performance can lead to inefficient queries.

Expert Zone

1

FETCH FIRST combined with ORDER BY on indexed columns can use index scans to avoid sorting all rows.

2

The SQL standard allows FETCH FIRST with or without the ROWS keyword; some databases require one or the other.

3

Using FETCH FIRST with ties (FETCH FIRST N ROWS WITH TIES) returns extra rows that match the last row's sort key, which can affect pagination.

When NOT to use

Avoid FETCH FIRST with OFFSET for very large datasets or deep pagination; use keyset pagination or cursor-based methods instead. Also, if you need exact control over row counts with ties, consider alternative approaches.

Production Patterns

In production, FETCH FIRST is often used for simple dashboards or previews. For user-facing pagination, OFFSET with FETCH FIRST is common for small pages, but keyset pagination is preferred for performance at scale. Developers also combine FETCH FIRST with window functions for advanced analytics.

Connections

Cursor-based Pagination

Builds-on

Understanding FETCH FIRST and OFFSET helps grasp why cursor-based pagination improves performance by avoiding large OFFSET skips.

Indexing in Databases

Same pattern

Knowing how indexes support ORDER BY and FETCH FIRST reveals why some queries are fast and others slow.

Batch Processing in Software Engineering

Similar pattern

FETCH FIRST is like processing data in batches to avoid overload, a concept used in many fields to handle large workloads efficiently.

Common Pitfalls

#1 Using FETCH FIRST without ORDER BY expecting consistent results.

Wrong approach:

SELECT * FROM employees FETCH FIRST 5 ROWS ONLY;

Correct approach:

SELECT * FROM employees ORDER BY employee_id FETCH FIRST 5 ROWS ONLY;

Root cause:

Not understanding that SQL tables are unordered sets and FETCH FIRST returns arbitrary rows without ORDER BY.

#2 Using OFFSET with large values causing slow queries.

Wrong approach:

SELECT * FROM employees ORDER BY salary DESC OFFSET 100000 FETCH FIRST 10 ROWS ONLY;

Correct approach:

SELECT * FROM employees WHERE salary < last_salary ORDER BY salary DESC FETCH FIRST 10 ROWS ONLY;

Root cause:

Believing OFFSET skips rows cheaply, ignoring that the database must scan and discard many rows.

#3 Mixing FETCH FIRST syntax with non-standard keywords causing errors.

Wrong approach:

SELECT * FROM employees FETCH NEXT 5 ROWS ONLY;

Correct approach:

SELECT * FROM employees FETCH FIRST 5 ROWS ONLY;

Root cause:

Confusing similar but different SQL standard keywords or database-specific syntax.

Key Takeaways

FETCH FIRST limits the number of rows returned by a query, making data easier to handle and improving performance.

Always use ORDER BY with FETCH FIRST to ensure consistent and predictable results.

Combining OFFSET with FETCH FIRST enables simple pagination but can become slow for large offsets.

Keyset pagination is a more efficient alternative for deep pagination in large datasets.

FETCH FIRST is part of the SQL standard, promoting portable and clear SQL queries across databases.