Types of Indexes in PostgreSQL

The most common indexes know in PostgreSQL are 

1. B-tree
2. Hash
3. GiST (Generalized Inverted Search Tree)
4. SP-GiST (Spaced Partition GiST)
5. GIN (Generalized Inverted Index)
6. BRIN (Block Range Indexes)

Let's understand these in more detail

B-Tree: 

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

Hash-Index:

• A PostgreSQL Hash index can perform a faster lookup than a B-Tree index.
• Hash index is flat in structure and a B-Tree, as the name implies, has a tree structure. 
• When it comes to space, the Hash index is superior to the B-Tree index, since the 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. 
• 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 means that whenever a server failed or crashed, the hash indexes needed to be recreated using REINDEX.

GiST Index:

• It is called the Generalized Search Tree.
• This index type is particularly efficient against data where there is some degree of overlap among row values within the same column.
• It is preferred if we are using :
• Full-text search
• Geometric types
• Known Issues: 
• This index types is commonly known for being lossy, which means it can return false matches(internally not as output). Hence PostgreSQL will have to work a bit harder to process and verify results before returning them to you.

SP-GiST:

• It is called the space-partitioned GiST.
• This index allows the development of a variety of non-balanced data structures by using partitioned search trees.
• It is very useful when used with data that has a natural grouping of elements and a non-balanced tree structure. 
• This index is useful if you want to do clustering or classification operations on the data.
• Clustering and unequal distribution of the data presents an ideal use case for the SP-GiST index.

Example: Phone numbers– a typical phone number has 

• Three digits for the area code.
• Three more digits for the prefix
• Finally four digits for the line number. 

This is 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.

GIN Index:

• It is called the Generalized Inverted Index
• It is an index type that is most beneficial when a datatype has multiple values within a single column.
• It 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.

Most common data types used with a GIN index:

• JSONB
• Array
• Range Types
• hStore

BRIN Index:

• It is called Block Range Indexes.
• It can be used for many of the same use cases as SP-GiST indexes.
• These are most effective when used against larger data sets where a natural clustering of data is present. 
• BRIN index allows you to set aside or skip unnecessary data.
• BRIN index is far less costly in terms of maintenance compared to a B-Tree index.
• BRIN index is the right choice for very large datasets that are ordered, such as zip codes or dates