“The product page gets slower every time the catalogue team adds products. No single query is slow.”

1. 10:00

   REPORT

   Weekly review: /products p99 has grown every week for a month

2. 10:08

   DEAD END

   Postgres slow-query log (Case 2.4): nothing over 1 ms

   Every query is fast. The problem isn't any single query, it's HOW MANY there are. Neither a slow-query log nor the database metrics can show that.
3. 10:14

   QUERY

   Find /products traces with lots of database spans

   trace:rootName is an intrinsic: the name of the trace's root span. | count() > 50 keeps traces where more than 50 spans match the selector. That's a question about trace SHAPE, which only traces can answer.

   TraceQL· Tempocopy

   { trace:rootName = "GET /products" && span.db.system = "postgresql" } | count() > 50

   result

   Trace ID                          Root span       Matched spans  Duration
   6d1e0c2b9a7f4e3c8b5a2d1f0e9c8b7a  GET /products   201            352ms
   ...

4. 10:16

   FINDING

   One list query, then one stock query PER product, one after another

   The waterfall is unmistakable: a staircase of tiny identical spans. Each takes ~1.5 ms including the network round trip, and 200 of them in sequence is 300 ms. It grows linearly with the catalogue.

   GET /productsshoplite

   352 ms

   pg.query:SELECT productsshoplite

   3 ms

   pg.query:SELECT stock (1)shoplite

   2 ms

   pg.query:SELECT stock (2)shoplite

   2 ms

   pg.query:SELECT stock (3)shoplite

   1 ms

   … 196 more identical spans …shoplite

   336 ms

   pg.query:SELECT stock (200)shoplite

   2 ms

5. 11:02

   RESOLVED

   Replaced with one JOIN: 2 queries total, p99 back to 35 ms

1. 01One query instead of N+1

   A JOIN fetches everything in one round trip. For ORMs, the equivalent is eager loading (include, JOIN FETCH, select_related/prefetch_related) or a DataLoader-style batch. Switch the scenario off; the lab's normal path has no N+1.

   const { rows } = await db.query(
     "select p.id, p.name, p.price, s.qty as stock from products p left join stock s on s.product_id = p.id order by p.id",
   );

   terminalcopy cmd

   $ curl -s -X POST localhost:8080/admin/chaos -H 'content-type: application/json' -d '{"scenario":"n-plus-one","enabled":false}'

   ── expected output ──

   {"active":[]}

1. 01Turn on Tempo's span metrics

   Tempo remote-writes generated metrics into Prometheus, which must accept remote writes: add --web.enable-remote-write-receiver to Prometheus's command. The service-graphs processor also produces the service dependency map Grafana draws.

   metrics_generator:
     registry:
       external_labels: { source: tempo }
     storage:
       path: /var/tempo/generator/wal
       remote_write:
         - url: http://prometheus:9090/api/v1/write
           send_exemplars: true
   
   overrides:
     defaults:
       metrics_generator:
         processors: [service-graphs, span-metrics]

2. 02Database calls per request — graph it, alert on a jump

   Normally ~1.3 for ShopLite (1 per product listing, 2 per checkout). N+1 shows up immediately as this ratio climbing with catalogue size.

   PromQL· Prometheuscopy

   sum(rate(traces_spanmetrics_calls_total{service="shoplite", span_kind="SPAN_KIND_CLIENT", span_name=~"pg.query.*"}[5m]))
   /
   sum(rate(http_request_duration_seconds_count{job="shoplite"}[5m]))

   result

   {}  1.31

Run each one against your lab before revealing the answer. Share your own version in the comments; there's usually more than one correct query.