I recently learnt something about BigQuery pagination that I wish I’d known sooner. If you’re paginating BQ results with LIMIT/OFFSET in the SQL, you’re probably paying full slot cost on every single page request — even though BQ already has the results sitting there.

The problem Link to heading

BigQuery has a query cache, but it only kicks in when the SQL text and parameters are identical. If OFFSET changes on every page, every request is a cache miss.

The fix: reuse the job Link to heading

When BigQuery runs a query, it stores the result set in a temporary table for 24 hours. You can read from that table as many times as you want at zero cost using the original job ID:

# First page: run the full query
job = client.query(sql, job_config=config)
rows = list(job.result(start_index=0, max_results=11))
# Save job.job_id in the pagination cursor

# Subsequent pages: reuse the job
existing_job = client.get_job(saved_job_id, location=job.location)
rows = list(existing_job.result(start_index=10, max_results=11))
# Zero slot cost — just reading from the cached result table

The key insight is that result() accepts start_index and max_results parameters. You don’t need LIMIT/OFFSET in the SQL at all — just run the full query once and paginate over the results using these parameters.

fetch_count = page_size + 1  # +1 to check if there's a next page

if job_id:
    try:
        existing_job = client.get_job(job_id)
        rows = list(existing_job.result(start_index=offset, max_results=fetch_count))
        has_next = len(rows) > page_size
        return PageResult(rows=rows[:page_size], job_id=job_id, has_next=has_next)
    except Exception:
        log.info("Job reuse failed for job_id=%s, running fresh query", job_id)

# Fresh query fallback
query_job = client.query(sql, job_config=config)
rows = list(query_job.result(start_index=offset, max_results=fetch_count))
has_next = len(rows) > page_size
return PageResult(rows=rows[:page_size], job_id=query_job.job_id, has_next=has_next)

If the cached job has expired (after 24h) or something goes wrong, it falls back to a fresh query. The user just sees a slightly slower page load.

The cursor Link to heading

The job ID and offset get encoded into an opaque cursor that the client passes back on the next request:

def encode_cursor(job_id: str, offset: int, location: str) -> str:
    payload = json.dumps({"job_id": job_id, "offset": offset, "location": location})
    return base64.urlsafe_b64encode(payload.encode()).decode()

def decode_cursor(cursor: str) -> dict | None:
    try:
        data = json.loads(base64.urlsafe_b64decode(cursor))
        if isinstance(data, dict) and "job_id" in data and "offset" in data and "location" in data:
            return data
    except (json.JSONDecodeError, ValueError, UnicodeDecodeError):
        pass
    return None

The client never parses the cursor — it just passes it back as-is. This means you can change the format without any client changes.

Don’t forget to sign it Link to heading

There’s a subtle security issue here that’s easy to miss: the job_id in the cursor is attacker-controlled. If your query has a WHERE tenant_id = @id filter, that filter only runs on the initial query. get_job() doesn’t re-run the query — it just reads from the cached result table. So a malicious user could swap their job ID for another user’s and read their data.

The fix is to HMAC-sign the cursor payload with a key scoped to the user:

def _cursor_hmac(payload: str, user_id: str) -> str:
    key = f"{SECRET_KEY}:{user_id}".encode()
    return hmac.new(key, payload.encode(), hashlib.sha256).hexdigest()

def encode_cursor(job_id: str, offset: int, location: str, user_id: str) -> str:
    payload = json.dumps({"job_id": job_id, "offset": offset, "location": location})
    sig = _cursor_hmac(payload, user_id)
    token = json.dumps({"p": payload, "s": sig})
    return base64.urlsafe_b64encode(token.encode()).decode()

def decode_cursor(cursor: str, user_id: str) -> dict | None:
    try:
        token = json.loads(base64.urlsafe_b64decode(cursor))
        if not isinstance(token, dict) or "p" not in token or "s" not in token:
            return None
        expected_sig = _cursor_hmac(token["p"], user_id)
        if not hmac.compare_digest(token["s"], expected_sig):
            return None
        data = json.loads(token["p"])
        if "job_id" in data and "offset" in data:
            return data
    except (json.JSONDecodeError, ValueError, UnicodeDecodeError):
        pass
    return None

A tampered cursor returns None, which triggers a fresh query. No data leak, no error — just a slightly slower page load.

Don’t forget the location Link to heading

I deployed the job reuse feature and it looked like it was working — no errors, pages loaded fine. But every page was still running a fresh query. The fallback was silently catching the real error.

Turns out client.get_job(job_id) defaults to the US location. If your datasets are in a different region, the job won’t be found. You need to pass the location explicitly:

# This silently fails with a 404 if the job is in a non-US region
existing_job = client.get_job(job_id)

# This works
existing_job = client.get_job(job_id, location="europe-west1")

The location comes from query_job.location after running the initial query — BQ infers it from the dataset being queried. Store it in the cursor alongside the job ID and offset (the examples above already include this).

Results Link to heading

  • Page 1: still runs the full query (full slot cost)
  • Pages 2+: reads from cached job result (zero slot cost)
  • No client changes: cursor is opaque, backwards compatible

If you have any paginated BQ query where users typically browse more than one page, this is probably the easiest performance win you’ll find.

Further reading Link to heading