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.

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.