Zero-downtime schema migrations in Postgres using views

I looked into this recently, and views cover like 98% of the functionality that the client app needs from Postgres. One issue I ran into was that Postgres forgets that the primary key is a primary key when pulling from a view, which breaks some queries that rely on grouping by the primary key.<p><a href="https:&#x2F;&#x2F;dba.stackexchange.com&#x2F;questions&#x2F;195104&#x2F;postgres-group-by-id-works-on-table-directly-but-not-on-identical-view" rel="nofollow">https:&#x2F;&#x2F;dba.stackexchange.com&#x2F;questions&#x2F;195104&#x2F;postgres-grou...</a> has some more info on this

Author here, thanks for reading. As has been mentioned in some comments, the article only covers half the story of how this can be used for migrations. The greater idea is to have every commit be short-lived and two-phased: apply and cleanup. A migration is first applied and once it&#x27;s no longer used by any clients, it&#x27;s cleaned up.<p>1. Apply: the new schema is created and any new data is added to the backing table.<p>2. Cleanup: the schema is deleted and any data that is no longer needed is removed from the backing table.<p>For example, to perform a column deletion:<p>1. Apply: create new schema without the column which is being deleted. Don&#x27;t change anything in the backing table.<p>2. Cleanup: delete the schema and the column from the backing table.

Interesting approach, with a pretty dark side-effect: the tech debt silently creeping in and saying forever. Apart from that, you can use any abstraction over the base schema, depending on the use-case. Renaming a column can be as simple as renaming a field in the DTO structure.<p>The interesting part is when you actually get to pay the debt back. Yes, this is not free lunch. You move fast, but you have to eventually pay the cost of the underlying storage containing all the historical schemas combined at once.<p>This is the difference between carefully planned schema migrations and abstraction-based migrations. The former takes more time planning and executing, but the end state is the reference schema. The latter keeps on extending the underlying schema and building projections over it. Over time, the system accumulates tech debt in the form of ever-expanding base schema that contains all the historical columns.

I thought this wouldn&#x27;t work with INSERTs and UPDATEs, but it turns out newer versions of Postgres automatically support updating simple views. [1]<p>[1] <a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;12&#x2F;sql-createview.html" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;12&#x2F;sql-createview.html</a> (See &quot;Updatable Views&quot;)

This might work to some extent for renaming things but doesn&#x27;t for any other kind of migration I can think of:<p>- Dropping a column doesn&#x27;t work (assuming the point of dropping it is actual deletion, rather than just hiding it)<p>- Adding a column doesn&#x27;t work either<p>- Changing a column&#x27;s nullability, default value, or data type doesn&#x27;t work<p>- Doesn&#x27;t help with adding or changing constraints or indexes

Discussion on another &quot;zero downtime postgres migration&quot; from a few days ago.<p><a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=27473788" rel="nofollow">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=27473788</a>

I don&#x27;t like really solutions which force everything into a single schema just to do migrations. They shouldn&#x27;t be that difficult.<p>In this particular case, rather than making everything a view all the time, you could just use views during a migration window to get the same effect. Replace the table with a view which has all the columns plus the new name as an alias for the old one, migrate all the clients, replace the view with the table with the column renamed. No special permanent crap necessary.

Very interesting. I like the idea of &quot;virtualizing&quot; the hard schema with views to abstract away some things.<p>View performance can be a thing at larger scale for OLTP workloads, also, the solution you propose also adds complexity since you have two schemas now instead of one, and as you rightly point out, complexity with views themselves. The question becomes when is this added complexity worth it?

I don&#x27;t remember which but there was a DB server I worked with where the tables weren&#x27;t directly accessible and required alias&#x27;s to be created, thus enforcing portability.

I think views are great way of abstracting out business logic. The one thing keeping me from using it more frequently is that it doesn’t work well with Row Level Security.