Michael: Hello and welcome to Postgres fm.
A week show about all things Postgre.
I'm Michael, founder of PG Mustard, and this is my cohost Cola founder of Postgres ai.
Hey, Nicola, what are we talking about?
Nikolay: Hi Michael.
Last time we discussed a versioning of database schema or.
Database Migration management.
I already forgot the proper name of it.
We can continue this field and discuss database branching today and how it is different from, like snap ting for example, or from , schema version
control, like, cause it's, like adjacent area which is not yet developed.
We don't have good tools yet, by the way.
So, we probably will discuss some ideas and concepts and what to expect from the future in terms of various doing and what other companies do,
and also what my company does because looks like we go in this direction.
We develop a database branching right now, so,
Michael: Yeah, exactly.
Depending on exactly how you define it.
It seems like there's quite a few database companies at the moment talking about branching, but each one means something slightly different, or as you
dig into it, I've seen your conversation on Twitter with a few people trying to understand what they mean by it and trying to get some definitions down.
So I'm looking forward to hearing your thoughts around that.
Is it, is it worth us?
Oh yeah.
So database lab engines worth discussing maybe in terms of what it does what, what you are calling branching and what some others are.
Is that worth doing first?
Nikolay: Yeah.
Well, I think discussion of data base engine as a whole, it's maybe a separate discussion because there are many things that
it can do and many different use cases where it is useful.
But briefly, I think it's a good idea and yeah.
Let, let me do some overview of data.
Branching topic.
So database, lab engine, which we post develop.
It was born when we needed to experiment very quickly to check, first of all actually, sequel optimization ideas,
not on production, but in some non-production environment, which behaves where pogs behaves identically to production.
And we needed to isolate.
Experiments of different people.
And we also needed to iterate.
So to reset quickly and to throw out bad ideas switch to new ideas quickly.
But when you build indexes during hours or you already change your schema heavily changing data, sometimes you need like
to spend many hours converting some column and then you.
Uh, Dead end.
You need to start from scratch.
It's quite difficult usually to have another environment provisioned quickly, so we solved this originally for optimization using
think cloning, think provisioning of based on Zetas, either Zetas or lvm, although other options also possible to implement.
Without any big details, like you can run single server with dozens of.
Independent logically independent post instances where database is the same everywhere, but it's writeable.
So, so you can deviate, you can create your own index, and, and the planner behaves is exactly as own production.
This is the trick and the creation of CL takes only a few seconds regardless of database size.
Sounds like magic, but this magic is going to kill another.
I call it like many people call it black magic posts.
DBA knowledge is and skills is like area of black magic.
You need like to spend 15 years and then you quickly say, This will work, this won't work.
And people say, Oh, you are like black magic guy.
Yes, black magic means like something is hidden.
Our magic is is wide magic.
No, nothing is hidden in any developer, any engineer.
See behavior not disturbing others and experiment and fail and so on.
So this is what we did.
Many clones are running on single machines.
So you pay for one machine and have dozens of clones.
And we switched them to area of testing in C I C D pipelines.
So like it's whole new world as as well, like, again, whole big topic how to.
What, can be tested in C I C D pipelines in terms of database, in terms of Postgres?
Our idea is obviously we have database finally, not tiny, small, like one gigabyte of something just generated, or, I don't know, brought to you by.
Docker pool, but we have whole database.
Like we can, can be handed gigabytes or terabytes.
It's, it does, it doesn't matter for us.
We can just set it up and, and make pipelines working, provisioned very quickly in, in few seconds.
So testing is another area.
Some people do various things.
For example, some people just test ppg upgrade inside our containers also possible.
And so The key here is that we already do it for a couple of years or maybe three years.
We always said this is like, think cloning.
We use the term cloning, Clon cl clone.
It's very natural for cloud engineers, for dbs, for like SRE because This is cloning term used in cloud.
Right.
You can clone your S volume also from snapshots, so copy right there as well.
It's also like thin provision, but thin provisioning, but it's kind of different because you pay for each volume separately.
But still cloning is used there or RDS clones.
Aurora has also think loans and so you can single storage, but multiple instances.
Using all of them, use this same storage, and you can have multiple write.
Instances.
So you can do rights independently similar, but again, you, you need to pay for each compute node separately.
That's why no, neither RDS clones nor of ro clones are good for testing in i c d pipelines because you, you want.
Constant price, constant cost when you need to pay.
Also, by the way, you need to wait many minutes to provision them both.
Michael: I guess thin cloning is specifically named as opposed to thick cloning as it, you could take a full copy of it.
And I, and that's what a lot of systems have offered for a long time.
And this, this is obviously a step above that in terms of perform, in terms of speed, but also in terms of not having to have that extra,
Nikolay: thick cloning.
Actually, we can devote a whole episode to it, and maybe we should, because it's also interesting, an interesting question,
how to clone a large database using regular tools, for example.
Do, do you.
Clone P data directory or use P based backup at physical level, right?
How to do it live without interruptions.
Of course, any experience post DBA knows the answer.
PPG startup backup or just use PPG based backup by default, it will be okay and you can do it live or you
can you use it at logical level using pizza dump store.
It has questions, how to speed it up and so on.
But roughly we can assume that a regular, both of them, by the way, we consider thick cloning, but we distinguish physical and logical levels.
So that restore is also cloning, but at logical level.
But you can choose which objects to clone there, right?
And you can speed it up using dash.
But in this case, you need additional space because you cannot use that j and do it on the.
The problem, which is solved by another tool, Demetri Fontain, is developing pg copy db.
I, maybe I'm wrong with name of the tool, but it's quite new and it exactly provides your ability at logical level to use multiple threads and avoid.
Intermediate file backup file so you can do it on the fly.
It's, that's interesting.
But it also raises question how long our transactions are on the source.
Many, many, many things in the area of thick glowing.
Why?
I know it very well.
Because to provision database, lab engine, we need first to get data in a regular way, either logical or physical.
So right
Michael: like one thick clone that you can base the thin clones off, but that's where I
Nikolay: And we need to main, we need to also to maintain it either continuously or to do the full refresh on schedule.
Everything
Michael: nightly or something.
Yeah.
So, but that's where this becomes really useful, I think, for the branching discussion because suddenly if we can do thing clones
or something like them, we get the concept of maybe you can have branches that aren't just empty, that aren't just just the schema.
They can have real data behind them as well.
Nikolay: So what happened with branching, the branching term?
First of all, I, I didn't realize it in the past, but now I see it very well.
Cloning is very infrastructure, language.
It's not friendly To developers because in there is lon, but it's kind of different.
You clon whole repository.
There you have revisions.
Or commit number.
Commit numbers and branches.
And clones.
It's a language of SRE people or DBAs, DBAs infrastructure people, of course all any engineer knows, like clone in various aspects, but
still they prefer branching and suddenly some time ago planet scale, which originally provides charting my sequel vis, they are develop,
they develop, vis and founders are the same, who created vis, it looked like my per, from my perspective, suddenly to the tohar problem.
They added a schema management capabilities and they called it, Okay.
We have now database branching and we have zero downtime deployments for ischemic changes, hassle free.
So like, great.
And on the front page it was like, it was like maybe last year in 2021 and on the front page I saw that it was branching.
But when I clicked inside documentation, I was curious because it's our area.
I felt like, Okay, okay.
Do they play with think loans or what Think provisioning.
But inside you can see database schema branching already.
So slightly different term.
Right.
And you realize that they clon only sche.
, then you can change it.
They, then they produce diff uh, we some of topics of our previous episode.
And this diff you can see it, you can approve it, other people can approve it.
So there is some flow.
And then it's deployed in zero downtime fashion,
Michael: Yeah.
Nikolay: and nothing about data.
Michael: So that, that raises the question about test, like how do you test on the performance side of things, You
know, is this all and how do multiple people work together?
That kind of thing.
Nikolay: Right.
Well, first of all, this is also viable approach, I, I should admit, because this is hard problem, zero down time migrations.
By the way, last time we didn't mention my article I dunno, Like 18 mistakes of making schema changes MPOs before measuring
this article, I selected, there are many types of mistakes you can do, but I selected some and discussed in detail.
It's on, on our website ai.
So it's a good problem to solve.
Very, actually hard because most of diff tools we see, and I, maybe I'm wrong, but liquid base has diff tool
we mentioned and some, some, somebody in comments.
Thank you so much.
On YouTube raise this liquid base also has Diff and Jein has diff, There are separate projects like Mira, but all of them show.
Simple diff like create index without word, concurrently, not discussing the problem, how to change data, type
in, in 1 billion row table so they solve hard problem.
But there is hardest, hardest problem, how to generate diff.
In zero downtime fashion, as far as I understand, a planet base they show diff regular in regular form, but when they apply
changes, they perform something like similar to PPG Repak approach.
When you create full copy of table recording all changes.
In some delta table, like change lock, right?
And then in single transaction, or in multiple transactions, it's interesting also topic, but in steps you apply
all changes and then you already switch to new table.
Of course, this approach requires some disc space and it's kind of too heavy for small changes.
Sometimes, like it, it depends, But it's interesting that they have full automation.
But again, they don't care about data in this case, but their CEO in in Twitter discussion said they are working on data branching.
I'm very curious how they will solve the terminology problem.
You know, two biggest problems in computer science, Right?
Naming and cash.
Cash and validation.
Right?
So
Michael: and off by one errors.
Nikolay: Yeah.
Yeah.
, So obviously they have naming issue because they already use database branching for sche only.
Then this year earlier I saw that super base have database branching in their roadmap and also neon appeared.
And Neon said We are open source Aurora, right?
Michael: Yep.
Nikolay: Aurora has think loaning, which is, in my opinion, not good for testing.
It's too expensive and it's too.
It, they, it's like think cloning.
Yes, but you need to wait minutes and you need to pay for compute for each clone separately.
So it's, if you use big O notation in terms of money, it's a big O from number of clones for compute power.
Fortunately not for storage, but also of course theora also charges you for a.
And for testing, also not very pleasant, but I guess big enterprises are okay with this,
Michael: it's, boy, it's better than nothing as well, right?
It's better than not having it.
Nikolay: Of course you can.
Yeah, you can test some heavy changes in this way, but it, this is not something you will use for each pull
request or merge request of back end call changes.
It's too, too much.
But I also, by the way, I found that observed this area.
Heavy clones where we can use all CPUs and so on.
They are needed only infrequently for infrastructure teams.
For example, upgrades, big migrations to some new hardware or operation system.
But developers these days, they do changes many times per day, sometimes, right?
So like it's very often.
And there we need the very, very cheap and fast cloning.
So, okay, back to database branching.
So a new one in the very beginning said we are going also to be very good database for C I C D pipelines and we have database branching, not
discussing what it means actually, like, in detail, how, how it is, how is it different from cloning, for example, or snapshoting or like these
infrastructure languages or terms and Someone else also said, like some other projects also said like, we have gi like approach for databases, for pogs.
And then like I spent some time trying to realize how branching could, behave for, for database for POGS to solve problems of development and testing.
And finally I realized that branches are very different from our.
Because clones they take some memory, they consume memory.
For example, I, I want, in GI I can have thousands of branches and nobody, like I don't pay for it extra.
Okay?
Some, some small storage penalty that.
Right.
But if, if all branches are identical I don't pay at all.
No, Nothing.
Nothing.
Right.
But when you run a think loan and database engine, it consumes some memory because it has shed buffered.
So it's like it has something pogs running.
So it, it has some cost.
We have some limit, of course defined by size of your memory on, on the server and and she buffers, for example, right?
So we can adjust and run more clones, but still we have some limit for branches.
We don't want to have limit, right?
This is one thing, Of course, name is also a thing, but also a thing like in gi.
It's very good.
We discussed it like decentralized and one of properties of gi.
It allows you several stages of review.
You can review yourself before you push your commits.
You can ask your colleagues to review if you have full request me request and GitHub GitLab.
So to see difference between branches bef before you merge your development branch to your main branch.
But if we say our CL.
Branches.
We cannot do it because we, we want to do this.
We want to say this is our state, and then say multiple colleagues or multiple CI pipelines tested, check it and continue working with it.
For example, so we obviously we realized we need it, It was in our roadmap for a while, but we realized branching
looks like snapshotting on demand for your clones.
So you have your clone, you change something.
You put snapshot via api, cli, or ui, We have all of them.
And then you say, Okay, this is, this is Snapshot, this is commit, or this is it.
Continue working with it.
Right?
So Snapshot started to look like a branching, right.
And like kind of a name snapshot.
And then I, you know what I did?
I just opened a.
Documentation and started to read it from scratch.
And they say they have, by the way, slightly conflicting definitions of branching.
There is no good definition, like in the beginning, you need to go deeper, but I found good definition Branch is a pointer to commit a named winner to commit
well, There are issues with this term, but it's kind of also works for us.
We say, Okay, we have a named pointer to commit.
It can shift.
If new commit is created, it shifts automatically.
This, this kind of branch and that's it.
And we already developed prototype at I think it'll be database lab engine 4.0 when it, it'll be released.
We are not in hurry, so we want everything work very smoothly and tested by many teams properly.
But it's already, we have prototype, it's working.
At CLI level so far, not ui.
So you can maybe if you listen like one month earlier, later, we already have we should have UI as well and so on.
But you say like, I want Branch, so you, you started to deviate, you run, clone for this branch.
Others can run their clones.
, Clone is like your working directory.
You just grabbed the content of your code base and opened some ID or editors and started to change it.
So clones is a, like, it's a mean means to change the state.
Michael: Well, it's like a running application, isn't it?
Like the source code isn't like a running application.
Nikolay: Right.
But usually running application doesn't mean like changing of schema, but I, I usually avoid it.
Changing of schema should be during deployment, not.
During normal work.
Some people do it actually some evolution of schema initiated by your users.
Also temporary tables is a part of it, but I consider it as like very questionable practice leading to
issues with management from DBA point of view.
So I would say, Normal run, run of running applications should not change your sche.
You should try to avoid it,
but
developer openside it or changes it and then commits.
Get, get, get push.
Right?
Michael: What I meant more is I really like the snapshot analogy, and I think.
The code at a specific commit is kind of like a snapshot of the application, but it's not the application running and
the, and in the same way a clone is a running database, right?
That you, that you can create from a snapshot maybe, or from, I don't know quite how you're defining these
things, but yeah, we don't need them running all the time.
Just like a developer doesn't need an application, like they're a version of application running all the time.
Just, just while we are debugging something, just while we are actually trying to, to test it.
So yeah.
Makes a load of sense.
In theory,
Nikolay: Yeah.
I wanted to to emphasize also that we consider these snapshots as a whole with data.
It can, can be production data if you can afford it.
There are no, if there are no issues with pi i g d and others, but we focus on schema changes.
So data, We can, we also snapshot it and we provide it to CLS or branches, snapshots and so on.
But what is most meaningful is schema changes, right?
Because these should be deployed sometimes.
Of course data also should be deployed, but we want like gi like approach.
With data, but applied to schema only.
We don't want to have data comparison and when deploy this, maybe we will want it as, as well, because we have it here, right?
We can do something here as well.
But so far I'm like, I'm looking at the problems we have.
We just want to mirror the capabilities of GE and bring.
Branches, database branches to build very like effortless effortlessly build non-production environments matching your code.
So you have development, branch code, and you have development branching database.
So you can quickly take this code somewhere on your laptop or on your some Like, I dunno, non-production machine in cloud.
And then you can request a clone for this branch, latest snapshot.
And this branch will be used and we have posts running and you can, they go together and you can start testing, developing
your application and see how it works with a lot of data.
Similar to production, this is what we do.
But if you do some changes, data changes, we think they are not such, so relevant because some tester can do many weird things
with data there, , then we just need to, to throw it away.
So when we meet, we meet fully, but we look mostly on s.
And so far we relied that we use one of these tools we criticized last, last week Soke, liquid base, Flyway rail migrations.
We, we see that people already use them, so we are not going to, so there is something to help there.
We discussed problems.
They have new generation I'm sure will be born.
The nearest future, I think.
But what's really not solved is how to test it properly with a lot of data.
Here we have this branching.
So I just described some maybe not very well.
I described it because still it's kind of not very precise.
This concept.
It's already clear, but not super clear.
But what I'm trying to do here is try to define what the best branching.
And this is how we see it.
We already developing in this direction.
I'm curious what other companies think actually.
So, but I think it would be good to, to synchronize thoughts and to move in similar directions because in this case everyone wins
and developers Have similar concepts in various products, right?
Actually in source control management systems snapshots, clones and so on, like branches, they also.
Have different meanings, if you compare detail.
They have differences.
So pro probably here it'll happen as well.
Database branching can have different meanings in different tools.
obviously, Right.
Sorry, I'm talking too much.
yeah.
Michael: this is great, and I think you're working on a document, right?
Are you gonna share that on Twitter when you're ready or, Yeah.
Nikolay: Yeah, I have some draft for rfc in this area.
Discussing goals and anti antigo because for example, we want, to deal with data, deep data and so on.
We focused on schema changes mostly because data production has probably different data.
For example, we don't want to release data patches.
And also there is.
There is a problem.
Interesting problem.
You created a column.
I'm very sorry.
I feel very sorry all the time because I, I'm, we have delay probably, and you trying to interrupt me, but I'm already switched.
You wanted to ask something, Sorry.
Michael: I was just gonna add on the data front.
I think I added it last week, but some, we found when I was doing this before, we found sometimes data is schema, like the lookup tables.
So you might need to worry about that, but yeah, exactly.
So some, you might have to worry about that at some point, but it wasn't like we, I think we added it in version two or something.
So it, definitely can get away with not having it in there or at.
It does feel like it's, It is there.
It is important.
Nikolay: Right.
I spent some time trying to also to think about how to merge Merch is, is basically, it's deploy, if you consider main is what should
be done and should be present on our production environments.
So merch is some you need to have diff and then you need to go.
If his D to production, roll it out.
But, but, but Uhhuh.
Michael: A diff is slightly different.
Isn't like diff is like compare these two things, but then I need a script, like a, in order to actually make one of them the same as the other.
And that's not the same as a diff, but yeah, I agree.
So first step is diff, and then Second Step is a
Nikolay: Well diff can be seen as a series of DDL comments.
In this case, it's, it's same, but the problem is that you see it these dd well diff tools for post schema, they show alter comment.
created these comments.
Michael: Yeah, most of the I, But I consider that a second feature, right?
The first feature is compare these two schemers and they, they'll like highlight differences and
Nikolay: Well, maybe.
Yeah.
So we have two approaches for diff I agree.
But the problem with deployment will be if you go, if you're creating this comment to, to production, you will block people.
And so you need to, have advanced diff and we spend some time prototyping this as well, and.
Then I realized it's already kind of solved, like it's not solved well.
But we have a zoo of various tools for deployment management, like ski rub rails, migrations, and they all
ignore the fact that data should be changed in batches.
Again, I'm advertising GitLab migration help.
So which solves this very well for Ruby, and like, it's hard for us to like, either we need to choose something or we need to somehow abstract obstruction.
Like it's, and then I realized, okay, people already solve this somehow.
Let's just avoid this problem.
And we, I consider this, currently consider this as antigo and we just take care of conflicts.
So if someone already changed schema in this branch, you trying to.
So in other words, we have something like CVS or Subversion, very centralized.
So before you put your changes, you need to update and then you can, you can already push the changes.
So already resolving conflicts and maybe replaying your changes on top of other person changes.
So we just take care of conflicts in quite simple.
And we don't solve the problem of merge fully, maybe postponing it, but we, what we have among goals.
For example, imagine you created a column, which is empty.
Everything is filled.
You have full database, but one column, it's new and it's empty.
And how to test it, You need something there to test it.
So it looks like we need to think about ability to provide some i d.
what I don't like, like fixtures and like seed databases where we have some fake data, but here we need it.
We need to feel like we need to feel new columns, new tables and developers should decide how to do it should provide some mean for testing.
So we have everything, but somehow we need to fill new call
Michael: Yeah, like data generation.
Nikolay: Right.
Maybe Snapshotted consider this, like, this is our test data.
We have deviation from our production, from MA main branch, but we already have test data.
Let's go.
Like, it's good.
Any other engineers can work with it and test it and play and, and explore how this feature behaves with many roles.
Right?
Michael: Yeah.
Feels like a whole nother topic.
Nikolay: Yeah.
Well, testing is a whole another topic.
Definit.
There are major areas we can discuss there as well.
So what excites me here is I, any, any direction I go, I feel, How come this is still not developed?
How can we leave if without it, like we, I see how we can leave.
We test on production every time I see, I have some question like, It's about like this, not very beautiful term, but it's, it's called shift left testing.
When we want developers test first, then some like testing should be done in the very beginning.
It should be shifted to very left in this infinite develops develops sign, you know, like this
Michael: Yeah, I know what you mean, but like even if it's not done in production, it's often done in like a stage, like
Nikolay: Right.
But staging often is very different from production.
So we end up testing it on production,
Michael: I can see what you mean.
Nikolay: really testing, really testing.
We, we pretend sometimes with mark check boxes, it was tested in lower environments and staging everywhere.
But if you think about, was this testing real?
It was, it was fake testing.
And this is what we want to fix in our development processes.
Michael: Yeah.
Yeah, that'd be great.
Any last thoughts or things you wanted to share with people?
Nikolay: Well just keep an eye on on what we are doing.
Stay tuned and Any feedback, any ideas are welcome.
I'm always ready to discuss this topic with everyone.
I'm like, it's very, very, I think it's one of the hottest topics in the area of databases right now.
I mean, many problems are being solved, Kubernetes and so on, but this problem needs to be solved as well, and like, Majority of
development teams will benefit immediately from, from better new generation tooling to, to build new non-production environments.
So I think we spend too much time thinking on production, but to solve problems on, on production, we needed to start from non non-production.
And this is, this is interesting.
So ready to talk with everyone.
Just teach me out on Twitter, email anywhere and our regular mantra.
Thank you for, for feedback everyone, for subscriptions, likes topic ideas.
I think we will, again, next time, we should choose one of the topics proposed by, by our audience.
Very
appreciate ideas, and that's it.
Please, ah, share in your social networks, working groups.
Slack, discord, Master Don, right
everywhere.
Michael: Yeah, of course.
Absolutely.
Well, thank you Nicola.
Thanks everybody.
Take care.
Nikolay: Thank you, Michael.
Bye bye.
Michael: Bye.