PostgreSQL Keyset Pagination Query Generator

OFFSET pagination gets slower with every page, because PostgreSQL still reads and discards all the skipped rows. Keyset pagination (the seek method) remembers the sort key values of the last row and asks for the rows after them, so page 10,000 costs about the same as page 1. Getting the WHERE clause right is the tricky part. Ties need a unique tiebreaker, mixed ASC/DESC orders cannot use a simple row comparison, and nullable columns need NULLS FIRST/LAST-aware conditions. Enter your table, sort keys and page size, and this tool writes the first-page, next-page and previous-page queries, the parameter mapping, and a B-tree index whose order matches. The predicates were checked against plain ORDER BY results on PostgreSQL 14 and 18 with random data that includes duplicates and NULLs. Everything runs in your browser.

TableSELECTFilter (optional)

Sort keys (ORDER BY, in order)

nullable

Last key is unique (primary key or unique column)

Page sizeParameters

All keys share one direction and none is nullable, so a row-value comparison (a, b) > (x, y) works. PostgreSQL turns it into a single index range scan.

First page

SELECT id, customer_id, created_at, total
FROM public.orders
WHERE customer_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20;

SELECT id, customer_id, created_at, total
FROM public.orders
WHERE customer_id = 42
  AND (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Parameters

$1 = value of created_at from the LAST row of the previous page (prev-page query: FIRST row of the current page)
$2 = value of id from the LAST row of the previous page (prev-page query: FIRST row of the current page)

-- Previous page: p = values from the FIRST row of the current page
SELECT *
FROM (
  SELECT id, customer_id, created_at, total
  FROM public.orders
  WHERE customer_id = 42
    AND (created_at, id) > ($1, $2)
  ORDER BY created_at ASC, id ASC
  LIMIT 20
) AS page
ORDER BY page.created_at DESC, page.id DESC;

Supporting index

-- One B-tree index: equality-filtered columns first, then the sort keys in ORDER BY order and direction.
-- PostgreSQL can also scan it backwards, so the previous-page query uses the same index.
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_created_at_id_keyset_idx
  ON public.orders (customer_id, created_at DESC, id DESC);

Background reading: Keyset Pagination in Postgres vs OFFSET.

Do more than postgresql keyset pagination query generator — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Enter the table, the columns you return and an optional filter, then list the sort keys in order with ASC/DESC. End with a unique column such as id.
Mark nullable keys and pick NULLS FIRST or LAST; choose $1-style or :named parameters and the page size.
Create the suggested index, run the first-page query, then pass the last row's key values into the next-page query (or the first row's into the previous-page query) from your app, psql or Chat2DB.

Frequently asked questions

What is keyset pagination and why is it faster than OFFSET?

With LIMIT 20 OFFSET 100000, PostgreSQL must produce and throw away 100,000 rows before returning 20, so deep pages get slower and slower. Keyset pagination stores the sort values of the last row you showed (for example created_at and id) and asks for WHERE (created_at, id) < (last_created_at, last_id) ORDER BY created_at DESC, id DESC LIMIT 20. With a matching B-tree index, PostgreSQL jumps straight to that position, so every page costs about the same. Rows inserted or deleted while the user is paging also do not shift later pages, which happens with OFFSET.

Why can't I use a row comparison like (a, b) > (x, y) when the sort directions differ?

A row-value comparison compares column by column in one direction. (a, b) > (x, y) means a > x, or a = x and b > y. That matches ORDER BY a ASC, b ASC (and its reverse), but not ORDER BY a ASC, b DESC, where the second column must use <. For mixed directions the generator expands the condition into OR branches: a > x, or a = x and b < y. Nullable columns need extra branches as well, because comparisons with NULL return NULL. If the first key is NOT NULL, the generator also adds a redundant a >= x condition so the index scan can start at the cursor.

Do I need a unique column at the end of the ORDER BY for keyset pagination?

Yes. If the sort keys are not unique, for example many rows share the same created_at, the rows with the cursor's value either get skipped or repeat on the next page, depending on the comparison. Adding the primary key as the final key gives every row a unique position, so each row appears on exactly one page. Put the same columns, in the same order and directions, into one B-tree index. If you want to inspect the plans, Chat2DB (https://chat2db.ai/download or https://app.chat2db.ai) can run the generated queries with EXPLAIN against your own database.