Index

Indexes are data which has information where the data is located, this takes up more space on the database server, Indexes are built on B Trees , all the data is available in such a way that the look up time is reduced, this is especially useful when you need exact matches or prefix based search, the indexes use hashes to for exact matches in the database during the search and completely unusable for prefix matching, The database decides what function or type of INDEX has to be used with the column, such as for a PRIMARY KEY Database Constraints the index uses hashing by default and for a UNIQUE constraint it would use a B-Tree, at the end of the day all of this is automatically managed by the Database software

When using indexes with TEXT data there is a very high possibility that the entire column can be duplicated in the INDEX as well, this results in a INDEX size sometimes on par or greater than the data itself.

Using Explain Analyze statements we can see the query performance with and without an INDEX and the type of the Index as well.

B-Tree indexes are good for sorting, exact match lookup and prefix matching, these are not good for non-prefix matching, they don’t bring any value or performance to the table.

Hash Indexes are good for exact matches

When using a index it is also important to know what indexing strategy to use. for example using an index speeds things up, but we can also fine tune the this process by creating a separate column just for the Hash value and use this column for the search which further speeds things up!

Here are some of the types of Indexes we have in PostgreSQL

• B Trees Index - The default Index for most applications
• Hash Index - Quick Lookup of long Key Strings
• Block Range Index - BRIN - Smaller / Faster if data is mostly sorted
• Generalized Lookup Index - GIN - multiple values in a column
• Generalized Search Tree - GiST
• Space Partitoned Search Tree- GiST

Read more about PostgreSQL Indexes

Inverted Index

Inverted Indexes are used primarily for searching and building search engines, it is usually used with TEXT

Inverted Index takes a bunch of documents or data and splits them into keywords and ranks them in order to find them