[HN Gopher] The pedantic checklist for changing your data model ...
___________________________________________________________________
The pedantic checklist for changing your data model in a web
application
Author : rtpg
Score : 292 points
Date : 2021-06-08 07:13 UTC (1 days ago)
(HTM) web link (rtpg.co)
(TXT) w3m dump (rtpg.co)
| janpot wrote:
| With complex migrations, like spanning multiple tables or
| depending on external APIs, I sometimes use a variant of this.
| Where instead of migrating the underlying database in "Migration
| 2", my version 3 would migrate on read:
|
| - Read from new representation
|
| - If not found: - read X from old representation
| - migrate X to new representation (complex business logic here)
| - write migrated datum to new representation - return
| migrated datum
|
| - Either wait long enough until all data has been read at least
| once or systematically call your API for all records you want
| migrate (depending on the logic, you might want to throttle this)
| eyelovewe wrote:
| It's a very good topic. I currently use Ansible for deployment. I
| typically run the equivalent of a db migration as a custom
| application based upon a proven core, that, after extensive
| testing on copies of the production db, eventually runs against
| the production dB, as part of an orchestrated deployment.
| Verifying individual app nodes behaviors prior to moving onto the
| next is important, but also knowing the difference between data
| migrations that break old code vs ones that are agnostic, ak just
| make new data structures and slowly prune old ones, slowly
| meaning one or two deployments in the future, but before
| forgotten
| alkonaut wrote:
| Before dropping the old data, what is the simplest way of making
| sure it's not used? As the article says "this is hard to roll
| back".
|
| Is it as simple as keeping the data around, but e.g. renaming
| columns to "columnname_deprecated_yyyy_MM_dd" and then in the
| unlikely event that something starts failing, you can at least
| rename it back to the old name while putting out that fire?
| athenot wrote:
| That's a good precaution.
|
| Another one might be to log queries and check for use of that
| old column.
|
| 2 caveats: (1) ensure this logging doesn't cause performance
| bottleneck and (2) this might not catch queries that use globs
| (ie. "select * from ..."). For (1) you can do this one only
| some of your instances of your DB cluster and (2), try to
| always use explicit column references.
| candu wrote:
| As stated in the post: these steps make _complete sense_ if you
| 're aiming for a nice, smooth zero-downtime transition from the
| old data model to the new.
|
| However, if you can get away with some downtime, you can instead
| notify your users of a planned outage, shutdown, update
| everything, and startup again. This eliminates several steps of
| the process, which has the added benefit of reducing the number
| of places where something can go wrong.
|
| To put this in context: say you guarantee 99.9% uptime - that's
| still 43.2 min / month of downtime available to you! You can
| update quite a lot of machines / database rows in even 10-20 min,
| making this viable in many cases. (Obviously there is a scale
| threshold past which this is no longer viable, but if you're at
| that threshold you can probably also spare the engineering effort
| to go the zero-downtime route!)
| jchook wrote:
| One upside to writing an intermediate version that supports
| both the old and new representation: your data migrations are
| not an urgent race against the clock, or mission critical to
| the release.
|
| You can decouple migrations from the release, and rest easy
| knowing your app is working without praying that a massive
| migration happens successfully and promptly.
|
| Also since your UI or admin Interface likely lets you inspect
| your data in either representation, you can more naturally
| verify up/down migrations work in staging.
| Tainnor wrote:
| There's also some DB updates that, even if strictly speaking
| non-backwards compatible, run in just a matter of seconds.
| Additionally, in postgres you can set statement_timeout, which
| should abort any query that takes too long.
|
| If you have a reasonable deployment strategy, then the chance
| that someone will talk to your application in the exact moment
| that the app is in some invalid state, might be quite small.
| Sebb767 wrote:
| > This eliminates several steps of the process, which has the
| added benefit of reducing the number of places where something
| can go wrong.
|
| It actually also removes some advantages. The author fails to
| point that out, but the advantage of these small steps is that
| you can roll back to the previous version at any time without
| loosing writes. If you find a production bug that does not
| impact data consistency, you can go back one version without
| loosing the writes that happened while the new version was up.
|
| > To put this in context: say you guarantee 99.9% uptime -
| that's still 43.2 min / month of downtime available to you!
|
| On the other hand, there might be a fatal bug at some point and
| you will need 3 hours of leeway to get up & running again.
| Shutting down is surely the easier method, but the available
| downtime is usually not free real estate for updates.
| plasma wrote:
| My advice whenever you have large, complex, or slow to perform
| (eg hours/days) migrations, that will take some time to process,
| don't put yourself into a position of "we can't go back", until
| the very last moment, after you have been assured as much as
| possible things are fine.
|
| Things to keep in mind include:
|
| * If migrating data to a new column, keep the old data for a
| while
|
| * After migrating the data (with new code writing old/new data),
| you may want to do an audit query to validate everything was
| migrated correctly
|
| * If you need to mutate data inline in the column, add a new
| column and just copy the (pre-migrated) data to it as a backup
| (so worst case you have a pristine copy from before), then drop
| that column some time later
|
| * Runtime config flag in your app that lets you read the new
| column/truth source as authoritative for a decision, or put the
| flag back to false to revert it. This could include a percentage
| so that you slowly creep load onto a new service and build trust
| in it functioning correctly.
|
| * If the data migrated is quite complicated, help yourself to
| easily switch between old/new data sources for your own manual
| review in your apps code paths at runtime (eg, render this
| analytics from Source A or Source B via a runtime flag, query
| string parameter etc)
| wdb wrote:
| I am wondering how the long running migrations are done. Are
| they running as a scheduled job outside the app? I can imagine
| it can't be part of the app itself as that would block
| deploying a new version of the app (/docker image)
| rtpg wrote:
| Original author here. I do think it's good to think about
| rollback strategies (especially given that your change might go
| out with something else that might need rollbacks).
|
| There is one thing though, and that is that a lot of these
| transformations end with more information, so you can usually
| recover the old state if you ever needed to (or write some
| compatibility shim for whatever reason)
|
| I had a whole part about rollbacks (and an intuitive proof
| about why each step was needed and not being able to skip any)
| in my notes for this, but I decided to leave it out cuz I
| couldn't do it justice just yet.
|
| There are so many little details to cover in this space, and
| honestly the vocabulary and tooling are a bit poor so it's hard
| to be succinct
| jrockway wrote:
| I strongly agree with this. Relatively recently, I did a major
| migration to clean up some weird account states that we didn't
| want to support anymore, so we could launch a new feature. I
| spent some time writing all the SQL queries to do the
| migration, went into staging, ran them... and found that there
| were a lot of edge cases that weren't handled correctly. It was
| terrible. I decided to abort the SQL statement approach, and
| just wrote a somewhat-complicated program with extensive unit
| tests to handle the migration. The tests exercised the edge
| cases and ensured that they were accounted for correctly in the
| migration. (And we had time to come up with a real plan for
| each of them.)
|
| When the time came to do the cut-over in production, we had a
| war room, ran the program, and it was all over in seconds.
| Everything worked perfectly and I honestly felt a little
| dejected how boring the most major change we had ever made as a
| team went. I had hyped myself up for some stressful debugging,
| hair pulling, restoring things from backups... but it was all
| over and done with perfectly in a few seconds. I didn't really
| know what to do with the rest of the day. It worked! Major new
| feature! Back to work.
|
| I try to make all future "major" migrations equally boring. The
| time you spend debugging things in development is time you
| don't spend with production being down. Worth it every time.
| hinkley wrote:
| > new column/truth source
|
| I think we need to be a bit more explicit in this part.
|
| Database columns are your system of record. If the code treats
| them as the source of truth, you need to break that _first_. If
| your code is factored properly you only need a toggle in the
| retrieval code. If it 's not factored properly, now you
| understand why some of your peers are pushy about separating
| data retrieval from data use - it concentrates any mutation to
| a point in time before first use _where you can easily find it
| when surprises happen_ and where there can be no concurrency
| issues. If that 's too daunting you can settle for the lesser
| solution of replacing use with a function call.
|
| Once everything is using the new source of truth, you can put a
| toggle there. And after the migration you can decide if you
| want to keep them separate or fold them back together.
| cletus wrote:
| Did you even read this article? That's literally what it said.
| jameshart wrote:
| The article didn't mention having a rollback strategy. This
| post is a useful addition to the discussion.
| hackerfromthefu wrote:
| The authors reply to this comment also assumed that the
| data schema migration will always succeed - by saying the
| new schema usually 'has more information' that you can
| reconstruct the original from.
|
| All in all the authors writeup and reply is only covering
| the happy path, not the real world nuance that mistakes or
| errors can happen!!
| rtpg wrote:
| I mean... I understand the notion of fucking up a
| migration, of actually introducing data loss etc.
|
| I guess this is partly my fault, cuz I said this list was
| pedantic but I didn't go in deep enough ("rollback if
| something is clearly blowing up" for example).
|
| I mean yeah, don't delete the old data until you're sure!
| But you know what? If I'm moving two booleans into an
| enum with 4 states (or less states but I checked the
| production database to confirm a case didn't exist), I
| feel pretty confident about doing that data move and not
| having some secret data being missed.
|
| You can use logic/math/data analysis to determine you are
| handling every case! And yeah, you can make mistakes, but
| the variety of mistakes become much smaller. And this is
| all in the framework of having backups of your data,
| having reviews, letting time pass to reduce
| uncertainty... just run of the mill "run stuff on
| computers and editing multi-tenant data" stuff.
| jkingsbery wrote:
| For whatever it's worth, a pretty good book that covers this in a
| lot of detail is https://databaserefactoring.com/. One of the
| authors had several notes on his website about this prior to
| putting them into a book:
| http://www.ambysoft.com/books/agileDatabaseTechniques.html
| amelius wrote:
| Can't this be automated? It seems like such a basic
| transformation, that a computer should be able to do it.
| Silhouette wrote:
| Systematic database migrations do feel complicated, but how
| would you automate the process in the general case? You need to
| coordinate changes to two systems: your application code and
| your database. You can be doing anything with either. You can't
| update all relevant instances/records instantaneously, so you
| need to allow for intermediate states where both old and new
| code or data are in use while the update process itself is
| running. I think the article here does a pretty good job of
| describing this and a systematic way of dealing with it. At
| least you only need that many steps if you're making a breaking
| change to your existing data model rather than just extending
| it, which still requires care but is quite a bit simpler IME.
| 0xbadcafebee wrote:
| Yes. For each step, write code that makes the change, and at
| the end of the change, updates a record (somewhere; different
| table, let's say) to note it is compete. Write a test that
| check if the record is updated, validates the change, and upon
| validation, updates the record a second time. Make each step
| depend on the validated value of the previous step. Put them
| all into cron jobs.
|
| They will churn until all the steps are complete and validated.
| If a step fails, the rest of the steps won't happen. You can
| also write rollback steps that depend on yet another value
| written if a step or test fails. And if you're paranoid, you
| can force the steps to wait for another value to be flipped
| which you would flip manually, so you can inspect everything
| before allowing the next step to fire.
|
| All of this is complicated, so, try to only write backwards
| compatible code where the data model never changes, or only
| ever adds fields, or use feature flags, or some other strategy.
| There's a book on this stuff somewhere...
| biggerfisch wrote:
| Sure, but automating this requires a lot more tooling. In the
| case described with multiple, widely spread instances, you'll
| need to be able to
|
| a) Reliably determine which step you are on - this includes
| both migration steps and application deployments b) Fully
| automate your deployments from a list of staged code changes
| ("normal" CD will have issue with this) c) Fully automate
| migrations _after_ all deployments are complete
|
| Those are all totally possible steps, but they are all going to
| vary based on your setup, making a drop in solution for this
| process unlikely to be easy.
|
| Additionally, these are only the steps if everything works!
| What happens if there's a bug halfway through? You probably
| want to stop the deployment process and either revert or
| quickly hotfix. A human-based system can handle this quite
| easily, but an automated system will need to be able to measure
| errors and reliably determine if an issue exists.
|
| So its not that this can't be automated, in fact, I know that
| it has for several large organizations, but the hurdle to get
| to the full level of automation is rather high and fraught with
| secondary issues to consider.
| slver wrote:
| The fact the author was kidding by writing "you can't have
| (planned) downtime" in itself shows everything has downtime. So
| if your app can have downtime, why not have some planned downtime
| as well?
|
| Oh the users can't take it, they'll go away, yadda yadda. That's
| usually BS. I've witnessed giant sites with massive traffic that
| go offline every night. Apple's own web store goes offline before
| updates every time (most valuable brand, looks like this doesn't
| affect them huh?). Every marketing exchange also goes offline
| every day and weekend (except the fancy cryptocoin ones I guess).
|
| My overall point is, keep things simple. Planned downtime can
| vastly simplify your update deployment, and planned downtime can
| result in less unplanned downtime. So don't give it up for cargo
| cult reasons. You're not Google Search until proven otherwise.
|
| Also you can have planned downtime piecemeal. If your data is
| sharded in some way (for example by user) you can update the user
| when they log out and you'll seemingly have no global downtime.
|
| But downtime is inherent to well working systems. You sleep every
| night. If you don't things go haywire.
| 0xbadcafebee wrote:
| I don't want to have to stay up late to do it. I don't want to
| have to deal with a rollback. I, and the developer, want to be
| able to make changes when we feel like it, not based on a
| schedule. The developer does their work at their pace, I go
| about my work without thinking about their deployment. Apps
| with planned downtime are often poorly designed and have other
| problems, usually due to nobody taking the time to make them
| work better or fix tech debt. Downtime deployments can lead to
| huge changes which, if not completed successfully, can lead to
| much longer downtimes in the event of a rollback. And, yeah, I
| don't want my customer to see any downtime or errors. Call me
| fussy, but I want our product to work better than everyone
| else's.
|
| It's 2021. We may not have flying cars, but we _do_ have zero
| downtime deployments, _sometimes_. Let 's not go back to the
| dark ages please.
| slver wrote:
| Unfortunately "it's CURRENT_YEAR" and "downtime means you're
| poorly designed" are very poor, circular arguments.
|
| The only thing you said back which I can trace back to an
| objective problem is that you don't want to stay late.
| 0xbadcafebee wrote:
| Would you rather a change 1) depend on two people doing a
| series of precarious steps and recovery which could
| possibly lead to a large amount of downtime, or 2) depend
| on one person doing a series of predictable small steps
| through automation which won't lead to any downtime? I
| prefer #2. _Also_ I don 't want to stay late.
| slver wrote:
| Planned downtime means only one thing: you have an atomic
| update, that needs some downtime.
|
| It doesn't inform you that the steps are "precarious" vs.
| "predictable" or that you have have or don't have quick
| way of recovery, or how small or big the update is.
|
| You can just as easily screw up your application state
| without downtime.
|
| You gotta be careful thinking purely by association. It's
| natural and intuitive, but often causes you to group
| unrelated characteristics together.
|
| Maybe my update is as simple as adding a column to a
| table. If I have millions of rows, that won't be instant,
| it can take say half an hour. In InnoDB for example it'll
| do a full table copy to add a column. But it's not
| precarious or fragile, or dangerous. It's just what it
| says on the can. It'll either do the copy or it won't.
| nickjj wrote:
| > So don't give it up for cargo cult reasons. You're not Google
| Search until proven otherwise.
|
| Agreed, and in a lot of cases the planned downtime is a matter
| of seconds which will only be noticeable if someone happens to
| be loading your page at the exact point in time where your web
| server is offline.
|
| In most cases it doesn't really take that long to change a
| column in a table with 15,000 rows on it. For most apps I'd
| much rather go the route of making this a dead simple operation
| with 5 seconds of downtime vs a multi-migration 8 step
| approach.
| rtpg wrote:
| - people tend to want to use many systems during working hours
|
| - engineers/CS staff tend to want to work during working hours
|
| => it is hard to plan for downtime during normal working hours.
|
| If you want to deploy multiple times a day, and if you want to
| be able to merge in new code into your main branch, and you
| don't want to have to gate in merges etc for "deploy windows"
| with downtime, a great solution is to just do the extra work to
| avoid downtime on simple stuff.
|
| I think the irony is that you really need to already be a
| certain size to do downtime-centric workflows reasonably
| without asking a bunch of people to do stuff at weird hours.
| slver wrote:
| I have to point out, it seems, that you don't change your
| database schema "multiple times a day". Nor is every database
| schema linked to downtime (especially if you follow
| service/module orientation with distinct aggregates, and
| don't throw everything in one shared database like it's the
| 90s). Most updates don't need downtime. Few rare ones, do,
| unless you want major complications.
|
| The irony you point out, isn't a huge irony. When your
| business is small, downtime is fine. As you grow and it
| becomes "not fine" you have people around the world that can
| handle it off-peak hours for the users during dev work hours.
| zeckalpha wrote:
| Instead of an error prone human checklist, has anyone made
| frameworks with these sequenced structural migrations in mind?
| I've worked with both Rails migrations and Alembic, but they seem
| a layer of abstraction too low to only allow safe migrations.
| 0xbadcafebee wrote:
| Not a framework, but schemaless key-value stores allow you to
| play fast and loose and avoid a lot of the reason for
| migrations, with obvious downsides. Monorepos also allow you to
| change and deploy all of the code at once without having to
| coordinate versions of both dependencies and deployments.
| dgb23 wrote:
| Also using a key value store or some other no-sql non-graph
| storage can sometimes be put between two structured migration
| endpoints. The advantage is that you have a clear way to
| first pull things apart before you put them together again
| and an intermediate step that is durable and easy to analyze.
|
| But I think the article doesn't talk about full data
| migrations or integrations of two very distinct models, but
| rather about incremental, on the fly migrations on production
| databases, so the above doesn't apply well I think.
| makapuf wrote:
| Schema-less data does not make it unstructured data, it's
| just that it's now unknown/hidden and that you're perpetually
| in the middle of a migration with no obvious way to know
| you've finished migrating.
| simonw wrote:
| Even a monorepo won't help if you run multiple servers with
| your application code - there will still be a few seconds (or
| even a few minutes) during your deploys when some of those
| servers are running an old version of the software and some
| are running the latest release.
| rtpg wrote:
| Par of the problem is that tools don't really have a notion of
| "behavior of system 2 releases ago".
|
| If you had a Django plugin that, like, let you say "oh the
| previous version of the app that was deployed had this model
| shape, so during this migration I need to do X/Y/Z" using the
| git info, you could do more.
|
| But that would be a leaky abstraction and would run into a sort
| of coordination problem mid-deploy (what states are other
| machines in?). There might be some restrictions you could place
| to make this work though
| YesThatTom2 wrote:
| This is what the humans need to do. It works with any framework
| or database.
|
| You might find some software frameworks that make this easier
| than others but expecting to find a technology that replaces
| human methodology is like reading a book on Scrum and saying
| "can't we make a software system that does this for us?"
| bthecohen wrote:
| This is a very useful guide. I'd add one additional step that can
| sometimes be useful if you're doing a more complex change than
| adding a field to an object. For example, sometimes you may want
| to read from an entirely new schema or data source, while
| ensuring that the user-facing behavior stays the same.
|
| In that case, you'd want to add a step between steps 2 and 3
| where you double-read, either inline or within a shadow workload,
| and add telemetry to alert you if there are discrepancies between
| the two versions.
|
| Only when you're confident that the two data models are producing
| acceptably equivalent results would you cut over the primary
| reads to the new model.
|
| This pattern often comes to play when you break off a piece of
| state from a monolithic database into a dedicated service.
| [deleted]
| globular-toast wrote:
| This is actually really useful to see written down. Perhaps a
| more complicated example would be useful, like changing the
| relationships between two or more models.
|
| I feel like this kind of thing isn't really taught. I mean, it
| was a long time ago that I went to uni, so I don't know for sure.
| But it seems to me like a lot of developers either learn from
| someone else because they're lucky enough to get a position
| somewhere where it's already known, or they have to invent it
| again for themselves. But it seems important enough that it
| should be an expected part of professional training.
|
| Also, the fact the author felt it necessary to call it "pedantic"
| speaks volumes. Imagine an electrician calling it pedantic to
| check that the mains has been disconnected or a capacitor has
| been discharged. There's nothing pedantic about good, formal
| practice.
| cdelsolar wrote:
| I agree. On a similar vein I was trying to figure out how best
| to model something complex recently, what kind of data
| structures to use, etc, and Google searches don't really help.
| Where are the books on this kind of stuff for our field? In the
| end I just invented something decent, but it seems like a
| missed opportunity.
| 0xbadcafebee wrote:
| Our whole field is both constantly changing and (AFAIK) has no
| formal trade organization like electricians/plumbers. We also
| aren't licensed like electricians and plumbers. It's just a
| bunch of academics, and a bunch of haggard people who've been
| in the trenches, and a lot of random books with great lessons
| that nobody reads.
| YesThatTom2 wrote:
| This is called The McHenry Technique in the book The Practice of
| Cloud System Administration by Limoncelli/Chalup/Hogan.
|
| That chapter lists many techniques for live upgrades.
| aero142 wrote:
| This comment is the top Google result for this term.
| LukeEF wrote:
| Managing Databases in a CI/CD pipeline is frankly difficult.
|
| Applications are developed continuously, different versions of
| the application need to run in test, dev and staging
| environments, each of which has different data and a different
| schema. This makes for a difficult set of challenges in the set
| up of each environment, and the reliable promotion of software
| from one environment to the next. Staging environments need
| copies of production data in order to test things properly. They
| also need all the new data and schema changes that are required
| to make the new version of the software work.
|
| So now you're moving data and schema changes back and forth, up
| and down the pipeline. But unlike with software source code, you
| do not have Git to clone, branch and merge your changes. As
| applications become more data-centric and designers create design
| systems with all kinds of amazing reactive components for devs to
| use in their applications, each of these components has fields
| and each of these fields have data, and this data needs to be
| stored in a database, and this database needs to updated every
| time new components are added, modified, or used in new ways.
|
| We should manage the revision of data the way we manage the
| revision of our source code. We should merge our latest changes
| into the production database. We should simply clone our
| production database for staging, we should just push changes from
| a developer's local machines to a test environment!
| Tainnor wrote:
| Many frameworks and libraries (ActiveRecord, flyway, etc.)
| allow you to write migration scripts that get automatically
| applied during deployment. In that sense, you can version
| changes to your DB. Of course, it's true that you don't have a
| strict guarantee that your prod DB matches these scripts, but
| as long as you don't change the schema from outside such
| scripts and don't manually rollback/delete them etc., it should
| work out well in practice--at least, I've never had an issue
| with that specific part of migrations (of course, there are
| other issues you can run into with migrations).
| Sebb767 wrote:
| This fails as soon as you have multiple instances of your app
| running (for load balancing or HA reason). Server one might
| still be running version A while server two is running the
| migrations done by version B - leading A to crash or corrupt
| data. If shutting down for the length of the update is not an
| option anymore, you will need to use the process the author
| pointed out.
| pottertheotter wrote:
| I've not looked into it a ton, but would something like
| Pachyderm address this?
| LukeEF wrote:
| Yes - pachyderm is good for managing and running distributed
| data pipelines on flat files
|
| There are also versioned databases like TerminusDB and Dolt
| that try to do similar in the database
| linux2647 wrote:
| Alternatively, my company tends to rely on "testing in
| production" to avoid having different environments. If we need
| to try something new, we use feature gates/flags to only give
| select users access to experimental code paths. It's worked
| well for us, but it does take some getting used to
| 0xbadcafebee wrote:
| > We should manage the revision of data the way we manage the
| revision of our source code.
|
| I think some new features of e.g. Postgres allow for something
| like this, but all SQL databases have the same basic design
| flaw: no inherent version control. SQL Databases are so big and
| complex that they haven't evolved much in 30 years. That's why
| there's so many "new" databases that are either a wrapper
| around Postgres, or trying to re-create _parts_ of a relational
| database from scratch.
|
| It would be great if the next iteration of the SQL standard
| included version control semantics.
| avgDev wrote:
| I use .Net Core, and often make changes to data base through
| the application and the application updates the database in
| both staging/production. This creates a migration history and
| allows me to revert back to a previous database state. However,
| in some instances data is still lost, in that scenario I would
| just do a back up.
|
| This way I can almost painlessly revert back, and restore data.
| twic wrote:
| This stuff is not trivial, but it's also not rocket science.
|
| Production is production. Everywhere else, the database is
| temporary. It is be blown away and brought up to a known,
| source-code-defined state when the application is deployed. You
| never migrate any database except production. You always
| rebuild from scratch. Think of the state of the database as
| being more like the binary than the source code.
|
| The state of a database is defined as the content of a specific
| base dump from production, plus some number of SQL scripts to
| run on top of it.
|
| Base dumps need to be put somewhere accessible. When i worked
| with Oracle, they lived on the server. You run a command to
| create a dump on the server, then get a sysadmin to scp the
| dump file to your dev/test database server. With PostgreSQL,
| you can dump to a local file, so set up a file server or
| something where dumps can go. Could even be in source control,
| using large file support. Base dump files are named with a
| timestamp, GUID, etc.
|
| In the app codebase, there is some config giving the name of
| the base dump, and those additional SQL scripts. There's some
| tool, script, build command, etc, which uses those to rebuild
| the database - delete (or rename!) database, restore base dump,
| run SQL.
|
| You might not want to include everything in the dump. When i
| was doing e-commerce work, we would dump the schema and the
| catalogue data, but not user data (profiles, orders, etc),
| because of data protection. If your database is very big,
| obviously you will have to be very selective about data here.
|
| On one project, the process of rebuilding a database was quite
| slow, because there was a lot of stuff to add on top of the
| dump (not just SQL, some of it used some weird data loader
| tool). To make that faster, we did caching: after a rebuild, we
| would dump the state of the database, then when doing a
| rebuild, check if there was already a suitable dump, and if so,
| just load that. The dump was named as a hash of everything that
| went into it. Easy to get that wrong! For example, the hash
| should include the version of the data loader tool.
|
| When you finally release to production, then as long as the
| dump is similar to what is live, you just run the SQL scripts;
| it's like a dev deploy but without deleting the old database
| and loading a dump (deleting prod and loading a dump of prod
| peephole optimises to doing nothing!). 'Similar' means that the
| schema is the same and the data, if any, is reasonably fresh.
| If the dump is behind production somehow, you have some dev
| work to do where you update to a fresh dump, and amend the SQL
| scripts as needed.
|
| Some time after a release to production, take a fresh dump, and
| propagate it into the source code: update the dump filename in
| config, and remove any SQL scripts which have now been
| released. You don't have to do this right away, because the
| fresh dump should really correspond to the old dump plus the
| scripts anyway.
|
| If there is additional data or schema you need in some lower
| environments, that's fine. Include that as some extra SQL
| scripts which will never be run on production.
| Tainnor wrote:
| I don't understand why you shouldn't migrate non-production
| databases. I agree that there should not be an expectation
| that they are stable (although it can still be useful to keep
| data around in test environments), but if you constantly
| apply migrations upon every deployment (as well as locally),
| you have a good test bed for the production migration.
|
| That gives you at least a base level of sanity checks that
| your migrations work. Of course, this is no guarantee that
| your production migration will also work - you may e.g. add a
| FK constraint but there are violating rows in prod -, but for
| that you can wrap your migration in a transaction. Upon
| failure, the migration is rolled back and, of course, you
| should then also rollback the deployment. Then you can start
| investigating why the migration went wrong.
|
| Also, creating dumps from prod and storing them locally is
| something I don't recommend (and it's also disallowed at the
| company I work for) unless you take care to purge/mask all
| sensitive data (all PII and everything that includes trade
| secrets of some sort) _before_ it reaches your local machine.
| twic wrote:
| I wasn't very clear in my statement about migrations,
| sorry. I'll put it another way: the only migrations you
| should run in dev are ones you intend to eventually run in
| production.
|
| The process of loading a base dump and then running SQL
| scripts is precisely to mimic what will happen when you
| release to production. As you say, this is a good test bed!
|
| What you shouldn't do is try to move between existing
| intermediate, non-production-like, states of the database
| by migrating. And really, what i object to are down
| migrations, because you won't ever use those in production.
| Migrating up is not so bad. But since running individual up
| migrations is something you will never do in production,
| why do it in dev?
|
| Indeed, always running your whole queue of up migrations on
| top of a fresh base dump is a way of catching the kind of
| problem you hit in production, where existing data violates
| a new constraint.
|
| The nuke-and-pave rebuild approach also avoids the problem
| of moving between branches and reverting pushed but
| unreleased schema changes. Those become trivial.
|
| As you say, and as i said in my initial comment, there will
| be data in production that you can't or don't want to use
| in dev for some reason. But that's just another development
| task - you need to take your base dumps in such a way that
| that data is not included. You can exclude certain tables.
| You can only dump certain rows, corresponding to some kind
| of testing user etc (one bank i worked with had a "shadow
| branch" defined in their production systems, a made-up
| branch with made-up customers, that they could use for
| final testing in the actual production environment). You
| can dump into an intermediate database in production,
| anonymise and delete any data you need to, and then take
| the base dump. I'm sure there are loads of ways to do this.
| jimpudar wrote:
| Sometimes you can't use production data in your staging
| environment, like when prevented by privacy laws. Often times
| developers who would not have access to production data would
| have permissions to view data in a staging environment...
|
| Creating tools to produce realistic test data and also using
| fuzzing tools can be just as good and sometimes even better
| than using actual production data.
| LukeEF wrote:
| I'm having a hard time visualizing this - are we talking
| outside devs (consultants) or folks in a different
| jurisdiction who don't have rights under law, or just a
| company administrative policy? Agree that fake data can work
| well, but if you can clone the production DB, that seems a
| preferable and easier approach.
| Silhouette wrote:
| I am struggling to think of any time it _would_ be
| appropriate to clone a production database and use it for
| staging. That almost inevitably translates into any
| developer who ever deploys database-related changes having
| access to the contents of the production database, or at
| least every part of it that might ever be affected by code
| changes they are making. There are numerous legal,
| regulatory and ethical issues with granting such broad
| access, unless you 're small enough that the few people you
| obviously do need to have the ability to grant full DB
| access happen to include anyone who is likely to change
| code that talks to the database. And even then it still
| seems like bad practice to actually grant anyone that
| access when it's not strictly necessary!
| dwohnitmok wrote:
| There's a variety of reasons this may happen that stem from
| either legal, certification, or company reasons.
|
| Your production database may have medically-sensitive PII
| (or for something like SOC-2 compliance any PII at all)
| that cannot be shared any human (other than the original
| user) unless with prior approval.
|
| Even for non-externally mandated reasons, companies may
| (and often do) wish to restrict access to production data
| by developers to minimize concerns around data exfiltration
| and snooping on user data by company employees.
| LukeEF wrote:
| that makes sense - I suppose using my model you could
| mask some of the data in a versioned graph or a
| collection contained in the database that can be surfaced
| up to other users who can then clone the collection that
| excludes PII. You could run the main collection and the
| PII free collection in the same data product. This might
| be an easier approach than creating fake data & fake
| schema.
| dwohnitmok wrote:
| > This might be an easier approach than creating fake
| data & fake schema.
|
| I doubt this. This is for two reasons: the first is that
| the development database usually shares the same schema
| as the production database so that's not an issue.
|
| The second is that fake data convincingly takes care of
| various issues surrounding de-anonymization of data using
| correlations among bits of data that ostensibly have had
| their PII-sensitive bits removed.
|
| If protection of user data is a priority, there are far
| fewer headaches associated with creating entirely fake
| data to populate the same schema than trying to figure
| out post-hoc censoring of production data.
|
| That's not to say there aren't valid use cases of the
| latter. You often will want to do post-hoc
| censoring/aggregation if you wish to track e.g. usage
| metrics. This is in fact often a component of ETLs.
| However, those are removed from everyday development
| tasks.
| ljm wrote:
| Legally. I've worked in healthcare and any direct access to
| production is strictly forbidden. If you can't figure it
| out through your logs and monitoring, and you can't
| reproduce in earlier environments, then the problem isn't
| that you don't have prod access, the problem is that your
| instrumentation has a gap.
|
| Personally, I think we rely on prod access as a crutch
| because it's easier to expect that than it is to build a
| sufficient infrastructure. Cloning a prod database or
| allowing ad-hoc r/w access is on my list of strictly
| forbidden operations.
| zild3d wrote:
| > We should manage the revision of data the way we manage the
| revision of our source code
|
| I believe planetscale is trying to do this, introducing
| branches to the database
| https://docs.planetscale.com/concepts/nonblocking-schema-cha...
| tremon wrote:
| It's really not that difficult if you do the necessary legwork
| beforehand. That basically means, do not access the tables
| directly. Databases offer a read abstraction via views, and a
| write abstraction via stored procedures. Use those in your app,
| and suddenly you can atomically change your database schema
| without your app even having to go offline.
|
| And as long as you take care of backwards compatibility in your
| abstraction layer (i.e. sensible defaults for new input
| columns, hide output columns in the view), you can run both the
| old and the new app side-by-side.
| 0xbadcafebee wrote:
| That requires the developers to be very familiar with
| databases, which they usually are not. And I've never heard a
| single developer in my entire career say "Hey let's talk to a
| DBA before we design and implement this feature".
| bouke wrote:
| I believe the checklist is missing adding any optional
| constraints on the new field. This should be done after deploying
| version 2 (writing to new field) and backfilling existing
| records.
| wdb wrote:
| I hope the next blog article would discuss how to support
| multiple versions of APIs like Stripe does :)
| [deleted]
| cletus wrote:
| One thing I'd add to this is that your database schema and your
| UI data model and/or API are decoupled so you can update your
| front end to the new representation before you do any database
| changes.
|
| One thing I liked was the chosen example is changing Booleans to
| enums. This almost always happens. Your field is almost never a
| Boolean. This goes for code too. We've all read code like:
| foo(false, false, true, null);
|
| This leads to coding standards that all such parameters must be
| commented and breakages when the parameter list is changed. It
| also often results in hacks to squeeze a third value into your
| Boolean (eg Optional<Boolean>).
|
| Or, my favorite: @nullable Optional<Boolean>
|
| As I like to say: for when three values for your Boolean just
| aren't enough.
|
| So yeah, my tip for the day is just make any Boolean an
| enumeration from the outset.
| KptMarchewa wrote:
| One of my favourites features in Python are named parameters. I
| even wish Java could be more verbose in that manner.
| cletus wrote:
| I go back and forth on Python in this regard, specifically
| the named vs positional arguments in that an argument can be
| one or the other.
|
| Part of me prefers just using an anonymous object in
| Javascript. Typescript is also cleaner than that.
|
| Day to day I use Hack (FB's PHP fork), which has a type
| system and shapes, which are essentially anonymous objects in
| the JS/TS sense but a little more awkward syntactically:
| function foo(shape( 'a' => int, 'b' =>
| int, ?'c' => int, )): void;
| foo(shape('a' => 1, 'b' => 2));
|
| But with Python you sort of have this awkward mix of args and
| kwargs.
| klibertp wrote:
| You can mark args as keyword only or positional only in the
| recent versions of Python (starting 3.7, maybe?)
| vbsteven wrote:
| If the project permits it, Kotlin fills that gap in Java
| well. Named parameters + defaults + nullability is a nice
| combination to have when it comes to evolving functions or
| types with backwards compatibility in mind.
| skocznymroczny wrote:
| You'd be surprised how many people are against this feature.
| I thought it'd be a no-brainer when it was proposed in the D
| community, but there was a lot of pushback against the
| feature (it's not implemented yet but it was accepted for the
| language). The main argument was that named arguments will
| cause your code to break when the arguments are renamed.
| makapuf wrote:
| Seems acceptable since names are now part if your
| interface, and you could object that now you can relax
| ordering. Whats more interesting is that python has
| position only and name only arguments, meaning that there
| are uses for both.
| habibur wrote:
| > The main argument was that named arguments will cause
| your code to break when the arguments are renamed.
|
| Here's the counter point : ordered argument list will break
| if the order is re arranged in the defined function. While
| for named arguments it doesn't.
| Sebb767 wrote:
| > Here's the counter point : ordered argument list will
| break if the order is re arranged in the defined
| function. While for named arguments it doesn't.
|
| Counter-counterpoint: Once you add support for named
| arguments, your cannot do either, as you don't know which
| convention the caller uses.
| Cthulhu_ wrote:
| enum Bool { True, False,
| FileNotFound };
| jeffbee wrote:
| Don't ever make True be the first item in an enumeration if
| there are any languages in your stack that initialize them by
| default. Make the default item be
| InvalidIForgotToInitializeThis.
| athenot wrote:
| This is good advice since some DBs (like MySQL) store booleans
| as 8 bit ints anyways.
| davidkunz wrote:
| (Disclaimer: I work here)
|
| The SAP Cloud Application Programming Model (CAP) let's you
| derive your DB schema and UI from one semantic data model. The
| runtime framework interprets this and provides (deep) CRUD
| functionality out of the box. Business logic can then be inserted
| using different request handlers. Therefore you have (depending
| on additional business logic) usually only one place to define
| your model.
|
| https://cap.cloud.sap/docs/
| mustardo wrote:
| Wow such spam, and SAP wow... What trash
| jaywalk wrote:
| You're clearly not a spammer so I want to go easy on you, but
| nobody on HN is looking for a SAP sales pitch. I can promise
| you that.
| davidkunz wrote:
| I'm sorry, it wasn't meant to be a sales pitch (it's free to
| use anyway), I just wanted to show another way to deal with
| the "you change your database model, now you have to change
| many other places too" problem.
| cromulent wrote:
| I didn't think you were shilling, but I was a little
| disappointed not to get a deep link to the docs for that
| specific capability :)
| davidkunz wrote:
| It's all built around that feature. You define your model
| using the language CDS (core data services), after
| compilation, you'll get a JSON representing the model
| (used by the generic runtime, either Node.js or Java),
| table/view SQL statements for the databases SQLite/HANA
| and OData metadata used by generic UIs. That means you
| semantically define your model and get all the artifacts
| needed to run the application. With generic support for
| most "enterprise qualities" like authorization,
| localization, draft support, extensibility etc.
| https://cap.cloud.sap/docs/get-started/in-a-nutshell
| gives a first glimpse. But again, I don't want to
| advertise. Like everything else, this approach has pros
| and cons, just wanted to give some insights how we dealt
| with that problem.
___________________________________________________________________
(page generated 2021-06-09 23:02 UTC)