Episode Transcript
[00:00:00] Speaker A: The debate on whether to use an integer primary key or a random identifier like a UUID has been raging for years. But we've also had evidence for years that UUIDs, when used as primary keys, can also hurt your performance.
And of course more recently with the introduction of UUID version 7, which are time ordered ones, that alleviates a lot of their performance issues.
And in the first post this week we're going to talk about an actual case of the performance improvements that you can see when you switch to using these time ordered UUIDs.
But I hope you, your friends, family and co workers continue to do well. Our first piece of content PostgreSQL 1823 times faster inserts with UUID version 7 this is from andyatkinson.com andy 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 1 compared to version 4, but they wanted to move to version 7 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 four 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 a version 7, since it's time ordered, you're essentially appending it to the already existing index so you don't have to deal as much with page splits.
And they did reference these resources that did research and write ups about it, but they went ahead and implemented it for their tables. 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 5 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 IO 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 1UUIDs and genrandom uuid that was added in PostgreSQL for the version 4UUIDs and they did have a few UUIDs generated by the client application as well. So they basically needed to replace the defaults with UUID version 7.
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, query 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 also set the statement timeout to only run for 100 milliseconds. And of course they had to introduce retries as well because inevitably trying this once or twice didn't work. But after doing enough retries they were able to apply it. So they show an example of a function to be able to do that. They also consider potentially canceling queries if they were holding locks. They didn't ultimately choose that option, but that's another possibility. And he did mention the downsides of EUID version 7 is that it is timestamp ordered, so you're essentially, you know, quote leaking or exposing the creation time of the record.
But you have to make that decision if it's important or not for your application. But if you are using version 4 UUIDs for primary key, I think now is the time to move to version 7 now that it's a built in option in Postgres 18.
Maybe you won't see 23 fold improvement in your insert performance, but something like this is definitely possible. And if you want to learn more,
[00:04:25] Speaker B: check out this blog post.
[00:04:26] Speaker A: Next piece of content Time Traveler's primary key this is from pgedge.com and this is another blog post ironically talking about version 4 UUIDs and version 7 and the issues with version 4 UUIDs version 7 as well. And it actually shows some of the bloat potential here. So the average leaf density of random uuid is 71% versus 90% for the version 7. An example of the leaf fragmentation that can occur with a random UUID versus not much in this example for the version 7 he does mention disadvantages of UUIDv7, but he also mentions another alternative is snowflake IDs which is a smaller version essentially doing what UUIDv7 does. So instead of UUIDs having 128 bits, the snowflake IDs have 64 bits, and at 64 bits, the first 12 is broken down as a counter for 4096 unique IDs per millisecond.
The next 10 bits are to encode a local identifier, for example an instance.
The next set is a timestamp with millisecond precision, and bit 63 remains unused for signing purposes. So again, time ordered in instance ordered but half the size of a Uuidv 7. So that's an alternative you can explore. The PGEdge has released a Snowflake extension to use these types of IDs if you're interested in that. But if you want to learn more,
[00:05:59] Speaker B: check out this blog post.
[00:06:01] Speaker A: Next piece of Content how to optimize when you can't do anything this is from H domrovoskaya.WordPress.com she's talking about an issue she was working with with a customer. They were on Postgres version 13.6. They had a 750 gigabyte table with 16 billion rows that wasn't partitioned.
It had several indexes already, but none of them were suited to the type of query they wanted to do. And the query had this pattern basically filter on three columns and then look for the start date column less than a date and an end date column greater than a date. And they didn't really have any indexes to make that more efficient. She said, you know, we could have replaced it with a range type and put a just index on it to help with the range query.
But she couldn't really make those types of changes in this particular instance. So what she decided to do is to create a materialized view that she could then apply specific partial indexes to to make the performance better. So she goes through the process of doing that and got the queries down to running in milliseconds. So this is a great example of using a materialized view, because you can then apply indexes to it to make queries run faster. You could refresh it concurrently if you need to, but you don't have to change the fundamental structure of the existing table. But if you want to learn more,
[00:07:26] Speaker B: check out this blog post.
[00:07:27] Speaker A: Next piece of content Read your rights wait for in PostgreSQL19 this is from Clickhouse.com so this is a new feature presumably coming that allows you to wait for a particular LSN before running a select query against it.
So how you would use this is on your writer or your primary you would do some sort of an insert or update and then you would get the current wall position and then when you want to query to ensure that that data has actually made it to say, a replica or a reader that's following the primary, you would first call waitforlsn and give it the LSN you received after the write and then you can process your query, ensuring that that write has made it through to the standby or the reader. And she says, quote There is no application side polling and no snapshot is held while waiting. The back end sleeps on a latch and the startup process wakes as soon as replay reaches its lsn. Now, of course, because it's waiting, you can introduce a timeout and you can also establish a mode. The standby, of course, is that it has been replayed in the database on the standby, but you can also choose other modes like standby flush, standby write, or the primary flush. And of course, with regard to the timeout, you can also specify no throw so it doesn't raise an exception. So this is pretty cool. I know a lot of times application frameworks have built this read after write capability, but maybe this would require less plumbing from them to achieve some of that. So if you want to learn more,
[00:09:04] Speaker B: check out this blog post.
[00:09:06] Speaker A: Next Piece of Content There was another episode of Postgres FM last week. This one was on estimating WorkMem, but the discussion was a little bit broader than that because Nick and Michael were joined by Sean Thomas and they discussed estimating workmem and memory management in general, as well as throwing in writing extensions as well. Because I know Shawn Thomas typically has written a number of blog posts talking about how to build extensions.
So they did talk about WorkMem and how it's not the memory for all of Postgres, but it's basically a memory limit per operation or sort type operations for each query in Postgres and how a lot of people struggle to configure this appropriately. And I think they said some people use the formula of taking shared buffers divided by the number of connections and then dividing that by 5 because it can be used more than once per connection. Workmem.
Now that is a very conservative estimate, so you may want to increase that a bit more. The question is, how do you do that? He actually mentioned something interesting where he looks in PGSTAT database at the number of temp files that have been generated and the temp bytes and divides the bytes by the files to get the average size of the temp file that's been created and basically adds that as a buffer to the existing work memory to increase it to try and eliminate those, say, sort operations that are happening on disk, let them happen in memory. So I hadn't heard of that technique. That's one way to go. What I tend to do is turn on log temp files. So in the log it generates the size of the files, how many times that's happening. So you can compare that against your existing workmem and determine if you want to increase it or not. So that's how I tend to like to do it. They also talked about the role of connections and how high a connection should be and how it's really advisable to use a pooler such as PgBouncer because that also helps you deal with memory management by not having as many connections in Postgres. And of course talked about extensions a little bit. But if you want to learn more, definitely encourage you to listen to the episode here or watch the YouTube video down here.
Next piece of content, history of Postgres sharding. This is from planetscale.com and they go a little bit into the history you can see all the way back to the video game ultimate in 1997 through work that Skype did Vitess, which is my SQL sharding Instagram Citus Sharding Postgres and then of course they put Nikki on here which is their sharding solution that's still in development, hasn't been released yet but they're starting to talk about it more. We can see the Skype mention was with regard to PL proxy to be able to use multiple sharded database the Instagram logical shards and then of course Citus for scaling out. They mentioned a few Postgres compatible sharding solutions and then how they're building Nikki and of course their objective is one connection string can go to any router letting your application treat 1000 shards as one unified database. And I don't know if this is going to be open source or if it's only going to be available if you're using PlanetScale because they're saying Nikki also handles backups, restores node health insights, connection police. So it seems a lot of the planetscale infrastructures included we'll just have to see. And the last blog post is problems with large tables in Postgres. This is again from PlanetScale and they show this example that I've seen a few times in other databases where you have something like an events table growing exponentially compared to other tables in the database. And how do you deal with this? One way is partitioning so you partition that events table. Another option is vertical scaling. You just keep scaling up your box or the instance size you're using at your hosting provider. And this is the default path for everyone because it doesn't require them to do too much work other than click something.
But as they say, it's almost always a band aid solution that only hides the real problem because partitioning does take work and sharding takes work. Now, a couple of years ago partitioning was the main solution and you would rarely shard. Although right now we have three new entrants into the sharding area. We have PGDog, we have Neki as well as multiuras that are all working on the sharding. So if this continues to mature, maybe this becomes a comparable level of work to partitioning. I don't know, we'll have to see. But I have said in the past that choosing the sharding route means you have to become a better shepherd of multiple database servers. Whereas if you choose partitioning you just have to deal with this one large table. You just partition this one table, you can leave the rest of your tables unpartitioned and you can still only have your primary and a few replicas. You don't suddenly have to have thousands of databases you have to be the shepherd of, because that's the trade off you're making if you choose sharding versus something like partitioning. But they go into the problems that a large table causes. It takes forever to vacuum. It's not as efficient with repacking in terms of reclaiming space, although be aware it rarely reduces the size of the table it's talking about internally. Slow queries on a large table hold connections longer. It can be slower to do backups and recovery. Maybe you have a little bit too many indexes on that large table to make it efficient. If you were to shard it or partition it, you wouldn't need as many indexes. And it may not necessarily be so large because it has many rows, but maybe it's has a lot of wide columns. But of course they say at the end here sharding is the solution to large tables. I don't know if I agree with that. I think partitioning is the best solution when you're dealing with a situation like this. Sharding can help you make everything smaller, unless they potentially have some secret sauce I don't know about. But if you want to learn more,
[00:15:15] Speaker B: definitely check out this blog post.
[00:15:17] Speaker A: I hope you enjoyed this episode. Be sure to check out scalingpostgres.com, where you can find all the links for the content discussed, as well as sign up to receive weekly notifications of each episode there. You can also find an audio version of the show as well as a full transcript. Thanks. I'll see you next week.
