Is your feature request related to a problem? Please describe.

Each node in the cluster runs a query periodically to find jobs in a claimable state which are claimed by expired sessions and to remove that assignment.

Unassigned jobs in a claimable state are then claimed in a different periodic loop.

Both of these transactions will lay down locks during their execution. These locks have an arbitrary duration and can block users reading the table, leading to a bad user experience. Another big problem with these queries is that they require scanning and performing an index-join on all rows in the relevant states even though they are only looking for rows with a specific claim_session_id.

Describe the solution you'd like

Add a partial index to the jobs table which includes the state and the claim_session_id for the claimable states. This will allow for much more efficient update queries which scan narrower spans.

Epic: CRDB-7912