7 ms·

I'm a bit confused with the "branching" [0] and "non-blocking schema changes" [1] features. I'm confused as they sound like "the next big thing" and I don't see

I'm a bit confused with the "branching" [0] and "non-blocking schema changes" [1] features. I'm confused as they sound like "the next big thing" and I don't see anything special here. Not saying are bad concepts or ideas, the contrary. But not really useful either. Surely I'm missing something, so I would love to hear from the PlanetScale team here if possible. I have a strong and long Postgres operational background, so I may be also here with assumptions that might be different in MySQL/Vitess/PlanetScale. My main concerns/questions are: * I can't imagine testing DDL changes without data. Having data there is so important to understand the change and its impact, that I won't do them without data. And unless I'm mistaken, these branches only contain DDL, no data at all ("data from the main database is not copied to development branches"). * While it sounds neat, has a web UI and a CLI, managing branches of a schema and using CI and approval lifecycle... is something that sounds like I could do, and possibly better (as it is more integrated with tooling and workflows) from Git platforms themselves, isn't it? I could do branches, merges, CI, comments on MRs, approval... I could even easily build a deploy queue ("promote") with a CI. Doesn't sound like too hard. * I don't understand how the "safeness" and the non-blocking nature of changes are ensured. Many DDL changes will take different amount of locks on rows or tables, which may cause some queuing and even lock storms in the presence of incoming traffic. Without incoming traffic, they may run fine. In other words: the impact of a migration can only be determined in combination with the traffic hitting production. How does PlanetScale do this? How for example is handled the case where a DDL changes the type of a column to another type which causes a table rewrite, which essentially locks the table and prevents concurrent writes? Again, not saying both concepts are bad. Terminology and methodology may be already an innovation. And surely I'm missing a lot. But other than this, I don't see myself using this (testing migrations without data is a showstopper, and not the only one) and I don't see much of an innovation from a safeness perspective here. Why this system isn't one where thin clones of the database are created as the branches (e.g. like in Database Lab Engine [2]), where you can play with data too, and then some data synchronization is performed to switch over to the branch once done (is not easy at all, but doable with many precautions)? That would be a significant improvement in the process, IMHO. [0]: https://docs.planetscale.com/concepts/branching https://docs.planetscale.com/concepts/branching [1]: https://docs.planetscale.com/concepts/nonblocking-schema-changes https://docs.planetscale.com/concepts/nonblocking-schema-cha... [2]: https://postgres.ai/products/realistic-test-environments https://postgres.ai/products/realistic-test-environments

shlomi-noach 5y ago
Hi, engineer at PlanetScale and maintainer for Vitess here. Appreciate your thoughtful comment, a couple answers: > is something that sounds like I could do ... from Git platforms themselves Git is very bad at analyzing SQL diffs. It can show you the textual diff between two CREATE TABLE statements, but it will not know what it takes to get you from _here_ to _there_. It has many parsing issues, like capturing irrelevant columns due to trailing commas. Or, for example, if you change a column's data type as per your suggestion, Git cannot differentiate that from a complete drop and recreation of the column. It just doesn't have insight into how SQL works/parses. And most importantly, it is unable to provide the actual operational diff you're seeking: the ALTER TABLE statement to take you from state A to state B. At this point I just want to give a shout out to skeema [0] and its underlying library tengo [1], which tackled this issue in a git-like manner for MySQL dialects/flavors. > Many DDL changes will take different amount of locks on rows or tables, which may cause some queuing and even lock storms in the presence of incoming traffic. This is indeed one of our main premises. Online schema changes in PlanetScale (and based on Vitess) will: - Run concurrently to your production traffic - Will automatically throttle when your production traffic gets too high, and in particular taking care not to affect replication lag - Will run completely lockless throughout the migration, up to the cut-over point, where locking is required - At cut-over point, will only cut-over when it predicts smooth operation (i.e. when satisfied that its own backlog for cutting over is short enough that lock time is minimal) - To top it all, PS also manages the lifecycle of the Online DDL, such as service discovery, scheduling, error handling, throttling (mentioned), cleanup and garbage collection and more. I just want to clarify the above is based on proven technologies, widely used in the MySQL community, which I was fortunate enough to be involved in for the past years. These run at scale for the largest deployment in the world today. We keep evolving Online DDL with more to come. Please also see these docs on the Vitess website: [2] [0]: https://www.skeema.io/ https://www.skeema.io/ [1]: https://github.com/skeema/tengo https://github.com/skeema/tengo [2]: https://vitess.io/docs/user-guides/schema-changes/#the-schema-change-problem https://vitess.io/docs/user-guides/schema-changes/#the-schem... (Comment edited for formatting and grammar)
- ahachete 5y ago
  Thank you for your comments, I appreciate it. I'm still not sold, however. I would like to understand the underlying principles, "how this works". I don't need implementation details (happy if they are shared, though) but more on the main principles of operation. Please see my further comments below: > Git is very bad at analyzing SQL diffs. Agreed, nothing against. So PS has built-in a nice SQL diff. Neat! But what this really brings? I mean, it's not that there aren't SQL diff tools, tools to manage DDL migrations. Besides this, why not layer it on top of Git? Many orgs and integration tools already have similar workflows (e.g. approval workflows, issue management tools, CI, etc) and if instead of coming up with a new system it would be a layer on top of the existing ones, it would probably have less friction to use. Just my perspective on this, of course. > Run concurrently to your production traffic Can you elaborate? How? Do they run on another servers? Or are they waiting on a queue change waiting to be applied? If they run on different servers, what they run there, since AFAIK the migration is only DDL, there's no data? > Will automatically throttle when your production traffic gets too high, and in particular taking care not to affect replication lag Same as above: who will throttle, the migration? But what is the migration? Let's use my example: a column type change requires a table rewrite. So the table rewrite will throttle, i.e. slow down? But where is this table rewrite running, on the main server (apparently not) or on a shadow server (apparently either since migrations have no data)? Actually you mention "when your production traffic gets too high". What is "high", can you quantify? We run customers that do dozens to thousands of transactions per second. Is this high enough? Will their migrations ever run, or will wait for very long periods of time, maybe forever? > Will run completely lockless throughout the migration How is this possible? Where the migration is running, then? A shadow table, shadow server... none? > At cut-over point What's cut-over? Are groups of servers switched? This is what it sounds to me, and that would explain how it could be lock-less and not affecting production traffic. However, it does not explain how data is synchronized from the production database to the migration branch, nor how it keeps being updated with the real production traffic. This is essentially the crux of me failing to understand how this system works. In general, I apologize if these are too many questions. But in essence, I feel this all sounds really well, but unless I have a deeper understanding of how the principles work, and they are sound to me, I won't be able to recommend this for production usage, as I know from experience the many caveats migrations have. If they are all solved, hats off, but I would appreciate if from a technical perspective this would be more clearly explained. Thank you!

shlomi-noach 5y ago

Hi, engineer at PlanetScale and maintainer for Vitess here. Appreciate your thoughtful comment, a couple answers: > is something that sounds like I could do ... from Git platforms themselves Git is very bad at analyzing SQL diffs. It can show you the textual diff between two CREATE TABLE statements, but it will not know what it takes to get you from _here_ to _there_. It has many parsing issues, like capturing irrelevant columns due to trailing commas. Or, for example, if you change a column's data type as per your suggestion, Git cannot differentiate that from a complete drop and recreation of the column. It just doesn't have insight into how SQL works/parses. And most importantly, it is unable to provide the actual operational diff you're seeking: the ALTER TABLE statement to take you from state A to state B. At this point I just want to give a shout out to skeema [0] and its underlying library tengo [1], which tackled this issue in a git-like manner for MySQL dialects/flavors. > Many DDL changes will take different amount of locks on rows or tables, which may cause some queuing and even lock storms in the presence of incoming traffic. This is indeed one of our main premises. Online schema changes in PlanetScale (and based on Vitess) will: - Run concurrently to your production traffic - Will automatically throttle when your production traffic gets too high, and in particular taking care not to affect replication lag - Will run completely lockless throughout the migration, up to the cut-over point, where locking is required - At cut-over point, will only cut-over when it predicts smooth operation (i.e. when satisfied that its own backlog for cutting over is short enough that lock time is minimal) - To top it all, PS also manages the lifecycle of the Online DDL, such as service discovery, scheduling, error handling, throttling (mentioned), cleanup and garbage collection and more. I just want to clarify the above is based on proven technologies, widely used in the MySQL community, which I was fortunate enough to be involved in for the past years. These run at scale for the largest deployment in the world today. We keep evolving Online DDL with more to come. Please also see these docs on the Vitess website: [2] [0]: https://www.skeema.io/ https://www.skeema.io/ [1]: https://github.com/skeema/tengo https://github.com/skeema/tengo [2]: https://vitess.io/docs/user-guides/schema-changes/#the-schema-change-problem https://vitess.io/docs/user-guides/schema-changes/#the-schem... (Comment edited for formatting and grammar)

ahachete 5y ago
Thank you for your comments, I appreciate it. I'm still not sold, however. I would like to understand the underlying principles, "how this works". I don't need implementation details (happy if they are shared, though) but more on the main principles of operation. Please see my further comments below: > Git is very bad at analyzing SQL diffs. Agreed, nothing against. So PS has built-in a nice SQL diff. Neat! But what this really brings? I mean, it's not that there aren't SQL diff tools, tools to manage DDL migrations. Besides this, why not layer it on top of Git? Many orgs and integration tools already have similar workflows (e.g. approval workflows, issue management tools, CI, etc) and if instead of coming up with a new system it would be a layer on top of the existing ones, it would probably have less friction to use. Just my perspective on this, of course. > Run concurrently to your production traffic Can you elaborate? How? Do they run on another servers? Or are they waiting on a queue change waiting to be applied? If they run on different servers, what they run there, since AFAIK the migration is only DDL, there's no data? > Will automatically throttle when your production traffic gets too high, and in particular taking care not to affect replication lag Same as above: who will throttle, the migration? But what is the migration? Let's use my example: a column type change requires a table rewrite. So the table rewrite will throttle, i.e. slow down? But where is this table rewrite running, on the main server (apparently not) or on a shadow server (apparently either since migrations have no data)? Actually you mention "when your production traffic gets too high". What is "high", can you quantify? We run customers that do dozens to thousands of transactions per second. Is this high enough? Will their migrations ever run, or will wait for very long periods of time, maybe forever? > Will run completely lockless throughout the migration How is this possible? Where the migration is running, then? A shadow table, shadow server... none? > At cut-over point What's cut-over? Are groups of servers switched? This is what it sounds to me, and that would explain how it could be lock-less and not affecting production traffic. However, it does not explain how data is synchronized from the production database to the migration branch, nor how it keeps being updated with the real production traffic. This is essentially the crux of me failing to understand how this system works. In general, I apologize if these are too many questions. But in essence, I feel this all sounds really well, but unless I have a deeper understanding of how the principles work, and they are sound to me, I won't be able to recommend this for production usage, as I know from experience the many caveats migrations have. If they are all solved, hats off, but I would appreciate if from a technical perspective this would be more clearly explained. Thank you!