pagination

notes.md

Pagination Details

Everyone loves to say "you're probably doing pagination wrong" -- in terms of writing efficient SQL queries.

Here is the tl;dr:

• make sure you are using ORDER BY in your query, otherwise subsequent queries with a LIMIT and OFFSET could either skip rows or duplicate rows across pages
• when going to next page, don't use LIMIT .. OFFSET since the database still has to fetch all rows, then order them, then move to the correct page
• instead using something like WHERE id > {max_id_from_prev_page} and (assuming there's an index on that column) the database can jump directly to the correct spot to read just the number of rows you're displaying on the next page
• this helps to keep the query constant time as the size of the data grows and/or as you page deeper into the data

read more:

• https://medium.com/swlh/sql-pagination-you-are-probably-doing-it-wrong-d0f2719cc166
• https://use-the-index-luke.com/no-offset

Peeling back the curtain a bit on "pagination tokens" and how they're usually just hashed wrappers around whatever the indexed key of the next row past the current response would be.

Makes it easier for the client to request the next page, instead of having to sort the current sent, get the max ID value and then append that as a "get the next row greater than this one."