2

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.

2 Answers 2

4

The SELECT query you show could benefit a lot from an index-only scan after including version_stamp in the PK index. (Or adding an additional multicolumn index to cover that.) That's assuming your table is VACUUM'ed enough to allow index-only scans.

For starters, the data type varchar(16) is an unfortunate choice for id as it occupies 17 bytes on disk. Space is typically allocated in chunks of 8 bytes, which results in 7 bytes of alignment padding to 24 bytes in the PK index you have now. The worst case. (And possibly in the table, too.) See:

bigint (8 bytes) or even uuid (16 bytes) would perform much better. Handling varchar is slightly more expensive to process on top of that. See:

The upside: adding an integer to the PK index will hardly grow its size at all, as it can occupy 4 bytes of the 7 currently lost to padding.

It will reduce the benefits of "index deduplication" a bit - depending on typical write patterns and the level of concurrency. (Multiple concurrent versions of the same PK entry may now have a different version_stamp and can't be compressed.) But that's a side note.

A much more important downside: So far, nothing you disclosed in the question stands in the way of HOT updates for the displayed UPDATE command (or similar). Adding version_stamp to the PK index rules out HOT updates when that column is updated. The index now requires an update, too, which might add cost to write operations and create more table and index bloat. See:

The best course of action heavily depends on the complete picture:

  • Are HOT updates even possible right now?
  • What's the ratio of reads and writes? And which is more important to you?
  • Can your autovacuum keep the visibility map up to date and cope with index bloat?
  • Can you switch id to a more favorable data type?

To note: this is how you would change your PK:

ALTER TABLE t1
  DROP CONSTRAINT t1_pkey
, ADD  CONSTRAINT t1_pkey PRIMARY KEY (id) INCLUDE (version_stamp);

Or some more sophisicated variant with CREATE INDEX CONCURRENTLY if you can't afford a long exclusive lock on the table. See:

create unique index ... like you display is related, but different.

4
  • The actual issue we have faced with is the distribution of response time for "version check" queries looks weird: from sub-millisecond to seconds without any "significant" (10% of expected) load in DB, however storage seems not to be so bad for demonstrating such poor response times. Aug 7, 2023 at 13:12
  • 1
    @AndreyB.Panfilov Your real question seems to be quite different from the question you asked. Use auto_explain to capture plans for slow queries, including both analyze and buffers, and having track_io_timing on. Optionally, have auto_explain.log_timing = off if you are worried access to the system clock is too slow. Also have log_lock_waits on and deadlock_timeout set to a low enough value that it would show up if relevant. then ask a new question about inconsistent timing, collating all of this info.
    – jjanes
    Aug 7, 2023 at 18:48
  • @Andrey The "actual issue" sounds like material for a separate question - with details like jjanes instructed. Aug 7, 2023 at 21:40
  • @ErwinBrandstetter I have got an opinion you were asked about some context, so I have provided it. Your awesome A gave me enough food for thought and now I need some time to check my hypotheses, thanks. Aug 9, 2023 at 1:31
1

Probably not. If you find the same version_stamp using an index-only-scan, then you need to immediately access that table page anyway in order to do the update of it. The only time you avoid that IO is if you find a different version_stamp and so throw an error. But that will probably be pretty rare, and not worth optimizing for. (Also, if the version-stamp is different presumably it was very recently updated, and so the visibility map bit has been cleared and you had to visit the page anyway.)

1
  • That is not so bad as it mights look, writing conflicts are challenging on both user and application sides, however those conflicts are rare, in the most cases we are checking whether application is dealing with stale data w/o writing it back to DB, however some hot rows do exist. Aug 9, 2023 at 1:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.