How to best query for paginated infinite scroll

[cameronmcefee]

Hello! I'm curious if someone can guide me as to the best approach to querying data for infinite scrolling content. In theory the schema specifics aren't critical info here, and I'm not asking for code. I'm interested in the strategy, not the nuance. Let's just assume there's an ordered list of fairly simple records (but possibly thousands of them), and as you get to the bottom of the list you want to load N more items.

The most straightforward approach for me to reason about is that you're always fetching from 0 to a limit, where the limit is a variable you increase for each new query. Is SQLite fast enough that this is viable to a reasonable extent, or do the queries begin to slow down noticeably as the quantity increases?

Alternative 1: Have an offset and a limit, and you continually move the offset, but that gets into virtualizing the list which I would love to avoid.

Alternative 2: Or there's the idea of doing the queries in limited chunks and appending that to an ever growing array somewhere but that introduces a new thing to keep in sync.

And then there's the question of making it bi-directional which I assume might change the reasoning.

I don't have a CS background but I feel like this is the sort of thing that someone with a CS degree would be able to say "well obviously you start by trying X". Can anyone set me in the right direction?

Thanks for your guidance!

[lucamegh]

Fetch only the IDs in a single query, then retrieve the single element (or a batch of elements if you prefer) using the ID at a given index path. This approach is similar to how Core Data operates under the hood. Even with thousands of rows, memory usage remains minimal, especially if your IDs are integers. Additionally, this method provides a better user experience, as users can immediately have a sense of the size of the list by looking at the scroll bar's size.

[mbrandonw]

Hi @cameronmcefee, we don't have any tools built into the library for handling pagination.

I personally think you should start by just loading everything into memory and see how it goes. If it turns out there is no performance problem (make sure to run in release, not debug), then you've just saved yourself a lot of work.

Further, our tools make it very easy to load just the small bits of data the rows in your list needs. For example, if your table has many columns and some columns hold lots of data, but all you need is the title to display in the row, then you can simply do:

@FetchAll(Item.select(\.title)) var itemTitles

And if you need two or more columns from the table, then you can define a custom @Selection:

@Selection 
struct Row {
  let title: String 
  let isFlagged: Bool
}

@FetchAll( 
  Item.select {
    Row.Columns(title: $0.title, isFlagged: $0.isFlagged)
  }
)

This greatly reduces the amount of decoding that has to be done from SQLite and reduces memory usage.

[aehlke]

To expand on trying out loading into memory first - it can also sometimes work to load all data into memory, but still paginate how the UI loads that data into rows in your list or other such component.

[cameronmcefee]

Hmm, I'm trying to separate the part of me that struggles to believe it's possible from the part that desperately wants it to be true. However @aehlke's addition does reduce my skepticism a bit, as the arguments I initially want to make boil down more to UI performance than database performance.

Sorry, I'm new to SQLite and it's performance considerations, so I'm probably naive about where the bottlenecks are.

For example, imagine Photos.app. Thousands of images stored as data. I guess you do a big FetchAll and just get identifiers to be able to make the list. Then each thumbnail in the list does it's own fetch to get it's image data, which is throttled by whatever lazy view decides which thumbnails to actually render. However then as you scroll you're firing off tons of queries for each individual item. Is that not an issue? I would have thought having a few infrequent big queries would be better than tons of tiny queries, but again, I'm naive.

[aehlke]

You don't need to do a query per item. You can get all IDs, and then paginate in batches so that you only insert rows when near the end, which means fetching the next batch of items given an array or set of IDs and insert them as rows.

You should still be able to do pagination in the DB too, though it adds complexity. There should be a way to get the next N items beginning from a given ID but I haven't implemented that here / with SQLite to share my experience (used to do it with MySQL)

[mbrandonw]

For example, imagine Photos.app. Thousands of images stored as data. I guess you do a big FetchAll and just get identifiers to be able to make the list. Then each thumbnail in the list does it's own fetch to get it's image data, which is throttled by whatever lazy view decides which thumbnails to actually render. However then as you scroll you're firing off tons of queries for each individual item. Is that not an issue? I would have thought having a few infrequent big queries would be better than tons of tiny queries, but again, I'm naive.

If you store only the URL/identifier of the image in SQLite and the actual image is on the file system, then you would use something like AsyncImage or one of the popular 3rd party libraries for loading images into SwiftUI views. That way you keep a very small amount of data in memory (just IDs/URLs) and you only load images that are actually being displayed. Whatever library you decide to use for image loading should have enough smarts to unload any data not being displayed on screen.