Using PostgreSQL views to ensure backwards-compatible, non-breaking migrations

Photo by Karl Paul Baldacchino on Unsplash

TL;DR

You can use PostgreSQL views to rename tables and columns in a backward-compatible way.

Renaming a table from foo to bar:

Step 1:

CREATE VIEW bar AS SELECT * FROM foo; 
-- creates an "alias" for the table: 
-- you can now address it as foo (real table) or bar (table alias)
-- it will point to the same table

Step 2:

Change the code to use bar instead of foo and deploy

Step 3:

Once you have decommissioned the old code that references the table as foo , you can run a (transactional!) migration with

DROP VIEW bar;
ALTER TABLE foo RENAME to bar;

Works also with renaming columns in a similar fashion !

General principle for non-breaking / backwards-compatible / no downtime migrations

Why is it important to have backwards-compatible migrations ?

The alternative is to wait for ongoing processes to finish and then shut off your app, run your migration, and then restart your app. While this may be acceptable for non-critical, low traffic systems, it is generally not something you can afford to do.

Even if you can reasonably introduce downtime during your migrations, doing so discourages you to adopt continuous deployment practices: if your deployments degrade user experience by causing downtime, you’ll be incentivized to deploy less.

Additionally, backwards-compatible migrations are much less stressful to deploy, even if they require a little more upfront work. In the chance that something goes wrong, it’s less likely to break production.

Introduction to Views

One can think of views as named queries.

Instead of having to explicitly having to write this query everytime:

SELECT column1, column2 FROM some_table LEFT JOIN some_other_table ON condition;

You can create a view:

CREATE VIEW my_complex_query AS SELECT column1, column2 FROM some_table LEFT JOIN some_other_table ON condition

and operate on my_complex_query directly.

We can also see my_complex_query as a computed table !

Renaming a table

Let’s consider a simple case where we had a table named invoices — containing billing data of our clients. This name was fine until we started handling the invoices from our suppliers in our system ! To avoid confusion, we must now express the separation between client invoices and supplier invoices. This requires us to rename our old invoices table ;).

Naive way

We can do so by running a simple RENAME:

ALTER TABLE invoices RENAME TO client_invoices;

But this would break old, still running code :(

Using views

This time, we won’t simply rename — we will also create a view to keep the old code working:

ALTER TABLE invoices RENAME TO client_invoices;
CREATE VIEW invoices AS SELECT id, ... FROM client_invoices;

That way, we can address this table as invoices OR client_invoices, and postgreSQL makes sure the two are perfectly in sync!

Make sure this migration is transactional, so your database is always in the initial state or the final state ! (i.e client_invoices is always defined either as a view or as the new name of the table)

When we finally decommission the old code, the app completely forgot about the invoices table. We can run a migration to safely remove the View:

DROP VIEW invoices;

You have now successfully renamed table invoices to client_invoices without downtime or breaking changes !

Renaming a column

Using the exact same principle, we can rename columns — the downside is we will be forced to rename the table as well (Take the opportunity to give it a more meaningful name, or use the technique again to rename it back, choice is yours!)

Naive way

Using views

Conclusion

We’ve seen two simple examples where PostgreSQL Views can help us refactor our database structure without introducing breaking changes from the code’s perspective.

You can see a code illustration of the first example (renaming table) here

For the sake of simplicity, the code in the examples was written in SELECT. For simple views such as the ones we used and for PostgreSQL 9.3+ — UPDATE, INSERT, DELETE statements also work !

See postgreSQL doc for more info:

A view is automatically updatable if it satisfies all of the following conditions:

The view must have exactly one entry in its FROM list, which must be a table or another updatable view.

The view definition must not contain WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clauses at the top level.

The view definition must not contain set operations (UNION, INTERSECT or EXCEPT) at the top level.

The view’s select list must not contain any aggregates, window functions or set-returning functions.

Keep in mind that views are just virtual tables that can be defined by any SELECT query. Depending on the use case, you can use them to handle much more complex refactoring operations:

• splitting tables,
• regrouping them,
• moving tables or columns around,
• organizing them in domain-bounded schemas

and make your database schema (almost!) as malleable as your code.

There are many other use cases to views I find useful that I didn’t mention in this article, for example:

• We can use views as a “database API”, either to simplify a complex database model for a read use case or to expose a clean interface for downstream data consumers and avoid coupling
• We can use views for frequently used queries which we want to name with minimal overhead
• In read use cases where we can afford eventual consistency, we can use materialized views for read use cases to greatly enhance performance on expensive reads. If you have a very complex query with a lot of joins that takes a long time to load, you can cache the result in a materialized view and put as many relevant indexes as you’d like !

So I can only encourage you to use them liberally :)