Timeline for Is it good practice to always have an autoincrement integer primary key?
Current License: CC BY-SA 4.0
20 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Dec 29, 2023 at 2:19 | comment | added | Frank Hopkins | @DrunkenCodeMonkey UUIDs by now at least are - for most databases - no worse than an autoincrement column and if you need them anyways to access a row with an externally available id you can just as well use them as primary key. However, you need to be careful how you store them (binary) and how you craft them (indeed, you want them to be sequential typically). See percona.com/blog/store-uuid-optimized-way for a performance comparison. | |
| S Oct 26, 2020 at 16:52 | history | suggested | CommunityBot | CC BY-SA 4.0 | Putting TL;DR: at the end of the post is redundant, the reader has to read through the entire post before they see it. |
| Oct 25, 2020 at 3:49 | review | Suggested edits | |||
| S Oct 26, 2020 at 16:52 | |||||
| Feb 4, 2020 at 10:04 | comment | added | pensnarik | There is a good point but one should consider that using UUID in unique indexes in PostgreSQL might lead to write amplification due to the fact that's it has a random nature. See this article for the details: 2ndquadrant.com/en/blog/on-the-impact-of-full-page-writes | |
| Oct 1, 2018 at 22:21 | comment | added | Rahly | As much as i abhor auto incrementing keys, because it's the sign of a lazy designer, using UUIDs is just as bad. Just because it's harder to "guess". This is just another demonstration of security through obscurity. Which is ALWAYS a bad idea. If you have a URL problem you should be securing access to the data requested, or limiting the data. The question you need to ask is that does knowing someone's user id bad? In short, no. Knowing the id does nothing other than what you let them do with it. | |
| Aug 21, 2016 at 14:26 | comment | added | Lie Ryan | @DrunkenCodeMonkey: in most DBMS, gaps in the primary key id does not necessarily indicate deleted rows. In Postgres and Oracle, for example, getting a number from sequences are non-transactional; if you insert a row in a transaction then rollback the transactions, your pk ids will have gaps. Sequences can also be pre-allocated by the database's child processes to speed up ID generation, which can cause gaps when the database server is restarted. MySQL/Maria also does something similar, even though they don't have explicit sequence object. | |
| Aug 19, 2016 at 22:37 | comment | added | Drunken Code Monkey | There is also a huge problem with UUIDs in the case of auditing. In many enterprise databases, deleting rows is a big no-no. Auditors often use the primary key sequence as a starting point to investigate missing or tampered with data. If you need a globally unique key, stick it in an global entities index table. | |
| Aug 19, 2016 at 22:31 | comment | added | Drunken Code Monkey | Purely from a database point of view, this answer is completely wrong. Using UUIDs instead of auto-incrementing integers grows indexes way too fast, and affects performance and memory consumption adversely. If you are talking from the point of view of web service or web app, there should be a layer in between the database and the front end anyway. Anything else is bad design. Using data as a primary key is even worse. Primary keys should be use only on the data layer, nowhere else. | |
| Aug 19, 2016 at 17:39 | comment | added | jpmc26 | Blindly choosing UUID over an incrementing key is not something I'd advise. Sure, UUIDs have lots of advantages, but they have some drawbacks, too. (Some databases index them poorly, for instance.) Examine your use case. Weight the benefits and drawbacks. Then decide. | |
| Aug 18, 2016 at 19:47 | comment | added | Voo | UUIDs are a good solution for sharded databases though, although there exist other systems that can just as well and with other useful properties (such as better performance, or being able to sort by time without needing to fetch anything but the key). Hell, Flickr uses DB ticket servers with auto increment and that works on their scale just as well. | |
| Aug 18, 2016 at 19:37 | comment | added | Voo | It's funny that you use "waste of space" as an example (although you could create exactly the same problem with UUIDs obviously, so rather confusing) and then neglect to mention the fact that UUIDs are an order of magnitude larger than autoincremented values. Yes there are some very, very rare situations where you don't want the user to guess your IDs, but an UUID is a horrible choice there as well! UUIDs are trivial to guess and the only version that isn't necessarily (4) does not require a cryptographically strong PRNG so you can't rely on that one either. | |
| Aug 16, 2016 at 20:29 | comment | added | AnoE | I can't suggest using UUIDs for everything. Yes, a session ID should not be an autoincrement, but for almost everything else, if you have a dedicated ID field created internally to the DB or the application, having it actually be sequential is quite useful. It's kind of a SCN ("system change number"), it helps debugging or generally gives you (as a developer/dba) useful information about which entities were likely created close together. | |
| Aug 16, 2016 at 18:49 | comment | added | LosManos | One advantage with UUIDs is that they are very very unique. This means that any typo where you join CustomerID=ProjectID will return an empty result instead. This is easier to catch when debugging. | |
| Aug 16, 2016 at 16:29 | comment | added | Olivier de Rivoyre | @CodesWithHammer: Yes Clustered index on UUIDs are ranked on the UUIDs, thus your rows has a non business-logical order in memory. Thus you can have bad performance because you don't benefit of the memory caching of your PC. When using id, you may benefit the fact the rows are sorted by creation date. Imagine a select between two dates as a usecase. | |
| Aug 16, 2016 at 13:45 | comment | added | Codes with Hammer | @Stephen: If I remember correctly, the other problem with UUIDs as a PK is that database engines have a lot of trouble making an index on a UUID field. Is this right? (ETA: On reading the next answer, I might have meant "clustered index" instead of just "index.") | |
| Aug 16, 2016 at 12:16 | comment | added | Dave Sherohman | On the "URL guessing" point, having a unique ID (sequential or otherwise) does not imply exposing that ID to users of the application. | |
| Aug 16, 2016 at 8:21 | comment | added | yurez | Another scenario when UUIDs are better: implementing an idempotent PUT operation, so that you can safely retry requests without introducing duplicate rows. | |
| Aug 16, 2016 at 7:28 | comment | added | user44761 | The entire paragraph about uniqueness is moot - uniqueness can be enforced, with or without a primary key. Besides, UUIDs are better on the theoretical side, but awful to use when debugging/performing DBA tasks or otherwise doing anything that is not "resisting to attacks". | |
| Aug 16, 2016 at 6:37 | comment | added | Stephen | The problem with UUIDs is that they take up too much space for the majority of tables. Use the right unique identifier for each table. | |
| Aug 16, 2016 at 6:08 | history | answered | Drathier | CC BY-SA 3.0 |