Partial index in postgres

Photo by Kevin Ku on Unsplash

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:

    id bigint NOT NULL,
    status integer,
    start_at timestamp without time zone NOT NULL,
    end_at timestamp without time zone NOT NULL

The status is used as an enum:

  enum status: {
    pending: 0,
    completed: 1,
    closed: 2

Since most of our reads will deal with pending and completed events, we only want to index those:

  CREATE INDEX idx_events_status_not_closed ON, end_at) WHERE status <> 2;