Pagination in PostgreSQL with ROW_NUMBER explained

Home » Python » Pagination in PostgreSQL with ROW_NUMBER explained

Paginating large result sets in PostgreSQL can look simple at first glance, but doing it efficiently and correctly – especially when there are ties in your ordering column – needs a bit more than just slapping a LIMIT/OFFSET on a query. The window function ROW_NUMBER() is one of the most versatile tools you have to solve this problem while also unlocking a bunch of extra analytical use cases like ranking, top-N queries or duplicate detection.

This guide dives deep into how to use ROW_NUMBER() for pagination in PostgreSQL, how it really works under the hood, how it differs from other ranking functions, what performance implications to expect, and how other major database engines behave with similar patterns. We will also look at tricky real-world scenarios like cursor-based pagination when the sort column contains duplicates and how to combine ROW_NUMBER() with CTEs, joins and subqueries for clean, production-ready SQL.

What the PostgreSQL ROW_NUMBER() window function actually does

At its core, ROW_NUMBER() is a window function that assigns a unique, sequential integer to every row in a result set, starting at 1 and incrementing by 1 without gaps. This numbering can be applied to the entire result or restarted for each group of rows, depending on how you define the window.

The generic syntax in PostgreSQL looks like this:

ROW_NUMBER() OVER ( [PARTITION BY partition_expression] ORDER BY order_expression )

Two parts inside the OVER clause control how row numbers are assigned: PARTITION BY (optional) splits the result set into independent groups, and ORDER BY (mandatory) defines the order within each partition that determines which row gets which number.

If you omit PARTITION BY, the function treats the complete result set as a single partition and simply numbers all rows according to the specified ordering. Once you add PARTITION BY, row numbering restarts from 1 in every partition, which is extremely useful for per-category rankings, top-N-per-group queries and grouped deduplication.

Unlike ranking functions such as RANK() or DENSE_RANK(), ROW_NUMBER() completely ignores ties and never repeats a number, even when rows have identical values in the sorting columns. That makes it ideal for deterministic pagination and precise row slicing.

Basic ROW_NUMBER() examples to build intuition

Before using ROW_NUMBER() for pagination, it helps to see it in action on simple examples, where the goal is just to number rows in a controlled way. Imagine a table employees with columns id, name, department and salary.

To assign a global row number sorted by salary descending, you could write:

SELECT
id,
name,
department,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

This query returns all employees, sorted by salary from highest to lowest, with row_num = 1 for the best-paid employee, 2 for the second one, and so on, with no gaps and no repeated values. Ties in salary are broken arbitrarily unless you extend the ORDER BY with additional columns.

If instead you need row numbering to restart inside each department, you combine PARTITION BY with ORDER BY:

SELECT
id,
name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

Here, every department gets its own 1, 2, 3, … sequence, so you can easily find “the top earner in each department” by later filtering for row_num = 1 in an outer query or CTE. This pattern is the backbone for many top-N-per-group queries.

To cleanly separate numbering logic from filtering logic, it is common to wrap the window function in a CTE or subquery and then filter the generated row numbers in the outer SELECT. This is especially important because window functions cannot be used directly in the WHERE clause of the same SELECT that defines them.

Using ROW_NUMBER() for classic page-based pagination

The most straightforward way to do pagination with ROW_NUMBER() in PostgreSQL is to compute the row index for each row, and then ask for a numeric range that corresponds to the page you want. This is sometimes used as an alternative to OFFSET/LIMIT, and also works nicely when porting pagination code from SQL Server or Oracle.

Suppose you want a page of results with size @PageSize and page number @PageNumber (0-based index). The typical T-SQL query looks like:

SELECT PK_ID, Truco, Descripcion
FROM (
SELECT
PK_ID,
Truco,
Descripcion,
ROW_NUMBER() OVER (ORDER BY Truco, PK_ID) AS RowNumber
FROM TrucosInformaticos
) AS Trucos
WHERE RowNumber BETWEEN (@PageSize * @PageNumber) + 1
AND @PageSize * (@PageNumber + 1);

The same logic translates directly to PostgreSQL – you only adapt parameter syntax and, if you want, wrap it in a function instead of a stored procedure. The essence is: compute ROW_NUMBER() once, then slice rows by a numeric interval that corresponds to your page boundaries.

For example, in PostgreSQL, for a fixed page you might write:

WITH ranked_posts AS (
SELECT
id,
title,
ROW_NUMBER() OVER (ORDER BY title, id) AS row_num
FROM posts
)
SELECT id, title
FROM ranked_posts
WHERE row_num BETWEEN 11 AND 20;

This returns rows 11-20 in the ordering defined by ORDER BY title, id, effectively giving you the second page with page size 10. The big advantage over plain OFFSET is that the row numbers are explicit and can be combined with additional logic, for example, joining back, filtering, or doing further analytics.

Cursor-style pagination when the sort column has duplicates

Offset-based pagination is easy to reason about, but it can cause performance issues on large tables and also becomes fragile when the underlying data changes between requests. Cursor-based pagination (also called keyset pagination) aims to solve this by using the last seen item as an anchor instead of an absolute offset.

Things get tricky when the column you sort by contains duplicate values. Consider a schema with posts and comments:

CREATE TABLE posts(
id uuid PRIMARY KEY,
title varchar(255) NOT NULL
);

CREATE TABLE comments(
id uuid PRIMARY KEY,
post_id uuid NOT NULL REFERENCES posts
);

Imagine you first build a query that orders posts by the number of comments in descending order:

SELECT p.*, COUNT(c.id) AS comments_count
FROM posts AS p
LEFT JOIN comments AS c ON p.id = c.post_id
GROUP BY p.id
ORDER BY comments_count DESC;

For cursor-based pagination, one might be tempted to select up to a certain comments_count threshold and then apply a LIMIT:

WITH cte AS (
SELECT p.*, COUNT(c.id) AS comments_count
FROM posts AS p
LEFT JOIN comments AS c ON p.id = c.post_id
GROUP BY p.id
ORDER BY comments_count DESC
)
SELECT *
FROM cte
WHERE comments_count <= (
SELECT comments_count FROM cte WHERE id = '00000000-0000-0000-0000-000000000003'
)
LIMIT 3;

The problem appears when multiple posts share the same comments_count. If two posts both have a count of 2, and your cursor points to one of them, using <= includes both of those tied rows on the second page, while using < skips all rows with that same count and jumps too far, missing some posts you expected to see.

This is a classic symptom of ordering by a non-unique key in cursor pagination – the database cannot deterministically slice the dataset “in the middle” of a group of ties if your cursor only encodes the non-unique value. You need a unique and stable ordering to define a cursor safely.

One workaround is to create a synthetic, unique sort key by combining the comment count with the primary key, for example via concatenation:

WITH cte AS (
SELECT
p.,
COUNT(c.id) AS comments_count,
CONCAT(COUNT(c.id), ':', p.id) AS comments_count_id
FROM posts AS p
LEFT JOIN comments AS c ON p.id = c.post_id
GROUP BY p.id
ORDER BY comments_count_id DESC
)
SELECT *
FROM cte
WHERE comments_count_id < (
SELECT comments_count_id
FROM cte
WHERE id = '00000000-0000-0000-0000-000000000003'
)
LIMIT 3;

By building a composite key like '2:00000000-...-0003', you make the ordering strictly unique and can safely say “give me rows with comments_count_id less than the anchor” without ambiguity. It is the same idea as always including id in your ORDER BY as a tie-breaker.

In practice, you do not have to concatenate to a string; you can just use multiple columns in ORDER BY and encode them in your cursor object in the application layer. The important part from the database side is that the total ordering is unique and reproducible between calls.

Pagination with ROW_NUMBER() vs LIMIT and OFFSET

PostgreSQL supports the classic LIMIT and OFFSET syntax out of the box, and for many small to medium result sets, it is perfectly fine to use. You simply specify how many rows to skip, and how many to return.

However, OFFSET-based pagination has two big drawbacks: performance and stability. As OFFSET grows, PostgreSQL still has to scan and discard all the previous rows before it can start returning results, which becomes expensive on large datasets. And if data changes between requests, the pages can “shift” and show duplicates or skip rows.

Using ROW_NUMBER() for pagination gives you a way to materialize the row index once and then slice it cleanly:

WITH numbered_products AS (
SELECT
product_id,
product_name,
price,
ROW_NUMBER() OVER (ORDER BY product_name) AS row_number
FROM products
)
SELECT product_id, product_name, price
FROM numbered_products
WHERE row_number BETWEEN 11 AND 20
ORDER BY product_name;

This pattern reads intuitively: it first assigns each product its position in the sorted list, then the outer query fetches rows 11 through 20. As long as the underlying data does not change between execution and consuming the page, you get a stable slice of the logical sequence.

That said, ROW_NUMBER()-based pagination isn’t a silver bullet for performance either. The database still needs to evaluate the window function over all eligible rows to assign numbers, so for extremely large tables, this can be just as costly as a large OFFSET. Where it shines is when combined with additional filtering or when you want deterministic, row-number-based logic beyond pure pagination.

How window-function pagination behaves across database engines

Window functions such as ROW_NUMBER() are standardized SQL features, but each database engine optimizes them differently for pagination-like patterns. Some products can recognize “top-N with window function” queries and stop scanning early using index access; others will quietly process the full set every time.

Consider this typical top-N / pagination-style query using ROW_NUMBER over an ordered index in a sales table:

SELECT *
FROM (
SELECT
sales.*,
ROW_NUMBER() OVER (ORDER BY sale_date DESC, sale_id DESC) AS rn
FROM sales
) AS tmp
WHERE rn BETWEEN 11 AND 20
ORDER BY sale_date DESC, sale_id DESC;

Here, ROW_NUMBER counts rows according to the ordering defined in the OVER clause, and the outer WHERE restricts the result to a specific page (rows 11-20). This is logically equivalent to a top-N query combined with an offset.

Oracle, for example, is able to recognize the stop condition and use an index on sale_date and sale_id to implement a “top-N in pipeline” behavior. Its execution plan may show WINDOW NOSORT STOPKEY, indicating that the engine does not need an extra sort and will stop as soon as it has produced the upper bound of the requested window.

Support for this kind of optimization is not universal. Some versions of PostgreSQL and other engines like MySQL, MariaDB and Db2 do not stop index scans early in these window-function-based patterns, meaning they still process more rows than strictly necessary to deliver the requested page.

Recent PostgreSQL versions (15+ and onward) have improved window-function performance and optimizations, but behavior can still differ between major versions. Always inspect execution plans with EXPLAIN (ANALYZE) to see if your database is able to exploit indexes and stop early, or if it is scanning and sorting the full result set.

Combining ROW_NUMBER() with DISTINCT, CTEs and subqueries

A common gotcha when using ROW_NUMBER() alongside DISTINCT is that the window function is evaluated before the duplicate elimination step. This can lead to confusing results where apparently duplicate values still receive distinct row numbers.

For instance, if you try to enumerate distinct prices from a products table with a single query like:

SELECT DISTINCT price,
ROW_NUMBER() OVER (ORDER BY price) AS rn
FROM products;

You might be surprised to see multiple rows with the same price but different rn values, because the window function ran on all rows before DISTINCT removed duplicates from the final projection.

The robust way to handle this is to first materialize the distinct values (via a CTE or subquery), then apply ROW_NUMBER() on top of that:

WITH prices AS (
SELECT DISTINCT price
FROM products
)
SELECT price,
ROW_NUMBER() OVER (ORDER BY price) AS rn
FROM prices;

Alternatively, you can use a subquery directly in the FROM clause, achieving the same effect. The critical idea is to decide explicitly which result set is the “window” where ROW_NUMBER() should operate, and if you need uniqueness, build that set first.

This pattern is extremely handy for pagination tasks like “get the product with the 3rd highest price” or “enumerate distinct prices with row numbers and then pick a specific one.” You can first get unique sorted prices with ROW_NUMBER() and then join or filter on the particular rank you care about.

ROW_NUMBER() for ranking, top-N and duplicate removal

Although our focus is pagination, it would be a waste not to mention that ROW_NUMBER() is a fantastic tool for ranking and deduplicating data. Many of the same patterns you use for pagination double as ranking logic.

To get the top N records per category, you can partition by category and rank rows by a metric such as price descending:

WITH ranked_products AS (
SELECT
product_id,
product_name,
category_id,
price,
ROW_NUMBER() OVER (
PARTITION BY category_id
ORDER BY price DESC
) AS rank
FROM products
)
SELECT product_id, product_name, category_id, price
FROM ranked_products
WHERE rank <= 2;

This returns the two most expensive products in each category. You can then join to a categories table using USING (category_id) or an explicit join to display human-readable names.

For duplicate removal, ROW_NUMBER() is often used in combination with PARTITION BY to mark all but the first occurrence in each group as duplicates. Consider a simple table:

CREATE TABLE items (
id INT,
name VARCHAR
);

Suppose you insert several duplicated names and want to remove the extra copies while keeping the lowest id for each name. You can first identify duplicates:

SELECT
id,
name,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS row_number
FROM items
ORDER BY id;

Any row with row_number > 1 is a duplicate. You can then use a CTE and a DELETE statement to remove them:

DELETE FROM items
WHERE id IN (
WITH ranked_items AS (
SELECT
id,
name,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS row_number
FROM items
)
SELECT id
FROM ranked_items
WHERE row_number > 1
);

After running this, selecting from items will show only distinct names, with one representative row kept per value. This is a clean, declarative way to de-duplicate while controlling exactly which row you preserve.

ROW_NUMBER() vs RANK() vs DENSE_RANK() in pagination scenarios

PostgreSQL offers several ranking window functions: ROW_NUMBER(), RANK(), and DENSE_RANK(). While all of them assign ordered numbers, they behave differently when there are ties in the sorted columns.

The important differences are:

• ROW_NUMBER() always assigns a unique integer to each row, even when ties occur; numbers are strictly sequential (1, 2, 3, 4, …).
• RANK() gives the same rank to identical values but skips numbers after ties (for example, 1, 2, 2, 4 – rank 3 is missing).
• DENSE_RANK() also gives the same rank for ties but does not skip numbers (1, 2, 2, 3).

For pagination, ROW_NUMBER() is usually the safest choice because it guarantees exactly one row per number, which maps naturally to page ranges like 1-10, 11-20, etc. If you used RANK() or DENSE_RANK(), you could end up with pages that have fewer or more rows than expected due to ties.

On the other hand, for reporting use cases like competition results where tied values must share the same position, RANK() or DENSE_RANK() represent the intent better than ROW_NUMBER(). You can still paginate those results, but you need to be aware that “position” no longer directly corresponds to the physical row number.

Practical tips, pitfalls and performance considerations

When using ROW_NUMBER() for pagination and analytics, a handful of good practices will save you from subtle bugs and unnecessary performance headaches. Most of them boil down to being explicit and deterministic.

Always define a clear ORDER BY inside the OVER() clause. Without it, PostgreSQL is free to return rows in any order for the purposes of the window function, and your row numbers can change between executions even if the underlying data is identical.

Whenever possible, include a unique column (often the primary key) at the end of the ORDER BY list. This turns the ordering into a total order and avoids ambiguity with ties, which is critical for cursor-based pagination and for predictable top-N results.

Do not expect to use window functions directly in the WHERE clause of the same SELECT. Instead, wrap them in a CTE or subquery and filter on the derived column in the outer query. This pattern is simple, reusable and keeps your SQL readable.

When paginating, prefer ordering on indexed columns whenever practical. Both ORDER BY and ROW_NUMBER() rely on sorting or index scans; proper indexing can make the difference between milliseconds and seconds on large tables.

Be careful when combining PARTITION BY with pagination in some engines. In certain database products and versions, using partitioned window functions inside views or subqueries can disable otherwise available stopkey optimizations, causing the engine to process more rows than necessary. Testing with realistic data and reading query plans is essential.

For very large datasets and highly dynamic data, consider combining ROW_NUMBER() pagination for “admin-style” views with cursor-based keyset pagination for user-facing endpoints. That way you get both deterministic tooling queries and efficient, stable navigation in your API or UI.

Viewed as a whole, ROW_NUMBER() is not just a pagination trick: it’s a powerful analytical building block that lets you number, rank, slice, and clean up result sets in PostgreSQL and across other major SQL engines with the same underlying logic. Mastering it – together with a solid understanding of OVER(), PARTITION BY, and the differences from RANK() and DENSE_RANK() – gives you a very flexible toolkit for efficient pagination, top-N queries, and robust deduplication in real-world applications.