3 ms·
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
by 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 agoFor 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 agoI 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 agoIf you're querying manually, you can still do it the second way even if your pkey is the bigserial.