cockroachdb / cockroachdb/cockroach
sql: optimistically perform writes before reads for UPSERT statements
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
Several weeks ago @ajstorm and I discussed a potential optimization for `UPSERT` and `INSERT ... ON CONFLICT ... DO UPDATE` statements. Currently these statements perform a read to check for conflicts before performing the write. If conflicts are expected to be unlikely, then it would be beneficial to instead perform the writes of the unique indexes first, and then only read existing rows if there are conflicts. The reads are necessary in order to write all the KVs, e.g., if the `DO UPDATE` updates a subset of columns in the table, the non-updating columns need to be read in order to construct the new KVs.
If conflicts are relatively common, this could perform worse. In an ideal world the system can make an informed decision to use this optimization or not. But a session setting to enabled/disable the optimization would be a good starting point.
See also: #14482, #71153
Jira issue: CRDB-40319
Contributor guide
Assessment
This issue has not been assessed yet.