3 ms·

Terrible advice. Surrogate keys are keys are a layer of indirection. They don't fix all problems, but they fix some problems. Not least of which is performan

Terrible advice. Surrogate keys are keys are a layer of indirection. They don't fix all problems, but they fix some problems. Not least of which is performance. Often natural keys are character strings, whereas surrogate keys can be fixed size integers, saving index sizes on your FKs.

mypalmike 2y ago
Conversely, certain queries can be much faster by using natural keys when the FK is all that you need in the result rather than additional fields in the primary table. In this case, the primary table doesn't need to be queried at all. This doesn't generally overcome the benefits of synthetic keys, but it is an optimization sometimes put into practice.
- crabmusket 2y ago
  Aren't you literally describing an index?
  - mypalmike 2y ago
    No. Maybe an example helps. You have a users table with username as natural PK. You have an access table with timestamps of users hitting a service, with FK of username. If you query the access table for a range of timestamps and just want the usernames, they're right there in the results of the access table query. If you had instead used a synthetic user_id key, the db would have to do an additional lookup in the users table via a join for each user_id to get the usernames.
    - crabmusket 2y ago
      Oh of course, that makes sense. I'm not sure how I misunderstood that!
mrkeen 2y ago
> surrogate keys can be fixed size integers This launches into the other debate about PKs: using UUIDs rather than sequential keys.
- sgarland 2y ago
  This is less of a debate, and more of an indicator of who has had to work with a DB at scale using UUIDv4 everywhere. Don’t blow up your B+trees.
  - mrkeen 2y ago
    I guess. Either your system is happy enough to route every new entity through "one DB at scale" so it can let your "one DB at scale" be in charge of an auto-incrementing long, or it isn't.
    - sgarland 2y ago
      A common method is to have a small app (which can quite easily be HA) that hands out sequential chunks to each shard, interleaving and/or gapping as necessary.

mypalmike 2y ago

Conversely, certain queries can be much faster by using natural keys when the FK is all that you need in the result rather than additional fields in the primary table. In this case, the primary table doesn't need to be queried at all. This doesn't generally overcome the benefits of synthetic keys, but it is an optimization sometimes put into practice.

crabmusket 2y ago
Aren't you literally describing an index?
- mypalmike 2y ago
  No. Maybe an example helps. You have a users table with username as natural PK. You have an access table with timestamps of users hitting a service, with FK of username. If you query the access table for a range of timestamps and just want the usernames, they're right there in the results of the access table query. If you had instead used a synthetic user_id key, the db would have to do an additional lookup in the users table via a join for each user_id to get the usernames.
  - crabmusket 2y ago
    Oh of course, that makes sense. I'm not sure how I misunderstood that!

mrkeen 2y ago

> surrogate keys can be fixed size integers This launches into the other debate about PKs: using UUIDs rather than sequential keys.

sgarland 2y ago
This is less of a debate, and more of an indicator of who has had to work with a DB at scale using UUIDv4 everywhere. Don’t blow up your B+trees.
- mrkeen 2y ago
  I guess. Either your system is happy enough to route every new entity through "one DB at scale" so it can let your "one DB at scale" be in charge of an auto-incrementing long, or it isn't.
  - sgarland 2y ago
    A common method is to have a small app (which can quite easily be HA) that hands out sequential chunks to each shard, interleaving and/or gapping as necessary.