3 ms·

I think you are way out of your realm of experience here. Nope, schema changes are not that common compared to other tasks and will only account for a small fr

I think you are way out of your realm of experience here. Nope, schema changes are not that common compared to other tasks and will only account for a small fraction of your DBA's day. I can say this from copious (over 20 years) experience. They will make FAR fewer mistakes than some random engineer. It takes less time (like a fraction, 1/20 or less) to validate and give feedback on schema changes than it does to untangle the mess that gets made otherwise, restore data from backups, go to meetings to explain outages to executives, etc etc so if you want to keep your DBA's happy and able to sign out of work at a reasonable hour, make sure they get to approve schema changes before they go out. Not to mention that very few engineers have a good understanding of schema design or how to write performant schemas, how to index data, etc. Getting a good migration the first time is just so much less painful and time consuming than trying to deal with bad ones.

thayne 2y ago
> It takes less time (like a fraction, 1/20 or less) to validate and give feedback on schema changes But that's the thing, with tools like this, pt-online-schema-change etc., it is reasonable for a DBA to review changes written by other engineers. Without them, when you have extremely large data sets, it is necessary to do magic with triggers, views, etc. to safely make certain kinds of changes. And doing that is beyond the experience of most non-dba engineers, and has a lot of nuance to it. I'm not saying DBAs shouldn't have to approve schema changes. I'm saying they shouldn't have to write complicated migration scripts by hand. > I think you are way out of your realm of experience here. Or maybe my experience is different than yours.
- ltbarcly3 2y ago
  Dude, if you have "extremely large datasets" then why are you acting like your advice is generally applicable. Fine, for db's with trillions of rows then you need to be extremely careful when doing migrations. However, migrations won't be faster when you use a tool like this, they will be slower. So maybe you are fine with migrations taking weeks and weeks to complete, but in that case how many migrations are you running that your DBA's are overwhelmed by the volume of migrations to approve? Which one is true?
  - thayne 2y ago
    > Dude, if you have "extremely large datasets" then why are you acting like your advice is generally applicable I'm not. At least that wasn't my intention. My reply was in the context of "bigger and more important" companies, which are also more likely to have extremely large datasets, and where taking long, or even short periods of downtime for a database migration is unacceptable. > migrations won't be faster when you use a tool like this, they will be slower Sure, but they can be run without taking downtime. > So maybe you are fine with migrations taking weeks and weeks to complete, but in that case how many migrations are you running that your DBA's are overwhelmed by the volume of migrations to approve? Which one is true? Both can be true. Most migrations will take a lot less time, maybe minutes, maybe hours, sometimes days. Migrations that take weeks are rare, but they do happen. But, having a migration take several hours with no downtime can often be preferable to taking a few minutes of downtime, even if you are ok with having scheduled downtime, because it will unblock work dependent on the new schema sooner. Of course, it depends on your situation. If schema changes are rare, you probably don't need to worry about something like this. If your tables are small enough that schema changes can be done quickly with minimal or no downtime, then you probably don't need this. But there are cases where tools like this fill a need.
  - evanelias 2y ago
    > migrations won't be faster when you use a tool like this, they will be slower That statement is conflating some things, re: "tool like this". In most cases, schema management tooling is a separate layer above online schema change tooling. Online schema change tools (e.g. pt-online-schema-change) are designed to execute table alterations in a non-disruptive fashion, even if the table is huge and/or being written heavily. These tools are indeed often slower than running an ALTER TABLE directly in the database, because the non-disruptive design involves a more complex set of operations occurring in the background. No real way around that; it's a trade-off. In contrast, schema management tools -- ranging from simple migration tools, to more complex services/daemons -- are focused on tracking schema information (either imperative migrations or declarative desired-state) in a source control repo, and executing SQL either directly or by shelling out to another tool. They sometimes include more complex components such as schema linters, verification logic, schedulers, shard mappings, web GUIs, APIs for checking whether a change has been completed yet, etc. Pgroll is rather unusual in that it overlaps into both domains. That approach has pros and cons, but that isn't my point here; rather, I'd say it's best to avoid generalizing about "tools like this" when discussing a tool that works differently than most other software in the same space. That aside, as someone who has spent most of the majority of the past two decades working on database infrastructure at a wide range of different scales and companies, I largely disagree with your statement about schema changes being a trivial part of the database team's day. In my experience, that only happens in two scenarios, at opposite ends of a spectrum: either a company that isn't making many product changes and therefore doesn't have many corresponding schema changes; OR a company that has enough schema changes for safe self-service schema management automation to be a worthwhile investment. Most "tech" companies do fall into that latter extreme though, and greatly benefit from self-service schema management automation. This is about product development velocity, not table size.

thayne 2y ago

> It takes less time (like a fraction, 1/20 or less) to validate and give feedback on schema changes But that's the thing, with tools like this, pt-online-schema-change etc., it is reasonable for a DBA to review changes written by other engineers. Without them, when you have extremely large data sets, it is necessary to do magic with triggers, views, etc. to safely make certain kinds of changes. And doing that is beyond the experience of most non-dba engineers, and has a lot of nuance to it. I'm not saying DBAs shouldn't have to approve schema changes. I'm saying they shouldn't have to write complicated migration scripts by hand. > I think you are way out of your realm of experience here. Or maybe my experience is different than yours.

ltbarcly3 2y ago
Dude, if you have "extremely large datasets" then why are you acting like your advice is generally applicable. Fine, for db's with trillions of rows then you need to be extremely careful when doing migrations. However, migrations won't be faster when you use a tool like this, they will be slower. So maybe you are fine with migrations taking weeks and weeks to complete, but in that case how many migrations are you running that your DBA's are overwhelmed by the volume of migrations to approve? Which one is true?
- thayne 2y ago
  > Dude, if you have "extremely large datasets" then why are you acting like your advice is generally applicable I'm not. At least that wasn't my intention. My reply was in the context of "bigger and more important" companies, which are also more likely to have extremely large datasets, and where taking long, or even short periods of downtime for a database migration is unacceptable. > migrations won't be faster when you use a tool like this, they will be slower Sure, but they can be run without taking downtime. > So maybe you are fine with migrations taking weeks and weeks to complete, but in that case how many migrations are you running that your DBA's are overwhelmed by the volume of migrations to approve? Which one is true? Both can be true. Most migrations will take a lot less time, maybe minutes, maybe hours, sometimes days. Migrations that take weeks are rare, but they do happen. But, having a migration take several hours with no downtime can often be preferable to taking a few minutes of downtime, even if you are ok with having scheduled downtime, because it will unblock work dependent on the new schema sooner. Of course, it depends on your situation. If schema changes are rare, you probably don't need to worry about something like this. If your tables are small enough that schema changes can be done quickly with minimal or no downtime, then you probably don't need this. But there are cases where tools like this fill a need.
- evanelias 2y ago
  > migrations won't be faster when you use a tool like this, they will be slower That statement is conflating some things, re: "tool like this". In most cases, schema management tooling is a separate layer above online schema change tooling. Online schema change tools (e.g. pt-online-schema-change) are designed to execute table alterations in a non-disruptive fashion, even if the table is huge and/or being written heavily. These tools are indeed often slower than running an ALTER TABLE directly in the database, because the non-disruptive design involves a more complex set of operations occurring in the background. No real way around that; it's a trade-off. In contrast, schema management tools -- ranging from simple migration tools, to more complex services/daemons -- are focused on tracking schema information (either imperative migrations or declarative desired-state) in a source control repo, and executing SQL either directly or by shelling out to another tool. They sometimes include more complex components such as schema linters, verification logic, schedulers, shard mappings, web GUIs, APIs for checking whether a change has been completed yet, etc. Pgroll is rather unusual in that it overlaps into both domains. That approach has pros and cons, but that isn't my point here; rather, I'd say it's best to avoid generalizing about "tools like this" when discussing a tool that works differently than most other software in the same space. That aside, as someone who has spent most of the majority of the past two decades working on database infrastructure at a wide range of different scales and companies, I largely disagree with your statement about schema changes being a trivial part of the database team's day. In my experience, that only happens in two scenarios, at opposite ends of a spectrum: either a company that isn't making many product changes and therefore doesn't have many corresponding schema changes; OR a company that has enough schema changes for safe self-service schema management automation to be a worthwhile investment. Most "tech" companies do fall into that latter extreme though, and greatly benefit from self-service schema management automation. This is about product development velocity, not table size.