How to use ValueObservation.tracking properly for paginated query with offset and limit?

[hyouuu]

Can't finds docs or examples about this use case so asking here - if I do:

        let observation = ValueObservation.tracking { db in
            try Note.all()
                .limit(100, offset: 0)
                .fetchAll(db)
        }

Does it only observe 0-100 rows? If I lazily increase my load to add 101-200, 201-300, how to properly observe them as well?

Additionally, when I observe 0-100 and insert 1 row at 0, does that mean the observed value becomes the new row + original 0-99 and the old 100th is ignored?

Thanks!

[groue]

Hello @hyouuu,

How to use ValueObservation.tracking properly for paginated query with offset and limit?

You can't use ValueObservation.tracking improperly. 😉

---

The doc does not indeed provide a self-contained description of "what is observed, exactly?".

There is a beginning of a description in ValueObservation Performance:

ValueObservation is triggered by database transactions that may modify the tracked value.

Precisely speaking, ValueObservation tracks changes in a DatabaseRegion, not changes in values.

For example, if you track the maximum score of players, all transactions that impact the score column of the player database table (any update, insertion, or deletion) trigger the observation, even if the maximum score itself is not changed.

The DatabaseRegion documentation further completes the picture:

A DatabaseRegion is the union of any number of “table regions”, which can cover a full table, or the combination of columns and rows identified by their rowids:

|Table1 |   |Table2 |   |Table3 |   |Table4 |   |Table5 |
|-------|   |-------|   |-------|   |-------|   |-------|
|x|x|x|x|   |x| | | |   |x|x|x|x|   |x|x| |x|   | | | | |
|x|x|x|x|   |x| | | |   | | | | |   | | | | |   | |x| | |
|x|x|x|x|   |x| | | |   | | | | |   |x|x| |x|   | | | | |
|x|x|x|x|   |x| | | |   | | | | |   | | | | |   | | | | |

I understand that it is not trivial to group those descriptions in a single idea. Also, there is this "rowid" word that is not known by everyone (SQLite doc: Rowid Tables).

---

Key to the understanding is the fact that GRDB does not observe. Instead, SQLite notifies changes.

To understand how SQLite is the only major player in the picture, consider that database observation works just as well for people who write raw SQL by hand. GRDB plays no role here, because GRDB does not parse SQL (it is hard, and the SQL grammar evolves with SQLite versions, meaning that parsing SQL creates a maintenance burden - it's not in anyone's interest for GRDB to start parsing SQL):

// Observation setup: raw SQL is just fine.
let notesObservation = ValueObservation.tracking { db in
    try Note.fetchAll(db, sql: "SELECT * FROM notes LIMIT 100")
}

// Some changes that trigger the observation. Again, raw SQL is welcome.
try dbQueue.write { db in
    try db.execute(sql: """
        -- May impact the 100 first notes
        INSERT INTO note VALUES (...);

        -- May impact the 100 first notes
        UPDATE note SET ... = ... WHERE ...;

        -- May impact the 100 first notes
        DELETE FROM note WHERE ...;

        -- May impact the 100 first notes, if there is a foreign key
        -- from noteOwner to note that deletes notes when their
        -- owner is deleted.
        DELETE FROM noteOwner WHERE ...;
        """)
}

Observation is thus entirely driven by SQLite Data Change Notification Callbacks, which notify individual insertions, updates, and deletions, and are totally unaware that your app is interested in the first 100 notes (or the maximum player score, or whatever).

GRDB does something, of course. Is is akin to "since the application performs an observation, I must trigger a refresh whenever SQLite notifies a change that may have an impact on the fetched values, in order to make sure that 1. the app never misses a change, 2. people keep on trusting the library, and 3. the GRDB GitHub repository is not flooded with bug reports":

• GRDB queries SQLite about the region that is read by Note.all().limit(100). SQLite says that it is "all columns in unspecified rows of the database table for notes". The limit plays no role. The exact list of 100 notes plays no role. In the end, the observed region is the full database table for notes.

• GRDB listens to raw change notifications, and triggers observations that observe a region impacted by the notified changes.

To understand why the limit is ignored, why we don't care about the exact list of 100 notes, consider that this information is not useful:

• When SQLite notifies an insertion, it's not possible to know whether it will be included in the 100 first notes or not, without fetching. We don't know anything about row ordering, and the change notification only provides a rowid, not the inserted data. Well, since we need to fetch anyway, we can just trigger the observation.
• When SQLite notifies an update, it's not possible to know whether it will enter, leave, or change position in the list of the 100 first notes, without fetching. We don't know anything about row ordering, and the change notification only provides a rowid, not the updated data. Again, since fetching is required, let's just trigger the observation.
• When SQLite notifies a deletion, we again need to fetch. Now it is more subtle: maybe the request is based on a condition computed from the whole list of notes (we don't know because we do not parse SQL), which means that even if a row that is absent from the list, it may have an impact on the list when deleted. In the end, fetching is the safest bet, so the observation is triggered and that's it.

Does it only observe 0-100 rows?

Since the observed region is the full database table, it considers that any inserted, updated, or deleted note must trigger a refresh.

Changes in notes that are beyond the first 100 will trigger a refresh (and notify the same previous list of 100 notes). As the ValueObservation documentation says: "ValueObservation may notify consecutive identical values."

Additionally, when I observe 0-100 and insert 1 row at 0, does that mean the observed value becomes the new row + original 0-99 and the old 100th is ignored?

The observed region does not change: it remains the full list of notes.

[hyouuu]

Thanks for the thorough explanation @groue ! A quick follow up: Although any change in the table triggers the observation callback, the callback only returns the first 100 notes (if I understand correctly). For my use case where I increasingly query for 0-100, 101-200, 201-300, do I need to keep 3 observations in order to get the changes properly? Or should I change it so that initially it queries for 0-100, then increase it to 0-200 and cancel the original observation?

[groue]

I suppose you have no use for 100 notes as soon as you start displaying 200. It's probably better to stop the previous observation and start a new one when the number of notes increases. Generally speaking, try to keep the number of active observations low, and don't keep observing values that your app no longer cares about.

[hyouuu]

Makes sense! The last question is about performance - the reason to query in pagination is to keep each query light and fast, but the observation does an initial value return immediately from the doc, so that when I'm getting 1001-1100 and change the observation to 0-1100, does it need to fetch 0-1100 initially? Any way to avoid it? Or shouldn't I worry about the performance here at all?

[groue]

I assume you are implementing a list that extends as the user scrolls down.

No one has built a high-level API on top of GRDB that would lazily fetch pages of values as the user is scrolling. That's an interesting topic, but it happens nobody has started working on it - not me, not any user I know.

You're thus in uncharted territory.

Your tools are the Xcode profiler (Instruments), and the various GRDB debugging tools, such as ValueObservation.print(_:to:) or Tracing SQL Statements.

Don't forget the performance of the GUI framework as well (UIKit UITableView, or SwiftUI List, the latter being known for its difficult handling of long lists).

If you feel courageous, and if you know what "data consistency", "database isolation" and "SQLite snapshots" mean (all three concepts can be learned by reading articles on Internet), then you may profit from the groundwork made available by DatabaseSnapshotPool. If GRDB were ever to provide ready-made support for lazy paginated lists, it would be based on it.

[hyouuu]

Interesting didn't think I'd be the first one using paginated queries with GRDB 😂 Will try with the incremental query we talked about first and if I notice a performance issue then will see if I can manage the snapshots. Merci!!

[groue]

I'm surprised as well 😉 I think this is because the concept of paginated data sources is not built-in on iOS, like it is on Android, because people have learned the hard way how bad are the performances of SwiftUI List, and because a list of hundreds of items is not very usable on a mobile device. Things are different of a desktop computer, sure, but there are less desktop developers.