WingNews logo WingNews
top | item 32542747

(no title)

feike | 3 years ago

Reminds me of Markus Winand who hands out stickers on database conferences banning offset.

His site is a great resource for anyone wanting to take a deeper dive on SQL performance:

https://use-the-index-luke.com/sql/partial-results/fetch-nex...

discuss

order

ReactiveJelly|3 years ago

So basically do it like Reddit?

https://old.reddit.com/?count=25&after=t3_wtpvdp

I noticed Reddit's pagination has that "after" parameter, which points to the last post on the current page.

It glitches out if the last item is deleted by moderators, but otherwise it works smoothly.

djbusby|3 years ago

On Reddit I frequently see the "next" page having the same posts as the previous page. Not all the same but many of the same. Like, maybe after is being respected but the sorting is different or something.

croes|3 years ago

Can't he just use rowversion?

No need for row value syntax and it works with MS SQL Server

dinkledunk|3 years ago

how to jump to an arbitrary page?

nextaccountic|3 years ago

You can do that with postgres histograms https://www.citusdata.com/blog/2016/03/30/five-ways-to-pagin... - go to the section "Keyset with Estimated Bookmarks"

> As we saw, plain keyset pagination offers no facility to jump a certain percentage into the results except through client guesswork. However the PostgreSQL statistics collector maintains per-column histograms of value distribution. We can use these estimates in conjunction with limits and small offsets to get fast random-access pagination through a hybrid approach.

squeaky-clean|3 years ago

Jumping to a specific page is a bit of an ambiguous / undefined term in this case. Like asking for a specific page in a book that's still being written. Maybe today the plot twist occurred on page 100, but then the author decides chapter 1 needs more backstory, and now the plot twist happens on page 115.

Unless you can guarantee your data is static, or that the sorting order cannot be mutated and only append later values, the concept of what data belongs in which page could be changing every millisecond.

jsmith99|3 years ago

Do you really need to jump to an arbitrary page and land on the exact item? For many applications an approximate jump is fine. If your column is fairly uniformly distributed you can guess the index for any arbitrary page.

MatmaRex|3 years ago

You don't, but instead you can jump to an arbitrary place in the results. For example, you could show results starting from the letter P, or show results starting from 2022-04-02.

hnuser847|3 years ago

Spoiler: you can’t.