15 ms·

SQL Keys in Depth

435 points by chmaynard 9y ago

chmaynard 9y ago
A review article that combines original research, excellent writing, and useful advice. Impressive!
- Ericson2314 9y ago
  Yeah, not the usual poorly argued heresay. Good job author.
stickfigure 9y ago
On Postgres I prefer to use a single sequence to generate ids across all tables. This reduces the chance of accidents (eg accidentally deleting the wrong thing with id #123) and reduces information leakage ("oh, I see I'm customer #5, you must only have 4 other customers").
- eropple 9y ago
  That's not a bad option, but I think I'd find locally-unique integer IDs spread across tables to be a little confusing. In that case I think I'd lean towards UUIDs, which may be a little easier overall, or use separate sequences but expose IDs via Hashids in my API/frontend: http://hashids.org http://hashids.org
  - int_19h 9y ago
    In Postgres, having multiple tables share a single ID sequence is trivial thanks to SEQUENCE/NEXTVAL.
    - eropple 9y ago
      UUIDs are easier because they immediately scan as random values when you look at them. They are a clear indicator of surrogacy in a way that a spread-out sequence is not. It doesn't matter how difficult picking a sequence is (and your post is odd because I don't think I gave any indication of unfamiliarity with Postgres?). It's a cognitive concern with keys that look simple but are not obviously unrelated (as a normal sequence is by convention or UUIDs are by definition) to the rest of the table.
    - WorldMaker 9y ago
      Sure, but Postgres doesn't to my knowledge support a cross-table UNIQUE index to insure your sequences are followed in all cases. I imagine that all it takes is one confused junior developer to add a bunch of manually IDed data by hand to create some weird and confusing corner cases in this scheme.
      - int_19h 9y ago
        You can define the field as GENERATED ALWAYS, though, in which case specifying the value explicitly in INSERT won't work (there's an escape hatch, but it has to be explicitly and very verbosely invoked).
- maxyme 9y ago
  Another way I've seen done to preserve uniqueness where order leakage doesn't matter is prefixing IDs uniquely per table. Something like uid147... for user IDs pid12... for post ID... It is certainly helpful when debugging and isn't hard to write a test to verify prefixes aren't duplicated across tables.
- cup-of-tea 9y ago
  What is the advantage over UUIDs? The author says that the surrogate keys should never be exposed outside of the database anyway, so no problems with data leakage. If an artificial key is to be exposed then it can be obfuscated to prevent information leakage. I think it should be obvious that it is obfuscated, though, like Youtube video IDs, for example. If you generate integers then your users might assume they are not obfuscated. Eventually a customer will get some low value integer and might assume that they are "customer #5" even if they are not.
  - jeltz 9y ago
    Using a global sequence means 8 bytes less storage per row (including index entries and all foreign keys which refer to the PK), and more importantly typically mostly sequential inserts into B-tree indexes which means less bloat and faster insert and access. Disadvantages are data leakage and, theoretically, minor contention on accessing the sequence.
Ericson2314 9y ago
This is well done. And yet many people get this stuff wrong. As a language person, I think about how we could make these choices more natural by rewriting SQL; make them the path of least resistance rather than requiring much pondering and wisdom.
- Ericson2314 9y ago
  Some ideas: 1. Rename the constraints to mimick the classification of keys described here. E.g. require UUIDs for artificial keys. 2. Make joins with foreign artificial keys much lighter syntactically than free form joins on natural keys. Make joins involving non-ubique columns in the WITH (today's syntax) harder still. People should be able think of foreign keys as references to be dereferenced separate from full joins. 2. Going further, break apart "tables" to represent the are used for different things people use them for, e.g. key-value maps vs "join tables" (a term I had never heard before but immediately could define from experience) vs cannonical data. In math terms, distinguish sets of aggregate data from relations. Having full algebraic data types for the sets of agreggate data but not relations could help. (maps are a special case of relations.) Another final related distinction is tables-as-types vs tables-as- collections. I suppose that relates (no pun :)) back to what joins are allowed. 4. Keep the references pointing in the right direction. For example, à la Rust and capability theory, the owner should point to it's uniquely owned children. But, in SQL today, we instead invert the back reference, having the children refer to their unique parent. Supporting lists (of children) and unaliased foreign keys directly would make the cannonical back-reference an implementation detail.
- hug 9y ago
  Let's be honest: If you could make these choices more natural then you would have done so by now. If you could make SQL better than it is, you already would have done that as well. In fact, if most people who are interested in SQL could make SQL better than SQL is, we wouldn't be using SQL anymore. We're not using it because we have some weird tradition we enforce. We're not using it because we like the way it looks, the way it writes, or the way it handles. We're not using it because we've tried to use ORMs and those ORMs have proved conclusively better across use cases than SQL. The reason we use it is because we don't yet have a better solution. If you can write a better solution than SQL I would love to hear about it: I absolutely loathe SQL, but I use it because out of everything I've seen, it's the simplest and most elegant solution to the problem that I've ever come across. So yeah, while we could possibly and should definitely make SQL better if we can, I don't think that suggesting that we just re-do it from scratch is the way to go. That way lies madness, and possibly javascript frameworks.
lifepillar 9y ago
The most important property of surrogates, which I never see mentioned, is that two tuples, anywhere in the database, have the same surrogate key if and only if they are (believed) to refer to the same real-world entity. This is a crucial difference wrt auto-incrementing or randomly generated values that are independent in different relations (which is a common practice). Among the rest, with surrogates defined as above you may freely join relations on surrogate keys being sure that you are joining related data. In his landmark 1979’s paper, Codd defined surrogates and pretty clearly, and that part of his work was based on an earlier paper entitled On Entities and Relations (I don’t recall the authors right now), so this is not exactly news. Unfortunately, current DBMSs provide little to no support for properly implemented surrogates.
- dragonwriter 9y ago
  > Unfortunately, current DBMSs provide little to no support for properly implemented surrogates. UUIDs instead of per-table autoincrement keys seem to be a solution which covers the part of the problem that involves DB support as opposed to data model design, and several DBs have adequate support for UUIDs.
  - ProblemFactory 9y ago
    UUIDs (and other mostly random IDs) also have the important advantage that they can be generated offline - in mobile apps, client-side javascript or elsewhere. With autoincrement keys, you need to either contact the database server for each data object you create, or implement complex logic to replace temporary IDs with database IDs later.
    - dullgiulio 9y ago
      Nope, you can't. You still have to check for uniqueness on the server side before committing them, so you are back from the start. Always keep in mind that anything coming from the client cannot be trusted.
      - 9y ago
slg 9y ago
>Modern SQL is supposed to abstract from the physical representation. Tables model relations, and should not expose an implicit order in their rows. However even today SQL Server creates a clustered index by default for primary keys, physically ordering rows in the old tradition. Which is why the UUID (or GUID in SQL Server speak) can have other drawbacks there in comparison to auto incrementing bigints, namely delays due to the data being ordered randomly on disk. There are obviously ways to counter that like adding extra non-clustered indexes or changing the clustered index to something besides the primary key, but at that point the extra time and overhead might defeat the benefits you gained from going with UUIDs in the first place.
- jeremy_wiebe 9y ago
  Isn’t that exactly what the NEWSEQUENTIALID function was created for? https://docs.microsoft.com/en-us/sql/t-sql/functions/newsequentialid-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/functions/newsequ...
  - slg 9y ago
    In theory yes, it solves some of the problems and introduces others. It isn't usable everywhere NEWID is usable, so if you assign UUIDS as part of a query you are out of luck. It also assumes you are generating all your UUIDs on the same machine without restarting which once again negates some of the benefits of UUIDs.
    - tatersolid 9y ago
      ULIDs solve these issues. Basically 48 bits of millisecond timestamp then 80 bits of randomness. The technique has been used in MSSQL apps since about 2003 (although people called them COMBs instead of ULIDs back then) https://github.com/oklog/ulid https://github.com/oklog/ulid
      - slg 9y ago
        Nice, I hadn't seen that before. It looks pretty good on first glance.
simonw 9y ago
"For instance, a database of hobbyist club members could include uniqueness on the two columns first_name, last_name of members. Duplicates are likely unintentional at this scale, and if necessary the constraint can be dropped. Until an actual conflict happens the key is a reasonable precaution." Absolutely do not do this. People have names that are duplicates. A situation where someone is unable to join a club because their name clashes with an existing member is not OK. Expecting a club administrator to be able to drop a uniqueness key from their database in order to resolve this situation is not a reasonable solution!
- dullgiulio 9y ago
  I think the author fell for falsehood 21: http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/ http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-b...
  - ofirg 9y ago
    I do not want to belong to any club that would not need to drop a database constraint in order to accept me.
    - dullgiulio 9y ago
      Yes, that's the problem. I understand what he wants to say (basically YAGNI), but imagine the reception desk employee having this problem, escalating it until it reaches a developer just to register John Doe.
      - pizza234 9y ago
        I wonder if the parent's reply is actually a joke based on paraphrasing Groucho Marx (the statement itself doesn't have much logical sense).
        ThrowAwayFSp 9y ago
        wonder no more
dizzystar 9y ago
It's an interesting article with interesting ideas. I'm squarely in the camp of using natural PKs until there is good reason to use surrogate PKs. I generally disagree with the notion of using a combination of surrogate and natural keys. In SQL, a PK isn't just another unique key: a PK an important block of communication. As a rule of thumb, when a PK is attached to a semantic value, it is saying that this is the identifier of the table. If a PK is on a surrogate key, it is saying that there is no good unique identifying value in the table. When you are dealing with larger data sets, this distinction isn't minor, as it helps to understand the intent of the data when working with it. PKs serve as guideposts in your design along with guideposts to the person who has to maintain (or fix) your database later on. I know some disagree with me on that, but there are many undeniably good reasons to use a natural key. A good place is a check-constraint table, where you say, have a list of US states and you want to ensure that "New York" and not "New Yoerk" inserted into the state column of an address table. Put a PK on valid_state_names and FK to the PK from addresses.
- wvenable 9y ago
  > If a PK is on a surrogate key, it is saying that there is no good unique identifying value in the table. Almost always there is no good unique unchanging identifying value on a table. Take something as simple as "Person" -- there is literally no unique unchangeable value for such an entity. And that's the rule more than the exception. I take exactly the the opposite approach; nearly all entities should be identified with a surrogate key. You pretty much cannot go wrong with this approach. In the US state names are pretty stable, but in other countries state/province names have changed so even that value is poor choice for a primary key.
  - dizzystar 9y ago
    Yes, each set of data should reflect the data you want to use. That's why I said I default to natural keys unless there is good reason to use a surrogate key. If I have to compare databases I've worked on over the years, the databases that are all surrogate will have many more data errors than a database that is predominantly natural keys. I could write a book on why that happens, but if you don't know relational theory cold (not saying you don't), you should just use natural keys. The short answer is that using all surrogate keys is equivalent to not using unique constraints at all. Data is bound to change at some point in the future. The entire point of having constraints is to ensure that your data keeps its integrity, now and later on, no matter how the data needs to be updated.
bullen 9y ago
There are two things this article could point out: The difference between centralized, distributed and decentralized primary keys. And how public/private key encryption should be a natural part of every primary key. The problem is all SQL databases uses sync clients which make them completely useless in any distributed or decentralized setting. The real tension here is that there is no guarantee that a large UUIDs are unique, and we have not embraced the random occurrence of a collision.
B-Con 9y ago
> One thing to avoid in publicly exposed numbers is sequential order. It allows people to probe for resources (/videos/1.mpeg, /videos/2.mpeg etc) and also leaks cardinality information. Add a Feistel cipher on top of a sequence. This hides the ordering while maintaining uniqueness. > The PostgreSQL pseudo encrypt wiki gives an example cipher function: > [...] I get that they want to leave the key value in the schema, but practically this kind of thing feels worth pulling out into the storage engine. Just generate a random string as the ID upon row insertion and force uniqueness on that column. If insertion fails, generate another random string. (And it should pretty much never fail because otherwise you're still suspect to the enumeration attack you're trying to prevent.) Although given that Postgres can read from a file, could the above be done in the schema by reading from /dev/urandom? If so that seems like the better approach.
foreigner 9y ago
At what scale does all this stuff start to actually matter? I have an database with ~100 tables and ~500M rows driving a medium-traffic web app and various back-end systems. We use auto-incrementing integers as primary keys and try not to expose them externally. Indexes are added as necessary to enable specific queries. We don't enforce any other constraints (e.g. not-null or foreign keys) at the database level. ... and it all works and performs just fine? The considerations the author mentions all make some sense to me in theory, but when do they actually matter in practice in a modern system?
- vinceguidry 9y ago
  They don't necessarily matter until you hit Twitter-size for performance purposes, but take care to pay close attention to the idea of natural vs. artificial keys, and heed his warning about turning artificial keys into natural keys. One day you'll be integrating database systems together and you'll be glad you followed his advice. I've seen some really ugly bizdata schemas.
- amarkov 9y ago
  As you can see leaking through in a few of the examples, the scenarios where it matters are the ones where non-experts are reading to and writing from the database. If a data entry clerk is just typing values into a form thinly wrapping an INSERT, you'd better make sure any input that would break implicit assumptions is rejected. If you have a legion of Bobs from marketing who "know some SQL", you can't hand-optimize every single query they try to run. When the database is just serving a webapp you control end to end, the only part that'll really have a huge impact is making sure you can partition by the primary key effectively. Which is good, because complex indexing schemes and foreign key constraints actually scale very badly.
- rotten 9y ago
  You can get some real headaches with auto-incrementing integers if you have to go to a multi-master or some other distributed system for data inserts. How do you know which integer to insert next for a table that is replicated across systems? One system could do even numbers and one could do odd. One could do every other 100 integers. Or you could use UUIDs and not worry about it. My argument is that auto-incrementing integers are inherently not scalable. They can scale to a certain extent, and then you have to switch key systems.
txutxu 9y ago
Great article. But MAC address cannot be considered unique, as soon as software like keepalived is running.
- daenney 9y ago
  OK. But how does that relate to this article? If you're using keepalived what you're doing is moving the connection from one instance to another if the current one is detected as being dead. Even if only the MAC was used to generate the UUID (which it is not, timestamp is factored in too) I struggle to find a scenario in which this would be a problem. Even if you time it down to the nanosecond, if keepalived is used as intended you'd never generate a UUID at the same time on a number of machines sharing the MAC. And from a privacy concern, if the MAC cannot be considered unique, that's only a bonus?
  - txutxu 9y ago
    The article list the MAC address as a natural unique key Here are some values that often work as natural keys: [...] mac address on a network [...] that was the reason for my comment.
    - daenney 9y ago
      Ah, I missed that this was what you were commenting on. I thought it was about the use of the MAC in the UUID generation. Thanks!
danielovichdk 9y ago
Funny example in regards of a card-deck. How would you have unique keys for the 3 jokers in the deck ?
- LeonM 9y ago
  A joker does not have a suit, nor a number, so it would not fit in the example table anyway.
  - IgorPartola 9y ago
    Represent the suit as J and number them sequentially? Also not all games use jokers.
  - WorldMaker 9y ago
    Arguably Joker is the number, and like Ace is 1 you might use 0 or 14 depending on math preferences as a surrogate number (or stick to the character designations like A, K, Q, J). Decks with four jokers typically have suited jokers. Decks with two are typically (though not always) red/black and could be given arbitrary suits (say hearts/spades).
- WorldMaker 9y ago
  Decks with four jokers are often suited (just as the most common decks with two jokers are red/black), so a deck with three jokers you could treat as suited jokers that are missing a suit.
erpellan 9y ago
The very first example implies its own counterexample. In most decks of cards there are 2 jokers.
- dannyfraser 9y ago
  One red, one black.
Dowwie 9y ago
programmers who are intrigued by the recommended pg_hashids extension have a wide variety of alternatives to using a pg extension for the same key obfuscation logic: http://hashids.org/ http://hashids.org/
epx 9y ago
The reading was worth because of the curse generator anedocte.
manigandham 9y ago
After a decade of large systems relying on RDBMs, we now use 64-bit integers for all primary keys with a global Hi/Lo id generation system (app reserves a range of numbers on startup to assign to records automatically). This means plenty of ID space, maintains rough numeric ordering, allows ID creation without a roundtrip for every insert, is easily portable across different databases, and produces unique IDs for every row in the database which greatly simplifies everything from caching to replication.
- walshemj 9y ago
  does seem to me that the author is a hobbyist or a junior developer with little real word experience. Literally the first Colum of almost every SQL table I have written has had a column called id with auto increment.
  - sparkie 9y ago
    The trouble with "natural keys" is that they're rarely actually unique. The barcode is a typical example. A naive developer might use a barcode as a primary key, but will soon be in for a world of pain when he realizes that products often use the same barcodes for different configurations (packaging etc), which usually need different SKUs. The same product from different origins may have the same barcode, which often matters.
    - mickronome 9y ago
      Not critique, but some additional perspectives for those less well versed in designing databases. It's also important to realise that almost every time a duplicate key on an assumed unique key would trigger a constraint violation error, absence of this constraint would lead to some non sensical effects elsewhere in the system if ignored. Barcodes are a typical example also for this, as unless the entire dataflow from warehouse to cash register is equipped to handle duplicate EAN codes, all kinds of nonsense results could be the result, potentially with significant economic risk if duplicates are allowed to enter into the system unchecked. Everytime a constraint is removed from something that 'obviously' is/needs to be unique, the next step should be to add validation, duplicate and/or alias handling code everywhere that column is touched. Especially important at input and output. Good database design is really hard, if at all possible, have other people try to come up with ways to break your scheme.
navium 9y ago
"There’s no need to manually create indexes on columns already declared unique; doing so would just duplicate the automatically-created index." Is the last part real? Makes me feel like forking postgesql just to save the world from accidental duplicate expensive indexes.
- jimktrains2 9y ago
  Yes, in PG at least an index is created, otherwise the unique check would become prohibitive.
  - cheapsteak 9y ago
    I think the question is more on whether a duplicate index would be created instead of having the unique constraint and primary key designation on the same column share an index
- manigandham 9y ago
  An index is usually the most efficient way to check for uniqueness, so most databases do in fact handle this constraint with an index. Some of them will also automatically reuse this index if you manually try to create one for the same column.
rusbus 9y ago
One thing to keep in mind with Postgres is that unique key indices can lead to deadlock and stall other transactions that insert the same key.[1] I wouldn't say "unique constraints considered harmful" but I would definitely say they can be surprising and must be used with care. [1]: https://rcoh.me/posts/postgres-unique-constraints-deadlock/ https://rcoh.me/posts/postgres-unique-constraints-deadlock/
- lobster_johnson 9y ago
  The relatively new "INSERT ... ON CONFLICT" mechanism is a great way to circumvent this issue. Using that feature, you can tell it to ignore conflicting dupes, or you can have it transform the insert into an update.
mikst 9y ago
hey, is it me being far from expert, or: why use UUIDv1 if you can just use timestamp? wouldn't time-based solution be prone to time misconfig across servers? Or that little shuffle doesn't really matter?

SQL Keys in Depth

435 points by chmaynard 9y ago

chmaynard 9y ago

A review article that combines original research, excellent writing, and useful advice. Impressive!

Ericson2314 9y ago
Yeah, not the usual poorly argued heresay. Good job author.

stickfigure 9y ago

On Postgres I prefer to use a single sequence to generate ids across all tables. This reduces the chance of accidents (eg accidentally deleting the wrong thing with id #123) and reduces information leakage ("oh, I see I'm customer #5, you must only have 4 other customers").

eropple 9y ago
That's not a bad option, but I think I'd find locally-unique integer IDs spread across tables to be a little confusing. In that case I think I'd lean towards UUIDs, which may be a little easier overall, or use separate sequences but expose IDs via Hashids in my API/frontend: http://hashids.org http://hashids.org
- int_19h 9y ago
  In Postgres, having multiple tables share a single ID sequence is trivial thanks to SEQUENCE/NEXTVAL.
  - eropple 9y ago
    UUIDs are easier because they immediately scan as random values when you look at them. They are a clear indicator of surrogacy in a way that a spread-out sequence is not. It doesn't matter how difficult picking a sequence is (and your post is odd because I don't think I gave any indication of unfamiliarity with Postgres?). It's a cognitive concern with keys that look simple but are not obviously unrelated (as a normal sequence is by convention or UUIDs are by definition) to the rest of the table.
  - WorldMaker 9y ago
    Sure, but Postgres doesn't to my knowledge support a cross-table UNIQUE index to insure your sequences are followed in all cases. I imagine that all it takes is one confused junior developer to add a bunch of manually IDed data by hand to create some weird and confusing corner cases in this scheme.
    - int_19h 9y ago
      You can define the field as GENERATED ALWAYS, though, in which case specifying the value explicitly in INSERT won't work (there's an escape hatch, but it has to be explicitly and very verbosely invoked).
maxyme 9y ago
Another way I've seen done to preserve uniqueness where order leakage doesn't matter is prefixing IDs uniquely per table. Something like uid147... for user IDs pid12... for post ID... It is certainly helpful when debugging and isn't hard to write a test to verify prefixes aren't duplicated across tables.
cup-of-tea 9y ago
What is the advantage over UUIDs? The author says that the surrogate keys should never be exposed outside of the database anyway, so no problems with data leakage. If an artificial key is to be exposed then it can be obfuscated to prevent information leakage. I think it should be obvious that it is obfuscated, though, like Youtube video IDs, for example. If you generate integers then your users might assume they are not obfuscated. Eventually a customer will get some low value integer and might assume that they are "customer #5" even if they are not.
- jeltz 9y ago
  Using a global sequence means 8 bytes less storage per row (including index entries and all foreign keys which refer to the PK), and more importantly typically mostly sequential inserts into B-tree indexes which means less bloat and faster insert and access. Disadvantages are data leakage and, theoretically, minor contention on accessing the sequence.

Ericson2314 9y ago

This is well done. And yet many people get this stuff wrong. As a language person, I think about how we could make these choices more natural by rewriting SQL; make them the path of least resistance rather than requiring much pondering and wisdom.

Ericson2314 9y ago
Some ideas: 1. Rename the constraints to mimick the classification of keys described here. E.g. require UUIDs for artificial keys. 2. Make joins with foreign artificial keys much lighter syntactically than free form joins on natural keys. Make joins involving non-ubique columns in the WITH (today's syntax) harder still. People should be able think of foreign keys as references to be dereferenced separate from full joins. 2. Going further, break apart "tables" to represent the are used for different things people use them for, e.g. key-value maps vs "join tables" (a term I had never heard before but immediately could define from experience) vs cannonical data. In math terms, distinguish sets of aggregate data from relations. Having full algebraic data types for the sets of agreggate data but not relations could help. (maps are a special case of relations.) Another final related distinction is tables-as-types vs tables-as- collections. I suppose that relates (no pun :)) back to what joins are allowed. 4. Keep the references pointing in the right direction. For example, à la Rust and capability theory, the owner should point to it's uniquely owned children. But, in SQL today, we instead invert the back reference, having the children refer to their unique parent. Supporting lists (of children) and unaliased foreign keys directly would make the cannonical back-reference an implementation detail.
hug 9y ago
Let's be honest: If you could make these choices more natural then you would have done so by now. If you could make SQL better than it is, you already would have done that as well. In fact, if most people who are interested in SQL could make SQL better than SQL is, we wouldn't be using SQL anymore. We're not using it because we have some weird tradition we enforce. We're not using it because we like the way it looks, the way it writes, or the way it handles. We're not using it because we've tried to use ORMs and those ORMs have proved conclusively better across use cases than SQL. The reason we use it is because we don't yet have a better solution. If you can write a better solution than SQL I would love to hear about it: I absolutely loathe SQL, but I use it because out of everything I've seen, it's the simplest and most elegant solution to the problem that I've ever come across. So yeah, while we could possibly and should definitely make SQL better if we can, I don't think that suggesting that we just re-do it from scratch is the way to go. That way lies madness, and possibly javascript frameworks.

lifepillar 9y ago

The most important property of surrogates, which I never see mentioned, is that two tuples, anywhere in the database, have the same surrogate key if and only if they are (believed) to refer to the same real-world entity. This is a crucial difference wrt auto-incrementing or randomly generated values that are independent in different relations (which is a common practice). Among the rest, with surrogates defined as above you may freely join relations on surrogate keys being sure that you are joining related data. In his landmark 1979’s paper, Codd defined surrogates and pretty clearly, and that part of his work was based on an earlier paper entitled On Entities and Relations (I don’t recall the authors right now), so this is not exactly news. Unfortunately, current DBMSs provide little to no support for properly implemented surrogates.

dragonwriter 9y ago
> Unfortunately, current DBMSs provide little to no support for properly implemented surrogates. UUIDs instead of per-table autoincrement keys seem to be a solution which covers the part of the problem that involves DB support as opposed to data model design, and several DBs have adequate support for UUIDs.
- ProblemFactory 9y ago
  UUIDs (and other mostly random IDs) also have the important advantage that they can be generated offline - in mobile apps, client-side javascript or elsewhere. With autoincrement keys, you need to either contact the database server for each data object you create, or implement complex logic to replace temporary IDs with database IDs later.
  - dullgiulio 9y ago
    Nope, you can't. You still have to check for uniqueness on the server side before committing them, so you are back from the start. Always keep in mind that anything coming from the client cannot be trusted.
    - 9y ago

slg 9y ago

>Modern SQL is supposed to abstract from the physical representation. Tables model relations, and should not expose an implicit order in their rows. However even today SQL Server creates a clustered index by default for primary keys, physically ordering rows in the old tradition. Which is why the UUID (or GUID in SQL Server speak) can have other drawbacks there in comparison to auto incrementing bigints, namely delays due to the data being ordered randomly on disk. There are obviously ways to counter that like adding extra non-clustered indexes or changing the clustered index to something besides the primary key, but at that point the extra time and overhead might defeat the benefits you gained from going with UUIDs in the first place.

jeremy_wiebe 9y ago
Isn’t that exactly what the NEWSEQUENTIALID function was created for? https://docs.microsoft.com/en-us/sql/t-sql/functions/newsequentialid-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/functions/newsequ...
- slg 9y ago
  In theory yes, it solves some of the problems and introduces others. It isn't usable everywhere NEWID is usable, so if you assign UUIDS as part of a query you are out of luck. It also assumes you are generating all your UUIDs on the same machine without restarting which once again negates some of the benefits of UUIDs.
  - tatersolid 9y ago
    ULIDs solve these issues. Basically 48 bits of millisecond timestamp then 80 bits of randomness. The technique has been used in MSSQL apps since about 2003 (although people called them COMBs instead of ULIDs back then) https://github.com/oklog/ulid https://github.com/oklog/ulid
    - slg 9y ago
      Nice, I hadn't seen that before. It looks pretty good on first glance.

simonw 9y ago

"For instance, a database of hobbyist club members could include uniqueness on the two columns first_name, last_name of members. Duplicates are likely unintentional at this scale, and if necessary the constraint can be dropped. Until an actual conflict happens the key is a reasonable precaution." Absolutely do not do this. People have names that are duplicates. A situation where someone is unable to join a club because their name clashes with an existing member is not OK. Expecting a club administrator to be able to drop a uniqueness key from their database in order to resolve this situation is not a reasonable solution!

dullgiulio 9y ago
I think the author fell for falsehood 21: http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/ http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-b...
- ofirg 9y ago
  I do not want to belong to any club that would not need to drop a database constraint in order to accept me.
  - dullgiulio 9y ago
    Yes, that's the problem. I understand what he wants to say (basically YAGNI), but imagine the reception desk employee having this problem, escalating it until it reaches a developer just to register John Doe.
    - pizza234 9y ago
      I wonder if the parent's reply is actually a joke based on paraphrasing Groucho Marx (the statement itself doesn't have much logical sense).
      - ThrowAwayFSp 9y ago
        wonder no more

dizzystar 9y ago

It's an interesting article with interesting ideas. I'm squarely in the camp of using natural PKs until there is good reason to use surrogate PKs. I generally disagree with the notion of using a combination of surrogate and natural keys. In SQL, a PK isn't just another unique key: a PK an important block of communication. As a rule of thumb, when a PK is attached to a semantic value, it is saying that this is the identifier of the table. If a PK is on a surrogate key, it is saying that there is no good unique identifying value in the table. When you are dealing with larger data sets, this distinction isn't minor, as it helps to understand the intent of the data when working with it. PKs serve as guideposts in your design along with guideposts to the person who has to maintain (or fix) your database later on. I know some disagree with me on that, but there are many undeniably good reasons to use a natural key. A good place is a check-constraint table, where you say, have a list of US states and you want to ensure that "New York" and not "New Yoerk" inserted into the state column of an address table. Put a PK on valid_state_names and FK to the PK from addresses.

wvenable 9y ago
> If a PK is on a surrogate key, it is saying that there is no good unique identifying value in the table. Almost always there is no good unique unchanging identifying value on a table. Take something as simple as "Person" -- there is literally no unique unchangeable value for such an entity. And that's the rule more than the exception. I take exactly the the opposite approach; nearly all entities should be identified with a surrogate key. You pretty much cannot go wrong with this approach. In the US state names are pretty stable, but in other countries state/province names have changed so even that value is poor choice for a primary key.
- dizzystar 9y ago
  Yes, each set of data should reflect the data you want to use. That's why I said I default to natural keys unless there is good reason to use a surrogate key. If I have to compare databases I've worked on over the years, the databases that are all surrogate will have many more data errors than a database that is predominantly natural keys. I could write a book on why that happens, but if you don't know relational theory cold (not saying you don't), you should just use natural keys. The short answer is that using all surrogate keys is equivalent to not using unique constraints at all. Data is bound to change at some point in the future. The entire point of having constraints is to ensure that your data keeps its integrity, now and later on, no matter how the data needs to be updated.

bullen 9y ago

There are two things this article could point out: The difference between centralized, distributed and decentralized primary keys. And how public/private key encryption should be a natural part of every primary key. The problem is all SQL databases uses sync clients which make them completely useless in any distributed or decentralized setting. The real tension here is that there is no guarantee that a large UUIDs are unique, and we have not embraced the random occurrence of a collision.

B-Con 9y ago

> One thing to avoid in publicly exposed numbers is sequential order. It allows people to probe for resources (/videos/1.mpeg, /videos/2.mpeg etc) and also leaks cardinality information. Add a Feistel cipher on top of a sequence. This hides the ordering while maintaining uniqueness. > The PostgreSQL pseudo encrypt wiki gives an example cipher function: > [...] I get that they want to leave the key value in the schema, but practically this kind of thing feels worth pulling out into the storage engine. Just generate a random string as the ID upon row insertion and force uniqueness on that column. If insertion fails, generate another random string. (And it should pretty much never fail because otherwise you're still suspect to the enumeration attack you're trying to prevent.) Although given that Postgres can read from a file, could the above be done in the schema by reading from /dev/urandom? If so that seems like the better approach.

foreigner 9y ago

At what scale does all this stuff start to actually matter? I have an database with ~100 tables and ~500M rows driving a medium-traffic web app and various back-end systems. We use auto-incrementing integers as primary keys and try not to expose them externally. Indexes are added as necessary to enable specific queries. We don't enforce any other constraints (e.g. not-null or foreign keys) at the database level. ... and it all works and performs just fine? The considerations the author mentions all make some sense to me in theory, but when do they actually matter in practice in a modern system?

vinceguidry 9y ago
They don't necessarily matter until you hit Twitter-size for performance purposes, but take care to pay close attention to the idea of natural vs. artificial keys, and heed his warning about turning artificial keys into natural keys. One day you'll be integrating database systems together and you'll be glad you followed his advice. I've seen some really ugly bizdata schemas.
amarkov 9y ago
As you can see leaking through in a few of the examples, the scenarios where it matters are the ones where non-experts are reading to and writing from the database. If a data entry clerk is just typing values into a form thinly wrapping an INSERT, you'd better make sure any input that would break implicit assumptions is rejected. If you have a legion of Bobs from marketing who "know some SQL", you can't hand-optimize every single query they try to run. When the database is just serving a webapp you control end to end, the only part that'll really have a huge impact is making sure you can partition by the primary key effectively. Which is good, because complex indexing schemes and foreign key constraints actually scale very badly.
rotten 9y ago
You can get some real headaches with auto-incrementing integers if you have to go to a multi-master or some other distributed system for data inserts. How do you know which integer to insert next for a table that is replicated across systems? One system could do even numbers and one could do odd. One could do every other 100 integers. Or you could use UUIDs and not worry about it. My argument is that auto-incrementing integers are inherently not scalable. They can scale to a certain extent, and then you have to switch key systems.

txutxu 9y ago

Great article. But MAC address cannot be considered unique, as soon as software like keepalived is running.

daenney 9y ago
OK. But how does that relate to this article? If you're using keepalived what you're doing is moving the connection from one instance to another if the current one is detected as being dead. Even if only the MAC was used to generate the UUID (which it is not, timestamp is factored in too) I struggle to find a scenario in which this would be a problem. Even if you time it down to the nanosecond, if keepalived is used as intended you'd never generate a UUID at the same time on a number of machines sharing the MAC. And from a privacy concern, if the MAC cannot be considered unique, that's only a bonus?
- txutxu 9y ago
  The article list the MAC address as a natural unique key Here are some values that often work as natural keys: [...] mac address on a network [...] that was the reason for my comment.
  - daenney 9y ago
    Ah, I missed that this was what you were commenting on. I thought it was about the use of the MAC in the UUID generation. Thanks!

danielovichdk 9y ago

Funny example in regards of a card-deck. How would you have unique keys for the 3 jokers in the deck ?

LeonM 9y ago
A joker does not have a suit, nor a number, so it would not fit in the example table anyway.
- IgorPartola 9y ago
  Represent the suit as J and number them sequentially? Also not all games use jokers.
- WorldMaker 9y ago
  Arguably Joker is the number, and like Ace is 1 you might use 0 or 14 depending on math preferences as a surrogate number (or stick to the character designations like A, K, Q, J). Decks with four jokers typically have suited jokers. Decks with two are typically (though not always) red/black and could be given arbitrary suits (say hearts/spades).
WorldMaker 9y ago
Decks with four jokers are often suited (just as the most common decks with two jokers are red/black), so a deck with three jokers you could treat as suited jokers that are missing a suit.

erpellan 9y ago

The very first example implies its own counterexample. In most decks of cards there are 2 jokers.

dannyfraser 9y ago
One red, one black.

Dowwie 9y ago

programmers who are intrigued by the recommended pg_hashids extension have a wide variety of alternatives to using a pg extension for the same key obfuscation logic: http://hashids.org/ http://hashids.org/

epx 9y ago

The reading was worth because of the curse generator anedocte.

manigandham 9y ago

After a decade of large systems relying on RDBMs, we now use 64-bit integers for all primary keys with a global Hi/Lo id generation system (app reserves a range of numbers on startup to assign to records automatically). This means plenty of ID space, maintains rough numeric ordering, allows ID creation without a roundtrip for every insert, is easily portable across different databases, and produces unique IDs for every row in the database which greatly simplifies everything from caching to replication.

walshemj 9y ago
does seem to me that the author is a hobbyist or a junior developer with little real word experience. Literally the first Colum of almost every SQL table I have written has had a column called id with auto increment.
- sparkie 9y ago
  The trouble with "natural keys" is that they're rarely actually unique. The barcode is a typical example. A naive developer might use a barcode as a primary key, but will soon be in for a world of pain when he realizes that products often use the same barcodes for different configurations (packaging etc), which usually need different SKUs. The same product from different origins may have the same barcode, which often matters.
  - mickronome 9y ago
    Not critique, but some additional perspectives for those less well versed in designing databases. It's also important to realise that almost every time a duplicate key on an assumed unique key would trigger a constraint violation error, absence of this constraint would lead to some non sensical effects elsewhere in the system if ignored. Barcodes are a typical example also for this, as unless the entire dataflow from warehouse to cash register is equipped to handle duplicate EAN codes, all kinds of nonsense results could be the result, potentially with significant economic risk if duplicates are allowed to enter into the system unchecked. Everytime a constraint is removed from something that 'obviously' is/needs to be unique, the next step should be to add validation, duplicate and/or alias handling code everywhere that column is touched. Especially important at input and output. Good database design is really hard, if at all possible, have other people try to come up with ways to break your scheme.

navium 9y ago

"There’s no need to manually create indexes on columns already declared unique; doing so would just duplicate the automatically-created index." Is the last part real? Makes me feel like forking postgesql just to save the world from accidental duplicate expensive indexes.

jimktrains2 9y ago
Yes, in PG at least an index is created, otherwise the unique check would become prohibitive.
- cheapsteak 9y ago
  I think the question is more on whether a duplicate index would be created instead of having the unique constraint and primary key designation on the same column share an index
manigandham 9y ago
An index is usually the most efficient way to check for uniqueness, so most databases do in fact handle this constraint with an index. Some of them will also automatically reuse this index if you manually try to create one for the same column.

rusbus 9y ago

One thing to keep in mind with Postgres is that unique key indices can lead to deadlock and stall other transactions that insert the same key.[1] I wouldn't say "unique constraints considered harmful" but I would definitely say they can be surprising and must be used with care. [1]: https://rcoh.me/posts/postgres-unique-constraints-deadlock/ https://rcoh.me/posts/postgres-unique-constraints-deadlock/

lobster_johnson 9y ago
The relatively new "INSERT ... ON CONFLICT" mechanism is a great way to circumvent this issue. Using that feature, you can tell it to ignore conflicting dupes, or you can have it transform the insert into an update.

mikst 9y ago

hey, is it me being far from expert, or: why use UUIDv1 if you can just use timestamp? wouldn't time-based solution be prone to time misconfig across servers? Or that little shuffle doesn't really matter?