The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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?


  • 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/.
  • Hi!

    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