Nik [00:00:13]: Hello, this is Postgres.FM. I'm Nik. And again, Kirk joined me. Hi, Kirk.

Kirk [00:00:20]: Hey, Nik.

Nik [00:00:21]: And today we have a topic which popped out, last week again, and this is not new topic, we have some connection with Kirk to this topic. Let's admit, right?

Kirk [00:00:34]: And for everyone wondering, the, in the show notes, there's gonna be a link to all of the details. Well, probably more than you want.

Nik [00:00:41]: This particular topic, UUID version 7, you played some role in it, In Postgres.

Kirk [00:00:48]: Yeah. So absolutely. So with this topic, UUIDv4s, this was one of my first introductions to Nik and Andrey. We looked into ULID under hacking and how much faster it was than UUIDv4. And this actually led us to, under a hacking session, creating, the UUIDv7 patch, which Andrey, you know, carried through to the end. And I learned quickly how hard it is to get something published, the number of rebases, waiting for, you know, finalization of RFCs and things like that.

Nik [00:01:23]: Exactly, yes.

Kirk [00:01:24]: This was crazy for me. (Nik: Yeah.) So anyway, so—

Nik [00:01:27]: Three years it took, I think, or two, two maybe years,

Kirk [00:01:31]: Two. Two. We just missed one version and got into the next.

Nik [00:01:34]: Yeah. It should've been in Postgres 17, but RFC was not fully finalized. And I remember Peter Eisentraut said no. Like, it makes sense.

Kirk [00:01:44]: We understand

Nik [00:01:44]: So however, however, I remember everyone, like Node, Python, Google libraries, like all of them already had... RFC was almost final, but Postgres has, I think, higher bar in terms of what should be, like decision, how they should be made because it's really hard to change them later. So Postgres', consensus was let's wait and it was split, right? So some functions, went to 17, but final— it was finalized in 18. Anyway, as we always say, it was possible to use, UUID version 7 even before, because the data type is the same. It's just UUID, right? It's like just slightly different, in terms of content, but not the format.

Kirk [00:02:33]: So back to the problem at hand. Why is UUIDv7 faster than v4? V4 is absolutely

Nik [00:02:40]: And is it faster?

Kirk [00:02:43]: And... Oh, wow, and is it faster becomes one of the questions.

Nik [00:02:45]: How come faster? Yeah. Okay.

Kirk [00:02:47]: Why, why is it, why is it faster—

Nik [00:02:49]: What does it mean?

Kirk [00:02:49]: Becomes the question. But the concept behind v7, UUIDv7s, is that it has a date time at the prefix, so it tends to sort to the right in an index. Just like an incrementing ID, just like any other date time field, it's gonna sort to the right, and most systems are optimized to handle that, right? This is kind of well-known stuff. So, and we knew that, and we talked about that in our hacker session. Testing it and making very sure it worked, we did rough jobs at that. But now I'm concerned, Nik. Like, what is it that drove you, besides having this great forge tool, but what is it that drove you to say, "Hey, I'd like to waste five days of, you know-"

Nik [00:03:33]: It's just three days, not five. Three days.

Kirk [00:03:35]: Okay, three days— (Nik: Yeah.) of 24 by 7 with my, with my AI forge, (Nik: Right. Right.) pounding on this.

Nik [00:03:43]: Right. It's called forge, the build up forge, and yes. So we had, again, a case. We saw it quite often, customers use UUIDs in tables And we just like always, like if you just look at the schema, you see UUID data type, and you don't know is it like version 7 or 4? But if you look at the definition, how it's inserted by like default, like you see the function. And obviously, if you see the old function... not UUID version 7 function Andrey created.

So you understand that. And for example, it was interesting. It was, like just we specifically discussed, that database is growing fast. A lot of I/O is concerned, right? (Kirk: Mm-hmm.) And how to properly partition it as well, like, and this is what... This for me was number like 550 time I discussed this topic with some customer, right? I like this is super common these days. I think, also with AI development, it's super common to have UUID used instead of ID, and like if you think in the future you probably will have some distributed, maybe sharded system and so on, definitely you don't want a regular big serial, data type is used.

So, since it was... Since I/O was a central topic, I said, "Guys, just like, let's consider UUID version 7." And I realized that I keep talking about this, but we don't have good data proving what's happening, We have, and our materials, have a section prior art, overviewing all the other people work and for example, Tomas Vondra. But it's three years old, like it's about Postgres 16 and it exploring specific area.

And I was thinking we should just point our forge system to this topic to study it under multiple angles. And with forge... As I mentioned already in previous episodes, forge is our generation three of benchmarking tool. I specifically like, you know, these, these, these books, and our YouTube listeners can watch them. And Brendan Gregg, right? Benchmarking is still hard. Even with AI and forge is using a lot of AI, it's hard.

But first version of like predecessors—

Kirk [00:06:18]: Well, hold on, hold on.

Nik [00:06:19]: What?

Kirk [00:06:20]: Let's keep the conversation a little bit higher first. All right. So you had a client, and you're talking to the client. You're realizing, hey, not... First off, this isn't common knowledge. Not everyone understands that not all UUIDs are built the same. Second, you wanted from an integrity standpoint to be able to say UUID is much faster than UUIDv4. Version 7's much faster than version 4. At the same time, I know you, Nik, you wanted to be able to explain kind of exactly why it's faster, right? And now as you're looking at this, you're like, "Okay, we got the prior art. We do know it's faster, but we've never drilled into this." (Nik: Right.)

Like, and now I have tools that can do it. (Nik: Right.) And this is part of training those tools— (Nik: Right.) and practicing with these tools. And part of the reason it took three days is because you were practicing. We didn't get everything right as you told them what to do the first time.

Nik [00:07:18]: Yeah. That's... And I thank you for, You'll just elaborate at why benchmarking is hard. And ideally what we usually do for specific case, we take this data, this workload, like observing production, and just reproduce it and see, and we can show exactly what will happen if you change data type and how it will be beneficial for in terms of I/O, for example, right? (Kirk: Yep.) But also, like it makes sense to study it in... with purely synthetic data because it, it's interesting itself to look at benchmarks which are, repeatable.

And it also can be material for, You can reproduce the same benchmark with your own data and your own workload, and this is the whole idea of our approach. Like, as I said, like, this is generation three of benchmarking system. The first one was created in 2018. I, as I said, like just eight years ago. So yeah, the second one... And it was bunch of shell scripts.

It was like— (Kirk: Right.) right. However, already quite interesting because we started accumulating knowledge, what we should do every time we do a benchmark. For example, we should—

Kirk [00:08:33]: When did Flame Graphs make it in?

Nik [00:08:35]: Well, Flame Graphs—

Kirk [00:08:35]: For example

Nik [00:08:36]: Came there as well. Already there we had Flame Graphs.

Kirk [00:08:38]: That the version, first one? (Nik: Yeah.) First version, Flame Graphs. Awesome.

Nik [00:08:41]: Yeah. The, it was called Nancy Bot. Actually, I was think... I was envisioning it already like a bot. (Kirk: Okay.) yeah. And Flame Graphs were— (Kirk: Yeah.) was one of the tools, like which is useful to compare things, but complexity is high because you're thinking about some bottlenecks, but maybe you measure, and if you don't explore all the details, you probably... You might miss some different bottleneck. And,

Kirk [00:09:12]: By the way, this is gonna mean higher level. Don't go so deep. (Nik: Okay.) Like—

Nik [00:09:17]: Okay. Right. So when version two came out, when we had, We became a Google AI startup, and we started playing with LLMs, but it was terrible in terms of hallucination. So version three with current models is... It's exciting. Like it's super powerful and with all the methods we have, all the toolings we have accumulated during all those years, and models are smarter, and the process of reviewing all the details, it's amazing, but still it's hard Right? Because, for example, one of the first results this time, couple of days— (Kirk: Mm-hmm.) was inserts of integer eight primary keys are faster, right?

Kirk [00:10:04]: Hmm. Okay.

Nik [00:10:07]: Faster than, UUID version 8.

Kirk [00:10:10]: Version 7.

Nik [00:10:12]: Seven, seven, right. Integer eight, 11. It's, seven, eight.

Kirk [00:10:15]: Yeah, version seven of UUID, yeah.

Nik [00:10:17]: Yeah, so we have, UUID version 4, which is randomized, and you can see in the value, you can see, I think, the third component starts with digit 4, right? After a hyphen. Version 7 looks exactly like that, but it's digit 7 at the third component. And integer 8 is just integer 8. It's a 64-bit integer, It looks like—

Kirk [00:10:44]: So when you said component, you kinda meant chunk, if— (Nik: Yeah.) chunk them by the dashes. (Nik: Right.) Perfect. (Nik: Right.) Yeah.

Nik [00:10:50]: So the first, one of the first results, Forge came, came to me with, like, after first few waves. By the way, I think we had 10 waves of benchmarks. (Kirk: Mm-hmm.) Every time we provisioned multiple VMs and did all the things. On each run we collect 80 artifacts, all the logs, all pages, da, da, da, five, everything. So it said, "You know what? Like, integer 8 is winning, and it's faster in terms of inserts to, like, primary keys than UUID version 7."

Maybe it's so, like, it's smaller, right? It's 8 bytes versus 16 bytes in any of UUID values. But then what, what's most interesting, I couldn't... Like, I wouldn't catch it without spending a lot of effort. And fortunately, due to the how system works right now with all the reviews, we use multiple models, like, it's crazy already. Quite sophisticated. It refuted the findings itself, saying, "You know what? I made a mistake." And we have micro benchmarks, including FIO, so we check disks. And it said, "I did the wrong thing, like this time. I checked apples versus... apple versus orange," right?

Integer 8 experiments were on one VM, UUID version 7 on another VM, and they are not equal. Despite spec, the spec is equal, but the cloud gave us different hardware in terms of, like, real physic. And micro benchmarks with FIO, F-I-O, caught this because disks on integer 8 machine were, much slow— much faster, right? (Kirk: Right.) And that's like, that's interesting, like, because actually our methodologies always include, like, combine on the same machine. Yes, it's sequential. If you want to move faster, you probably want to spin out multiple machines and do different experiments on different machines, but there's always this risk. And micro benchmarks, they catch this, like different hardware.

So making experiment sequential, waiting longer on the same machine, It's, like, it eliminates this risk. Or you need to be very, like, careful with micro benchmarks and compare them all the time and see that you have, like, equivalent hardware, right? So anyway—

Kirk [00:13:19]: So back to... Yeah, so I wanna raise the level back up a bit 'cause you drilled into your DB Forge. We started by talking about this recent test, and we want to cover the DB the Forge process that you're using with AI Lab Forge, right? How do I say it correctly? What's the trademark name?

Nik [00:13:36]: DBLab Forge. DBLab Forge.

Kirk [00:13:37]: DBLab Forge. Perfect. (Nik: Yeah.) So we have the DBLab Forge, and you just got into some of the details about what makes telling it how to do things hard, but also that it noticed its own mistake. Cool. (Nik: Yeah.) That's an excellent point. (Nik: Yeah. Yeah.) I wanna have the highlighter over that in a podcast format, right? (Nik: Yeah.) So I wanted to hit that. Now we're, we're popping back over. So now you're testing your own harnesses out, you're pointing it at a client-related communications problem, right? Not necessarily a tech problem. Now you wanna get the numbers out. And so then you ran it. So obviously the int8 being faster kinda surprised you, turned out to be a false alarm.

What else surprised you, Nik, in the results?

Nik [00:14:23]: Well—

Kirk [00:14:23]: So now it collected a bunch of results. What surprised you?

Nik [00:14:26]: I don't think, like, I was super surprised. I saw what, roughly what I expected. I... What surprised me that it caught itself, like, on this mistake and corrected itself, and I just observed it. This I like a lot, So—

Kirk [00:14:45]: I was I'll be honest— (Nik: Yeah.) I was surprised, I was surprised that there's a threshold.

Nik [00:14:50]: Yeah. Well, yeah.

Kirk [00:14:50]: And once I saw the threshold— (Nik: Yeah.) it makes sense to warn people. You test this in your own little environment, not thinking twice about growth. And if you're a startup, you can make a mistake I've seen many startups make. And it's like, oh, this is what... UUIDv4, UDID... UUIDv7 are about the same. And you turn around and find out that the... And this came out of the results, and it surprised me. Once UUIDv4 is running for a while, and the index size while it's growing, once it just consumes all the memory, it kicks the table out of memory.

Nik [00:15:24]: Right. Evicted, yeah.

Kirk [00:15:25]: And then all of a sudden it starts writing to disk, and its performance is no longer as the same as when it was all in memory. (Nik: Yeah.) Now, that makes sense every—

Nik [00:15:33]: That's a, that's a good point. Yeah, I agree with you. This is a super interesting first thing, first finding, right? So, if you watch us on YouTube or listen, if we're still in your pocket, just check out the graph which, pops out, pops out on the screen right now. And regular podcast listeners, follow the link, so you will see all the graphs there this is super interesting result. Once your database doesn't fit memory, and like not only shared buffers, but also page cache, when it's not enough, and pages started to be evicted, the interesting thing is that UUID version 4 insert rate, when we check at maximum speed, like we try to saturate everything and go as fast as possible, with pgbench's default behavior, right?

Like, I call it, stress testing, basically. It's kind of load testing— (Kirk: Yeah.) when we explore the limits. So we see that the maximum insert rate drops like 30, 40%, almost twice for UUID version 4, and it doesn't drop for UUID version, 7 or integer 8. They keep inserting to the end of the, to the right side of the index, and they don't need this eviction to happen. They don't... Like, they are resistant to the fact that your database overgrew or, like working set overgrew the, you know, the database size, So it means for... I think if your database like, for example, 10 terabytes, but you have like, say 64 gigabytes of memory, it doesn't mean that it'll, it, this will happen with your table.

If table is tiny, probably your whole, your B-tree is still in memory, But if it, if your B-tree is already large... And also, like Postgres needs memory for other tables, right? We have other tables in database usually. It's like... So once it's quite large enough, for example, you have a one terabyte table and index size is also like hundreds of gigabytes or something, there's a big chance that UUID version 4 will suffer in terms of insert rates. It will suffer from this eviction, and you will see a lot of additional I/O.

While if you switch to integer 8 or inter— UUID version 7, there is no such effect at all.

Kirk [00:17:52]: Good. So visually, for those driving who you can't look at this shitty graph—

Nik [00:17:55]: Or walking the dog. Walking the dog.

Kirk [00:17:57]: Or, or walking the dog, yeah, or running. For you guys, again, here's the difference. The difference is UUIDv4 is random, which means the page it's gonna insert is into is random. So you start at the top of the B-tree, and you go left or right randomly. So now what happens is the B-tree doesn't fit in memory anymore. You're doing page flushes and loading from disk to figure out how to follow through. Now, the top section of the tree likely loads, but again, you're doing inserts. What does that mean? You're rebalancing pages in the middle that have already been balanced before, and then those things generate I/O. Then you insert, and now you're inserting back way to the left or in the middle.

And so you need to touch such a random number of pages that you're actually causing a bunch more backwards I/O. UUID version 7, because it moves to the right, every one you generate is further to the right than the last one, what happens? It only touches the last page, the last one. And then when you add a page, you're splitting off of that last one, and you're never going backwards. That's the same as the auto-increment IDs operate. (Nik: Right.) It's the same concept. So that's important. So if you understand that visual, you understand this is it. But Nik, I wonder, is this all of the slowness, or is this about half of the slowness?

Nik [00:19:25]: Well, it's a good question. So first of all, I want, I wanted to say, inserts need to get the page to load it to memory, like reading, right? To make it dirty—

Kirk [00:19:38]: Dirty

Nik [00:19:39]: To change it, right? And yeah, so in the, with UUID version 7, like the same thing as with integer 8, we don't need to touch old rows, but it doesn't mean they're not needed sometimes. Like yeah, they got evicted from memory, kind of like archive data, but maybe some selects will need them anyway. Yeah, we don't know, But at least from constant inserts, we don't touch that, and effect is obvious, right? (Kirk: Yep.) And it's, once again, like to look at these graphs, basically the rates of inserts are same, like for integer 8 and UUID version 7 are like basically the same.

That's great result, right? So you like... And they don't degrade. They don't degrade, when we have really large table and index, so it's stable. But also, you are right—

Kirk [00:20:36]: But that means version 4 degrades.

Nik [00:20:39]: Yeah, yeah. You are right in terms of like what else is happening. If you have full page writes enabled, and you should unless it's Aurora or, or you're running Postgres on ZFS, it means that after each checkpoint, every time we touch a page, whole page is compressed and goes to WAL, So page is 8 kibibytes, kilobytes, kibibytes, And your insert might need like only like 24 bytes tuple header, and then how many bytes? Maybe this is a narrow table, like only like two, three columns. (Kirk: Yeah.)

Right? But you need to touch the whole eight-kilobyte page, compress and make it dirty and write. And with UUID version 4, it means like we, as you said, we are randomly inserting to different parts of B-tree, so we need to make those pages dirty, and after checkpoint they need to fully go to WAL, which means that WAL is becoming bigger than it could be. In UUID version 7, exactly like, as with integer 8, we make multiple inserts on the same page, and between two checkpoints we have a chance— To make it dirty just once, but with many, many inserts in the same page, And then it goes to WAL just once compressed, full page write, which is much more efficient.

Right. And back to UUID version 4, it goes to WAL, and next checkpoint cycle after checkpoint, if we touch the same page again with another insert, it we make,

Kirk [00:22:20]: Write it again

Nik [00:22:21]: We need to write it again, right? So my point is and it was measured, I don't remember exact numbers. It's hard to say like, oh, like, with UUID version 4, you write, like, 10% more WAL. It's hard to say because it will be purely synthetic, and in real systems, a lot of things are writing to WAL, including autovacuum, right? So but definitely with UUID version 4, you somewhat, like, it's somewhat bigger in terms of WAL writes.

Kirk [00:22:54]: And again, for the visual, for the listener, one system is modifying only pages to the right, and checkpoints come on a regular interval, and those are the pages that get written. Both the pages written to the table, but also you're appending to a table. So you're pushing them out further to the right. You're appending effectively to the size of the B-tree file. You're pushing that out to the right. So the amount of WAL you're writing is pretty minimal. But back to the only the index side, because it's the same for the table, but only the index side for version 4, UUIDv4, that index, the more random the number generator is, the more random the leaf you're ending up on.

And that's the selling point of these things is that they're random. So the thing is the number that you touch is almost proportional to the ratio of index leaf pages and the number of pages you've actually inserted. Now, that's my hypothesis. We didn't test that, right? (Nik: Okay.) But that should hold up based on randomness. And so the challenge is when the checkpoint comes. So it's not just the number of rows you write, it's the number of rows and the number of leaf pages they fit into, and the frequency of checkpoints. (Nik: Mm-hmm.)

Those three things are working against you, and it makes up, I understand, about half of the loss of performance.

Nik [00:24:17]: Yeah. So a few days ago when we still had, iterations for Forge, running, remember we discussed that you mentioned, I mentioned Aurora, and you also mentioned that I don't remember exact question, but you pointed to some ideas that Aurora doesn't have checkpoints, (Kirk: Right.) maybe there is no such, like, benefit of having UUID version 7 on Aurora because it doesn't have, like, how WAL is organized, right? Like, if there are no... full page write is off on Aurora. (Kirk: Right.) So there's no this effect of touching the whole page and the need to compress it. By the way, for non-Aurora, we should have compression on, LZ4, Zstd. This is probably another Forge experiment, right?

But we compress it, and Aurora, we, like, on Aurora, this is not needed. But... and I was like, "Oh, yeah," like, unfortunately, yeah, because I like Aurora because they charge you for I/O, and your work on performance, on query performance optimization is exactly, like, can be measured in dollars. This is good. So I asked Forge to the next round to explore that. Well, not with Aurora, just, like, understanding what to expect, and we have numbers. The numbers— (Kirk: Mm.) impressive, So numbers are impressive. It was measured for some small setup that WAL doesn't matter here. What matters is if your... as we said, if index is large enough so it doesn't fit, it, like eviction, which this database is experiencing, eviction of index and the heap, pages from memory happening all the time, thrashing, If it affects this particular index, UUID version 4 will cost you 10, roughly 10 times more than UUID version 7 in terms of I/O costs on Aurora.

It's not a measurement, it's prediction. We can actually... we could explore it, on Aurora particularly, but this tool is, like, we use it both for synthetic benchmarks like this and discuss it publicly, but also we use it for our customers. There are some customers which already receive some interesting reports for their particular case. So my advice here is either, like, come to us, we will help you to explore things in depth, or just use AI and go and experiment, right? What will happen with my particular table, and what would be I/O cost?

What I/O cost am I paying for staying on UUID version 4, right? This is interesting question. An experiment is not super difficult to organize. I would just... On Aurora you have, thin clones as well, by the way. You can provision thin clone, you can then clone—

Kirk [00:27:20]: Ah.

Nik [00:27:20]: I would clone this, like, production data locally there to two new tables to make sure we don't have bloat, because bloat will affect both of these things. We can discuss it separately. I would just to measure, like, strictly unbloated table and index, for UUID version 4 versus version 7, and then I just insert, maybe I do some reads according to my workload. With AI agents, it's quite easy to organize. It's not easy to remember all the details, but this exercise is quite easy to organize. And I would just measure it and see how much additional I/O UUID version 4 costs me and how it translates in terms of my budget, like my spending, right?

This is... Yeah.

Kirk [00:28:05]: And to be clear, when we first did the ULID and the UUIDv7 work, one of the, one of the people, who ran into this problem, it was a cost issue with Amazon, and that's what caused them to look into the problem—

Nik [00:28:22]: So it was Aurora

Kirk [00:28:23]: And to see that the ran... It was Aurora. Yeah. So they were using Aurora, and it was a massive increase in part because they just crossed that threshold where they were now generating a ton of IO and way more than they realized, and this— (Nik: Yeah.) not only was it costing, it was bottlenecking. (Nik: Yeah.) Because they actually—

Nik [00:28:42]: Yeah, yeah. So in sorts, like they... There is degradation at it, in sorts rate, but also in some systems, you start paying for additional IO. Yeah. So should we say that this is a kind of performance cliff? It's not like acute cliff, but you know, like in real world, not all cliffs are like this. Sometimes it's like, you know, we have one here on La Jolla. I, I'm scared when I see it, but some surfers go down with a surfboard like almost like... Yeah. So it's looks like a cliff, but you can descend, right? Looking at those graphs—

Kirk [00:29:21]: Kind of like— (Nik: Yeah.) kind of like a performance hill. But let's be honest.

Nik [00:29:26]: Hill, yeah.

Kirk [00:29:26]: When, when do we care? And I'm a hot sauce, like, advocate from years ago, and their answer was always the right answer late is the wrong answer. And I kind of like that. And they said if speed's important to— (Nik: Yeah.) timing must be built into your system. (Nik: Mm.) So part of my answer comes from when it impacts performance, right? It's a problem. And when it impacts user perception, it's a serious problem, right? (Nik: Mm.) So those are where, you know, it doesn't have to be a cliff where you fall off and you can't do anything, but it is a cliff if nobody can do their job in a reasonable amount of time who used to be able to do it before you hit that cliff. (Nik: Yeah.)

It's still a cliff.

Nik [00:30:08]: Yeah. And how to understand, actually, I'm wondering right now in real time, like not being prepared. Maybe it's a good question for additional forge rounds. I'm wondering how could you understand are you past that point or you're still before for this particular in B3 index on UUID version 4? How to understand.

Kirk [00:30:29]: Hmm.

Nik [00:30:30]: Like, are you already like falling from that hill? (Kirk: Right.) Or you approaching this moment? And this is interesting— (Kirk: Right.) right? Like, let's maybe think. I saw like as usual, we have a set of recommendations at the end. (Kirk: Yep.) One, one set is for current versions and DBREs or some tool— (Kirk: Yeah.) tool builders, and another set is for future, like what we could what could we improve in Postgres? I noticed in the second area, forge recommended to consider, eviction stats in PG PG... There is pg_stat_io. We have— (Kirk: Mm-hmm.) I think three years already, three versions of Postgres.

But it's impossible to see numbers by relation in there. (Kirk: Mm.) At the same time, there is pg_stat, all tables, pg_stat_io all tables.

Kirk [00:31:25]: Ah

Nik [00:31:25]: But those guys don't have, eviction data as well. So how many pages written by backends? It's maybe one of the keys, things. Yeah. I, I'm just thinking like something—

Kirk [00:31:43]: No, I get it. Here's my answer. I'm incredibly pragmatic with this stuff. There's approximately zero cost for reusing UUIDv7s, okay? At least from my perspective. There are some drawbacks. I'll ask you about those. But considering that, just switch to UUIDv7s. No one has to know. Now, (Nik: Yeah.) you might have a slightly different opinion. (Nik: Yeah.) So what are some of the pushback ideas for somebody complaining about why they don't want to use UUIDv7?

Nik [00:32:15]: Yeah, yeah, yeah. So well, obviously one, which is discussed widely, very widely, including I remember some discussion on Hacker News as well, is that it, has... I think I think actually next day it always amuses me how some topic pops up and starts growing in mult— in multiple directions. So we had a call with customer. I promised him, them actually to bring this data in synthetic form. But next day, I was reading Hacker News, and this discussed there as well, I mean, UUID version 7. It was an article title was something like UUID version 7 is a weird thing.

It's kind of a weird thing. Why it's weird? Well, yeah, some like the order is not guaranteed. Yeah. Like if you are within like millisecond, yes, some... Yeah, order is not guaranteed. It's almost, almost there. But definitely one of the biggest points people keep in mind is that your value, primary key value starts revealing timestamp. It might be security concern in some cases, right?

Kirk [00:33:30]: Yeah, 'cause it's the date and time of when it transpired. (Nik: Yeah.) So the challenge is if that can help you, for example, identify, you know, somebody or something. You know, imagine it's a reporting system by the government for somebody who got very sick, right? And this report came in from the space station, and it came in because of that at a very odd time. Right? All of a sudden you start knowing based on this time,

Nik [00:34:03]: Based on the ID

Kirk [00:34:03]: Might have come from here.

Nik [00:34:04]: Based on the ID.

Kirk [00:34:05]: Right, right. Yeah. (Nik: Yeah.) Based on the ID, you interpret the time, and you're just like, "Oh." You're like all of a sudden it's like if this was a normal time for a normal patient, we can now kind of tell what part of the world they're likely to be in, right? And all of this. So yes, I get how that, you know, how that could be, you know, looked at as ugh. (Nik: Mm-hmm.)

I don't know the answer to fixing that. And again—

Nik [00:34:31]: I have some additional answer.

Kirk [00:34:33]: Go ahead.

Nik [00:34:33]: So regular numbers, like good old integer eight primary keys. (Kirk: Mm-hmm.) You can... If you have, for example, some object in social media or something, the timestamp, you know two primary keys. You... If you know timestamp for one, you can guess for another roughly. You're just— (Kirk: Yeah.) observing difference. Some people also don't want to reveal the rates of growth. Yeah, well, in integer eight reveals this growth. UUID version— (Kirk: Mm-hmm.) seven not so much, But it, like, there are some nuances here. But what I definitely can see is that if you use UUID version seven, you have...

And obviously you usually have, primary key lookups, and it goes to addresses maybe, like, you have URL including this UUID version seven value. You have quite good mechanism to start partitioning based on time, And I had a couple of write-ups about that, including for TimescaleDB. It's quite easy. So you can start partitioning by time, and you can use it with TimescaleDB, with UUID version seven. And it's natural, and it gives additional like, benefits actually.

You will, like, you just... All primary key lookups definitely will work with partitioning in terms of the planner will know how to route to proper partition— (Kirk: Right.) because it's right there, It's right, right in the value. It's included to URL. But also, like, with partitioning time-based, you can think about data retention properly, so you can start dropping old partitions. Easy, right?

Kirk [00:36:16]: Yeah. Right.

Nik [00:36:17]: For UUID version four, you, if you want this approach, to define which data should be dropped, because it's not needed anymore, for example, you keep only two years of data and that's it, it's much more, like, much harder. You need to create at, add, and so on, like,

Kirk [00:36:33]: Another column or something. (Nik: Yeah, yeah.) And/or another index.

Nik [00:36:36]: Yeah. At the same time—

Kirk [00:36:38]: On every— (Nik: Yeah.) Yeah. (Nik: Yeah.) And that's another thing that I wanted to ask the people who are listening. Is it a fair comparison of choosing between UUIDv4 and v7 when you know you want the data to be sortable based on, like, the created at date?

Nik [00:36:56]: Oh, yeah.

Kirk [00:36:56]: So it gives— (Nik: Yeah.) a different benefit where you say, hey... And this is what I love about it. We often add a created at date.

Nik [00:37:03]: And index it.

Kirk [00:37:04]: I don't need those anymore, and I index it, right? (Nik: Yeah.) So we often create that and index it so I can sort by it. (Nik: Yeah.) I can get rid of that, okay?

Nik [00:37:13]: Yes, you can.

Kirk [00:37:14]: That's three huge benefit, and it saves me an extra index, an extra column, all that extra other I/O.

Nik [00:37:20]: However, it will—

Kirk [00:37:22]: However

Nik [00:37:22]: Bring you additional complexity. I have such tables in our own Postgres database actually. (Kirk: Right.) We have UUID version seven. I decided to get, got rid of created at.

Kirk [00:37:33]: Created at.

Nik [00:37:34]: And every time I touch those database, those tables, it's easy, like, with agents of course, like they will extract not thinking about it, But if you—

Kirk [00:37:42]: And you wrote a creative function

Nik [00:37:44]: Well, yeah. Couple of years ago when I remember I was still manually touching those tables, it was inconvenient, It was inconvenient not to have created at, because every time you need to call a function to see the actual timestamp, because it's in... Like, basically it's encoded in ID, and it's not human readable.

Kirk [00:38:03]: That's a minor—

Nik [00:38:04]: Slight inconvenience, yeah.

Kirk [00:38:05]: Yeah. And I know. Now, this'll drive you crazy. That's why we have views.

Nik [00:38:12]: No, no, no.

Kirk [00:38:12]: I know you don't like views.

Nik [00:38:13]: Well, yeah. You know how to... Yeah. How—

Kirk [00:38:17]: I'm pushing your button on that one

Nik [00:38:18]: Provoke me. This is... No. (Kirk: Yes.) I'm not going to be provoked by views this time.

Kirk [00:38:24]: I did not get a text from Michael telling me to ask you about views. (Nik: Right.) So just, you know, that, don't go blaming Michael now.

Nik [00:38:31]: So yeah. Also, remember Andrey, I think you and Andrey decided to keep this function in Postgres? (Kirk: Mm-hmm.) It was approved by Postgres, but we had some pushback from the authors of RFC that, "Oh, like, RFC doesn't encourage having this extract timestamp function," right? Remember this interesting detail? (Kirk: Yeah, yeah.) Yeah.

Kirk [00:38:56]: Yeah. And it's like, first off, why not? Second, we can write it ourselves. It's not that hard. (Nik: Yeah. Yeah.) Right? We, you know, (Nik: Yeah.) no, it's perfect.

Nik [00:39:07]: But it's important if you... I, I'm super happy to see everything went to Postgres because it became official supported, and now, like, all the agents see it and use it. You just need to ask.

Kirk [00:39:19]: Absolutely. And one of the things I liked, it was one of my early engagements with you guys and being part of this team a bit, and then it actually was one of the top listed items when Postgres came out. (Nik: Yeah.) Everybody was mentioning— (Nik: Yeah.) like, you know. And it, that's my joke with the, with the B-tree bloat removal project— (Nik: Right.) when that goes live, that's gonna be one of the most referenced things in whatever version that goes in.

Nik [00:39:43]: I hope so. Yeah, I hope so. I hope so

Kirk [00:39:46]: So awesome. So again, so the big thing we just uncovered was when you look at how much time is spent because of the random It splits into two sides. It's actually not just the IO and updates of different pages, it's also the check pointing that goes on. And that was new to me. So again, every time you get a result that surprises you, it's educational, right? You just learned something if you got surprised. And so I loved that in this process. I also loved... When you compare, by the way, just the numbers people will be able to read out of our closing piece on this is on the IO cost, given some number of things, it's a 10X, meaning your IO cost in Aurora go from $55, roughly, if you're using UUIDv7, but if you're using UUIDv4, you're looking more like $540 for the same amount of work. (Nik: Right.)

Right?

Nik [00:40:45]: In this particular, like, estimate. Yeah.

Kirk [00:40:47]: In this, in this— (Nik: Yeah.) in this technically made up estimate. But again, since it was driven, some of this was driven by someone in Aurora who got hit with big bills and IO and a little bit of performance issues. (Nik: Yeah.) I think that's, that's the takeaway. Go ahead.

Nik [00:41:02]: Yeah. One one another, like, slight criticism is that, it's 18 bytes, and I agree with that. Like, index size is bigger because index B3 keeps, raw values in leaf nodes, right? (Kirk: Mm-hmm.) So it's, for... But this is comparison with integer eight, right? (Kirk: Right.) It's not comparison with UUID version four. And I think, what we didn't explore this time with Forge, there is also a Snowflake ID, idea, which is used, like, you know, most, like, famous is Instagram, How they generate IDs. It's much shorter. It's eight bytes. (Kirk: Okay.)

But it also has this, like, timestamp component, so it's, order-able. You can order by and have, like, order of creation, at least roughly. So this is also, like, some maybe future work where, like, Postgres could have data type, at least extension for that or something. I'm not sure. Because maybe I do want... I want the same, but you know, like, eight bytes, just to keep— (Kirk: Yeah.) B-tree index smaller, you know.

Kirk [00:42:10]: I'll be honest, I want XID 64-bit. I don't care anymore. Like, space is far cheaper than the hassle of dealing with some of these issues.

Nik [00:42:18]: Well, memory is expensive, and this all pollutes memory as well.

Kirk [00:42:22]: Ah, but you know the solution for expensive memory?

Nik [00:42:26]: No.

Kirk [00:42:26]: It's expensive memory. (Nik: Mm-hmm.) Right? Because the more expensive it becomes, the more companies that are gonna get on the bandwagon and make it, right? And so people are gonna come up with interesting ways to make it cheaper, and then we're gonna... Because that's what happened. If... I remember when I bought my first PC, I couldn't afford the memory. (Nik: Mm-hmm.) Like, back in the '80s, the memory was so expensive. (Nik: Yeah, yeah.) It was, like, more than the computer.

And what cured that was it being expensive enough for long enough that all of a sudden four companies are making memory, and then memory was, like— (Nik: Yeah.) free for a while.

Nik [00:42:59]: Yeah. Well, yeah. But right now we live in the world when, for some types of memory, quadrupled over last two years. So maybe we should explore Snowflake ID. Maybe. (Kirk: Yeah, yeah, yeah.) At least... I mean, you don't... We don't have it in Postgres natively, but it's not like nobody says we couldn't have, like, bunch of PLPGSQL or something, some function, or SQL, pure SQL functions, and have it already, right? And explore how it behaves and, like, in terms of performance as well. Yeah, yeah.

Kirk [00:43:33]: I'm curious to ask the people that are listening, if you stuck on this long, and you're still listening to us, how interested are you guys in understanding the dbforge, tool set? And 'cause we're gonna point it again at the bloat, programs that you know, B-tree bloat removal, and get more feedback there. We're gonna do some more of that. Then I'm actually gonna, follow the recipe and build my own version of this, per se, with Nik's guidance, and we're using this in the future to help convert all the Perl tasks to Python, moving forward with the Postgres program. (Nik: Right.) So there's a big push by Jelte and Dunstan, Andrew Dunstan, are really wanting to do this, and I wanna help them out.

Salma wants to help them out after we get the bloat going. But I'm curious to hear back from you guys, is covering that as its own topic a worthwhile topic in here? And then how much do you wanna follow anything else? And Nik, I'll let you comment. (Nik: Yeah.) Are you afraid to cover that topic or hesitant or? 'Cause you kinda told—

Nik [00:44:39]: No

Kirk [00:44:40]: It's the beta version three right now.

Nik [00:44:41]: It's... Yeah, it's now closed, but we are rolling out it for wider audience. It's not open source. That's, like—

Kirk [00:44:51]: Ah

Nik [00:44:52]: The thing. Yeah, yeah, yeah. (Kirk: Yeah.) We have a lot of open source pieces, but this time I decided to keep it closed source for now. And, (Kirk: Good.) for customers it's, like, our... one of our key tools. We explore stuff with our customers. So I wanted also to say, like, in the closing, that I would just jump to UUID version 7 and just if blockers are not blockers for my particular case. And what's great, you don't need to rewrite your table initially, and you don't need to change data type because data type stays UUID. Just UUID it's called, So you just need to change default function, which generates new values.

That's it.

Kirk [00:45:37]: But the danger of doing that—

Nik [00:45:40]: Danger—

Kirk [00:45:40]: Is even the danger is all of those earlier ones cannot be treated as UUIDv7s, and They cannot— (Nik: Yeah.) decoded with the dates.

Nik [00:45:52]: Well—

Kirk [00:45:52]: Right? 'Cause you're, you're using random numbers now.

Nik [00:45:54]: Right. Yeah, that's a good point. I'm pretty sure it can be solved at application yeah, because as I said, like, the third, chunk, You said chunk, right?

Kirk [00:46:05]: Yeah, I call them chunks, yeah.

Nik [00:46:06]: Chunk— (Kirk: Yeah.) four instead of seven, so if it's strict distingu— you can distinguish it easily. You can even think about partitioning and what will happen with all the version four values, Like— (Kirk: Yeah.) you can just go to old partition or something. But you're right, like, you... we definitely... I'm very curious what will, native function produce if you call it for version four values, I guess.

Kirk [00:46:32]: I think it knows that it's not a version— (Nik: Yeah, yeah.) seven—

Nik [00:46:34]: Yeah. Obviously, yeah

Kirk [00:46:35]: Function. And it is, just so everyone knows, it's the beginning of the third chunk. (Nik: Right.) So it's after the second dash. (Nik: Right. Right.) right. So right after the second dash, it either says 7, 4, 8, 9, you know, there's a bunch— (Nik: Right.) variants. (Nik: Right, right.) Perfect

Nik [00:46:53]: So yeah, yeah, you can either explore and understand everything, or you can just, just jump to it and observe, benefits already from production. Many, many people use version seven. (Kirk: Yep.) Mm.

Kirk [00:47:07]: And if you're curious, it's easy enough to set up a test. Again, we're publishing the links to this in the, you know, in the notes, right? And it, it's really remarkable. And again, give us feedback. How did you like this? How did you like the way we covered it? And you know, we're looking forward to doing more of this.

Nik [00:47:26]: Exactly. And if, there are good ideas what to explore with this Forge tool additionally also, please leave a comment, on YouTube or on X.

Kirk [00:47:39]: Thank you very much, Nik. Excellent session.

Nik [00:47:41]: Yeah. Thank you. We'll see you all next time.