Schema Change During Peak Season
A travel company's Campaign instance holds 60M recipient records and a custom booking schema with 400M rows, linked to recipient. Two weeks before peak booking season, marketing asks for a new segmentation attribute on recipient (a computed loyalty tier) and a new field on booking (channel of booking). The DBA notes that adding a column to booking on this instance historically caused a table rewrite that locked writes for 40 minutes. The loyalty tier can be derived from existing booking history via a workflow. There is a change freeze in 10 days. The instance has a staging environment that mirrors production schema but not data volume.
1. Recommend what ships before peak season and what waits, with justification.
Read the worked answer
Ship before peak: the loyalty tier - but implement it as a computed workflow attribute written to an existing spare field or a small side table, not as a new column on the 400M-row booking schema. The tier is derived from booking history, so a scheduled workflow can compute and persist it without a table rewrite on the largest table. This delivers the marketing need with near-zero schema risk.
Defer: the new booking.channel column. On this instance, adding a column to booking triggers a 40-minute write lock. Doing that inside a 10-day freeze immediately before peak season is an unacceptable risk: if the rewrite overruns or fails, the largest table is unavailable during the highest-revenue window.
Why not just do it in staging first? Staging mirrors schema but not volume, so the 40-minute lock - which is a function of row count - will not reproduce. A staging test would falsely pass.
Decision rule: changes whose risk scales with data volume do not go in before a peak with a freeze. Changes that are additive, reversible, and volume-independent can. The tier work qualifies; the column does not.
2. Marketing pushes back, saying the booking channel is needed for peak campaigns. What is the defensible compromise, and what must be true for it to be safe?
Read the worked answer
Compromise: capture booking channel without altering the 400M-row table before peak. Options, in order of preference:
- Side table keyed by booking ID. Create a new small
booking_channelschema linked tobookingby ID, populated by an inbound workflow from the source system. New tables are cheap to create; the risk was in rewriting the existing large table. Joins at query time add cost, so restrict use to the specific campaigns that need it. - Derive channel from existing data if the source system already records it somewhere reachable - avoids new storage entirely.
- Defer and schedule the column for the first post-peak maintenance window, with a rehearsed rollback plan.
Conditions that must hold for option 1 to be safe:
- The side table is populated by a workflow that is idempotent and can be re-run, so a partial load is recoverable.
- The campaign queries joining to it are tested at production-like volume - this is where staging's lack of data volume bites, so load-test the join against a production-sized copy or a sampled extract.
- The join does not become a mandatory step in high-volume nightly builds; if it does, you have reintroduced a performance risk in a different place.
- A named owner accepts the added query complexity as temporary debt with a removal date after the real column ships.
If none of these can be guaranteed inside the freeze window, the honest answer is that the channel attribute ships after peak.