You own an OLTP service whose hottest query filters on a tenant column where one tenant holds roughly 80% of the rows. The statement is parameterized, plans are shared across tenants, and latency is bimodal. How would you decide between forcing re-planning per execution, splitting the statement, or reshaping the data - and what would you measure to justify the choice?

## Framing the decision The symptom - bimodal latency on a shared plan over a skewed predicate - is a parameter-sniffing outcome, but at this scale the interesting question is not "what is the mechanism" but "what is the right system design". Three families of answer exist, with very different costs and half-lives. ## Step 1: quantify before choosing Nothing here can be decided from principles alone. The numbers that determine the answer: - **Traffic mix.** Executions per second for the whale tenant versus the long tail. A 100:1 call-rate ratio points to a different answer than a 1:1 ratio, even with the same row skew. - **Plan cost matrix.** Run each class under each candidate plan (by testing with literal values, or by forcing each plan) and record actual runtimes. You need four numbers: small-tenant-under-selective-plan, small-tenant-under-scan-plan, whale-under-selective-plan, whale-under-scan-plan. The asymmetry in that matrix tells you how much a wrong plan actually costs. - **Optimization cost.** Measure CPU per compile for this statement. Multiply by the call rate to price the re-plan-always option honestly. - **Current shared-resource health.** Plan-cache compile rate, cache contention waits, CPU headroom. If the server is already near the compile-contention knee, per-execution planning is off the table regardless of its accuracy benefit. - **Distribution trajectory.** Is the whale growing? Are there several emerging whales? A rule that names one tenant rots quickly if the answer is yes. ## Step 2: evaluate the options against those numbers **Re-plan on every execution.** Always the right plan; cost is `compile_cpu x call_rate` plus contention. For a 200 microsecond OLTP lookup at 5,000 calls per second, a 1 ms compile is a 5x cost increase and a throughput ceiling - disqualifying. For a query executed a few times a minute, it is free and correct, and I would take it immediately. The honest rule: per-execution planning scales with call rate, and OLTP call rates are exactly where it does not fit. **Force a single generic plan.** Removes the lottery and makes latency predictable, which has real operational value: predictable is often more valuable than fast. But it is only acceptable if the plan cost matrix shows the middle-ground plan is *tolerable* for both classes. With 80/20 skew it usually is not - the average describes no real tenant. **Split the statement.** Give the heavy class its own statement text so the cache holds two entries, each compiled once and each stable. Benefits: one compile per class, no dependence on which value compiled first, explicit intent in the code, and independent monitoring per class. Costs: an application change; a routing predicate that must be maintained as the tenant distribution shifts; and mild duplication in the data-access layer. Mitigate the rot by deriving the routing from a maintained size threshold rather than a hardcoded tenant identifier. **Reshape the data.** Partition by tenant so the whale's rows live separately and per-partition statistics describe each class honestly; or move the whale to a dedicated shard or database. This eliminates the plan-choice problem as a side effect and simultaneously addresses the noisy-neighbour issues that a whale tenant always brings - buffer-cache dominance, lock contention, backup and maintenance windows. Costs are the largest: migration, routing at the service layer, operational complexity, and possibly cross-tenant query paths that no longer work. **Improve the estimates.** Cheapest to try and worth checking first: ensure a histogram exists on the tenant column with enough resolution to represent the whale as a most-common value. Sometimes the optimizer is choosing badly only because it cannot see the skew, and better statistics plus a value-specific planning mode resolves it without structural change. This rarely suffices at 80% skew but costs an hour to rule out. **Pin a plan.** Fast, brittle, and it freezes a decision against today's data while hiding future regressions. Acceptable as an incident mitigation with an explicit expiry, never as the design. ## Step 3: sequence the response A credible principal answer separates timescales: - **Now (hours):** confirm the diagnosis, check statistics/histogram quality, and mitigate - force the plan that protects the majority of traffic, or force re-planning if the call rate can absorb it. - **Next (days):** ship the statement split, with per-class latency metrics and a documented routing rule. - **Later (quarters):** decide whether the whale justifies partitioning or physical isolation, driven by its growth curve and by whether other whales are emerging. ## Step 4: define success and guard it Declare the target explicitly - for example, p99 per tenant class rather than a global p99, because a global percentile hides exactly this failure mode. Then add guardrails: alert on plan changes for the hot statements, on compile-rate spikes, and on per-class p99 divergence. The failure you are engineering against is not "slow query"; it is "a plan silently flipped and one class of users fell off a cliff", and only per-class observability catches that early. ## The judgement being tested That you price optimization CPU against call rate rather than assuming re-planning is free; that you recognise splitting as converting a runtime gamble into a design decision; that you know reshaping fixes more than plan choice; and that you attach a measurement and a rollback story to whatever you pick.