2 ms·

If you can’t model the table with a natural key (or it would be so large as to inhibit performance), then a simple, normal monotonic integer is best. MySQL even

If you can’t model the table with a natural key (or it would be so large as to inhibit performance), then a simple, normal monotonic integer is best. MySQL even lets you use unsigned ints, so if you use a bigint, you can go all the way up to 2^64-1. For those who think this doesn’t work in distributed systems, it absolutely does – PlanetScale uses them internally [0]. If what is likely the largest MySQL (under Vitess) cluster in the world can manage, yours can too. If this is still untenable, then anything k-sortable (like UUIDv7, as the sibling comment mentioned) is a vast improvement over randomness. Don’t cause B+tree page splits, especially in an RDBMS with a clustering index like MySQL. [0]: https://github.com/planetscale/discussion/discussions/366 https://github.com/planetscale/discussion/discussions/366

hot_gril 3y ago
Even if there's a natural key, serial bigint is usually the best. You can use unique indexes beside that without being stuck with them, and joins are faster on integer pkeys.
- sgarland 3y ago
  For performance, generally yes. Designing a purely relational model is satisfying, but theory often falls apart in prod. It’s still a good idea, IMO, to think about table design starting as though you have a natural key (composite or singular). It helps develop the schema; you can then drop in a serial/identity/autoincrement column, and use the other relationships as FKs.
- throwaway296273 3y ago
  I agree. It also makes queries simpler. It's easier to handle WHERE id = 123 compared to WHERE site = 'HN' and username = 'hot_grill' The last query is easier to write when you are querying the database manually, but I find the first more easy to handle programatically. It's easier to pass an argument from an URL or a message queue in this case. As systems evolve, you may find that you need a third component to the natural key. If you don't use a simple id, you need to update every query that references the natural key
  - hot_gril 3y ago
    If you're querying manually, you can still do it the second way even if your pkey is the bigserial.

hot_gril 3y ago

Even if there's a natural key, serial bigint is usually the best. You can use unique indexes beside that without being stuck with them, and joins are faster on integer pkeys.

sgarland 3y ago
For performance, generally yes. Designing a purely relational model is satisfying, but theory often falls apart in prod. It’s still a good idea, IMO, to think about table design starting as though you have a natural key (composite or singular). It helps develop the schema; you can then drop in a serial/identity/autoincrement column, and use the other relationships as FKs.
throwaway296273 3y ago
I agree. It also makes queries simpler. It's easier to handle WHERE id = 123 compared to WHERE site = 'HN' and username = 'hot_grill' The last query is easier to write when you are querying the database manually, but I find the first more easy to handle programatically. It's easier to pass an argument from an URL or a message queue in this case. As systems evolve, you may find that you need a third component to the natural key. If you don't use a simple id, you need to update every query that references the natural key
- hot_gril 3y ago
  If you're querying manually, you can still do it the second way even if your pkey is the bigserial.