Pagination is a standard requirement for database applications, but performance often degrades when dealing with complex queries or massive result sets. While basic pagination relies on LIMIT and OFFSET, this approach forces the database to scan and discard rows, leading to high latency on deep pages.

Problems with Standard Offset Pagination

The typical method involves skipping a set number of rows before returning the next batch. In PostgreSQL, this looks like the following:

SELECT * 
FROM products 
ORDER BY created_at 
OFFSET 20 ROWS 
FETCH NEXT 10 ROWS ONLY;

As the OFFSET value grows, PostgreSQL must still read and count those rows internally before returning the requested slice. This results in linear performance degradation relative to the page depth.

Leveraging Index-Only Scans

To mitigate the overhead of skippign rows, ensure the ORDER BY and WHERE clauses are supported by a proper index. If the database can walk the index, it avoids sorting the entire result set.

For a table named orders with columns order_id, customer_id, and status, creating a composite index helps specific filtering queries:

CREATE INDEX idx_orders_status_id ON orders (status, order_id);

Keyset Pagination (The Seek Method)

A superior alternative to offset-based pagination is keyset pagination. Instead of counting rows to skip, you filter the result set based on the last seen value.

If you retrieved the first page of active orders, you would remember the last order_id (e.g., 1024) and query the next page like this:

SELECT * 
FROM orders 
WHERE status = 'active' AND order_id > 1024 
ORDER BY order_id 
LIMIT 10;

This technique allows PostgreSQL to jump directly to the relevant position in the index without scanning the rows that came before it.

Matreializing Complex Subqueries

When dealing with complex aggregations or joins that are expensive to compute repeatedly, consider using a MATERIALIZED VIEW or a CTE (Common Table Expression) with a temporary storage strategy, though keep in mind that materialized views require refreshing.

CREATE MATERIALIZED VIEW recent_high_value_orders AS 
SELECT o.order_id, c.name, o.total_amount 
FROM orders o 
JOIN customers c ON o.customer_id = c.customer_id 
WHERE o.total_amount > 500 
ORDER BY o.order_id;

-- Now paginate the view efficiently
SELECT * FROM recent_high_value_orders 
WHERE order_id > 550 
LIMIT 10;