You inherit an OLTP table with 140 columns, most of them nullable, written by several different subsystems. What symptoms tell you it should be split, how would you split it, and what does splitting cost?

## What a god table is A kitchen-sink table accretes: one entity table gains a column per feature until it holds several unrelated concepts. The classic `users` table with 140 columns spans authentication, profile, preferences, billing, KYC, marketing consent, denormalised counters and half a dozen abandoned experiments. Width alone is not the defect - some entities legitimately have many attributes. The defect is that **several independent things are stored as one thing**, so they share a row lock, a row rewrite, a null vocabulary and an access-control unit. ## Diagnosing **Null profiling.** Count non-nulls per column. Columns populated for a small percentage of rows are candidates for extraction, and columns whose non-null sets *coincide* are the strongest signal: they form a group that is present together and absent together, which is a subtype or an optional aspect wearing a disguise. The current schema cannot state "these five are all present or all absent"; extracted into their own table, that becomes a NOT NULL per column and a single row's existence. **Write attribution.** Find which subsystem writes each column - from code search or audit data. Columns written by different services at different frequencies are separate concerns. A `last_seen_at` touched on every request forces a rewrite of the entire row, which in engines that do copy-on-write updates means the whole row is re-written and every index on it may need maintenance. **Lifecycle mapping.** Group columns by when they become populated: at registration, at verification, at first purchase, at churn. Different lifecycles mean different tables, and the extraction gives each stage a real existence marker instead of an overloaded null. **Hidden one-to-many.** Look for role-suffixed or numbered columns and for columns whose meaning is "the most recent X". These are collections flattened into the row. **Access-control review.** If some columns are sensitive - identity documents, financial detail - and others are not, one table means one grant. Splitting lets the sensitive satellite carry stricter permissions. ## How to split **First, the unambiguous wins.** Extract true one-to-many groups into child tables with a foreign key. Nobody argues about these and they remove the most confusion. **Then vertical partitioning into satellites.** Choose seams by ownership and lifecycle, not by alphabetical tidiness: ``` user(user_id PK, email, status, created_at) -- core, always read user_profile(user_id PK/FK, display_name, bio, avatar_url) user_kyc(user_id PK/FK, document_type, verified_at, ...) -- present only when verified user_activity(user_id PK/FK, last_seen_at, login_count) -- hot, high write rate ``` Each satellite shares the parent's key as its own primary key and foreign key, which makes the one-to-one relationship enforced in one direction: a satellite cannot exist without its parent, and cannot exist twice. **Keep in the core** the columns read on nearly every access path and populated for nearly every row. A satellite that every request must join is a cost with no benefit. ## The costs, stated honestly **Joins.** Read paths that wanted several groups now join. For an indexed one-to-one join on the primary key this is cheap, but it is not free, and multiplied across a chatty request path it is measurable. **Existence ambiguity moves rather than disappearing.** Previously "is this column null?", now "does the satellite row exist?". That is an improvement only if the answer is well-defined. Decide up front whether satellites are created eagerly with the parent - in which case something must guarantee it, since a foreign key only constrains the child's direction - or lazily, in which case every reader must handle absence. Writing that rule down is part of the design; leaving it to each caller reproduces the original mess. **Write coordination.** Updates that previously touched one row now touch two, inside one transaction, with a deadlock risk if different code paths take the rows in different orders. Fix by convention: always touch the parent first. **More objects.** More tables, more migrations, more mapping code. This is real but usually overstated relative to the clarity gained. ## When to leave it alone - The table is genuinely one concept with many attributes, all read together and populated for nearly all rows. - The system is being retired or replaced within a horizon shorter than the migration. - The columns are wide but cold, and the engine already stores oversized values out of line, so the row-width argument is weaker than assumed - measure before invoking it. ## Executing on a live system Create the satellite, backfill in batches, dual-write, migrate readers group by group with reconciliation queries comparing old and new, then stop writing the old columns and finally drop them. Drop last and separately: dropping columns is the irreversible step, and keeping them readable until every reader has moved is what makes the migration safe to abandon halfway. And apply the ratchet: while the migration is in flight, new features add columns to the appropriate satellite, never to the core. Without that rule the table grows faster than the split proceeds.