4 ms·

Here I'll give you one: If you want to change a property of the database that will give your specific use improved performance, you have no way to transactional

Here I'll give you one: If you want to change a property of the database that will give your specific use improved performance, you have no way to transactionally apply that change. Rolling back becomes a problem of operational scale, rolling out as well. What if you need to release some feature, but that feature requires a database feature enabled? Normally you enable it once, in a transaction hopefully, and then roll out your application. With this style you have to wait for N database servers to connect, enable, validate, then go live before you can even attempt the application being deployed, much less if you get it wrong.

lonelappde 6y ago
You roll the feature out incrementally to the users who are ready. You build backwards compatible features. Basic software engineering.
- sah2ed 6y ago
  > You roll the feature out incrementally to the users who are ready. You build backwards compatible features. Basic software engineering. The parent mentioned having to support ~150 B2B customers, so the effort is amplified x100 — more than 100 individual customers databases have to be coddled as you’ve described, albeit they are stuck with poor tooling to manage changes across their unusual architecture.
  - magicalhippo 6y ago
    While not a web-app, we too have ~200 B2B customers running our application and each one have their own DB. Some self-host, most others are hosted by us. We have written our own tool to upgrade the DB schema, it's nothing fancy, just takes an XML description, compares with the current DB and makes changes. However it ensures the upgrade process is automated. New versions of our application come with a new DB schema, and we also have an auto-updater for our software. We have a small launcher application which allows the users to select a few older versions of our software, so upgrades are almost always safe in the sense that if the user encounters a bug with the new version, they can try in the previous one. If the DB schema upgrade tool fails for some reason, the application upgrade will be held back and we'll get a notification. Combined this allows us to be quite aggressive with pushing out new versions, and to do so without manual intervention. A limitation with this setup is that DB changes have to be backwards compatible. So we do have a few cases of "if this table exists do this, else do that" type logic and similar, but mostly it's not a big deal. For example I recently had to refactor some old table where they had used five columns for some additional references (ref_1, ref_2 etc), into a new table of additional references. To handle this I just made a new view which would return either the refs from the new table, or the data from the columns in case the new table didn't have any data for that record. I then changed the application to use a grid instead of five separate edit controls, and to use this new view. If the user had to use an older version, that version would just write to the five fields and not populate a row in the new table, and the view would then return this data if the user later viewed it in the new version of our application. So the "overhead" of backwards compatibility in this case was just ensuring this would be sufficient and writing that view, so just a few minutes.
  - rbanffy 6y ago
    Doing the same thing to 100 databases is usually easy to automate. I managed a group that had a dozen customers on a single multi-tenant database and some migrations took more than a day - we needed to set maintenance windows on weekends (and once we had to roll back after an error). Having a dozen databases we could roll out updates to on a client per client basis would have saved us from some enormous headaches. OTOH, no two companies are alike. You do you.
- orisho 6y ago
  Sure, this problem can be solved as can any other -- but there's a cost to it in development time. For every feature, the dev working on it had to do it in 3 steps to ensure backwards compatibility and handle partial migration failures gracefully. Imagine doing this for a small feature - the ops effort dwarfs the actual feature! Many small features would probably be dropped.
  - gomox 6y ago
    Multi-single-tenant actually makes it easier to do transactional schema upgrades because these operations only impact one customer at a time. No fiddling with middle-of-the-migration state.
    - 411111111111111 6y ago
      Depends on your uptime guarantees. It becomes a non-issue if you're able to go down for a few moments. Not necessarily viable for some software though, it just depends on what you're running. (Especially because you can apply the migration on slave and subsequently switch master, making this downtime as quick as the time your system needs to do a database switch.) This doesn't apply to big services with millions of users, however.
jcims 6y ago
For this architecture to work you'd likely have to be able to operate more than one release at a time and move customers along with their database. If you're able to do that then this becomes much less of an issue.
mathieuh 6y ago
I’m not a database architect, but can’t you just add the column or whatever as a nullable field in one migration, update the application to write to the column, and then make the field non-nullable in a further migration? As I say I’m no expert but this is what I’ve done previously.
- gomox 6y ago
  You can, but a transactional database migration is much nicer, predictable and less prone to bugs.

lonelappde 6y ago

You roll the feature out incrementally to the users who are ready. You build backwards compatible features. Basic software engineering.

sah2ed 6y ago
> You roll the feature out incrementally to the users who are ready. You build backwards compatible features. Basic software engineering. The parent mentioned having to support ~150 B2B customers, so the effort is amplified x100 — more than 100 individual customers databases have to be coddled as you’ve described, albeit they are stuck with poor tooling to manage changes across their unusual architecture.
- magicalhippo 6y ago
  While not a web-app, we too have ~200 B2B customers running our application and each one have their own DB. Some self-host, most others are hosted by us. We have written our own tool to upgrade the DB schema, it's nothing fancy, just takes an XML description, compares with the current DB and makes changes. However it ensures the upgrade process is automated. New versions of our application come with a new DB schema, and we also have an auto-updater for our software. We have a small launcher application which allows the users to select a few older versions of our software, so upgrades are almost always safe in the sense that if the user encounters a bug with the new version, they can try in the previous one. If the DB schema upgrade tool fails for some reason, the application upgrade will be held back and we'll get a notification. Combined this allows us to be quite aggressive with pushing out new versions, and to do so without manual intervention. A limitation with this setup is that DB changes have to be backwards compatible. So we do have a few cases of "if this table exists do this, else do that" type logic and similar, but mostly it's not a big deal. For example I recently had to refactor some old table where they had used five columns for some additional references (ref_1, ref_2 etc), into a new table of additional references. To handle this I just made a new view which would return either the refs from the new table, or the data from the columns in case the new table didn't have any data for that record. I then changed the application to use a grid instead of five separate edit controls, and to use this new view. If the user had to use an older version, that version would just write to the five fields and not populate a row in the new table, and the view would then return this data if the user later viewed it in the new version of our application. So the "overhead" of backwards compatibility in this case was just ensuring this would be sufficient and writing that view, so just a few minutes.
- rbanffy 6y ago
  Doing the same thing to 100 databases is usually easy to automate. I managed a group that had a dozen customers on a single multi-tenant database and some migrations took more than a day - we needed to set maintenance windows on weekends (and once we had to roll back after an error). Having a dozen databases we could roll out updates to on a client per client basis would have saved us from some enormous headaches. OTOH, no two companies are alike. You do you.
orisho 6y ago
Sure, this problem can be solved as can any other -- but there's a cost to it in development time. For every feature, the dev working on it had to do it in 3 steps to ensure backwards compatibility and handle partial migration failures gracefully. Imagine doing this for a small feature - the ops effort dwarfs the actual feature! Many small features would probably be dropped.
- gomox 6y ago
  Multi-single-tenant actually makes it easier to do transactional schema upgrades because these operations only impact one customer at a time. No fiddling with middle-of-the-migration state.
  - 411111111111111 6y ago
    Depends on your uptime guarantees. It becomes a non-issue if you're able to go down for a few moments. Not necessarily viable for some software though, it just depends on what you're running. (Especially because you can apply the migration on slave and subsequently switch master, making this downtime as quick as the time your system needs to do a database switch.) This doesn't apply to big services with millions of users, however.

jcims 6y ago

For this architecture to work you'd likely have to be able to operate more than one release at a time and move customers along with their database. If you're able to do that then this becomes much less of an issue.

mathieuh 6y ago

I’m not a database architect, but can’t you just add the column or whatever as a nullable field in one migration, update the application to write to the column, and then make the field non-nullable in a further migration? As I say I’m no expert but this is what I’ve done previously.

gomox 6y ago
You can, but a transactional database migration is much nicer, predictable and less prone to bugs.