Usage of surrogate keys in Vertica

Mrao

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

Vertica recommends to use Surrogate keys or Natural keys?

colin_loghin

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

Sashidhar_Arcot

I

Daniel_Leybovic

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.

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