We switched to cursor-based pagination

Post by zaroth » Sun, Aug 21, 2022, 9:42 PM UTC

robertknight wrote: post #59

nemothekid wrote: post #48

Earlier quoted context omitted.

> I can get to an arbitrary page either through a path/ query param or at least close with a pagination row that contains a way to jump around I've had quite a few heated discussions on this point. The problem is, once your dataset gets large enough, this use case is incredibly difficult to scale; not impossible (Google does it with millions of search results), but prohibitively expensive compared to how often the ne…

> Google does it with millions of search results Google cheats, but in a way that very few users will notice. You can only access the first 20 pages of search results. Depending on user behavior, this is one way to offer navigation via page number while limiting worst-case cost.

Post by orf » Sun, Aug 21, 2022, 9:50 PM UTC

nextaccountic wrote: post #70

jlg23 wrote: post #54

Earlier quoted context omitted.

This post is about "database cursors" and "keyset pagination". In practice, these terms refer to the same thing, one seen bottom-up the other seen top-down. Implementation-wise, one saves the state of the cursor in the pagination [parameters] and resumes reading from the DB with an equivalent cursor.

> these terms refer to the same thing No, cursor is this https://en.wikipedia.org/wiki/Cursor_(databases) https://www.postgresql.org/docs/current/plpgsql-cursors.html I once did pagination using database cursors, which is something different than keyset pagination: The server would keep a cursor open and keep fetching more data from the same query. This enabled the system to have an interface similar to offset pagina…

Post by chowells » Sun, Aug 21, 2022, 10:03 PM UTC

G3rn0ti wrote: post #35

theogravity wrote: post #7

Earlier quoted context omitted.

It needs to talk about how to actually implement it. Most articles like this one mention its existence and why it's good, but generally stop there.

Postgres supports cursors and documents them very well: https://www.postgresql.org/docs/current/plpgsql-cursors.html Basically, you declare a cursor like that: DECLARE cname CURSOR FOR ; You can pass the cursor’s name „cname“ (and even store it on the client side, although, best encrypted) and obtain the „next“ slice of your data corresponding to the query on demand like that: FETCH next cname; Not sure you really ga…

Post by kibwen » Sun, Aug 21, 2022, 10:24 PM UTC

simonw wrote: post #52

hamandcheese wrote: post #31

Cursor based pagination doesn’t actually solve the described pitfalls if your results are ordered by anything mutable, though. A result you haven’t yet seen can be mutated to sort before your current cursor, likewise a result that you’ve already seen can be mutated to sort after the current cursor, causing you to see it twice. Cursor based pagination does minimize the issue somewhat, because only the mutated rows are…

The only approach I can think of which might be able to handle mutability of the rows used for sorting would be to support paginating through a snapshot: provide a mechanism whereby a full snapshot of the query at a specific point in time is captured such that the user can then paginate through that snapshot. Expensive to implement, so this would only work for situations where you can afford to spend significant stor…

Post by debrice » Sun, Aug 21, 2022, 10:33 PM UTC