4 ms·

Once your schema changes take more than a few minutes, yes. There's a lot of toil and burden if you need to take down your application every time you need a sch

Once your schema changes take more than a few minutes, yes. There's a lot of toil and burden if you need to take down your application every time you need a schema change. Announcements, coordination with internal teams and customers and then coordinating with other engineers. We aren't talking about zero downtime here, but continual, recurring downtime due to schema changes. Once you have beyond a few million rows in a normal RDBMS, schema changes can take minutes to hours depending on the type. Do this a few times per month and you now have 'lots' of downtime and you are blocking other engineering work from happening. It eventually becomes so much of a hassle that engineers don't want to do schema changes, blocking feature work. The more seamless and painless you can make them, the better.

salawat 3y ago
Perhaps we shouldn't be collecting and retaining such large datasets that these issues become such a pressing problem?
- paulddraper 3y ago
  Interesting, elaborate.
- klooney 3y ago
  I feel like you're implying that this is caused by personal data collection and tracking, but it's not- you can get there pretty easily, in a small to medium sized app, with just user tables, or users + things configured. The giant data lakes for vacuuming up tracking data generally never do schema migrations at all.
_a_a_a_ 3y ago
I'm going to have to be a bit contrary here. How often do you expect to make the schema changes? I mean I quoted this bit "...make schema changes sometimes on a daily basis" – is this realistic, or a kind of business insanity typically caused by bad management? Ditto "...but continual, recurring downtime due to schema changes". This really looks like a failure of management rather than a technical problem to be solved. Also aren't you likely to be doing something larger than just a schema change very often, in which case that would necessitate replacing your application, so changes are not just restricted to the database. You now have a bigger problem of co-ordinating app and DB changes. I also asked to do you need permanent uptime because in a lot of systems, especially smaller ones (and by the long tail most systems are going to be smallish) the users are very tolerant of an hours' downtime a month, for example. "Once you have beyond a few million rows in a normal RDBMS, schema changes can take minutes to hours depending on the type" That's a pretty strong claim; what kind of thing is going to take hours that your database can do consistently? Does it even take hours? I had a 100,000,000 row table of unique ints lying around so I put a foreign key from itself to itself (a bit daft, but just for timing purposes. DB is MS SQL, table is fully hot in memory) alter table [tmp_ints_clustered] add constraint ffffkkkkk foreign key (x) references [tmp_ints_clustered](x); 21 seconds. What you're doing (if you can get it correct! Which I have to wonder at) is doubtless excellent for some very large companies, but in general... I'm afraid I'm not so sure. Edit: I feel I'm perhaps missing your bigger picture.
- rdoherty 3y ago
  [dead]
- dylan604 3y ago
  I agree with your push back. Even in DEV, I'm not making daily schema changes. in fact, I hate schema changes and go back and forth on if the change is really necessary. sure, changes do become necessary, but sheesh, daily is a sign to me that something else needs to be looked at in the dev cycle. like, is nobody forward thinking enough to come up with a workable schema. is the requirements truly being made by the seat of the pants. also, are the new schema requests really necessary to existing tables, or can we hang a new table and extend the joins? seems like taking a bit of time to do some forward thinking on the initial schema should keep daily changes from existing
  - saltcured 3y ago
    On the other hand, a reluctance to do schema changes often just leads to a de facto schemaless system if new concepts start getting hacked into generic fields instead of properly modeled. When you make schema evolution really easy to do with no downtime, you can also start doing phased deployments where you think in terms of backwards-compatibility. First you add some new/optional parts to the schema. Then you update the applications to use it, where the application has to tolerate data with or without the new bits. Eventually, you might convert something from optional to required, first for new data entry and later as a conversion of existing data, where that makes sense. Then finally you might deprecate the application ability to handle the old missing bits, and remove that from your code maintenance problems.
    - _a_a_a_ 3y ago
      Maybe proper releases, running on different machines, might be a better option most of the time.

salawat 3y ago

Perhaps we shouldn't be collecting and retaining such large datasets that these issues become such a pressing problem?

paulddraper 3y ago
Interesting, elaborate.
klooney 3y ago
I feel like you're implying that this is caused by personal data collection and tracking, but it's not- you can get there pretty easily, in a small to medium sized app, with just user tables, or users + things configured. The giant data lakes for vacuuming up tracking data generally never do schema migrations at all.

_a_a_a_ 3y ago

I'm going to have to be a bit contrary here. How often do you expect to make the schema changes? I mean I quoted this bit "...make schema changes sometimes on a daily basis" – is this realistic, or a kind of business insanity typically caused by bad management? Ditto "...but continual, recurring downtime due to schema changes". This really looks like a failure of management rather than a technical problem to be solved. Also aren't you likely to be doing something larger than just a schema change very often, in which case that would necessitate replacing your application, so changes are not just restricted to the database. You now have a bigger problem of co-ordinating app and DB changes. I also asked to do you need permanent uptime because in a lot of systems, especially smaller ones (and by the long tail most systems are going to be smallish) the users are very tolerant of an hours' downtime a month, for example. "Once you have beyond a few million rows in a normal RDBMS, schema changes can take minutes to hours depending on the type" That's a pretty strong claim; what kind of thing is going to take hours that your database can do consistently? Does it even take hours? I had a 100,000,000 row table of unique ints lying around so I put a foreign key from itself to itself (a bit daft, but just for timing purposes. DB is MS SQL, table is fully hot in memory) alter table [tmp_ints_clustered] add constraint ffffkkkkk foreign key (x) references [tmp_ints_clustered](x); 21 seconds. What you're doing (if you can get it correct! Which I have to wonder at) is doubtless excellent for some very large companies, but in general... I'm afraid I'm not so sure. Edit: I feel I'm perhaps missing your bigger picture.

rdoherty 3y ago
[dead]
dylan604 3y ago
I agree with your push back. Even in DEV, I'm not making daily schema changes. in fact, I hate schema changes and go back and forth on if the change is really necessary. sure, changes do become necessary, but sheesh, daily is a sign to me that something else needs to be looked at in the dev cycle. like, is nobody forward thinking enough to come up with a workable schema. is the requirements truly being made by the seat of the pants. also, are the new schema requests really necessary to existing tables, or can we hang a new table and extend the joins? seems like taking a bit of time to do some forward thinking on the initial schema should keep daily changes from existing
- saltcured 3y ago
  On the other hand, a reluctance to do schema changes often just leads to a de facto schemaless system if new concepts start getting hacked into generic fields instead of properly modeled. When you make schema evolution really easy to do with no downtime, you can also start doing phased deployments where you think in terms of backwards-compatibility. First you add some new/optional parts to the schema. Then you update the applications to use it, where the application has to tolerate data with or without the new bits. Eventually, you might convert something from optional to required, first for new data entry and later as a conversion of existing data, where that makes sense. Then finally you might deprecate the application ability to handle the old missing bits, and remove that from your code maintenance problems.
  - _a_a_a_ 3y ago
    Maybe proper releases, running on different machines, might be a better option most of the time.