You need to split a wide table into two and rename several of its columns, but dozens of applications read it and you cannot redeploy them together. How would you use views as a facade to stage that change, and where does the approach run out?
answer
- view keeps the old name and column names
- physical schema moves, contract holds still
- reads cheap, writes need triggers or dual-write
- reassembly join can regress hot paths
- no deadline + no usage telemetry = permanent shim
basics
~20 sIntroduce the new tables alongside the old shape, then expose a view carrying the old name and column names over the new structure so existing readers keep working unchanged. Migrate consumers to new views over time, then retire the compatibility view. It runs out on writes, on plan quality, and on the fact that a shim nobody is forced to leave lives forever.
solid answer
~1 minThe staged shape: 1. **Rename the physical table** out of the way (or build the new tables beside it) and create a view with the *old* name and old column names projecting from the new structure. Readers see no change. 2. **Publish new views** expressing the target shape, and move consumers onto them one deployment at a time, tracking who still uses the compatibility view. 3. **Backfill and dual-write** if the split changes where data lives, so both shapes stay correct during the overlap. 4. **Retire** the old view once its consumer count reaches zero — verified by usage telemetry, not by asking. Where it runs out: the facade is easy for reads and hard for writes, because a view over two tables is not auto-updatable and needs an INSTEAD OF trigger, which brings its own multi-row and performance problems. The reassembling join can be slower than the original scan, so hot paths may regress. Views cannot rename what callers *write*, cannot fix semantic changes (a column that now means something different), and cannot help clients that do their own DDL introspection. And without a hard deadline and usage tracking, the shim becomes permanent — the failure mode is organisational, not technical.
code
sql · 13 lines-- new physical shape
CREATE TABLE customers_core (customer_id BIGINT PRIMARY KEY, full_name TEXT);
CREATE TABLE customer_contacts (customer_id BIGINT PRIMARY KEY
REFERENCES customers_core, email TEXT, phone TEXT);
-- old name, old column names, new tables underneath
CREATE VIEW customers AS
SELECT c.customer_id,
c.full_name AS name, -- column was renamed
k.email AS email_address,
k.phone
FROM customers_core c
LEFT JOIN customer_contacts k ON k.customer_id = c.customer_id;go deeper
Know that a view can keep the old table name and column names while the real tables change, so readers do not break.
Lay out the sequence — new tables, view with the old contract, migrate consumers, retire — and note that writes are the awkward part.
Add operational substance: dual-write and backfill during the overlap, measuring the reassembly join on hot paths, and usage telemetry before dropping the shim.
Decide whether the facade is the right tool at all — weigh reader-to-writer ratio, hot-path cost, and whether the change is structural or semantic — and attach ownership, a deadline and a retirement plan so the shim cannot become permanent architecture.
## The problem the facade solves A schema change is only hard because of coupling. If one deployable owned the table, you would change both together. With dozens of independent readers the change becomes a distributed release, and distributed releases with a big bang fail. A view breaks the coupling: because the view controls the output shape, the physical schema can move while the logical contract holds still. That converts one coordinated change into many independent ones. ## The staged sequence **Establish the new physical shape.** Create the new tables — say customers and customer_contacts after splitting a wide customers table — with the target column names and types. Populate them from the existing data. **Reconstruct the old contract as a view.** Rename the original table (customers → customers_legacy, or drop it once data has moved) and create a view named customers whose select list reproduces the original column names exactly, joining the new tables as required. Existing readers issue the same SQL and get the same shape. Column *renames* are the cheapest case here: aliasing new_name AS old_name is exact and free. **Move consumers.** Publish views for the target shape and migrate consumers individually. Each team makes a normal change on its own schedule. **Handle writes.** Reads are the easy half. If any consumer writes through the old name, the compatibility object must accept writes, and a view over two tables will not do so automatically. Options: keep writes going to the base tables directly and only shim reads; add an INSTEAD OF trigger to fan the write out to both tables; or dual-write from the application during the overlap. Each carries cost — the trigger typically fires per row and can wreck bulk paths, dual-writing needs an idempotency and reconciliation story. **Verify and retire.** Instrument who reads the compatibility view — most engines can report per-object usage — and drive that count to zero. Then drop it. A migration without a retirement step and a date is a migration that does not finish. ## Where it genuinely runs out **Writes.** This is the sharpest limit. Read facades are close to free; write facades mean triggers, and triggers move business logic into the database, degrade bulk performance, and can break ORMs that assert on affected-row counts. If the writers are few, migrating them first and shimming only readers is usually the better plan. **Performance.** The reassembly join is real work. A previously single-table scan becomes a join, and if the split moved a hot column to the other table, every hot query pays. Join elimination can rescue the cases where the second table's columns are unused — but only with declared foreign keys and uniqueness, and only for consumers that do not select those columns. Hot paths deserve measurement before, not after. **Semantics, not just shape.** A view can rename and reshape. It cannot make a column that changed meaning mean the old thing — if amounts moved from gross to net, or a status enum gained a value existing readers mishandle, no projection saves you. Semantic changes need consumer-side changes regardless, and pretending otherwise with a facade produces silent wrong answers, which is worse than a break. **Introspection and tooling.** Clients that read catalog metadata, generate code from the schema, expect writable primary keys, or rely on constraints being visible on the object they query will notice the difference between a table and a view. ORMs in particular often behave differently against views. **Feature parity.** Triggers, constraints, indexes and statistics live on tables. Anything a consumer relied on at the table level — a unique constraint they counted on, an index hint, row-level security — must be re-established, and some of it cannot be. **Organisational decay.** The most reliable failure. A compatibility view with no deadline, no owner and no usage tracking is permanent. Years later nobody knows whether it can be dropped, the "temporary" join is in every plan, and the split it enabled has become two tables that are always joined — the worst of both designs. Attach a date, an owner, a consumer list and a usage dashboard at creation, and treat the retirement as part of the same project rather than as follow-up work. ## The judgement call Use the facade when consumers are many, independently deployed, and mostly readers, and when the change is structural rather than semantic. Prefer expand-and-contract on the table itself — add the new column, dual-write, backfill, migrate readers, drop the old column — when the change is small enough that a view is more machinery than it saves. Decline the facade when the hot path cannot afford the reassembly join, when writers dominate, or when the change alters meaning; in those cases pay for the coordinated migration, or version the interface explicitly so consumers opt in rather than being silently reinterpreted. ## Interview framing Give the sequence, then spend most of the answer on the limits — writes, plan cost, semantics, tooling, and the retirement discipline — because at this level the interesting content is knowing when the pattern is the wrong tool and how it decays if unmanaged.
- Which part of this migration does the view facade help least with, and what do you do instead?Writes. A view spanning two tables is not auto-updatable, so supporting existing writers means an INSTEAD OF trigger, with per-row firing, multi-row correctness risk and affected-row counts that can break ORMs. The usual answer is to migrate the writers first — they are typically far fewer than the readers — and use the facade only for the read population.
- How do you know it is safe to drop the compatibility view?From usage telemetry, not from asking teams. Most engines expose per-object access statistics or can be instrumented with logging, so you drive the read count for that object to zero over a defined window that covers monthly and quarterly jobs. Announce a removal date up front and, if the risk warrants it, revoke access before dropping so the object can be restored quickly if something surfaces.
It is a change-of-address service. Mail addressed the old way still arrives, which is exactly what lets you move without telling everyone at once — but forwarding is not free, it cannot fix letters whose meaning changed, and nobody ever cancels it unless the service has an expiry date.
saying these in an interview costs you the question
- Assuming a view facade transparently supports existing writers as well as readers
- Believing the reassembly join is free because views have no overhead
- Using a view to paper over a change in a column's meaning rather than its name
- Creating the compatibility view with no owner, deadline or usage tracking
- Treating the physical rename as the end of the migration