loading...

Indexing Databases – A Postgres Example

stephencweiss profile image Stephen Charles Weiss Originally published at stephencharlesweiss.com on ・2 min read

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

Discussion

pic
Editor guide