Nikolay: Hello, hello, this is Postgres.FM.
I'm Nikolay from Postgres.AI and as usual, my co-host is Michael
from pgMustard.
Hi Michael, how are you doing?
Michael: Hello, Nikolay.
I am good.
Thank you.
How are you?
Nikolay: I'm very good.
So our topic today, we are going to discuss denormalization. But
it's very like maybe high level and basics and the pros and cons
of having it used, applied as a methodology.
So we already had a couple of episodes.
We discussed materialized views.
It's a very close topic to this one.
What else?
We discussed schema design.
Michael: Yeah, we called it data model trade-offs.
I'll link up those two.
Yeah, I think data model trade-offs was probably the closest
to this because naturally it came up as a big topic.
And then the third one that it came up in was I had a good chat
with Markus Winand and he made some great points around normalization
and denormalization that well, at least normalization I'll
link as well.
Maybe I need to re-listen to that one.
Nikolay: Yeah, it was my choice for this week, and I'm just
thinking that it's important to get back to this topic at some
maybe slightly different angles because I keep seeing issues
with this and cases of this, and I have two different customers
which have consulting contracts with Postgres.AI, and I just
observed the need for denormalization, or at least to consider
applying denormalization to solve some performance issues.
Before we discuss performance and reasons, let me say this.
If you don't understand normalization, don't use denormalization.
You need first to learn normalization, and feel it, and use it.
Because otherwise, with relational databases, it can be tricky
to understand particular cases and limitations and consequences
of applying denormalization.
I'm not talking about non-relational databases, but we can maybe
mention the work by Stonebraker in the EDB blog, discussing
that MongoDB particularly, or maybe DynamoDB there as well, I
don't remember exactly, the problems which happen to MongoDB,
I think, document databases and big JSONs, and the tendency to
have denormalized state design by default normally, and issues
later, both flexibility and performance-wise.
Michael: Was that the blog post with Álvaro?
Nikolay: Yeah, but, yeah, yeah, yeah.
So it also had the word harmful, which I inherited and then got
a big backlash on my sub-transactions related blog post on Hacker
News.
Yeah, so let's keep that aside and consider we talk about only
Postgres and maybe slightly wider relational databases.
First thing you need to understand is normalization, normal forms,
at least 3, 4, Boyce-Codd, right?
Normal form of M.
Functional dependencies, it's very interesting thing to understand.
It's not only theory because it's like it's it feels theory and
always felt like theory to me but when you design your schema
if you understand these things you learned it studied them you
feel much better thinking like how to design schema tables and
columns and foreign keys between them right?
Michael: Yeah but and in the in the world at least in the last
3 to 5 years I think the vast majority of systems I've come across
have had pretty good like understandings of normalization and
I've got pretty good hygiene in that area from the things I've
come across.
I think it's 1 of those database things that back-end engineers,
full-stack engineers, generally do look up on.
They do know it's important and they do some thinking and up-front
design work, get second opinions and things.
So I actually haven't come up...
I have come across some cases of, you know, like, is it EAV,
entity attribute value, like nonsense or like just things that
haven't been fully thought through, but they've been really rare
compared to people that have done things pretty well from the
start.
Nikolay: EAV is radical normalization, right?
Which leads to bad performance, usually.
Yeah, that's a good point.
But I think many back-end engineers look at this topic, normalization
versus standardization in general, using lens of ORM, right?
They have different terminology, right?
Sometimes hard to understand each other talking.
Also, I think today Most powerful LLMs, they do a great job designing,
suggesting design.
For example, we integrated and it produces mermaid diagrams.
So if you ask to design, I don't know, like Twitter-like or Facebook-like,
YouTube-like schema or e-commerce, and then you iterate adding
tables, it generally tries to be like, to apply common sense
in terms of normalization as well
Michael: That makes sense.
Nikolay: But again before starting to use denormalization.
It's good to understand normalization first in several forms.
Then first thing when you Let's say sometimes it's convenient
to have a denormalization.
I maybe cannot think about a particular case right now, but it
happens.
I know it happens.
Sometimes you just think there is functional dependency here,
but I don't go and normalize and move this data to a separate
table, for example, because I know, for example, this functional
dependency might vanish in the future.
You know, it might change.
I just know my field, and this functional dependency is only
for now, it feels so, but it might change.
In this case, it might be beneficial and providing more flexibility
if you don't apply next step of normalization in this situation.
Yeah, it sounds abstract and yeah, I don't, I cannot imagine
some.
Michael: I think of 1 example, like imagine if you're an early
stage startup without product market fit and you're adding features
quite quickly and make like a simple example would be You're
a blogging platform and you think the idea of adding tags would
be a good idea.
So you instead of adding a whole extra schema around that you
could have a single column where you let people add a few tags
and you know that might not be how you want it to be eventually
but you also might remove that feature in a couple of weeks if
people hate it or something So like you might for convenience
go with 1.
Well you don't like that.
Nikolay: Well this particular case I remember my talk in Maryland
2008 the first time I came to the US to speak how good Postgres
is for web 2.0.
It's like 16 years, right?
And this was my example, tags.
And I talked about hstore.
Or today we can talk about Array.
Back to that time, there was no GIN at all.
GIN was not created yet.
We used only GiST and R-tree or L3 in some cases, it depends.
But in the case of tags, it's natural to think, okay, I'm going
to put these 2 different tables, and then you need additional
table to have many-to-many relationship.
And this is AE-AV, exactly.
This is what is going to have bad performance if you have a lot
of entries in the main table of like your objects and also a
lot of tags and a lot of relationships between them.
It's really...
This join can be problematic sometimes.
But we will probably return to this schema, like 3-table schema.
Later what I wanted to just emphasize, I cannot just not to comment
here, it's about performance.
I would these days consider arrays with tags or maybe arrays
with IDs.
We know foreign keys don't support this, like 1 row and you have
tags, tag IDs, right, and then like multiple foreign keys to
different entries in the text table, but we can live without
the foreign key here.
For the sake of having good performance, we can just have these
IDs and or just text as is like text, text array, right?
So integer 8 array, big int array or text array.
In this case, using GIN should be good.
Michael: Or just JSONB.
Nikolay: JSONB, I feel would add like additional layer of something
here which we don't really need because we just need ideas or
bunch of phrases, texts, like text, right?
So I don't know.
Do you think this is denormalization?
Michael: I think strictly speaking, yeah.
Nikolay: Well, let's not say we are breaking first normal form
here Because of course first normal form is like say it's saying
it's just saying that in each cell basically of a table In each
Each value should be atomic, right?
It should not be a table, for example.
Or in this case, array or JSON.
We are breaking it, strictly speaking.
But in this sense, if we consider...
In this case, every time we use array, we are already breaking
normal form.
But in what sense it's denormalization.
If we are repeating values, For example, later we found a typo
in some tag and going to adjust it.
This is functional dependency, of course, and we will need to
update a lot of rows here.
Of course, it's better to store IDs and have tag values in a
separate table.
So in this case, we can even join in an interesting way, maybe
with lateral join or unnest here.
It's interesting, but it's a separate topic.
So if we don't store values of text, I don't see denormalization
aspect here.
It's just, yeah, we treat the first normal form in interesting
way.
This discussion happened 20 years ago, I think.
I remember some from Chris Date, right?
Some works like, are we breaking the first normal form in such
cases or no?
But if we store just IDs, well, it's quite well normalized and
we don't have a AV so we can use GIN search much faster.
If we need some tags, we like this is this query is going to
be much more efficient, in my opinion.
But we lack foreign key, unfortunately.
This is what we lose in such approach.
So, flexibility is 1 thing.
Different thing, and this is most interesting of course, performance.
We already started discussing performance.
I would highlight 2 particular cases.
This is exactly 2 particular customers I observed over the last
couple of weeks, 2 particular situations.
Let's start from maybe a simpler 1.
It's slow count, right?
Did we have an episode about it?
I'm sure we
Michael: did, yeah?
Nikolay: So slow count or in broader view, slow aggregates.
So there are approaches to solve it using like index-only scan.
We also had an episode.
I think we are covering more and more things, which is great.
But if we see that it's not enough, what else?
We need to start denormalizing in some form.
And obviously, we could use materialized view, which has all the
counts, but it's very asynchronous.
Michael: And the...
Like pre-calculated.
Nikolay: Yeah, pre-calculated.
You can do it, you cannot do it often.
You cannot, For example, we have a huge table, like 10 billion
rows, for example, and we insert 1 more row.
And we say, okay, our materialized view is just create materialized
view, blah, blah, blah, name as select count star from the table.
Interesting, right?
Is it a good idea to do it often?
Well, maybe no.
We should do it not often.
In this case, it will be lagging and showing not relevant information,
like kind of eventual consistency.
Yes, we will reflect next insert unless it's got it's got deleted
right but it will be lagging and some people can notice it not
good.
So what else like we can consider synchronous and also like when
you refresh materialized view, it's kind of everything like it could
skip some old data understanding it hasn't changed, and just
count the only fresh part of the table.
Maybe we have partitioning there, I don't know.
But Postgres doesn't support only additional extensions like pg_ivm,
or what was the name of different extension, I forgot, I just
found it recently.
Michael: The 1 you just mentioned, was it denorm?
Nikolay: Yeah, denorm.
It was interesting.
I think it's Python and external to Postgres, because pg_ivm
is an extension, so obviously you don't have it on many managed
Postgres offerings.
But if it's external, or for example, you implement it yourself,
it's quite possible, using maybe triggers or something, maybe
some queue mechanism involved externally to make it asynchronous
and incrementally updated.
In this case, it can be good and maybe more resilient, like updating
more faster and having more actual data.
The problem is, like with modularity, the problem is it's heavy.
It's a very rough and heavy tool.
In most cases I deal with them.
I say let's try to avoid them.
Get rid of them, right?
In this case particularly as well.
By the way, we could build also our own like kind of materialization
mechanism using logical decoding, right?
Logical replication even.
For example, accumulating some events through, it can be also
external queue mechanism, but also using logical replication,
if you batch and then update, not on each insert, but after 100
inserts or updates and so on, deletes also.
And then you reflect this change, and nothing is lost because
it's quite Postgres-centric approach, right?
Because Postgres guarantees nothing is lost.
What do you think?
Michael: Well, I like it.
I remember reading a good blog post by Timescale on how they
how continuous aggregates work and I remember thinking I would
I wouldn't want to implement that myself like I remember thinking
it like keeping it synchronous or like synchronous enough like
it's just quite painful so I do admire people that have done
that and yeah I can see the argument for it, but I also I think
at this point if once you're considering that you probably also
should be considering the synchronous approaches like triggers
or some other way of keeping things just actually in sync.
Nikolay: Yeah, well, I agree and I like this.
I have a sense of like lack of some additional materiality mechanism
in Postgres which is not yet developed which could support some
kind of asynchronous way to update things.
Michael: Oh I'd love it if it was in core that would be amazing.
Nikolay: Yeah in core exactly this would be great but it's maybe
a hard task to have.
Michael: And also not that high up the priority list for most
people I think.
Nikolay: Yeah but it would give a lot of interesting things interesting
capabilities to develop interesting well-performant and responsive
I mean not responsive but data is coming and you reflect it like
within a few seconds it's great.
By the way
Michael: when you mentioned performance so I looked up the definition
of denormalization on Wikipedia, and it said denormalization
is a strategy on a previously normalized database to increase
performance.
So they're explicitly saying that...
Nikolay: Right, but we can assume that in our head sometimes
we normalize and then we move back and denormalize and then go
like deploy it right away in production.
This would...
Michael: Oh yeah, I thought, obviously there is the...
There's an interesting part there that says it has to be previously
normalized, but I thought it was also interesting that the only
reason they gave was for performance reasons.
Nikolay: Well, maybe I'm wrong thinking it can be not only for
performance reasons Of course in my in my experience I did for
performance reasons.
Let's be honest.
Let's be honest here here I just wanted to cover maybe cases
which can happen
Michael: You mentioned a couple of cases that come up recently.
Nikolay: Yeah, well, before we move to the next 1, I wanted to
emphasize, if we think about, okay, we are not going to use materialized
view, what else?
We have a table, we insert, but we need the count very fast imagine
We have a trigger and we update that count on each insert Or
update and or delete the
Michael: update and delete.
Nikolay: Yeah delete Update maybe no big depending of course
if you have soft delete approach then you need To reflect updates
because they might be doing soft deletion, right?
Michael: Well, and if it's only a count, yeah, sure, but if it's
a different kind of Aggregate function, then you might have to worry about
Nikolay: averages, sum and so on.
Yeah, min-max probably is fast enough, thanks to index anyway.
Yeah, by the way, of course, this also, there is ongoing discussion,
there are ongoing discussions about having Column store and Postgres
all the time, which might be good here, but maybe not.
It depends on the particular case, because if your main part
of data needs to be Row store, you still need to deal with some
replication inside your engine or outside of your engine.
So replication means like maybe it's triggers, maybe it's logical
Replication involved, who knows.
But yeah, it can be interesting.
But again, we have a main table and trigger which on insert,
on each insert, it increments this counter on this additional
table, what do you think will happen?
Is it good?
Michael: Like hot spots, we've talked, yeah.
Nikolay: Hot spots.
Michael: Yeah.
At sufficient scale.
And I guess all of this is only relevant at sufficient scale,
isn't it?
Nikolay: Yeah, so this synchronous approach can be super dangerous
because locking, heavy locking, and even without heavy locks,
if we have Foreign keys, for example, there, and Yeah, it's going
to have multixact ID involved and even SELECTs can downgrade
So I
Michael: think you can spread out you can spread out the hotspot
like You don't have to have a single record that gets updated.
You could have a series and that that series could grow as your
concurrency grows.
Nikolay: Right, right.
That's like, yeah, like, butches in place, right?
And you, then you just sum all of it and this is your total count,
right?
That's, that's good.
Yeah.
This is actually what we did a few times.
We just had batches.
I don't remember particularly how.
Basically, some kind of partitioning of your account inside 1
table, right?
You partition those counts into buckets or batches, how to say.
Then you increment them sometimes collapsing maybe and so on
Yeah, also vacuum is an issue here.
If you update that table very often and it can 1 row can can
be super heavy in terms of real Disk space because of that tuples,
right?
Yeah, So Postgres MVCC is not good in this particular case.
Michael: Well, I think you'd ideally be aiming for those to be
hot.
There's no reason, I think, to index that column.
So I think you'd be aiming for those to be hot updates.
Nikolay: Good point.
Yeah, good point.
Michael: And then hopefully avoid vacuum issues at all.
Nikolay: It's great.
Like index less stable.
Yeah, actually, this is maybe the case when primary key is definitely
against our goals.
Good performance against
Michael: the primary key anyway.
But yeah, I understand.
Nikolay: Yeah, I mean, this is a concept from theory as well.
At the same time, when we learn relational theory, normalization,
we also learn that tables without primary keys, basically, it's
also breaking rules, right?
So in this particular case, we don't want primary key.
It's interesting.
Michael: Isn't there like a saying, something like, first you
have to learn all the rules so then you know how like how and
when it's safe to break them.
Nikolay: Yeah, yeah.
Well, the whole demonization idea, you need to learn how to do
it right and then how to break it right.
To have good performance.
So let's, we don't have a lot of time, so let's discuss these
most interesting maybe case.
Back to AV maybe, but not particularly.
In my projects, I did it several times.
I imagine you have social media, for example.
You have a lot of users, say a few million users, and you have
a lot of objects, say dozens of millions of objects.
It can be posts or comments, anything.
Maybe it's not only like posts.
There is also something, some concept, like kind of blog organization
or some data source where posts exist.
And people can relate to those channels, right?
Channels, let's say channels.
And for example, they can subscribe Or they can have permissions
to view them or not to view them as different things So and then
you just need to display last the most the freshest 100 entries
posts or something right
Michael: relevant to them
Nikolay: relevant to this person yeah only from subscriptions
yeah or only only that data which is allowed to view to access
to edit or even even worse if you want to show hundred last updated
or last accessed last changed different kinds of timestamps can
happen here.
And it's interesting because some timestamps belong to the objects
itself, for posts, for example, creation time or modification
time.
But some of timestamps can belong to this particular relationship
between users and those channels or posts themselves.
For example, last accessed.
Right.
It's definitely for each person.
It's different in respect to particular post.
1 user accessed it 1 day, another user, it's the same day but
different time, so timestamp is different.
So this particular task is very common.
This is common pattern.
Not pattern, Pattern maybe should be applied to solutions, right?
But usually people just do SELECTs, joins, and that's it.
And at some point, performance become terrible.
It happened first in 1 of my social medias, and I was super upset.
I thought it's really similar to graph issues Like graph working
with graphs like for example return First circle of connections
second circle of connections and try to find some connections
like in LinkedIn, right?
I remember many, many years ago, we were trying to solve it in
relational database.
It's quite difficult.
We didn't have a recursive CTE at the time and lateral joins
and so on.
So It's a hard problem to solve.
Query has several joins and filtering, order by, but some filters
columns exist in 1 column, in 1 table.
Different columns exist in different tables.
When filters and order by are in different columns, in different
tables, it means you cannot have ideal situation, a single index
scan or even index only scan, which is very good.
Then you need to rely on 1 of 3 join algorithms, Postgres implements.
I can assure you there will be edge cases where statistics and
planner, they don't have idea how to execute this.
So
Michael: this is
Nikolay: bad performance.
Michael: The age-old issue of which is more selective.
Is the planner going to be better off going through an index
on the order by until it finds enough posts, or whatever the
example is, to satisfy the limit?
Or is it going to be cheaper to look at all posts that satisfy
the other conditions and then order those assuming they're a
smaller set and unless the statistics are good then you could
end up doing the wrong 1, you know, the more expensive 1, and
have a very slow query if it's a huge index.
Nikolay: Exactly.
Imagine we have, we order by creation time, and we just subscribe
to channels, channels have posts, so we have users table, we
join with channels, we have subscriptions and posts, and then
we order by creation time of posts and limit 100, right?
Order by desc, order by creation at desc, limit 100.
So in this case, indeed, as you say, Postgres need to choose
what to do.
Is it a good idea to extract all subscriptions and then in memory
order by and find 100?
Or it's better to use an index on created ad, go backwards on
this index, and try to find posts which can be displayed for
this particular user, meaning that they are among subscriptions.
Actually, both paths are bad.
They might be good in some cases, but at a larger scale, there
will definitely be cases where both paths perform really bad,
dealing with a lot of buffer operations and bad bad timing, right
execution time.
So how would you solve this particular?
How?
What was
Michael: the solution?
Nikolay: Yeah, yeah, well, denormalization is 1 of the ways,
right?
Michael: So like storing a duplicate of this data in a materialized
view?
Nikolay: Yeah, For example, we take creation time and we just
propagate it.
For example, if it's a relationship to particular items, posts,
we can just propagate to this table which represents this relationship.
And then We can even have an ideal situation, a single index
scan.
Not index only scan because we usually need to join data and
bring actual data from… Maybe it's not in the index, but it might
be index only scan for this particular table, which has a relationship.
This is 1 of the ways.
Of course, if you have channels, well, it's more difficult, because
if we order by creation of posts, not channels.
So it's, yeah.
Michael: Yeah, I think some of these cases, you mentioned the
1 where different users have different timestamps, for example,
if it's like last viewed, I think that gets really complicated
in terms of...
Nikolay: No, vice versa, I think it's good, because if we order
by last access, For example, last access timestamp on channel,
we just deal with channels.
We can propagate it to the posts themselves.
Well, we cannot.
Yeah, I agree with you.
It really becomes complicated.
So there are cases where it's really hard to apply denormalization,
but there are cases where it's easy.
If we forget about channels and, for example, think about relationship
between users and these objects, it can be, for example, permissions
or it can be, I don't know, like last access timestamps should
be stored neither in users nor in objects table.
It should be stored in a table in between, right?
Like many-to-many relationship.
So in this case, usually, by default it's good.
We just use this table, We have index, we quickly find what we
need, 100 for this particular user, 100 objects that should be
displayed.
But if additional filtering, this is usually in real life, we
need additional filtering involving data inside objects table,
right?
It can be, well, soft delete, for example, we mentioned soft
delete.
So like, deleted at timestamp.
If it's not, if it's filled if it's not now Then the subject
should not be showed in this result set right But we can propagate
when we delete, we can propagate in all entries this object has
to all users.
We can propagate to this table.
Of course, it can be a lot of rows.
This depends on the situation.
I would not propagate it synchronously if it's more than 1, 000
rows, because delete will be super heavy.
Soft delete, it's update actually, right?
But we know if it's like limited number of users can access each
object, like not more than 1,000 say, or 10,000.
We can do that.
Or we can do it asynchronously.
Again, there is some need in, like I would say there is a need
not in asynchronous, not in incremental materials, but maybe
in asynchronous triggers.
So like there is an Oracle, there is pragma autonomous.
It's not exactly the same, but sometimes.
There are folks which are using PgQ, for example, in Cloud SQL.
It's available, right?
1 of the good things about Cloud SQL is the availability of PgQ,
so you can implement asynchronous triggers there yourself.
If some object has update to be soft deleted, we propagate to
all rows of this last accessed or last viewed or something table.
In this case, we can have, again, we can have single index only
scan.
But it might be more difficult than that.
There is another way.
It's not about denormalization.
This is how we solved it.
Not only we, I know GitLab also has to-do list and they apply
the same recipe which I think 1 day we will blog about.
Forget about denormalization and just use huge recursive CTE with
some tricks, some algorithm actually implementing this newsfeed
pattern to solve it in a very interesting way.
Michael: Oh, is it like the skip scan recursive CTE?
Nikolay: It's not skip scan, it's like advanced skip scan.
So you know you need to return 100 rows.
You have 100 slots to fill, and then you start working with channels,
filling these slots, and each time you work with next channel,
you think, okay, this channel has a fresh post, for example,
has a fresh object, and this is the place we like we replace
some object we keep in memory we replace and put it to the slot
and at some point you finish and return but it requires some
effort to implement
Michael: yeah I can imagine that that sounds really cool it reminds
me a little bit of the latest feature 1 of the latest features
in pgvector where have you have you seen this in I think 0.8
it added being able to continue a scan.
Like if you...
Nikolay: Didn't help us.
We checked yesterday.
Michael: Oh, no.
But the idea is the same, right?
If you don't get enough results in your limit, go back and continue
the scan.
Nikolay: It's good if you have quite like uniform database.
In our case, we are speaking about Postgres.AI, for example,
we have more than 1 million documents and more than 90% of them
is mailing lists, mailing lists and in-entries emails, right?
And when, for example, you ask for source category in this huge…
This approach doesn't help.
But still, it encounters so many mailing list entries, it cannot
find sources.
So basically we are considering either, I think we currently
already using partial indexes there, and I'm seriously thinking
we should move to separate tables because we have only limited
categories and our use cases, very often we deal with only 1
source.
Michael: You could maybe partition on source.
Nikolay: Yeah, actually it's also a good idea maybe, yeah.
So we deviated here from original denormalization topic, and
I think it's another story how to implement newsfeed better,
right, without denormalization, but with huge recursive CTE.
But, like, my summary is that first of all, denormalization should
be applied only you know normalization, and it can bring new
performance dangers if you don't think about concurrency and
various issues with Postgres MVCC, locking, LockManager, for
example, and so on.
It's an interesting thing, still quite useful to consider in
many cases, but it should be tested well as usual.
Michael: I think it's 1 of those sharp tools, right?
Like, sometimes advanced craftsmen need sharp tools, but be careful
with their sharp.
Nikolay: Yeah, yeah.
Something like this.
Agree.
Michael: Wonderful.
Thanks so much, Nikolay.
Catch you next week.
Nikolay: Sounds good, have a good week, you too.
Bye bye.