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@xxxxxxxxxxxxx> wrote:
Mohan Radhakrishnan <radhakrishnan.mohan@xxxxxxxxx> 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
