Nikolay: Hello, hello, this is Postgres.FM.

My name is Nik, Postgres.AI and as usual, my co-host is Michael,

pgMustard.

Hi, Michael.

Michael: Hello, Nik.

How are you doing?

Nikolay: I'm doing great.

How are you?

Michael: I'm good, thank you.

Nikolay: I tried to say I thought, but actually this is you who

thought it's not enough episodes starting with "multi" word.

So what is the topic today?

Tell us.

Michael: Yeah, I chose multi-column indexes and I know we've

talked about them a bunch.

We've done episodes on things like index-only scans, for example.

But it felt to me looking back at those that we didn't really

get into some of the details around multi-column indexes that

I find particularly interesting.

I think also this is still the area I see people, especially

fairly experienced full stack engineers, backend engineers, struggling

with in terms of performance or not realizing how big a difference

they can make or how, how helpful they can be in certain, especially

a lot of the workloads I see are web application, just like a

lot of reads, like maybe 80, 90% reads and a lot of trying to

optimize performance of those reads, especially with the odd

kind of aggregate type query.

And index-only scans help a bunch, but only once you understand

multi-column indexes.

And I think there's some detail in there that I'd like to discuss

with you.

So yeah, I thought it was about time we actually had one dedicated

to this.

Nikolay: Yeah.

Great.

I guess we will be going slightly beyond just multi-column indexes.

Michael: Right.

Possibly.

What do you mean?

Nikolay: Well,

maybe the question is how we can compare them, for example,

having just indexes on a single column, but many of them.

And sometimes I have a case where people intentionally understanding

consequences chose to cover every column with index, a single

column, I guess.

And yeah, we usually say it's an anti-pattern, right?

Michael: Well, I mean, yeah, I guess it depends.

I've not seen a workload, like with a set of access patterns

where that would make the most
sense.

Personally I tend to see tables
that have some columns that get

queried a lot more than others
and the groups of columns that

get queried very often together.

Lots of equality searches amongst,
for example, account ID, User

ID, but there may be a range search
in terms of date.

And like, that's the kind of Query
that ends up being like so

much more efficient with a multi-column
index than with a series

of single column indexes.

So yeah, the kind of access patterns
I see, I can't imagine living

without them, But I'm sure that,
well, I'm looking forward to

hearing about the case where it
made most sense to...

Nikolay: Well, it was multi-tenant
application where the freedom

of Queries is unpredictable.

So every tenant can choose different
types of filtering and ordering,

and we don't know in advance.

And they are not engineers, those
tenants, right?

So we need to give them some support,
at least somehow.

But index-write amplification,
I guess, is a killer in this use

case if data size grows.

Michael: Yeah.

And given it's a multi-tenant application,
are we talking single

Database per...

Nikolay: Separate tables.

Michael: That makes way more sense.

So that starts to make some sense
then at least.

Nikolay: Yeah, there are smaller
Tables of course.

And there's isolation, complete isolation.

So all blocks and all tuples in
these blocks, buffers or pages,

how you name it, they are belonging
only to specific tenants,

so all problems are localized.

Anyway, if we think columns A and
B and we have 2 indexes on

both columns, when does it make
to consider a 2 column index

instead?

This is a question, I guess.

Michael: Yeah, yeah, I like that
a lot.

And I guess we probably should
go 1 step back and say probably

we're going to be talking about
B-tree indexes for most of this.

Nikolay: How about btree_gin indexes?

Michael: Yeah well there's a few
the docs mention and it's quite

funny The word only is still in
there, I think from the initial

commit.

So it says currently only the B-tree,
GiST, GIN and BRIN index

types support multi-key column
indexes.

And that, the key is important
word there, but basically that

means, key columns not include
columns.

So there is a subtle difference
there.

Nikolay: Well, GIN indexes can't
be multi-column?

Michael: GIN, So GIN can, BRIN
can, GiST can and B-tree can.

So when it says only, I think the
only inbuilt index type that

doesn't support multi-column indexes
is hash.

So I think only is a bit of a dubious
word there.

Nikolay: Is that it?

Is that it?

Michael: That's it?

I can't think of a sixth 1.

Nikolay: Okay.

Michael: Or maybe SP-GiST, but like
I think that's a subcategory

of GiST.

Anyway, basically I think we're
probably gonna be talking about

a B-tree, like the vast majority
of indexes is a B-tree and the

vast majority of multi-column indexes
I see a B-tree.

Have you got any different examples
other than, well yeah, maybe

B-tree, btree_gin?

Nikolay: Yeah, there's a problem
with GIN.

I always like to talk about that
if you have a scalar column,

column of scalar values, like timestamp
or integer, bigint, and

you need to order by it and limit
like 125.

Then it can be inefficient because
GIN doesn't know, like planner

needs to choose to use only B-tree
or only GIN.

And this is where btree_gin can help
to combine them into multi-column

index, right?

So we can talk about hybrid indexes
when 1 layer is GIN and another

is B-tree, for example.

Michael: Yeah, well, and I think
that kind of starts to get to

why we need or want multi-column
indexes in the first place,

because I think a large part of
it is ordering or clustering.

Like you want it, B-tree is a simple
example.

Beautifully, it has a single order
through the entire structure,

right?

Nikolay: Single.

Michael: Yes, exactly.

So the order is really useful for
things like enforcing unique

constraints.

Like things can be, things are
placed in an order and you can

see, is there 1 of this already
in place?

And multi-column just gives you
kind of order by the first of

these columns first and then for
any that are equal order by

the second column next and I don't
think the docs make this super

clear I think they give a good
example but that could be clearer

I think that it's single order
and order by A, then order by

B and then C and then D and however
many you you list and that's

if they're key columns.

Includes is a bit different, that
doesn't affect the ordering

and that has some benefits.

Well, we can discuss whether it
does, I guess, in a bit.

That's 1 of the main things I wanted
to discuss with you.

Actually, I think that's a particularly
interesting part.

But that ordering thing is really,
really important in terms

of then choosing which order to
index things in in the first

place.

So I've never, I've never come up with or come across a guideline

that I'm totally happy with in terms of...

Nikolay: Well, it's straightforward.

We discussed this 20 years ago in some circles.

So, by the way, not only ordering, but also filtering, if your

filters involve range comparison or like between operator, right?

Or in case of GiST, it can be something else.

So anyway, so it can be not only strict ordering, but also just,

is it inside the range or no?

Is it more or less these operators?

But as for the rule, ah, let me this time also step 1 step back

and say super roughly at high level forgetting about parallel

operations and so on.

We have 3 types of access to data.

Sequential scan on 1 side for lots of data, it's the slowest.

index and index-only scan, let's combine them in this case.

On another side, the fastest single index scan, especially single

index-only scan is the fastest if you have a lot of data.

And between them there is a bitmap scan, which is a combination

of bitmap index scan and bitmap heap scan.

And if you have like, This is like a proposal to have an explanation

why we need a multi-column index.

In case of 2 columns and filtering or ordering on them, If you

have 2 separate single column indexes, you likely will end up

using either 1 of them or maybe both of them, but in the form

of bitmap scan, which is not the most efficient.

And with multi-column index can bring you the most efficient

either index scan or even index-only scan, right?

Michael: Yeah, and I think There are exceptions or times where

they're pretty much equal in efficiency.

Nikolay: Of course.

Michael: But it's very, very easy to demonstrate with non overlapping

like datasets.

Like you quite often use the social media thing, right?

If we had an index on user ID and a separate index on created_at

or you know some kind of timestamp looking at all of the users

posts from a certain time range even if we can do the bitmap

index scan on users just to find all of that users posts and

then a bitmap index scan on all of the posts in that time range,

it's very easy to see how we're having to scan a ton more data

in that And then even though we're only looking at, they're doing

like the bitmap index scan, only having to look at the index

entries and the bitmap itself will be relatively small if that

user doesn't have any posts.

And then the eventual heap scan will be relatively small and

relatively efficient.

The actual initial scans to get the right data is huge compared

to having a multi-column index with all of the users posts next

to each other with their created_at timestamps.

So it's, yeah, in terms of, we
often talk about buffer reads,

right?

Nikolay: We could create statistics,
right?

And so the planner would know that
this user was recently active,

so we need to follow the index
on created_at.

And another is not recently active,
so it's better to fetch all

entries for this user and then
order by memory and have top end

sort in memory.

Michael: But both with any sufficiently
active system, both would

lose massively in terms of buffer
reads still to the index-only

scan or even an index scan on a
single multicolumn index.

Nikolay: Yeah.

Yeah.

Anyway, also, also what matters,
random page cost matters here,

right?

Because planner might, in reality,
index scan might still be

better or vice versa, but planner
might think it can choose a

bitmap scan in favor of index scan
because random page cost

is 4, for example, default.

Anyway, let's return...

Michael: While we're on the topic
of outdated things, I actually

think there's a relevant part of
the docs that I have an issue

with that I wanted your opinion
on.

It's in this area.

It says multi-column indexes should
be used sparingly.

In most situations, an index on
a single column is sufficient

and saves space and time.

Indexes with more than 3 columns
are unlikely to be helpful unless

the usage of the table is extremely
stylized. They're still

there in the latest versions and
to be fair to them, they've

existed pretty much since 7.2.

Nikolay: I understand.

In reality, it depends on the application.

In some applications, there are
absolutely critical queries which

must have index scan and sometimes
even index-only scan with

aggressively tuned to vacuum and
visibility maps maintained very

well so index-only scan doesn't
degrade.

So I think this is like, I can
see why this was written, but

I cannot agree with it in all cases
because definitely we have

3 column indexes sometimes because
we choose to have the most

efficient path specifically to
support specific queries, which

are most important for us.

They receive a lot of traffic,
so we cannot allow degradation

here.

Michael: Yeah.

What about 4 or 5 columns?

I guess it's rarer.

I don't see many, but I've seen
a few.

Nikolay: Well let's a little bit
postpone this question because

I know you wanted also to touch
covering indexes, right?

Because maybe this is where we will discuss this, but let me

return to your previous question.

Yeah.

How to choose the order.

And I remember the rule originally was, if it doesn't matter,

so there are 2 rules.

You need to spend time understanding them.

I'm pretty sure listeners who are most experienced, they know

this very well, but I know a lot of unexperienced listeners our

podcast receives.

So 2 rules, 1 rule, and this is maybe most popular in terms of

mentionings.

The column on, of the index on columns A, B makes a separate

index on columns A, B makes an index on column A, a single column

index, like not needed, you can drop it.

Right, because these 2 column index can support queries that

need only index on column A.

Right, and this means that if you have queries dealing with both

A and B, in terms of ordering and filtering, And also you have

queries that deal with only A, you should put A on the first

place.

This is rule number 1.

And there are nuances, of course.

And second rule is if you cannot choose, if you have patterns

that kind of… imagine you have only 1 query which involves both

columns.

How to choose the order?

And the rule says choose that column which will give you the

highest selectivity and put it to the first place.

So the scope of like your scope narrows down faster.

For example.

Michael: Yeah, I like it.

And I think for single query, and I think there's a, another

1 I've heard is your equality conditions in the leftmost.

And then if you have a range condition or an inequality condition,

like a greater than or less than, that needs to come last.

So you want all of your quality conditions first, then a range

condition like created_at.

Nikolay: So with previous 1, imagine

Michael: if- Well, but it's really important for, let's say you

have a query that has like where A equals 1, B equals 2, C equals

3, and then D is between 17 and a thousand and 1.

It's way more efficient to have the AB again, depending a little

bit on selectivity, I'm sure you can come up with examples where

it isn't true, but having a, b and c in some order and then d

last is really important to making sure that it's as like low

buffers as possible in terms of that index scan.

Nikolay: But imagine we have a query, forgetting about order

by limit, We have a query which selects with 2 filters.

1 is some boolean column and another is time range.

So what should go first?

Michael: Well, I, so think about it though, because like, let's

say the 50 50, that's the simplest case, right?

We've got 50% true, 50% false.

Probably if we have a different thing, It's trickier.

Nikolay: It should go, it should be partial index actually.

But yeah.

Michael: Maybe depending if we need to like query the opposite

1 sometimes.

Nikolay: Maybe it should be, maybe it should be 2 partial indexes.

Michael: Or I don't like, I don't think so.

I don't think 2 partial indexes are more efficient than…

Nikolay: But do you agree that filter on date, usually, if we,

for example, we know that we usually select narrow ranges like

day or maximum month, and we have a lot of data for many years.

Do you agree that the filter on timestamp gives you better selectivity

in general case?

Michael: I think it has to, right?

What do you mean?

Nikolay: Well, of course, if a Boolean column has some, like

it has 2 values and most of columns have 1 value, but you're

selecting another 1, it's a different story.

Where is deleted?

And we know we have most rows not soft deleted, right?

Or something like this.

In this case, the Boolean filter can give you better selectivity,

but it's less.

Michael: But I think a scan, let's say, let's think about like

the buffer reads, if you, if it's true and between these dates

or between these timestamps, our index scan can be perfectly

efficient if we are indexed by boolean first then timestamp because

we only have to look at the true values between a certain date

range.

If we mix that, if we do date range then boolean values, we discard

half the values we read.

We read all of the date range and have to discard half the Boolean

values.

Why should I get

Nikolay: all the date?

I don't get it.

Why?

Michael: We should test it.

I reckon there'll be double the buffer reads.

Nikolay: I forgot to mention my main rule, always doubt and test

in reality.

Michael: Based on my understanding, you'll get double the buffer

reads for putting the date first and the Boolean second.

All of the rules of thumb are good, but they're not perfect.

And actually they're slightly contradictory.

And I would say like even the most selective first, think about

the, the shared, multi-tenancy thing.

Yeah.

Yeah.

Yeah.

I like, I prefer account ID before user ID, even though user

ID is definitely more selective.

Because like partly because we're never really, well, in most

cases, data locality is 1 thing.

Yeah.

Data locality is 1 thing, but also we're very rarely serving

a single query with a single index.

Normally we're serving more than 1 query.

But then the rules start to become a little bit more of an art

form and you're trying to think what will they be in future?

What's most likely, what, what might we want to query together?

Like it's quite likely we want to query users in the same account

together at some point, maybe we want to look at all posts by

this account or this team And having that data closer together

in the index has benefits than if we do it by user ID and it's,

let's say it's, or no matter how you're doing user ID, whether

it's, maybe there'll be clustered teams.

Like maybe if you've got like a big int, maybe they'll be clustered

close together, but they, they won't all be.

You might add a team member many years after setting up your

account and that kind of thing.

So I prefer some breaking of those rules.

Nikolay: There is 1 more rule, Which is foundational.

Data locality, by the way, I would like to think about it.

And also partitioning, if we have it, of course we don't want

to scan all partitions, right?

And actually partitioning serves as kind of an index.

Michael: Kind of leading, like partition keys, kind of the leading

column or leading columns.

Nikolay: I remember I was trying to create an index on partitioning

key in each partition.

It was so strange because this partition had only 1 value.

And I was trying to include this as additional column and somebody

said, we have partitioning, why do we do this?

And I realized, oh, partitioning eliminates the need in additional

step in indexes or some indexes.

So what I was trying to say, additional rule is to remember the

order of execution of select clause.

Oh, not select clause, the whole query.

So first, like we should think, for example, simple, let's make

it, keep it simple.

Order by goes last.

Right.

So if we have order by created_at desk limit 25, it means created_at

should be the last step in the multi-column index.

Right?

Michael: Yeah.

I never thought about that.

But yeah, that fits with the range
thing I was talking about.

Nikolay: Yeah, but because of the
order of execution, if you

put created_at on the first place,
it won't be helpful.

It's a simple rule.

Intuition should tell you this,
but maybe not.

But I agree with you that rules
can be confusing a little bit,

and it's better to test and learn
and sometimes doubt and test

again.

And testing, we should pay attention
to buffers.

Michael: Yeah.

This, well, and this is going to
get better in Postgres 18, right?

People are going to get it by default.

Yeah, me neither.

Nikolay: We already have customers
migrating to 17.

So next year I expect buffers will
become default and I hope

you will see a growing number of
data with buffers.

I mean plans with buffers involved
because it's difficult.

Michael: Yeah, we definitely will.

Nikolay: Cool.

What about covering indexes?

Michael: Well, yeah, when I saw
the commit for adding include

to create index.

So create index added this option
to include certain columns

in the leaf pages, so in the tuples
that are stored in the leaf

pages so you can return more data.

Like if a query for example wants,
your order by like maybe wants

to filter by user ID equals this
and name equals this, order

by this day, the name could be
a payload, doesn't have to be

in the index ordering, but it could
be there in the index leaf

pages.

To achieve index-only scans.

Yeah, very, very important.

That's I think the only use case
for this, but it's a very important

1.

Important enough to get added.

Nikolay: The whole purpose is to
avoid heap fetches, but that

also works if under heavy load,
it works only if you tuned to

vacuum properly and avoid blockers
of xmin horizon.

Michael: Yes, yes.

Otherwise your index-only scans
will end up just regular.

Nikolay: Slowly degrade to index
scans basically.

Michael: Yeah.

So yeah, but covering index, Like
the word covering for me, I

think is slightly loaded because
I think a, an index for me is

covering for a query.

If you can serve it with an
index-only scan.

In my opinion, we had covering
indexes before this, before includes,

because if you had a multi-column
index on all of the columns

that you needed, that was a covering
index for that query.

Nikolay: My favorite
topic, criticizing the choice

of terminology.

I agree with you.

Michael: But I'm
only reiterating this because

I think it's important for people
to understand.

Like I think it could be confusing
if you hear that the covering

indexes are only for the word when
you use includes because it

is confusing.

So yeah, it got added in Postgres
11, 2018.

And I'm interested in kind of like
the, the benefits over like,

why not just add it as another
key?

What are the downsides to doing
that?

Nikolay: I spent
some time thinking about it and

I came to conclusion when they
were added in 2018, I spent some

time and I came to conclusion that
the key difference is the

semantics of uniqueness.

If you add additional columns to
your multi-column indexes, you

change uniqueness semantics, right?

Michael: That's a great point,
yeah.

Nikolay: If you want an index on
this can work well, but you

cannot, but this is unique index.

This is where you should have including,
But it's quite a rare

case actually.

Michael: So let's say we want an
index on email, back to last

week's episode or whenever it was.

We want it to be unique on email,
but we want to include the

name as a payload, but we want
to let people have the same name

as each other, even if they, But
wait, because it's unique on

email already, wouldn't it always
be unique on email and name?

Oh no, because then we, we could,
if they had a different name,

they could use the same email.

Yeah.

That's a problem.

Nikolay: So we don't want to have
uniqueness on pairs of values.

Yeah.

For example, If you want uniqueness
only on email and name it

something like this, you said payload.

This is good point of view on it.

You're just putting there as a
passenger.

Michael: Yeah, that makes perfect
sense.

Nikolay: Yeah.

I also think, do you remember,
is there any size difference?

Michael: Well, I would, yeah, I
was going to bring up the exact

same thing.

I wondered if it also got done
for that reason a little bit,

because if we've got the structure
of a B-tree, you've got the,

Once it gets sufficiently large,
you've got levels of kind of,

they're kind of like an org tree,
don't they?

Like layers of management almost.

And the, if you have a multi-column
index with keys, so where

they're part of the ordering, you
need to preserve that information

at the different levels, like for
ordering purposes, but because

includes columns aren't needed
for ordering, they're not relevant,

they only live in the leaf pages
at the bottom level of the like

B-tree.

So size-wise there should be a
difference, but, and I think this

has changed recently, de-duplication
I think changes this, not

for unique indexes, But in cases
where you can have duplicates,

which is quite a lot of indexes,
I actually think the deduplication

will normally beat the benefits
of not having to store it multiple

like storing the data at multiple
levels.

Obviously depending a little bit
on how big this data is, that

you're probably not putting huge
paid loads in this.

But yeah, I think there was a size
benefit.

I think it's less now that we've
got the B2D duplication work.

Nikolay: Yeah, obviously, in the
columns you're including part

of the create index, you cannot use
them in filtering and ordering.

They are like passengers, they
are only beneficial for

index-only scans.

But since they are sitting like
basically in leaves, do you remember

this?

There is a problem with B-tree on text.

There is a limitation due to key
size.

Right.

So you could not like 2000 something
bytes or something like

this.

I guess in this case for for columns
used in including there

is no such limitation.

Right.

I'm not

Michael: there must be a a limit.

Right.

Because it's still going in normal
data pages.

Nikolay: I'm not sure.

I'm not sure.

Let's not jump to...

Michael: Wait, yeah, but I read
this in documentation and I haven't

made note of it, but it is in the
docs, I'll find it.

Nikolay: But I must submit, this
is a rare beast to meet in the

wild.

I don't see you including the word
often.

I see, but not often at all.

Michael: Oh yeah, I was going to
ask.

So you, so generally you see people
with multi-column indexes,

but they're all keys.

Is that fair?

Nikolay: Yeah.

Yeah.

It's, well, this is what Minds
tells you.

Including is still kind of exotic.

It's good to have it, but not so
obvious when exactly you should

use it.

Well, we discussed it, but still
in normal practice you say, ah,

I will just add it.

If it's not unique index, why should
I think about it?

If there are no limitations in
key to be met, if it's just some

timestamp or number, it's easier
to edit.

So I wanted to touch 1 more topic
here.

When we should avoid completely
putting another column to multi-column

index or this type of indexes we
just discussed, which are called

the covering but you don't like
it.

When should we avoid adding such
columns, another column there?

For example, we see, oh, there
is obvious benefit.

Michael: So when you say benefit,
like benefit to a specific

set of read queries, what could
be faster?

Nikolay: Imagine we have some simple
case, like a table, some

filter with 4 thereby, and it's
already working well, but we

see there is also column status,
it has several values, and we

see most of the time we select
70% of everything, like status

equals active or something.

But we think, actually we could
shave off, we see some rows are

filtered out dynamically.

This is what pgMustard always suggests
immediately.

Oh, index works inefficiently because later some

rows you exclude, right?

Rows filtered or something like
that.

What is it called?

Michael: Well, yeah, there's a
couple of ways.

It can be either a filter or an
index recheck.

Some types of index and sometimes,
for example, with a bitmap

scan.

I was looking at this today actually,
because Postgres 18 changes

this a little bit, but exact heap
blocks and lossy heap blocks,

so that can end up with a recheck.

Nikolay: But I'm talking about
rows removed because we have

an additional part of the work,
work,

Michael: filter, yeah, filter

Nikolay: and they are filtered out
dynamically and we think,

oh, it's not efficient.

Let's go fully efficient.

And we, we start adding, for example,
status to like as additional

column in our multi column index,
or it was a single column,

but it becomes 2 columns, right?

Or we decide to use it as a condition
and make index partial.

So we say where status equals active
because we know most of

the time it's active or something
like this.

It doesn't matter.

So index starts using this column
somehow.

Any of 3 ways, another column, including or where?

Keywords.

Michael: I know what you're going to talk about.

Are we going into HOT update territory?

Nikolay: Exactly.

Exactly.

So this is what happened to me.

I was like enthusiastic optimizing some project.

It was, I think, I remember first time it was very long ago.

It was DocSend.

Later it was acquired by Dropbox.

It was in San Francisco.

And I was like, oh, we optimized additionally, like shaved off

like 20-30% of buffer hits and reads and it's great.

It's small optimization, but it's helpful.

And we can pay this price, okay, we need to maintain this index

anyway, it's slightly bigger now, but it's still worth it.

And then later I see degradation of updates.

And you know why, right?

Because we had HOT updates, because basically most of updates,

they updated status.

Yeah.

And now it's participating in the index definition, so HOT updates,

it makes HOT updates not possible at all.

And HOT updates, just a reminder, we know there is a new audience

usually, So heap only tuple updates.

This is update which changes only table pages, pages of table

data and doesn't touch indexes.

Because normally regular update, it needs to touch every single

index, which produces more dirty pages, Checkpointer or budget

writer or even backend, they need to flush it to disk, first

of all, it doesn't matter.

And then it generates more WAL and it slows down updates.

Regular updates are much slower than HOT updates.

So just putting another column to your multi-column index might

affect updates.

This is worth remembering.

Michael: If it wasn't indexed already.

Like if it was indexed already in some other way.

Fair enough.

It's a really good point And I think those index efficiency tips

are the ones, like, I think people are most surprised by, like

people think they look through their query.

They see, I'm talking about like experienced engineers, but not

experienced Postgres folk look through a query plan, see index

scan, index scan, index scan, maybe there's a lot of loops.

Maybe there is a rows removed by filter, but maybe it only says

1, but it's like, 20, 000 loops and they don't realize that that's

the most inefficient part of their query.

Like just, anyway, so I love those, but normally when it's like

index efficiency is we talk about
a percentage, like 0.1%, like

that, or less than 0.1%.

Great.

If it's 70%, we score it much less.

Well,

Nikolay: It's a combination of
factors, this choice.

Yeah.

But 1 particular case which is
very common and when I see it

I immediately recognize the problem.

If I see SELECT blah blah blah
where some filters and then order

by created_at desk limit 100.

Definitely there are multiple indexes
which include updated_at

and there is a trigger which or
just some Ruby or Django doesn't

doesn't matter application which
updates updated_at always.

So this table doesn't see what
updates 100%.

And yeah and how to break that
issue not losing the the mechanics

of updated_at timestamp, I don't
know.

I try to sometimes to avoid indexing
this.

If you present to your users values,
really like you need order

by updated_at desk, limit something.

You need it, right?

I try to say, okay, updated_at
is very close to created_at usually

let's use created_at And then like
somehow like it doesn't work

well.

Michael: So it doesn't, I would
have thought in most cases it

would actually, but yeah.

Nikolay: Depends of course.

It depends.

Yeah.

Sometimes it's good.

Like you basically already have
almost everything and on the

fly.

Yeah.

Sometimes there is correlations,
very good correlation.

But sometimes if it's on partition
table, updates happening all

the time and it's a mess.

And updates might happen a month
later, for example.

This is like, screw this.

Michael: I will, for newer listeners,
I will link up our episodes

on HOT updates and we did 1 on
limit that's really good for this.

Nikolay: Yeah, yeah, yeah.

I just wanted to say this is a
warning.

Michael: Yeah, yeah.

Nikolay: We have multi-column releases,
Everyone needs them,

100%.

But we should be careful if we
need fast updates and fewer WAL

bytes put to WAL.

Because WAL bytes can be an issue.

And they put pressure on both backups
and replication systems.

Michael: Yes, and this is the old
read versus write thing, isn't

it?

And also, like if you've got a
write-heavy application or if

you're not doing many reads at
all, you might not want many indexes

at all.

Like It is a trade-off in general,
not just multi-column, right?

Nikolay: Sometimes, you know, sometimes
here it makes sense to

split and have one-on-one relationship,
2 tables, and in 1 you

update a lot And you give up, but
you have only like 3 or 2 columns

and that's it.

Right.

And another 1 has everything else
and search and so on.

But in this case, you will end
up with the problem that we cannot

create an index on 2 tables.

We lose for some queries, we will
lose in a single index scan.

We will need to join.

Yeah.

So it's

Michael: back to denormalization
topic again.

Yeah.

Nikolay: Yeah.

Yeah.

Michael: I had 1 more on include.

I wondered if it might end up with
less bloat in some workloads.

So when we're updating that column
that's been included as a

payload for example, the tuple
doesn't need to move in the index.

So in the case where we've got
them both as key columns, We're

having to move the tuple to a different
page most likely.

If it's like ordered by email,
then name, for example, if the

name changes, probably the entry
will move page and you could

end up with a split.

So I was just thinking in terms
of updates, I wonder if we'd

end up with less index bloat if
we used include instead of multiple

keys.

It seems like a minor advantage
but I wondered if that could

help as well.

Nikolay: Yeah it's an interesting
point I should think about

it.

Michael: Yeah but yeah it feels
to me like a Include feels like

a scalpel, like a very specific
tool that is helping.

Nikolay: Including, yeah, the word
is including.

Are you sure?

No, I'm not sure.

Include?

Yeah, it's includes.

It was a bug in my mind sorry yeah
include

Michael: it's not even includes
ploys include singular

Nikolay: yes yeah

Michael: yeah but that shows how
common like shows how commonly

you see it.

Nikolay: Yeah.

Super rare.

Michael: Including would probably make more sense, but it's just

include, yeah.

Nikolay: So, okay.

Michael: Good factor naming.

I think we're done.

Nikolay: Yeah, it was a bag of something, some pieces of advice.

Michael: And I think we'll come back to some of the other index

types at some point.

By the way, I found it just as like an interesting tidbit GIN

and BRIN because of their structures doesn't actually matter

about the ordering of the columns which I found fascinating.

Nikolay: Say that again?

Michael: GIN indexes and BRIN indexes, the order of the columns

doesn't matter.

Nikolay: Yeah.

Interesting.

Okay.

Michael: I mean mostly we're worried about B-tree so it's not

too important.

Nikolay: Yeah, 90 plus percent.

Michael: Yeah.

Alright, nice 1 Niccolo.

Nikolay: Thank you so much.

Michael: Catch you next week.

Nikolay: Bye-bye.