Skip to content
PodSubs
ShowsPeopleGuestsJobsNewsEvents
More
BlogPodCred
Menu
ShowsPeopleGuestsJobsNewsEventsBlogPodCredNetworksSign in
Join nowSign in
  • Shows
  • People
  • Jobs
  • News
  • More
    • Guests
    • Events
    • Blog
    • PodCred
    • Networks
    Join nowSign in

Postgres FM

Postgres FM artwork

Multi-column indexes

Aug 22, 202541 minAudio
ShareXLinkedInFacebookWhatsAppEmail

Nik and Michael discuss multi-column indexes in Postgres - what they are, how to think about them, and some guidance around using them effectively. Here are some links to things they mentioned: Multicolumn Indexes (docs) https://www.postgresql.org/docs/current/indexes-multicolumn.html Our episode on Index-only scans https://postgres.fm/episodes/index-only-scans Combining Multiple Indexes (docs) https://www.postgresql.org/docs/current/indexes-bitmap-scans.html Enable BUFFERS with EXPLAIN ANALYZE by default https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c2a4078ebad71999dd451ae7d4358be3c9290b07 “PostgreSQL includes an implementation of the standard btree […] The only limitation is that an index entry cannot exceed approximately one-third of a page” https://www.postgresql.org/docs/current/btree.html Our episode on HOT updates https://postgres.fm/episodes/hot-updates Our episode on LIMIT vs Performance https://postgres.fm/episodes/limit-vs-performance ~~~ What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc! ~~~ Postgres FM is produced by: Michael Christofides, founder of pgMustard Nikolay Samokhvalov, founder of Postgres.ai With credit to: Jessie Draws for the elephant artwork

Audio and video play straight from the show's own hosting. PodSubs stores and serves no media.

Transcript

From the show. Tap a time to play from there.

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.

Open the transcript file

Hosts and Guests

Hosts

  • Michael Christofides Host Feed-stated
  • Nikolay Samokhvalov Host Feed-stated

Credit the people on this episode

Be the first to rate this episode

More episodes

All episodes
  1. Postgres 19

    Nov 19, 202653 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://youtu.be/VcDU-NRE3hI Michael, Nik, and Kirk discuss Postgres 19: why the release came in November and not in...

    Credit the people on this episode

  2. The hidden costs of CREATE TEMP TABLE

    Nov 13, 202639 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gAdhpYewLZ8 Nik and Kirk discuss the hidden costs of CREATE TEMP TABLE: dead tuples and...

    Credit the people on this episode

  3. Extensions on demand

    Nov 6, 20261 hr 9 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=7oeAAqU00zk Nik and Kirk are joined by Álvaro Hernández to discuss how Postgres in...

    Credit the people on this episode

  4. UUIDv4 vs UUIDv7

    Oct 30, 202648 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gf8e3JkQ_8M Nik is joined by Kirk Wolak to discuss what a random UUIDv4 primary key really...

    Credit the people on this episode

  5. Dump/restore done. Now what?

    Oct 23, 202651 minAudio

    Nik is joined by Kirk Wolak, Software Architect at KiraSoft, to discuss what a database still needs after pg_restore finishes and before it takes production traffic - what pg_dump and pg_restore leave behind, why...

    Credit the people on this episode