Racket/db LIMIT/OFFSET pagination: performance uncertainty with large offsets

Does racket/db forward LIMIT/OFFSET verbatim?

Racket/db is a thin wrapper around database drivers. When you call query or query-rows with a SQL string that contains LIMIT and OFFSET, the library passes that string to the underlying driver with essentially no transformation. Therefore the pagination semantics and cost are determined entirely by the database engine, not by Racket code. This means the performance characteristics you observe are the same as if you ran the raw SQL directly on the DBMS.

Latency impact of large OFFSET values

Offset‑based pagination requires the engine to scan and discard the skipped rows before returning the requested page. On common back‑ends this cost grows roughly linearly with the offset value:

• SQLite: scans the B‑tree row‑by‑row; with a table of 500 k rows and an offset of 100 k the engine must read roughly 100 k index entries before fetching the limit rows. Row width and cache size affect the exact time, but latency typically becomes noticeable beyond a few tens of thousands of skipped rows.
• PostgreSQL: the planner can use an index scan or an index‑only scan if the selected columns are covered. Without a covering index the cost still scales with offset; at offset > 100 k on a 500 k‑row table you may see seconds‑scale delays depending on row size and work_mem settings.
• MySQL: the optimizer historically struggles with deep offsets. Without a covering index each page fetch scans the entire index up to the offset, leading to rapidly increasing execution time. MySQL 8 improves the planner but deep offsets remain expensive without a tight index.

In practice, on a dataset of a few hundred thousand rows, offsets exceeding 50 000–100 000 often produce measurable slowdown compared with the first page, especially when the sort column lacks a selective index.

Keyset (seek) pagination pattern in Racket

Keyset pagination replaces OFFSET with a WHERE clause that references the last seen value of a monotonically ordered column. This turns the query into an index range scan, whose cost is roughly constant regardless of page depth.

Racket/db has no built‑in keyset helper, but the common practice is to construct the SQL manually using parameterized placeholders. Example for a table with a primary‑key id:

(query db
   "SELECT * FROM items WHERE id > $1 ORDER BY id LIMIT $2"
   last-seen-id page-size)

Keep the SQL text stable across calls and vary only the bound values; racket/db’s prepared‑statement caching avoids re‑parsing overhead.

If the sort column is not unique, include a tie‑breaker (e.g., (created_at, id)) and ensure a composite index exists.

To tailor the recommendation, please provide the database engine version and average row size (or typical column count).