I have a partial index filtering only on a column with non-NULL values. If there is an update to a row where this value was NULL before and after the update, is the index touched at all?

I have a table where the rows are soft-deleted using a column named "deleted" that contains a nullable timestamp. I want to create a query that will hard-delete all rows that were deleted more than a week ago, by searching on this column. This table has a very high update throughput, but they only touch "live" rows where deleted=NULL, and I don't want to add more overhead of locking or updating another index.

CodePudding user response：

If you insert, update or delete a row that is excluded in the WHERE condition of the index (and if, in the case of an update, the new row version also is excluded), the index won't have to be modified, so you don't have to pay that price.

The index is not locked anyway, so that need not concern you.