dbplyr LIMIT translation and DBI chunked fetch for large remote tables

Short answer

Yes—the database server enforces the translated LIMIT, so DBI cannot receive more rows than that limit. However, collect() on a dbplyr remote table typically calls dbGetQuery(), which pulls the entire result set into R and ignores the fetch-size argument. If you manually issue dbSendQuery() and loop dbFetch(n = ...), the driver's fetch size controls round-trip chunking, but the total is still bounded by the server-side LIMIT. For LIMIT/OFFSET pagination, each page is a separate SQL statement, so DBI streaming applies within each page, not across the whole sequence.

Confirmed mechanics

• Server-side cap: Assuming dbplyr 2.x+ and a standard SQL backend, slice_head(n) or limit(n) is rendered into the dialect's LIMIT clause. The database returns at most n rows; DBI cannot fetch what the server did not send.
• collect() path: The common dplyr verb collect() uses dbGetQuery(), which materializes the complete result. The n argument to dbFetch() is not part of this path.
• Manual streaming: dbSendQuery() followed by repeated dbFetch(n = chunk) lets a driver pull rows incrementally, subject to driver support.
• Pagination scope: dbplyr has no universal cursor abstraction in core; offset-based paging generates independent statements. Streaming resets per statement.

Where driver variance applies

The likely source of confusion is that DBI's fetch-size documentation describes the low-level streaming API, while dbplyr's collect() uses the higher-level dbGetQuery() convenience wrapper. Fetch-size support is driver-specific: RPostgres and RMariaDB honor the chunk argument, while RSQLite pulls all rows regardless. Large OFFSET values also force the server to skip rows before returning the limited set, which can degrade performance independently of client-side fetching. Finally, dialect differences (e.g., Oracle ROWNUM or FETCH FIRST) may change edge-case semantics, though the row-count guarantee remains.

Practical pattern for bounded pagination

If the goal is to page through a large remote table without materializing the entire dataset, keep each page small enough to fit comfortably in memory and use collect() per page:

page_size <- 5000
repeat {
  page <- tbl(con, "big_table") %>%
    arrange(id) %>%
    slice_head(n = page_size) %>%
    collect()
  if (nrow(page) == 0) break
  # process page
}

If a single page is still too large for memory, render the dbplyr query to SQL and stream it manually:

# Render the lazy query to a SQL string, then stream
res <- dbSendQuery(con, rendered_sql)
while (!dbHasCompleted(res)) {
  chunk <- dbFetch(res, n = 1000)
  if (nrow(chunk) == 0) break
  # process chunk
}
dbClearResult(res)

What to verify

Because behavior depends on the driver, confirm chunked support before relying on it. On RPostgres, for example, you can verify that total rows never exceed the translated limit:

res <- dbSendQuery(con, "SELECT * FROM big_table LIMIT 10 OFFSET 1000")
total <- 0
while (!dbHasCompleted(res)) {
  chunk <- dbFetch(res, n = 5)
  total <- total + nrow(chunk)
}
dbClearResult(res)
stopifnot(total == 10)

The one diagnostic that changes the recommendation is the DBMS and DBI driver in use: on RSQLite, manual dbFetch loops will not reduce memory pressure, so smaller per-page limits are the safer route.