Production Query Tuning

Introduction

Tuning live queries safely takes process: capture, reproduce on a copy, fix, ship behind a feature flag, measure. Doing it wrong takes the site down.

Understanding the topic

Core concepts to understand:

• Capture exact query + bind vars from logs.
• Reproduce on prod-like data — never real prod.
• Try indexes / rewrites / partitioning / caching.
• Roll out behind a flag; monitor p95/p99.
• Be ready to roll back fast.

Syntax reference

Visual workflow / architecture:

Capture ─▶ Reproduce ─▶ Hypothesize ─▶ Fix ─▶ Measure
                                 ▲          │
                                 └──── iterate

Real-world use

On-call SQL tuning at scale (Stripe, Shopify, GitHub) follows this exact loop. The 'reproduce' step is what separates pros from cowboys.

Best practices

• Reproduce before you fix.
• Use feature flags / shadow reads when possible.
• Document the fix and metrics.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Walk through your last production query tuning.
• Q2. How do you reproduce safely?
• Q3. Rollback strategy.
• Q4. Shadow reads — what is it?
• Q5. Metrics you watch during a rollout.