Types of Indexes in PostgreSQL

Introduction

If you’re interested in building apps that are powered by a database, it’s important to have a solid understanding of indexes and how they work. An index is a type of database structure used to speed up queries. A database index is a bit like the index in a book– for example, if you wanted to find any references to the topic of “PostgreSQL” in a book, it’s a lot easier to consult the book’s index and find the page numbers of those references than it is to flip through every page of the book.

There are several types of indexes in PostgreSQL, and they each work a bit differently. In this article, we’ll look at each of these index types and discuss common use cases for each one.

Prerequisites

If you’d like to follow along with some of the examples we present in this article, you will need to have PostgreSQL server installed and configured on your machine. The process needs to be running in the background. Linux and Windows users can download PostgreSQL here.

PostgreSQL Index Types

PostgreSQL provides a number of different index types to users. Your choice of index for a given table will depend on a few factors:

• The data within the table
• The data type
• The lookup types to be performed

Shown below are the various types of PostgreSQL indexes:

• B-tree
• Hash
• GiST (Generalized Inverted Search Tree)
• SP-GiST (Spaced Partition GiST)
• GIN (Generalized Inverted Index)
• BRIN (Block Range Indexes)

B-Tree Index Type

The PostgreSQL B-tree index creates a self-balanced tree– in other words, it sorts itself. It will maintain its balance throughout operations such as insertions, deletions and searches. Using a B-tree index speeds up scan operations because it doesn’t have to scan pages or records sequentially in a linear fashion.

When to Use a B-Tree Index in PostgreSQL

A B-Tree index is a safe choice to rely on when you’re first planning and creating your database tables. This type of index is highly efficient and works especially well for operations performed on large blocks of data.

NOTE : When you perform a simple CREATE INDEX operation, a B-Tree index will be created for you by default.

Using B-Tree in Query Planner

When performing comparisons on index columns that involve various operators [<, <=, =, >=, IS NOT NULL, IS NULL, IN, BETWEEN], the PostgreSQL query planner will use the B-Tree index.

PostgreSQL Hash Index Type

A PostgreSQL Hash index can perform a faster lookup than a B-Tree index. However, the key downside of the Hash index is that its use is limited to equality operators that will perform matching operations.

In earlier versions of PostgreSQL (9.X and below), hash indexes were not WAL-logged, or crash-safe. This meant that whenever a server failed or crashed, the hash indexes needed to be recreated using REINDEX.

When to Use Hash Index in PostgreSQL

When it comes to space, the Hash index is superior to the B-Tree index, since a Hash index is flat in structure and a B-Tree, as the name implies, has a tree structure. Depending on circumstances, this gain in space can be a real benefit.

In summary, if you need a speedy lookup while utilizing less space, a Hash index can act as an excellent alternative to a B-Tree index.

Creating A Hash Index in PostgreSQL

To create a Hash index, we use the PostgreSQL statement CREATE INDEX statement followed by the HASH index type within the USING clause. An example of this statement is shown below:

PostgreSQL GiST Index Type

The GiST, or Generalized Search Tree index type is particularly efficient against data where there is some degree of overlap among row values within the same column.

When to Use GiST Index in PostgreSQL

GiST is most beneficial when it’s used for:

• Full-text search
• Geometric types

However, there are few caveats to keep in mind when using a GiST index. This index types is commonly known for being lossy, which means it can returns false matches. This doesn’t mean that you’ll actually get incorrect results– it just means that PostgreSQL will have to work a bit harder to process and verify results before returning them to you.

PostgreSQL SP-GiST Index Type

A SP-GiST index, also known as a space-partitioned GiST index, allows the development of a variety of non-balanced data structures by using partitioned search trees.

When to Use SP-GiST Index in PostgreSQL

The SP-GiST index is particularly useful when used with data that has a natural grouping of elements and a non-balanced tree structure. A good example of this kind of data is phone numbers– a typical phone number has three digits for the area code, three more digits for the prefix and finally four digits for the line number. There will be a degree of grouping for the three digits of the area code and the three digits of the prefix; however, some area codes and prefixes will be present in much greater numbers than others, resulting in an unbalanced tree. This clustering and unequal distribution of the data presents an ideal use case for the SP-GiST index.

PostgreSQL GIN Index Type

A GIN index, also known as a Generalized Inverted Index, is an index type that is most beneficial when a datatype has multiple values within a single column.

Shown below are the most common datatypes used with a GIN index:

• JSONB
• Array
• Range Types
• hStore

When to Use GIN Index in PostgreSQL

A GIN index is a good choice when you want to search for specific text within a large set of documents; for example, it would help optimize a search function for a customer name that queried a table of a million customer records. A user can simply type three letters of the customer name in the search box and the GIN index will do its magic, quickly and efficiently returning all the names containing those three letters.

PostgreSQL BRIN Index Type

BRIN or Block Range Indexes can be used for many of the same use cases as SP-GiST indexes, as they are most effective when used against larger data sets where a natural clustering of data is present. A BRIN index is far less costly in terms of maintenance compared to a B-Tree index.

When to Use BRIN Index in PostgreSQL

A BRIN index is the right choice for very large datasets that are ordered, such as zip codes or dates, because BRIN indexes allow you to set aside or skip unnecessary data.

Conclusion

When you’re working with data in PostgreSQL, you can optimize the performance of queries by using many different types of indexes, including the default B-tree index. In this article, we provided an overview of these types of indexes in PostgreSQL and described potential use cases for each one. With this information to guide your decision, you’ll be able to choose the most appropriate indexes for your own database tables.