A short review of when and how to index a Postgres database. A good complement to my post on indexing Mongo DB

Common Commands

Why Index Your Postgres Table

The two most common reasons to index a database are:

1. A table is large and queries take a long time
2. A program consistently searches the same attribute

Speed Benefits – A Practical Example

The easier of the two to see is time to execute a query.

Situation: I have a product descriptions table with 10m+ records. In order to serve my website, I need to find and retrieve descriptions for a specific description quickly.

Before adding an index, it took 28k+ ms to find my product. After adding an index, the time to complete the same query fell to 3.579ms. That’s equivalent to 99.99% reduction! Not bad for a single line of code!

Types Of Indexes

The default index type for Postgres is the B-tree, which is also the default and well suited for common situations.

There are other types available, however, including:

• Hash
• GiST,
• SP-GiST, and
• GIN

The Cost Of Indexing

While indexing has benefits, it comes with costs too.

Specifically, indexing will slow down inserting / updating records on a table.

When To Avoid Indexes

Indexing is not for every situation. Some scenarios in which you should pause before creating an index include:

• If your table is small
• Tables that have frequent, large updates / insertions of records
• On fields where null is a common value
• Fields are that are commonly updated

Other Note Worthy Points Regarding Indexes

Implicit Indexes

Implicit indexes are automatically created by Postgres for fields that have a primary key or unique constraint.

Partial Indexes

Partial indices are built on a subset of a table based on a conditional statement. Therefore, the index only applies to the rows which satisfies the conditional.

To create a partial index, use the following:

CREATE INDEX <index_name> ON <table_name> (<conditional_expression>)

Further Reading

Postgres Docs

Tutorials Point: PostgreSQL Indexes