Aug 30, 2026 · 16 min · 9 segments
In this episode of Scaling Postgres, we discuss getting 23 times faster inserts with UUIDv7, the benefits and downsides of UUIDs, how to optimize queries without table changes and using WAIT FOR to…
Creston JamisonHost
Our first piece of content, PostgreSQL 18 23 times faster inserts with UUID version 7.

This is from andyatkinson.com. And they had a situation where they were using a mix of UUID version 4, so that's the fully random UUIDs, and also UUID version 1, And he said, you know, insert performance wasn't as bad for the version one compared to version four, but they wanted to move to version seven to improve their insert performance because it does help result in smaller indexes and fewer page splits.

which means less CPU and I.O., and I imagine you should be seeing less wall produced as well.

So the reason why the complete randomness of version 4 is a problem, particularly if you're talking about primary keys or indexes, is that it has to insert those randomly across the index, whereas the version 7, since it's time-ordered, you're essentially appending it to the already existing index.

And they did reference these resources that did research and write-ups about it.

So he says, for many of the tables they did this conversion for, going from version 4 or version 1 to version 7, there wasn't an obvious change.

However, in five of the tables they saw a 6-fold increase, an 8-fold, a 9-fold, a 20-fold, and a 23-fold.

So you can look at the PD Analyze graphs down here for insert performance, and you can see the average I.O. time drop like a rock.

And I'm assuming this is total time, he said, went from about 0.7 to 0.03 milliseconds, which is crazy.

So to migrate to version 7, they basically altered the default for the column.

They were using the UUID-OSSP module for the version 1 UUIDs and genrandom UUID that was added in Postgres 17 for the version 4 UUIDs.

Now changing that default is a very fast operation, but the problem is it requires an access exclusive lock.

So if you have a highly active table, it can take a while to apply that very fast change.

So he says infrequently queried tables didn't have a problem changing this default, but it was of course the higher queried ones that had a problem.

So they basically set a very tight lock timeout, so it would cancel this statement if it caused something else to lock greater than 50 milliseconds.

And of course they had to introduce retries as well, because inevitably trying this once or twice didn't work.

They also considered potentially cancelling queries if they were holding locks.

Our first piece of content, PostgreSQL 18 23 times faster inserts with UUID version 7.

This is from andyatkinson.com. And they had a situation where they were using a mix of UUID version 4, so that's the fully random UUIDs, and also UUID version 1, And he said, you know, insert performance wasn't as bad for the version one compared to version four, but they wanted to move to version seven to improve their insert performance because it does help result in smaller indexes and fewer page splits.

which means less CPU and I.O., and I imagine you should be seeing less wall produced as well.

So the reason why the complete randomness of version 4 is a problem, particularly if you're talking about primary keys or indexes, is that it has to insert those randomly across the index, whereas the version 7, since it's time-ordered, you're essentially appending it to the already existing index.

And they did reference these resources that did research and write-ups about it.

So he says, for many of the tables they did this conversion for, going from version 4 or version 1 to version 7, there wasn't an obvious change.

However, in five of the tables they saw a 6-fold increase, an 8-fold, a 9-fold, a 20-fold, and a 23-fold.

So you can look at the PD Analyze graphs down here for insert performance, and you can see the average I.O. time drop like a rock.

And I'm assuming this is total time, he said, went from about 0.7 to 0.03 milliseconds, which is crazy.

So to migrate to version 7, they basically altered the default for the column.

They were using the UUID-OSSP module for the version 1 UUIDs and genrandom UUID that was added in Postgres 17 for the version 4 UUIDs.

Now changing that default is a very fast operation, but the problem is it requires an access exclusive lock.

So if you have a highly active table, it can take a while to apply that very fast change.

So he says infrequently queried tables didn't have a problem changing this default, but it was of course the higher queried ones that had a problem.

So they basically set a very tight lock timeout, so it would cancel this statement if it caused something else to lock greater than 50 milliseconds.

And of course they had to introduce retries as well, because inevitably trying this once or twice didn't work.

They also considered potentially cancelling queries if they were holding locks.
The rest of this transcript — segmented and speaker-labeled, so you land on the exact moment something was said
Search every transcript — by keyword, by phrase, or by meaning, across every show Radar indexes
Trends — what is surging across podcasts, measured against its own baseline
Alerts — when a name you follow appears in a newly indexed episode
No account is needed to search Radar.