4 ms·

The reason (other than "don't expose integer PK externally") people say you should use integer as PK and UUID as a secondary/external facing id is that conventi

The reason (other than "don't expose integer PK externally") people say you should use integer as PK and UUID as a secondary/external facing id is that conventional B-tree indexing of UUID is not as efficient as B-tree indexing of autoinc integers in most databases. However if you want any sort of efficient lookup on the external key (UUID), your database still needs an index on the UUID, and you are back at square one. I choose to forego the integer PK and just use UUID since I have to create index over it anyway.

marcosdumay 6y ago
Odds are very big that the set of externally visible entities is much smaller than the set of database entities. That is, unless you decide to put the same interface into your database and your API, what is not rare for OOM-only programmers to do, but always ends in tears.
- MrCapybara 6y ago
  What OOM means in this context? I assume it's not Out Of Memory?
  - marcosdumay 6y ago
    Ops, I misstyped ORM.
evanelias 6y ago
> However if you want any sort of efficient lookup on the external key (UUID), your database still needs an index on the UUID, and you are back at square one Yes and no. It depends on whether your database treats primary keys differently than other indexes. For example, in InnoDB primary keys are always clustered indexes: the row data is directly stored in a btree arranged by the PK; secondary indexes just store PK values in their leaf nodes, so that they can do a lookup on the clustered index. As a result, in InnoDB smaller PKs are preferable. So performance is generally better when using an incremental ID as PK and then UUID as a secondary index, as opposed to the reverse. Assuming you have multiple secondary indexes, the total table size will also be smaller in InnoDB with integer PK than with UUID PK.
deckard1 6y ago
> However if you want any sort of efficient lookup on the external key (UUID), your database still needs an index on the UUID, and you are back at square one. Well. Many systems already do a form of this. They store a session ID (the browser cookie) in something like redis which maps to an internal database id which is often incremented. The internal database ids are never seen outside the DB. In this case it's fine because the external IDs are ephemeral (relatively) and centralization is a hard criteria (you typically can't have two people creating an account with the same user name, or having one email address linked to multiple record IDs, etc.). This is really why these discussions are pointless without specifics and a concrete system.

marcosdumay 6y ago

Odds are very big that the set of externally visible entities is much smaller than the set of database entities. That is, unless you decide to put the same interface into your database and your API, what is not rare for OOM-only programmers to do, but always ends in tears.

MrCapybara 6y ago
What OOM means in this context? I assume it's not Out Of Memory?
- marcosdumay 6y ago
  Ops, I misstyped ORM.

evanelias 6y ago

> However if you want any sort of efficient lookup on the external key (UUID), your database still needs an index on the UUID, and you are back at square one Yes and no. It depends on whether your database treats primary keys differently than other indexes. For example, in InnoDB primary keys are always clustered indexes: the row data is directly stored in a btree arranged by the PK; secondary indexes just store PK values in their leaf nodes, so that they can do a lookup on the clustered index. As a result, in InnoDB smaller PKs are preferable. So performance is generally better when using an incremental ID as PK and then UUID as a secondary index, as opposed to the reverse. Assuming you have multiple secondary indexes, the total table size will also be smaller in InnoDB with integer PK than with UUID PK.

deckard1 6y ago

> However if you want any sort of efficient lookup on the external key (UUID), your database still needs an index on the UUID, and you are back at square one. Well. Many systems already do a form of this. They store a session ID (the browser cookie) in something like redis which maps to an internal database id which is often incremented. The internal database ids are never seen outside the DB. In this case it's fine because the external IDs are ephemeral (relatively) and centralization is a hard criteria (you typically can't have two people creating an account with the same user name, or having one email address linked to multiple record IDs, etc.). This is really why these discussions are pointless without specifics and a concrete system.