Zero Downtime Schema Changes with Vercel and Xata

Modern deployment platforms like Vercel pioneered preview deployments with immutable URLs. In this post we will explore leveraging that and making schema changes easier in the whole deployment pipeline.

Zero downtime schema changes are hard. The following issues are usual in any (or all) schema change stories:

• Some migrations require a table lock and you accidentally block writes in production. Usually, because the environment where the migration is tested doesn’t have the same scale as the production in terms of traffic and data.
• Zero downtime is hard. Your application can have several instances running for redundancy and any changes in the underlying schema changes means either you need to atomically stop and start all instances or if they roll over, some instances of the application are not compatible with the new schema changes temporarily.
• The solution to the above problem is expand contract pattern but because of the nature of this pattern, you have to split a feature into several pull requests, doubling the amount of reviews, changes in the database schema and therefore time.
• Backfilling further complicates schema changes, for larger databases, backfilling might take a while further slowing down the development cycle.

Better Schema Changes with Xata

At Xata we have experience in deploying schema changes at scale and we distilled our learning into an open source tool called pgroll. Xata platform integrates with pgroll using a simple xata roll command.

Let’s talk about the features of the platform at a higher level before diving into the code. I have mentioned some commands in this section but don’t worry too much about understand it all already. The examples later will make things clearer.

Xata platform solves the problems mentioned above by combining powerful features designed for modern development workflows.

Instant Postgres branches with xata branch create command. The branch inherits schema and data (can be anonymized with xata clone command) from a parent branch, not very different from how git works:

• This allows one to test schema changes for potential locks on realistic data size as production.
• It uses CoW (copy-on-write) for branch creation, that means the new branch only stores a diff of the changes made to it. So for N branches you don’t pay N times the amount for storage but a fraction of the cost more than a single database.

Note: You will still be billed hourly for the compute time of the branches but as we will see in the examples, we will remove the branches after the PR merge cycle. Additionally, soon we will have scale to zero. Which means compute of an unused branch will shut off automatically when unused and restart automatically when a new request comes in.

• Better yet, if you are using xata roll to create migrations, all your migrations will be lock free by default.

In addition to better locks xata roll also does the following:

• Multi version schemas, instead of changing the database schema in place. xata roll uses database views to have two views of the same database available at a time. This means that rolling over pods “see” the version of the database schema that they are compatible with.
• Once all the instances have rolled over, you can use xata roll complete command to consolidate back to a single view. This is made clear by the following image:

pgroll multiple active versions, client applications rollout

• Multiple schemas combined with pgroll migration format gives you a declarative way to implement the expand contract pattern. Because we have the ability to make different version of the app “see” different schemas, we don’t need to split our expand and contract pattern into multiple pull requests.
• The declarative migration format of pgroll also allows you to define the backfills declaratively. This means no longer writing backfill scripts and more importantly no longer patching holes for developers in your infrastructure to run these backfilling scripts.

Implementing Zero Downtime Schema Changes with Xata

Enough talking, let’s talk about how this really looks like in a real world use case application. We will show the use case where an application is leveraging Vercel’s preview deployments via the Vercel app integration.

First you need to create a xata project from xata.io UI or the Xata CLI:

Then add the following environment variables to the Vercel UI:

You can use the following Xata CLI commands to get these values:

Additionally, XATA_BRANCHNAME would usually be main (by default) and XATA_DATABASENAME would be app (by default). But you can always change these values.

With these environment variables you allow Xata CLI running in Vercel’s build environment to:

• Authenticate and create a new branch
• Use the provided branch as parent and make copies of it for the PRs.

Secondly, we will change the vercel’s build command to a custom script in Vercel’s dashboard. Note that you can also make this change in code using the vercel.json config file.

Let’s examine the contents of build.sh command:

It simply invokes build.ts via bun (alternative to node) that does the heavy lifting of creating a xata branch and setting the correct URL in the DATABASE_URL environment variable.

Let’s examine the contents of build.ts file:

In build.ts we do the following:

• Download the latest xata CLI
• Delete/create a xata branch, this will be an instant copy of the main branch that you configured via the env vars (only for branches)
• We use the xata branch wait-ready for this branch to be ready (usually in seconds)
• Then we use the xata branch checkout command to “checkout” the newly created branch
• Then we use xata roll migrate to start a migration in our preview branch to test the changes in CI and from the preview deploy
• Then we extract the connection string of this branch using xata roll url. This command gets an augmented connection string that sets the search_path to the version the current application supports (by looking at the versioned migrations folder). If you will run the same command from the main branch of the codebase, it will use the search_path corresponding to that codebase and the deployed application will always “see” the schema it is compatible with.

This is all we need to have the multi version schemas and preview branches with realistic data. Additionally, we (optionally) need the following actions that are documented here:

• check-merge-readiness.yml - We started the migration when we merge to main but we never complete it. Other PRs should see this and at one time, we should only have one migration running in the main branch. This action simply checks if something is running on the main branch and blocks the current PR from merging. You will have to execute xata roll complete against main (and you can write another action to do that that you can execute on demand)
• xata-clone.yml - you might be using Xata platform for staging and production environments. In that case, you can use xata clone command to create a anonymized copy of your production database on a nightly schedule. Additionally, you will need a way to run and complete migrations against your production database. Something that automatically happens in the build.ts shown above if you host your production with xata platform.

Conclusion

In this post, we explored the challenges of schema migrations and how the Xata platform helps streamline testing and deploying changes at scale.

Thank you for reading! We look forward to having you try the platform. If you'd like early access, you can join our private beta today.