Basically, similar to SQL Server, the values of an index are stored on pages in a doubly-linked list. The name for the B-Tree index within PostgreSQL is a shorthand for a multi-way balanced tree index. You can use the script in SampleData.sql to load in some data. The file to start with is CreateDatabase.sql.

Let’s break each of these down and talk about them.īefore we get there though, I’m storing all the scripts I generate for these articles publicly on GitHub. This is especially true of PostgreSQL as some of the index types are very focused on specific query patterns. It is important to keep in mind that both your types of data, and your actual queries, that drive the indexes that are going to be best for your databases. When you know which index to use where, you can then drill down to better understand that index, and how best to measure the performance impacts. That article is quite a way down the track from here. I’m not yet up on performance tuning within PostgreSQL. Further, I won’t be going very far into how these indexes affects performance. However, I won’t be covering the indexes in detail. I’m hoping you’ll develop an understanding of which indexes are likely to work better in each situation. We’ll get into what the indexes are, how they work, and how best you can apply them within your databases. In this article I’m going to go over the different types of indexes and some index behaviors.

In addition, again, similar to other RDBMS, there are properties and behaviors associated with these indexes. PostgreSQL has a number of different index types, supporting different behaviors and different types of data. As with any other relational data management system (RDBMS), PostgreSQL uses indexes as a mechanism to improve data access.

Postgres unique index