background

Postgres Best Practices? | Scaling Postgres 357

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

In this episode of Scaling Postgres, we discuss best practices, caching monitoring statistics, a new vector extension called VectorChord and the importance of monitoring wait events.

Content Discussed

YouTube Video

Podcast Audio

Transcript

Best practices is a loaded term. If someone asked me what are the best practices with regard to software development, I would have to think long and hard about that. Similarly, if someone asked me what are the best practices with regard to PostgreSQL, I would have to take a really long time to come up with everything I could think about because there's just so many areas. Do you mean best practices with regard to designing your schema or with regard to security or or backups or configuration? So I don't think I will ever have a blog post or even a show that just talks about best practices for postgres because it's too huge a topic. I might do one covering a very small area of Postgres, but we're going to be talking about some best practice content this week and be sure to stay till the end where my consulting corner talks about the importance of weight events. But I hope you, your friends, family and co workers continue to do well.

Our first piece of content is actually an episode of Postgres FM where they talk about “Best Practices”. So even though the show is 41 minutes, they could have probably talked for triple that amount to be able to try to cover as much area as possible. But what prompted this show is actually this blog post 7 crucial PostgreSQL best practices so when I saw this episode before watching it I said I'm just going to check out this blog post because maybe it's really great and here it is and I started reading it and I started thinking this seems like something I might get out of ChatGPT or some LLM because when it talks about performance optimization and query performance it says implement composite indexes for multi column queries. So it just seems like a lot of matter of fact stuff statements and I considered going through this blog post in detail but it would just take way too long. But when I looked back at the postgres FM I noticed that it was actually on Hacker News. And one of the first comments of Hacker News is that this looks like it was generated by an LLM. But if you are looking for some best practices, I definitely encourage you to watch this episode as opposed to look at the blog post. Although you can look at it in conjunction with watching it. If you want my perspective on best practices, I think you should check out the courses section on my website@scaling postgres.com.

Next piece of content “stats_fetch_consistency: caching PostgreSQL monitoring statistics?” this is from cypredeck postgresql.com and I had never heard of stats fetch consistency, and Lorenz, who's the author, said he had never heard of it either. And it basically defines how often your stats are kept up to date in postgres. So he shows an example here where he's looking at the PGSTAT checkpointer. Then he runs a checkpoint and he queries that stats view again and it gives the same output. So it should have gone from number requested from four to five, but it stayed at four. But then if he does a commit then, then now it shows that the count is at 5. So this behavior, this apparent cache behavior, is based upon the stats fetch consistency value. And these are the different system statistics that are impacted by setting this value. By default, it is cache. And this value is cached for the duration of the transaction. You can set it to none, which means these stats aren't cached at all. Or you can set it to snapshot. So basically it reads the data for all the objects in the database and keeps them cached for the duration of the transaction. So this is just something to be aware of if you're using these stats counters and they're not incrementing the way that you would expect them to.

Next piece of content “Waiting for PostgreSQL 18 – Allow parallel CREATE INDEX for GIN indexes”. And this is from depeche.com and this is a great addition, more parallelism. And he tested it out and the interesting thing that he observed is that the parallel index creation actually resulted in a smaller index compared to when the index was run serially. And when he used the index, he got the right results back. But then in the comments here, Matthias mentioned that the way the parallel index build works is that it prearranges the data in a way that's efficient to be able to insert into the index, which results in a smaller index. So that's a great benefit as well, in addition to taking less time because it's building in parallel. But if you want to learn more, check out this blog post.

Next piece of content “pgvector, a guide for DBA – Part1: LAB DEMO” this is from dbi-services.com so this is just a blog post to get started with, essentially PGvector. And it's a lab demo to work through the process of working with embeddings and AI, etc.

And this is followed up by another blog post, part two of it, where it covers “indexes” and applying HSNW indexes as well as IVF flat indexes, and even covering some disk and algorithms as well as binary quantization so just another set of blog posts to kind of get started with AI if you're interested in that.

Next piece of content. “VectorChord: Store 400k Vectors for $1 in PostgreSQL”. This is from blog VectorCord AI and VectorCord is a new extension to use vectors and embeddings within postgres. And they're specifically using some different types of quantization as well as IVF flat indexes. So not HNSWS to store a lot of embeddings very efficiently within postgres. And then they even have another blog post that looks at it from the performance side.

So “Vector Search at 10,000 QPS in PostgreSQL with VectorChord”. So they are showing the significant cost advantage of VectorCord compared to PGVector, but also with regard to performance as well to achieve 10,000 queries per second. So if you're looking to push the limits of of embedding, query and storage, maybe check out this new extension.

Next piece of content. “Introducing pgNow: A free, point-in-time diagnostic tool for PostgreSQL” this is from softwareandboost.com and this is a free tool released for doing monitoring of PostgreSQL servers. And they're essentially taking what already exists in PGStatActivity and PGStat statements and putting a GUI on top of it, as well as some other areas as well. So if you're interested in that, you can check out this blog post. This had a lot more detail than the blog post mentioned last week or week before last.

Next piece of content, “Automated Management of Extended Statistics in PostgreSQL”, this is from Danolevo.substack.com and this is an experiment where he's trying to have extended statistics being added automatically. And as a reminder, postgres does track statistics per column per table, but it does not store statistics relating to columns to one another. But the planner can take advantage of that if you define those. The problem comes with identifying what related columns should statistics be collected for. That would actually result in a better plan. And he goes through thinking through that and coming up with some different options. So you can check out this blog post if you're interested.

Next piece of content. The PostgreSQL Person of the Week is “Doug Ortiz”. If you're interested in learning more about Doug and his contributions to postgres, definitely check out this blog post.

And in this week's consulting corner, we're going to be talking about the importance of wait events. So I was contacted by a client and they said hey, we've got this query that's really slow. It was an update statement. So I said okay, what's the where clause? Is there an index being utilized? And it happened to be using the primary key and the where clause. So I said okay, well that's not the issue. Is there some type of locking going on? So could some other query be holding a lock or something? Locking the table temporarily that would prevent the update from happening. And that wasn't the case. But once I looked at the wait events which can be found in PGSTATactivity or if you're on RDS, Performance Insights gives you those wait events. And for this particular update statement there was a ton of of time being spent in client read. And what that basically means is that the PostgreSQL process was waiting for the client to do something, so it was within a transaction and it was preventing other queries from happening to that particular row, but it was the client actually doing so many other things that was causing the query to run so slow. So the postgres process was fine, he was waiting for the next set of instructions or to close the transaction, but but it was the client holding things up on its side. This could have also been possibly found by looking for queries that are idle in transaction for too long, which I think different monitoring tools show that to you for all of the database and you can get it individually looking at PGStatActivity. But dedicated monitoring tools like Performance Sense Likes, for example does give you a listing of the top queries and the weight events associated with that particular query query, but just another best practice to consider when you're looking at why a query is slow. 
 

episode_image