A short review of when and how to index a Postgres database. A good complement to my post on indexing Mongo DB
The two most common reasons to index a database are:
- A table is large and queries take a long time
- A program consistently searches the same attribute
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!
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:
- SP-GiST, and
While indexing has benefits, it comes with costs too.
Specifically, indexing will slow down inserting / updating records on a table.
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
Implicit indexes are automatically created by Postgres for fields that have a primary key or unique constraint.
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>)