Re: looking for a globally unique row ID

On Thu, Sep 14, 2017 at 2:45 AM, Rafal Pietrak <rafal(at)ztk-rp(dot)eu> wrote:
> Hello everybody,
>
> Can anybody help me find a way to implement an ID which:
>
> 1. guarantees being unique across multiple tables.
>
> 2. guarantees its uniqueness not only during INSERT, but also during the
> lifetime of the database/application (e.i. during future UPDATES).
>
> 3. guarantees persistence of value across database backup/restore/upgrade.
>
> an obvious candidate - a single SERIAL() (same serial) used in every
> table that needs that ID does not guarantee (2).

A shared sequence meets all of those requirements. I tend to prefer
this over GUID. GUIDs are twice the size, and randomized which has
some unpleasant performance characteristics. The advantage they
bring is being able to set up multiple generation points (say, across
multiple database servers) without fear of conflict.

Sequence generation is very fast so there is no performance related
argument not to use the approach.

merlin