Index in PostgreSQL

There are different types of index in PostgreSQL. They are below:

1. B-tree
2. Hash
3. GiST
4. SP-GiST
5. GIN
6. Single Column Index
7. Multi Column Index ( Composite Index)
8. Functional Index
9. Index on Foreign Key
10. Unique Index
11. Partial Index
12. Implicit Index 

When Index is created, it blocks the table for writes and performs the entire index build with a single scan of table. Other transaction can read the table. DDL and DML operations will be in waiting state until Index rebuild is done. 

PostgreSQL supports building index without locking out writes. We need to specify concurrently option while creating index. When we use concurrently option, PostgreSQL must perform 2 scans of the table and it should wait for all existing transaction that could potentially use or modify the index to terminate. Thus, it takes more time to complete. But this allows to continue write in production, so it is helpful. But it will slow down other operation.

B-Tree - Balanced Tree are used for Equality and Range Queries. All data types and NULL values.

Hash - Hash Indexes are only useful for equality comparisons, but you pretty much never want to use them since they are not transaction safe, need to be manually rebuilt after crashes, and are not replicated to followers.

GIN - Generalized Inverted Index are useful when an index must map many values to one row, whereas B-Tree indexes are optimized for when a row has a single key value. GINs are good for indexing array values as well as for implementing full-text search.

GIST - Generalized Search Tree - indexes allow you to build general balanced tree structures, and can be used for operations beyond equality and range comparisons. They are used to index the geometric data types, as well as full-text search.

Implicit Index - Indexes are automatically created for primary key constraints and unique constraints.

Places where we should avoid Index:

1. Where there are too many updates,deletes in table.
2. We should avoid in small tables
3. Should be avoided where there are too many NULL values
4. Columns that are frequently manipulated should not be indexed.