Dashboard Aggregates Over 1.4 Million Pothole Reports

The design moves 1.4 million features across the network to compute three numbers, so it fails on volume, latency, and service limits. A layer's maxRecordCount, 2,000 by default here, caps a single response; exceededTransferLimit=true is the server stating that the result was truncated. Paging 706 times multiplies latency and load, and the bursts of requests explain the intermittent 503s.

Replace each widget with a purpose-built request:

• Total open: where=status='Open' with returnCountOnly=true. One small response, no geometry, no paging.
• Grouped counts: outStatistics with a count on objectid, groupByFieldsForStatistics set to status and district, returnGeometry=false. One request returns every group.
• Citywide map: do not render 1.4 million points in the browser. Use a vector tile layer or a cached tile layer built from the data, or server-side clustering, and let the feature layer serve only zoomed-in extents.

Add indexes on status and district, request only needed fields, and cache aggregates for 5 to 15 minutes. Instrument server response time separately from client render time so you can tell whether the bottleneck is query cost or drawing.

Use keyset pagination, not offset pagination. Keep a checkpoint of the largest objectid already written and loop with where=objectid > checkpoint and orderByFields=objectid ASC, resultRecordCount set to maxRecordCount. Each page is a bounded indexed range, so cost stays flat; deep resultOffset values get progressively more expensive and some services cap them.

Operational rules:

• One writer, concurrency of 2 to 4 at most, because the export competes with the dashboard.
• Retry 429 and 503 with exponential backoff plus jitter, honoring Retry-After when present.
• Persist each page to staging and advance the checkpoint only after a successful write, so a failure resumes rather than restarts.
• Request returnGeometry=false when the warehouse loads geometry from another source; otherwise set outSR explicitly and document it.
• Compare the final row count against a returnCountOnly call to detect missed pages.

For recurring large extracts, evaluate the hosted layer's extract or replica capabilities, or use the ArcGIS API for Python query with return_all_records, which encapsulates this loop. Schedule off-peak, alert on non-zero exit, and log per-page row counts so a silent truncation is visible.

Five-minute refresh against 1.4 million features is a data-model problem, not a caching problem. Precompute what the dashboard needs: a scheduled script (a notebook, Data Pipelines, or a small service) writes counts into a compact summary table or hosted layer keyed by status and district. The dashboard then reads tens of rows instead of aggregating a million, and a five-minute cadence becomes cheap and predictable.

If live counts are non-negotiable, cache per widget with a short TTL, deduplicate concurrent requests so ten open tabs do not issue ten identical queries, and set the client refresh interval to match the TTL rather than firing on every view change.

For the map, avoid re-querying on every pan and zoom. Use a tiled or clustered representation with a fixed refresh, and drill down to the feature layer only for the visible extent with a tight outFields list. Keep the layer's maxRecordCount in mind for those extent queries and use exceededTransferLimit as a signal to refine the scale rather than to page.

Finally, watch the failure mode where each open dashboard tab polls independently; that multiplies load and is a common cause of intermittent 503s. A shared backend cache or the summary layer removes both the load and the variance.