UUID v5 as a Primary Key in PostgreSQL: when business keys stop being unique (and how to avoid collisions)

Summary
I was generating deterministic UUID v5 primary keys from business keys. It worked well—until subtle but serious collision risks appeared:

Ambiguous concatenation (ab + cd vs a + bcd)

Cross-table collisions (different tables, same business values → same UUID)

This post presents a clean, modular solution using explicit separators and namespace scoping, and explores the harder problem of multi-source ingestion.

⚙️ Context & Problem

In data platforms and ETL systems, business keys are often the only stable identifiers available (for example, composite natural keys).
To ensure idempotency and simplify upserts and deduplication, a common pattern is:

generate a deterministic UUID (UUID v5) from business keys;
use that UUID as the primary key.

This approach has clear advantages:

same input → same ID;
safe reprocessing;
simpler merges and comparisons.

However, determinism introduces its own risks.

❌ Problem 1 — Concatenation ambiguity

If business values are concatenated without boundaries:

("ab", "cd") → "abcd"
("a", "bcd") → "abcd"

UUID v5 hashes the final string together with the namespace, so both cases produce the same UUID.

This is a silent data bug: no constraint violation, no error—just incorrect identity.

❌ Problem 2 — Collisions across tables

Even with safe concatenation, consider two different tables:

table_a(col1, col2)
table_b(col1, col2)

If both contain the same business values, a deterministic UUID generated from those values will be identical—even though the entities belong to different domains.

This can lead to:

incorrect joins in downstream pipelines;
unintended overwrites during merges;
subtle corruption in consolidated datasets.

🎯 Goal

Design a clean, deterministic, and scalable UUID generation strategy that:

avoids concatenation ambiguity;
prevents cross-table collisions;
does not depend on storing random “seed UUIDs”;
remains suitable for ETL and distributed systems.

🧱 Technical Approach

Guiding principles

Explicit boundaries between values → always use a separator.
Domain-aware determinism → UUIDs must be scoped to their domain.
Single responsibility → one function generates identifiers.
No hidden state → deterministic, explainable behavior.

1️⃣ Explicit separator (mandatory)

Instead of:

ab + cd → abcd

Use:

ab | cd

Now:

ab | cd ≠ a | bcd

The separator must be either:

guaranteed not to appear in the data, or
consistently escaped/normalized.

2️⃣ Namespace scoping

UUID v5 is computed as:

UUID = v5(namespace_uuid, name)

If the namespace is too broad, determinism becomes dangerous.

A practical solution is to scope namespaces hierarchically, for example:

stable root namespace
project
database server / cluster
database
table

Each level derives a new namespace from the previous one.

This guarantees:

the same business key in different tables produces different UUIDs
consistent behavior across environments.

⚠️ Why not store a random namespace UUID in the database?
Because if that value is changed or lost:

all deterministic IDs change;
primary and foreign keys break;
historical data becomes inconsistent.

That is operationally unsafe.

🧩 Practical PostgreSQL Implementation

Enable UUID support

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Helper: build a safe name string

This function:

trims values;
replaces NULL with a sentinel;
enforces a separator.

CREATE OR REPLACE FUNCTION build_uuid_name(
    values text[],
    sep text DEFAULT '|',
    null_token text DEFAULT '<NULL>'
) RETURNS text
IMMUTABLE
LANGUAGE sql
AS $$
SELECT array_to_string(
         ARRAY(
           SELECT COALESCE(NULLIF(btrim(v), ''), null_token)
           FROM unnest(values) AS v
         ),
         sep
       );
$$;

Main function: deterministic UUID v5 primary key

This version uses a stable root namespace (uuid_ns_dns()) and nests namespace derivation without intermediate queries.

CREATE OR REPLACE FUNCTION uuid_v5_business_pk(
    project_name text,
    db_server text,
    db_name text,
    table_name text,
    business_values text[],
    sep text DEFAULT '|'
) RETURNS uuid
IMMUTABLE
LANGUAGE sql
AS $$
SELECT uuid_generate_v5(
         uuid_generate_v5(
           uuid_generate_v5(
             uuid_generate_v5(
               uuid_generate_v5(
                 uuid_ns_dns(),
                 project_name
               ),
               db_server
             ),
             db_name
           ),
           table_name
         ),
         build_uuid_name(business_values, sep)
       );
$$;

🗂️ Optional: Namespace Registry Table (governed determinism)

String-derived namespaces are deterministic, but fragile: names can be reused, renamed, or drift over time.

A namespace registry table introduces a governed root namespace per logical domain, decoupling identifier stability from infrastructure naming.

A domain can represent:

a logical dataset (dataset_x_v1);
a canonical entity (entity_y);
a producer–entity boundary (source_a:entity_z).

Why this helps

Stability across renames
Explicit ownership and governance
Multi-source collision isolation, without adding a source column to the final table

Trade-offs

Namespace UUIDs must be immutable once used
ID generation now depends on registry availability (usually acceptable)

Registry table (minimal design)

CREATE TABLE IF NOT EXISTS uuid_namespace_registry (
    namespace_key   text PRIMARY KEY,
    namespace_uuid  uuid NOT NULL UNIQUE,
    description     text,
    created_at      timestamptz NOT NULL DEFAULT now(),
    created_by      text
);

Registered UUID generation function

CREATE OR REPLACE FUNCTION uuid_v5_business_pk_registered(
    p_namespace_key text,
    p_business_values text[],
    p_sep text DEFAULT '|'
) RETURNS uuid
STABLE
LANGUAGE plpgsql
AS $$
DECLARE
    v_namespace uuid;
BEGIN
    SELECT r.namespace_uuid
      INTO v_namespace
      FROM uuid_namespace_registry r
     WHERE r.namespace_key = p_namespace_key;

    IF NOT FOUND THEN
        RAISE EXCEPTION
            'Namespace not registered for namespace_key=%',
            p_namespace_key
            USING ERRCODE = '22023';
    END IF;

    RETURN uuid_generate_v5(
        v_namespace,
        build_uuid_name(p_business_values, p_sep)
    );
END;
$$;

🧠 The harder case: multi-source ingestion

Everything above works as long as identity has a single authority.

The real complexity appears when a single target table ingests data from multiple independent sources, each producing deterministic UUIDs from business keys.

At that point, determinism alone becomes a liability.

The core issue

Different sources may:

use identical business values,
apply the same UUID logic,
and generate the same UUID,

even though the records:

originate from different systems,
carry different trust levels,
or represent slightly different semantics.

These collisions are silent—and extremely dangerous.

The constraint is intentional:

The final table represents a canonical view, not a staging area. Adding a source column is not desired.

Possible strategies (none are perfect)

Option A — Include the source in the namespace

Each source gets its own namespace.

Pros

Collision-proof
Fully deterministic
No runtime coordination

Cons

Same real-world entity → different UUIDs
Requires later entity resolution

Option B — Surrogate internal ID + natural key

The target system owns identity.

Pros

PK stability
Clear ownership
No producer collisions

Cons

Harder idempotency
Requires matching logic

Option C — Central ID registry / resolver

A canonical identity service.

Pros

Strong consistency
One UUID per real-world entity

Cons

Operational complexity
Runtime coupling
Throughput and availability concerns

Where this stands

Avoiding a source column while preserving:

deterministic behavior,
collision safety,
long-term maintainability,

is a non-trivial design problem.

This part of the system is intentionally open for discussion.

If you’ve solved this differently in a multi-source data platform, I’d genuinely like to compare approaches.

💬 Final Thoughts

UUID v5 is powerful—but only if:

concatenation is explicit and unambiguous;
determinism is scoped to the correct domain.

Otherwise, collisions will eventually happen—quietly and painfully.

LinkedIn → https://www.linkedin.com/in/frederico-gago-5849281aa
GitHub → https://github.com/fredericogago

⚙️ Context & Problem#

❌ Problem 1 — Concatenation ambiguity#

❌ Problem 2 — Collisions across tables#

🎯 Goal#

🧱 Technical Approach#

Guiding principles#

1️⃣ Explicit separator (mandatory)#

2️⃣ Namespace scoping#

🧩 Practical PostgreSQL Implementation#

Enable UUID support#

Helper: build a safe name string#

Main function: deterministic UUID v5 primary key#

🗂️ Optional: Namespace Registry Table (governed determinism)#

Why this helps#

Trade-offs#

Registry table (minimal design)#

Registered UUID generation function#

🧠 The harder case: multi-source ingestion#

The core issue#

Possible strategies (none are perfect)#

Option A — Include the source in the namespace#

Option B — Surrogate internal ID + natural key#

Option C — Central ID registry / resolver#

Where this stands#

💬 Final Thoughts#

⚙️ Context & Problem

❌ Problem 1 — Concatenation ambiguity

❌ Problem 2 — Collisions across tables

🎯 Goal

🧱 Technical Approach

Guiding principles

1️⃣ Explicit separator (mandatory)

2️⃣ Namespace scoping

🧩 Practical PostgreSQL Implementation

Enable UUID support

Helper: build a safe name string

Main function: deterministic UUID v5 primary key

🗂️ Optional: Namespace Registry Table (governed determinism)

Why this helps

Trade-offs

Registry table (minimal design)

Registered UUID generation function

🧠 The harder case: multi-source ingestion

The core issue

Possible strategies (none are perfect)

Option A — Include the source in the namespace

Option B — Surrogate internal ID + natural key

Option C — Central ID registry / resolver

Where this stands

💬 Final Thoughts