Pagination
Choosing between offset and keyset pagination
You've now learned two ways to paginate query results: offset pagination and keyset pagination.
Neither approach is always the right choice. The better option mainly depends on how users need to navigate the results and how large the result can become.
When to use offset pagination
Offset pagination is a good fit when users need numbered pages or need to jump directly to a specific page.
For example:
12345SELECT id, payment_referenceFROM ordersORDER BY idLIMIT 100OFFSET 5000;The OFFSET tells PostgreSQL how many rows to skip before returning the next 100.
Offset pagination is often a reasonable choice when:
the result set is small enough that large offsets aren't a performance concern;
the interface needs numbered pages;
users need to jump directly to arbitrary page numbers.
Its main drawback is that larger offsets require PostgreSQL to process more preceding rows.
When to use keyset pagination
Keyset pagination is a good fit when users move through results sequentially and don't need to jump directly to arbitrary page numbers.
For example:
12345SELECT id, payment_referenceFROM ordersWHERE id > 5000ORDER BY idLIMIT 100;Instead of counting how many rows to skip, keyset pagination uses values from a known row boundary to determine where the next page should begin.
Keyset pagination is often a good choice when:
the result can contain a large number of rows;
navigation proceeds from a known current position to the rows before or after it;
you want to avoid the increasing work caused by large offsets;
direct jumps to arbitrary page numbers aren't required, for example in load-more interfaces.
The main trade-off is that retrieving a page requires knowing a cursor that identifies the relevant position in the ordered result.