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 trades storage and write work for a possible reduction in read work. A full scan can win when the table is small or a predicate selects much of it, but neither row count nor selectivity decides alone. Projection, ordering, caching, row width, and available access paths change the comparison. Start with the query’s actual result contract. ...
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. ...