Application is aggressively caching data in memory and in order to support consistency (preventing persisting stale data) it is doing something like:
-- typical table structure: create table t1 ( id varchar(16) primary key, version_stamp int4, .... )
-- typical update statement update t1 set version_stamp = version_stamp + 1, col1 = ?, col2 = ?, ... where id = ? and version_stamp = ?
If update mentioned above tells that no rows have been updated, that means application has attempted to persist stale data and exception gets thrown, and the main idea is to prevent or, at least, minimise such cases. In order to do so, application is performing following queries (per request, transaction or method call):
select version_stamp from t1 where id = ?
If no rows have been returned, that means the row have been deleted, if returned
version_stamp differs with
version_stamp kept in memory, that means we are dealing with stale data.
The question is: is it worth to define primary keys as:
create unique index on t1(id) include(version_stamp)
Or not in such case. The typical RPS for such queries is about 10k per second.