Publish the inbox query decision with both latency and write cost
The review has several attractive charts but no single explanation of which changes should ship together or what would trigger rollback.
- Focused work estimate
- 5h + prerequisites
- Priority in the scenario
- High
- Engineering practice
- Performance evaluation · Engineering tradeoffs
Estimated field mix
- Performance engineering50%
- Database engineering50%
Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence.
Review it, then add it to your workspace.
The board opens an editable draft; nothing is saved until you confirm it. Sign-in and workspace permissions apply, and Demo boards remain ephemeral.
Project context
A fictional maintenance service lists open work orders beside years of closed history. A query that was cheap in a small demo now scans far more rows than it returns. Recreate the schema and synthetic workload locally; no customer database or supplied fixture is assumed.
Setup prerequisites
- Create an isolated local database with tenants, work orders and assignment history.
- Seed 200,000 synthetic work orders with documented skew and a repeatable random seed; record PostgreSQL version, settings and resource limits.
Preceding work
Complete these dependencies, or supply their agreed outputs before taking this ticket.
- PQUERY-101 · Seed an inbox where one tenant owns most of the closed history
- PQUERY-102 · Capture buffer reads and row estimates for the slow inbox query
- PQUERY-103 · Count hidden assignment queries made by one inbox request
- PQUERY-104 · Batch latest-assignee lookup for the current inbox page
- PQUERY-106 · Replace deep offset paging with a stable inbox cursor
- PQUERY-105 · Evaluate a partial index for open work without slowing closure updates
- PQUERY-107 · Explain the bad prepared plan used for the largest tenant
- PQUERY-108 · Rehearse the inbox index migration while writes continue
- PQUERY-109 · Keep performance fixtures useful after the inbox schema changes
Acceptance criteria
- Compare baseline and candidate with three paired warmed runs at fixed concurrency, reporting p95, p99, query count and buffer reads.
- For the exercise require at least 20% lower median p95 for the dominant-tenant open view, no more than 10% loss in closure-update throughput and exact fixture equivalence.
- List the retained and rejected changes, noisy or inconclusive cases, migration order and rollback triggers.
Implementation constraints
- These relative thresholds are local exercise gates; report all repetitions and do not claim production improvement from synthetic results.
Verification to include
- Run both large and median tenant workloads alongside the closure-update fixture.
- Rehearse rollback and rerun correctness plus the baseline workload to confirm recovery is understood.
Deliverables
- Query-performance decision, raw run results and migration handoff
Rollout and recovery
Advance only the documented candidate within the exercise environment; keep previous query and index definitions available for a bounded rollback.
Value of the work
For the engineer: Practice reading execution plans, balancing read and write cost, and validating performance without weakening tenant boundaries.
For the team: Create reproducible query diagnostics and reversible index or query proposals for an operational application.
Evidence boundaries
Outcome Evidence: Tests, patches, and runbooks are requested deliverables. They become Outcome Evidence only through a qualified Mission and immutable Evidence IDs.
Ownership Evidence: Independent adaptation must be observed under a declared verification policy and cite immutable Evidence IDs. Completing a planning ticket establishes no Ownership Evidence.