Search Campaign Builder lets users create and manage Google Ads search campaigns without leaving the platform. The backend is a Java and Spring Boot service. It sits between the Rails platform and Google Ads, and stores campaign hierarchy data for every entity. As campaign counts grew, the service began to struggle under load, and a customer-reported production incident confirmed it.
I led the investigation and the fixes end to end, pairing with the tech lead at the key decision points. Diagnosis started in Datadog, where the query breakdown showed sequential scans on the hot paths alongside query counts and cumulative execution time. I replicated the same scenarios locally to confirm and isolate them. A benchmark that imported Google Ads campaigns with different entity counts across iterations mapped the scaling curve precisely. Three root causes emerged independently: missing indexes on hot query paths, N+1 queries in the campaign fetch layer, and transaction lock contention.
A fourth issue surfaced during benchmarking. The same long-running campaign collided with itself when two builds imported it at the same time. That was a correctness problem hiding inside the performance failure.
Each issue needed its own fix. I added indexes on the hot paths and consolidated the N+1 queries. I narrowed the transaction scope, accepting a slightly wider consistency window in exchange for shorter lock durations. The idempotency guard went at the import layer instead of into a database constraint, because the coordination had to happen before the transaction opened and across build boundaries. I re-ran the benchmark harness at 4× load to confirm the fixes held.
Delivered across three sprints. The service absorbed 4× workload growth at flat p50, p95, and p99 latency. The Postgres index hit rate rose from 81% to 93%, and query latency on the hot campaign fetch path fell with it.