4 ms·

You may be right about pgroll specifically, I haven't looked at it closely. However, you can't really say "just do db migrations in a safe way". The way your DB

You may be right about pgroll specifically, I haven't looked at it closely. However, you can't really say "just do db migrations in a safe way". The way your DBA staff would "apply migrations safely" would be to use ghost-tables and views and triggers and locks - ie: they would write pt-online-schema-change or gh-ost or VReplication or, well, pgroll. These tools were born at places like Facebook or Github by the team responsible for applying migrations safely. The argument that "all software has bugs" applies to both the database itself as well as the software you're writing on top. Hence why "reversible" is the 2nd selling-point here.

thayne 2y ago
And if you don't use a tool like that (whether off the shelf, or home built), then your DBAs will be a bottleneck to any schema changes, which will lead to them being overworked and stressed, which will lead to them making more mistakes.
- ltbarcly3 2y ago
  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.

thayne 2y ago

And if you don't use a tool like that (whether off the shelf, or home built), then your DBAs will be a bottleneck to any schema changes, which will lead to them being overworked and stressed, which will lead to them making more mistakes.

ltbarcly3 2y ago
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.