Michael: Hello and welcome to PostgresFM, a weekly show about
all things PostgreSQL.
I am Michael, founder of pgMustard.
This is my co-host Nikolay, founder of Postgres.ai.
Hey Nikolay, what are we talking about today?
Nikolay: Let's continue the discussion about UUIDs.
I chose this topic because I recently implemented something myself
and also because some of my customers raised this again.
So I think many people still have questions.
I had, was it a solo recording about UUID?
Okay, it was a solo episode.
But obviously I have fresh things to discuss and you also have
something to discuss.
So let's return to this topic and be more practical this time
and think about really, really large datasets and how to work
with them more efficiently.
It means partitioning.
UUID plus partitioning.
Michael: So that's what you looked into.
Nikolay: Yep.
Well, let's talk about only partitioning.
We are not sharding experts, so let's do only partitioning.
Michael: But I would say, actually, I think it's important to
mention that one of the big benefits of UUIDs in general is that
they do suit a setup where you're generating IDs in
multiple places.
So it is a natural fit in terms of topic.
Nikolay: So basically you ditch sequences.
Michael: I think this lets you avoid some of the drawbacks that
we're seeing in UUIDs, some of the new implementations, some
of the new standards mean that we don't have the drawbacks that
typically come up in discussions of bigserial versus UUID or
ULID, as they're often called if they're sortable.
So yeah, you were looking into this.
Nikolay: Never, ever integer for primary keys, never.
Michael: Yeah.
Hopefully, people know that now.
You were looking into it for partitioning recently then.
So what was the use case?
Nikolay: So the use case was our chatbot, a new AI chatbot we
developed, and we needed to have a storage for all communication
that happens in a public channel, because we have a paid version,
and a free version, and the free version obviously is recorded
and shareable.
So basically, we created a chats table, and I originally used BigInt
primary keys, and then I was ashamed by my team members, saying
that come on, we need to use Timescale there first of all and
also, like numbers in URL is not looking great. We need some better
ideas to have there.
Michael: Is this because they would be guessable, or is it a security
thing or something else?
Nikolay: Good question.
I have a long history about this.
So I remember my first social networks, we tried to hide the
actual number of posts and users we have.
But then a competitor was released and they didn't care about it.
They didn't care about it.
And then I also started not to care about it.
It's okay to have some numbers in the URL and share actual numbers
with everyone.
But here I just think maybe it will be maybe shared data set
in the future.
So I don't want to have sequences.
And also maybe I don't want to share actual numbers.
I don't know.
Like it's one of the things where you're not fully sure.
So I'm kind of okay to share numbers, right?
So for example, we generate 5,200 messages per day with this
bot.
People are using it. We have 72 people joined the program, many
more waiting in the waitlist.
It's not a lot at all, but obviously it will explode soon, I
think so.
And maybe I don't want competitors to see actual numbers.
I don't know.
50-50 here.
Because I had experience in both cases.
I had experience sharing numbers, I had experience hiding numbers,
and we used two prime numbers to have not actual random, but kind
of random.
If you take a sequence and multiply it by a very large integer and
then take a modulus with another integer, and these two integers
should be mutually prime.
In this case, you have like a rotational, without collisions, you
have like kind of random numbers but they are not actually random.
And nobody knows your actual ID number.
So this is how you can hide the growth rates and actual number
of your, like, business metrics and so on, like how many posts
or comments or anything you have in your social network, for
example.
Again, it's not my main reason.
I just think, okay, we need to use a UUID probably, and then the
team said we also need to partition it.
We already used TimescaleDB.
Let's just use it.
And then I think, okay, obviously, I want my indexes to behave well.
If I use a regular UUID version 4, for example, supported by Postgres,
currently, I know we will have performance issues because numbers
are going to very different random places on the B-tree index.
Not purely random, but any insert disturbs some arbitrary part
of index.
And this is not good for performance, especially also if you
want to show last 25 items, for example, ordered by time, creation
time.
Yeah, you will deal with a lot more buffers than you should deal
with.
Michael: And a lot more for write-ahead logging, right?
Because you've got full-page images for different parts of the
index each time instead of well B-trees are very optimized for
adding things to the to the end to the right side continuously
like timestamps and UUIDs don't fit that pattern at all but that
takes us on to I guess some of the
Nikolay: newer right so I I And since we also had a session on
PostgresFM YouTube channel with Kirk and Andrei, I think it's
Kirk who brought this topic, let's implement ULID in Postgres.
And using Andrei's hands, we usually use Andrei's hands because
he's a true hacker in those sessions.
And he implemented quite quickly some patch, sent it to hackers,
and quite soon we realized there is already ongoing proposal
of RFC change to support version 7, version 8 of UUID, and Andrei
renamed the patch from ULID to UUID version 7, and then it was blocked
by the idea, let's wait until the standard is finalized and fully
approved.
You read this thread.
There was also an idea, let's just avoid naming version 7 completely.
Michael: Well, I thought that was a really neat solution.
So, ultimately, what we want in, well, what you want, what I
would like, I think what lots of people would like, is a function
within Postgres to generate things that can be put in a column
of type UUID, but that when we generate new ones based on a timestamp,
they are sorted at the increase over time.
So that comes with loads of benefits for updating B-trees and
some of the other benefits that you mentioned already.
Now there are some new definitions of new specifications for
UUIDs coming down the pipe that would suit those perfectly.
But somebody made a really good point.
I think it was Andres from Microsoft.
One of the first replies that Andres got was we could just name
the function along the lines of what it does rather than UUID
v7.
I can't remember the exact name, but they came up with something
very sensible, I'm sure.
And, yeah, that seemed really smart to me.
Because then even if the spec changes for v7, it doesn't matter.
We can implement those later or have different functions for
those, but this one could still be useful to users earlier, before
the spec finalizes, before everything else gets agreed.
So I like that as a suggestion, but I think it either got lost
or people are very happy waiting another year.
Nikolay: Well, it's just because it looks like RFC makes good
progress and it should be finalized.
It's still not finalized.
I've checked it a couple of days ago.
It's not finalized.
But let's explain once again what we want.
We want to take good things from both worlds.
Growing integers, right?
Which if you order them actually completely, if you have, for
example, regular approach, you have ID and created at.
Created at is 16 bytes and ID is 8 bytes.
If you use 4 bytes, you will still use 8 bytes because of alignment
padding, we know it.
So, and if you just insert and created at is just now, and you
insert just 1 row per transaction, or you have clock timestamp
instead of now, in this case you can insert many rows.
So, the order of IDs and created at values will be the same.
You can order by ID desc limit 25 or you can order by created
at desc limit 25, same.
And this is good.
This is good benefit, and it's also good for performance because
of locality of data, rows created inside the same second go probably
in 1 or 2 pages only.
Like, very, it's packed, not distributed sparsely, right?
And UUID is good because it doesn't require any sequences.
You can generate it using thousands of nodes.
This is a name, UUID, universally unique ID.
But they go into different places of B-tree and you cannot order
by them.
You cannot order by them.
This is the main problem.
Michael: In like version 4 of the specification.
Nikolay: Yeah, yeah, I'm talking about current situation.
And also with RFC, it's still not finalized, so it's also the
current situation if you don't use drafts, right?
And this is a problem.
And we have good blog posts from Shopify.
It's MySQL, but it's very good.
They explain the economical benefits from switching to different
type of UUID, newer type of UUID.
And also, Husayn Nasir, sorry, I pronounced his name wrong, on
YouTube explaining very well for backend engineers, This Shopify
case and why a better version of UUID is really better, he explains
it very well.
Very, very well.
Like, his explanation is, anyone can understand this now, right?
Maybe better than I just explained, right?
Michael: Let's link it up.
I actually pulled out a quote from that Shopify blog post.
It said, in 1 high throughput system, they saw a 50% decrease
in insert statement duration by switching from UUID v4 to ULID.
Nikolay: And you can order by them.
If you want, like, 25 letters posts, you need to order by, right?
Michael: Even if you don't need those, even if you don't need
that, there could be significant benefits because of
Nikolay: the B-trees.
Let's slowly move to partitioning.
If you don't have an orderable ID and you rely on created at
column, or you can have an index on created at, you can order by
and use an index scan on that.
Okay, It's okay.
Not perfect situation because you have many more buffer numbers
in the plan, right?
Because you fetch from random places.
But if you want to partition such table, in URL you have UUID,
right?
How to find which partition to deal with?
Michael: You could do like hash partitioning.
Nikolay: Well, yeah, yeah, yeah, yeah.
But I want to partition by time, I forgot to say.
I want to partition by time because older data is used less often.
Michael: And maybe like you want to phase it out over time.
Maybe you don't even, like, you might want to eventually drop...
Exactly.
Nikolay: Or move to a different tablespace.
It's not super popular solution, of course.
Also, There are some ideas to be able to move it to object storage,
which is only supported in Timescale Cloud.
I think one day it should be possible with regular vanilla PostgreSQL.
Michael: I think I read about another provider doing it recently
too.
Nikolay: Interesting.
Send me a link.
I'm very interested in learning about it.
I think moving older partitions, time-based partitions, like
our old data, like 2 years old, moving to object storage, eventually
moving towards like bottomless Postgres, right, it would be great.
But back to our question.
In URL you have blah blah blah, in our case postgresql.org slash
some UUID.
And we need to understand which partition to deal with.
In our case it's TimescaleDB.
How to tell TimescaleDB which partition it is?
If it's a regular UUID, I think it's a nightmare.
Michael: Well, I saw the update and the reply you got from, was
it James Sewell at Timescale?
Nikolay: A couple of guys replied from Timescale company.
I really appreciate it, but it was about already this sortable
ULID version 7.
This is already solved.
This recipe I have in this Postgres Marathon.
It's recorded.
I implemented it my own way.
It was not efficient.
And they just provided very good advice how to do it much more
elegantly.
And I have it covered in my small article.
So ULID, it's similar to UUID version 7 or 8.
ULID, it's actually it should be UUID because it's universally
unique, lexicographically orderable or something.
Sortable.
Michael: Yeah, probably like UUID.
But I like, I admire the people that came up with ULID.
It's nice and simple and unique.
Nikolay: So I guess we are about to abandon this abbreviation
at all and just use UUID version 7 or 8 because of the standard.
Michael: Or maybe in the future we'll just say UUID
because I don't, there might not be that many benefits to
using the random ones.
Yeah, sure, you get extra uniqueness, like you get more bits
assigned to the uniqueness part, but we already have so many,
like I was looking up version 7 and I think the spec currently
says 48 bits for the timestamp.
So if you're thinking in terms of bits
Nikolay: and bytes.
16 bytes.
Michael: Yeah.
So 16 bytes or 128 bits.
48 of those are reserved for the timestamp component.
And there's a little bit about, to specify the version, 74 bits
for the randomness.
Now naturally in a UUID v4, you get all 128 bits for randomness.
So there is less randomness possible in these ones.
Nikolay: There may be collisions, right?
Michael: Or just a slightly higher percentage chance of it, right?
Like, it's not, we're still talking about a, you know, especially
if you're talking about in your case, the chance of collisions
is just basically 0, which is why these are useful.
Nikolay: Yeah, even if we will have like 100 messages per second,
we will be fine.
Michael: Yeah, exactly.
Nikolay: And we can generate them on any server basically.
Well, how clocks are set will be a question of course.
Michael: This needs to be UTC, right?
Nikolay: So this version 7 or ULID or version 8, version 7 and
8 they distinguish in precision only, right?
Michael: That was my understanding.
Nikolay: The idea is let's take timestamp, let's generate regular
UUID, or something like that, and then produce a new UUID, the
prefix of which will be, not prefix, first bytes of which will
be corresponding to timestamp, so they will be sortable.
If you can order by and with very, very high confidence, you
can say, this corresponds to created timestamp.
And actually, the interesting thing...
So, Postgres is on pause, right?
Waiting for RFC to be finalized.
I mean, I actually started to doubt that Postgres 17 will get it.
This idea to change name and just provide something not depending
on the RFC is good, but I guess consensus was not, maybe not
consensus, but what I saw from these Postgres scale hackers mailing
list, people decided to wait until the RFC.
And it's not fully clear, I checked the status, it's still like
waiting for some reviews, but it's not fully clear when it will
be finalized.
So I guess, probably 17 will not have it.
Michael: My experience with these things is it depends if a couple
of people get excited about this if somebody ends up needing
it, somebody commits it, if somebody puts a patch together that
can generally be agreed on and somebody else is willing to review
it, it could get in.
But it depends on a couple of people at least having the energy
and
Nikolay: focus.
Well I just remember Peter Eisentrout's comment that we should
probably wait for the standard and Postgres is very close to some
standards, you know, like including SQL standards.
So it's a very, very important comment.
My impression is that we're just waiting for the RFC.
But good news is that you can generate it in any application
code yourself.
There are many, many, many ULID or this ULID version 7, if RFC
is not yet finalized, they're called already version 7, you can
do it.
And you can find on GitHub a lot of stuff.
But you can also generate it using PL/pgSQL.
Yeah.
And additionally, like, and Daniel Verite, I hope I pronounce it
right, sorry guys if I pronounce names wrong, showed some proposal how
to generate it using PL/pgSQL, and then I asked, I'm just looking
at the code, I'm saying, we don't need actually PL/pgSQL here,
we can use it with regular SQL function, and he answers, yes,
just scroll down.
The same GitHub discussion, it was GitHub gist, I see a SQL function,
it's quite simple and I just started to use it.
So I used that function, and I just generate UUID with 7 using
small SQL function, that's it.
But then the very big question, do we really need a created that
timestamp?
If we have a timestamp already present in this UUID value which
is 18 bytes, and we don't need a very good precision, maybe we
can extract the timestamp back.
And the answer is yes.
I saw Andrei's comment in PostgreSQL hackers saying that the authors
of the RFC proposal don't encourage it, extraction of UUID version
7 values, extraction of timestamp.
But for partitioning, we actually need it.
And also, we need it if we want to just drop our created at column,
because why?
We have created that right here.
Michael: I'm not sure we do need it for partitioning.
Like, if maybe in Timescale, the way you define a hypertable,
but for example, if I set up a range partition using a ULID,
And I could tell it, if I maybe, maybe, yeah, I could, or maybe
not even just prefix, maybe exact UUID between these 2 UUIDs.
Nikolay: Yeah, yeah, yeah, yeah, I agree.
Yes, I'm in the context of my particular recipe with TimescaleDB.
Yeah.
And TimescaleDB, of course, would like to have timestamps.
And I created some recipe which looked quite weird, but it worked.
But then a couple of Timescale guys provided good advice on how
to use it much better, just saying time partitioning func option
when you create hypertable, TimescaleDB hypertable, and that's
it.
This func function is our function which converts UUID version
7 to timestamps.
I strongly believe this is a very helpful function to have.
So we need to have it.
Even if RFC authors think we don't need it, I think we need it.
I hear you, we can have ranges, yes.
But having this function is super helpful because I can reconstruct
timestamps even with some not perfect precision.
Timestamps are 16 bytes.
UUID version 7 is 16 bytes.
We know not everything on those 16 bytes is related to a timestamp.
You said like how many bytes, I don't remember.
Michael: More than half is random, yeah.
Nikolay: Yes, so we lose precision.
But I don't need the milliseconds.
Michael: Oh, that's
Nikolay: a good point.
Michael: That's a good point.
I'm looking at the V7 spec.
I suspect V8 with more precision.
Nikolay: If you need better precision, use V8 and have better
precision paying some extra storage costs and memory, of course,
Michael: and buffers and
Nikolay: so on.
Michael: I don't think it's still UUID format, right?
So I don't think you do pay those extra precision.
But I think you get less randomness.
Nikolay: Ah, less randomness.
Well,
Michael: Fewer bits for the random jump.
Nikolay: Well, I need to look at it then.
Yes, I only played with v7 and I decided to use it, but maybe
I should look at v8 and understand the trade-offs.
Yeah,
Michael: Well, good to understand at least.
Nikolay: Yeah, yeah, interesting.
So anyway, you can take UUID v7 right now, generate it on application
on using this SQL function, quite simple, and then join this
with Timescale and have partitioning provided by TimescaleDB,
which is like fully automatic, very good.
And that's great, right?
And it's sortable and it's quite efficient.
Yes, it's 16 bytes versus 8, but for timestamps, we also had
16 bytes.
And I decided not to create created_at at all.
Michael: Yeah, you said do we still need created_at?
And then you answered yes, but I thought you meant the other
way around.
So you mean now we don't need created_at at all?
Nikolay: Yeah, we can extract it and that's it.
So I don't need super precision in my case, it's just some messages.
And I'm fine with even second precision.
So yeah, it's a good question which precision I get extracting
from this UUID version set up.
Michael: Well, there's an argument you could, if you don't care
about the spec, you could invent your own version that only goes
down to a second precision and gets even more random.
You could play with that a little bit.
Nikolay: And adjust the function and feed it to Timescale and
so on.
This recipe is already showing all internals, right?
It's pure SQL.
So we don't need to...
We can use it in any place, in any Postgres flavor, RDS, Aurora,
anywhere right now.
So that's great.
Of course, in RDS you don't have TimescaleDB, right?
But you have a new sharding of Aurora, just released like a month
ago or when.
So yeah, I don't know, like I'm excited to see us shifting from
regular numbers to these IDs, actually.
Yeah, it's good to see some numbers.
Let's provide some links to blog posts for those folks who want
to explore overhead.
Michael: Well, I think there's an interesting post by Brandur
that I'll link to.
Nikolay: Exactly, this is one of them.
Michael: Yeah.
Nice.
But they also mentioned a couple of downsides of ULIDs, which
I hadn't considered that I think's worth mentioning.
And well, let's talk about them quickly.
1 is that, well, there's naturally less randomness because we're
taking up some of it with timestamps.
We've talked about that 1 already.
But the second 1 is you could end up producing a lopsided index.
So if you have deleted data, we won't reuse that space, like
sequential IDs.
But in other UUID implementations, because they're more random,
you would reuse that space.
So we've got kind of a different bloat problem, except if we
partition, right?
If we're partitioning and if we've got index maintenance, if
we're dropping our partitions over time, we'll naturally get
rid of that bloat.
But I thought it was a really good point that there are some
downsides to this always increasing idea as well.
But they mentioned at the end of the blog post that they expected
to go into the investigation very much on the side of using big
serial or big ints everywhere.
And actually they ended up thinking these ULIDs or they called
them performance aware or intelligent UUIDs, which I thought
was quite a nice phrase.
Was their favored approach now?
Nikolay: I'm very sorry.
You said in the end of his blog posts, and I see pictures of
the walk.
I don't know how it's related at all.
No, no,
Michael: A bit above that.
Just at the end
Nikolay: of the title.
I've got distracted.
First of all, very good title of the blog post, identity crisis.
So ID crisis.
But yeah, these pictures of the San Francisco Bay Area and the walk down
to Pacifica.
I know Brando already heard 1 of our episodes and highlighted
my phrase on Twitter when I was saying, very good blog post,
but completely wrong.
Something like this.
Not completely, right, okay.
And I was right, actually.
I like speaking from experience.
And now I must say, you should not put very good pictures of
101 highway.
Because it's super distracting.
Michael: Yeah, I actually remember that quote.
I think it was I think the words you used were something along
the lines of great post 1 conclusion.
Nikolay: Ah, yes, exactly.
Right.
And then
Michael: that must be I think there must be a second one as well,
because I think at Crunchy Data, credit to them, they listened
to the one where we were talking about random page cost and how
it was still 4, but they did some benchmarking and reduced it,
I think, to 1.1, which is a much more SSD-friendly number.
So maybe they listened
Nikolay: to 2.0.
And also, yeah, I also learned from this session, which was full
of, I also learned that 1.0 probably is not better than 1.1,
which is interesting because they had numbers.
Yeah, they had some numbers proving that 1.1 is better than 1.0.
So this is super interesting.
Next time I touch this area, I will pay attention to their numbers.
Michael: Nice.
Nikolay: Yeah, that's good.
Michael: Were there any other posts or things that you've seen
that you wanted to draw people's attention to?
Nikolay: I don't know.
There are many materials about it.
I don't remember particular ones.
Let's just put something in the show notes.
It's an interesting topic, I think very important for maybe everyone.
Michael: Yeah.
There's an outdated one by Christophe Pettis that I thought was
quite good, but then a more recent one by them as well, suggesting
that we think about it in two steps, the age-old UUID versus serial
question.
They suggested thinking, firstly, should our keys be random or
should they be sequential?
That's a very good point.
And do you want them to be guessable?
Do you want to be able to infer?
Actually, we didn't talk about this, but if you're using ULIDs
and someone can tell their ULIDs, they also get a little bit
of extra information.
They can tell when this ID was created.
Is that a problem for you or not?
These are the questions you need to ask yourself.
So that's number one.
Should they be random or should they be sequential?
Nikolay: Do you want to hide it, right?
Michael: Yeah.
And then the second one is, should they be 64 bits or should they be
be larger than that?
And that's a second separate question.
But I think Christoph might be
Nikolay: really good.
I want them to be shorter, not larger.
Michael: Okay.
But sorry, bits.
Did I say bytes?
I don't remember.
Anyway, the point is how much randomness, like how much entropy
do you need?
Nikolay: And that's collision risks, basically.
Michael: Yeah.
But these 2 questions, they're separate questions.
And Michael made a really good point that often they get conflated
in these arguments between like people that argue for UUIDs are
arguing along 1 of these questions and people that argue for
bigints are just completely ignoring that question and going
down a completely different question.
So I think asking both of those questions is important before
picking your ID.
Nikolay: We could handle collisions maybe in certain conflict
and just adjusting last bits slightly on conflict.
Yeah, well, I'm not sure.
I'm just thinking like out of cloud.
Michael: But yeah, in terms of likelihood of collisions, I actually
haven't done the math to how many, like how unlikely they are.
But I think they're pretty minuscule likelihood, at least in
most of the deployments.
Nikolay: It depends on the volumes you're trying to ingest in
your database.
Michael: Yeah, of course.
Nikolay: Yeah.
But anyway, I think next time we build a new system we need to
think should we use these like int8 surrogate keys at all
Or maybe it's time for UUID version 7, version 8 with partitioning?
Actually, my response to my team was, partitioning?
Come on, we know how to handle a billion rows easily in 1 physical
table.
Let's just grow it until a billion.
And I said, no, no, no, let's do it.
Like, it's so easy.
Like, let's just do it normally, in a normal way.
Okay, okay.
We have partitioning.
We have UUID version 7.
A modern approach, you know.
Michael: Well, last question.
Last question from me.
What chunks interval did you go for?
Nikolay: Oh, that's a good question, I actually need to check.
Because I remember for development, I think I used for experimentation.
I used 1-hour chunks, very small, like tiny.
Michael: So, even smaller in your test setup?
Nikolay: Well, maybe a minute, just for testing, but eventually,
I think it will be, I don't know, days or weeks, but it will
be smaller chunks.
TimescaleDB is good with small chunks.
A lot of small chunks.
Michael: Oh really?
Nikolay: Not months.
Well, maybe months actually.
It depends, actually.
It depends.
It's a good question.
I need to reconsider it.
This I need to finalize, but we can adjust it over time as well,
I think.
Michael: Yeah, I wouldn't be surprised if you start off much
longer and then go smaller and smaller.
Nikolay: Makes sense.
It depends.
If you want to test it in advance how it works with a big number
of partitions, chunks of hypertable, you probably want to go
smaller and find problems earlier.
It's a problem when you want to go deeper, right?
But if you build a normal system, of course, it makes sense I
think the rule of thumb is, it's not about Timescale.
Rule of thumb is, except Timescale, partitioning for all tables
which exceed 100 gigabytes, maybe actually 10 gigabytes.
This rule of thumb, so to speak, was raised by several folks.
I listen to them very well.
Their words matter to me in a lot of sense.
So why 100 gigabytes?
Why 100 gigabytes?
Why?
It's just like, okay, it's some empirical rule based on, for
example, Alexander Kukushkin said, based on Zalando experience,
those 100 gigabytes.
But then I realized I can build some theoretical basis and find
a better threshold.
So, theoretical basis is how many transaction IDs consuming transactions
per second you have.
For example, 100, right?
So how fast your move consuming exceeds.
And then how long does it take for Autovacuum to process a single
table?
And also how long does it take to create an index on the largest
tables.
Because when you create an index, you hold a snapshot horizon.
If it takes many hours, you have, for example, 5 hours to build
an index on a 5 terabyte table.
For example, it's very arbitrary numbers, right?
During which, autovacuum cannot delete the tuples from any table
in your cluster, in your database.
And it means that if you have a lot of write growth during this,
you insert a lot of...
So you can start comparing these numbers and understand, this
is...
Okay, I need not to go more than like 100,000 writes to be spent
during index creation.
And if you split your physical table into smaller physical tables,
partitions, or chunks in the sense of Timescale, index creation
becomes faster.
And autovacuum is blocked for a smaller period of time.
Michael: But a couple of other things, like a couple of things
I've heard being used for this are size of cache, like how many
chunks do you want to be in memory?
Like if your memory is smaller than your most recent chunk size,
that might not be optimal for performance.
You might be better off with smaller chunks.
Some of the more recent ones are more likely to be in memory.
And then and then another one.
Nikolay: It's but it's very rough, rough reasoning.
I would
Michael: say yes, but it's the other direction, right?
It's like, one of them is encouraging you to do it earlier and
earlier.
That one is in the same direction, isn't it?
It's like earlier is better.
But you can go too far.
You could have too many.
Let's say you want to regularly query the last day, but that's
going to involve querying 24 partitions.
We've already talked in the past about problems when you query
too many relations at the same time.
Nikolay: Yes, planning time and execution time and lock manager
contention.
A lot of stuff happens if you have a lot of partitions, and especially
if each partition has a lot of indexes.
So yeah, many, many things to, there's a trade-off obviously
here, but Timescale is very good with a large number of partitions.
Michael: Okay, good.
Nikolay: Yeah, so we can go with daily partitions, it's okay.
And we have cases, my colleagues have observed some cases where
we have dozens of terabytes of data with daily partitions, very
small, maybe not even daily, I don't remember details, maybe
some like 8 hours partitions, ingesting like a lot per second.
Michael: Did they change the...
Remember when we talked about the lock manager issues with not...
When you don't have partition pruning and lots of indexes on
each partition?
Did Timescale...
Like presumably that hard limit is still there in Timescale as
well.
16.
Yeah.
So you say it's really good with lots of partitions, but if you're
querying too many of them at the same time, like if we're doing
monthly reports against 31 or however many days, we're going
to bump into those limits, right?
Nikolay: Definitely but a monthly report is just a single query.
The problem is when you have thousands of such queries per second,
then they start competing and the log manager, lightweight log
contention is happening.
If it's some not frequent...
So, second question to check is how many QPS you have.
Michael: Yeah, yeah.
So if it's like dashboards or something.
If it's monthly reports,
Nikolay: you're probably fine.
Nobody says we cannot have more than 15 indexes on a table, achieving
16, this fast path locking threshold.
It's okay to exceed it.
The only problem when you need to exceed it a thousand times
per second.
Yeah.
Michael: Cool.
Right.
Any last words on this one?
Nikolay: No, I think it was good.
A lot of things to understand and play with.
And I think I encourage folks to look at these new versions of
UUID if not yet.
Does it matter where we generate it, on application or on database?
Michael: I think the only risk is time zones.
Nikolay: Clocks can be off.
Michael: Yeah, true.
If they're off by seconds, then it will be going to different
database pages.
Yeah, it won't be
Nikolay: as bad.
And also, order will be broken.
Michael: Yeah, so I guess it matters a bit.
Nikolay: Yeah, I prefer generated on Postgres database side.
But interesting question, pros and cons of database versus application
side.
Anyway, I think Postgres eventually will have UUID version 7.
Not sure for 17, but it should be for 18 at least.
Only a couple of years of waiting.
But we don't need to wait, and we know now how to use both this
new type of UUID and partitioning, TimescaleDB or any other.
Yeah, good?
Michael: Nice one.
Well, thank you, Nikolay, and catch you next week.
Nikolay: Thank you.
Bye.