Physical optimizer

The physical optimizer decides what lookup structures exist, based on what the workload actually does. It has two parts. Part II, the Cabin controller, is built: it creates, extends, repairs and drops Cabins on its own, and it is off by default. Part I, relayout, is shadow-only: it reports how rows could be moved and moves nothing. Both act only on structures that cannot change a query result, so a wrong decision costs performance, never correctness.

The idea

A query optimizer chooses how to run a statement over the structures that exist. KDBS’s query plan is fixed by the statement’s shape (see Statements and execution plans). The physical optimizer works one level down: runtime statistics change what exists, such as which columns earn a Cabin and when a Cabin has stopped paying for itself.

Shared parts

The decay score

The engine has one way to measure “how hot is this, now”. A score is two numbers, score and last_bump. Its value at time t is score × 2^(−(t − last_bump) / half_life). A touch decays the score and then adds to it; a read only decays it. There is no background pass, so cold scores cost nothing until read. The arithmetic is fixed-point, with no floating point. decay_half_life (default 600 s) sets the half-life for the whole instance.

The page epoch

Every page header carries an epoch counter, bumped when the page’s rows move. Waystone trail entries and Cabin location hints record the epoch they saw. A mismatch makes the location a miss, and the reader takes the normal path. An epoch match alone never accepts a location: the primary-key check at the slot still runs.

Part II: the Cabin controller

The Cabin controller is one background controller for the instance. It decides which Cabins should exist, using only what the workload did. It manages only Cabins it created itself (origin=auto). It never touches a Cabin you declared, or a Bound Cabin that backs an assertion.

Inputs and actions

It reads exactly three signals:

1. S1: how often each statement shape runs, under the decay score.
2. S2: how many pages each execution scans.
3. S3: Cabin quality: location-hint hits and failures, and lookups that found their value unobserved.

It has four actions: CREATE, EXTEND (observe more values), HEAL (re-check location hints in a batch) and DROP.

The decision

Benefit and cost are both measured in page accesses, in 16.16 fixed point:

Benefit  B(c) = sum over shapes i of  f_i × max(0, P_scan,i − P_cabin)
Cost     C(c) = P_rel / T_amort  +  h_fail × f_lookup × k_heal

f_i is the decayed frequency of a shape the Cabin would serve, P_scan,i the pages it scans without the Cabin, P_cabin the pages a Cabin lookup touches (2), P_rel the table’s pages, T_amort the amortization window, h_fail the hint failure rate, f_lookup the decayed lookup frequency, and k_heal the pages per heal (2).

Action	Condition (defaults)
CREATE	B > 3 × C for 3 consecutive snapshots, and the page budget admits it
EXTEND	More than 20% of lookups miss coverage, and the missed share alone clears the create bar
HEAL	Hint failure rate above 10% while the Cabin still pays for itself
DECAYING	B < 0.5 × C
DROP	Still decaying after the cooldown (128 half-lives), or a HEAL did not restore quality
Back to ACTIVE	B > 3 × C again

The wide gap between the drop threshold (0.5) and the create threshold (3), plus the confirm count and the cooldown, stops the controller from creating and dropping the same Cabin over and over.

CANDIDATE --create--> BUILDING --built--> ACTIVE --B < 0.5 C--> DECAYING --cooldown--> DROPPED
    ^                    |                   ^                      |
    +--- interrupted ----+                   +----- B > 3 C --------+

Safety properties

• Decide is a pure function. It maps an immutable statistics snapshot to a set of actions, with no I/O and no clock reads. A separate step applies the actions. Seeded replays through checked-in traces test that the decisions are deterministic.
• Builds use the scan ring, a small set of buffer frames reserved for scans, so a build cannot push the foreground working set out of the buffer pool.
• The budget is a ranking. When the page budget is full, a new Cabin is admitted only in exchange for dropping the active Cabin with the lowest net benefit, and only if the newcomer’s net benefit is at least twice as large.
• Kill switch. SET CABIN_OPTIMIZER OFF stops new decisions and in-flight builds. It leaves existing Cabins as they are.

Cooldown and quiet periods

A dead Cabin and one whose workload is quiet overnight send the same signal: no lookups. Only waiting longer than the quiet period tells them apart, so the drop at the end of the cooldown is a timeout, not a judgement. At the default half-life of 600 s, 128 half-lives is 21 h 20 min, longer than a market’s roughly 17.5 h close-to-open gap. Lowering the cooldown below one night’s worth of half-lives retires live Cabins. A 24/7 workload with no quiet period is the case that can safely lower it.

Measured

These numbers come from tools/cabin_optimizer_benchmark.py and a scenario driver, measured on 2026-08-10 on an engine version before v3.0.0’s architecture change. The project states that numbers from that engine do not carry over to the current one.

• Idle cost: with nothing to decide, the controller’s tick cost 2 to 3 µs of CPU, and on/off latency differences stayed inside the same-configuration noise. Conditions: cores = 1, durability = group, EC2 with 2 × AMD EPYC 7571, data on xfs (bench/results-cabin-optimizer.md, at commit f1d59b5).
• Benefit: on a hot non-key equality matching 10 rows in a 10,000-row table, the Cabin the controller created gave 10.9 times lower client p50 latency (1,439 µs to 132 µs) and 19 times lower server CPU. It was created 1.4 s after the switch was turned on, with the snapshot interval lowered to 500 ms, and the served reply was verified byte-identical to the scanned one. At 1,000 rows the gain was 1.97 times, and at 200 rows 1.17 times. Same file and conditions.
• Three business days with hot values rotating nightly: day-1 throughput was 608 TPS with the controller off, 1,680 with it on, and 1,724 with the five right columns declared by hand. Conditions: cores = 1, durability = group, snapshot interval 500 ms, decay_half_life = 5, EC2 with 2 vCPU, data on xfs, run 2 of 2 (bench/results-cabin-optimizer-days.md, at commit 7200fa2).

Observing the controller

SHOW CABIN_OPTIMIZER prints a header line, then one line per managed Cabin:

cabin_optimizer=<on|off> managed=<n> pages_committed=<n> page_budget=<n>
  ticks=<n> creates=<n> extends=<n> heals=<n> drops=<n> deferred=<n> failures=<n>
rel=<name> column=<name> state=<CANDIDATE|BUILDING|ACTIVE|DECAYING>
  cabin_id=<n> pages=<n> streak=<n> benefit_q16=<n> cost_q16=<n>
  [hint_fail_pct=<n> coverage_miss_pct=<n>] last_action=<s> reason=<s> epoch=<n>

On a sandbox instance with the default settings:

kdbs> SHOW CABIN_OPTIMIZER
cabin_optimizer=off managed=0 pages_committed=0 page_budget=1024 ticks=0 creates=0 extends=0 heals=0 drops=0 deferred=0 failures=0

The counters count actions applied. last_action is the newest decision, so a decided CREATE next to creates=0 means the apply step deferred or was refused. benefit_q16 and cost_q16 are the last scores in 16.16 fixed point, refreshed every pass. ANALYZE marks a probe served by a managed Cabin with cabin_optimizer=true. With cabins = off, the view answers CABIN_OPTIMIZER absent (cabins = off).

Part I: relayout (shadow-only)

Relayout would move rows to make pages denser or hotter rows closer together. No mover exists. The planner observes, plans and reports, and it moves nothing.

• sys.access_stats records one row per access shape, (kind, table, columns). It is keyed by columns, never values: WHERE flag = 1 and WHERE flag = 2 are one shape. SHOW ACCESS lists it.
• SHOW RELAYOUT reports, per table, each shape with its raw count and decayed weight, and one line per candidate plan (compact, cluster or defrag) with its predicted benefit and the gate blocking it. SHOW RELAYOUT <table> also counts pages, live rows and delete-marked rows.
• The planner runs only when SHOW RELAYOUT asks. It has no background task and costs nothing at idle.

Each plan kind is blocked by a named gate:

Plan	Would	Blocked by
compact	Drop delete-marked rows no reader can see and reclaim pages	No purge of a user table’s delete marks consumes the reader horizon yet
cluster	Put a hot set of rows on fewer pages	It would break the between-page key order that range pruning relies on
defrag	Rewrite a table onto contiguous pages	Page reuse across tables would let trail validation pass wrongly for a live table

The planner builds plans only for heap tables. Heap tables can no longer be created, so for every table created since then it proposes nothing:

kdbs> SHOW RELAYOUT
relayout_relations=4
rel=airlines clustered=btree shapes=1 walk_weight_q8=0
shape kind=Lookup columns_mask=0x1 uses=3 weight_q8=768
plans=none reason=btree-outside-v1-mover-scope
rel=passengers clustered=btree shapes=1 walk_weight_q8=0
shape kind=Lookup columns_mask=0x1 uses=8 weight_q8=2048
plans=none reason=btree-outside-v1-mover-scope
rel=flights clustered=btree shapes=1 walk_weight_q8=0
shape kind=Lookup columns_mask=0x1 uses=3 weight_q8=768
plans=none reason=btree-outside-v1-mover-scope
rel=tickets clustered=btree shapes=0 walk_weight_q8=0
plans=none reason=btree-outside-v1-mover-scope

The Lookup shapes on airlines, passengers and flights are the foreign-key checks made when the sample flights and tickets rows were loaded.

plans=none states jurisdiction, not a clean bill of health.

Configuration

Key	Default	Effect
cabin_optimizer	off	Turns the Cabin controller on. Also SET CABIN_OPTIMIZER ON or OFF at runtime.
cabin_optimizer_page_budget	1024	Pages all managed Cabins may use together.
cabin_optimizer_theta_create_pct / _drop_pct / _swap_pct / _extend_pct / _heal_pct	300 / 50 / 200 / 20 / 10	Thresholds, in percent. Validation requires drop < 100 < create.
cabin_optimizer_confirm_snapshots	3	Consecutive snapshots a CREATE must hold for.
cabin_optimizer_amort_windows	64	Half-lives a build cost is spread over. 0 is refused.
cabin_optimizer_cooldown_half_lives	128	Half-lives a decaying Cabin gets before it is dropped. 0 is accepted.
cabin_optimizer_snapshot_interval_ms	10000	Decision cadence.
physical_optimizer	shadow	off or shadow for relayout. on is refused at startup, naming the three gates.
decay_half_life	600	Seconds for an untouched score to halve. 0 is refused.
access_statistics	on	Records access shapes into sys.access_stats. Off leaves the report stale, not wrong.

See Server configuration.

Limitations

• The Cabin controller is off by default. Until you turn it on, a CABIN AUTO column behaves like one with no policy. Declare CABIN when you want a Cabin now.
• Its managed state and decision log are in memory and rebuilt by observing traffic after a restart.
• A drop at the end of the cooldown is a timeout, not evidence that the Cabin is dead.
• Builds decline while any transaction is in flight, so the controller may rarely build on a busy instance (see Cabins).
• SHOW CABIN_OPTIMIZER on another core can block that core for the length of a Cabin build (known gap).
• It manages Cabins only. It creates and drops no index, and accepts no hints.
• Relayout is not built. It reports and moves nothing, and it reports nothing for tables created since heap storage was suspended.

See also

• Cabins
• CREATE CABIN, DROP CABIN
• SHOW and DESCRIBE
• ANALYZE
• Inspect the workload
• Known gaps