--- title: "Battle test: five million records, exact counts, and a faster Inbox badge" section: "Trust & Security" canonical_url: "https://support.buildwithhq.com/trust/battle-test-five-million-row-2026-09-23.html" reviewed_at: "2026-09-14" authority: Official --- # Battle test: five million records, exact counts, and a faster Inbox badge What we measured while tuning secured Contacts, Global Search, and Universal Inbox paths on a five-million-record database. ## In brief - **Scale fixture:** 5,000,000 records across 20 organizations - **Authenticated soak:** 10,851 requests; zero errors or pool waiters - **Security result:** Tenant, role, and location checks passed On September 23, 2026, we used a retained five-million-record Core database to test the paths most likely to become expensive as a SaaS grows: authorized Contacts counts, Global Search counts, and the Universal Inbox attention badge. We tested both the large database directly and a rebuilt authenticated API gateway. The work also updated the golden Core database so newly provisioned SaaS databases inherit the reviewed procedures and indexes. Note These are measurements from one controlled local test machine, not an availability guarantee or customer SLA. The large-scale SQL fixture and the authenticated gateway fixture are deliberately separate; we do not present a small routed database as five-million-row HTTP evidence. ## The test shape - Five million active records distributed across 20 tenant organizations. - About 250,000 Contacts and 250,000 open Inbox items in each loaded organization used by the scale cases. - Real DataRoles, location assignments, direct-conversation visibility, read state, and server-derived tenant/user identity. - A second 400-contact fixture to catch plans that help a very large organization but penalize the more common small organization. - Concurrency at 8 and 32, followed by a 60-second authenticated mixed gateway run. ## What changed Exact totals remain opt-in. Ordinary Contacts and Global Search requests return rows first without calculating a full count. The product asks for the exact authorized count separately and caches it for the session. Browsing is bounded to 5,000 matches, while the exact total can still report all authorized matches. Search runs before the browse limit, so a person can find a contact outside the unfiltered window. The exact Contacts and Search counts use dedicated secured procedures and indexes rather than copying the entire authorized set into a temporary primary-key table. The common Global Search path starts from indexed terms and intersects terms before applying the same record-security checks. The top navigation no longer calculates the complete per-source Inbox aggregate every time it refreshes. It performs a fixed-work probe that displays 1 through 98 or **99+**. Opening Universal Inbox separately loads the exact unread, approval, human-review, worker, assignment, delivery, and per-source totals. A failed exact request is shown as unavailable; it is never converted into a false zero. ## Large-database results | Case | Before | After | | --- | --- | --- | | Contacts exact count (249,998 visible) | 2,786,694 mean logical reads; 4.525 s p95 | 5,092 mean logical reads; 1.234 s p95 | | Global Search common-term exact count (250,000 visible) | 1,506,553 mean logical reads; 2.581 s p95 | 7,328 mean logical reads; 0.888 s p95 | | Top-bar Inbox attention | Exact aggregation: 7,416 logical reads; 1.070 s p95 | Bounded 99+ probe: 3,210 logical reads; 0.510 s p95 | The optimized Contacts plan reduced logical reads by more than 99% in this fixture. It did not make exact counting free: an exact count still has to prove every visible match. That is why exact totals remain opt-in and session-cached instead of running on every list request. ## Concurrency and connection pools At direct-SQL concurrency 32, the bounded badge completed 64/64 operations with a 1.184 s p95 and 30.4 operations per second. The exact badge also completed 64/64, but its p95 was 2.249 s and throughput was 15.7 operations per second. The bounded projection therefore protects the high-frequency top-bar path while retaining exact totals where they are useful. The authenticated gateway then mixed the bounded badge, exact badge, fast Contacts, and fast Global Search at concurrency 32 for 60 seconds. All 10,851 requests returned HTTP 200, with no errors or timeouts. Throughput was 180.4 operations per second. Process-level SqlClient counters observed six active pools, at most 59 managed connections, at most 22 active connections, and zero connections waiting for a pool slot. An independent SQL-session observer saw at most 59 gateway sessions and 24 running sessions. | Authenticated route | p95 | p99 | | --- | --- | --- | | Bounded Inbox badge | 275 ms | 433 ms | | Fast Contacts page | 281 ms | 439 ms | | Exact Inbox badge | 371 ms | 505 ms | | Fast Global Search page | 241 ms | 606 ms | This is intentionally a capacity test, so a test-only high rate limit was bound to the one disposable SaaS. Production rate-limit defaults were not changed, and normal 429/Retry-After behavior remains covered separately. ## Security and correctness checks Count and badge performance is not allowed to weaken authorization. Independent derivations compared the optimized results with the established secured-record envelope. The suite covered six role/location identities across organizations, including users with no visible location and users allowed to see the loaded location. A hostile request targeting another organization returned no information, and a forged SaaS identifier through the authenticated gateway returned HTTP 403. The migration was replayed twice, its grants and index were inspected, and procedure contracts were regenerated. Stored-procedure conformance reported zero violations and zero unresolved changed calls. Before applying to golden and the routed battle Core, the operator created compressed COPY_ONLY backups with CHECKSUM and passed RESTORE VERIFYONLY. The routed activation gate then confirmed that the sole active Core had the required procedure and index contract. ## What this does not claim - The five-million-row plan measurements are direct SQL evidence; the authenticated HTTP soak used the current smaller routed battle tenant. - The observed latency belongs to this hardware, data distribution, and concurrent workload. A different workload can produce different plans and timings. - Instance-wide SQL wait deltas can include unrelated local activity. - This test is not a disaster-recovery exercise, third-party-provider certification, public Internet load test, or compliance attestation. ## Design guidance for builders and API clients - Use the default rows-first Contacts and Global Search calls for interactive pages. - Request an exact count only when the user needs it, and cache it for the session or invalidate it after a relevant write. - Treat `99+` as an attention indicator, not an exact total. Load the exact Inbox badge when the Inbox panel opens. - Do not treat `hasMore: false` at the 5,000 browse boundary as proof that a full export is complete. Use the documented uncapped Records enumeration path for complete synchronization. Related guidance: [Progressive loading and exact counts](../api/progressive-loading-and-counts.html) and [Tenant Data Security](tenant-data-security.html). --- Capability review: 2026-09-14. [View the canonical guide](https://support.buildwithhq.com/trust/battle-test-five-million-row-2026-09-23.html).