sqitch migrations while "live"

[dhess]

Hi, these are possibly naïve questions! I'm new to this stuff.

Assume I've written a sqitch migration from schema version n to n+1, and that it's backwards compatible, in the sense that clients using schema version n will continue to work with a database running schema version n+1. (I realize that sqitch doesn't version schemas as sequential integers; I'm using this notation here for simplicity.)

In this situation, is it generally safe to run the sqitch migration from version n to n+1 while clients are actively using the database? In other words, imagine 10 containers each running an instance of the client application that is using schema version n. I would like to launch a new container with an instance of the client application that is using schema version n+1, but before running the application, that container would run the sqitch migration from schema version n to n+1, without first draining the other currently-running clients that are using schema version n.

On a related note, does sqitch guarantee that a migration from schema version n to n+1 is atomic? In other words, if I begin running this migration and then, while it's in progress, I start another instance of this migration, is this safe?

[theory]

In this situation, is it generally safe to run the sqitch migration from version n to n+1 while clients are actively using the database?

In general, yes, I think so. Of course it very much depends on the change. For example, if you add a new column with a default value to a table with, say, a billion rows, depending on your database engine and version, it could require a rewrite of the entire table, which would put a lot of pressure on clients and could greatly increase query latency. A better approach is to use a version of a database that dynamically assigns missing defaults at query time, like Postgres 11, or else don't include a default and dynamically assign it when missing in your n+1 clients.

But the short answer is: test it and see.

On a related note, does sqitch guarantee that a migration from schema version n to n+1 is atomic? In other words, if I begin running this migration and then, while it's in progress, I start another instance of this migration, is this safe?

It does not, because most database DDLs are not atomic. For those that are, put a BEGIN at the top of your change script, and a COMMIT at the end to make it atomic.