How can I efficiently paginate large result sets using LIMIT and OFFSET in PostgreSQL while ensuring consistent ordering?

Answer

Use LIMIT and OFFSET together with a deterministic ORDER BY on a column (or set of columns) that is guaranteed to be unique, preferably backed by an index. This bounds the work to the requested page and prevents the server from returning an unpredictable subset.

Steps for this case

1. Identify a column or combination of columns that uniquely identifies each row (e.g., a primary key or a unique index).
2. Add an ORDER BY clause that sorts by those column(s) in a consistent direction (ASC or DESC).
3. Apply LIMIT page_size and OFFSET page_number * page_size.
4. Execute the query; the planner can use the index to fetch only the needed range, skipping the earlier rows efficiently.

Why this works

• According to the PostgreSQL documentation, "When using LIMIT, it is important to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows." [1]
• The same source notes that "The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient." [1] Using an indexed ORDER BY lets the planner stop scanning after the required offset, reducing the cost.
• The SELECT documentation confirms: "OFFSET says to skip that many rows before beginning to return rows" and "If both OFFSET and LIMIT appear, then OFFSET rows are skipped before starting to count the LIMIT rows that are returned." [2]

When keyset pagination may be better

If the table experiences frequent inserts/deletes that shift the positions of rows, keyset pagination (using WHERE column > last_seen_value AND ORDER BY column LIMIT n) avoids the cost of scanning skipped rows altogether. Consider it if you observe rising latency as page numbers grow.

Missing diagnostic detail

Do you have a unique (or primary key) index on the columns used in ORDER BY? If not, creating one changes the recommendation because without it the planner cannot guarantee stable ordering or efficient offset skipping.