Michael: Hello and welcome to PostgresFM, a weekly show about
all things PostgreSQL.
I am Michael, founder of pgMustard, and this is my co-host
Nikolay, founder of Postgres.AI.
Hey Nikolay, what are we talking about this week?
Nikolay: Hi Michael.
How was your vacation?
Michael: Oh yeah, really good.
I had a really nice week off pretty much completely, which is
nice and rare as a founder.
I think you know that.
But I did enjoy listening to the podcast as a listener for the
first time in a while.
So yeah, really enjoyed your episode last week.
Thanks for doing that.
Nikolay: Good to hear.
But let's talk about the question, do we need foreign keys or
we only need American keys?
Michael: Yeah, the word foreign is interesting, isn't it?
Nikolay: Right.
Foreign tables, foreign keys.
Michael: Yeah.
Yeah.
Should we use them or like when should we use them, perhaps,
if not always?
What are the pros?
What are the cons?
In what cases can they be a problem?
Or what cases can they really help?
That kind of thing.
Nikolay: Right.
Well, I think it's obvious that it's better to use them if you
don't expect heavy loads, but if you are preparing for heavy
loads, until what point you can use them, right?
And I think dropping them is easier, maybe.
Maybe not, actually.
If you already designed your system to, for example, to delete,
to propagate deletes using cascade option, if you drop foreign
keys, you lose some functionality, right?
Michael: Oh, I see what you mean.
So like, maybe we'll get to this at the end, but maybe if you
start with them, and then if they become a problem, or if you
start to get success and lots and lots of scale, or lots and
lots of load, migrating to maintaining that integrity of the
system, quality of data without them, and processes around data
deletion, things like that.
Nikolay: Right, so they can give you not only quality of data,
but also some functionality.
Cascade deletes.
And if you rely on them, dropping to solve some performance problems
in the future will be problematic.
I think I've thought about it many times, but we have this also,
right?
We cannot drop them sometimes because we will lose functionality
and this is not good.
But in general, there are people, I know there are people who
think you should avoid them.
And you know, for example, I can tell you, you should avoid sub-transactions.
This is my position and I can prove why you should avoid sub-transactions
unless it's absolutely necessary.
Should we apply similar logic to foreign keys after you saw some
examples of bad behavior or something?
Or it's different?
This is a question I have in my head.
And honestly, in this morning, thinking about this episode, I
was thinking, oh, there are new options in Postgres 17 we should
explore.
Why we didn't explore?
And then I realized, okay, okay, it's only beta 1 of Postgres
17.
It's still too early.
But I can't wait when we will see new results, because we will
talk about it later in this podcast, in this episode.
But Postgres 17 will bring some new settings, which are interesting
in the context of some performance issues you can have with foreign
keys being used.
But let's start with simple things first.
Michael: Good idea.
Nikolay: We had an episode about constraints, and we mentioned
there are 6 constraints, right?
6 constraints, and foreign keys is 1 of the types Postgres offers.
Usually I say foreign keys are good because I trust database
system much better than anything else when we talk about data
quality and constraints and integrity and so on, and referential
integrity, what foreign keys offer us.
You cannot maintain it in complex systems without foreign keys,
because even if you implemented it on your application code,
for example, Python or Ruby, later your system can be becoming
more and more complex.
And if someone brings other interfaces to database, new application
code, like in different language, different frameworks, for example,
or someone is working directly with database somehow.
It's not uncommon to see multiple types of code working with
the database, right?
In this case, you have already multiple implementations of foreign
key, or not foreign key, referential integrity checks and enforcement
logic.
It means that, For example, imagine you have Ruby code and you
have Python code somehow working with the same database.
They both need to have this logic, and it's hard to maintain.
this logic.
Eventually you will see some cases where this integrity is not
enforced.
So, while in database foreign keys are implemented internally
using triggers, triggers is a good thing in terms of like, it's
inevitable.
Of course, you can say ALTER TABLE DISABLE TRIGGERS ALL.
In this case, it will disable all triggers, including these implicit
system triggers, which are supporting foreign keys.
This is when you can see them in backslash d table name.
If you ALTER TABLE, DISABLE TRIGGER all, and then backslash d,
you will suddenly see that, oh, this table has some triggers.
Because usually they are hidden, backslash D doesn't show them.
But when they are disabled, suddenly they show up.
So it's a funny trick.
And these foreign key, these triggers, unless you disable them,
and you should not disable them of course, unless you know what
you do.
I mean, during some massive operations and so on, but in this
case, it's on your shoulders to ensure that they are followed,
Because when you enable them back, Postgres won't check them
for existing data.
So if they are enabled, they are inevitable to work.
So any write you perform is checked using those triggers, and
if write breaks these rules, it won't happen.
It will be rolled back.
Which is good.
So it's like more trustworthy approach, checks on database side.
Make sense?
Michael: Yeah, so I mean to be super explicit, if you try and
insert a row in the table that has a referencing column, and
you include an ID that isn't in the reference table, then you'll
get an error saying so.
That kind of thing.
Nikolay: Right, right.
So this is a good thing, obviously.
Better quality of data, but of course there are complexities
under heavy loads in large systems.
1 of the complexities is when you define a foreign key for existing
large tables, or you define foreign key for a new table, but
it points to a large table.
Yeah.
Of course, in this case-
Michael: not just large, but busy, right?
Nikolay: Busy, okay.
Michael: Or active in any way, yeah.
Nikolay: Well, there are 2 types of problems here.
One is related to busyness, another is related to large volumes
of data.
Anyway, we need to...
Postgres needs to acquire locks on both sides,
Michael: which is challenging.
Both tables.
Nikolay: Right.
Different types of logs, but anyway.
And second, it needs to ensure that existing data already complies
with our constraint.
Michael: Constraint, yeah.
Nikolay: Right.
And if you do it straight, like in a regular way, in general,
you will have issues.
High risk of issues.
And the risk is becoming higher and higher.
The more you have data, the higher are TPS, the higher risks
are.
So at some point, and it's better to do it earlier, you need
to install foreign keys in a proper way.
So you need...
Fortunately, Postgres supports...
We discussed this, but let's repeat.
Postgres supports a two-step approach for constraint creation
here, similar to check constraint.
You can define foreign keys as not valid state, flag, rights.
It means that they will have flag not valid, but important to
remember, it doesn't mean they are not working.
They are immediately working for all new rights.
They are just not checked for existing data.
I remember I had a super big mistake when I designed some complex
procedure related to int4 to int8 conversion, and I
forgot that so-called invalid foreign keys are working for new
rights, so I kept old foreign keys at some point just for the
sake of being able to roll back, I mean to revert all operations.
I decided, oh, I need them because if a decision will be made
to revert changes, I will already have them.
I will just validate them.
But new rights were not followed.
New rights were blocked, basically.
And this was a big mistake I learned.
So it happened on production and it was terrible.
It was my mistake.
So not valid doesn't mean it doesn't work.
Similar to check constraint, and actually indexes, if you say
it is valid, false, which is not actually recommended due to
different reasons.
It also means index actually is maintained.
So foreign key is maintained for all new rights.
And then second step, you say alter table validate.
This is not blocking.
It's blocking briefly, right?
But if you have huge dataset to check, okay, your DML queries
are not blocked.
Again, they are blocked very quickly, like briefly, for a short
period of time.
Anyway, this is not a trivial operation, but it's already well
described in various sources.
For example, as usual, I recommend GitLab migration style guide.
It's called migration style guide, but it's about database migrations,
it's about DDL basically, how to deploy DDL under heavy load
reliably.
So, and they have Ruby code, which is called Migration Helpers
RB, which demonstrates how to do it reliably under any heavy
load.
So yeah, it's 1 thing to remember, you cannot just create them
easily.
But this is true for any DDL.
DDL is dangerous.
We discussed this multiple times.
There's also a headache associated with installing them to partition
tables.
Right?
Yeah.
I always forget details.
Every time I deal with it, I need to double check details.
It's always an issue.
Michael: And it changes, right?
The restrictions around partition tables change every version.
Exactly.
It improves each version.
So things that I thought were not possible become possible.
So it's good that we check documentation, documentation is great,
but it's difficult to remember the details version to version.
Nikolay: Right, the changes are in a good direction.
Michael: Exactly.
Nikolay: Which is good.
So yeah, if you're an old version, it's one thing, another version,
like newer version, different thing.
But in general, on newer version, a lot of things are possible.
So, so yeah.
Michael: One last thing that you normally mention around creating
these on large tables with, or like busy or large tables, is
the idea of having timeouts and retries.
So I think that, I don't think you mentioned that this time,
but it's an important extra point.
And I'll link to your...
You did a good Twitter...
When you were doing a Twitter marathon, you did a great post
on this.
I'll link that up as well.
Nikolay: Yeah, I already forgot about this.
But Yeah, right.
In general, if you need logs, you can do it explicitly with log
table, but you need to do it with...
So basically, if a two-step approach, like invalid and then validate,
is implemented in Postgres, generally you don't need it.
For custom checks and for foreign keys.
But for some things like adding some column actually, or dropping
column, it's needed.
You need to deal with locks and do it with lock timeout and retries
as usual.
But with foreign keys...
Michael: Why not do it with this 1 though?
Like, I would...
I still think it's sensible.
I think, for example, you gave a good example of if autovacuum
is running in the heavy mode, in
Nikolay: the transaction...
Michael: You are right.
Nikolay: You are right.
You are right.
Yeah.
Michael: So if it doesn't yield its luck or something else isn't
yielding a luck that you don't know about, it's so helpful then
to have that timeout on a retry.
Nikolay: You know what?
In general, DDL is hard.
Like under heavy load in large systems, it's really hard.
I wish it was much simpler in Postgres.
I think there is a huge room for improvement step by step, and
I hope it will be improved.
But now we need to, it's like art.
You need to improve your tools and libraries and so on.
You are right.
Even when we install a foreign key with not valid flag, we still
need logs on both tables, right?
And these logs, if they cannot be obtained, they will block us.
And if they block us, we start blocking everything else, including
SELECTs, because it's DDL and SELECTs is waiting.
So, yeah, it's kind of...
I think for SELECTs, in this case, it's not that...
Maybe not.
Michael: Or inserts, for example.
Nikolay: Yeah, we need to check, we need to carefully check types
of locks that need to be installed on both cases.
I only remember they are different on both sides.
But in general, this should be carefully designed.
It's better to follow existing experience.
For example, GitLabs, maybe some frameworks already implemented.
I don't know.
Django, I think, Ruby on Rails in general, they don't offer good
automation here.
It's not enough.
So if you're preparing for heavy loads, it's definitely worth
keeping an eye on this topic and maybe to check it earlier than
it starts biting you, because sooner or later it will.
So first it bites, nobody notices, right?
Okay, some spike of latencies, a few seconds during deployment.
But then at some point, especially if longer transactions are
allowed, at some point it can bite you so heavily, so you will
notice downtime a few minutes, for example, not good.
So it's better to double-check carefully, step by step, and rely
on other people's experience.
But you are right, we need to deal with low log_timeout and retries
here as well.
And I think especially for partition table case you need it.
Michael: Why especially there?
Nikolay: Because I think, I don't remember details, but I remember
when you create foreign key on partition table, you need more
actions to be done.
First,
Michael: yeah.
Would you have to do it on each partition and then...
I
Nikolay: think at least until some version, you cannot create
a not valid foreign key on partition table.
This is a problem.
Michael: Oh, interesting.
Nikolay: Right, right.
Yeah.
But again, I don't...
Yes, it differs for each version and so on.
It should be carefully studied, tested, and so on.
But with proper understanding that locks are needed, it's solvable.
Right?
It's solvable.
So maintenance overhead, basically.
There is some maintenance.
Oh, let's mention 1 more thing.
Maybe people don't...
Not everyone realizes it.
We also didn't realize until our clients at some point, it was
a company called Miro, which is very popular now.
We helped them with some things to fight bloat and so on.
And we offered to use pg_repack to fight bloat.
And they had at that time deferred constraints.
So foreign key constraint checks were done at commit time, not
immediately.
And I remember that it caused some issues with pg_repack.
Again, I don't remember all the details.
There is a good blog post about this.
I will send it to you.
But additional maintenance overhead, which is caused by the presence
of foreign keys in this special state, deferred constraints,
deferred foreign keys.
So definitely if you use foreign keys, you need to pay attention
to different things when you do some operations with your database.
This is true.
But I would say it's worth it, right?
Because you have good referential integrity and so on.
So I would still choose foreign keys in spite of these problems,
right?
Yeah,
Michael: I tend to agree.
I think the vast majority of us are working on systems that don't
have the characteristics where foreign keys cause problems.
I know we had an episode only a couple ago where we had some
possible exceptions to that.
Well, maybe we'll get to that in a moment.
Nikolay: You talk about 100 terabyte episode, which was also
episode number 100.
We have received good feedback about this, considered as the
best episode we had.
So, yeah.
Actually, someone told this was the best podcast episode.
What's so like?
Yeah, We had great guests.
Again, thank you because you organized all the invitations and
so on.
Michael: I was just pleased to be there.
It was a good fun 1 to record as well.
But yeah, not just the fact that they're 100 terabytes, right?
The fact that they're heavily loaded systems and they're constantly
busy and they have to worry about a lot of these scaling limits
and I guess keep hitting different cliffs or different
Nikolay: cliffs.
Michael: Maybe that's a bad word but like limits of what you
can do or how many of a certain thing you can exhaust before
falling back to a different method.
But 99.9% of us are not working with those systems and don't
have to worry about those and don't have to worry about them
initially as well.
So I think it was Sammy from Figma who said as well, and I think
I agree with this, at the beginning maybe you don't have to design
for this kind of thing and if you have to solve this, it's a
good problem.
You're probably successful in other ways.
If you've got to a heavily loaded Postgres and you're not making
enough money to pay people to fix this kind of problem, you've
probably done something wrong.
You need a business model that works with that load, and all
of them did that successfully.
And I can't think of an example where a company got too successful
with load, but not with finances.
So I think they can normally solve these problems.
Nikolay: Well, I not fully agree.
I think, yeah, let's talk about cleaves, but first let's talk
about performance overhead, foreign keys, and PgBench supports
foreign keys option, which is not used by default.
And before this podcast, we were very curious, we didn't like...
Simple question, is it better, what's the difference between
2 PgBench runs with and without this option?
Of course, this option should be used during initialization time,
but we checked and we saw difference on a small scale, like 1
million rows in PgBench accounts, scale 10.
We saw difference only 11%.
On my MacBook, it was a quick and dirty experiment, all in memory
and so on.
As expected.
I expected also like 5 to 10% or so.
You said 10%, right?
Michael: I guessed.
It was a complete guess.
And also, I don't know why I guessed before even knowing.
I'd forgotten the breakdown of what proportion was selects versus
inserts updates and deletes I definitely shouldn't have guessed
before knowing that.
Nikolay: Why do you care about selects, deletes, updates and
so on?
Michael: I thought I guessed that well maybe this is wrong again
I guessed that there would be a bigger impact on...
Right.
...Inserts.
Yeah, exactly.
Than, so, than the select operations.
That was my guess.
Nikolay: Well, by the way, actually, maintenance, Let's put it
to the maintenance overhead basket.
When you need to delete a reference set row, a row in a referenced
table, sometimes we call it parent or something.
If an index, well, index always exists on 1 side of foreign key
because otherwise foreign key creation will complain lack of
index.
You need like unique index on 1, or primary key, for example.
Primary index.
Index for primary key, which is also unique index, right?
Michael: You need a unique constraint.
Nikolay: Right, right.
If index is not created on the other side, which by default is
so, you can...
Michael: Yeah, no, yeah.
Nikolay: Yeah.
And you delete this reference row.
Postgres needs to find all dependent rows to either say it's
okay, or maybe to propagate delete if you again use cascade deletes.
And if an index is not there, it will be a sequential scan of the
whole table.
So delete will be super slow.
And this is also kind of maintenance overhead to me because if
you expect...
Sometimes people say, oh, we don't have such deletes, we use
only soft deletes, right?
So, or something, like, we don't care.
We don't need those indexes to be present.
But if you have some risks, sometimes, like, not often, sometimes,
these deletes are happening very rarely in some systems.
You need to maintain these indexes and need to remember about
them and need to create them and so on.
So it's also a kind of overhead.
Michael: I see this quite often.
And I actually didn't write a blog post about this, it was quite
a while ago now.
But yeah, it's the time I see triggers causing performance issues
the most often, because
Nikolay: it's on blast.
Michael: That's exactly that's how you spot them in the explain
plans.
Yeah.
So I'll include I'll include that.
But my view is normally it makes sense to index your referencing
columns.
Also for select perform, like often people, I find they're a
good candidate for selects anyway.
Yeah.
Like there's loads of workloads and patterns where it makes sense
to have them indexed.
Of course, if you're already adding like a multi-column index
with them in the leading column, you don't need an additional
index.
But what I mean is like an index on that so that they can be
looked up.
It is normally.
Nikolay: Yeah, our Postgres checkup tool also has such kind of
report, non-indexed, like foreign keys without supporting indexes.
But we usually say it's very special.
And some people, I see this as well.
I see that people say, well, we don't need that because our work
is different.
Michael: I remember a blog post, I think it was from Pocona,
arguing the opposite case, saying, please don't automatically
index.
I'll include that as well.
Nikolay: I would say as well, I would say don't automatically
index because every index has a different kind of overhead.
It has a penalty, right?
Michael: Well we've discussed that many times, haven't we?
Not just insert overhead, but also killing hot updates in some
planning.
Yeah, lots of,
Nikolay: lots of.
So many dangers around when you need to grow and have good performance.
Right.
So back to these experiments with PgBench, with and without foreign
keys, You say you expect problems only in write operations.
Interesting.
Michael: Or at least more overhead.
Nikolay: Let's remember that because I have a case, you know
I have a case where it's very different.
Okay, in this case, PgBench, we observe roughly 10%.
I'm quite sure it will be the same at larger scale, on different
machines, and so on, with PgBench, I think.
PgBench is my PgBench type of workload.
Roughly 10%.
Would you pay this price, knowing that everything is slowed down?
Right, operations are slowed down by 10%.
Michael: Most systems I work with that have been easy.
Yes, like, most systems aren't maxed out on CPU aren't maxed
like it's, it's an easy decision.
And for the for the anybody that's ever dealt with bad data,
Like bad data can be so painful, as in bad quality data.
I mean, you have to look through a system and you're like, this
doesn't make sense.
Like these are referencing something or maybe like a product
that just doesn't exist.
What plan are these customers on?
Or, you know, if you're doing analytics and you're trying to
categorize things, and there's bad data in there, it just becomes
such a nightmare at those times.
Nikolay: Or somebody just disabled foreign keys, did some writes
and then enabled them back.
Or some back, sometimes, or human cases without foreign keys
actually.
Michael: But I also want- I'm talking about, yeah, I'm talking
about cases without, but I mean, anybody that's ever dealt with
that, and it's probably kind of slightly more seasoned folks
that have had to deal with it once or twice in their career,
it's just so painful that you then think, do you know what, I
don't want to, I'll happily pay 10% going forwards to not have
to deal with that again.
Nikolay: I remember cases when the financial integrity was broken,
even with foreign keys, but it was a bug in Postgres that was
very long ago.
Since then they became very reliable and I would pay this price,
10%, to have these checks constantly performed by Postgres to
ensure the data quality is higher in this area.
And let's emphasize that this overhead is not a cliff.
It doesn't matter how many transactions per second you have,
how many rows you have.
This extra penalty is added to all write operations all the time
because extra work needs to be done.
For example, you insert, Postgres needs to make sure that you
insert a value to child table.
Let's call it parent-child for simplicity.
It's not necessarily meaning of the relationship can be different,
but let's call it.
You insert a record to a child table, Postgres needs to ensure
that parent record exists because you're referring to it.
So it performs basically implicit select to check that the record
exists, and this is extra work that always needs to be done.
And even if you have 1 transaction per second, very tiny workload,
still penalty will be there.
We can check this actually with EXPLAIN (ANALYZE, BUFFERS) as
well and see that more buffers need to be involved when you insert
a row with foreign key versus without foreign key, right?
Michael: Maybe 1 more?
Nikolay: Well, depends.
Depends how big the table is and how many hops index scan
Michael: should show.
True, actually, definitely not 1, at least 2.
Nikolay: Again, it depends.
But maybe, I don't know, maybe it's index only scan.
I think there's the
Michael: page, there's like the top...
I don't think you'll ever get data in that root node, but I could
be wrong.
Nikolay: Well, yeah, okay.
Anyway, we agree that this is not a cliff.
It's constant work.
Michael: Yes, true, true, true.
Nikolay: Additional workload, which is like...
Additional weight you need to carry with performing this work
all the time.
You have a contract to do this all the time.
It's like a tax, actually.
Foreign key taxes.
Michael: That actually is a good metaphor, I think.
Nikolay: Yeah, like 10% tax from foreign keys.
So we have consensus here.
We are ready to pay those taxes to live in the world with better
data quality.
But there are performance cliffs, as you already mentioned, and
they are terrible sometimes.
So one of them was raised a year ago by Lukas Fittl, at pganalyze
series of small videos which I like.
Michael: 5 Minutes of Postgres.
Nikolay: Right, right, right.
They are always based on some other focus materials, and Lukas
reflects very well in the video.
So it was based on Christophe Pettus blog post, where a simple
workload was described.
Imagine a Twitch stream and a new stream is starting, and we
have a streams table, kind of a parent table, and we have some
viewers or something table, and immediately like 1,000,000 or so
viewers join the stream and you need to insert them to this,
let's say, child table, parent and child.
And if you do it in separate inserts, separate transactions,
when Postgres performs insert, not only it needs to check that
we've selected that row exists, during the duration of insert
this writing transaction, we need to lock that row with access
share lock.
Basically, for key share lock.
If it was explicit lock, it would be for key share lock.
So to ensure that this row won't disappear during our transaction,
right?
Michael: Yeah.
Nikolay: And imagine now like a million inserts are happening
around the same time.
Of course, it's not possible.
It depends on how many resources you have, but many of them will
collide and try to happen at the same time.
And in this case, since multiple transactions need to perform
a row-level lock on this parent table, Postgres starts using multiexact
IDs.
Right?
Multi-exact IDs.
I'm like, oh.
So multi-exact IDs are created and multi-exact IDs are used when
multiple transactions lock the same row.
And this mechanism is very easy to achieve some performance cliffs
when you grow, when multiexacts are actively used.
Well, by the way, Lulas also mentions another problem with multiexacts.
Many, many monitoring systems, and PgAnalysis is a good exclusion
here, as well as our version of PgWatch, PgWatch PostgreSQL Edition,
They both care about this.
But many systems care about only regular transaction ID wraparound.
They forget that multi-exact ID wraparound also might happen.
It's just very rare.
I never heard about this case so far, but it's still possible.
So you need to monitor and ensure that autovacuum freezes rows
properly and takes care of rows which were involved in this multiexact
mechanism.
So 1 problem.
But another problem, this cliff.
So in source, we have contention on multi-exact mechanism.
Multi-exact has SLRU, S-L-R-U, and buffers are quite small.
The number of buffers, like there are default.
So it's not default, it's hard-coded until Postgres version 17.
And yeah, so it's like...
I saw this problem with SLRUs in different cases, in sub-transactions
case many years ago, not many, several years ago.
And I remember I touched it a little bit at that time already.
And sub-transactions is also a performance cliff.
Everything is right, your scale is right, and then suddenly the
system is down, basically, because overhead becomes enormous.
It usually happens when you achieve the limits of these SLRU
buffers.
We needed it for sub-trans SLRU, but this is similar to multi-exact
SLRU.
Around the time I learned that Andrei Borodin already proposed
improvements in algorithm, not only improvements, but also make
this tunable.
And good news, this work is committed to Postgres 17.
So if we observe this performance cliff at some point, at some
rates of inserts, you observe performance becomes terrible, like
system unresponsive downtime, right?
You might try to tune to postpone this cliff from your current
situation.
But the only-
Michael: At what cost?
Like a little bit of extra memory?
Yeah,
Nikolay: algorithm was improved instead of sequential capital
O of N, I think.
Or maybe even quadratical algorithm.
It was improved, better algorithm.
But also you can adjust configuration and have more buffers,
for example.
I remember we tested some earlier versions of these patches.
There are multiple patches in this work.
By the way, big congratulations to Andrei.
More and more works he was working on, like many years, they
are committed.
It's great.
But I remember there are some complexities in our particular
case related to sub-transactions.
But I think we should test these things and study this exactly,
this very performance cliff Christoph describes in his blog post,
right?
Michael: Yeah, well, so I think that's worth mentioning because
this is 1 solution, but Christoph also mentions a couple of alternative
approaches to that schema design.
Nikolay: He mentions reliable approaches.
1 of them is just drop foreign key.
In this case, no more performance cliff, because multi-exact
mechanism is not involved.
Absolutely true.
Michael: But also alternative approach, like batching the updates,
or there was another idea.
Nikolay: What updates?
Inserts.
It's a very important...
We always say you need to perform deletes and updates in batches.
But we never said this about inserts.
Inserts is better to do in a single transaction if you can.
Because inserts are usually not blocking.
Not in this case.
Here we need to deal with this multi-exact row level lock.
But if you can do it in single transaction, it's great, even
if it's a million rows, it's okay.
Or multiple bigger batches, like a thousand inserts.
But straightforward implementation of this approach, like, oh,
viewers, just join our channel.
It will be separate inserts because there happens different sessions,
right?
You need to know about this problem to design system differently
and start batching these inserts.
So it's a dangerous performance cliff.
But this performance cliff demonstrates the problem for inserts.
I have another case, which is not yet published.
I hope we will publish some blog post about this.
It was not observed in production, but it was inspired by some
problems we had with some customers related to multi-exact IDs
contention.
So imagine we have parent and a couple of records, just 1 and
2, ID1, ID2, and child table with some inserts happening.
Not at a huge rate.
Not at a rate where we already have this performance cliff.
And then also we want to have a lot of selects, selecting, for
example, the latest child inserted to the child table.
Just with join, right?
So we join, we select parent ID 1, and we select what was the
latest child inserted recently.
We have timestamp, for example, to do this, or we just order
by primary key, if it's just an integer primary key for the child
table.
And now, we think, Well, that's it.
So we inserted, like, say, we insert 10 per second, for example.
And we select as much as we can.
Like, imagine we have a lot of viewers trying to read the latest
inserted record to child.
And now we start updating the parent id equals 1.
This is the cliff.
We perform terrible for everyone, including selects.
This is the key for this demonstration.
It can be like 100 times worse.
100 times bigger latency, 100 times lower
Michael: TPS.
It really surprised me when you told me this, when you showed
me this.
Nikolay: Well, We need to research all the details, but I suspect
this is related to the fact that in this case, when you have
inserts and updates of parent inserts to child, they also...
Well, first of all, all those inserts, if they happen around
the same time, it's also multixact involved.
But updates also need row-level lock, right?
And they also...
Actually, multixact ID is being put to XMax hidden column,
system column, right?
So XMax column defines the transaction ID when the row becomes
dead.
And if the transaction succeeds, okay, this tuple is dead.
If the transaction is cancelled, nobody knows.
It's like it would be XMAX being now, right?
Live tuple.
But in this situation, Postgres puts not regular transaction
IDs, but multixact IDs.
And produces some volume of dead tuples all the time with multixact
IDs inside XMAX.
And I suspect that when selects are trying to find live tuples
for parent, it's really expensive because multixact mechanism,
we need to check which transactions were canceled.
It's very expensive.
So, selects need to scan a lot of dead tuples, performing expensive
verification, and they degrade a lot.
So this workload, I think, anyone can experience.
And I remember I designed a few systems where, for the sake to
have fast counts, when inserting to child table, for example,
comments or something, or views or something, I don't know, like
something, I decided to update some counter in the parent table,
to have denormalized data, to avoid slow count.
So I just insert an update in the same transaction.
In different transactions, I also insert an update, plus 1, plus
1.
Of course, this already is not good, because these updates have
some contention.
You're updating the same row, you cannot update 2 times, it will
be sequential, like Postgres will.
It will be contentional heavy locks.
You can improve this, but anyway, even if you perform batched
updates, for example, in separate transactions, you have high
risks of multi-exact IDs being involved, and then you already
can have a lightweight lock contention on multixact mechanism.
Michael: A favorite.
Nikolay: Yeah, overlock multixact offset.
And yeah, so this is not good.
And I imagine this can happen with very good chances.
And it also can be seen as a performance cliff.
So suddenly, it was fine, but then it quickly becomes terrible.
Yeah, so what to do?
I don't know.
I think we need to study, research all those cliffs, document
them and understand how to predict them maybe.
At what rates everything is fine.
Because I don't see them under very heavy loads, I don't see
them.
But it doesn't mean they're not there.
Michael: Or they're not coming.
Nikolay: Or they're not coming.
At some point, they might bite you back.
Michael: That's interesting.
Nikolay: For those who are interested, let's put links.
I have a series of benchmarks performed with our PostgreSQL bot
with some visualization.
Of course, it requires explanation maybe, but if you know how
PgBench is organized, everything is there, including all, it's
easy to reproduce.
Michael: If you're talking about the graph that I remember seeing,
it doesn't take that much for explanation, because you have 4
fairly large bars on the bar chart, all showing lots of transactions
per second, and then 1 that is so tiny, you have to almost like
zoom in to see it.
And it's quite easy to say, oh, that 1 didn't do so well.
Nikolay: Yeah, let's explain a little bit.
The tiny one is this cliff demonstrated.
3 others.
One was, let's just remove updates.
Another one, let's keep updates, but update ID 2, not 1.
Another one, let's remove inserts.
And the last one, let's remove foreign keys.
Yes.
Which is fair, right?
In this case, just to show that foreign keys is the key of evil
here.
Yeah.
And all bars are TPS for selects.
Maybe latency.
Actually, it's more correct to show latency, not TPS.
But okay, TPS.
Somehow people tend to like TPS numbers more, but latency is
what matters here for sure.
Michael: Then we'd see 1 really large 1 and 3 small ones.
It's not as dramatic a graph.
Nikolay: Yeah, maybe.
Yeah, smaller is worse.
Smaller is not better, yeah.
Because latency is inverted logic.
Smaller is better.
Michael: Anyway, it's still great.
Nikolay: So yeah, SELECTs can be affected badly.
Michael: Has this changed your opinion on should you use foreign
keys or not really?
Nikolay: Yeah, this is the first question I asked to myself and
my team.
I said, like, are we observing a similar case to sub-transactions
where We, like, I basically started telling everyone, like, if
you want to grow, try to avoid them.
At least know all the problems they have.
By the way, for sub-transactions, there is a new setting also
interesting to double check in Postgres 17.
But like, answer is still not 100%.
I'm not 100% sure about the answer here because like foreign
keys is a good thing in general.
So I think in sub-transactions case, we saw how to achieve the
problems very fast, like very easily when you grow.
With foreign keys, again, I'm not sure.
Actually, the customer I mentioned, they removed some transactions.
And the problem has gone.
So I think sometimes these problems come together.
For example, we know that, as we discussed, foreign keys, they
put a SelectForShare lock.
And this causes multiexact mechanism being involved.
But sometimes if you work with SelectForUpdate, you work with
SelectForUpdate.
You don't expect multiexact mechanism to be involved.
But if sub-transactions are there, SelectForUpdate might lead
to multiexact mechanism because every nesting level of your
nested transaction or sub-transaction is considered a separate
transaction when Postgres puts xmax value.
And if you lock row SelectForUpdate and then define save point
and then perform update, this is similar to select for share.
Although it's select for update.
Because basically multiple transactions are trying to lock the
same row, so multiexact is needed here.
And there is a blog post, I don't remember, a very good blog
post about Performance Cliff.
This is a very blog post which said, sub-transactions are cursed,
just remove them.
And I became a follower of this approach.
So in that case, with that customer, we had 2 hypotheses.
Either sub-transactions removal will help or it won't help just
because they had this case we demonstrated in this experiment.
Fortunately, it was related to sub-transactions, but who knows,
maybe in some case we will see that no sub-transactions, but
still performance cliff like that.
Does it make sense?
Michael: Yeah, for sure.
And I think I was just looking up the blog post because it was
bugging me.
Are you talking about the 1 from Nelson L.
Hage?
Nikolay: I think so.
Yeah, yeah.
I included it to my 4 cases with sub-transactions.
Michael: We did an episode on sub-transactions and I found that
in the show notes.
I'll double-check it and I'll link it up if that's the
Nikolay: right 1.
It's very good, very good blog post, very precise.
It doesn't explore too far this situation with Select for Update
and foreign keys.
I think it even doesn't mention foreign keys.
Yeah, because they had different problem, but for sure it was
a good starting point for me some time ago to start understanding
that multi-exact IDs also might be dangerous.
It's also a cliff due to various reasons.
So I think we need to postpone exact answer to this.
And maybe...
Michael: Matthew 14 I had 1 more thing I wanted to add.
I think there's like, there's definitely people I know who, who
have been using Postgres for 5 or 10 years and they're riding
this wave of, they adopted Postgres when it didn't have all the
features they thought they were going to need.
But as they've needed them, Postgres has been adding them.
As they've scaled, their business has been able to stay on Postgres
because it got replication features or it got parallel query
features or some things that they started to be able to really
benefit from Postgres improve as they needed improvements.
So it feels like this could be another 1 of those things that
Postgres is going to get better in this area.
There's enough noise around these things.
You mentioned some, even if these don't, even if these get reverted
in 17 and don't make it in 17, something will probably make it
into 18 or 19 or 20.
And there's enough people at scale pushing Postgres to these
limits that a lot of us that aren't there yet can probably get
away with adding foreign keys, using them even in cases where
maybe it's not recommended at scale, and maybe Postgres will
be improved or will have configurable settings by the time we
do need them.
Nikolay: Yeah, yeah.
Makes sense.
Maybe.
Yeah, we need to explore those settings and test various edge
cases.
I would say these performance-critical cliffheads should be called
edge case.
Michael: Yeah, or corner case is easier.
Nikolay: Corner case, let's say this, what I just described,
sub-transactions plus select for update, this is corner cases.
Michael: 2 edges where they meet.
Nikolay: Exactly, exactly.
This is a perfect example of corner case.
But others are edge cases.
We just need to explore the territory and find all the edges
and think if it's possible to move them to have more room when
needed.
Or just, yeah, at some point probably foreign keys should be
just dropped in some cases.
Sometimes, maybe.
Michael: Good.
Well, we spoke to some people who don't use them.
So that's really, yeah.
Nikolay: But in general, I keep using them.
I keep using them everywhere.
Yeah, me too.
And I keep suggesting use them.
Michael: Great.
Nikolay: Pay your tax.
Michael: And society will be better.
Nikolay: But don't get into jail with these cliffs.
Oh
Michael: Interesting, yeah, thank you so much Nikolay as always
and catch you next week.