background

Faster Writes With ZFS? | Scaling Postgres 342

Join Over 1,000 Engineers & Get New Episodes Weekly!

In this episode of Scaling Postgres, we discuss the read and write performance of Postgres on ZFS, handling URL text identifiers in the database, denormalization and a new pgvector release.

Content Discussed

YouTube Video

Podcast Audio

Transcript

Have you ever run Postgres on the ZFS file system? Personally, I haven't run any of my own postgres installations on ZFS, but I do have at least one client that is running some database systems on ZFS and the main reason is because of the built in compression that it has because it enables you to shrink down your data volumes quite significantly. But of course when you're doing compression there can be some downsides, so we'll talk about some of that this week, but I hope you, your friends, family and co workers continue to do well. 


Our first piece of content is “PostgreSQL: Maybe we should give ZFS a chance (1) – baseline” this is from dbi-services.com, and he said he started playing around with FreeBSD as opposed to Linux and he noticed that the performance was a little faster. So he says, hmm, maybe I'll try comparing the built in files system, which currently is ext 4 to ZFS to see how it compares. So he did this on Debian and he's using PostgreSQL 17. He does two GP3 disks, so he's doing this on Amazon. One disk has ZFS, one has ext4 and he installs Postgres on both of them. Now he did say there were some license incompatibilities trying to run OpenZFS on Linux, so he didn't address the licensing issue. That's something you will have to explore if you're considering trying to run zfs. Or maybe y-ou need to use a different version. But this initial blog post just sets everything up to do the actual test, so you can feel free to read through this. 


However, the next post “Maybe We Should Give ZFS A Chance Part 2” Is Testing what the performance is, so I should mention the ZFS volume is using ZStandard compression, so it is using compression and it's using ZStandard and as a result of that the ext4 file system, the empty cluster was 39 megabytes, whereas the empty ZFS cluster was only 4.7 megabyte. So it already shows some of the space saving that's possible. So we loaded some Data in the ext4 cluster and the ZFS cluster using PGBench and what he found is that it was faster with ZFS. So 382 seconds to process versus 267 seconds to process for ZFS. Some of the reason for this is that maybe because ZFS allows you to turn off full page writes in terms of the wall, it's not necessary because you're not going to get a torn page with it. But in his test definitely the writing was more performant on ZFS. So he said, all right, well let's take a look at the reads and this is the TPS summary of the reads. The ext4 you can tell was around 850 transactions per second on average, whereas the ZFS was around 621 transactions per second. So the reading was slower. So apart with the licensing issues, which you will definitely have to address based upon what operating system you're going to be using with ZFS, it appears that the advantages are, at least from this test, faster write performance and a smaller database size. The disadvantage is slower read performance. I'll also mention that one of my clients that is running Postgres on ZFS, the disadvantage that they've seen with it is a replica Keeping up with the primary so when doing streaming replication, right now Postgres only has one apply worker and it was having difficulty keeping up with the wall stream from the primary. So this was seen on ZFS, but it wasn't seen when using ext4 volumes. So ZFS could definitely be an interesting option based upon your use case. And if you want to learn more, you can check out these blog posts.


Next piece of Content “Text identifiers in PostgreSQL database design” this is from notso.boringsql.com and typically in web applications today, usually the tables that are created have numerical primary keys, integers, bigints, things of that nature. Therefore a lot of the URLs that are constructed have numbers in them. So this is order 1, 2, 3 or this is product 3,4,5. But there's some people that don't really like that because they feel it may give data exposure or security issue because people can know how many records are being created. Like if you create a new order and you see the order number is 50,000, you know there's about 50,000 orders in the system. It also brings up privacy and confidentiality questions as well as just not being very descriptive. It's just a random number, there's no meaning built into it. That can sometimes be an advantage when giving people URLs. So this blog post talks all about using text based identifiers for these URLs to identify them and efficient ways to do that. Now the blog post talks about how integers are definitely more performant than other solutions like falling back to text because for example, integers are typically smaller than the text based alternatives as well as usually being more performant. You could use UUIDs to address some of these concerns. But again, those are awfully long numbers to be put into a URL, so a bit difficult to work with. He does give some examples of how you can set these identifiers up as surrogate keys. Basically separate dedicated text columns that you put a separate index on so you can retrieve whatever pages requested through the browser. And he has different techniques of structuring that, and I'll let you look that over. But I found this particular part of the blog post super interesting, and it's about reversible text IDs, and he's specifically referring to squids, which is a project that takes a number and converts it into a text representation. So this could be order this text string here and your system would convert that back to the number internally and do a query against the orders table to find the order. So I find this super interesting, particularly if you have concerns about privacy and confidentiality or security and you don't want people to know your numerical IDs you're using for a URL. Using a technique like this is great because you don't have to maintain a separate column in the database. It doesn't need to have its own dedicated index. The application server itself has this library that converts the numerical identifier to this string. And then when a URL is then read to look a particular record, it converts the text string back into a number before it sends the SQL query. So this should give you really great performance and give you obscure IDs. So go ahead and check the link to this project if you want to learn more about that. But if you want to get the details with regards to all the techniques he suggests, definitely encourage you to check out this blog post.


Next piece of content. There was another episode of Postgres FM last week. This one was on ”Denormalization”. So Nikolai and Michael talked about denormalization, which is basically doing the opposite of normalization. And the primary reason that you would normally do this is for performance because when you start having to join 2, 3, 4, 5, you know, 10 tables together at once, and you want really fast real time queries, sometimes that can bog things down. And a way to optimize it is to denormalize the data. Basically combine data into one table or even copy columns that should reside in table X, but maintain another copy in table Y so that you don't have to necessarily do a join. The data is right there and ready to be queried. And to my thinking, denormalization is a way to match the stored data format to the data format that you're presenting on the screen to a user, for example, the less manipulation you have to do, the better performance you can generally get. But they discussed all sorts of different techniques. You can do denormalization. They talked about using triggers, which is basically an asynchronous way to keep data up to date in another table. Or you could do it asynchronously through jobs that run, for example, in your application framework. Basically, they covered why you would want to do denormalization and covered some specific cases. So if you want to learn more, you can listen to the episode here or watch it on YouTube down here. 


Next piece of content “pgvector 0.8.0 Released!” so this release has some enhancements to query performance and usability when using filters, specifically doing filtering in the where clause. There's cases where queries with PGvector may not use an index if the data is of a low size, or it might use a B tree index as opposed to an HNSW index or the IVF flat index. But there's also been performance improvements for searching and building HNSW indexes as well. They also added a new feature called iterative index scans. So approximate nearest neighbor searches actually do the filtering after the index has been fully scanned. And depending upon how you've configured the index, you may not have enough records for what you're looking for. And it's an issue they are calling over filtering or not returning enough results to satisfy the conditions of the query. So basically this iterative scan allows you to pull out enough records for a given query. Now related to this, there's also a blog post from thenile.dev they go into more detail about these iterative index scans. So if you want to learn more about it in detail, I definitely encourage you to check out this blog post as well.


Next piece of content “Accessing Large Language Models from PostgreSQL”, this is from crunchydata.com and this is similar to other extensions we've heard about. This One is the OpenAI API access extension and a basic basically performs three functions. It returns a list of models being served by whatever API you're using. It's using the OpenAI API, but that's because so many services are actually mimicking the OpenAI API, so if you write it for their API, you can still interface with it. The second thing it does is it returns the text answer to the prompt evaluated using the context. So basically you send some context text and a prompt text and it will return what the text output from the LLM was and then thirdly, it returns the vector embedding of the prompt text. So if you want to do semantic search, I imagine this would be the function that would do that for you. So this is a postgres extension, so it allows you to do each of these functions within postgres, and they do say be sure to install the HTTP extension as well, because you'll need it to interact with whatever API endpoint you're using. And you configure this extension using set command so you can see you're setting the API key, whatever URL you're going to use, as well as the particular models that you're using. And they give you an example of doing a sentiment analysis based upon people making comments. So this is another extension to interface directly with different LLMs. And feel free to check out this blog post if you want to learn more.


Next piece of content “8 Steps in Writing Analytical SQL Queries”, this is from crunchydata.com and if you ever have to write a big hairy SQL query, this breaks down pretty well through eight steps on how to do that. What you need to think about, what you should focus on 1st, 2nd, 3rd, 4th, etc. So I thought this was a pretty interesting blog post and I encourage you to check it out if you want to learn more. 


Next Piece of Content “Deploying the Zalando Kubernetes operator” this is from cybertec-postgresql.com and I know I've talked a lot about the cloud native PG operator well this is Zalando, which is one of the earlier Kubernetes operator and in this one CyberTech is giving it a spin on Kubernetes. So check it out if you want to learn more. 


Next piece of content is actually a YouTube channel and it's all the videos from the PostgreSQL conference in Europe in 2024. So go ahead and check out this video content if you want. 


And the last piece of content is “Hacking Postgres: Heikki Linnakangas”. This is from tembo.io and this is their podcast. So if you want to learn more about a Neon Co founder, definitely encourage you to check out this piece of content. 


 

episode_image