Ask HN: How do you manage direct updates to databases in a production system
Hi HN! When you have a production system running, every now and then there are requirements to made a DB update because of some bug in the code, or for some use cases which were not thought of earlier. How do you handle such updates?
The common way I know is the access is given to a small number of trusted people who make the updates. Are there any other ways or best practices such that the small set of people don't end up becoming bottlenecks, apart from the potential risk of abuse of trust.
81 comments
[ 2.4 ms ] story [ 148 ms ] threadWe have the same approach. A very small number of people have write access to the production databases. If things can’t wait for a schema change release (15-30 min) and can’t be done through the back office API, we do it manually. It’s very rare.
It should be a rare exception not a normal thing, so a few people is fine.
Best practice is to also have your change reviewed beforehand, and run in a transaction where you also validate expected results before committing, etc.
You can also use a specialized tool. https://datacadamia.com/data/database/migration#tool
Make a branch, test your code and deploy it.
The best solution I've seen is to write the migrations by hand, including rollback scripts. This goes for both schema changes and data changes. Schema changes should, in my opinion, be done out of sync with code changes. The new schema is applied first, if that runs for a few days, code can be updated to use the schema. This allows for easier schema and code rollbacks.
Just as a counterpoint: In my experience with using Flyway in a professional context with big Postgres instances for the past 6 years we never had a single issue with it.
Either way, you need to designate someone who is knowledgeable to oversee the process. Automated deployment just makes this process way faster (thus "reducing bottleneck") where this person only needs to review code to see if anything suspicious isn't there and approve the deployment.
Manual deployments are prone to human error, especially under stress/time pressure. If manual deployment "package" (a set of sql scripts) is poorly written, there's huge incentive to "contact directly", which again could lead to manual errors.
The biggest drawback is culture which is the hardest ("we done this way for n years and we don't want to change").
[0] https://alembic.sqlalchemy.org/en/latest/
In all cases though you should have a change management process where the schema change is documented, reviewed, and approved and only a small number of people have the necessary access to run these types of changes in production. Change management is your friend.
1: https://www.percona.com/doc/percona-toolkit/3.0/pt-online-sc...
2: https://www.percona.com/software/database-tools/percona-tool...
I only had 2 of these in the 5 years I worked there, but here's an example. We had an internal purchase request system used for puchasing a keyboard or what ever you needed for work. Of course the purchase request went through a chain of approvals starting with your manager and eded with the CTO. The CTO threw a fit for having to approve keyboards and other small items, so it was deemed an 'emergency' to fix it right away. I had to immediately patch the code so he wouldn't see trivial requests. The 'fire call' allowed me to submit the code directly into production without going through the change control procedures, which only happened once per week.
And you better be damn sure that your changes are correct, crap rolls down hill very quickly when it involves very senior people.
That's an organizational problem though. We have a culture where, given a choice between "let it burn" and "quick fix with potential to blow it up (rather than put out the fire)" - responsibility for fallout in case it blows up lays with the person making the decision (the senior manager/cto) - not the person doing the work and describing the trade-off(s) (this should work - but there's a non-trivial chance it can blow up).
That said, work for fixing the aftermath does flow down hill.. It has to.
In my opinion such queries should be subject of established development process: 1. Commit your query to a repository 2. Send to code review 3. After successful review and merge it should be deployed automatically by ci/cd
It may be necessary to run query directly via console in some cases, though. But such query should be committed to the repository latter any way.
And of course you should use proper tools like comments suggest.
- reproduce the issue and fix locally, with prod data copied over if needed (better than guessing)
- save (back up) all data that is being changed somewhere, even if it's on your local machine temporarily (again, better than not having it)
- if your datastore has transactions, run the change and a confirmation query in a transaction/rollback several times before committing
- don't hide what you're doing, tell everyone who will listen that you're mucking about in prod
- if someone will hop on a screen share with you, let them
Main theme here, when making data changes, is test as much as you can and make sure folks know it's happening. If you're making data changes, the code has already failed, so don't feel bad doing what you need to do, but mitigate loss as much as you can. Shit happens, deal with it, but don't make it worse if you can.
My point is, testing on a copy is necessary, but you must combine it with thinking as carefully and thoroughly as you can about the potential side-effects, which in turn depends on your understanding of the application architecture. Some of those side-effects you can test for, others are not so obvious unless you can better simulate the production environment with noisy users who will complain when things go wrong. One strategy is to find people internally who are good at being real life simulators of noisy users who bump into edge cases.
With a sufficiently comprehensive data anonymisation tool you can work around this limitation, but that's an additional investment that a business needs to be aware of up front.
Your dev environment should be running on a sanitised version of prod data.
Some people prefer to operate on a subset (10%~) of prod data, I have always preferred to take the cost of taking everything.
This has a nice consequence of allowing you to test automated restores at regular intervals, but you must be mindful of the "fail-open" nature of sanitising data. (IE: new data must be known to the sanitisation tool).
Pipeline usually goes:
Prod/live -> Replica -> Backup/Snapshot -> Copy to archive -> Copy from archive via streamed sanitiser -> sanitised backup -> Restore[n] Dev Envs from same backup.
But, even that said: you can copy the binary files over to a new machine (copy-on-write snapshots -> rsync) -> store a copy -> start up the database, sanitise -> ship around to dev envs.
You’re conflating dev environments with restoring backups. Those can be the same thing but are often separate.
If you’re running hundreds of terabytes then the systems in place to shard that data must be well tested.
Migrations must happen on similarly sized data, along with various distributed transaction guarantees because I doubt you’re going to be using dedicated-attached storage for that. And if you do then testing multipath needs to be part of your testing too.
Is it expensive? Yes. But that’s what working with that amount of data costs.
Or is this a strawman intended to stump me, because I have dealt with such “data requirements” before and when they saw the sticker price of doing things properly suddenly those hundreds of terabytes weren’t as “required” anymore.
I haven't looked into it in a while and the last time we ended up rolling our own.
Any tool for doing this would have to be so generalised as to be extremely difficult to configure I believe (as difficult maybe as setting it up with custom shell scripts)
The main value is the use of ZFS snapshots to give you almost-instant (2-3s for a 20G DB on my dev laptop) writeable clone of an import, which you can test your migrations etc against, and then just revert or destroy, which has been extremely helpful for me.
Happy user, no relationship, etc.
[1] https://postgres.ai/products/how-it-works
I've never had to work on a 24/7 critical system so I dunno how to do that.
In addition I would build a validation service that checks and tests the update data (the list of changes) before they are submitted to the change service.
I would not permit any adhoc changes to the prod database. The rest service should be the only way for a mutation to be done short of a complete new release.
If you need to upgrade production stuff more than that you have problems elsewhere.
I worked in a business where if the website went down we missed £26m revenue. This is not a particularly big business in the modern sense. However, one MD had a screwed up migration that didn't have a good roll-back plan. There was a missed day of revenue, and he - and several others in his top team - left the business within a calendar month.
This kind of thing colours my view on prod risk.
I’m a big fan of sqitch[1] but many migration tools will handle data updates as well as DDL.
1: https://sqitch.org/
1. Please don’t issue I/U/D SQL statements directly. You’d better write up some programs acting as the “revert transactions” to do the data modification. In this way, you don’t need to grant the I/U/D privileges of the production tables to any user IDs. Instead, you give access to those programs. It will help you to remove the human error as much as possible. And the behavior of the programs is more predictable and consistent.
2. You should have a fixed change window for these kinds of data changes. You should not execute those “revert transactions” whenever you want.
3. Then you give the execution access of those programs to the people who need to do the work only during the change window. That is you grant the execute access to the user ID beforehand and revoke the access afterward. Since this is grant/revoke between user ID and programs, it’s much safer. If you have to grant/revoke between user ID and tables, there might be cascade effects.
4. Before the change, capture the “before” data and get ready the fallback script.
5. Don’t forget to turn on the audio options/tools during the change window.
6. If you guys work in a physical office, you can think about binding those revert transactions to a dedicated desktop.
I know these rules are complicated and tedious, but they could protect the team and the data as well :)
In other case, you write small job called fixtool, that goes through normal code review process, then gets deployed once, runs, and gets deleted after it fixes the situation.
In development I create the migration which ends up being a file that alters the schema. I run the migration command and make sure things work as planned and all tests still pass.
Then I push up the feature branch and it gets code reviewed. This review could either be by yourself or a team member depending on team size.
After everything passes on CI it gets merged and deployed. At this point it depends on how you deploy things but that could be to a staging environment where it runs which gives you extra confidence that it works before rolling it out to production or you could go straight to production if you don't have a staging environment.
As for how it gets run, usually it happens after the new version of your code is available to be pulled but before your web app gets restart. This way it can run while the old version of your app is running, then once it completes you can restart your web app to pick up the new code base that was part of the deploy.
If you're careful about how you migrate things you can do a lot of migrations without hard downtime, but it's usually a balancing act between how complicated you want the migration process to be (multiple steps / deploys) vs how much downtime you can live with (1 step / deploy).
Basically migrations are code that get treated like any other code deploy with a proper review. If it's an especially important migration it wouldn't hurt to get it reviewed by more than 1 person and also perform a production DB backup right before you deploy the migration.
I've used Django migrations. From the dev side it's super easy: some code PRs also have associated migration code. A deploy will run the migration then change the application code. It has sharp edges but works 97% of the time. The framework also generates rollbacks, for easy removal of problematic migrations.
Migrations are a good place to store code for an update. If it’s an update that will take a while I might use the migration to enqueue a job.