PostgreSQL Pagination Performance Guide

Why Pagination Can Become Slow

Using OFFSET with large values forces PostgreSQL to scan and skip many rows, which reduces performance significantly.

OFFSET Example

SELECT * FROM users
ORDER BY id
LIMIT 20 OFFSET 10000;

This query scans the first 10,020 rows before returning results.

Keyset Pagination (Recommended)

SELECT * FROM users
WHERE id > 10000
ORDER BY id
LIMIT 20;

Keyset pagination uses indexed columns and scales much better.

Best Practice

For high-traffic applications, avoid large OFFSET values and use keyset-based pagination.