7 ms·

integer ids are still often used internally for database primary keys with UUIDs being the done thing for external interfaces. Personally I've never experience

• blacktriangle 6y ago
  I've run into one, but it was pretty dumb. Multitenant application using the account ID as the first element in the URL on a Rails app. when it got to account 404, my address.com/404 went to the static 404 page rather than account 404. That user was pretty confused for awhile.
  • corytheboyd 6y ago
    Ouch, using the top level path for something dynamic like that is rough. Hope someone learned the right lesson from that :p
  • edmundsauto 6y ago
    This is discussed in the article - they suggest to start your INT ids at a really large number to avoid this kind of thing.
  • Can_Not 6y ago
    I don't know rails, but that is just terrible design. address.com/accounts/404 is what the devs should have used, and "404" is a response code and an error page, not a URL you redirect/rewrite URLs to.
• chromatin 6y ago
  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.
• marcus_holmes 6y ago
  sooner or later you'll make a mistake in your code and use a document_id where you meant to use a user_id, and send a bunch of someone else's data to a user. the "big integer" stops this happening for other integers that might be used in your code and that you might accidentally send to the database as a user_id.
  • dragonwriter 6y ago
    > the "big integer" stops this happening for other integers that might be used in your code Unless you might also use big integers in your code. 32-bits is big enough for all numbers-used-as-numbers-instead-of-ids is...a risky assumption.
• dan-robertson 6y ago
  One bug I saw came from picking an integer id as “one more than the largest id of the current elements we have.” This worked fine until they added support for deleting elements, which also worked fine most of the time. A common disadvantage of integer keys is that programs will have bugs and use a foo_id as a bar_id. If most ids are small integers then it is likely that a valid foo_id may be a valid bar_id, whereas uuids probably won’t collide. This can be somewhat mitigated with a sufficiently strong type system. Even in a dynamic language like lisp you can represent your ids as e.g. (foo . <id>), and only need to get the tags right on the boundary. An advantage of integer ids is density: if your ids are likely close together, there are probably some better or more compressed data structures you can use.
• urban_alien 6y ago
  I've been using PHP professionally for 5+ years and never experienced it either. Sounds to me that this is just bad coding
• nostrademons 6y ago
  This is terrible from a UX perspective though. Nobody wants 36 random digits in their URLs. Assuming you can stomach the latency hit, the best solution is usually a lookup table where you take all the friendly URL keys and map them to internal identifiers. So if you're making a multiplayer game site and want to create a page where folks can find their friends, then you might support yourgame.net/user/username, yourgame.net/character/charactername, yourgame.net/steam/steamlogin, yourgame.net/xbox/xboxgamertag, etc. Internally you have an inverted index that maps [type, string] to the internal ID for the player, then proceed normally.