RStudio Data Viewer on a large dplyr lazy table: is pagination happening in the database or only in the IDE grid?

Server-Side Pagination in RStudio

When you call View() on a lazy tbl (a dbplyr object), RStudio executes server-side pagination. It does not trigger a full collect() of the dataset into your R session. Instead, the IDE appends LIMIT and OFFSET clauses to the SQL query, fetching only the small chunk of rows required to populate the current grid view. Scrolling to the bottom of the grid triggers subsequent queries to fetch the next page of results from the database.

Verification and Diagnostics

To confirm that the IDE is pushing limits to the database rather than pulling the full table, you can use the following methods:

• Inspect the SQL: Use dbplyr::show_query() on your lazy table to see the base query. While View() handles the final SQL internally, you can simulate the viewer's behavior with head(lazy_tbl, 1000) %>% show_query() to verify that LIMIT is correctly translated by your database driver. An explain plan on that bounded query confirms the pushdown before any collection happens.
• Database Monitoring: The most definitive verification is monitoring the database server's active process list (e.g., pg_stat_activity in Postgres) while scrolling through the Data Viewer. You will see a series of incoming queries with varying OFFSET values as you move down the grid.

Handling Mutating Data and Pagination Stability

Using OFFSET pagination on a table where rows are being inserted or deleted can lead to drifting results, where rows are skipped or duplicated across pages. To ensure stable pagination, you must provide a deterministic ordering strategy.

Avoid ordering by non-unique columns (like a timestamp that might have duplicates). Instead, use a combination of a sort key and a unique identifier:

lazy_tbl %>%
  arrange(desc(created_at), primary_key_id)

By including the primary key in the arrange() call, you ensure that the database returns rows in a consistent sequence regardless of concurrent mutations, making the OFFSET logic reliable. For very high-churn tables, keyset (cursor) pagination on the unique key is more robust than OFFSET, but that requires manual queries rather than the viewer grid.

Assumptions and Limitations

This behavior assumes you are passing the tbl object directly to View() on a recent RStudio/Posit release; viewer and Connections pane behavior can differ across versions, so confirm against current release notes. If you pipe the object into collect() before viewing, the entire dataset is loaded into RAM, and pagination happens exclusively within the IDE grid. Note that complex window functions or heavy ORDER BY clauses may still cause performance lag on the database server, even with LIMIT applied.