Partial index in postgres
What is a partial index?
A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries for only those table rows that satisfy the predicate.
A partial index is useful when you are dealing with large amounts of data and lots of inserts. Any index takes a toll on insert performance (because indexes must be rebuilt), so a partial index is an ideal improvement.
A real world example:
The status is used as an enum:
Since most of our reads will deal with pending and completed events, we only want to index those: