Pagination

Postgres WHERE (A, B) > (X, Y)

(A > X) OR ((A = X) AND (B > Y))

Drawbacks of Cursor Pagination

- Client cannot skip pages, need to traverse through each page one by one. - Clients need to manage cursors in query_params

Advantages of Offset Pagination

- easy to implement on both FE and BE side - you can jump (skip) to arbitrary pages

How to work with Cursor Pagination

1. Client sends a request specifying a limit: GET api?limit=50 2. Server responds with results and next-cursor id: 12345 3. In all subsequent requests client sends next-cursor: GET api?limit=50&next-cursor=12345

Cursor Pagination

A cursor is a unique identifier for a specific record, which acts as a pointer to the next record we want to start querying from to get the next page of results. By using a cursor, we remove the need to read rows that we have already seen by using a WHERE clause in our query (making it faster to read data as it is constant i.e. O(1) time complexity) and we address the issue of inaccurate results by always reading after a specific row rather than relying on the position of records to remain the same.

When to Use: Limit-offset

Applications with restricted pagination depth and tolerant of result inconsistencies. In some applications users don't typically advance many pages into a resultset, and you might even choose to enforce a server page limit. If result inconsistency and restricted page numbers aren't a problem in your application then limit-offset may be convenient for your needs.

Downsides of Offset Pagination

Search gets slower as the number of records increases because the database still has to read up to the offset number of rows to know where it should start selecting data. This is often described as O(n) complexity, meaning it is generally the worst-case scenario. E.g. google does not show results of 21+ pages, and deep pagination with offset does not perform well. Inefficient for large dataset.

Offset Pagination

When retrieving data with offset pagination, you would typically allow clients to supply two additional parameters in their query: an offset, and a limit. An offset is simply the number of records you wish to skip before selecting records.

Why column should be unique in Cursor-based pagination

e.g. you have 3 values: 1. Aberforth Dumbledore 2. Albus Dumbledore 3. Dudley Dursley So, in a query WHERE last_name > "Dumbledore", Albus might be skipped. The solution is to set a secondary sort column on a unique value, like id, and then remembering both the last value and last id. WHERE (last_name > "Dumbledore" OR (last_name = "Dumbledore" AND id > 2)) To ensure the query is performant as the number of records increases you'd need a database index set up on title and id.

Keyset pagination example

first page - SELECT * FROM medley ORDER BY n ASC LIMIT 5; second page - SELECT * FROM medley WHERE n > 5 ORDER BY n ASC LIMIT 5;

Complexity of Cursor Pagination

By using a cursor, we remove the need to read rows that we have already seen by using a WHERE clause in our query making it faster to read data as it is constant i.e. O(1) time complexity.

Which pagination we should use to implement infinite scroll?

Cursor pagination is the preferred backend method for delivering an infinite scroll on the frontend.

Requirement for Cursor Pagination

Cursor-based pagination requires a unique, unchanging ordering of items in the result set. This ordering might typically be a creation timestamp on the records, as this presents a consistent ordering to paginate against. It should be a constant value, such as a timestamp, slug, or other fields that are only set once, on creation like id.

Why there's might be duplicates in Offset Pagination?

Here's how limit-offset pagination can be inconsistent: Suppose a user moves from page n to n+1 while simultaneously a new element is inserted into page n. This will cause both a duplication (the previously-final element of page n is pushed into page n+1) and an omission (the new element). Alternatively consider an element removed from page n just as the user moves to page n+1. The previously initial element of page n+1 will be shifted to page n and be omitted.

How does Offset Pagination work for a dataset of 5000 items? e.g. GET api?offset=1000 e.g. GET api?offset=2000

In the first query the database would find 1000 items and return, in the second one the database would also read 1000 first items, then move to another 1000 items and skip the first 1000 items. O(n) complexity

Keyset pagination

Keyset pagination (also known as the "seek method") is used to fetch a subset of records from a table quickly. It does this by restricting the set of records returned with a combination of WHERE and LIMIT clauses. To get the next page, you check the value of the column in the WHERE clause against the last row returned in the previous page of results.

Trade-offs for Keyset Pagination

Keyset pagination comes with a couple of trade-offs. - Without the offset, you don't know exactly how many pages there are in the table or which page number you are currently on. - Additionally, keyset pagination requires a sortable field on your model. Sequential IDs and date fields work well.

What's the best pagination approach for high-traffic web servers?

Since users typically access pages of information in a linear fashion, keyset pagination is usually considered the best choice for paginating ordered records in high-traffic web servers. Like many engineering decisions, choosing pagination techniques involves tradeoffs. It's safe to say that keyset pagination is most applicable for the average site with ordered linear access. However even limit-offset has its strengths, and more exotic techniques provide special performance characteristics for certain kinds of data. You can see there quite a few possibilities. Pick the right tool for the job and don't let pagination be a closed book.

Best use keys for Keyset pagination on UI

The Seek Method perfect for "Infinite Scrolling" and "Next-Prev" (only this button) navigations.

Workaround for using only unique values for sorting

The solution is to set a secondary sort column on a unique value, like id, and then remembering both the last value and last id. WHERE (last_name > "Dumbledore" OR (last_name = "Dumbledore" AND id > 2)) To ensure the query is performant as the number of records increases you'd need a database index set up on title and id.

Why we might encode cursor value?

This allows to technically implement different underlying pagination schemes per endpoint, while giving a consistent interface to consumers of the API. e.g. you could have 2 endpoints, where 1 uses cursor-based pagination and second one - offset based. Одним из преимуществ использования абстрактного курсора вместо конкретных монотонных полей является возможность сменить нижележащую технологию (например, перейти от использования последнего известного идентификатора к использованию даты последней известной записи) без слома обратной совместимости.

Can we use Cursor Pagination for sorting a user table that can sort on first name and last name?

This could pose a problem for cursors because one requirement for the implementation is a unique sequential column (or columns) to sort by. Remember, with cursors, you have to be able to point to a specific place in the data set and say, I want records after this one record. With this in mind, it makes sense that most cursor implementations are based on the timestamp column. Because it's certainly sequential and if you track time to a granular enough level, one can reasonably assume the column is unique.

Why Offset Pagination is inefficient for large datasets?

This is because the database needs to fetch all of the previous rows, even though it discards them.

Как ускорить Offset Based Pagination?

Создать индекс.