[latch]

Another benefit of partial indexes is to limit a unique constraint:

create index users_email on users(email) where status != 'delete'

[SomeHacker44]

Be very careful, then, as the optimizer will (usually?) not use the index if the condition is not part if the query.

[jackTheMan]

you can create views for that, then it will be always part of the query.