![]() ![]() Locality=# create table records (id int8 not null, uuid_v4 uuid not null, uuid_v7 uuid not null, filler text) This table will have 10 million rows in it. ![]() We will also add 100 bytes of filler for every row, both to be realistic and in order for the planner to pick the plans that I want. Let’s also add a timestamp-based UUID method (using the soon-to-be-standardized UUID v7 method) to see if the reason for this is the UUID type itself, or the data ordering. To start with, we’ll create a table of records with a traditional integer id, a random UUID. Getting started: table with a random UUID This is a story of how that can become important. With random ID’s, values that are near each other in the index are going to be inserted at totally different times, and be in totally different places in the table. Since workloads commonly are interested in recently inserted rows, this means that a random UUID-based index will need to keep essentially the whole index in cache, whereas a sequential id index can make do with only a few recent pages.īut this relationship goes the other way, too. By contrast, id’s generated from a sequence are going to be close by each other in the index. B-tree indexes on UUID values are built in lexicographic order, which means that when using this index to look up two rows that were inserted at approximately the same time, they will be in completely different places in the index. The trouble with picking random values is that they lose any correlation between the order of inserts and the order of id’s. Random values for UUID’s = loss of correlation There even is a convenient PostgreSQL function called gen_random_uuid() for generating these values. That standard requires fixed values for 6 bits, leaving only 122 bits of randomness– but that is still plenty for all practical purposes. (As an aside, for those worried about collisions: you should take up the lottery, since winning the jackpot twice in a row is a much more likely outcome than your system ever generating two identical random 128 bit numbers.) There is a standard for doing this called UUID v4. The most common way to generate a UUID is to pick a random 128 bit number. The most important of these downsides is a loss of temporal locality. However, like everything good in life, UUID’s come with their own downsides. To move sets of related records between different databases without having to deal with renumbering everything.To be able to generate keys independently of the database.There are various compelling reasons to use universally unique identifiers (UUID) as primary keys. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |