Solutions for pagination

[binarykitchen]

Hi ya,

Wondering if you support pagination for the iterator API?

This to support pagination on UI side while endless-scrolling tables.

Thanks for suggestions.

Cheers,
Michael

[vweevers]

Short answer: no. Using SQL terminology, abstract-level has an equivalent to the SQL LIMIT clause, namely the limit option and/or iterator.nextv(). But there's no equivalent to the SQL OFFSET clause.

There's no operation that can skip N amount of keys. You could manually skip keys by repeatedly calling next() or nextv() but that's expensive. You can seek to a key, but determining that key either requires an index (built by you) or for your keys to be numeric and sequential.

It's an interesting question, and we could perhaps implement new primitives to help build a solution. For example, we could make skipping keys faster by doing it natively (instead of visiting each key in JavaScript land). But ultimately, a userland solution tailored to your use case is probably going to give the best performance. I wonder how relational databases implement OFFSET.

If your application only has a single user (at a time) then you could just keep an iterator alive and iterate it lazily as requests from the UI come in, but whether that's an option is hard to say without knowing more about your application.

[binarykitchen]

Likewise, you raised interesting points as well.

If your application only has a single user (at a time) then you could just keep an iterator alive and iterate it lazily as requests from the UI come in, but whether that's an option is hard to say without knowing more about your application.

Well, no, it's a large web app, perhaps getting thousands of users in the next months ... 😓
Don't want to keep iterators open for them in real-time. Also, a URL should come with an offset and limit, so the resource can be referenced to anytime, the immutable way.

Has anyone ever tried to "fake" SQL-like pagination statements in Level DB before?

[vweevers]

Have you considered cursors? Where the input is not an offset but instead the last key visited. You'd do db.iterator({ gt: cursor, limit: 10 }) and then use the last key as your next cursor.

[binarykitchen]

Ah, cursors. I missed them out in your documentation. Thanks.
When I searched for "pagination" in your documentation, this didn't come up.

Perhaps you want to edit the "cursor" section so that they might help the classic SQL folks searching for pagination? ;)

[binarykitchen]

On a side note:

Pretend I have 50 concurrent, registered users accessing their inbox email table at the same time.
They all scroll multiple times forth and back within the next second:

• Then, at the same time, I'd have to validate all their user sessions,
• Refresh the cursor for each of them, and to deliver the next 10 rows for each of them individually,
• Which will require more servers and software engineers.

I wonder whether Level DB, classic SQL, or even Duck DB can tackle this technical challenge better.

[vweevers]

You'd have the same load issue with SQL, and the answer is probably caching.

[binarykitchen]

Aye, this:

Have you @vweevers been through this before?

[vweevers]

Perhaps you want to edit the "cursor" section so that they might help the classic SQL folks searching for pagination?

It's a broader concept rather than an abstract-level feature, so I thought it best to add something to the FAQ: Level/community#149

[binarykitchen]

Awesome. I'll respond tomorrow.
Here in New Zealand, it's 1am now 😅