I'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 w

I'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't too much off an issue since it was a data warehouse. 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/JSON/TSV from source-control ingested/copied into table). Views, functions, etc... all pointed out to a write schema that was managed traditionally (Flyway, CLI/Docker, etc). If the read schema changed, I (CI/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/CD (or manually if all else went wrong). There were two options of making the new schema "live": 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'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/rollback. Great for blue/green. Old schema would get dropped after a certain amount of time and sign-off. 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. I really like your idea/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)? The only idea that I'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/tables (hardly original). Problem with this is that for blue/green to continue working, it has to ensure that dual-write works in both schemas for a while (old-->new, new-->old) and there'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.