StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhat's your opinion on using UUIDs as database row identifiers, particularly in web apps?
text
Body
copied!<p>I've always preferred to use long integers as primary keys in databases, for simplicity and (assumed) speed. But when using a <a href="http://en.wikipedia.org/wiki/Representational_State_Transfer" rel="noreferrer">REST</a> or Rails-like URL scheme for object instances, I'd then end up with URLs like this:</p> <pre><code>http://example.com/user/783 </code></pre> <p>And then the assumption is that there are also users with IDs of 782, 781, ..., 2, and 1. Assuming that the web app in question is secure enough to prevent people entering other numbers to view other users without authorization, a simple sequentially-assigned surrogate key also "leaks" the total number of instances (older than this one), in this case users, which might be privileged information. (For instance, I am user #726 in stackoverflow.)</p> <p>Would a <a href="http://en.wikipedia.org/wiki/Uuid" rel="noreferrer">UUID</a>/GUID be a better solution? Then I could set up URLs like this:</p> <pre><code>http://example.com/user/035a46e0-6550-11dd-ad8b-0800200c9a66 </code></pre> <p>Not exactly succinct, but there's less implied information about users on display. Sure, it smacks of "security through obscurity" which is no substitute for proper security, but it seems at least a little more secure.</p> <p>Is that benefit worth the cost and complexity of implementing UUIDs for web-addressable object instances? I think that I'd still want to use integer columns as database PKs just to speed up joins.</p> <p>There's also the question of in-database representation of UUIDs. I know MySQL stores them as 36-character strings. Postgres seems to have a more efficient internal representation (128 bits?) but I haven't tried it myself. Anyone have any experience with this?</p> <hr> <p>Update: for those who asked about just using the user name in the URL (e.g., <a href="http://example.com/user/yukondude" rel="noreferrer">http://example.com/user/yukondude</a>), that works fine for object instances with names that are unique, but what about the zillions of web app objects that can really only be identified by number? Orders, transactions, invoices, duplicate image names, stackoverflow questions, ...</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload