A partial index stores entries only for rows satisfying its predicate. This can reduce index size when the useful subset is small, but it can also enforce a rule that applies only to that subset. For example, a seat may have many historical reservations while allowing at most one active reservation. A partial unique index expresses that rule at the database boundary. ...
A covering index contains the values needed by a particular query. PostgreSQL can sometimes return those values through an index-only scan, avoiding heap visits for qualifying rows. The word “sometimes” matters: having the columns is a structural requirement, while visibility information determines whether execution can actually skip the heap. An Index Only Scan label alone does not prove zero table access. ...
An index is a bet: you pay write and storage cost up front so reads can skip most of the table. When a query genuinely needs most of the table, the bet loses: the engine descends a B-tree per row instead of reading the pages once in order. ...
A composite B-tree index orders tuples by its first key, then by the next key within each first-key group. This structure can make a tenant-specific time range cheap to locate. The useful rule is about how much index space a predicate narrows, not a prohibition against mentioning later columns or writing WHERE conditions in a different textual order. ...
A queue with a hundred ready jobs and thousands of completed jobs can benefit from an index containing only ready work. PostgreSQL calls this a partial index: its predicate determines which table rows have entries. The benefit depends on the actual query population and write pattern, not simply on making every index smaller. ...