Using Window Functions for Efficient Pagination in PostgreSQL

By John Doe January 14, 2025

Summary: in this article, you will learn how to paginate the results using window functions in PostgreSQL.

Table of Contents

Introduction

Window functions offer yet another way to implement pagination in SQL. This is a flexible, and above all, standards-compliant method. However, only PostgreSQL 15+ can use them for a pipelined top-N query.

Example

The following example uses the window function ROW_NUMBER for a pagination query:

SELECT *
  FROM ( SELECT sales.*
              , ROW_NUMBER() OVER (ORDER BY sale_date DESC
                                          , sale_id   DESC) rn
           FROM sales
       ) tmp
 WHERE rn between 11 and 20
 ORDER BY sale_date DESC, sale_id DESC

The ROW_NUMBER function enumerates the rows according to the sort order defined in the OVER clause. The outer WHERE clause uses this enumeration to limit the result to the second page (rows 11 through 20).

How it works…

The PostgreSQL database recognizes the abort condition and uses the index on SALE_DATE and SALE_ID to produce a pipelined top-N behavior:

                                                QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Subquery Scan on tmp  (cost=0.42..141724.98 rows=334751 width=249) (actual time=0.040..0.052 rows=10 loops=1)
 Filter: (tmp.rn >= 11)
 Rows Removed by Filter: 10
 Buffers: shared hit=5
 -> WindowAgg  (cost=0.42..129171.80 rows=1004254 width=249) (actual time=0.028..0.049 rows=20 loops=1)
    Run Condition: (row_number() OVER (?) <= 20)
    Buffers: shared hit=5
    -> Index Scan Backward using sl_dtid on sales  (cost=0.42..111597.36 rows=1004254 width=241) (actual time=0.018..0.025 rows=22 loops=1)
       Buffers: shared hit=5

Note: Since version 15 the execution plan shows the “Run Condition”, which may abort the downstream execution.

There is no sort operation and that the database aborts the execution when reaching the upper threshold. Considering that the aborted operations are executed in a pipelined manner, it means that this query is as efficient as the OFFSET method.

There’s more…

While this optimization could conceptually work with any monotonic function, there is a clear focus on the ROW_NUMBER function among the analyzed implementations.

Caution needs to be taken when using PARTITION BY: Even if the WHERE clause limits the rows to a single partition, the pure presence of PARTITION BY disables this optimization in other database products, like Oracle. This might happen if the partitioned window function is part of a view, but the outer query limits the view to a single partition.

The strength of window functions is not pagination, however, but analytical calculations. If you have never used window functions before, you should definitely spend a few hours studying the respective documentation.