Manage concurrency as a workload policy, not a knob
Workload Management, Queues/Warehouses/Resource Groups, Concurrency Scaling, and Noisy-Neighbor Isolation
Use queues and resource isolation to control noisy neighbors while measuring the fairness, throughput, and tail-latency tradeoff.
Learning outcomes
Define workload-management queues, resource pools/warehouses/groups, admission, service time, and noisy-neighbor interference.
Measure queue wait separately from query service time and tail latency.
Use isolation or priority only after understanding the throughput/fairness tradeoff.
Distinguish the local two-slot scheduler simulation from vendor-specific cloud workload managers.
Protect certified BI and current loads without bypassing security, correctness, or backfill isolation.
Chapter 26 begins from the accepted AtlasMart state established through Chapters 01–25: 10 current paid lines, 8 orders, 12 units, 820 USD gross revenue, 495 USD cost, and 325 USD gross profit. Source progress remains committed through sequence 208; Chapter 20 metric contracts, Chapter 21 certified marts, Chapter 22 security controls, Chapter 23 lineage/ownership, Chapter 24 tests, and Chapter 25 SLO/incident practices remain authoritative. The 360,000-row benchmark below is a clearly labeled scale fixture for performance mechanics only; it never replaces these business control totals.
Runtime: Python 3.13.5 + SQLite 3.46.1. Benchmark grain: one synthetic paid sales line per row. Scale fixture: 360,000 rows across 180 dates, 100 products, and 5,000 customers; product 1 intentionally receives about 30% of rows and North receives about 50% of customer-linked rows. Storage: a local SQLite database, WAL journal mode, temporary structures configured in memory. Timing: 3 warm-up executions then 17 measured executions; reported cache state is warm. The operating-system cold cache is not forcibly cleared, so no “cold-cache” claim is made. Security: synthetic identifiers only. Distributed limitations: SQLite does not expose distributed shuffle bytes, warehouse queues, or hardware SIMD counters; those mechanisms are explained conceptually and, where useful, modeled explicitly rather than mislabeled as SQLite measurements.
1. Realistic problem: an ELT rebuild becomes a noisy neighbor
At 09:00, one long daily-product rebuild occupies capacity while several interactive BI queries arrive. Each BI query is individually fast, but consumers wait behind admitted work. A noisy neighbor is a workload whose resource consumption degrades another workload sharing the same constrained capacity. The fix is not automatically “add more concurrency”: higher concurrency can increase contention, cache churn, spill, or cost.
2. Separate queue wait, service time, and elapsed time
elapsed_time = queue_wait + service_time# queue_wait: request -> admitted start# service_time: admitted start -> completion# tail latency: percentile over elapsed or service samples; name which oneIf the BI SQL itself takes 10 ms but waits 500 ms for a slot, SQL tuning may not fix the consumer experience. Conversely, eliminating queue wait by admitting everything can increase service time through resource contention.
3. Actual two-slot harness evidence
| Task | Queue ms | Service ms | Elapsed ms |
|---|---|---|---|
| BI2 | 0.00 | 54.79 | 54.80 |
| BI1 | 0.00 | 57.04 | 57.05 |
| ADHOC1 | 54.69 | 427.35 | 482.04 |
| BI3 | 482.19 | 51.05 | 533.24 |
| POINT1 | 535.72 | 34.95 | 570.66 |
| ELT1 | 56.45 | 1007.88 | 1064.33 |
The harness launched six tasks with only two admissions allowed. The ELT task’s repeated group-by work consumed about 1,007.88 ms of service time. Later BI/point work waited hundreds of milliseconds despite having far shorter standalone service times. This is local scheduler evidence, not a cloud warehouse capacity claim.
4. Modeled isolation: protect BI, accept a tradeoff elsewhere
| Job | FCFS queue ms | Isolated queue ms | Interpretation |
|---|---|---|---|
| BI_A | 0.00 | 0.00 | starts immediately in both |
| BI_B | 39.74 | 39.74 | waits behind earlier BI either way |
| BI_C | 394.61 | 78.49 | protected from non-BI backlog |
| ADHOC_A | 79.49 | 647.05 | pays the cost of BI isolation |
The deterministic model uses measured p95 service times multiplied by four so queue effects are easy to see. BI_C queue wait drops from about 394.61 ms to 78.49 ms, while the ad-hoc job waits much longer. Isolation redistributes latency; it does not create capacity.
5. Workload-management vocabulary across engines
| Concept | Vendor-neutral meaning | Examples of engine-specific names |
|---|---|---|
| Queue/admission class | Rules deciding when work may start. | Queue, workload class, priority, admission control |
| Resource pool | Capacity reserved/shared for a class. | Warehouse, resource group, reservation, pool |
| Concurrency scaling | Additional capacity/admission when demand rises. | Autoscaling slots/clusters/warehouses |
| Isolation | Reduce interference between classes. | Separate reservations/warehouses/pools or quotas |
BigQuery reservations are one non-prerequisite product example: Google documents reservations as resource pools that can isolate workloads. Other warehouses use different primitives and billing models. Do not copy capacity numbers or queue behavior across products.
6. Backfills and current loads need separate blast-radius thinking
Chapter 16 already established partition-aware backfills. Performance management adds a resource dimension: a large historical backfill should not silently starve the current certified load. Options include lower priority, separate resource pools, time windows, rate limits, or concurrency caps. The correct choice depends on freshness SLOs, cost, engine capabilities, and data volume.
7. Controlled failure: maximize concurrency because hardware is idle
Admitting every queued query can improve utilization briefly while worsening p95/p99 through CPU contention, memory pressure, spill, or cache eviction. The repair is a concurrency curve: measure throughput and tail latency as concurrency rises, then choose a policy from consumer SLOs and cost—not from maximum CPU utilization.
Does a dedicated BI resource pool guarantee good BI latency?
Show answer
No. It limits one source of interference. Poor SQL, bad data layout, skew, stale statistics, under-provisioned capacity, or many BI queries competing inside the same pool can still produce high tail latency.
8. Security and governance still apply
Workload pools must not become privilege bypasses. A “fast BI warehouse” still inherits Chapter 22 row/column policies and Chapter 20 metric contracts. Workload identity should be auditable so operators can attribute capacity use without embedding secrets in query text or exposing protected data.
9. Production judgment and bridge
Accept a performance change only after result semantics, security policy, freshness/history behavior, retry/replay behavior, and reconciliation still pass. Performance evidence must name the workload, dataset, engine/version, cache state, concurrency, physical layout, and percentile—not just “faster.” Treat any cost or latency number in this chapter as fixture-specific, not a universal target. Keep rollback simple: indexes/materializations can be removed, query rewrites reverted, and workload-pool assignments restored while the logical model and governed metric contracts remain unchanged.
Next: Benchmark Before/After Changes and Track Performance Regression per Metric/Data Product.
10. Verification checklist
- Queue wait and service time are measured separately.
- Noisy-neighbor evidence uses concurrent representative work.
- Isolation benefits and displaced latency are both reported.
- Capacity policy is documented as engine-specific; concepts remain portable.
- Backfills cannot starve current-load SLOs by default.
- Security and governed metric semantics survive routing to different resource pools.
Knowledge check
Check your understanding
- What is the central mechanism in “Workload Management, Queues/Warehouses/Resource Groups, Concurrency Scaling, and Noisy-Neighbor Isolation”, and which AtlasMart grain or metric contract must remain unchanged?
- Which observable evidence in this lesson distinguishes the correct design from the controlled failure?
- Which assumptions are local or engine-specific, and what must be re-checked before production use?
Review the answers
1. Preserve the lesson’s declared business grain, history semantics, governed metric definitions, and reconciliation controls while changing only the mechanism under study.
2. Use the lesson’s counts, sums, checksums, plans, traces, timing/cost calculations, or failure-state evidence—not a green task status or naming convention alone.
3. Re-check runtime/version, data scale and distribution, cache/concurrency, storage layout, security context, pricing/region where relevant, and the exact product guarantees before production adoption.
Authoritative references
- SQLite — EXPLAIN QUERY PLANOfficial interpretation of scan/search operators, index use, temporary B-trees, and the warning that plan-output format is not a stable application API.
- SQLite — Query PlanningOfficial background on table scans, multi-column indexes, sorting, and why the planner chooses among semantically equivalent algorithms.
- SQLite — EXPLAINOfficial semantics and limitations of EXPLAIN/EXPLAIN QUERY PLAN used by the local lab.
- Python — statisticsUsed for deterministic latency summaries in the local benchmark harness.
- BigQuery — Understand reservationsNon-prerequisite vendor example showing how a cloud warehouse can isolate workloads with resource pools; the Chapter 26 concepts do not require BigQuery.
11. Lab cleanup/reset
The mandatory lab is local and synthetic. Delete
ch26_lab/atlasmart_perf.sqlite and the generated
benchmark JSON, then rerun the setup script to restore the
deterministic 360,000-row fixture. No cloud resources, accounts,
paid services, or production credentials are created.