Episode Transcript
[00:00:00] I heard of Planetscale years ago, but I never looked into them because from my understanding they were MySQL only. So as a Postgres user and DBA, I really didn't have any reason to investigate them too much.
[00:00:16] But that all changed today when I found out that Postgres is coming to PlanetScale. And if you ever wondered what PlanetScale was or how it works, we're going to cover a number of blog posts that talk about what they're doing. Also, be sure to stay till the end of my consulting corner if you want to keep up to date with how the summer of upgrades is going, but I hope you, your friends, family and co workers continue to do well.
[00:00:42] Our first piece of content is announcing PlanetScale for Postgres. This is from PlanetScale.com and at this point it is in a private preview.
[00:00:52] And what caused them to actually start supporting Postgres in addition to MySQL is basically customer demand because they announced PlanetScale Metal. So I'm not really very familiar with PlanetScale's architecture at all. My vague understanding is that they use Vitess in front of MySQL, so it's kind of a pooler sharder in front that does all of the logic. So unlike something like Aurora DB or AlloyDB that Google does, or even Neon, where they've totally rebuilt the storage infrastructure, here it looks like they're achieving scale by using tooling in front of the actual database systems and using I guess basically the native replication to keep copies of the data in place on multiple instances. And because they're doing this, they can use bare metal instances that have local instruments NVME SSD drives that gives them amazing performance.
[00:01:51] And apparently when they started offering this for MySQL they had, quote, an immense number of companies say, hey, can you do this for postgres? So when they started navigating the landscape, they heard stories of regular outages, poor performance, and high cost. I haven't heard too much about poor performance unless they're doing something on the application side or regular outages. I'm not familiar with that as well. But high cost is the number one thing I hear about when you're moving to a hosted postgres platform.
[00:02:24] And this is where the planetscale metal starts becoming very interesting. And this paragraph talks about some of the architecture of PlanetScale. It uses real postgres running a proprietary operator, so presumably Kubernetes. So because it's Postgres metal, they are using local NVMe SSDs on the instances. They do have a proprietary front end technology, a proxy layer they call P.S. bouncer for Planetscale Bouncer. Presumably that helps them with the connection pooling and I guess query buffering as well. And they also mentioned Vitess for postgres and they said Vitess isn't coming for postgres, but they're looking to build something new that basically replicates what the Test does for MySQL and that's something presumably that's in process.
[00:03:13] Now the next blog post I'm going to cover is actually benchmarking Postgres again by PlanetScale and they actually did benchmarks against all these different providers. Amazon Aurora, Google AlloyDB, Neon Lake Base, Supabase, Crunchy Data, Tiger Data and Heroku PostgreSQL.
[00:03:32] And you can see in the performance chart here in terms of queries per second. PlanetScale of course comes out on top, which you would expect.
[00:03:39] But I think the reason why is because they're using these very fast local NVME drives as opposed to so many of these other providers are using network attached storage. Now in terms of doing these benchmarks, they used a latency benchmark where they basically sent select one from another instance in the same region to check latency. They did the TPCC benchmark to assess OLTP type queries and then an OLTP read only as well. They talked about some of the standards they used in terms of measuring it and emphasized here all of the products we compared with use network attached stored for underlying drives. So unlike what PlanetScale is doing. And they talk a lot about their methodology here, but they also have an invitation for you to provide feedback on their benchmarking philosophy if you think, hey, this is skewed some way so you can reach out to them.
[00:04:33] And then the links up here are detailed comparisons for the other platforms they measured the benchmarks against. So if you click on this you now see the PlanetScale versus Amazon Aurora benchmarks. What they compared the details and then the performance differences. So they really spent a lot of time on this. This is pretty impressive. Now I also dug into this next post announcing planetscale Metal. This was back in March, but this is their announcement of PlanetScale Metal for their MySQL so it goes into detail of what metal is doing. Again, it's basically locally attached NVME SSD drives and they have some customer quotes here, but I found this post more interesting. Planetscale Metal, there's no replacement for displacement and they talked about why network attached storage isn't ideal for databases. It's basically the latency and you also get lower throughput. But there's also the immense cost involved because network attached storage gets really expensive, particularly when you're talking, for example, in Amazon with the iO2 block storage, that can get really expensive compared to local NVME SSDs. So they show an example here of someone migrating from a network attached storage volume to metal and 99th percentile queries drop from 9 milliseconds to 4 milliseconds. And then they do address the durability issue because they say the advantage of the network attached storage is that you can attach the storage to another instance if there's a problem on that instance. But they said when you're running multiple replicas that can take over in the event a primary fails, that should have you pretty much covered. And they give really low percentages about different risks that can happen in this case even though you aren't using shared storage. And of course they talk more about the price differences as well, and especially that the storage you get on Amazon ebs, for example, cannot be discounted by reserved instances or savings plans, whereas the instance can leverage that. So if you have the drives on that instance, they can take advantage of that, whereas the network attached storage does not. So that's another benefit of having this metal attached storage. So overall, I was really intrigued by all of this information. I definitely encourage you to check this out to see if this may be something you're interested in.
[00:06:50] Next piece of content. How often is the query plan optimal? This is from Vonder me, and he's talking about the situation where you have a particular query and you trust that the optimizer is going to give you the optimal plan to basically execute that query. It can even be as simple as choosing whether to do an index scan or just do a sequential scan against the table. And he goes through a few examples here at different levels of selectivity or percentage of pages, what particular plans are chosen, and areas where the planner actually gets it wrong. And this is a very simple situation. For example, some index scans take longer than the sequential scan when you actually run it, even though the plan predicted that the index scan would be faster than the sequential scan.
[00:07:36] But of course this also has dependencies on how you configure your system.
[00:07:40] And then that's a simple example what happens when you're joining five different tables and doing grouping and ordering and all of that. It can be a real challenge to make sure that the optimizer is choosing the best plan every time. And he talks about this problem in general and how even though we are using a cost based optimizer that looks at the data, looks at, collects statistics to try to make the best type of plan, he still thinks it's the best way to go. But he brings up the point that it will not always choose the best plan. And there can be variances. But check this out if you're interested. Next piece of content avoid UUID version 4 primary keys this is from andyatkinson.com and I wholeheartedly agree with this. I know I've talked about this a ton on scaling postgres and personally I always use integers. Unless there's a use case I need to use uuids.
[00:08:34] But in the Future using UUID version 7 is definitely the way to go because that is time ordered. But let's check out some of the things he mentions here. And he mentions randomness as the issue that's the core part of the poor performance and why you would want to generate UUIDs is maybe you're generating them outside of the database. And he says some people think they're more secure, but he says not really. But even if you are using integers there is a way to generate obfuscated values. And then some of the reasons against UUIDs is that they consume a lot of space like they're four times the size of a regular integer, twice the size of a big int and because of that randomness they add insert latency due to index page splits, fragmentation and you have excessive I O lookups as well. Now version 7 UUIDs deal with some of the randomness, but they still are double the size of like a bicint for example.
[00:09:27] And he looks at some stats here and basically the size of them does impact performance anyway but he talks a lot about this and then his recommendation is basically stick with sequences, integers and big integers and if you need to use version 4 alternatives use something like version 7, which is kind of what I mentioned. But if you want to learn more, check out this blog post.
[00:09:49] Next piece of content Building Replication Safe LSM trees in postgres this is from paradedb.com and they are the creators of PG Search, an extension that uses elastic like search index and query engine within postgres. And this talks about how they got log structured merge trees or LSM trees to work with physical replication. So they talked a little bit about what LSM trees are. So writes go into a mem table once that's full it gets flushed into something called an sstable, and then eventually that gets compacted as well and pushed down. But the particular structure of it made it hard to transmit this data via the wall. And they talked about what changes they implemented to be able to safely transmit these structures from a primary to a replica. And if you're interested in the details about all of that, feel free to check out this blog post. Next piece of content streamlining how your code interacts with Postgres this is from enterprisedb.com and this is a post that advocates not using an orm. And he is using GO as an example and he says this can frequently be the case in Go that they say just use SQL, but this post says you don't have to use an orm. You can write your SQL, put it in a string, send that query to the database, get the data returned, and convert those rows into structures. So this is a function that does that, and this is all without object instantiation that you would need to use for an orm. And I will say because I use Ruby on Rails, I use the ORM most of the time, but I have no qualms about dropping to the lower level PG library and just bypassing the ORM and interacting with the database directly when it makes sense. But check this out if you want to learn more.
[00:11:42] Next piece of content operating PostgreSQL as a data source for analytics pipelines Recap from the Stuttgart Meetup this is from dataegret.com and this is a presentation about using Postgres as the data source for clearly analytics. And they talk about all sorts of different tools in setting up change, data capture and different ways you can get data from postgres into different analytics tools. So if you want to learn more about that, you can check out this presentation.
[00:12:10] Next piece of content the PGTDE extension is now ready for production. The this is from percona.com and this allows you to transparently encrypt data in Postgres, but it does require patched postgres. So they say it is available as open source, but it's in the percona distribution for PostgreSQL. So it is patched Postgres with the PGTD extension. Now they are working to contribute these changes upstream so it doesn't require patch postgres but but it may take a while for that to happen of course. So you can check out this announcement if you're interested, related to that performance test for Percona Transparent Data Encryption. This is from andreascherbond La and I think this was sponsored by Percona, but he did a performance test of TDE and he found that the transaction per second for read only tests was pretty much identical from native to TDE or TDE with the wall. Whereas when looking at write, the TDE did have an impact on wall generation, so it was up to 20% slower with read write workloads. And he does describe how installing the Percona distribution for postgres and getting the extension set up was. And he transparently mentions all how this arrangement and the blog post was conducted. But check this out if you want to learn more. Now it's time for my consulting corner. Well, the summer of upgrades continues, so I actually have four clients I'm working with to upgrade their databases. If not this summer, then in the early fall.
[00:13:52] One of them was just completed this morning, so that's great. But I still have three to go, so that's just a quick update. I hope your summer is going well.
[00:14:04] I hope you enjoyed this episode. Be sure to check out scalingpostgrads.com where you can find the links to all 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 and I'll see you next week.
