Zero-downtime schema migrations in Postgres using Reshape

I&#x27;ve used a similar custom technique in the past, but only been able to deal with it from a read perspective. As a result I had to split schemas into read and write schemas which turned out wasn&#x27;t too much off an issue since it was a data warehouse.<p>Read schemas were composed of mostly read objects: views, functions, procedures, external tables (backed by S3) and in some cases tables that were filled with static data (CSV&#x2F;JSON&#x2F;TSV from source-control ingested&#x2F;copied into table). Views, functions, etc... all pointed out to a write schema that was managed traditionally (Flyway, CLI&#x2F;Docker, etc).<p>If the read schema changed, I (CI&#x2F;CD) would build out a new schema with the changes, leaving the existing schema unchanged. Every read schema was immutable and idempotent. In fact I would build a docker image with all of the DDLs and specific deployment tool (specific DB CLI version), publish and tag it. Worked perfectly for CI&#x2F;CD (or manually if all else went wrong).<p>There were two options of making the new schema &quot;live&quot;:<p>1) Swap old and new schema (not all DBs have this functionality). Swap back if something goes wrong. 2) Schemas are treated like artifacts with versions. It&#x27;s up to the applications to decide which version they need to use, or matches their read service contracts. Swapping back to previous version goes hand in hand with the application release&#x2F;rollback. Great for blue&#x2F;green. Old schema would get dropped after a certain amount of time and sign-off.<p>This worked great, but having to split read and write schemas (even for data warehouses) and having two different database migration techniques eventually becomes problematic.<p>I really like your idea&#x2F;approach of using a new schema for your new table, and adding triggers to your old table until you complete the migration. Do you have a way to rollback (without having to create a new migration that undoes the current migration)?<p>The only idea that I&#x27;ve come close to having about solving the write problem is by encapsulating write activities in a procedure that dual-writes to both old and new schemas&#x2F;tables (hardly original). Problem with this is that for blue&#x2F;green to continue working, it has to ensure that dual-write works in both schemas for a while (old--&gt;new, new--&gt;old) and there&#x27;s the potential for split-brain. Moreover, adding dual-write capabilities to existing procedures means that the schema is no longer immutable. The whole process becomes more complex overall.

Is there any migration scenario that you anticipate your view strategy won&#x27;t be able to handle? Maybe add a &quot;Limitations&quot; section? Again..congrats. I am impressed and I hope someday this becomes part of PostgreSQL native feature set.

Important: Note: Reshape is experimental and should not be used in production. It can (and probably will) destroy your data and break your application.

I&#x27;ve had some issues in the past with queries on top of views not properly using indexes in postgres. I wish I&#x27;d saved some examples but I&#x27;m now quite cautious about putting a lot of views between my queries and my tables (though the examples I had were not simple views, but views that joined several tables together). Are the views only used during the migration or are they used all the time?

This is very interesting, thanks for sharing. I had been noodling on the concept of zero downtime schema changes in postgres as well, and recently started <a href="https:&#x2F;&#x2F;github.com&#x2F;shayonj&#x2F;pg-online-schema-change" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;shayonj&#x2F;pg-online-schema-change</a>. Its inspired by pg_repack and pt-online-schema-change (mysql).<p>I will give this a spin! Kudos

This is so cool; wish I had this a few years ago when I implemented most of these features by hand on a per-migration basis!<p>I was using sqitch which worked well, but was thinking along the same lines as the author here, &quot;can&#x27;t this be automated?&quot;.<p>It&#x27;s really nice to see both a confirmation of my approach and someone willing to explore the solution space.

Wow! I love how elegant and powerful your strategy is. There is a lot of potential here and your tool might turn PostgreSQL into an even easier choice for new projects. Congratulations!

IMHO this is the opposite to the ideal approach. The current schema version should contain tables and the old schema versions should contain views. Using the current form of Reshape is asking for maintainability and performance problems down the road as its approach doesn&#x27;t let you easily drop old schemas and change indexes as new needs emerge.

This looks really nice and I&#x27;m certainly going to try it out soon!<p>One thing that is not that clear to me, what the vision is on how to use Reshape once it is production ready. Is it meant to be used exclusively to handle you db schema (e.g. replacing alembic, sqitch, etc. completely) or alongside such tools to handle difficult migrations?

Interesting, will give this a look. Have been digging into zero downtime migrations quite a bit lately.

I am curious what is issue with existing migration tools? There are so many tools out there. What is the problem they are not solving?

How do INSERTs work if you are migrating from `full_name` to `first_name` - `last_name`?