Re: Primary keys and composite unique keys(basic question)

I will cover the UUIDs first. They are indispensable to us.
1. The data is distributed over regions So we need the row to be unique.
2. This distributed data is sent to services as events. That is the
application architecture.

But we don't search using UUIDs always. Only when data from another
distributed service
is received we need them and in such cases we have to join using them.

But for local data we can identify another composite unique key. Does
PostgreSql
create a unique index for us ? What about a FK that references this
composite
unique key ? Does it create a FK index ?

Thank you.

On Wed, Mar 31, 2021 at 7:42 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Mohan Radhakrishnan <radhakrishnan(dot)mohan(at)gmail(dot)com> writes:
> > We have UUIDs in our tables which are primary keys. But in
> > some cases
> > we also identify a composite unique key apart from the primary key.
>
> > My assumption is that there should be a unique key index created by us
> > using the composite key. And when we fetch using this composite key
> instead
> > of the primary key we have a performance boost due to the index.
>
> You haven't provided a lot of detail, but use-a-UUID-as-a-primary-key
> is often an antipattern. The UUIDs are quasi-random, meaning there's
> no locality of reference in the primary key index, resulting in
> inefficiency in searches and insertions. If the composite key you
> mention has some actual relationship to your application's usage
> patterns, it could be winning as a result of better locality of
> access to that index.
>
> regards, tom lane
>