Usage of surrogate keys in Vertica

Surrogate keys are very common in DW environment for some of the benefits it provides.

Vertica recommends to use Surrogate keys or Natural keys?


  • Options
    The same considerations in choosing a surrogate versus a natural key apply. The only suggestion I have is to avoid GUID externally generated as they do not compress very well. A sequencer would work well/.
  • Options

    You need surrogate key only if you have no simple Natural key (when natural key is a several fields, or field is complex like GUID as Colin pointed). I don't see any reason for surrogate key, if you have a simple natural key.

    From my experience - there are no such big difference in Vertica, because it is columnar DBMS and columns are sorted. Once, at AT&T I had a table with140 columns where 10 of them where a PK and I wanted yo improve a JOIN. I defined a surrogate key as hash of 10 fields and didn't get any performance boost. Actually  i got some boost, but less than 2%, its not critical (Vertica version 6.1.2).

    Of cause it depends on schema and requirements, here no silver bullets.


    Surrogate keys simplifies query writing/development. Its important too - less place for mistakes.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file