It's about one o'clock, which means the next presentation will start here in the Post-Kaz
Dev Room.
So, let's give a hand to Gultin Genetly Neck, who is about to talk about Post-KazQL locks,
table-level locks.
Okay, how is it now?
Yeah.
Good.
So, today's topic is it says in the presentation, we will talk about table-level locks,
main idea locks.
So, let's start.
A short, like who am I?
Page.
I work in a startup called Zeta.
They do Post-Kaz service and we have our own Post-Kaz tools.
And one of them today, I will mention.
I am also recently recognized a Post-Kaz project as Post-Kaz contributor.
And I have been using Post-Kaz for a long time already and happy to be part of the community.
Thank you for coming.
I am visiting our bedroom.
I also do, like, I run the user group for the product because I live in product for almost eight years now.
So, if you are around and if you are close by to product, you can just visit every month.
We meet and talk Post-Kaz with two talks a month.
And there are other things I do as well.
I have, I am Turkish.
So, I have communities in Turkey that I run as well.
We have this community in Turkey that I have been part of running for ten years now.
And now we are also organizing our own conference.
If you happen to be Istanbul in summer, feel free to join.
And recently also I became a part of the diversity community.
And we are trying to make Post-Kaz welcoming for everybody.
And yeah, that's enough of me.
And I like to come to Post-Kaz a lot.
It is somehow, you know, I don't know how you feel.
But over the years you see that your friends develop your projects.
They develop and you make new friends.
So, yeah, things change, but something stays same.
And that's what I think makes Post-Kaz.
We know that it will be every year in January where February will get together.
And then have this nice conference that we can just talk.
And so, I appreciate it.
I want to share, like, how much I love being here.
And today's talk will be mainly about, like, these are the talk points,
not exactly how the order of the talk will be,
but we'll be talking about MSDC design.
We will have mainly covered a video,
looks, and how the local works in Post-Kaz.
And then how we can arrange the impact of the looks
that happens during the production environments.
And then we will introduce project open source project
that I'm related with SP0.
Okay, so, what is actually, like,
let's go, like, maybe generic and the concept of look.
When I was thinking of writing something about looks,
I was like, I always tend to, like, go to the words
etymology, like, where does the look come from and so on.
So, I had ordered, like, Persian looks,
and history of locking and lock picking and so on.
And then eventually, I noticed, like,
there are some parallels that I can draw between these things
now that I had this books.
To be able to pick a look, you have to understand, like,
how it's inner workings, inner mechanisms work, like,
how the pins interact, what is the, like, tumbler, the wing.
And if you know a position, like, if you know the correct position
of these things, when you enter, when the interactions
of these mechanisms, you can actually open a look,
any type of look without a key, right?
It's, like, the kind of how look picking works.
And the same actually applies for databases as well,
to be able to understand this locking mechanism in databases
in general, in postgres.
You need to understand some of the inner mechanisms
that are intervent here.
And mainly in postgres, this is about,
uh, concurrency, because in the real life in the physical world,
the locking is usually for about security,
securing a save or securing a door and so on.
But in postgres, it's not something we do for securing it,
and it's not in the later security,
but how we will have, how we handle high concurrency,
so that I want to, like, explain a little bit.
And it is not something only postgres uses, obviously,
and it is used everywhere.
And, but how do we, how postgres actually handles it a little bit?
So that, that's why it brings us to topic of MECC,
it's like, I believe everybody knows about it.
Is there anybody who doesn't know what the MECC is in the room?
Yeah.
Okay, but if it refers to for people who actually maybe heard this first time,
it means multi-version concurrency control is so mouthful.
But what does it mean?
When something writes,
when we are writing and updating the data, some data,
it doesn't really change directly in place.
So instead, we do copies of the version,
we multiply the versions of the rows basically,
and then we keep those versions.
And what does it do?
While, let's say,
a query seeing a snapshot of what we have,
and the other queries can get the other versions of the same thing,
without actually blocking each other.
That's the whole concept in a way that we can actually,
when we are writing, we are not blocking the reads,
that's happening at the same time,
and the reads are not blocking the writes.
So this is why it's called like multi-version,
because we have the multi-version of the same row.
And this is very good,
because most of the,
we avoid most of the looking,
thanks to this architecture that we have,
we are using in post-fresh.
And if we didn't have this,
we would probably have to,
we couldn't achieve this level,
we could have probably have to use logs more.
But for example,
for the most of the DML operations,
we are covered,
because it allows like the updates,
delete, merge,
step in,
by using,
utilizing this.
But it is like,
there are some logs that we can't,
we can't avoid some of the looking,
because even though we are able to,
thanks to this multi-version view that we do,
we can actually avoid some of the looking,
but not everything.
So reads still have to look some of the,
we still have to look some of the objects.
So that's why it's important that this bit is,
quite crucial for the,
for how we do the concurrency,
how we do the isolation,
but we can't really avoid not,
not having any looking.
Okay, so,
when I just mentioned like,
in theory,
in Mississippi,
it might not really be clear perhaps.
So that's why I put an example here.
I hope it is readable everywhere in the back.
So what happens?
When a transaction updates data,
as we said, Postgres creates a new version.
And this new version of the role,
but it still has the old version of the role as well.
So we have two versions.
And there are some system,
system columns that it is actually happening at the meantime.
It's not visible to the users,
but if you query you will see it.
So I think two of them,
if you knew two of them that will be useful,
in this example for sure,
X-Men and X-Max columns.
So in the X-Men,
it is the which transaction ID that creates this version.
That's where we market this,
where we put X-Men information.
And the X-Max is like,
this transaction ID that either deletes this version,
or actually updated it,
or if it's empty,
if it's now,
it is the current one that we will see,
which we will check in the example soon.
And here,
this example is based on the default behavior of the isolation level in Postgres.
It is read, read,
so we have multiple level of transaction isolation,
which you can see the details,
but this is the one that is default behavior to Postgres,
and I will explain this example based on the read committed isolation level.
So what happens when,
when Postgres creates this,
every time a statement starts,
we create a snapshot.
So what has this snapshot in high level?
It keeps the older transaction ID,
is the tar running gate to moment.
It has the latest committed transaction ID,
and it has the list of in-progress transactions as well.
And now we can check the example that will be much easier.
So in the first,
there are two transactions,
you see like there is transaction ID,
100 and transaction ID,
120.
When we see the select,
there is like employee of Alice,
and it's salary is 50,000.
And then it starts,
it gets the snapshot,
that is the view that it has.
It has this information,
and it sees salary at 50,000.
And you see there,
the rows X max is the null.
And it means that this is where we are,
that's what we see.
And the transaction ID,
99 is already committed.
That's the transaction before the transaction,
that's how the view is,
that's how we see it.
And then the second transaction,
the 101,
is updates to salary to 60,000 in the another transaction.
And then that,
that this one more version of the row.
And now we see that the all version is marked with the 100,000,
because this is the transaction,
that the second transaction updating it,
because it's like,
it kind of makes it,
it says,
okay, I'm updating this now,
and I created another transaction,
another row with the X mean,
being the same transaction ID.
So it marks it as a 100,000 there.
And then why will we,
this is happening,
unless the transaction ID,
second transaction,
100,000,
when I have another select coming with the transaction 100,
I will still see the 50,000 salary,
because nothing is committed.
But by the time that the update is actually committed,
the second transaction,
since each statement gets an upstairs snapshot,
this time,
the third select on the left column,
you will see the current salary as 60,000.
At that point,
we will be actually being informed,
this transaction is committed,
and then we have the new snapshot with this information,
it's not anymore visible,
like when you start a new snapshot with this statement,
you will not be able to see the old version anymore.
So this is how,
thanks to this,
we are actually managing,
let's say,
there's a long-running transaction in Postgres,
and we are still doing some operations,
it can still has its own version of,
its own snapshot,
its own view of world basically.
And until it is done,
we still have this rows like being kept.
By the time,
when the time that this is actually,
not anymore in use,
that's why it's important to have,
like,
you can easily think of that,
these rows are getting collected.
So we have to think about the vacuum operations,
we have to tune our system for that,
you know, the knowledge of this architecture,
we should be aware of,
like, you know,
I have to think about the regular vacuuming,
I need to tune things,
I need to monitor my long-running queries,
because the more the query is long-running,
the more we have to keep this version of raw,
that we can get rid of basically.
So there are, like,
somethings like,
of course,
that's implied with this architecture,
but thanks to it,
we don't need to look all the time.
So there are, like,
we can easily cover most of the ML operations,
thanks to this architecture,
but we can't avoid everything.
So we still have to have logs.
And this is what,
topic of today will be,
we'll just cover,
like,
video logs,
and we can't avoid most of them.
Okay.
And so also,
like,
I want you to think a little bit like,
okay,
this is how it works,
but why do we look?
Because, like,
it doesn't seem like nature,
like,
because it brings you,
what does it bring?
It reduces our throughput,
you know,
that it will increase latency,
probably,
it means a lot of performance,
and like,
why do we have to look at it?
But then,
if my intention is,
like,
I want to make data correct,
and,
and every time somebody queries the database,
I want them to get correct results.
So if I have to deliver correct results,
without getting things messed up,
you have to have an agreement sort of that's,
like,
okay,
I have to give up something,
and that's why,
actually,
how these transaction isolations
and concurrency model works,
because I could just show something randomly,
I could just deliver wrong results,
but we tried to avoid it.
So that's the whole point of why we actually look,
because we try to keep things in order and incorrect,
and it will take time,
it doesn't come free.
But,
imagine a scenario,
like,
two edges of something,
like,
the scenario that we never looked,
the same,
that is lifted off,
we are in this,
like,
on our keyword,
there's nothing looked,
about what happened.
In one example,
maybe easy that I had prepared for this,
like a financial report is running,
like the year,
and salary upgrades,
and the HR is calculating the averages,
and maybe somebody is creating an audit column.
Normally,
if you didn't visit the set of log,
post-press,
when you are trying to add a column,
it will have to,
it will have access exclusive log.
But imagine in this scenario,
we don't have something like this,
so we are actually,
we are definitely creating,
like, corruption issues,
our schema changes,
and then we can't actually do the query.
So it will be messy.
And that's why we are trying to avoid it,
why having these logings in where we have to do,
when we have to change the data definition,
basically.
And the another scenario that we are looking,
let's say everything,
like every transaction is waiting for each other.
When there's a query,
we are looking to a whole table.
This could also work.
If there is, let's say,
one user using one table or like few users using,
that they never use the same resources,
same rows and same tables at the same time.
That could also work,
but in real life,
it doesn't work like that.
So we have to have at least a few users,
at least that,
and they are always sharing the same resources.
So that's why we actually need to log stuff,
because in real life, this rarely happens.
That's why I think when you think about the two edges,
you understand,
okay, this is reasonable,
that we have to, for the correctness.
We have to have this isolation levels,
and we have to actually add you to some standard.
Okay.
And there are like different type of log-mosts in postgres.
These are not,
we don't only have table-level rocks.
We have also roll-level logs.
We have advisor logs that you can actually use.
Your self or your kind of application.
There are like other logs that like weight logs that are in the,
you don't usually see the material.
So there are lots of type of logs that we can see.
But today,
the most that we will talk about is like table-level logs
that are video logs that I will cover.
So, why I think that is important to understand,
because schemas that,
your database schemas rarely stay static,
they evolve.
So you might add a column,
you may change the, I don't know, type of the column.
You can add a constraint,
drop a partition.
I don't know.
So there are things that you can't,
you have to do these things.
And while you do these operations,
you have to have some sort of knowledge,
like what kind of impact this operation
will have in my system.
And that's where with the idea,
you probably most of the author-table comments,
and their different levels of supplements.
We'll have the exclusive logs,
access, exclusive logs.
And that's why it will create
for potential issues with your application,
timeouts, and even downtime.
So that's why it's important to understand
how this thing's happened,
and what kind of looks better acquired,
and how you can actually minimize
this issues that might be related to the logs.
Yeah, I think I covered everything,
but for example,
when you run a DDL,
you assume that maybe that might
block other DDLs,
but it will also block the manipulation,
as well, like updates and even select.
So that is very important,
because I think that is not naturally
what comes to mind when you are running
an author-table,
and I already should be looking
some select for example.
So we have to be aware that running this DDL
commands will actually impact all sorts of queries,
and we will probably block all the access to your table.
That's why we will have to understand,
like, okay, running one author-table
with different supplements will impact
the system differently than with another one.
That's what we will go through deeper later.
And I divided the presentation with
some kind of checkpoints.
So if you don't take anything from the presentation,
just take away this points that I will show,
and there are, I think,
six or seven of them.
I hope this will be useful.
So for up until this is now,
up until this point,
I think this is the important takeaway that we have.
So we talk about MECC,
this is the approach that Postgres makes it
highly efficient for the concurrent DM operations,
because we are not modifying data in place.
We are just writing in copies of data.
So this will allow us to,
as we said, write, not blocking reads,
and read, not blocking writes.
But this, even in this system,
some level of looking,
we will not be able to avoid.
That's why I think it's important to know
different versions of different DDL commands.
We'll have different levels of looks.
So we have to be aware of this
where I level of strengths of the looks,
that will be the rest of the presentation about.
Okay, so I keep saying like,
looks and what are those.
The list that I'm showing is like,
the category in the table level,
looks in Postgres.
I marked the first three is a bit more lighter,
and the other ones are a bit more darker.
So it starts from the more restrictive,
less restrictive one to the more restrictive
when you go from top to bottom.
Because, as you see, for example,
in the access share,
this is when selects are actually acquiring the slope,
and they are not CDL, right?
And then roll share,
these are like select for share,
update also requiring this,
and then the roll of exclusive is required
by the DMN operations,
so these are not only required by the DDL looks,
that's why I kind of like,
logically separate the middle of the slope
from the maintenance operations.
I make the mark as like more restrictive,
there are more scale and stronger looks.
So you can see all this,
like, which statement gets what type of look
from the Postgres documentation,
and there's I think some website even check the Postgres code
and like shows it like when you type which command
it will take what kind of look.
So this bit is,
I think, is kind of important to understand,
but it's not as important,
I think, to understand how these different modes of looks
interact with each other.
Like when I'm trying to acquire some look
and there's already an existing look,
how this will acquire,
how they will be behaving against each other.
So they are connection,
because not every look,
every mode will block the other type of mode,
some of them will allow each other to work in parallel in multiple,
at the same time,
but some of them will block basically
if you have the some type of look,
it will be blocking the others that are trying to acquire the same look.
So it's important to understand this relationship,
and Postgres is a matrix that shows it clearly.
So I recommend you to check the documentation
about exclusive looking page,
I think that you can see this matrix
how they are interacting.
Okay, so that will bring us to,
sorry,
to what kind of things I should understand from this list that I show.
What is the very important of course,
access to exclusive will not only block the other,
like DDLs and other rights,
but like it will also block the reads.
That means that that table that we are trying to access
will be blocked for everything.
So that's really important when you are considering how to avoid this,
that's why we are trying to minimize the impact of it.
And some of the key things that understand it,
Postgres, if it is possible,
it will try to always acquire the less restrictive look.
There are optimizations that are already included,
so it will not do something that if it can avoid.
So that's good to know,
and it's not like it's trying to make it the most out of this looking.
And yeah, at the end, I mentioned something like,
a transaction when a transaction takes a look,
it will keep it until the end of the transaction.
And this will bring me to actually the most critical lesson
from this to take away is that,
imagine a transaction that you started and it has multiple statements,
and it took, let's say, access exclusive.
But you are doing,
while you do this DDL change,
you're also doing some, I don't know,
update some DML that you are running.
Normally, if you didn't put these things in the same transaction,
this state must in the same transaction,
you would be actually taking access exclusive,
maybe with the first operation,
and then in the second transaction,
you will have the DML operation that you wouldn't need to,
have access exclusive,
maybe it will have the raw exclusive look.
By putting them in the same transaction,
you are actually blocking the whole thing with access exclusive,
until all the state must in the transaction finishes.
There's something that I think not everybody realizes,
because I see that it is kind of a pattern
that all the things put together,
and the DLs and the MS people use them is mixed.
So I would recommend to not mix this different level,
acquiring statements.
So when you are running a transaction,
try to minimize this impact,
that if you otherwise,
the until all the things finishes,
the transaction will keep the look that it's already true.
So I recommend people to don't mix this comments.
And then it will be minimizing this looking
for operation that is needlessly taken by this transaction
until the end of this transaction.
Okay, and this works as also the how postpress handles the look you,
how does it work in postpress.
So I said like what important is not one statement
is getting one type of look,
but how they are actually relating to each other.
Because when I'm when a transaction is requesting a look
that's trying to require a acquire a look,
and then it's already there's a transaction already held,
there's a look that already held by another transaction.
This one that we are trying to acquire will have to wait.
And then the one after that will come,
that we will be blocked by after this as well.
So we will just keep adding to this queue,
and this will like make a cascading impact on the system.
And again, maybe this is something not easily be understood,
and that's why I think show you some example
the colleague actually works.
And if you didn't specify a look timeout,
this queue will the transaction that gets into the queue
will wait indefinitely.
So that's really important.
So if you didn't set up anything,
you will notice that this will definitely go on
and on until you get probably applications slowed down,
and maybe down time.
So it's really important,
especially when you're running details,
to make sure that you have some look timeout set up,
which we will talk later.
And maybe one thing that I should also mention,
normally, we would expect that we have the system,
you'll peachy looks,
and you would expect to see this looks in peachy looks clearly,
but it is not there.
So you have to actually use this PG blocking PID function
to see which backhand is blocking what backhand is,
which looks like, by the blocking what process.
So that's also important, you know,
which is not so straightforward,
like to understand what's blocking what's just,
you can't really see this in the PG looks.
Okay.
As I said, if you didn't set up the look timeout,
we would have this waiting transactions will wait forever,
that's why it's important to set it.
But then again, it's not like something,
you can just say, okay, I set the look timeout,
all this covered now, I will have no issues.
When you set the look timeout,
then you will maybe get the timeout, right,
when you're running this commands.
And what happens?
You have to think about how to handle this timeout gracefully,
and you have to make a mechanism to retry mechanism,
or if somebody's actually running these things,
they have to try this retry logic implemented,
to otherwise pick the application will probably get errors,
and we don't understand what's happening.
So database is giving some error and application,
not understand it.
So we have to make sure, like if you are sitting this look timeout,
you should also be doing something in case things get timeout.
They need to retry it or revert it through back and so on.
Okay.
And now that we kind of covered the theory of how the looking queue works,
the look-contention post-press.
I wanted to show a simple, like, few query examples
that to display you how it works in real life.
So imagine a select query that is like running for a very long time,
and it is having an access share look.
The access share look is the one that's selects day.
It is the less disruptive one.
It can be used by multiple transactions at the same time.
It almost conflicts with nothing besides this access exclusive look.
But there is this thing that before I started my schema change,
and this select is running.
And I'm trying to run this second comment,
author-table-detached partition.
This specific example is given,
because it's happened in the production,
which has a giant payment system,
because the tool that they were using didn't
it concurrently at the end for this comment,
and it really like co-stop time.
So that's why this particular author-table-detached partition
that you might not think,
the partition might actually influence the main table,
but it did.
So let's say that I'm trying to acquire this will
actually normally need to be the brief access exclusive look.
But it can be that because the select that is running already
for a long time, it has the access share look,
and this access exclusive that I'm trying to acquire,
it actually conflicts with it.
So I can't take it.
So what happens?
The author-table is placed in this lock queue that I mentioned,
and then imagine there are other 20, 30,
they can just try and do,
they also try to like simple primary key lookups,
sub-selects,
but they can't do anything either.
So now they have to queue behind this author-detached partition
command.
So they are all conflicting,
and so what happens that they go back behind this command,
and then until the long-running select finishes,
and until this then author-table finishes,
then they will get their chance.
So we just have this like-custating impact of the effects.
So you would normally not think probably,
because I don't know if all of you check before you run the DDL,
is there some long-running select.
People watching,
yeah, simple select,
somebody's running a view somewhere,
it just runs like an array for,
I don't know, this is how it runs.
But before we actually do this scheme,
it changes or maintenance operations,
like welcome and so on.
We have to be careful,
that's why I put this information.
So as simple select,
the wrong-running select can actually cause this issue,
if you are trying to do the modifications to your schema.
That's why we had this like, you know,
before we said that we had to set the log time out,
and we need to be also ready for,
if it times out,
we have to have some sort of retry logic.
Okay, and I already mentioned,
but this is the important takeaway to just note it again,
any type of long-running query can actually cause issues.
Especially when you have a high read-write workload,
production environment, that's risky.
So please, at least for EGL operations,
set this log time out,
because normally,
I think when you think of an application regular application,
the ideas are not really triggered by users, right?
But users do,
they just make up the same data,
they query stuff,
but maybe they do some deletes,
but they don't rarely probably,
if it depends on their application type,
but adding a column more,
changing something like that,
it doesn't really happen.
So if you can cover the EGL operations,
at least with this log time out,
you should be pretty okay.
You shouldn't be worried about this log time out,
impacting you when you are running this standard DML.
Okay, so we, so far,
talk about kind of like what happens,
what can go wrong,
but there are things that you can actually do
to minimize this looking impact.
Now let's see it over some queries.
For example,
if I'm running some query,
and that query option has concurrently,
try to use that,
like because if,
it will surely will be less impacting
than running the same command without concurrently.
But it doesn't mean that like,
I run the,
how that I have this like,
creating this concurrently,
I use this detached partition concurrently,
all my issues solved.
I don't need to think about anything anymore.
I will not block anything.
It doesn't work like that.
It will probably make it a bit less restrictive,
because concurrently has its,
it devises the things in the different transactions,
actually internally.
So they will maybe briefly take the access exclusive,
and then do the other thing,
and then it will try to make it less impactful.
But this will take longer,
that's one thing that you need to remember,
it will take longer,
and it might not complete successfully,
so that it might leave stuff in like,
not completed,
and so you need to be also aware of,
you need to check them,
like you can't just say,
okay, I run concurrently,
it should be okay.
So you need to be aware that there are,
some risks associated with that too,
but as long as you are aware of it,
you can,
that are actually the comments,
for example, in this partition,
the touch partition thing,
there's a,
a comment called finalize,
you can actually fix these things,
if they are kind of somehow disrupted.
So it's important that as long as you know,
what can cause also another thing that is very important
when you use concurrently,
the other comments are transactional,
but when you use concurrently,
these are not transactional,
but do I mean by that,
I can't roll back these things easily.
So I have to start over,
I have to do it again,
and I have to run the finalize or something,
you can't really like reverted,
you can't really like,
you know,
it can't be in the transaction below.
So these are information that if you have this knowledge,
you can use the concurrently,
but use them by knowing what you are getting into.
Okay, the second thing,
how actually we can do more
by just utilizing postgres.
So one of the things that I think comes to mind,
that we can split this large,
the most impact in the videos,
to something some different steps
that will be less restrictive in the system.
So what do I mean by that,
for example,
I have this first command here,
after table,
I'm going to add a not null with default,
with a volatile function,
it's like clock time step.
So if I have this,
like it will block everything,
even the select,
so it will have the access exclusive,
it will block everything,
even the selects,
it will hold this look for the entire duration of the table,
right?
Because it has to rewrite the entire table.
So if your table is really large,
this can actually,
can need to significant downtime.
That's why I'm thinking,
how actually I can't avoid having this access exclusive
for a very long.
So what can I do?
So I can actually have some,
we can have some simple strategies to apply here first.
I'm having it like,
I'm adding the nullable column with a default,
default,
instead of having it not null at the beginning.
So I'm adding like,
I'm adding a variable,
let's call them,
just I had the default,
and I have this default function.
And then,
after that,
I'm trying to update the table with
populate all the data,
and covering the null values.
So I'm trying to go through the,
this update,
and my populating goal in the null values,
and you should do this in batches,
because if you run all this at once,
you remember in the long-running queries,
are scary and dangerous,
so you have to remember to do this in batches.
And then,
after that,
I actually covered all this null values,
and I already had my column,
it's like created before,
it's a default value,
so I covered everything now.
And after this adding and not now concentrate,
it will be much easier,
because then,
when I split this,
what I did,
I reduced the time that I need to use access exclusive.
I actually avoid,
I actually avoid doing this update,
so I kind of make sure that new inserts are impacted,
but the existing,
we are not actually blocking those things,
so I actually did,
like a smart operation,
and at the end,
then I am trying to add the not now.
That's like,
it already reduced the impact a lot.
Okay.
And then,
I want to show actually more,
what actually could be done better,
remember the last commented I run in the previous example,
while setting the not null column like this,
and set the not null.
But there are,
this still needs to take a long time.
It will still block the right,
so what can we do,
that it's still better,
because it doesn't actually block increase,
because the previous one,
when you're adding with default and with volatile function,
it was already blocking reach too.
But this is a little bit better,
but it's still blocking the reach.
I'm blocking the right.
So what I did here,
that I utilize the check constraints,
so while I'm adding in the last three,
you could see,
I'm first adding the not null as a not valid.
So it doesn't really check the ones
that are existing already,
not for it means that it doesn't mean that,
because I think it confuses people sometimes I see,
this check constraints is applied for the new data
that is being inserted,
so it doesn't apply for the data that's already there.
So what I'm trying to achieve is that don't,
don't trigger for like checking for all the things
that I have in there.
So I'm trying to make sure this check constraint that I'm adding,
is actually applicable for the new data,
but it's not,
it's still in the normal state.
And after that,
I am trying to validate it,
and then after I validated,
I can actually add the constraint,
if I want to not null constraint.
And then once you actually postrate this smart enough
that it knows that it actually check constraint is fulfilled,
then adding a not null is like a simple material operation,
so it is easy.
It will still have the access exclusive.
It will be really brief,
and you have to remove now the other constraint that you created,
if you want it to have not now already there.
So remove the redundant thing,
it will still again have a brief access exclusive.
But like,
as you consider our methodologies,
that we are trying to reduce the content,
the time of it,
and how much that I hold this access exclusive,
that looks everything even reads.
So these are writing important,
and it's just an example of it.
There are multiple different operations,
for each operations you might need to probably think about it
a little bit more,
how can I avoid this.
Okay, so that will bring us to the fifth takeaway,
find something that is less,
that does a less looking,
and sometimes,
like, of course, you can't avoid some looking,
and that it can be hard,
and also it will,
of course, require some sort of expertise,
which is really clear,
that it's an imposterous documentation.
So if you check this,
how we can avoid these are not something we discovered,
or I discovered,
is clear,
like, check the check constants,
how the invalid words,
how the concurrent the commands are,
so just you can find this information.
And again,
this is the slight that I like the most.
Postgres keeps getting better.
So it doesn't stay again like the same.
So,
some operations before,
maybe in the past,
was like requiring maybe more restrictive looking,
but some of them sure already just made the changes.
And so, for example,
it's a very simple example that I wanted to add here,
adding it like column,
not now with default value one.
Before,
this will still require an access exclusive,
and it will block everything,
but in postgres,
it will be very,
now in the modern postgres,
it will be very quick,
because this is a constant value.
So postgres recognizes that this is constant,
it is one,
it can be stored as metadata.
I don't need to rewrite the table,
but before,
this would be requiring a rewrite,
as in the example that we had with this volatile function,
when we were having the same command.
And this location became minimal,
it will make this operation much less disruptive now.
But if you are running an older version of postgres,
this might be having the same impact on your system.
So please try to upgrade,
if possible,
and make use of it.
So then you will have more,
more developments to do how,
how long they will take,
and more concurrently comments as well,
in the example that we see.
Yeah.
So this is used latest version of postgres,
if you can.
And this is the kind of like final part of the presentation.
I will briefly discuss the tool,
that is,
we are developing in the company that I work for.
It is open source applied to license,
so if you try it,
let us know it is in the version 0.8,
and we are trying to,
you know, first major version.
Soon, that is being planned.
So if you see something,
if you try it,
let me know,
because yeah,
we need more users to try it,
and maybe more contributors,
if you are interested.
So that must be motivation.
I think the motivation is up until now,
it is very clear.
So these things are happening.
We have to sort it out.
And there are some impact on our system
that we can't maybe foresee.
So I have to plan how the looking issues work,
and we have to make sure that there is,
if you don't want to create an impact,
your system,
you have to do some data backfilling back and forth.
And what Pedro, how we do it,
is that we are trying to utilize a pattern called
expand contract pattern,
which is hard to say about.
What does it do?
I think it's a software pattern.
It's not something we discovered.
We have to do it.
While you are doing the migration,
you don't touch the existing column.
So you are trying to,
in the same physical table,
you are trying to add another column.
You do all these operations there.
Let's say you are trying to add a constraint.
You do it there.
And you don't do anything on the old one.
And then we have multiple views,
because we duplicate schema.
So we people who are application client applications.
They will have their own different versions of schema,
which one of them will be the old schema version,
with the physical table that only shows the existing column.
And we also add the other columns and create
another schema version.
And they will all, when they connect,
they will all see a different view of the old schema.
So that they will see that the new one will access
with the one that it is,
which are not now constraint, let's say.
And the other one will don't have this,
it's not now constraint.
But it will keep serving.
So both of the versions live at the same time.
This is the most useful thing about PGLO.
So you can serve both of the applications.
You are not disrupting anything.
And then once you are done with the old one,
you can actually mark it.
And in the meantime, you can test your changes
while both of the versions are at the same time available.
But how we do is, of course, impact is like,
you have to do dual rights.
You have to keep everything.
You need to, if somebody entered, let's say,
a value that is actually null value.
And you are trying to enforce a not null.
So you have to change, modify this null value,
that fits in your new version of you.
So you have to modify this.
And if somebody like added any value,
you have to push this to down as well with the update
the old version too.
So you have to keep all these both versions intact.
So this is what we call the tool rights.
And yeah, this is mainly, I think,
the theory behind it.
We are utilizing triggers here.
And I will show more later on.
And of course, when you have both versions at the same time,
that means that if you don't like something,
you can roll back.
And normally, I see, in my experience,
when I see people that have migration plans,
and the sigma migrations,
there are, like, tendons that will have,
like, the sigma migrations scripts that type pile up.
And there's also the scrollback scripts that type pile up.
They version them.
And be changing anything in the database schema,
become becomes a problem.
Multiple teams are involved.
I see that some companies have their own scripts
of how to do these things, what to run,
and what to change afterwards.
And this application and database
changes, arranging those things.
It becomes quite problematic.
And that's why I think this tool might help.
Okay.
I briefly explained how it first,
but maybe if we go like a more step by step,
it allows higher level operations.
Like, when we were seeing how I can actually run this SQL
comment, I show you that I split this thing to,
let's say, to three steps.
And I try to handle the,
how the locking would work,
which kind of order I should run.
Tool allows you to say,
let's say, run an alter table,
and then it actually handles this in the back word.
So you can use a JSON file to adjust what you want to do.
How do you want to do this casting between the different columns
and different data?
And as I say, we use this expank contract,
method, which is actually,
once you do the migration,
both of the versions are available,
you expand the things.
And once you agree that, okay,
I'm not rolling this pack.
I'm just completed the migration.
You actually drop all the other things,
then you'll just keep the new version.
So that is the phase that we call contract phase.
And yeah, you have the multiversions of schematics.
As I said, it allows the higher level operations.
So we convert this operations through the escrow
commands that it will run in a way
that it will not probably cause issues or looking.
And we have a long time out already set for the video operations.
So you don't need to worry about it.
And it will also handle the back filling.
And you can actually change,
however you want it to be done.
And you can actually add,
when you do this back filling,
you can do it in batches.
You can adjust the size of the batch.
You can actually put delays to it.
So there are multiple ways that you can actually
have more control over stuff that maybe it wouldn't be easier.
Just if you try to do this all directly in escrow.
Okay, I think we don't need to talk about this part.
We already said like, yeah,
we are adding the columns to the table and we are just hiding
or showing through different views how they look.
And when no longer needed, we are just dropping them.
We are all back.
Or we are just going more into the new version.
Okay.
So I think this is a good image to show like during the,
there is a time that is kind of in the middle of between
version one of application and version two of their application.
This at the same time, this is in the red kind of color.
They leave simultaneous data same time,
which makes it super easy.
If you want to do this library of green deployments,
you can start the migration,
have tests, everything,
and make sure that all the things are done.
And then either you roll back to the operation,
if you don't want it,
or you finalize the migration.
And then at that point, this common,
the distinct finishes.
We contracted, I may continue with the new version of the application.
And if you are interested,
there is, I added some links to the,
most of the slides,
and you can go read more how it's actually done.
A bit more details.
And I like this example.
I don't know.
It's like it's not so easy to understand before the back
or the back story effect,
because I took it from one of the blocks that we created.
I think here, for example,
is a good example.
When you have created a user stable,
you had this full name.
And then by time you decided that full name is not covering,
I want to have a first name,
and I want to have a last name.
I want to divide this thing.
And I want to have the H,
I see that there are nulls in H.
I don't want any nulls in the H column,
so I want to add the null constraint of the H column.
So what we do is,
in the user stable,
we have this full name,
and I'm adding this like,
Pigeral is not null column,
Pigeral is this is the last name.
And while I do this,
I'm trying to make sure that if somebody is entering full name,
I'm trying to make sure it is up and down,
pardon the JSON that I show.
I can actually see,
okay, you make this transformation,
and fill the last name,
fill the first name,
and add this column without actually
disrupting the view.
So all version will be,
it will still be showing the same one.
And the new version will start to already
in the old and new entries,
and will actually behave differently.
And then once I'm done with it,
I'm just actually able to continue.
And how we do it,
we just changed the searchpad setting.
So I'm setting,
for example,
in the first one,
it doesn't allow null in the name,
fill let's say we have a name fill.
So I'm trying to insert the null while you
in the name fill.
It's just say,
okay, you can't enter it,
because now I'm having the view,
that is not allowing me to enter.
So it is why I wait in this not null constraint,
but when I switch back to the old schema,
it will allow the nulls,
but this nulls will be actually transforming the back,
so that the new version of the columns
will actually be filled with this,
whatever transformation that we are trying to do,
and it will continue as this.
So users will not see any disruption.
And yeah,
based this up and down,
that's how we are waking the casting.
So I'm just transforming things here.
I think this operation is a column change operation.
I'm changing the column from,
I don't know,
integer to text or text,
or something.
So I'm trying to backhand for,
use expressions,
and I can control the bed size and the rate
while I'm doing these operations.
Okay,
so I think again,
this is showing the similar thing.
I think one benefit of having this dual right,
so it will,
we are actually moving this,
a problem from the application layer,
directly into database layer.
It actually,
I think,
most helpful in that regard.
So migration logic doesn't end up between,
like the split between the database and the application code.
We are taking these things by using
triggers to the,
directly to the database layer.
And if you want to do
similar way that,
without serving both of the versions,
you will have to do some sort of similar logic anyway,
so it's not,
it's not a very double thing.
If you want to keep both,
you have to have the stool right in any way.
And remember,
when I was saying that when you have the locked timeout,
you have to make sure that you are handling the,
a lot of timeout errors with gracefully,
so there's already,
we deal with this already in the code.
We set the locked timeout,
and we have also this retry logic built in.
Okay,
and we are almost at the end.
So I wanted to basically show,
showcase this tool,
and to give you the,
you know, chance to see it,
and you don't necessarily use it,
but at least take away the,
the same mechanisms that I described,
that is like,
it doesn't need to be part of the tool,
but try to apply in your life,
how, what kind of mechanisms that we can have
directly in progress.
Okay.
Yeah,
so importance is like we need to balance this looking,
and we need to,
because between looking,
performance and the correctness,
there is a balance.
You can't really like avoid,
you can't keep everything at the same level,
so you have to sacrifice something,
but to be able to make sure that we are correct,
and we are actually having multi-current,
like,
concurrence,
the high-current,
the same environments,
we have to be aware of looking.
And yeah,
you can also use tools,
if it helps.
If you like to read more about this topic,
I have a few blocks that I write in pgroll.com,
or you can go to the GitHub repository itself,
and try to contribute already.
And yeah,
this was the block that I started the block first,
and then the block turned to this talk.
I hope you enjoyed this so far.
And yeah,
some people are taking picture,
I will wait for it.
Thank you.
So the technical part of the talk finished,
but I wanted to share something very personal to me now,
and I don't know,
it feels a bit vulnerable,
but last year I lost someone that is really important to me,
and it is really hard to be around
and not having to see my rounds,
but if you don't know his name,
please check it out.
He was really important for the Postgres community,
and for me personally.
So,
yeah,
if you want to remember him,
because we lost him,
watch this talk that he,
it was his last talk,
he was doing the keynote in Prague,
and he was a visionary for Postgres,
he contributed to Postgres a lot,
and I think his vision will lead us
for a long time already.
So just,
I remember him basically.
Thank you.