Choosing `UUID` for Unique IDs in a Multi-System Environment

When designing a database schema, the choice of unique identifier is critical for scalability, data integration, and future-proofing. While databases traditionally use SERIAL (auto-increment integers), in modern distributed and service-oriented architectures, UUID (Universally Unique Identifier) is often the better choice.

---

Why Not SERIAL?

A SERIAL or BIGSERIAL column generates sequential IDs (1, 2, 3...). This works fine for a single, isolated database but creates challenges when scaling:

• ID collisions during merges: Two different systems may both have a record with id = 100. Merging them becomes complex.
• Predictable IDs: The sequence can reveal the number of records (e.g., knowing you have 50,000 customers).
• Sharding and replication issues: Multiple services writing to their own databases may generate overlapping IDs.

---

Why Use UUID Everywhere?

A UUID is a 128-bit identifier that is globally unique. Using UUIDs for both master tables (e.g., customers, products) and transaction tables (e.g., orders, invoices) brings key benefits:

✅ Advantages

1. Seamless data merging
   
   Whether merging customers or orders across services, IDs will never clash. This simplifies integration projects.

2. Supports distributed systems
   
   Microservices, event-driven architectures, and multi-database systems can generate IDs independently without coordination.

3. Future-proofing
   
   Even if today you only have one database, tomorrow you might need to replicate, shard, or consolidate with another system.

4. Security
   
   UUIDs are non-sequential, making it harder for outsiders to guess the number of records or enumerate IDs.

⚠️ Trade-offs

• Larger storage size (16 bytes vs. 4 bytes for integers).
• Slightly slower indexing and joins.
• Harder to read/debug manually (though often hidden behind APIs).

---

Example Schema

Master Table: Customers

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE customers (
    customer_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

Transaction Table: Orders

CREATE TABLE orders (
    order_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    customer_id UUID NOT NULL REFERENCES customers(customer_id),
    order_date TIMESTAMP DEFAULT now(),
    amount NUMERIC(10,2) NOT NULL
);

Final Decision

If your system might merge data across multiple databases, services, or environments,
then the consistent choice is to use UUID for all primary keys — both master data and transactional data.

This avoids conflicts, simplifies future integrations, and aligns with best practices for distributed systems.