Auto-Increment Integer vs UUID as Primary Key

Direct Answer

Auto-increment integers are compact, simple, and naturally ordered, so they often work well when one database assigns IDs. UUIDs can be generated independently across services and are harder to guess, but they use more space; random UUIDs can also reduce B-tree locality. Choose from the system's ID-generation, exposure, merge, and index needs rather than from a monolith-versus-distributed rule.

Code Example

-- PostgreSQL identity: small and ordered
CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

-- PostgreSQL UUID: independently generated and stored in its native type
CREATE TABLE events (
    id UUID PRIMARY KEY
);

These are two valid designs. Pick after deciding where ids are created and checking the index and storage trade-offs on the database you actually use.

Common Mistakes & Pitfalls

• Ignoring UUIDv4 locality: Random UUIDs spread B-tree inserts across the key range and can increase page splits, index size, and cache work. Measure insert throughput and index behavior on the real schema instead of quoting a fixed penalty or an unrelated table statistic.
• Choosing from architecture labels: A single service may need independently generated ids, while a distributed system can also coordinate numeric ranges or sequences. Decide from generation and merge requirements rather than the words monolith or microservices.
• Using text when a native type exists: PostgreSQL's native uuid type stores a UUID in 16 bytes and validates its format. Text storage can still be necessary for portability, so compare the actual database and client requirements rather than treating one representation as universal.