I compared OFFSET-based pagination with keyset (cursor) pagination in PostgreSQL using EXPLAIN ANALYZE.

The goal was to understand why OFFSET pagination is considered bad, even though it doesn’t look slow on small datasets.

Tests were run on https://neon.com/postgresql/postgresql-getting-started/postgresql-sample-database dvdrental database rental table (has index on rental_id).

OFFSET Query

EXPLAIN ANALYZE
SELECT *
FROM rental
ORDER BY rental_id
OFFSET 10000
LIMIT 10;

• It scanned 10010 rows.
• Discarded first 10000 rows.
• Returned 10 rows.
• Execution time: 2.9 ms.
• actual rows: 10010

Keyset (Cursor) Query

EXPLAIN ANALYZE
SELECT *
FROM rental
WHERE rental_id >= 10000
ORDER BY rental_id
LIMIT 10;

• Jumps directly to rental_id = 10000 using index.
• Scans only 10 rows.
• Returns 10 rows.
• Execution time: 0.15 ms
• actual rows: 10