Building a better and scalable system for data migrations
yorickpeterse.com
yorickpeterse.com
One thing I would love to see implemented more thoroughly in database systems is the ability to throttle and/or pause the load that DML can impose. Normal SQL only says "what" the ALTER TABLE should do, but the "how" is often rather lacking. At most you get a CREATE INDEX CONCURRENTLY in postgres or an "ALGORITHM=instant" in mysql, but rarely do you get finegrained enough to say "use at most XYZ iops for this task", let alone that you can vary that XYZ variable dynamically or to assign priorities to load caused by different queries.
AFAIK TiDB and pt-osc provide ways to pause a running migration, gh-ost can also throttle a migration dynamically. Vitess also has several ways to manage migration, as it leverages gh-ost. For postgress I don't think any of the currently popular tools have good ways to manage this, but I would love to be proven wrong.
Vitess throttling is by default based on replication lag, but you can use different metrics, such as load average, or indeed multiple metrics combined, to define what constitutes a load.
Disagree. SQL is a declarative language that is both clear and universal. Feel free to generate the migrations however you’d like, but I want to see the SQL.
Another bonus (and I’m sure I’ll be told I’m wrong) is that you don’t need to write tests for the migration, because it’s declarative. Assuming you know what it means (if you don’t, maybe you shouldn’t be administering a DB) and what its locking methods entail, you will get _precisely_ what it says, and nothing more. If you get a failure from, say, a duplicate entry when creating a UNIQUE constraint, that’s orthogonal to the migration itself – you described the end state, the DB tried to make that happen, but was unable to do so due to issues with your data. All the tests in the world wouldn’t catch that, short of selecting and deduping the column[s], and at that point, you’re just creating work.
I am firmly convinced that any and all infrastructure should be declaratively instantiated, and declaratively maintained. I do not want or need to test my Terraform files, nor my DDL.
This works until you need something beyond the trivial "UPDATE table SET column1 = column2", such as "Update the values in column X with the contents of file Y that exists in Amazon S3", or really anything else you can't express in SQL.
> Another bonus (and I’m sure I’ll be told I’m wrong) is that you don’t need to write tests for the migration, because it’s declarative.
This indeed is wrong, and essentially comes down to "It looks simple so it's correct". A SQL query "DELETE FROM users" might be correct, but if you meant for it to be "DELETE FROM users WHERE id IN (...)" it's going to cause problems.
In other words, at least for data migrations you absolutely have to write tests or you will run into problems.
> "Update the values in column X with the contents of file Y that exists in Amazon S3"
You actually can do that in SQL, assuming it’s hosted on AWS (or for Postgres, you’ve installed the correct extension). It would be a bit convoluted (I think to handle the UPDATE, you’d first dump from S3 into a temp table, then update from that), but it would work.
> A SQL query "DELETE FROM users" might be correct, but if you meant for it to be "DELETE FROM users WHERE id IN (...)" it's going to cause problems.
If someone doesn’t notice this issue in SQL, they’re not going to notice it in an ORM, either. It’s also possible (and a good idea) to enforce predicates for DELETE at a server configuration level, such that the DB will refuse to execute them. And really, if you actually want to delete everything in a table, you should be using TRUNCATE anyway.
The limitation you're implying—is not that of SQL, but your data model.
In contrast, a migration written in an actual programming language can handle all such cases. Depending on the amount of abstractions applied, it can also look close enough to a declarative language (in other words, it doesn't have to be verbose).
So yes, the limitation I'm implying very much is a limitation of SQL (or really any declarative query language for that matter). It has nothing to do with the data model as it applies equally well to using e.g. MongoDB, Redis, or really anything else that stores a pile of data you may want to transform in complex ways.
If you’re shifting or otherwise manipulating tuples, then yes, you probably want to handle that logic in a more general-purpose language (though it isn’t _required_, annoying though it might be otherwise).
But for DDL? No, I don’t want anything in the way. The state should be stored in VCS, of course, but I don’t want some abstraction trying to be clever about ALTER TABLE foo. Just run it how I specified.
I’m hoping my current job breaks this trend.
Once client code can work with the mixed state (while the migration is in progress) It no longer matters how long it takes. Once the migration is robust enough so it can handle crashes, interrupts, ... it no longer matters how often you trigger the "continue". The migration is using too many iops ? just kill it, schedule a continuance later.
Also, your smallest step needs to be an atomic multi-update (you don't want to bother with partial failures)
https://blog.redplanetlabs.com/2024/09/30/migrating-terabyte...
It’s timeless by looking at a captured schema, that doesn’t change with later code changes. Which means you can’t import / use methods of models and have to copy those over. So a different way than using the vcs for this, but still.
Not sure I’d name the second point “scalable”, but you can easily do data migrations as well.
It is really easy to use!
And you can certainly write tests for it, although it isn’t included in the framework [0]. But you can do that with just SQL too [1].
What I find much harder with (somewhat) larger dbs, is is a) determining whether it will lock up too much and b) whether it backwards compatible (so that you can roll back). Which is splitting in these pre- and post-migration steps as the article mentions. We currently use a linter for that but it is still a bit basic [2].
[0] https://www.caktusgroup.com/blog/2016/02/02/writing-unit-tes...
I'm trying to move my database to Postgres, there is a part which is "describing all the objects" (object id, properties, etc), and a huge table which is a log of events, that I'm storing in case I want to data-mine it later.
Of course this last table is:
huge (or should become huge at some point) better suited by columnar storage might be archived from time to time on S3 My initial thinking was to store it in Postgres "natively" or as a "duckdb/clickhouse" extension with postgres-querying capabilities, keep the last 90 days of data in the database, and regularly have a script to export the rest as Parquet files on S3
does this seem reasonable? is there a "best practice" to do this?
I also want to do the same with "audit logs" of everything going in the system (modifications to the fields, actions taken by users on the dashboard, etc)
what would you recommend?
While not dealing with that kind of scale yet, our application (An AI Data Engineer that has done migration work for users) needs to do before and after comparisons and find diffs. We use a branchable DB to compute those changes efficiently (DoltGres)
Could be an interesting thing to consider since it's worked well for us for that part.
Our build if u wanna check that out too -> https://Ardentai.io
This approach cuts the "database server" into an event stream (an append-only sequence of events), and a cached view (a read-only database that is kept up-to-date whenever events are added to the stream, and can be queried by the rest of the system).
Migrations are overwhelmingly cached view migrations (that don't touch the event stream), and in very rare cases they are event stream migrations (that don't touch the cached view).
A cached view migration is made trivial by the fact that multiple cached views can co-exist for a single event stream. Migrating consists in deploying the new version of the code to a subset of production machines, waiting for the new cached view to be populated and up-to-date (this can take a while, but the old version of the code, with the old cached view, is still running on most production machines at this point), and then deploying the new version to all other production machines. Rollback follows the same path in reverse (with the advantage that the old cached view is already up-to-date, so there is no need to wait).
An event stream migration requires a running process that transfers events from the old stream to the new stream as they appear (transforming them if necessary). Once the existing events have been migrated, flip a switch so that all writes point to the new stream instead of the old.
Though you can get a long way with just specifying the appropriate contexts before you kick off your migrations (and tagging your changesets with those context tags as well): https://docs.liquibase.com/concepts/changelogs/attributes/co...
That's one of the reasons we implemented "migrate down" differently than other tools.
I'm not here to promote my blog post, but if you are interested in seeing how we tackled this in Atlas, you can read more here: https://atlasgo.io/blog/2024/04/01/migrate-down
I put each DDL in a Liquibase changeset with a corresponding rollback DDL I constructed by hand. If the Liquibase changeset failed, I could run the rollback for all the steps after the "top" of my wish-I-could-put-them-in-a-MySQL-transaction operations.
But you are right MySQL itself doesn't support transactions for DDL and that is true whatever tool you use.
It is true that if you put multiple SQL operations in a single Liquibase changeset that are not transactional you can't reliably do rollbacks like the above.
It is also true that constructing an inverse rollback SQL for each changeset SQL by hand takes time and effort particularly to ensure sufficient testing, and the business/technical value of actually doing that coding+testing may or may not be worth it depending on your situation/use-case.
I wish there was a better way to run blue/green DB deployments. Though this feature is rare (e.g. gh-ost) and not that usable at less than bug tech scale.
But the underlying data and it’s model can be in flux and we handle exabyte scale ha dr rebalancing etc