Re:Architecture Notes: Datasette
by simonw on Friday May 27, 2022 @01:43PM
(#31532464)
I want this too!
Datasette avoids offset/limit pagination because it performs poorly on huge queries - and I don't want random visitors (and crawlers) hurting performance of a public instance by crawling through offset/limit of thousands of pages.
That's why table pages implement keyset pagination instead - so you can do https://congress-legislators.datasettes.com/legislators/legi... and get back records following the one with A000106, which is a fast query because the ID column has an index on it.
Supporting this with arbitrary queries is harder. One idea I had is to allow the user to specify which column and sort order should be used for keyset pagination - so you could construct a URL like this:
If a pagination column has been specified, Datasette would use the same trick it uses on regular table pages and add next links that way.
Would that work for you?
The other, probably easier option is a setting that enables offset/limit pagination of arbitrary SQL queries - turned off by default, but easy to turn on for users who are running Datasette on a private server. If that takes several seconds people can at least opt into it.
Re:Architecture Notes: Datasette
by samwillis on Friday May 27, 2022 @02:52PM
(#31533373)
Either would work, but as you said the latter is easer to implement and would have done the job for what I was working on. If I had had more time I would have looking into trying to do it myself, but there is never enough time...
Datasette avoids offset/limit pagination because it performs poorly on huge queries - and I don't want random visitors (and crawlers) hurting performance of a public instance by crawling through offset/limit of thousands of pages.
That's why table pages implement keyset pagination instead - so you can do https://congress-legislators.datasettes.com/legislators/legi... and get back records following the one with A000106, which is a fast query because the ID column has an index on it.
Supporting this with arbitrary queries is harder. One idea I had is to allow the user to specify which column and sort order should be used for keyset pagination - so you could construct a URL like this:
If a pagination column has been specified, Datasette would use the same trick it uses on regular table pages and add next links that way.Would that work for you?
The other, probably easier option is a setting that enables offset/limit pagination of arbitrary SQL queries - turned off by default, but easy to turn on for users who are running Datasette on a private server. If that takes several seconds people can at least opt into it.