As a designer, you might not know that there are actually two different ways to implement pagination: Cursor vs Offset.
The UI you design can influence which one your devs use or vice versa.
Here's how they work 🧵:
With offset pagination you provide the database with an amount of records to skip (an offset) and an amount to return.
The db will then traverse the table the specified number of times and return the requested amount of records
This behaviour makes it great at jumping to random points in a set of data but bad at dealing with data that changes often.
Deleting or adding a record changes which records are returned.
The ability to jump to a random point makes offset pagination great for paged UIs but this flexibility comes at a price.
The db traverses every row in the table - If you ask for row 2000 it needs to go through 2000 rows.
With cursor pagination you provide the database with the id of a record and a number of records before or after you'd like returned.
Because the db is looking for a particular item, it is not prone to the same issues with changing data and large data sets
This makes cursor pagination perfect for infinite lists. The last item in the current list is the cursor and you load x amount of records after it.
You don't have to worry about deletions and duplicates like you would with offset.
The bummer is cursor pagination needs ids to be unique and sequential because it's actually checking if the id is greater than the id of the cursor.
This means you can't just sort the table on any column. For instance "First Name" won't be unique or sequential
Recap:
Offset pagination - good for shallow, stable data sets that need to be traversed randomly. Works really well with a paged UI.
Cursor pagination - good for large sets of changing data. Works excellently with infinite scrolling UIs.