You must restructure a heavily used table — splitting it in two — while many independent read and write paths keep working. How does a view layer give you logical data independence during that migration, and where does the technique run out?
answer
- new tables → backfill → sync → atomic rename + view → migrate → drop
- reads absorb easily, writes need INSTEAD OF
- cardinality change = not derivable
- join per read: verify plans and latency
- instrument view usage before dropping
basics
~20 sCreate the new tables, then expose a view with the old table's name and shape so read paths keep working while consumers migrate. It runs out on writes (only simple views are updatable), on non-derivable changes such as cardinality shifts, on performance, and on the double-write period during backfill.
solid answer
~60 sThe technique: build the new structures, backfill, then replace the old table with a **view carrying its old name and column list**, derived from the new tables. Readers keep working unchanged, so logical data independence is purchased explicitly. Consumers migrate to the new shape on their own schedule, and the view is dropped once nothing references it. Where it runs out: - **Writes.** Only single-table, key-preserving views are auto-updatable. A view over a join needs an explicit translation — an INSTEAD OF trigger — that must handle inserts, updates and deletes, including which side to create or remove. - **Derivability.** If the split changes cardinality, no honest view reproduces the old shape. - **Performance.** A join per read can be materially slower than the original table access; plans change, and so do latency and lock footprints. - **Cutover.** Backfilling a large table while it takes traffic needs dual writes or a batched backfill plus a consistency check, and one instant where the old name changes meaning. - **Discovery.** You cannot drop the view until you can prove nothing uses it — tooling and ad-hoc access make that hard.
code
sql · 10 linesALTER TABLE customers RENAME TO customers_legacy;
CREATE VIEW customers AS
SELECT c.customer_id,
c.name,
c.created_at,
p.email,
p.phone
FROM customer_core c
JOIN customer_contact p ON p.customer_id = c.customer_id;go deeper
Know the basic idea: keep the old name as a view over the new tables so existing reads keep working while code migrates.
Lay out the sequence including backfill and atomic cutover, and note that writes need explicit handling.
Cover the write translation, backfill-under-load risks, reconciliation, plan and latency verification, and how you prove the view is unused before dropping it.
Frame it as buying logical independence at a cost: give the compatibility layer an owner and an expiry, and decide which changes justify the exercise versus a coordinated consumer migration.
## The shape of the migration Goal: split a wide table into two, with many independent consumers and no coordinated release. Logical data independence — the property that application views survive logical schema change — is not automatic here; it has to be manufactured with a view layer. The standard sequence: 1. **Create the new tables** alongside the old one. No consumer sees them. 2. **Backfill** existing data into the new shape, in batches sized to avoid long locks and replication lag. 3. **Keep them in sync** while backfill runs — dual writes from the application, or database triggers on the old table. 4. **Cut over the name.** Rename the old table aside and create a view with the original name and column list over the new tables, in one transaction so no consumer sees a missing object. 5. **Migrate consumers** to the new tables individually. 6. **Retire the view** once nothing references it, and drop the original table. Steps 4 and 5 are exactly logical data independence in action: the external schema each consumer sees is held constant while the conceptual schema underneath changes. ## Where reads work and writes do not Reads are the easy half. A view rejoining the split halves reproduces the original column list precisely, and any query that named those columns keeps its meaning. Writes are the hard half. Engines auto-update only a narrow class of views — essentially a projection and restriction of a **single** base table that preserves its key. A view over a join falls outside that class in general, so every write path must be handled explicitly through an INSTEAD OF trigger (or equivalent) that decides: - On insert: which rows to create on each side, and in what order relative to foreign keys. - On update: which side owns each column. - On delete: whether the child alone goes, or both sides. That trigger is real code with real semantics, and it becomes the place where the migration's correctness lives. It must also be idempotent and consistent with whatever dual-write path is still running. ## Where the technique genuinely runs out **Non-derivable changes.** If the split turns a single-valued attribute into a one-to-many relationship, no view can honestly present the old column: choosing one child row invents a guarantee that no longer holds. The same applies to dropping a column or narrowing a domain. These changes require consumers to change; no insulation layer can be honest about them. **Performance.** The view adds a join to every read that used to touch one table. Plans change, index strategy changes, and the lock and buffer footprint of common queries changes. A view that is correct but three times slower can still take production down, so the migration needs plan and latency verification, not just a correctness check. **Backfill under load.** Copying a large table while it takes writes is the operationally risky part: batch the backfill, bound the batch by time not just row count, watch replication lag, and finish with a reconciliation query that proves old and new agree before cutover. **The cutover instant.** Renaming a table and creating a view must be atomic, and it takes a lock that briefly blocks access to that name. Long-running transactions holding the old table can turn that brief lock into a queue, so it needs a lock timeout and a retry, run in a quiet window. **Knowing when you are done.** You cannot drop the compatibility view until nothing uses it. Application code is greppable; ad-hoc sessions, reporting tools, and scheduled jobs are not. Practical answer: instrument access to the view — log or count usage — and require an observed quiet period before dropping, rather than an assumed one. **Ownership and drift.** A compatibility view that survives without a removal date becomes permanent, and now two shapes must be maintained forever. Every such view should carry an owner and an expiry. ## The point to land Logical data independence is not a property the engine hands you the way physical independence is. It is bought, per migration, with a view layer plus explicit write translation, and it only covers changes that remain derivable. Saying that clearly — and naming writes, cardinality and performance as the three places it fails — is what distinguishes a senior answer from a textbook one.
- How do you keep old and new structures consistent while the backfill is running?Either dual-write from the application or install triggers on the old table that mirror changes into the new tables, then backfill historical rows in bounded batches. Finish with a reconciliation query comparing counts and checksums over key columns, and only cut over once it comes back clean, because a backfill that races live writes silently loses rows otherwise.
- How do you know it is safe to drop the compatibility view?Prove disuse rather than assume it: instrument access so every query touching the view is counted or logged, grep application code and job definitions, and require an observed quiet period covering the longest reporting cycle. Ad-hoc sessions and BI tools are the usual survivors, which is why observation beats code search alone.
saying these in an interview costs you the question
- Assuming a view makes writes work automatically — joined views need an explicit INSTEAD OF translation.
- Skipping backfill reconciliation and cutting over on the assumption that dual writes caught everything.
- Ignoring that the view adds a join to every read, changing plans, latency and lock footprint.
- Dropping the compatibility view based on a code grep alone, missing ad-hoc and reporting access.
- Treating a cardinality change as something a view can hide.