View Maintenance

Views Are Fragile to Schema Drift

A view is only as stable as the base table(s) it depends on. Because a view stores a query, not data, any change to the underlying schema that the query relies on can silently or loudly break it.

Dropped or Renamed Columns

sql
CREATE VIEW high_earners AS
SELECT id, name, salary FROM employees WHERE salary > 80000;

If someone later runs:

sql
ALTER TABLE employees RENAME COLUMN salary TO base_salary;

what happens to high_earners depends on the engine:

• PostgreSQL raises an error immediately at rename time if a view depends on the column (ERROR: cannot rename column salary ... because other objects depend on it — unless you CASCADE, which is not offered for renames the same way as drops, so PostgreSQL actually blocks this by default and requires the view to be handled first).
• Some engines instead let the rename succeed silently, and the view only breaks the next time it's queried, producing a runtime error like column "salary" does not exist — a much more dangerous failure mode because the break isn't discovered until a report runs (potentially days later) or, worse, a monitoring job silently starts returning errors that get swallowed.
• A dropped column referenced by a view is similarly caught immediately by some engines (dependency tracking) and deferred to query time by others.

CREATE OR REPLACE VIEW for Safe Redefinition

When a schema change is planned, the correct maintenance pattern is to update the view's definition before or alongside the schema change, using CREATE OR REPLACE VIEW rather than DROP VIEW + CREATE VIEW:

sql
-- Step 1: base table renamed salary -> base_salary
ALTER TABLE employees RENAME COLUMN salary TO base_salary;

-- Step 2: fix the view definition without losing its grants
CREATE OR REPLACE VIEW high_earners AS
SELECT id, name, base_salary AS salary   -- keep the view's external contract stable
FROM employees
WHERE base_salary > 80000;

Aliasing base_salary AS salary here preserves the view's external contract — client applications that expect a salary column keep working — even though the underlying physical column was renamed. This is a key reason views are valuable for maintenance: they can absorb schema churn on the base table without forcing every consumer to change.

Why CREATE OR REPLACE Beats DROP + CREATE for Maintenance

If a view has been granted to a dozen roles (GRANT SELECT ON high_earners TO staff_role, hr_role, dashboard_role, ...), running DROP VIEW high_earners; typically removes all of those grants, since they were attached to the view object which no longer exists. Recreating the view means someone must remember to re-run every GRANT — an easy step to miss, potentially leaving a formerly-granted role locked out (or, if grants are re-run carelessly, opening access more broadly than intended). CREATE OR REPLACE VIEW sidesteps this entirely by updating the same object in place.

Dependency Tracking

Well-behaved engines maintain a dependency graph (e.g., PostgreSQL's pg_depend) so that:

• Attempting to DROP TABLE employees fails if high_earners depends on it, unless CASCADE is specified (which would also drop the view).
• Tools can query system catalogs (information_schema.view_table_usage, pg_depend, etc.) to find every view impacted before making a risky schema change — a critical step in real-world schema migrations.

Edge Cases

• *`SELECT ` views are especially fragile-in-a-different-way**: adding a column to the base table changes the view's output shape unexpectedly (extra column appears), while removing one breaks it — explicit column lists are safer for long-term maintenance even though they require updating the view when new columns should be exposed.
• Nested views compound the blast radius of a schema change — a change to employees can break high_earners, which breaks any view built on high_earners, and so on (see 19.8).
• Testing before schema changes: mature teams run a "what depends on this column/table" query against the catalog before any ALTER TABLE/DROP COLUMN in production.

Key Takeaways / Interview Q&A

Q: What's the danger of a base table column being dropped when a view references it, in engines without eager dependency checks? A: The view can continue to exist without error until it's actually queried, at which point it fails — a silent, delayed failure that's easy to miss until a report or job breaks in production.

Q: Why prefer CREATE OR REPLACE VIEW over DROP VIEW + CREATE VIEW when fixing a broken view? A: DROP VIEW typically removes grants attached to the view object; CREATE OR REPLACE VIEW updates the definition in place and preserves those grants.

Q: How can a view help absorb a column rename without breaking client applications? A: By aliasing the renamed column back to its old name in the view's SELECT list (e.g., base_salary AS salary), keeping the view's external contract stable even as the physical schema changes underneath it.