Auto-Promoting Query Plan Baselines After a Canary Window

Auto-promotion after a canary window is the runbook for letting a candidate query plan become the anchored baseline only once it has survived a fixed observation period under real traffic. This guide covers the three failure signatures teams hit — promotion that never fires, promotion that fires too early, and a baseline that flaps between plans — and gives the exact thresholds, root-cause diagnostics, and copy-paste remediation (advisory-locked SQL, Python, and a scheduler config) that make canary promotion boring and reproducible.

The canary window is the safety interval between “a new plan looks good” and “the pipeline trusts it.” It exists because a plan that wins on the first execution can still degrade under a full daily traffic cycle — parameter skew, cache warming, and autovacuum all shift real latency after the first few runs. This runbook is the operational companion to automated baseline promotion and rollback; where that page defines the controller, this one is what you follow when the controller does the wrong thing.

Symptom Identification and Production Thresholds #

Each condition below is a hard trigger with exact numbers. Do not treat them as guidelines — wire them into alerts and act when they breach.

1. Promotion never fires. A candidate has accrued $canary_{executions} \ge 300$ over a window of $\ge 24 h$, its cost ratio sits at $\le 1.08\times$ the incumbent, yet its state stays CANARY for more than 6 h past eligibility. A candidate that clears every gate but never transitions is a stuck promoter, not a cautious one.
2. Premature promotion. A candidate reached PROMOTED in under 2 h of wall-clock time, or on fewer than 300 distinct executions. Any promotion faster than the configured canary_window_seconds means the window was bypassed, not satisfied.
3. Flapping baseline. The same fingerprint records more than 2 promote-then-rollback cycles within 24 h, or two adjacent transitions whose cost_ratio differ by less than 0.03. Oscillation this tight means the candidate is riding the promotion boundary with no hysteresis.
4. Clock-driven window error. The measured window length differs from promoted_at − canary_started_at by more than 120 s, indicating the window was timed against a skewed or non-monotonic clock rather than elapsed real time.
5. Concurrent-promoter contention. More than 1 controller replica logs a swap attempt for the same fingerprint within the same 5 s bucket, a sign the advisory lock that should serialize promotion is not being taken.

When condition 2, 3, or 5 fires, freeze auto-promotion for the affected fingerprint before investigating — a wrong anchor poisons every downstream comparison until it is reverted.

Root Cause Analysis #

Four failure domains produce every symptom above. Isolate which one you are in before touching config.

Window too short. The canary_window_seconds is set below a full daily traffic cycle, so a candidate promotes before it has seen its peak-hour parameter distribution. Confirm the configured window against how long candidates actually observe before promotion:

SELECT query_fingerprint,
       EXTRACT(EPOCH FROM (promoted_at - canary_started_at))::int AS observed_seconds,
       canary_executions
FROM canary_state
WHERE state = 'PROMOTED'
ORDER BY observed_seconds ASC
LIMIT 20;

Any observed_seconds well under 86400 on a promoted row means the gate is letting candidates through before a full cycle.

Missing stability gate. The eligibility check counts executions and elapsed time but never asserts the in-window p95 ratio, so a candidate that is stable on average but spikes under load still promotes. Verify the gate actually reads p95:

grep -n "p95_ratio" promotion_gate.py || echo "STABILITY GATE ABSENT"

An absent p95_ratio reference is the smoking gun for premature promotion of a plan that is cheap in cost units but slow in wall-clock latency.

Clock skew. The window is timed with a wall clock that jumped — an NTP step or a container clock reset — so promoted_at − canary_started_at no longer reflects elapsed real time. Compare the two timestamps against a monotonic execution counter:

SELECT query_fingerprint, canary_started_at, promoted_at,
       (promoted_at < canary_started_at) AS clock_went_backwards
FROM canary_state
WHERE state = 'PROMOTED' AND promoted_at < canary_started_at;

Any row returned proves the window was measured against a non-monotonic clock and the elapsed-time gate cannot be trusted.

Race with concurrent promoters. Two scheduler replicas evaluate the same fingerprint in the same tick and both attempt the swap because neither took a serializing lock. Detect the collision in the transition ledger:

SELECT query_fingerprint, to_version, count(*) AS attempts
FROM baseline_version_transition
GROUP BY query_fingerprint, to_version
HAVING count(*) > 1;

A non-empty result means two promoters minted the same version — the advisory lock in the remediation below is missing or being taken on the wrong key.

Step-by-Step Remediation #

Apply these in order. Each step is idempotent and safe to re-run.

1. Serialize promotion with a transaction-scoped advisory lock. Take a pg_advisory_xact_lock keyed on the fingerprint hash so only one promoter can act on a fingerprint at a time; the lock releases automatically at commit or rollback, leaving nothing to clean up.

BEGIN;
-- Derive a stable 64-bit lock key from the first 15 hex chars of the fingerprint.
SELECT pg_advisory_xact_lock(('x' || substr($1, 1, 15))::bit(60)::bigint);

UPDATE canary_state
SET state = 'PROMOTED', promoted_at = now()
WHERE query_fingerprint = $1
  AND state = 'CANARY'
  AND canary_executions >= 300
  AND EXTRACT(EPOCH FROM (now() - canary_started_at)) >= 86400
  AND cost_ratio <= 1.08
  AND p95_ratio <= 2.0;
COMMIT;

Expected output on a successful promotion:

BEGIN
 pg_advisory_xact_lock
-----------------------

UPDATE 1
COMMIT

UPDATE 0 instead of UPDATE 1 means the candidate did not clear every gate — inspect which predicate failed rather than loosening the whole WHERE.

2. Enforce the full gate in the promoter, including the stability check. The Python guard must assert all four conditions before it ever opens the swap transaction, so a missing p95 signal fails closed to HOLD.

from dataclasses import dataclass

import structlog

log = structlog.get_logger("canary.promoter")

CANARY_MIN_EXECS = 300
CANARY_MIN_SECONDS = 86_400
CANARY_MAX_COST_RATIO = 1.08
CANARY_ROLLBACK_P95 = 2.0


@dataclass(frozen=True)
class CanarySample:
    fingerprint: str
    executions: int
    elapsed_seconds: int
    cost_ratio: float
    p95_ratio: float


def is_promotable(s: CanarySample) -> bool:
    checks = {
        "execs": s.executions >= CANARY_MIN_EXECS,
        "window": s.elapsed_seconds >= CANARY_MIN_SECONDS,
        "cost": s.cost_ratio <= CANARY_MAX_COST_RATIO,
        "stability": s.p95_ratio <= CANARY_ROLLBACK_P95,
    }
    if not all(checks.values()):
        log.info("canary_hold", fingerprint=s.fingerprint,
                 failed=[k for k, ok in checks.items() if not ok])
        return False
    return True

Expected structured log when the stability gate is the blocker:

[info] canary_hold fingerprint=b7c2…e1 failed=['stability']

3. Time the window against a monotonic source, not the wall clock. Record canary_started_at and compute elapsed time from the database’s own now() inside the same statement, never from a client clock that can skew.

4. Pin the scheduler to one promoter tick per fingerprint. Configure the job so overlapping runs cannot both act; the advisory lock is the backstop, but non-overlapping scheduling removes the contention entirely.

canary_promoter:
  schedule: "*/15 * * * *"     # every 15 minutes
  concurrency_policy: Forbid    # never overlap a still-running tick
  starting_deadline_seconds: 120
  params:
    canary_window_seconds: 86400
    min_canary_executions: 300
    max_cost_ratio: 1.08
    rollback_p95_ratio: 2.0
    rollback_cooldown_seconds: 21600   # 6 h hysteresis against flapping

5. Add hysteresis to stop flapping. After any rollback, hold the fingerprint ineligible for rollback_cooldown_seconds (6 h) so a boundary-riding candidate cannot immediately re-promote.

Verification Checklist #

• [ ] Every PROMOTED row shows $observed_{seconds} \ge 86400$ and $canary_{executions} \ge 300$.
• [ ] The promoter guard rejects any sample missing a p95_ratio value instead of defaulting it.
• [ ] pg_advisory_xact_lock is taken on the fingerprint-derived key before the promoting UPDATE.
• [ ] The transition ledger has no (query_fingerprint, to_version) pair with count > 1.
• [ ] No promoted row has promoted_at < canary_started_at (clock monotonicity holds).
• [ ] The scheduler runs with concurrency_policy: Forbid and non-overlapping ticks.
• [ ] A rolled-back fingerprint stays ineligible for the full rollback_cooldown_seconds window.
• [ ] Fewer than 2 promote-then-rollback cycles occurred for any fingerprint in the last 24 h.

Compatibility and Engine-Specific Notes #

The gate logic is portable, but the serialization primitive that makes promotion race-free is engine-specific. Map it before deploying against anything other than PostgreSQL.

On MySQL, always pass a bounded timeout to GET_LOCK (for example GET_LOCK(fingerprint, 5)) and check for NULL/0 before proceeding, because a session-scoped lock left by a crashed connection can otherwise stall every promoter. On distributed engines, lean on the serializable transaction and the (fingerprint, to_version) unique constraint rather than any advisory mechanism, and treat serialization failures as a normal signal to retry the whole swap, not as an error to page on. For the canary shaping that produces these samples upstream, align the window here with designing canary and progressive delivery gates, and keep every fingerprint keyed off the deterministic plan hash so the lock key and the anchor agree.

Related #

• Tracking Cost Deltas Across Baseline Versions — supplies the cost ratio the eligibility gate compares against 1.08×.
• Tuning Thresholds for False Positive Reduction — the verdict stream whose stable runs accumulate as canary executions.
• Designing Canary and Progressive Delivery Gates — how the canary window feeding this runbook is defined in the delivery pipeline.
• Plan Hashing Algorithms for SQL Engines — the fingerprint that becomes both the anchor identity and the advisory lock key.

← Back to Automated Baseline Promotion and Rollback