Company
Date Published
Author
Bryn Llewellyn
Word count
2413
Language
English
Hacker News points
None

Summary

In a distributed SQL database, the primary key and business-unique key are two distinct concepts that serve different purposes. A surrogate primary key is a synthetic value with no meaning in the business world, generated behind the scenes automatically using an explicit or implicit trigger-based scheme. This approach is recommended for distributed databases due to the larger row counts and potential for performance issues when changing primary keys. YugabyteDB uses a pseudo-random generator, such as `gen_random_uuid()`, to populate surrogate primary key columns, which are faster than traditional identity-based sequences. The recommended approach is to specify a surrogate primary key for every table, identify at least one business-unique key, and create a not null, unique constraint on it. Using the `default gen_random_uuid()` function or an alternative pseudo-random UUID generator function can be used to populate the surrogate primary key column declaratively. This approach provides an insurance policy against the costly consequences of changing natural primary keys in distributed databases.