3 ms·

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 n

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

> 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.