# Exercise 1: Choose Horizontal vs. Vertical Partitioning for an Analytics Table

## Scenario

You maintain `page_events`, a single table that has grown to 2 billion rows at `EventTrack`, an analytics SaaS. Every row records one user interaction:

- `id`, `tenant_id`, `event_type`, `occurred_at` (small, always read)
- `session_id`, `user_agent`, `referrer_url` (medium, read by ~30% of queries)
- `raw_payload` (a JSONB blob, often 5-20KB, containing the full untruncated event data — read by fewer than 2% of queries, only when a customer opens a single event's detail view)

Query patterns, in order of volume:
1. **~85% of queries**: `SELECT event_type, occurred_at, session_id FROM page_events WHERE tenant_id = ? AND occurred_at BETWEEN ? AND ?` — a customer's dashboard chart for a date range.
2. **~13% of queries**: the same as above but also selecting `user_agent, referrer_url` for a breakdown-by-source view.
3. **~2% of queries**: `SELECT raw_payload FROM page_events WHERE id = ?` — a single event's detail view, opened one row at a time.

Storage is dominated by `raw_payload`: it accounts for roughly 90% of the table's total disk footprint despite being read by only 2% of queries. Tenants only ever query their own `tenant_id`'s data. Old events (older than 18 months) are queried almost never but currently cannot be dropped for compliance reasons — they must remain queryable, just rarely are.

## Deliverables

1. **State your horizontal partitioning scheme** — partition key and scheme type (range/list/hash), justified against the query pattern above and theory section 4 ("match the highest-volume real queries").
2. **State your vertical partitioning decision** — which column(s), if any, should be split into a separate table, justified against theory section 3's hot-column/cold-column reasoning and the storage-footprint numbers given above.
3. **Explain the interaction** — after applying both decisions, walk through query pattern #1 and #3 above and state, in each case, which partition(s) and which physical table(s) actually get touched.
4. **Address the 18-month-old-data requirement** — explain how your horizontal scheme does or doesn't make the "must remain queryable, but rarely is" requirement cheap to satisfy, referencing theory section 2's partition-dropping/archival point.

## Constraints

- Do not propose hash partitioning as your horizontal scheme without explicitly addressing why it would or wouldn't break query pattern #1's date-range filtering, per theory section 6's pitfall #3.
- Your vertical partitioning decision must be justified by the actual read-frequency numbers given, not by column "size" alone.