Michael: Hello and welcome to PostgresFM episode 75.
This is a weekly show about all things Postgres.
I'm Michael, founder of pgMustard.
This is my co-host Nikolay, founder of Postgres.ai.
Hello Nikolay, what are we talking about today?
Nikolay: Hi Michael, constraints.
Michael: Yeah.
And specifically the 6 DDL constraints that Postgres supports.
Nikolay: Yeah, all of them.
Michael: We're back to basics.
I love this kind of thing.
So nice choice.
Thank you.
Nikolay: Ah, it was my choice.
I'm writing this Postgres Marathon series of how-tos and considering
how to create various constraints without downtime.
Already covered checks and foreign keys and something else.
Saying this, those who follow me closely now understand when
exactly we record this podcast, right?
Okay, because I read these how-tos every day.
So, about constraints in the same order as the documentation describes
them. But also let's talk about practical complications when
you have a lot of data and a lot of TPS as usual, right?
Because the documentation actually doesn't cover these topics.
Michael: Yeah.
Another thing I don't think the documentation covers is why do
we have constraints?
I guess it's so obvious.
But I think it's worth mentioning that without these, we'd be
in real trouble database-wise, wouldn't we?
Nikolay: Well, yes.
So, constraint.
So we have a schema, right?
And without schema, it's not good to live without a schema.
Let's say no to NoSQL in general.
Maybe in some cases, it's fine, but if it's financial data and
so on, you need structure to ensure that data has good quality.
And constraints is the next step.
So you have a schema, so you define column names, data types, and
constraints is an addition to all this to ensure even better quality
of data.
For example, you say no one can create more than two rows with the
same value in this column.
For example, email, and this is an interesting situation because
usually people forget about case sensitivity,
Michael: Right?
Nikolay: Yeah.
Of text or varchar.
And then you say if there is a row in this table referencing
for example user id.
It means that such user should exist and so on and so forth.
So it's all about data quality but also sometimes
for foreign key constraints.
It also provides some automation.
I would say limited.
If you have automation for handling, for example, deletes
should dependent rows in the dependent table be deleted
or not?
If the main row is deleted.
But this should be used with care if you have a million rows
dependent.
Deleting 1 row might take a lot of time and this is also not
good.
Right?
Michael: Yeah, I feel like we've already dived into a few specifics
around unique constraints and foreign key constraints there.
But let's go.
Yeah, I think you're right.
I think the Postgres documentation does cover them in a really
good order.
And it starts with check constraints, which are super flexible
user-defined constraints, almost, I'd say.
Is that a fair description?
Where we can choose, I think it describes them as a boolean condition.
So it's a check that returns true or false for each row that's
inserted or updated.
Nikolay: Yeah, check constraint is usually very, how to say,
underappreciated.
It's underused in my opinion.
And unfortunately, there you can define only some expression
related to this table.
You cannot involve different tables and subqueries and so on.
But it's so it limits the expressive power of it.
Michael: Yeah, so we can define it like we can have a check
constraint on a column or we can have it on multiple columns.
Nikolay: Multiple columns is okay.
You can say, for example, I don't know, like the sum of these 2 columns
should be positive or something.
Some crazy stuff.
It's possible, definitely.
And this is good.
I mean, for example, if you say, if you want to say this is integer,
but it should always be odd or even or something like that, right?
You just define the constraint that will be checked, if you try to insert
something which violates this constraint, you will get an error.
And this is how you can achieve better data quality.
Michael: Yeah.
When you say it's underused or underappreciated, what are the
kind of typical cases you see for people?
Nikolay: Typical cases, people rely on ORM and perform all checks
on...
So, usual, like, 3 parts of architecture, front-end, back-end,
and database, usually people start with front-end when they realize.
And this is fair because you should check a lot of stuff, including
all constraints should be checked on front to minimize feedback
loop.
Users should quickly see that something is not wrong.
Ideally before they make an action, For example, filling some
form, I would prefer seeing constraint violated and an explanation
how to fix it before I press submit.
It's not good to press submit, wait some time, and then have
some retries.
It's very annoying.
So constraint checks on frontend make sense a lot.
But then people, if they use ORM, they prefer checking it in
code because it's more flexible, and those who write the logic
for Python, Ruby, anything, Java, they prefer writing it right
there because it's their favorite language.
But the thing is that if a company grows, the project grows, and
then you start having different users or applications, for example,
someone directly works with data in some good UI, or you have
another application written in different code, or in the same
code, but skipping these checks.
Implementation of constraints in application code is weak because
it's not guaranteed.
Only the database can guarantee it.
That's why check constraints or other types of constraints are
good to have in the database because it's the safest way to safeguard,
right?
So you're on the safe side and nobody will violate it unless
there is a bug.
Sometimes I saw some bugs and a unique constraint violation happened.
It's good that, for example, with uncheck soon we will have a unique
constraint corruption check.
Michael: Nice.
Nikolay: Yeah, but it's a different story.
So check constraint, you just say the expression always should
be followed, right?
It should always return true.
If it turns false, such an insert or such an update should be discarded.
Rollback.
Right.
Michael: And what, is it worth us discussing what to do or how
to add one retro?
Let's say you listen to the podcast and you realize you should
have some of these in place but you don't and they're on quite
large tables.
How would you go about adding?
Nikolay: Yeah, actually let me finish about this consideration
about front-end, back-end, and database and relationships between
them.
I remember in 2004, I implemented what's called MVC, right?
Model View Controller.
A very old architecture, maybe not cool anymore, I don't know.
But what I implemented there, I implemented a simple thing.
So we define constraints on Postgres and then we, at bootstrap
of our application, we analyze all constraints and build logic
to inject it to form and also with digital signature because
forms can be...
It was long ago, but it's interesting.
Forms, frontend followed constraints from the database.
It was cool.
I think it's an interesting idea, probably it should be also
rediscovered, I don't know.
So you just define constraints where they should be defined in
the database, but then the frontend follows exactly the same logic and
you don't need to implement it twice because if you implement
it twice, you will have bugs, different logic, right?
Michael: Yeah, well, and I've just realized there's another reason
which is handling concurrent sessions.
So you might have a constraint.
Let's say you have like an amount of stock of an item and
it needs to not go below 0 or an account balance that needs to
not go below 0.
If you have concurrent transactions, you need them at the database
level to make sure you don't end up letting a user take out more
money than they have.
Nikolay: Exactly.
You cannot check if such a row exists and then make a decision
outside of the database.
You need to make a decision inside the database.
Yes.
But also, what I'm talking about is having main constraints should
be in the database, but you can mirror them back and front, and you
can have automation, and it's good if somebody who develops ORMs
or GraphQL or something would follow this approach, considering
database side constraints as the main one.
So, check constraints are not only very flexible, not super flexible,
but quite flexible, but it also has this beautiful option to
be created in an online fashion, so to speak.
Yeah, not valid.
So you say not valid, quite a confusing term.
Michael: Yes, very.
Nikolay: Yes, so things to remember.
When you create something not valid, it means that it's already
being validated for all new writes.
So this is super confusing.
Michael: So it's kind of not validated on existing data.
Nikolay: So you cannot create not valid constraint and then insert
something that violates it.
This write will provide an error.
But what it does is add a not valid flag when it creates a check
constraint.
It just triggers a long-lasting operation of a full table scan to
check that all existing rows follow this logic of the constraint.
Michael: Which is the default behavior when you add a new constraint.
Nikolay: So if you don't think about it and just try to create
a check constraint for an existing large table, it will block
DDL and DML, everything basically, to this table, and it's not
fun.
So what you should do, if you want to do it without partial downtime,
is to do it in an online fashion.
You create, so 3 steps, not 2, 3 actually.
My how-to yesterday was not full.
So first you create it with the not valid flag.
Second, you understand that all new writes are already being verified
automatically by Postgres.
You yourself take care of existing rows.
You check existing rows are okay with this constraint.
Just with simple selects and if you find some rows that violate
it, you probably want to delete them or update them to adjust
the values depending on your application logic or on your logic.
And then only then the third step, alter table validate constraint,
which will scan the whole table but this step won't acquire locks
that would block your DML.
It will block only DDL, but hopefully, you don't alter during
this.
You don't issue any DDLs.
Michael: That middle step's nice, and you can even do it in batches,
I guess, if you want or need to.
But I guess it shouldn't be a big deal.
Nikolay: Yeah, depending.
But maybe scanning the whole table can also be fine, because it's just select,
right?
Well, if you update, yes, in batches, if you found many millions
of rows that are violated.
But this is, it depends, but this three-step approach is very,
like, universal, zero downtime approach and it's great.
Michael: Well, what's the downside of jumping straight to step
3?
Because you're kind of doing that if you think your data is fine.
I guess is it in the real world you're most likely to have some...
Nikolay: Jump, if you want, you just need to accept this risk
and that's it, of course.
If you are 100% sure, step 2 is optional, let's say.
But also, as usual, if you issue an alter with the not valid, you also
need to set lock_timeout and retry.
Because you still need to change the metadata of the table.
And if, for example, Autovacuum is running its transaction ID wraparound
prevention mode, processing your table, you won't be able to
acquire a lock and without lock timeout and retries logic,
you will start waiting and again, in this case, you will block
everyone, even if it's not valid, it's not good.
So retries are needed and graceful alter needed.
I wish there was such an option like graceful and you say like how
many retries and how long timeout for a particular operation should
be.
Michael: That would be a nice word, like instead of concurrently
it could be like gracefully.
Alter table gracefully.
Nikolay: Or concurrently, something like that.
Because I think 99% don't think about it until they have many
thousand TPS and then they realize some basic operation.
It was always working fine.
Sometimes probably not, but people like, you know, okay, we had
an issue lasting 30 seconds, something was not right, but it's
okay.
And then we don't understand why, right?
I mean, you blocked everyone dealing with this table for 30 seconds,
for example, But kind of fine, and we live with it until it starts
annoying you too much, and then you realize that you need to
lower lock timeout and retries.
I mean, it requires effort, unfortunately, to have this, right?
And if you're small, you don't care, but I wish it would be easier,
like something similar to create indexes concurrently or refresh
materialized view concurrently.
So also, when you validate, I think if, for example, a DDL-like
kind of create index concurrently is happening or autovacuum
processing, you won't be able to acquire this lock, so you need
also to be careful.
But in general, if there you start waiting, it's kind of fine.
It just makes your operation longer, but at least no DML transactions
performing DML operations are behind you in line, right?
So I mean, this is also an issue with this final step, but it's
not so harmful as in the first step when you need to instantly
inject this constraint with a not valid flag.
Yeah, so I think we covered it, right?
So let's move on.
Michael: The next one in the documentation is
Nikolay: not null.
I think this should be hidden behind some, I don't know, concurrently
or gracefully.
That would Michael: be awesome.
The next one in the documentation is the not null constraint, which
the documentation points out is probably one of the ones people
are most familiar with seeing in schema definitions.
But it's just a special case of a check constraint, which I hadn't
thought of before.
Nikolay: Right.
But unfortunately, you cannot, well, you can already, but if
you have check constraint NOT NULL, saying this column is not
null, like logically it's the same as the standard NOT NULL.
Same.
But primary key needs the latter, right?
It cannot use...
But it can.
Since Postgres 12, if you don't have NOT NULL constraint and
you define primary key or redefine it, it will try to implicitly
create NOT NULL constraint.
But since Postgres 12, when you create NOT NULL constraint and
you already have check, it's NOT NULL.
It will just reuse it, skipping full table scan, which is very
good optimization.
So you just create check constraint in this three-phase or two-phase
approach, as we just discussed.
And then you can rely on it when you're creating primary key,
for example.
Michael: Or
Nikolay: you can define NOT NULL constraint explicitly if you
need it for primary key or any other, I don't know, maybe your
application wants regular NOT NULL.
Relying on existing check is NOT NULL.
And then you can drop check and NOT NULL is still there and you
skipped this unfortunate full table scan.
While it's like I'm telling this because NOT NOW, creation of
NOT NOW itself, Postgres doesn't support 3-step or 2-step approach.
If you want to create NOT NOW right away on existing table, existing
column, it will need to scan whole table.
Michael: So we don't have NOT NULL, NOT VALID.
Yeah.
Nikolay: So yeah, this is like some nuances to keep in mind.
Generally, my recommendation is to think more about Check Constraints.
This is why I say they are underappreciated.
They are good and they, you see, here they support NOT NULL constraint
creation.
Since Postgres 12, not before.
But it means all currently supported Postgres versions.
Michael: Yeah, true.
Nikolay: 12 is already the oldest.
Michael: Let's move on.
Nikolay: Yeah, unique constraint.
Unique constraint, this is interesting.
I don't know how much detail we should cover here.
Before our recording, we discussed the case I discovered in 2017
and still saw in Postgres 16.
So, unique constraint, physically it relies on unique index,
but it's a kind of implementation detail.
You can say I want a unique constraint and Postgres will create
unique index implicitly.
And it's good that it can be done concurrently, of course, right?
Because indexes can be created concurrently, which is good.
That's it, basically.
You create a constraint, but I think you can say using, right?
Create unique constraint using some index if the index already
exists.
Or what?
Yeah, I don't remember in detail.
But what I do know is that although unique constraint relies
on an index, unique index, it's not absolutely the same.
Logically, again, it should be kind of the same, but you can
have an index without a constraint, not vice versa.
You cannot have a unique constraint without a unique index because
Postgres needs a unique index to support validation checks.
So imagine we created a unique index, but we haven't created a unique
constraint.
One place where you can see a constraint is needed is insert on conflict,
right?
If I'm not mistaken, right?
Michael: Yeah, I think so.
Nikolay: Yeah.
So on conflict requires a constraint to be present.
And if you say you have a conflict, like for example, do nothing,
you cannot, if you have an index without a constraint, you cannot
say insert blah blah on conflict on this constraint because the constraint
does not exist, it will tell you explicitly the constraint does not
exist.
But at the same time, Postgres has an interesting, I think it's
a logical bug, still not fixed, and I reported it in 2017, and
today I checked in Postgres 16, it has it still.
If you try to insert multiple rows, it will explicitly say
that the constraint, and it will use the index name, is violated.
So in one case it says there is no such constraint, and in another
case it says this constraint exactly with the same name is violated.
Okay, so inconsistency.
I think it's just a bug that needs to be fixed and that's it.
Michael: That's funny.
I'll link up the bug report as well.
Nikolay: Yeah, but honestly, from a user perspective, I think
it would be good to stop thinking about unique constraints and
unique indexes as something very different.
I cannot imagine the case when we, like, they should go together,
I think.
I cannot imagine, like, we have a unique index but why don't we
have a unique constraint in this case?
I think they should go together all the time. That's it.
Michael: It makes sense.
Nikolay: In this case, there would not be any inconsistencies
if the constraint...
When I create a unique constraint, a unique index is created implicitly.
Okay, but why not vice versa?
When I create a unique index, why Postgres doesn't create a unique
constraint?
I have no answer for this.
Michael: You can't create a unique constraint not valid as well,
can you?
So there's no difference there.
Nikolay: I don't think so.
Michael: The one thing they have at, like a change in recent versions.
Nikolay: As always, maybe I'm wrong.
I think I'm not wrong here.
Michael: I don't think so.
The one thing that has changed with these in the last couple of
years is in Postgres 15, we got this nulls not distinct option.
Which I still don't, I'd love to hear from people that have good
use cases for these, but it allows you to specify that you can
only allow a single null value rather than multiple null values.
Nikolay: You know, null, it's the biggest problem in SQL model
now, right?
We discussed it.
Michael: We have a whole episode on it.
Nikolay: Right.
But why I think people might want null as like, according to
this, how is it called, ternary logic, so three-value logic, true,
false, unknown, right?
According to this logic, null means unknown, and comparing one
unknown to another unknown, you cannot conclude they are the
same.
You always say they are not the same.
So the comparison should always yield to unknown, so basically
to another null.
Mixing nulls and unknowns is another topic.
This means that a unique index, unlike a primary key, of course,
a unique constraint, a unique key, let's also introduce the term
unique key because the SQL standard doesn't follow this term.
Unique key, unlike primary key, allows nulls in the column or
in multiple columns if it's a multi-column index or constraint.
But since we don't know if they are the same or not, we can allow
multiple nulls.
But historically, exactly because of the problems with manipulation
of large tables and so on.
For example, before Postgres 11, we had, if we, for example,
add a new column and we want a default, it's like a full table rewrite,
we cannot do it.
It was fixed in Postgres 11, not fixed.
A great feature that you can define like a virtual default, right?
But we say a default, we cannot say a default, I don't want a full
table rewrite, I don't want, so I say, okay, I will consider null as
false.
Just in my application. ```
All right?
So, before Postgres 11, null would be my false, and true would
be my true.
In this case, I'm breaking theoretical concepts here.
Null should not be considered as false.
It's not right.
But just I don't want this operation to be such a nightmare.
I have a billion rows.
Now it will be my false.
And this leads me to the idea I want to be like 1 value in index.
That's why.
So to avoid the long heavy operations, I give null a special
meaning, not as it was supposed to have.
This is practice, it's not theory, right?
This is usual, like in many industries, theory was very good,
we developed great concepts, and then you go to construction,
for example, oh, this is how it's used.
We didn't expect.
This is how nulls are used.
Some people use nulls given special meaning, and they consider
it as a normal value.
In this case, they might want an index or constraint to say there
should be only 1 row if null.
This is my maybe there are many other understandings but this
is what I have from my practice.
Michael: Cool.
In fact, you mentioned in passing there one other important thing
about unique constraints is that they can be defined across multiple
columns in the table as well.
It can be single columns; it's really common, but you can do it across
multiple as well.
Nikolay: Okay.
Should we move on?
Primary key
Michael: being a special case again.
Nikolay: Primary key, okay.
So not null plus unique constraint basically.
This pair gives you a primary key but there can be only one primary
key.
So I think we already covered.
You need not-null.
For not-null, you probably need implicitly or explicitly.
You can do it yourself or just rely on it when the primary key is
redefined.
For an existing large table, right?
For small tables, no problem.
Also, by the way, just this morning we had a discussion, for
example, creating this concurrently, should we use it for new
tables?
In my opinion, no.
Because creating this concurrently, or these multi-step operations,
in this case you lose the good, beautiful property Postgres has,
transactional DDL and ability to pack everything into a single
transaction.
If you just define the table, follow normal approach, don't care
about this 0 downtime stuff.
And you will have single transaction, right?
All or nothing.
Michael: Same for tiny tables.
Anything will work.
Nikolay: Yeah, maybe like less than 10,000 rows you don't care
about.
It takes like 100 milliseconds.
Let's go.
You have single step, it's atomic, great.
But if you have large tables, you need to redefine primary key
and int4 to int8, for example.
Michael: That's the big 1, yeah.
Nikolay: Yeah, my team and I implemented all types of this
operation and we helped multiple companies, a few billion dollar,
multi-billion dollar companies, public companies, we helped them
to convert int4 to int8.
I know a lot of interesting stuff around it, but in general,
you just need like not null, and we discussed how there's also
tricks if you post-guess 11 trick, like default minus 1, not
null, you can define right away, virtually, right?
You don't need even check constraint but since Postgres 12 we
rely on check constraint but we remember primary key needs actual
not null And we also create unique index and when we create primary
key, we say, using this index.
This allows us to put primary key creation as the final step into
a transaction, which will probably swap something, right?
Yeah.
Rename columns as well.
And there, of course, you also need to think about lock acquisition,
retries, low lock timeout, like all this stuff.
And it's, of course, if you have many, many, many, I don't know,
like gigabytes, dozens, hundreds, maybe terabytes, and a lot
of TPS.
You need to engineer this carefully.
I mean,
Michael: this is probably the most involved of all the things
we're talking about.
And I think it's probably too big to cover today.
But there is a really good talk by Robert Treat that I saw that
covers this in about half an hour in depth for people that actually
have to do this kind of thing.
Nikolay: I'm sure it's not possible to cover everything in half
an hour because there are several methods.
They have pros and cons and there are many many nuances, for example
Foreign keys.
If you redefine primary key, you need to deal with foreign key
redefinition.
And it's also interesting.
And autovacuum and running transaction **transaction ID wraparound prevention mode** can
block you.
And also if you decide to mark foreign key as not valid and then
you realize it blocks writes.
Or you just forgot to drop old foreign key and new rows after
switch.
So a lot of mistakes.
Michael: Yeah, please use bigints or **int8** in your
new tables.
Right away.
Nikolay: Or UUID version 7, 8.
Yeah,
Michael: Or UUIDs, yeah.
Cool.
Foreign keys?
Or actually, one last thing, is it worth discussing, like, multiple,
you can have multiple column primary keys?
I guess that's obvious from the multiple column unique ones as
well.
But yeah, foreign keys.
Nikolay: I don't think it's something somewhat different.
Yeah, so just you need to have not null on each column participating
in primary key definition.
That's it.
Unlike unique keys, of course.
So foreign keys involve two tables.
And creation of foreign key requires several locks to be acquired
on both sides.
Fortunately, and full table scan of both tables to ensure that
values in referencing table have values that are present in the
referenced table.
So in this case, if you just don't care and like brute force
approach, like defining documentation, just create it, that's
it.
Well, you have an issue because you will you are going to block
probably not, I think lock level there is not so bad than in
previous cases we discussed check constraints but you're going
to block DDL for sure.
Probably DML won't be blocked or will be blocked.
Yeah, probably it will be blocked as well.
I don't remember details here, although I wrote it a few hours
ago. Check out my how-to.
I specified all the locks and what you're going to block.
But in general, you should care about it as well.
And generally, under load, you shouldn't want to create foreign
key in one step.
You need, again, three steps.
First, creation with not valid, with retries and low timeout.
Then you need to check, again, like with checks, Postgres will
start checking new writes that inserted and updated rows, or
deleted in this case as well, because if you...
Michael: Very important, yeah.
Nikolay: Yeah, yeah, yeah.
And you need an index, but it's another story.
So, it will start checking to validate this constraint for new
writes, but for existing rows, we're still not sure.
So optional step two is to validate and fix if you see problems,
potential violation, right?
And then third step, you say alter validate constraint.
Again, understanding that ongoing index creation or recreation
or vacuum can block you.
So yeah, also three steps.
I explained in detail in my latest marathon post.
So what else to say here?
Foreign keys can be marked as, how's it called?
Deferred, right?
Am I like deferred constraints, right?
So when you have a complex transaction, you might want foreign
keys to be checked at commit time later, not at each statement
time, later.
And in this case, I don't remember again details.
Several years ago we had an issue, we worked with Miro and had
issue with using PgBouncer to fight bloat and they used deferred
constraints.
So there is an article about it, how to use PgBouncer if you have
deferred constraints.
And it explains a lot of details I will provide, Nick.
So Miro Engineering wrote it a few years ago.
It was interesting.
Also issued to address.
What else?
I think that's it.
Michael: I think on delete cascade is worth mentioning.
Like you can define at the point of constraint creation, what
you want to happen if, like if you have a, a really common example
is a table of blogs and a table of blog posts.
If you delete the blog, do you want the blog posts to be deleted?
Like what do you want to happen in those cases?
And that's the case where I think it's really important to mention
that whilst with when we define a primary key, we get an index.
Foreign keys defined on one table, their referencing column is
not necessarily, we don't check that it's indexed as well.
In fact, I've even read a blog post saying you shouldn't always
index that column.
But I think the cases that you should far outweigh the cases
where you shouldn't.
It is worth checking you do have indexes on those, so that
those deletes on cascade are efficient.
Nikolay: Right.
I always try to avoid using this option, but I see people in
quite large databases under a large load use this deletion propagation
or update propagation logic.
So 2 things here, you're right about indexes.
And Postgres DBA toolkit I have and postgres-checkup tool, they
have reports to find, like, you have a foreign key, there is an index,
primary key on one side, you don't have an index on the other side,
so when you will need to delete, you probably won't need to delete
every day, but at some point if you have to, it will be a sequential
scan.
Very bad, very slow.
But this is only one thing.
What if you're deleting a blog which has a million posts?
I don't like it.
I don't like this propagation.
If we know that only low volumes, low number of rows will be
automatically deleted.
It's okay.
But if it's an unpredictable number of rows, I would prefer having
my own logic with asynchronous propagation of change, with some
batches and so on.
Michael: Makes sense.
Nikolay: But I see people use it on a quite large scale.
Michael: Makes a lot of sense.
Cool, I think we're down to the last one.
We've made it to exclusion constraints.
Nikolay: Right, I always confuse exclusion constraints with constraint
exclusion, which was related to partitioning.
But exclusion constraints are for time ranges, like intervals,
and you want to define, you want to say my intervals should not
overlap.
So it's kind of advanced uniqueness, right?
Maybe not uniqueness, but it's like for special data, for GIST
SP-GiST indexes and so on.
So you define, I say, I'm building some schedule.
I cannot allow overlapping, or I'm like describing some, I don't
know, like 3D world and I have various shapes and I don't want
them to overlap.
In this case, GIST and its variation, SP-GiST, will support this constraint
and ensure that no, like, no balls, for example, or no cubes
or something overlap in that space.
So, Postgres supports this kind of thing.
Which is good, but it's quite a narrow use case for me.
I don't see it often.
Michael: Yeah, very cool.
I've heard, I think I've seen it being used in examples where
People are designing room booking apps or things like that.
But no, not used it myself.
In fact, it's a tiny little documentation entry, isn't it?
So it's
Nikolay: time and space.
Yes.
But yeah, the combination is very brief.
And here, I honestly don't have anything to say.
It's supported by some index, I guess, right?
So an index you create with concurrently, usually, if you want
0 downtime approach.
I'm not sure if exclusion constraint can be created based on
an existing index.
It should be possible.
I have no idea.
Michael: It says, yeah, good point.
I don't know.
Let us know in the comments.
Nikolay: Yeah, but in general, I think we covered pretty well
how 6 types of constraints, at least 5 types of constraints we
covered, and how to create all of them without downtime.
Just for this final part, let's think how to drop them.
Well, I think dropping is straightforward usually.
Yeah.
If it's like unique constraint, which is based on an index, you
probably just drop an index with concurrently, right?
Yeah.
Others, you just drop them, but you need a low lock timeout and
retries to be involved, because Postgres needs to change table
metadata, and during this, if it's blocked, it's not good.
So again, low lock timeout and retries are going to help here.
Michael: What about altering?
Like I saw a good blog post by where people took some one of the
use cases for check constraints is, for example, we often say
use text in Postgres.
It's in a lot of recommendations to use text and not be kind
of constrained on how long.
Nikolay: Not varchar, not char.
Michael: Exactly, yeah.
Nikolay: This, strictly speaking, is not a constraint, right?
But in a broader sense, it's a constraint.
But it's- Oh, but
Michael: you could implement it with a constraint.
Nikolay: Oh, exactly.
Yeah, and I like it because you control exactly how and when
it will be verified.
And you know, again, this is exactly why, again, check constraints
are underappreciated.
And recently, I indeed prefer using just text without limits,
not varchar(n), but just text.
And then if I need to limit, I use check constraints, I know how
to set it up without downtime.
And I know how to change it if I need to increase or decrease
this limit, I know how to do it, right?
And It's good.
Michael: But yeah, I think we only lose 1.
I think we only lose 1 benefit or like 1 optimization.
I'm not sure if there are if there is this optimization for check
constraints.
Let's say we want to relax the constraint from 200 characters
to 300 characters.
I think in if you're using varchar Postgres will handle that
knowing it doesn't have to recheck existing data.
But that's the only downside.
Nikolay: If you increase n in varchar, it won't rewrite.
And it won't scan it, because obviously if you just increase
the limit, existing rows already are okay with this.
So yeah, I agree.
And in the case of check constraint, I'm not sure if we have
any... Michael: I don't think so.
I've not heard of one.
But we can do the three-step process you mentioned.
In fact, we can probably add another constraint and then drop
the existing.
Nikolay: Exactly.
Michael: Yeah, great.
Cool.
Thanks so much, Nikolay.
Thanks, everyone, for listening.
Catch you next week.
Nikolay: Yeah. See you later.