Surrogate vs natural keys: should you use auto-increment IDs, UUIDs or UUIDv7?
Surrogate vs natural keys: should you use auto-increment IDs, UUIDs or UUIDv7?
A natural key is real-world data that identifies a row (email, ISBN, country code). A surrogate key is a meaningless identifier created by the database or application. In most OLTP schemas use a surrogate primary key and keep natural identifiers as UNIQUE constraints, because natural values can change, be reused, or turn out to be non-unique.
| Option | Pros | Cons |
|---|---|---|
Auto-increment / identity (BIGINT) |
small (8 bytes), sequential, fast inserts, readable | reveals volume and order; single-writer assumption; hard to merge from several databases; gaps are normal |
| Random UUID (v4) | generated anywhere, no coordination, hard to guess | 16 bytes; random inserts fragment B-tree and cache locality; slower on clustered indexes |
| UUIDv7 | globally unique and time-ordered, so inserts stay mostly sequential | 16 bytes; embeds creation time; needs library or database support |
UUIDv7 puts a millisecond Unix timestamp in the high bits followed by random bits (standardized in RFC 9562). It keeps the benefits of UUIDs with index locality close to a sequence. PostgreSQL 18 provides a built-in uuidv7(); elsewhere generate it in the application.
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.