Skip to content

Instantly share code, notes, and snippets.

@hartleybrody
Last active December 5, 2023 17:36
Show Gist options
  • Save hartleybrody/6e30f27fcc505388ec47f1c5558f2f6c to your computer and use it in GitHub Desktop.
Save hartleybrody/6e30f27fcc505388ec47f1c5558f2f6c to your computer and use it in GitHub Desktop.
pagination

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:

@hartleybrody
Copy link
Author

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."

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment