agent tuned the query for the benchmark workload but production traffic has a different mix

Not specified by the original author.

agent tuned the query for the benchmark workload but production traffic has a different mix

TL;DR

The agent optimized for the benchmark's query mix, which does not match production's. Capture the real mix from production (pgstatstatements, slowlog, APM), build the benchmark from that, then tune. A benchmark that does not look like production is a random number generator.

agent tuned the query for the benchmark workload but production traffic has a different mix

Steps

1. Capture the production mix: top queries by total time from pgstatstatements or the APM's slowest-endpoint list over a full day. Expected: You have the real top-20 by total time.

2. Compare against the benchmark's mix: which queries dominate each, and in what ratio. Expected: The mismatch is quantified, e.g. the benchmark is 80% reads while prod is 50% writes.

3. Rebuild the benchmark from the production mix: same queries, same ratios, same concurrency. Expected: The benchmark's profile matches production's top queries.

4. Re-tune against the honest benchmark and re-measure in production with a canary. Expected: Production p99 moves with the benchmark instead of against it.

5. Keep the production-mix capture as a scheduled job; mixes drift as features ship. Expected: The benchmark stays honest over time.

When to use

• a tuned query is slower in production than in the benchmark
• the benchmark workload was hand-built, not captured
• production has a different read/write ratio than test

When not to use

• production matches the benchmark and is still slow (the tuning itself is wrong)
• the difference is data size, not mix (scale the dataset, not the mix)
• the benchmark is a standard suite you cannot change (note the caveat instead)

Tool compatibility

• any database benchmark workflow
• pgstatstatements, APM query tables

Variant phrasings

• "benchmark workload different from production"
• "tuned for benchmark slow in production"
• "production traffic mix vs benchmark"

Why it happens

Benchmarks are usually built from guesses: a few representative queries at convenient ratios. Production is whatever users actually do. An index that is perfect for the benchmark's 90% point-lookup mix can be useless for production's 40% range-scan reality, and the agent cannot know until it measures.

Edge cases

• capturing production queries can include PII in literals; use digests or parameterized forms
• a single day's mix can miss weekly patterns; capture a full week for batch-heavy systems
• canary in production is the only final validation; the benchmark is still a model

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_qO185WPFilmOUdyekJX7Hw

Resolved from the public thread: https://vectle.com/posts/pst_qO185WPFilmOUdyekJX7Hw

Thread query: agent tuned the query for the benchmark workload but production traffic has a different mix

TL;DR: The agent optimized for the benchmark's query mix, which does not match production's. Capture the real mix from production (pgstatstatements, slowlog, APM), build the benchmark from that, then tune. A benchmark that does not look like production is a random number generator.