PostgreSQL Index and Types

Hey guys, In this blog I am going to explain you about PostgreSQL Index and Types.

PostgreSQL Index-

The database search engine can use indexes, which are specialized lookup tables, to speed up data retrieval. An index is simply a pointer to information in a table. An index in a database resembles a book's back matter index quite a bit.

For example, you must first consult the index, which lists all themes alphabetically, in order to find the page numbers that correspond to each discussion of a particular topic in the book.

Types of PostgreSQL Index--

There are six main index types in PostgreSQL:

1.      B-tree indexes
2.      Hash indexes
3.     GIN indexes
4.     BRIN
5.     GiST Indexes
6.     SP-GiST Indexes

B-tree indexes-

The B-tree is a self-balancing tree that preserves sorted data and permits logarithmic-time searches, insertions, removals, and sequential access.

Hash indexes-

Only simple equality comparisons (=) can be handled using hash indexes. It indicates that the query planner will take a hash index into account if an indexed column is used in a comparison using the equal(=) operator.

GIN indexes-

Generalized Inverted Indexes are referred to as GIN. It is frequently called GIN. When several values are kept in a single column, such as with the hstore, array, jsonb, and range types, GIN indexes are most helpful.

BRIN indexes-

Block Range Indexes is what BRIN stands for. Comparing BRIN to a B-tree index reveals how much smaller and less expensive it is to maintain.

With B-tree without horizontal partitioning, it was previously impossible to utilize an index on a very big table. A column with a linear sort order, like the generated date column in the sales order table, is frequently used with BRIN.

 GiST Indexes-

GiST, or generalized search tree, is its name. GiST indexes enable the construction of broad tree topologies. GiST indexes are helpful for full-text search and indexing geometric data types.

 SP-GiST Indexes-

Space-partitioned GiST is referred to as SP-GiST. Partitioned search trees are supported by SP-GiST, enabling the creation of a variety of unique non-balanced data structures. The best uses for SP-GiST indexes are for data that naturally clusters and is not a perfectly balanced tree, such as GIS, multimedia, phone routing, and IP routing.