1

I asked a question about whether or not I should normalize some static data and the general consensus was that it is not necessary since the data is unchanging, thus it is not redundant and will not likely result in update/deletion/insert anomalies.

However, is it worth normalizing further so that I can get an index on an INT type rather than VARCHAR? I'm not sure how most DBMS's do comparisons on strings but I would imagine that integers are faster than strings for comparisons.

At the same time if we are talking about a B+ tree index we are only talking about 3-4 comparisons for internal nodes and then however many records exist for the key which I am not even sure if comparisons occur at that level.

Adam Thompson
  • 459
  • 1
  • 4
  • 11

1 Answers1

3

integer is typically a bit smaller and faster than character types in every respect. The difference mainly depends on the length of the strings.

You asserted that the text data is unchanging, else this would be a strong case for using a surrogate key instead (integer or whatever).

Circumstances that would still make me lean towards an added integer surrogate key:

  • Strings are much larger than the 4-byte integer. Little to nothing to gain with short strings. The benefit grows with the avg. string length.

  • The key is used in multiple indexes and/or FK columns, so benefits add up.

  • You work with a locale other than C (the typical case) and you need rows to sort in immutable (obvious) fashion. The sort order of character types depends on the current locale settings (COLLATION).

  • All other tables in your DB work with integer keys and you'd rather not break the pattern.

On the other hand, with Postgres 9.5 or later you can benefit from the "abbreviated keys" feature. But only if you work with locale C on a *nix OS. That would tilt the scales towards using original strings.

Related:

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633