flâneur

Some indexing best practices - pgMustard

pgmustard.com · 1,222 words · saved by 1 readers

We all know that indexing is important, but it can be difficult to know where to start. In this post, my aim is to collate some of the best advice I’ve seen for indexing Postgres, and simplify it for folks just getting started. In a previous post, we covered a very basic introduction to indexing, so I won’t go over that again here. However, I will start with a super quick reminder on why we index. Postgres is perfectly able to run queries without indexes, by scanning tables and filtering out results. But, as our data grows, this gets more and more inefficient, and as a result, slower. Indexes allow us to look up the rows we need efficiently, and therefore quickly. With that in mind, let’s take a look at some do’s and don’ts. If indexes are so useful, why don’t we add them to every column? There are a few good reasons, but a big one is that indexes add overhead to writes. Not only do indexes need to be kept up to date when we write to the table, but they can also prevent optimizations l

Cover photo: Clark Young We all know that indexing is important, but it can be difficult to know where to start. In this post, my aim is to collate some of the best advice I’ve seen for indexing Postgres, and simplify it for folks just getting started. In a previous post, we covered a very basic introduction to indexing , so I won’t go over that again here. However, I will start with a super quick reminder on why we index. Why index anything at all? Postgres is perfectly able to run queries without indexes, by scanning tables and filtering out results. But, as our data grows, this gets more an

related reading