Sample Header Ad - 728x90

Any pitfalls/benefits of creating PK with include(..) in PostgreSQL?

2 votes
2 answers
306 views
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.
Asked by Andrey B. Panfilov (231 rep)
Aug 7, 2023, 01:42 AM
Last activity: Aug 9, 2023, 01:35 AM