BigQuery分页实现方案是否合理？多用户场景性能疑问

Great question—scaling pagination for thousands of concurrent users on BigQuery requires balancing correctness, resource usage, and cost. Let’s break down your current implementation and explore solutions to address your performance concerns.

Your Current Approach: Valid but Not Scalable for Thousands of Users #

First off, your code works in dev, and canceling old jobs before creating new ones for the same user is a smart way to avoid wasting BigQuery resources. That said, when you scale to thousands of users, this approach will hit some bottlenecks:

• Quota Limits: BigQuery has default concurrency limits for query jobs (typically around 100 concurrent jobs per project). With thousands of users hitting your service at once, you’ll likely hit these limits, leading to throttled or failed requests.
• Redundant Compute: If multiple users run identical queries (e.g., shared dashboards), you’re repeating the same query execution work over and over—wasting compute resources and driving up costs.
• Latency Overhead: Creating a query job and waiting for initial results adds small but cumulative latency, especially if users paginate frequently.

Alternative Approaches to Improve Scalability #

1. Cache Repeated Query Results #

If many users run the same queries, cache the full results once and serve all pagination requests from the cache:

• Implementation Steps:
  • On the first execution of a query, run the job and write results to a temporary BigQuery table (set an expirationTime to auto-delete old data, like 24 hours) or Cloud Storage.
  • For subsequent users or pagination requests, fetch data directly from this cached source instead of creating a new job.
  • Use keyset pagination (see below) on the cached table for efficient page loads.
• Pros: Eliminates redundant query runs, cuts job concurrency, and reduces costs.
• Cons: Not ideal for real-time data—set appropriate cache expiration times or refresh triggers when underlying data changes.

2. Reuse Query Jobs for the Same User Session #

BigQuery retains query job results for 24 hours by default (you can extend this with expirationTime). Instead of canceling a job when a user paginates, reuse the existing job ID for all their pagination requests:

• Implementation Steps:
  • When a user runs a query for the first time, store the job ID and its expiration time in a session store (like Redis) tied to the user.
  • For "next page" requests, fetch the stored job ID and call getQueryResults with the page_token instead of creating a new job.
  • Only create a new job if the user runs a different query or the existing job has expired.
• Pros: Reduces job creation overhead, keeps your job count lower, and maintains result consistency for the user’s session.

3. Keyset Pagination (Ditch page_token Entirely) #

If your target table has a unique, ordered column (e.g., id, created_at), use keyset pagination instead of relying on BigQuery’s job-based page_token. This lets you fetch pages directly with simple ad-hoc queries:

• Example SQL:

  SELECT *
  FROM your_table
  WHERE id > @last_seen_id  -- Pass the last ID from the previous page
  ORDER BY id ASC
  LIMIT 50
  

• Implementation Steps:
  • On the first request, fetch the first 50 rows and return the last id to the frontend.
  • For "next page" requests, the frontend sends the last id from the previous page, and you run the query above to get the next batch.
• Pros: No job management needed—each request is a lightweight query. Avoids job concurrency limits entirely, and is often faster than fetching via job results.
• Cons: Not perfect for frequently updated datasets (you might miss/duplicate rows if inserts/deletes happen between pages). Best for static or slowly changing data.

4. Precompute Results with Scheduled Queries #

If your queries are fixed and non-real-time (e.g., daily sales reports), use BigQuery’s Scheduled Queries to precompute results into a dedicated table:

• Implementation Steps:
  • Set up a scheduled query to run at regular intervals (e.g., nightly) and write results to a persistent table.
  • Frontend pagination requests directly query this precomputed table using keyset or LIMIT/OFFSET pagination.
• Pros: Zero runtime query overhead for users—data is ready to serve instantly. Lowest cost and highest performance for static reports.
• Cons: Doesn’t work for real-time or user-specific queries.

Final Recommendations #

• For unique user queries: Stick with your current approach, but optimize by reusing job IDs for the same user’s pagination (don’t cancel jobs prematurely). Also, check your BigQuery concurrency quotas and request an increase if needed.
• For shared/repeated queries: Implement caching with temporary tables to cut redundant job executions.
• For static/slowly changing data: Switch to keyset pagination to eliminate job management and improve scalability.

内容的提问来源于stack exchange，提问作者Akash Kumar Seth