Hi everyone, thank you for coming, my name is Lominoach, and this session is about
skip a diff.
This is a VTEST library for in-memory schema analysis, validation, organization, different
and manipulation.
So this is a mouthful, we're going to mostly concentrate around the idea of the diff, given
to tables or to schema, to get the semantic diff that transforms from one schema to the
other.
This could be all the tables created, we'll drop view, whatever it takes to get from here
to there.
And what I want to do is to walk through what it takes for common schema, for schema
diff, to get to that, to be able to do that.
And mostly concentrate around the idea of the diff and consider a few things that we don't
normally think about when we say diff, what does it take to get a diff, what is the implication
of the diff, is it applicable, et cetera, so we'll go beyond a little bit beyond the diff.
And mostly I want to show you how you can take schema diff back home, back to work, to
the office, and apply very interesting things with it, either directly programmatically
or through command line and tooling.
All right, my name is Lomium VTEST maintainer, I've authored a bunch of open source tools
and specifically, I've been around the schema management zone for like 15 years now in different
capacities and different aspects.
And with planet scale, planet scale offers a massive scale database as a service and one
of the features or offering of client scale is the idea of branching.
You have production branch, you can create your local branch where you are isolated from
production, you can do whatever you want, change your schema, whatever, and at the end,
issue a deploy request, which is like a getup deploy request, right, then this is where we
look at the production schema, we look at your schema and we say, okay, this is the day
for what does it take to move from here to there.
And we've done many, many, many, many tens of thousands of schema deployments, our customers
are doing that many deployments in production.
So this is like the main driver and validator for schema-diff.
But client scale is built on top of a test and schema-diff is a VTEST library.
So planet scale is just a user of this library.
VTEST is a CNCF graduate project, it's hard to describe in one sentence, but massive
scale, sharding, clustering solution on top of my skill.
The library schema-diff, VTEST uses it internally also for two different places.
But we won't talk about schema-diff just in general.
We do develop schema-diff, even though it's within VTEST, you can use it.
And we make sure to have the minimal set of dependencies in schema-diff.
If you all import it, you don't need to really import the entire gigantic code mix for
VTEST, you'll get a more minimal dependent set rate.
All right, so the most important thing to say about schema-diff is that it was designed
to be fully in memory, fully go-lang, no Excel independence, specifically, no Maya skill.
So we should be able to somehow-diff two tables, two schemas, by text, by maybe
important from that of this.
But by the time schema-diff sees this, it doesn't know anything about my skill.
It just knows his text or his set of statements.
This affects everything that we're going to talk about, because we lose a lot of information,
right, the traditional ways to go to information schema if you want to analyze something,
what are the keys, what are the columns you go to, this, this table, you go to the tables or
columns, table, information schema, and you've got gather data here, we're going to have to
need a different approach, and that different approach is going to be very powerful.
As we'll see later, we can get superpowers with that.
All right, a will show a lot of code.
Mostly you don't need to parse it, you don't need to really know it, no need to worry about.
I just want it to be available for you on this slide when you get back home.
schema-diff does work on my skill 8 and 8.4.
We don't support other flavors and not looking to support them.
All right, so given text or two texts, right, we have two creatables or schemas, we want to
be able to different.
It first begins with me being able to understand the text.
The first step for that is the parser.
We need to be able to parse the text, and thankfully, we test no surprise.
Understand, not only the my skill protocol is mentioned before, but also the my skill syntax
and can parse any my skill compliant, almost any my skill compliant text.
So the ESCLE parser library is one of the most fundamental libraries in the test.
And it's a classic AST and abstract syntax repository parser.
So given some text, I expect a skill parser to return a formal representation
of whatever query is expressed in that text.
So for example, if my query was a createable, then I expect the parser to return an object
that is the structure of createable.
And it kind of represents, yes, temporary, not temporary, yes, if it exists, not exists.
Right, it's a more formal representation of this syntax.
Specifically, inside here, we have table spec.
Table spec is yet another construct where you can see all the columns or the indexes or the constraints,
table options, et cetera.
If you look at column definition, it has a name, it has a type.
The type is yet another structure where you actually get the length, the character set,
whatever, all the properties of the columns.
So as you can see, this is the abstract syntax.
Right, you begin with some root, which is like the createable in this example,
and then goes down and down and down and drills into the particulars.
That's one part of ASCLE parser.
The second part is exporting, right, so I can take any statement or any such construct.
And or any sub-construct and export it into a string.
So far so good, great, parsing is not nearly enough to be able to really understand the schema.
The next part is normalization.
If you take a look at these two tables, they look different, but these two tables are identical.
Primary key can be defined in different ways.
The user gives the text, right, I can't control what the user gives,
but they can define it like this or this.
Upper key is lower key, it doesn't matter, 11, it doesn't matter.
If the column defaults now, it's the same if you don't define it at all, right.
In my spell 8.0 and above, this is the default character set.
So if that's the default character set, I can either define it or not define it.
So it's all equal and I need a way to decide what it is that I'm comparing.
So the second part in schema-diff is normalization and schema-diff chooses.
It uses reads the query, normalize the stuff, strips down stuff that
like I mentioned before, this example and export it back or can export it back with the parser
into the most minimalistic representation it can.
It's still not nearly enough because next comes validation.
The parser can give me syntax errors, but take a look at this table.
It is perfectly valid syntax-wise, but it's completely meaningless.
It's just not a realistic table.
Two different primary key is key over in non-existent column.
A foreign key constraint that has a different number of columns referenced and referencing.
Right, this is not a valid table. So schema-diff needs to go an extra mile and now validate
the design of the table or of the schema. Essentially doing what my skill does, but in memory
programmatically in goal and in the test, which it does. So we validate existence of columns
and foreign keys and all that it takes, but not only in the table scope, but also in the entire
schema scope. You may be aware that these sequence of steps is valid in my skill.
You can create a table on which view depends on it.
And you will only find out that the schema is broken when you try to select from the view
or export an input it. In a similar way, although it takes more effort, you can break foreign
keys dependencies in my skill. So schema-diff is also about validating cross-stable or cross-view
dependencies, checking that foreign keys do exist. There isn't index cover in the columns in the
reference table and the column type matches that between tables, etc. So it's really a lot of heavyweight
work to re-apply the same checks that my skill would do for you. Of course, if you go the
information schema way, it's easier. You do show createable. If my skill was happy to allow the
table to exist, you don't need to do all these things. So it's easier and this is more difficult
and still it pays off. All right, so now that we are able to parse normalize and validate,
I'm now finally able to get a text and from that text to get a create statement or a sequence
of create statements which I can iterate and export or do whatever I like. Which finally brings us
to the div, except wait, there's a slide before. If you're not doing go-land and you're asking
yourself, I can't use this because I'm doing Python, Java, whatever. We do have a theme wrapper
open source Apache to command line tool called schema-diff which gives some bare essentials of the
schema-diff library. You can run it from the command line. For example, this is how I load a file
schema file. You can load from database, etc. And I'll show a couple more examples later on.
All right, so now we're good to discuss the div. I've showed you earlier the table spec,
right where we have the list of columns indexes and constraints, etc. You can kind of be
in to understand how this works programmatically. If I have two of these and one table has a list of
six columns and the other has a list of seven columns. Well, let's probably an add column embedded
in this div. Of course, it's more nuanced. You need to check the codes which one is being
added. You need to check the names, etc. There's really a lot of nuances. The order of columns
really matters when you're differentiating between two tables. Because if the other is different,
we need to do, like, out the table, modify column after this or first, etc. But the order of
indexes doesn't matter. It has zero impact on your schema design. Similarly, for constraints,
the order doesn't matter. However, if you compare the names, then of course the names of columns
are super important in the same for indexes because you might be using four indexes or using the
name. Again, not for constraints. So it's really, really very nuanced. There's a
mirror of checks that we do when they think to just make sure we're doing the right thing
and not producing unnecessarily changes. So far, so good. Generally speaking, everything I'm going to say
today is nuanced. Has nuanced says, except for the things that don't. So feel free to
to heckle me later on and I'll explain where the nuances are like. All right. So we're obviously
not going to look into the entire code that does the diff, but we get the idea. And so it's
schema. If I'm able to compare, for example, two tables or entire schemas and get a diff or a list of
these that I can then range over iterate and print out to standard output, this would, this will
be the diff. So far, so good. Cool. Same with the command line tool. You can do that. Same thing,
just wrapped up nicely here. All right. The next thing is that the diff itself is a formal entity
just like the parsed queries of formal entity. So is the diff. The diff that I get back says,
what's the entity, what's the table name of the unit, whatever it is? What does the from look like
and what is the tool look like? What is the SQL parser statement that this diff describes looks like?
What does the diff look like as a text? So there's a gazillion building loss to the diff itself.
So before we move on, so now that we've reached the diff, I want to talk a lot beyond the diff.
But before that, I want to give you a few cool things, really interesting things you can do and we do
do with schema diff. So to begin with, I wanted to listen to the SQL parser walk function.
Walk is a visital design pattern function. It's a function that visits every element in your
three and for every element, it allows you to do something.
And this is a powerful tool for lint in your schema, lint in your changes,
checking for stuff that you disapprove of, right, manipulating, cloning. I give you an example.
I'm different to schemas. There's a list of changes. This could be
all-to-table, create view, drop you whatever. I'm going one by one, I'm walking the changes.
For every change I'm checking is this change a drop table or is there a drop table within the
change, is this change a drop column or it contains a drop column? If yes,
this is a bit of a dangerous situation, this is a risky, right, this is a diff that has
danger inside. We actually do that in planet scale, right, when a user drops a table, drops a column.
It's there, right, they're allowed to drop table drop a column and do the flow request.
But we put like big red letters, hey, that's fine, you're good. We don't want to alarm you,
but you might lose data as a result of this deployment, right. So this is a way of lint in the diff.
You can think of other ways of lint in schemas, like make sure this schema has a primary key,
or is there a foreign key? Do you allow foreign keys in your schemas? Yes, no. Do you allow
cascading foreign keys? You can really drill down to the details and it's easy to do that with
schemas schematically. Granted, these examples are also easily achievable, which is a simple
regular expression or a substrate search. That's fine, but there are more interesting. Just the other
days, Lefla had mentioned in the Belgian pre-forced in days. The idea to lint in index,
Jean-Pherson was also in the discussion, that the last part of the secondary index shouldn't be
a prefix of a column index by the primary key, right. It's not crazy things that
they require a lot more semantics to handle, and are easily achievable in schemas,
if I can write that in a few minutes plus testing, of course, but it's kind of easy to do here.
So far so good? All right. How about the diff itself? Why do we need the diff? So three,
very, really examples. One is of course, I'm deploying two production, right? I'm working at
GitHub. I have designed some changes to my tables. I want to ship that to production.
My code is in git. I want to somehow make production look like my git code. So we need to be
able to compare production with my git data and then apply those changes. Classic.
Some environments are not easily deployable, like your testing environment, staging environment,
et cetera. And so maybe you don't have the automated tooling, you just want to know that your
testing environment really looks like production right now, right? It's just for you.
You pull this game of production, you look into your testing deployment, you do the digs.
Or third option. A two-like orchestrator has a backend database. It deploys itself.
It's backend database. You don't need to deploy it on its behalf. You just run the next version of
orchestrator. Any deploys its own backend tables. The same actually happens with vites, vites not
not only allows users to access massively, show the databases and schemas in their own design.
It also rides the idea that it runs my skill to run some internal management tables in a
specialized schema, the site card and the school vites schema, where it presents stuff like workflows,
schema migrations, even hard bits. So as vites starts up, it reads its own schema definition
from code. It looks into whatever it is that it finds or does not find in the backend table.
And if there are necessary changes, it applies them upon startup before opening connections.
Makes sense? Cool. Let's go beyond getting the div. So the next weird step is that schema div,
the schema div lets you apply the changes in memory. So we have a schema. We parse the schema,
we validate it. We got a div from somewhere maybe compared to another schema. And then schema div
lets you apply that div onto your schema. Effectively running the schema change in memory.
Well, it is not an existing table because it is in memory. Why? Why would we need to do that?
So this is very powerful and it powers at least two very important things that we do one in vites,
one in in playing skill. I'll talk about just one of them, the one in
it's useful for everyone. Let's say put it like that. So getting the list of statements from a div
is not enough. If you compare two schemas in Telbeau, you need to create that table,
drop that table, modify that view. That is not enough. There is not enough information
in that output because order matters or a valid sequence matters. If you create table and create
that depends on that table. Of course, you need to first create the table and then create the view.
If you want to drop the two, if you want to do it right, you should first drop the table,
then drop the view. With foreign keys, there is a myriad of other operations that you
absolutely have to do in the right order or else, it will fail. Some sequences will fail.
So now we want to find the valid sequence. Not only do tables and views and foreign keys create dependencies in your schema,
the changes to those tables and views in the div, these changes themselves have dependency three,
have dependency three, so we need to figure out the tree. In a ev solution,
would we to try all the permutations of all the changes, right? If you were on your my skill
server, you would just try to run your query. Oh, that doesn't run. Let me run the second one.
Oh, okay, this one. All right, very the first one again. But with schema, if you can do something
in memory, because we know schema, if you're able to validate the schema, and it knows how to apply
a change. So let's imagine that I take a schema in schema, and apply this change. There's
this work. Yes, good. Does this work? Yes, good. Does this work? No, because maybe this depends on
another table, I don't know. We can revert back to the previous schema in memory and try the next one
or try the next one. Worst case scenario, actually this is really terrible. It's the permutation
of seven operations, the factorial of seven, that's a lot of operations. Schemeidif is smart
and then that not that name. It can identify which leafs could potentially be conflicting and which
do not. So it can reduce this factorial of seven into factorial of three times factorial of two
times one times one, so it's a lot smaller. So Schemeidif is able to try
different permutations and tell you, okay, of these changes, there is this specific order,
which with which you should apply the changes, or give you an error if there is no such order.
Okay, so far, cool. So we're talking about now ordering this, so you can get a list of order
this, programmatically or through the command name. I want to skip some other examples
and just now mention why we choose to do that in memory, because if a user has 1000 tables
and they change 10 of those tables, then using my Schemeidif information schema, I would need to scan
1000 tables times two, because I would need to first apply the new 1000 table schema and then compare
1000, read information schema for all these tables, only to find only really 10 tables are changed,
but then how do I figure the order? Because if I'm dependent on my skill, I would need to actually
apply articles and create those in drop tables. And if I had to do that permutations
on a live server, or even in Docker or whatever, it would just take time, it takes time.
We know it takes time, like multi-minute time, whereas with Schemeidif, we can handle these numbers
in sub-second and not a lot of memory, a few megabytes, a few tens of megabytes of memory in bed
conditions, so that's really useful to us. I only have like a few minutes?
Five, five, five. Perfect. So, again, skipping some features, but I want to further discuss
that even that order this is not enough. It still does not guarantee success of the migration.
We've ordered everything, the sequence looks correct, we're applying an audit table and it breaks.
Why is that? There could be multiple conditions. I could be reducing data scope, for example.
I could be changing the column from int to big int and sign. It's bigger on the positive side,
but if I have the value of minus one, this migration will fail, it should fail.
I hope it will fail. If I'm turning a nullable column to not now,
or that's fine, but if you want to come back, the fact that you removed uniconstrate means that coming
back, you re-ad uniconstrate, so that's something we want to know ahead of time.
More, even more, Schemeidif can predict not fully, there are stuff that are stateful,
can predict ahead of time whether a migration or a set of changes are eligible for instant
video. Today, you will just try it, you will write out the table, or I want to run it only
if it's instant, you will write out the table, algorithm equals instant, and if it's not instant
to vote, it will fail, but you will have to try it. How can you know it? Schemeidif will tell you,
this is eligible for online video.
Explain again, sorry. Correct. If you have minor versions,
yes, correct. So Schemeidif is a minor version aware, right? It knows what was added in what version.
Okay, I'm basically out of time, so there's more for this, but I want to leave a couple of
minutes for questions if I can. Thank you.