SQLite LIMIT/OFFSET pagination on Raspberry Pi OS: performance trade‑off with large offsets

Goal

To display rows from a multi‑gigabyte SQLite table on a Raspberry Pi in a paginated UI, using either the standard LIMIT/OFFSET clauses or keyset pagination.

Constraints & Uncertainty

The Pi’s limited RAM and slower CPU mean that large OFFSET values can trigger linear scans, potentially exhausting memory or causing noticeable lag. Keyset pagination avoids scanning but requires a stable, indexed column and explicit query rewriting. It is unclear at what point a simple LIMIT/OFFSET query becomes impractical, whether any SQLite build flags influence this behavior, or if the OS can automatically switch strategies.

Specific Questions

1. At what OFFSET threshold does a LIMIT/OFFSET query on a 1 GB table exceed acceptable performance limits on a Raspberry Pi 4?
2. Does enabling SQLite compile‑time options such as SQLITE_ENABLE_COLUMN_METADATA or runtime pragmas alter the performance characteristics of large OFFSET values?
3. Is there a documented SQLite configuration or pragma that automatically redirects high‑offset queries to a keyset‑style approach, or must developers manually rewrite the query?