# Exercise 2: Diagnose a Hot Partition

## Scenario

`ShiftLog`, a workforce-scheduling product, partitions its `shift_events` table by `LIST` on `company_tier`, a column with three values: `'enterprise'`, `'business'`, `'starter'`. This was chosen eight months ago because, at the time, the three tiers had roughly equal event volume and the support team's most common query was "show me every event for companies on tier X" (used for tier-specific SLA auditing).

Since then, the product has grown almost entirely through its self-serve `'starter'` tier. Current state:

- `starter` partition: 1.8 billion rows, receiving ~95% of all writes and ~70% of all reads
- `business` partition: 140 million rows, stable
- `enterprise` partition: 60 million rows, stable, but every enterprise customer has a strict contractual dashboard-latency SLA

Symptoms reported by the on-call team over the last month:
- Write latency into `shift_events` has climbed from ~4ms to ~80ms p99, company-tier-independent (i.e. even `enterprise` writes are slow now).
- The original "audit by tier" query is now rarely run — the support team migrated to a per-company audit query (`WHERE company_id = ?`) over a year ago, but nobody revisited the partitioning scheme when that happened.
- Autovacuum/maintenance windows on the `starter` partition alone now take longer than the full table used to take before partitioning was introduced.

## Deliverables

1. **Name the failure mode precisely**, using theory section 5's terminology, and explain mechanically why partitioning by `company_tier` made this specific outcome possible (why doesn't a partition scheme "protect" one partition from a growth pattern like this?).
2. **Explain why write latency degraded for *all* tiers**, not just `starter` — tie this to what a single oversized partition does to shared resources (buffer cache, autovacuum scheduling, WAL volume) even though partitions are logically separate.
3. **Propose a new partition key**, justified against the *current* real query pattern (`WHERE company_id = ?`), not the original one — per theory section 4's rule about matching the highest-volume real queries, and section 6's pitfall #1 about not revisiting a key after query patterns change.
4. **State the scheme type** (range/list/hash) for your new key and justify it specifically against the risk of simply recreating a different hot-partition problem (e.g. what happens if hashing `company_id` isn't evenly distributed because a few companies dominate row count).
5. **Describe one operational step** required to actually migrate from the old scheme to the new one without a prolonged full-table lock, at a high level (you do not need exact DDL).