Collecting large tables safely

Measure first

The eighty-six tables hold about 19 million rows between them, spread very unevenly — from none at all in especiais/orgaos_analises_pendentes_especiais to 5,052,521 in discricionarias/plano_aplicado_detalhado.

sizes <- tg_tables(counts = TRUE)
sizes[order(-sizes$rows), c("module", "table", "columns", "rows")]
#> # A tibble: 86 × 4
#>    module          table                           columns    rows
#>    <chr>           <chr>                             <int>   <dbl>
#>  1 discricionarias plano_aplicado_detalhado             15 5052521
#>  2 discricionarias perguntas_respostas_selecao_pac       6 1899784
#>  3 discricionarias meta_crono_fisico                    18 1562265
#>  4 parcerias       extrato_bancario                     13 1362980
#>  5 discricionarias todos_programas                      37 1258456
#>  …

That call makes eighty-six requests, and caches them. For a single table:

tg_count("fundoafundo", "gestao_financeira_lancamentos")
#> [1] 1160094

tg_count() takes the same filters as tg_get(), so you can size the thing you actually want rather than the whole table:

tg_count("parcerias", "proposta")
#> [1] 89415
tg_count("parcerias", "proposta", sg_uf_recebedor = "PE")
#> [1] 3258

What a page costs

Each module caps a page at its own size: 200 rows in discricionarias, especiais and parcerias, 1000 in fundoafundo and ted. tg_modules() reports it, and .page_size defaults to it. Unlike some APIs, these do not silently truncate a larger request — one row over the cap is a 422, and the package refuses it before sending:

tg_get("parcerias", "proposta", .page_size = 201)
#> Error in `tg_get()`:
#> ! `.page_size` must be a whole number between 1 and 200.

So the arithmetic is simple and worth doing, and the cap matters as much as the size. The largest table, discricionarias/plano_aplicado_detalhado, is ceiling(5052521 / 200) = 25,263 requests — at the default throttle of sixty a minute, about seven hours. fundoafundo/gestao_financeira_lancamentos has 1.2 million rows but lives in a module that serves pages of 1000, so it takes 1,161 requests, about twenty minutes.

rows <- tg_count("discricionarias", "plano_aplicado_detalhado")
ceiling(rows / 200)
#> [1] 25263

If you genuinely need a table that size, consider whether a filter narrows it first, and raise the throttle deliberately rather than by accident:

options(transferegovr.requests_per_minute = 120)

Limits and offsets count rows

.limit and .offset are in rows, not pages, whatever .page_size is set to.

tg_get("especiais", "meta_especiais", .limit = 450)

That is three requests: 200, 200, 50 — the last page is trimmed to the limit. An offset that falls inside a page is handled by fetching the page it lands in and dropping the rows before it:

tg_get("especiais", "meta_especiais", .limit = 100, .offset = 137,
       .page_size = 60)

Inf collects everything that matches:

programas <- tg_get("especiais", "programas_especiais", .limit = Inf)

Checking what you got

Every result carries the pagination state the API reported:

metas <- tg_get("especiais", "meta_especiais", .limit = 450)

tg_metadata(metas)
#> $module
#> [1] "especiais"
#> $table
#> [1] "meta_especiais"
#> $total_rows
#> [1] 156193
#> $rows_returned
#> [1] 450
#> $pages
#> [1] 3
#> …

total_rows is how many rows matched, rows_returned how many you have. If collection ends short of what the API said it would return, that is a warning rather than a silent truncation:

Warning: Collected 448 rows where the API reported 450.
ℹ The table may have changed while it was being read.

Why the row order is safe to rely on

These APIs publish no ordering parameter. Page two is simply “page two”, and whether that is a well-defined thing depends on the server keeping a stable order between requests — which nothing in the documentation promises.

Postgres makes no such promise in general: a query without ORDER BY may return rows in a different order between executions, and under offset pagination that means page two can repeat rows from page one and skip others entirely. A row count would not reveal it. Two pages of 200 that overlap by 40 rows still add up to 400.

So it was tested rather than assumed. The check is to fetch the same rows at two different page sizes and compare them as sequences:

strip <- function(x) {
  x <- as.data.frame(x)
  attr(x, "transferegovr_metadata") <- NULL
  rownames(x) <- NULL
  x
}

big <- tg_get("especiais", "meta_especiais", .limit = 450, .page_size = 200)
small <- tg_get("especiais", "meta_especiais", .limit = 450, .page_size = 50)

identical(strip(big), strip(small))
#> [1] TRUE

Three requests and nine requests, cutting the same 450 rows at different boundaries, produce the same rows in the same order. That is what rules out both overlap and skipping.

The same comparison holds 100,000 rows deep, across repeated calls, on tables with no natural key, and on tables with nested columns. test-live.R re-runs all of it against the real services, so a change upstream shows up as a failing test rather than as quietly wrong data.

Caching

Responses are cached for an hour, so re-running a collection during a session costs nothing:

first <- tg_get("especiais", "meta_especiais", .limit = 450)
again <- tg_get("especiais", "meta_especiais", .limit = 450)

tg_metadata(again)$cached
#> [1] TRUE

The default cache lives in the session’s temporary directory, so nothing is written outside the session unless you ask. For a long collection you will want it to survive:

tg_cache_dir(tools::R_user_dir("transferegovr", "cache"))

The APIs send no ETag, Cache-Control or Last-Modified, so HTTP caching would store nothing — this cache is the package’s own. Use tg_updated_at() to decide when a cached copy is stale:

tg_updated_at("fundoafundo")
#> [1] "2026-09-28 06:02:02 UTC"

A pattern for very large tables

For anything in the hundreds of thousands, collect in slices and write each one out, so an interrupted run does not start over:

library(purrr)

total <- tg_count("fundoafundo", "gestao_financeira_lancamentos")
slice_size <- 20000
starts <- seq(0, total - 1, by = slice_size)

walk(starts, function(start) {
  file <- sprintf("lancamentos-%08d.rds", start)
  if (file.exists(file)) return(invisible(NULL))

  rows <- tg_get(
    "fundoafundo", "gestao_financeira_lancamentos",
    .limit = slice_size, .offset = start
  )
  saveRDS(rows, file)
})

Because the order is stable, the slices reassemble into the whole table without gaps or repeats.