FreeBSDEnthousiast
FreeBSDEnthousiast

Reputation: 11

Hash as primary Key versus BigInt, read/write performance

I have an unlogged postgresql table holding three columns: 'key' of type varchar (255) hosting immutable sha256hex strings, 'created_at' of type timestamp without time zone and 'json' of type text. To give context, this table is used for managing a database cache fed by caffeine (java in memory cache library) upon eviction events.

So far, I have defined 'key' as being the primary key. Alternatively, I could have add a surrogate key (some bigint auto generated value) and add a unique index on the 'key' column.

I read few interesting articles here about the usage of surrogate keys and most of them advise their usage in a different context, when performing joins with other tables or when the 'key' column is subject to updates. Since it will never be my case, I was wondering what was the read/write performance penalty of using 'key' as primary key versus adding a surrogate (primary) key ?

To let you know, I only peform two queries :

I have read things about index fragmentation but I believe it would affect both solutions, am I getting it right ?

Upvotes: 1

Views: 94

Answers (0)

Related Questions