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:
- A table is large and queries take a long time
- 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>)
Top comments (0)