PostgreSQL Index Types

Postgres Index:

Indexes are their own data structures and they’re part of the Postgres data definition language (the DDL). They're stored on disk along with data tables and other objects.

There are five types of PostgreSQL indexes, which are also called methods, because they define the way each particular index handles its task. They also determine the syntax specifics. These five types are as follows:

1. B-tree indexes are the most common type of index and would be the default if you create an index and don’t specify the type. B-tree indexes are great for general purpose indexing on information you frequently query.
   B-Tree is the default index type for the CREATE INDEX command in PostgreSQL. It is compatible with all data types, and it can be used, for instance, to retrieve NULL values and work with caching. B-Tree is the most common index type, suitable for most cases.

2. BRIN indexes are block range indexes, specially targeted at very large datasets where the data you’re searching is in blocks, like timestamps and date ranges. They are known to be very performant and space efficient.
   BRIN (Block Range Index) applies to large tables where specific columns have a natural correlation with their physical location in the table. A block range is a group of pages that are physically adjacent in the table; BRIN indexes store summary information—page number along with minimum and maximum values—for each block range.

3. GIST indexes build a search tree inside your database and are most often used for spatial databases and full-text search use cases.
   The GiST (Generalized Search Tree) index allows using the tree structure to index schemes for new data types—for instance, geometric data types and network address data. GiST is also useful if you have queries that are not indexable with B-Tree. It is applicable for full-text search as well.

4. GIN indexes are useful when you have multiple values in a single column which is very common when you’re storing array or json data.
   GIN (Generalized Inverted Index) is suitable for mapping multiple values to one row. The most common case for applying the GIN index comprises operations with data types such as arrays, range types, JSON, and also for full-text search. Note that the GIN index will be slower for INSERT and UPDATE operations

5. Hash is a specific index type applied only if the equality condition = is being used in the query. It is called hash because it stores a 32-bit hash code derived from the indexed column value. Hash indexes are rarely used in PostgreSQL because it is necessary to rebuild them manually after crashes, and they can cause issues during transactions.

Want to extract all the benefits of PostgreSQL along with Graph features look at:

Apache AGE
Apache AGE Github