Re: Normal vs Surrogate Primary Keys...

Tom Lane wrote:
> "rlee0001" <robeddielee@hotmail.com> writes:
> > ... I know, for example, that by default PostgreSQL assigns every record a
> > small unique identifier called an OID.
>
> Well, actually, that hasn't been the default for some time, and even if
> you turn it on it's not guaranteed unique without additional steps, and
> seeing that it's only 32bits wide it's not usable as a unique ID for
> very large tables.
>
> "unique" and "small" are more or less contradictory in this context.
>
> > ... it seems like this is the sort of thing that even a fully SQL-compliant
> > DBMS could do internally to compensate for the performance issues with
> > using large natural keys in relationships.
>
> What performance issues are those, exactly?  I have seen no data that
> proves that large key size is a bottleneck for reasonable schema designs
> in Postgres.  Maybe that just means that we have more fundamental
> problems to fix :-( ... but there's no point in investing a lot of
> effort on an aspect that isn't the next bottleneck.

Well from what I understand, there are basically two reasons to use
surrogate primary keys: 1) No reliable natural candidate key exists or
2) The natural candidate keys are percieved to be far too large/complex
to use as the primary key. I have yet to hear anyone recommend the use
of surrogate keys in modern databases for any other reason. Obviously
that some entities in practice have no reliable natural keys cannot be
helped and in those cases a surrogate key pretty much has to be used,
enless the data modeler would find it acceptable to use every attribute
in the entity as part of the primary key (in order to ensure uniqueness
of each record overall).

The second argument for the use of surrogate keys is simply that
reliable natural candidate keys are often perceived to be too large to
use as primary keys. The perception seems to be that large primary keys
consume a considerable amount of storage space when used in foreign
keys. For example, if I key "employee" by Last Name, First Name, Date
of Hire and Department, I would need to store copies of all this data
in any entity that relates to an employee (e.g. payroll, benefits and
so on). In addition, if any of these fields change in value, that
update would need to cascade to any related entities, which might be
perceived as a performance issue if there are many related records.

I'm not saying that PostgreSQL specifically has performance problems
but that using large natural keys can hamper performance (both in terms
of storage space required and cascading update time) when a lot of
relationships exist between entities with such large keys.

Personally I hate using surrogate keys except in places where no
reliable natural key exists but find it nessisary in order to improve
the efficiency of foreign keys in the database. So my proposal was
simply to have the DBMS internally create and use an invisible
surrogate key to identify and relate records, but expose the natural
key to the environment. The currently OID implimentation cannot be used
for this as you've already stated but I think a similar implimentation
could work.

I'm just wondering if anyone else would take advantage of the
performance benefit, or perhaps sees a flaw in my logic.

>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly