Hacker News Reader

Comment by iyn | Hacker News Reader

What are the best practices for database migrations when trying to setup continuous deployment? Are there any existing tools/solutions that solve/simplify the problem? This is the issue that is almost always missing in articles/tutorial about CD

View on HN

It's hard. The general rule of thumb is that you make database changes independent of code changes, and you always make the changes in such a way that they are compatible with the current deployed code AND with the next version that you plan to roll out.

For example: let's say you have a feature that uses a column, but you want to move it to using a separate table.

Step 1: design the new table

Step 2: deploy the new table - existing code continues to run against the column

Step 3: run a back-fill to ensure the new table has all of the data that exists in the current column

Step 4: deploy code to use the new table instead of the column

The above is the best-case scenario - but it often doesn't work like that, because you need to ensure that data added to the old column between steps 3 and 4 is correctly mirrored across. One approach here is to deploy code that dual-writes - that writes to both the old column and the new table - along with extra processes to sanity-check the conversion.

GitHub's Scientist library is a smart approach to these more complex kinds of migration - which can take months to fully deploy. https://github.com/github/scientist and http://githubengineering.com/scientist/

Obviously your millage will vary but I've also found something that augments the above approach is to separate reads from writes in your app. Read from views instead of directly from the tables. You may also choose to write using sprocs, and, or explicit 'writer' types/functions in your app code.

This can help in the migration process because you can version the views and sprocs whilst running more than one version at a time. Essentially you're creating a versioned API for you db.

Of course you now have more db objects to manage (boo, hiss, more moving parts) but it also encourages you down a saner path of versioning your db objects and rationalising your persistence somewhat (Do we really need 3NF? If I update this table in isolation..I jeopardise consistency of this entity etc.)

None of this _solves_ anything but I've found it mitigates a hard problem.

Beyond a certain size (basically, once the time the migration will take because of the size of the data it applies to is too large), migrations are a heavy investment - in elapsed time, I/O, and so forth. As such, they are planned to a degree that CD probably isn't the solution for it (for example, you probably can only have one migration in flight at a time).

They aren't done live as a single big process that have the potential to lock all queries/updates over their execution, but rather as a set of smaller steps that don't lock.

Facebook has spoken about its online schema change process before - https://www.facebook.com/notes/mysql-at-facebook/online-sche... and its follow-up at https://www.facebook.com/notes/mysql-at-facebook/online-sche... for example, and I'm sure elsewhere.

Most people using MySQL would potentially first use something like https://www.percona.com/doc/percona-toolkit/2.1/pt-online-sc... instead of trying to create their own.

The same principles apply to other data stores that have more rigid schemas.

That's not the case for Instagram, they use PostgreSql, they could update tables without any downtime and this is fast. The only problem are migrations which copies data or doesn't just add fields or remove them.

MySQL has online, non-blocking schema changes since version 5.6. But the underlying data file has to be upgraded to the latest format version for it to work first, and to do that in a non-blocking way on a master server you are probably best off running percona toolkit one first. ALTER TABLE --- FORCE does a data file rewrite.

That's a big "yeah but".

You can't rate limit the io. That's huge and the percona tool and LHM offer ways to keep io under control. Along with that you have to pay attention to the live schema update matrix in the docs; many things will upgrade with out locking but require copying the table and this will blast your IO.

In addition even with the tools some changes require exclusive table locks. It needs it for a short period of time but if it can't get it because of a long running transaction , queue of transactions, and etc it can block everything up.

I work in a company that does several deployments a day, and has a giant database. The short answer is you design around it. If you can do something without majorly changing your data you do it. Another common thing is to deploy the code, but then to add a "feature toggle" so you just turn the code on when the data is ready. Basically, figure out how you can refuel while in the air.

> The short answer is you design around it.

Sorry but that seems like a pretty crap answer.

The answer to "what tools allow you to manage database migrations with CD" should not be "don't do database migrations with CD" or "roll your own toggling features."

How is lack of extant tooling swalsh's fault? Would you like to write some?

I apologize for the disappointing answer, but continuously deploying in the sense of constantly pushing code to production multiple times a day isn't just a feature you add to your stack. It's not just another tool. It requires an engineering culture that is focused on it. We have testing, and automated tools for sure, but we have A LOT more tools for logging, and robustly handing errors. When a bad commit is pushed into production, I can pull up a graph that shows me the number of errors, and the full read out of every occurrence of it. I can also revert that code very quickly. We also have a team that does nothing but monitor the code, and the deployments. There are a bunch of tools you can find probably... but my point is that it's something we're thinking about from the design stage... because from design to production it might just be a few days. Major features will get pushed in stages, its a very different way of doing things (at least for me). Before I was here, deploying was something I did maybe once every few months. The code had to be absolutely perfect, because it'll be a while before I can get a chance to fix it. Here, I deploy while I drink my morning coffee.

If you went back to the previous job, would you push toward more frequent deployments, or is there something that makes the two situations different?

If I was starting a new company I would without a doubt push towards doing frequent deployments all day long.

That said, I would would never push for it at my previous company. At this company it's something they've been doing since day 1. It's always been built into our process, culture, and hiring practices. At my previous company the "agile" process was just a shortened waterfall process. Though many of the engineers were very talented, and responsible... there was more than a handful that I would never trust to "self test" their code, or to monitor it as it deploys.

I definitely like that the more I think about it. We try to do deployments every couple months, but changes are so big and risky we end up pushing off deployments to the next release just because nobody wants to risk it.

I did a gig whereby the databases were HUGE changing the schema was aways avoided unless it was deemed it would pay for itself. i.e. the accompanying feature warranted a change and to what degree. To reduce entropy a full time DBA would tweak indexes and work with each component team to release db refactoring with the teams.

I don't think this is an approach I would recommend but if your gig is a giant enterprisey shop that manages risk far to conservatively it's a reasonable half-measure.

CD is an aspiration for a lot of shops and getting there is a cautious tale of lots of small victories and earning the trust of the decision makers. Sometimes that means:

> designing around it

I've always done two-phase migrations.

Phase 1: Upgrade schema for new code. Migrate initial data from old schema to new schema.

[Deploy: New code starts taking requests, writing to new schema. Old code is drained from the pool of handlers, continues to write to the old schema. Once old code is drained from the pool and the new code is validated by production traffic, run Phase 2.]

Phase 2: Catch-up migration of old data to new schema. Drop old schema.

I used Liquibase for migrations - change-sets can be tagged with contexts[1] and when you run the migration you can specify contextual information that each change-set can target (e. g. development AND pre-deployment). The principal tags I used were pre-deployment and post-deployment (which map to Phase 1 and Phase 2 above).

Schema migrations were a little harder to write but it meant that we could migrate live without impact to customers.

[1]: http://www.liquibase.org/documentation/contexts.html

I've used Flyway for db migrations and it works well.

https://flywaydb.org/

Anyone else use Flyway? Looks compelling for anyone not using RoR/Active Record (where migrations are out of box).

we use it for a fairly large postgres database (growing 4-5GB a day) and haven't had any big issues so far. currently looking at a sort of catch-22 issue at the moment where the application running flyway also reads information from the database to initialize some caches and that data is inserted and maintained by another application. this works fine on an already initialized database but not for the automated testing server where the data is wiped.

We also use it on a multi (postgresql) database setup where every customer has its own database. Works really good, we never had any issues with migrations and do them frequently (maybe once a week).

We use Flyway in a number of ways. Scripted as part of some deployment processes, included with Spring Boot applications for a RoR style migration on boot, and actually use it in Java code to manage other databases from our application during runtime.

An interesting way to use it is that you can have multiple applications that may use the same schema but are responsible for their own tables- you can specify a different schema metadata table for each application and they can generally live together fairly happily. We also have an application that 'deploys' data to target databases, so we are able to run Flyway via their Java library to make sure the target schemas are correct before running our statements against them.

Prior to Flyway I used Liquibase, which is also pretty powerful- but Flyway has just been so much more versatile- and the Spring Boot 'auto' integration has been awesome.

A somewhat similar question: what do you do with frontend assets? If you have JS/CSS/image files with a cache-busting URL, and you have different versions on different servers, you can run into problems.

For example, if most of your web servers are running version 1 and you're in the middle of rolling out version 2. A page request goes to one of the web servers with version 2, which returns HTML containing links to assets with the version 2 cache-busting URLs. The browser requests those assets, perhaps through the CDN, but the load balancer sends the requests to a web server still running version 1, where the assets don't exist yet. This means the browser will get a 404 error.

What are the best options for dealing with this problem?

Not sure that this is the best answer, but seems like a good use of feature flags. If the flag points to the old state, serve the old assets. If it points to the new state, serve the new assets. So make a deploy with both assets, deploy everywhere, then flip the flag.

A bit unsatisfying because it probably isn't always easy the include both assets.

Another idea (not sure if a good one) would be having the load balancers pin a certain session to a certain backend machine. Seems like this would make it better without fixing it, though: that session will still need to switch to a different set of assets when "their" server is deployed.

The underlying problem is the use of a naive cache buster which would cause the old server to reply one way and the new one another. For both the people who want the old asset and the new asset.

If instead of being something the web server throws away before replying, the version number actually caused different assets to be returned, then you'd not have this problem.

One pattern is to separate out assets into a different package that is deployed to a separate host group and have your clients request a different host name, or have your load balancers use a path match to use those servers for those requests.

Another is to push asset updates first to all hosts. All hosts, even without code update, will now be able to respond for the new assets.

Another is to use a local cache plus some backend service or database to serve the assets from the web servers - again, all hosts will now respond correctly for the old assets and the new.

We use alembic, a python sort of DSL, for migrations. We can go forward or backwards. Any schema changes need an alembic script for both directions. We generally try to do 'mini-migrations' that go with each change, as opposed to big bang migrations. In the past building stock exchanges it was generally big bang with a mass of SQL that could only run once. This are better these days. :)

A lot of it depends on the particulars of your database system. But there are certainly tools/solutions that exist, and essentially they all boil down to the same pattern: migration scripts should be executed exactly once in order. How this is done varies.

I do a lot in the .NET/SQL Server world, and my tool of choice is one that I wrote: http://josephdaigle.me/2016/04/03/introducing-horton.html. Conceptually, what this tool does could work for any RDBMS.

Similar tools for the . NET world include Roundhouse and fluentmigrator.net. I'm personally more of a fan of the latter, but we use both in our company in conjunction with Octopus Deploy. Roundhouse needs a bit too much Powershell for my liking.

Octopus Deploy also has a small tool, DBUp, which can run migration scripts. I've been using it for years with no issues.

https://dbup.github.io/

Irrespective of language / platform does anyone ever write 'down' migrations? Do you ever use them?

I've always found they don't pay for themselves in terms of testing and likelihood of being required.

Alternatives are snapshots (depending on if you can afford downtime) and simply writing recovery scripts that aren't tied to individual migrations but rather a deployment. i.e. run script before running any migrations and if it goes wrong run contra script to backout as opposed to cleanup script.

Reply on news.ycombinator.com