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

Question

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.

Answer 1

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:

• Configuring PostgreSQL for read performance

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:

• What is the overhead for varchar(n)?
• Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

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:

• Is a composite index also good for queries on the first field?
• Redundant data in update statements

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:

• Drop primary key without dropping an index

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

Answer 2

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.)