High Performance PostgreSQL for Rails

Filtering Rows with Partial Indexes

Partial indexes are B-Tree indexes that have an expression baked into their definition. The expression can serve the purpose of restricting the rows that are included in the index.

We’ll apply that to soft deletes in a moment. Restricting the rows in an index provides multiple benefits but also trade-offs. Let’s consider the benefits. The index consumes less space. The index contributes less to write latency since entries are only maintained in the index when the expression matches a particular DML operation.

A partial index is a specialized type of index that’s optimized for specific queries by both providing efficient retrieval and efficient storage.

Let’s go back to the soft deletes example. Let’s mix ...