3 ms·

Are these actually server-side cursors or keyset pagination? For keyset pagination you need essentially the last value of the columns you used to sort by, but t

Are these actually server-side cursors or keyset pagination? For keyset pagination you need essentially the last value of the columns you used to sort by, but this seems to have some random or encoded cursor value. I didn't think server-side cursors would be used at that scale so this has to be keyset pagination, so I think I'm missing something. And while I really like keyset pagination, it is annoying to implement in some cases. If your sorting column is not unique you need to sort by multiple columns. And that is really annoying and less efficient if you don't have row value comparisons. And not all databases and especially not all ORMs support those. I'm still waiting on EF Core support here, it looks a bit like this might happen but it's not certain yet.

gruez 4y ago
>but this seems to have some random or encoded cursor value Maybe the actual key value is stored server side for obfuscation/optimization reasons?
xdfgh1112 4y ago
Surely a keyset, even if it's multi-key, is more performant than an offset query. Offset queries usually require the previous rows to be computed first, so large offsets are super slow.
- Jweb_Guru 4y ago
  It is, but it's not a cursor in the traditional sense.
oconnor663 4y ago
By server-side cursors do you mean like open connections to MySQL reading a stream of query results, or something like that? I don't think that's feasible for most websites. The biggest issue is that you'd have to guarantee that when someone clicks "next page", their browser ends up talking to the exact same machine that it got the previous page from. But websites running on big fleets of machines don't usually work like that; instead your queries are usually somewhat randomly distributed across a whole tier. You might work around that by trying to keep a persisitent connection open from the browser to the server, but you'd lose that connection when you refreshed the page or when the server restarted, or if you lost WiFi for a minute. Making hiccups like that visible to the user is pretty frustrating. It's a lot more robust to use some sort of cursor design that doesn't rely on any persistent connections. Some other problems that crop up: - A lot of websites expect to serve most results from cache, and to only hit the DB a minority of the time, but to get your hands on a real DB cursor you'd have to hit the DB every time. - Not all DB's support moving cursors backwards. Maybe most of them don't? I'm not sure. - Open DB connections are usually a somewhat limited resource.
ko27 4y ago
EF core now supports it with PostgreSQL https://github.com/npgsql/efcore.pg/pull/2350 https://github.com/npgsql/efcore.pg/pull/2350

gruez 4y ago

>but this seems to have some random or encoded cursor value Maybe the actual key value is stored server side for obfuscation/optimization reasons?

xdfgh1112 4y ago

Surely a keyset, even if it's multi-key, is more performant than an offset query. Offset queries usually require the previous rows to be computed first, so large offsets are super slow.

Jweb_Guru 4y ago
It is, but it's not a cursor in the traditional sense.

oconnor663 4y ago

By server-side cursors do you mean like open connections to MySQL reading a stream of query results, or something like that? I don't think that's feasible for most websites. The biggest issue is that you'd have to guarantee that when someone clicks "next page", their browser ends up talking to the exact same machine that it got the previous page from. But websites running on big fleets of machines don't usually work like that; instead your queries are usually somewhat randomly distributed across a whole tier. You might work around that by trying to keep a persisitent connection open from the browser to the server, but you'd lose that connection when you refreshed the page or when the server restarted, or if you lost WiFi for a minute. Making hiccups like that visible to the user is pretty frustrating. It's a lot more robust to use some sort of cursor design that doesn't rely on any persistent connections. Some other problems that crop up: - A lot of websites expect to serve most results from cache, and to only hit the DB a minority of the time, but to get your hands on a real DB cursor you'd have to hit the DB every time. - Not all DB's support moving cursors backwards. Maybe most of them don't? I'm not sure. - Open DB connections are usually a somewhat limited resource.

ko27 4y ago

EF core now supports it with PostgreSQL https://github.com/npgsql/efcore.pg/pull/2350 https://github.com/npgsql/efcore.pg/pull/2350