ADR 007 - Use UUID for primary keys

• ADR 007 - Use UUID for primary keys
  • Context
  • Decision
  • Rationale
    • Identity vs GUID
      • Identity
      • UUID
    • References
  • Status
  • Consequences

Context

• TODO

Decision

Use UUIDv7 as the primary key for all new tables (instead of sequential integer IDs).

• They can be freely exposed without disclosing sensitive information,
• they are not predictable
• and they are performant.

Rationale

• TODO

Identity vs GUID

Identity

Pros:

• Small storage footprint
• Optimal join / index performance (e.g. for time range queries, most rows recently inserted will be on a limited number of pages)
• Highly useful for data warehousing
• Native data type of the OS, and easy to work with in all languages
• Easy to debug (where userid = 457)
• Generated automatically (retrieved through SCOPE_IDENTITY() rather than assigned)
• Not changeable.

Cons:

• Cannot be reliably “predicted” by applications — can only be retrieved after the INSERT
• Need a complex scheme in multi-server environments, since IDENTITY is not allowed in some forms of replication
• Can be duplicated, if not explicitly set to PRIMARY KEY
• If part of the clustered index on the table, this can create an insert hot-spot
• Proprietary and not directly portable
• Only unique within a single table
• Gaps can occur (e.g. with a rolled back transaction)

UUID

Pros:

• Unique across every table, every database, every server
• Allows easy merging of records from different databases
• Allows easy distribution of databases across multiple servers
• You can generate IDs anywhere, instead of having to roundtrip to the database
• Most replication scenarios require GUID columns anyway

Cons:

• It is a whopping 4 times larger than the traditional 4-byte index value; this can have serious performance and storage implications if you’re not careful
• Cumbersome to debug where userid='{BAE7DF4-DDF-3RG-5TY3E3RF456AS10}'
• The generated GUIDs should be partially sequential for best performance (eg, newsequentialid() on SQL 2005) and to enable use of clustered indexes

References

• UUIDv7: The Time-Sortable Identifier for Modern Databases
• Why Auto Increment Is A Terrible Idea
• Primary Keys: IDs versus GUIDs

Status

• Proposed
• Accepted
• Deprecated
• Superseded

Consequences

• Must provide an O11 and ODC implementations
• Link to How to O11
• Link to how to ODC