Question
Limit and Offset Pagination in Racket DB: Unresolved Streaming API Gap
Nia Nova
0 reputation · 10 Feb 2024, 19:35 UTC
56.5K views
Loading…
0 reputation · 10 Feb 2024, 19:35 UTC
The Racket DB library’s query-values function supports SQL LIMIT and OFFSET clauses, allowing developers to fetch a specific slice of a large table. The goal is to reduce memory consumption and improve response times when handling datasets that exceed available RAM.
However, query-values and query-rows eagerly collect all rows that match the query into a list before returning control to the caller. This eager collection becomes a bottleneck when the result set is very large, even if the LIMIT is small. Moreover, using very large OFFSET values can degrade performance on many SQL backends because the database must scan and discard rows before returning the desired page.
There is currently no documented, stable API in the Racket DB library that streams rows incrementally or exposes server‑side cursors for large result sets. Community discussions have suggested potential extensions, but no official implementation exists yet.
OFFSET values and alternative pagination strategies such as key‑set pagination or manual cursor handling?34775 reputation · 11 Feb 2024, 02:40 UTC
Assumes Racket 8.x and the portable racket/db collection. The library does not expose a stable streaming, lazy-sequence, or server-side cursor API. query-values and query-rows eagerly collect the full result set into a Racket list before returning, so even a small LIMIT still materializes every returned row in memory. LIMIT and OFFSET text is passed to the backend unchanged, but the driver does not stream rows incrementally. No official streaming roadmap item is part of the published library contract; community packages should be treated as experimental and unverified.
Confirmed: query-values returns a list, so memory use scales with page size rather than total table size. The database still pays the OFFSET scan cost because it must skip rows before applying the limit. Likely explanation: the portable API was designed for backend neutrality, so driver-specific cursor protocols were left out.
racketOFFSET pagination with key-set (seek) pagination where possible:(define (fetch-page conn last-id page-size)
(query-values conn
"SELECT id, title, created_at FROM items WHERE id > ? ORDER BY id LIMIT ?"
last-id page-size))This avoids the server-side offset scan. Require a unique, indexed sort key; append a tie-breaker such as ORDER BY created_at, id if the primary column is not unique.
racket/generator only if you need a lazy application-level sequence. This does not remove the database offset cost.DECLARE CURSOR / FETCH. Hold the transaction open across fetches, watch for lock contention and connection-pool exhaustion, and treat this as non-portable.racket --version
raco docs dbOpen the "Querying" section and confirm the return type is a list. For a memory check, compare (query-values conn "SELECT generate_series(1,1000000)") against a key-set loop in racket -t and observe GC pressure. To look for community packages, run raco pkg search db; none of the results should be treated as official or maintained without separate review.
Which database backend and Racket driver are you using? Cursor support differs by driver, for example PostgreSQL versus SQLite, and that answer changes whether a driver-specific cursor is practical.
Use comments to ask for clarification. Post a solution as an answer.
No question comments on this page.