Auto increment is a terrible idea(clever-cloud.com)
clever-cloud.com
Auto increment is a terrible idea
https://www.clever-cloud.com/blog/engineering/2015/05/20/why-auto-increment-is-a-terrible-idea/
93 comments
Strikes me as extremely reckless to edit a production database live with some tab completing editor. Is it really the norm to do that?
I don't know if its the norm but we do it here. Right click table, edit top 200 rows to add a new row.
We, unfortunately, edit the live db all the time.
We, unfortunately, edit the live db all the time.
It would seem to me that you should strictly control what software can edit the db. If you need some special feature, build it into your app and test it first. A general purpose editor can destroy the consistency of your entire database, or parts of it which you won't know about but your users will!
In my job, I edit the production database live because adding a special feature (or even a bug fix) takes like a year to get deployed to production, even if it's as simple as a one line change.
Yes, I understand it's a disaster waiting to happen. I mean if we want to edit a stored proc in the db, right click it in SSMS, script to modify, make the change, F5.
Hope everything doesn't blow up.
It's not ideal.
Hope everything doesn't blow up.
It's not ideal.
Why wouldn't you do that in the dev database, then push to test, then push to prod?
We dont' have a dev / test / live database.
All dev and testing is dont against the live database. YOu just have to be very, very, very careful.
It's something we are trying to implement now, honestly. It's on the TODO list.
The problem I'm having is where do you start? How do you simulate the data flowing through live in the test / dev enviornment.
All dev and testing is dont against the live database. YOu just have to be very, very, very careful.
It's something we are trying to implement now, honestly. It's on the TODO list.
The problem I'm having is where do you start? How do you simulate the data flowing through live in the test / dev enviornment.
Upvotes for honest anecdata and asking for advice!
> How do you simulate the data flowing through live in the test / dev enviornment.
Most don't, rolling with the schema and 'minimum viable' data flow instead.
Make darn sure your backups work by testing restores regularly. Restoring a production backup elsewhere may be an option if it's possible to avoid sensitive data (scrubbing afterward introduces risk of missing something, and should be scripted/repeatable).
Exactly matching production data flow is not as important as avoiding accidentally hosing the prod db.
> How do you simulate the data flowing through live in the test / dev enviornment.
Most don't, rolling with the schema and 'minimum viable' data flow instead.
Make darn sure your backups work by testing restores regularly. Restoring a production backup elsewhere may be an option if it's possible to avoid sensitive data (scrubbing afterward introduces risk of missing something, and should be scripted/repeatable).
Exactly matching production data flow is not as important as avoiding accidentally hosing the prod db.
Let me tell you from my experience. One time I was editing code on the live server using SFTP, before I knew about version control. And the whole thing was deleted, with no way to get it back.
It doesn't take that long to put some safeguards.
Here is the thing: WHENEVER you say "you have to be VERY careful" or blame someone for making a mistake, that's a place you need to put more checks. The computer should check routine things, not people.
"The problem I'm having is where do you start? How do you simulate the data flowing through live in the test / dev enviornment."
I am guessing you use MySQL so simply set up MYSQL REPLICATION and test all your stuff on the replica database first. Actually you should periodically dump the replica and test on a snapshot, so you can mess up the snapshot.
Main db -> Replica -> Snapshot.
It doesn't take that long to put some safeguards.
Here is the thing: WHENEVER you say "you have to be VERY careful" or blame someone for making a mistake, that's a place you need to put more checks. The computer should check routine things, not people.
"The problem I'm having is where do you start? How do you simulate the data flowing through live in the test / dev enviornment."
I am guessing you use MySQL so simply set up MYSQL REPLICATION and test all your stuff on the replica database first. Actually you should periodically dump the replica and test on a snapshot, so you can mess up the snapshot.
Main db -> Replica -> Snapshot.
It's a SQLSERVER, which at this point is about 200GB. Can I say just replicate the schema and then run a script to populate some test data. Or just move over a subset of the main DB?
Got any good articles on how to setup test environments. I would then need all the dev code to point to the test one, not the live db and any Web APIs to be able to switch to point to the test db (which must mean running 2 web apis, a live and a test one...)
Seems like it gets very complicated, but probably worth figuring out.
We don't even have CI yet.
Got any good articles on how to setup test environments. I would then need all the dev code to point to the test one, not the live db and any Web APIs to be able to switch to point to the test db (which must mean running 2 web apis, a live and a test one...)
Seems like it gets very complicated, but probably worth figuring out.
We don't even have CI yet.
Yes, typically when testing web applications, you have a second instance of the application running on a different machine than the production server, configured to run against a different database server as well. The exact details of how to do this well depend on how your application is written and its requirements.
If all you want to do is functional testing of the application by yourself, then it is OK to deploy a copy of the software on your desktop along with a test database. I do this for the application I work on, and when I need to test against MSSQL, the Express edition is good enough.
If you need to do load testing or integration testing or other more complicated forms of testing, you usually try to duplicated the expected production environment as well as you can. This may not be possible, so you may have to make decisions about what's most relevant and simply accept those tradeoffs.
EDIT: Don't worry about CI or anything else that fancy and complicated for now. You may not even need it; that depends on your team and how often your application changes. Just for now take the simplest steps you can until you get this basic stuff figured out first.
If all you want to do is functional testing of the application by yourself, then it is OK to deploy a copy of the software on your desktop along with a test database. I do this for the application I work on, and when I need to test against MSSQL, the Express edition is good enough.
If you need to do load testing or integration testing or other more complicated forms of testing, you usually try to duplicated the expected production environment as well as you can. This may not be possible, so you may have to make decisions about what's most relevant and simply accept those tradeoffs.
EDIT: Don't worry about CI or anything else that fancy and complicated for now. You may not even need it; that depends on your team and how often your application changes. Just for now take the simplest steps you can until you get this basic stuff figured out first.
Here's two ideas:
1. Set up replication, with your test db the replication slave of your production database, such that changes in production are mirrored in testing nearly instantly.
2. Take your production database backups (you do have those, right?) and restore them to the test database. This is less fresh than a replication setup but if you're just doing functional testing it should be enough to say "Well, okay, this little change isn't going to blow everything up."
1. Set up replication, with your test db the replication slave of your production database, such that changes in production are mirrored in testing nearly instantly.
2. Take your production database backups (you do have those, right?) and restore them to the test database. This is less fresh than a replication setup but if you're just doing functional testing it should be enough to say "Well, okay, this little change isn't going to blow everything up."
Not to excuse anything here, but you wouldn't believe how _insane_ some business environments are when it comes to databases. And yes, by that I mean those systems that you wouldn't believe anyone would ever want to risk losing.
Depending on what you're working on, chances are there even are people who use _security_ as an excuse to basically prevent any halfway useful emergency strategy one could think of when handling data impossible to pull off (oh the irony).
Depending on what you're working on, chances are there even are people who use _security_ as an excuse to basically prevent any halfway useful emergency strategy one could think of when handling data impossible to pull off (oh the irony).
Availability is one leg of the CIA triad. Security is the justification for having backups and a recovery strategy, not an excuse for ignoring it.
If you're doing manual updates to a production DB, using START TRANSACTION and then checking the outcome of your changes before you use COMMIT stops typos becoming catastrophes.
In case it's not obvious: new auto increment IDs are skipped forever whenever a transaction is rolled back.
This isn't really an issue at all but can upset those with OCD-ish tendencies.
This isn't really an issue at all but can upset those with OCD-ish tendencies.
those with OCD-ish tendencies best hope they never lose any customers then.
This. There's so much in software development that relies on guarantees that can only be upheld by behavior like this that I _sincerely_ doubt that anyone having issues with skipped rows is in the right place.
There are arguments for and against any technique, but this one seems really ridiculous to me, especially if you consider what any "countermeasure" would do to the code (which is usually much more important than the data in the long run!).
There are arguments for and against any technique, but this one seems really ridiculous to me, especially if you consider what any "countermeasure" would do to the code (which is usually much more important than the data in the long run!).
UPDATE tbl SET deleted=1
There's some caveats with this approach, since your DB has to keep track of the split state since you've started the transaction, it's quite a lot busier. So don't keep transactions open for any long stretches of time ;)
Who has been "pwn3d" by exposing their user count?
It's less about direct "pwn3ge" than about business intelligence. The number of users could be sensitive information for some businesses, but it's not just users. Any item where your volume is valuable proprietary information, you better make sure you aren't exposing an auto-incrementing id. Number of accounts, number of daily orders, number of distribution centers, number of product models, these might be data points that a growing business would prefer not to expose to their competitors via, for example, URL query parameters. You should make competitors work harder for their intelligence.
It's less about direct "pwn3ge" than about business intelligence. The number of users could be sensitive information for some businesses, but it's not just users. Any item where your volume is valuable proprietary information, you better make sure you aren't exposing an auto-incrementing id. Number of accounts, number of daily orders, number of distribution centers, number of product models, these might be data points that a growing business would prefer not to expose to their competitors via, for example, URL query parameters. You should make competitors work harder for their intelligence.
I think "pwn3d" does but refer to a security issue in the article, but to social networks claiming to have millions of users and outsiders being able to proof that it's far less via user ids.
I don't understand how that would be proof.
Imagine that the social network does a rewrite, and rather than converting all the old accounts/data it just uses an additional flag to indicate which database to use. In that case a user id would not be globally unique for the website, but it would be unique for the specific database.
This is actually more likely than it sounds. The company can hire a fresh new team to make an experimental new site, and if it's a hit, they can make some ugly hack to do calls to the old website using web APIs to get any old users from that system. This way the new team doesn't need to learn and figure out how the old system was built, which likely lacks any documentation and where the original developers has left the company.
Without a full understanding of the layers of indirection it would be impossible to determine with certainty the number of users a social network has.
Imagine that the social network does a rewrite, and rather than converting all the old accounts/data it just uses an additional flag to indicate which database to use. In that case a user id would not be globally unique for the website, but it would be unique for the specific database.
This is actually more likely than it sounds. The company can hire a fresh new team to make an experimental new site, and if it's a hit, they can make some ugly hack to do calls to the old website using web APIs to get any old users from that system. This way the new team doesn't need to learn and figure out how the old system was built, which likely lacks any documentation and where the original developers has left the company.
Without a full understanding of the layers of indirection it would be impossible to determine with certainty the number of users a social network has.
Social media networks claimed a number of users—falsely. Reporters created a couple accounts, got the true number, exposed the lies. Pwnage.
That seems a fairly terrible reason to advise against serial keys. That they expose fragrant lies about metrics made by incompetent marketing departments? Sounds like a feature.
[deleted]
here's an example of someone "pwn3d" by exposing their user count:
https://www.troyhunt.com/kids-pass-just-reminded-us-how-hard...
https://www.troyhunt.com/kids-pass-just-reminded-us-how-hard...
> all it takes to know your user count is to create an account
Also, this is only the case if your raw user IDs are visible. You only need a very basic encryption scheme to hide them. It can be as simple as taking two large prime numbers and sending (uid + x) % y to the user.
Also, this is only the case if your raw user IDs are visible. You only need a very basic encryption scheme to hide them. It can be as simple as taking two large prime numbers and sending (uid + x) % y to the user.
Basing user ids (as facebook did) on an autoincrementing number exposed when a user joined the site.
Mark Zuckerberg is famously 4 and you can find a lot of famous silicon valley types in the first million user ids :)
Mark Zuckerberg is famously 4 and you can find a lot of famous silicon valley types in the first million user ids :)
I really like UUIDs for all of the reasons described in the article. But can there can be significant performance related drawbacks:
UUIDs are double the size of 64 bit integers. Which could mean fewer rows on data and index pages, leading to memory pressure, leading to I/O pressure.
UUIDs are also random. I'm not an expert when it comes to PostgreSQL internals. But for MSSQL it's common and recommended to use your PK as a clustered index. The randomness can wreck havoc on the clustered index, causing severe fragmentation due to page splitting. There are some techniques to mitigate the effects of this (decreasing page fill factor, using incrementing UUIDs, using a surrogate IDENTITY clustered key), but each has significant tradeoffs
UUIDs are double the size of 64 bit integers. Which could mean fewer rows on data and index pages, leading to memory pressure, leading to I/O pressure.
UUIDs are also random. I'm not an expert when it comes to PostgreSQL internals. But for MSSQL it's common and recommended to use your PK as a clustered index. The randomness can wreck havoc on the clustered index, causing severe fragmentation due to page splitting. There are some techniques to mitigate the effects of this (decreasing page fill factor, using incrementing UUIDs, using a surrogate IDENTITY clustered key), but each has significant tradeoffs
I still like auto-increment IDs. They are small and give you a monotonically increasing value for optimistic locking.
If you need multi-DC uniqueness. I prefer to add a 2nd DC column. The combination of the two is unique across DC.
If you need to hide the count from customers--often the case--add a 2nd surrogate unique key that is a UUID. This keeps your joins small, but gives you a random key to give back to the customer.
If you need multi-DC uniqueness. I prefer to add a 2nd DC column. The combination of the two is unique across DC.
If you need to hide the count from customers--often the case--add a 2nd surrogate unique key that is a UUID. This keeps your joins small, but gives you a random key to give back to the customer.
While the article doesn't mention it, I think the recommended uuid function for postgres is uuid_generate_v1mc() from https://www.postgresql.org/docs/10/static/uuid-ossp.html
This generates a v1 uuid, so the first few bytes are time dependant, which reduces fragmentation compared to v4 uuids. At the same time, it generates random values for the "mac" section, so it isn't as host-specific and low-entropy as a normal v1 uuid.
This generates a v1 uuid, so the first few bytes are time dependant, which reduces fragmentation compared to v4 uuids. At the same time, it generates random values for the "mac" section, so it isn't as host-specific and low-entropy as a normal v1 uuid.
Random distribution seems like it would be better for write performance - it means that inserting multiple new values and rebuilding the index can happen in parallel rather than having to coordinate the auto increments. It also naturally has the right properties for sharding as and when you get to the point of needing that.
On the read side it shouldn't make any difference, as there's no reason the rows you want to access at any given point should have any correlation to when those rows were created.
On the read side it shouldn't make any difference, as there's no reason the rows you want to access at any given point should have any correlation to when those rows were created.
>>Random distribution seems like it would be better for write performance - it means that inserting multiple new values and rebuilding the index can happen in parallel rather than having to coordinate the auto increments. It also naturally has the right properties for sharding as and when you get to the point of needing that.<<
I'm not sure what you mean by "inserting new values and rebuilding the index can happen in parallel." In most storage engines using clustered indexes, writes that append to a b-tree are faster than writes that insert somewhere in the middle of a b-tree so there really isn't any rebuilding of the index required in the former case.
>>On the read side it shouldn't make any difference, as there's no reason the rows you want to access at any given point should have any correlation to when those rows were created.<<
That depends entirely on the nature of your workload. There are plenty of common workloads such as sensor data or log entries for which the common read patterns are very much correlated with the order in which the rows were written.
I'm not sure what you mean by "inserting new values and rebuilding the index can happen in parallel." In most storage engines using clustered indexes, writes that append to a b-tree are faster than writes that insert somewhere in the middle of a b-tree so there really isn't any rebuilding of the index required in the former case.
>>On the read side it shouldn't make any difference, as there's no reason the rows you want to access at any given point should have any correlation to when those rows were created.<<
That depends entirely on the nature of your workload. There are plenty of common workloads such as sensor data or log entries for which the common read patterns are very much correlated with the order in which the rows were written.
Firebase creates lexicographically ordered random-looking UUIDs so you can always check relative entry order: https://firebase.googleblog.com/2015/02/the-2120-ways-to-ens...
The article doesn't mention them but there are incremental only UUIDs (NEWSEQUENTIALID in TSQL) that solve that problem. Or you can prefix it with a timestamp.
The sequencing of those UUIDs resets every time Windows restarts. Given the frequency of OS and SQL Server updates that require reboots, that wasn't good enough for any of my workloads.
Agree. And if you have two clusters, they will start at different IDs. Which is why I would probably prefix them with a timestamp (if it is not a sensitive information). Say the number of seconds since 1-1-2000. Fits on 32 bits, that still leaves you with 96 bit of entropy for a UUID, which is plenty to guarantee uniqueness unless you are working at massive scale.
Don't they have the same problems the article dislikes about auto increment numbers?
I don't think so. They don't disclose information (other than the timestamp if you use one, but each ID is still random). You will not get collisions between tables either. And unless you operate on a massive scale, you are unlikely to get collisions between machines (and you can always xor the ID with the hash of the server name to further reduce the collision risk).
For anyone interested checkout UUID v1 which includes a timestamp.
I think you’re better off keeping the auto increment PK then expose a uuid token anywhere it can be viewed.
You probably don’t want to be literally passing around the literal primary key if you are concerned about leaking security information.
But I think it’s probably better so the user id being known is not a problem. For example, not caring if people know how users/widgets/whatever.
You probably don’t want to be literally passing around the literal primary key if you are concerned about leaking security information.
But I think it’s probably better so the user id being known is not a problem. For example, not caring if people know how users/widgets/whatever.
This. Use the autoinc for FK. Use GUID for exposing.
Making blanket generalizations in the title of an article is a terrible idea. Auto-increment integer IDs work for different use cases than UUIDs. This article does a decent job of describing the scenarios where UUIDs work well, but its section on when UUIDs are less optimal is a bit lacking. In particular, UUIDs are generally a bad choice of key for clustered or index-organized tables because their random insertion order results in fragmentation in the primary index. PostgreSQL doesn't have clustered indexes and perhaps that's why that drawback wasn't mentioned.
> A relational database is a graph where nodes are called entities and edges relations.
No, it's not. That's a common rationalization of the term by people who have zero knowledge of relational DB theory, but a relation is answer of entities, not a relationship between them; do, in this graph interpretation, “relation” isn't the right name for an edge, as it names a set of structurally similar nodes. An edge is a “(foreign key) relationship”.
> The role of a primary key is to provide a stable, indexable reference to an entity. You have two ways to construct your primary key: either as a semantic key, or as a technical key.
Why is this piece inventing new language when there are existing, widely recognized terms? This is the classic ”natural key” vs. “surrogate key” distinction.
> Also, serial is a 32 bit integer.
There are good reasons to avoid autoincrement, but that's not one, it’s just a reason to use bigserial instead of serial.
> If you don't have mutable state, then you don't need a technical primary key, and you can use a semantic key.
Even if you have mutable state, you can use a natural (“semantic”) key instead of surrogate (“technical”) key so long as the identifying attributes are immutable (which, if they are genuinely identifying, they logically must be.)
No, it's not. That's a common rationalization of the term by people who have zero knowledge of relational DB theory, but a relation is answer of entities, not a relationship between them; do, in this graph interpretation, “relation” isn't the right name for an edge, as it names a set of structurally similar nodes. An edge is a “(foreign key) relationship”.
> The role of a primary key is to provide a stable, indexable reference to an entity. You have two ways to construct your primary key: either as a semantic key, or as a technical key.
Why is this piece inventing new language when there are existing, widely recognized terms? This is the classic ”natural key” vs. “surrogate key” distinction.
> Also, serial is a 32 bit integer.
There are good reasons to avoid autoincrement, but that's not one, it’s just a reason to use bigserial instead of serial.
> If you don't have mutable state, then you don't need a technical primary key, and you can use a semantic key.
Even if you have mutable state, you can use a natural (“semantic”) key instead of surrogate (“technical”) key so long as the identifying attributes are immutable (which, if they are genuinely identifying, they logically must be.)
Article doesn't mention this but if you're exposing UUIDs to users, it's nice to base62 or base58 encode them.
E.g.
E.g.
uuid v4: de7013fb-658e-40a0-9c02-d23080b57fdb
base58 : tt7Udwi9Tx9VE1ze7HDBxi
I like using both UUIDs and integers. I use auto-incrementing integers internally for all the model relationships, but the user only ever sees random UIDs. I also like having different UID prefixes for each model, so you can tell what a given UID is referring to.
Here's the code I'm using for Rails: https://gist.github.com/ndbroadbent/628ef1d0c044e402d7501fb7...
Here's the code I'm using for Rails: https://gist.github.com/ndbroadbent/628ef1d0c044e402d7501fb7...
128 bits can be a waste of space, but I appreciate not wanting to publicly reveal the table size.
One of my biggest wishes from a database would be a function similar to auto increment, except that would pick randomly from the column's full integer range, guaranteed not to collide. I almost never need 128 bits -- 32 bits will let me handle 4 billion users.
It seems to me that a column index could be used to do that quickly -- essentially, if it's a binary tree, pick branches randomly but never pick a branch known to be full.
One of my biggest wishes from a database would be a function similar to auto increment, except that would pick randomly from the column's full integer range, guaranteed not to collide. I almost never need 128 bits -- 32 bits will let me handle 4 billion users.
It seems to me that a column index could be used to do that quickly -- essentially, if it's a binary tree, pick branches randomly but never pick a branch known to be full.
Ehh, if your main concern if obfuscation of the primary ID, you can... obfuscate your primary IDs to be used in your URLs.
Does anyone use ULIDs [1] instead of UUIDs for stuff like this? I like that they're sortable.
[1] https://github.com/alizain/ulid
[1] https://github.com/alizain/ulid
I've been using ULIDs heavily in my projects lately. The lexicographic sort was something I was particularly looking for, because CouchDB _id indexes are lexicographic, meaning I more often than not can just use the main index and the order it returns the items. Given these projects are "offline first" apps, a stable sort and low probability of collisions were key needs.
A UUID is not always non-predictable / entirely random. For example: `NEWSEQUENTIALID()` in MS SQL Server uses a randomly generated UUID, then auto-increments from there. More info on this function's usage here: https://www.codeproject.com/Tips/125526/Using-ROWGUIDCOL-in-...
This is why the writer specifically called out UUIDv4, which is random by definition. That's what type 4 means.
There are definitely better reasons that those given in the article. My two favourite are:
* it prevents tables having any obvious "natural" ordering. I still see developers writing queries, relying on the order of the data but not specifying ORDER BY. The flip-side to this is that sometimes they discover they need an ordering that they don't have - e.g. date record created - which is also great practice. * it removes pre-conceptions that developers might have about auto increments. I've seen auto-increment used for invoice numbers more times than I care to remember: a task it's entirely unsuited for. If a developer needs a monotonic ID, but don't have an AI field, they have to think longer and harder about the type of number they need, and what the rules about transactions rolling back need to be. Again, better correctness is the result (at a cost of more time / thought).
Auto-increment isn't a terrible idea at all, and there are good UI reasons to not like it, but there are also decent reasons to use something else.
* it prevents tables having any obvious "natural" ordering. I still see developers writing queries, relying on the order of the data but not specifying ORDER BY. The flip-side to this is that sometimes they discover they need an ordering that they don't have - e.g. date record created - which is also great practice. * it removes pre-conceptions that developers might have about auto increments. I've seen auto-increment used for invoice numbers more times than I care to remember: a task it's entirely unsuited for. If a developer needs a monotonic ID, but don't have an AI field, they have to think longer and harder about the type of number they need, and what the rules about transactions rolling back need to be. Again, better correctness is the result (at a cost of more time / thought).
Auto-increment isn't a terrible idea at all, and there are good UI reasons to not like it, but there are also decent reasons to use something else.
What makes autoincrement unsuited for invoice numbers, and what would you recommend instead? (I'll admit to being one of those developers who hasn't thought long and hard about this, but I can't off the top of my head come up with any reasons why they would be a problem.)
There is a different reason that auto incrementing ids are bad: they don't scale.
When you need to shard your table, the autoincrementing is is a bottleneck across all shards as you need to hold a lock on it when incrementing. In fact, that's exactly how it's implemented explicitly in POSTGRES.
And if you want something that's way MORE distributed such as CockroachDB or (RIP) Riak then forget about it.
But you don't necessarily need UUIDs. You can use for example https://www.npmjs.com/package/shortid
What we do is generate a random 7 character string (alphanumeric lowercase) and then check if it exists in the db and - if not - insert it, using a transaction. Otherwise repeat until one works.
When you need to shard your table, the autoincrementing is is a bottleneck across all shards as you need to hold a lock on it when incrementing. In fact, that's exactly how it's implemented explicitly in POSTGRES.
And if you want something that's way MORE distributed such as CockroachDB or (RIP) Riak then forget about it.
But you don't necessarily need UUIDs. You can use for example https://www.npmjs.com/package/shortid
What we do is generate a random 7 character string (alphanumeric lowercase) and then check if it exists in the db and - if not - insert it, using a transaction. Otherwise repeat until one works.
I'm using PostgreSQL's UUIDs as primary keys (and foreign keys) in a Phoenix application (a JSON backend to a React frontend). The only painful moments are:
* Typing SQL queries in the development database.
* Trying to remember which record is which by looking at the UUIDs. Often the first two characters are enough but it's a lot of random noise.
Inside the program it's all the same. No apparent performance penalties so far, but we don't have much data.
I used slugs in Rails apps not to disclose numerical ids from public URLs and data. There is no need to do that with UUIDs. However, if this were a server rendered web app I'll probably still generate slugs and use them in the URLs. UUIDs in permalinks are just too weird to look at.
* Typing SQL queries in the development database.
* Trying to remember which record is which by looking at the UUIDs. Often the first two characters are enough but it's a lot of random noise.
Inside the program it's all the same. No apparent performance penalties so far, but we don't have much data.
I used slugs in Rails apps not to disclose numerical ids from public URLs and data. There is no need to do that with UUIDs. However, if this were a server rendered web app I'll probably still generate slugs and use them in the URLs. UUIDs in permalinks are just too weird to look at.
ULIDs are a much better answer than type-4 GUIDs. You get insertion-order sorting with a large random component to prevent collisions/guessing.
A similar solution called COMB identifiers dates back at least 15 years in the MSSQL community.
Watch out for sort order in MSSQL with binary ULIDs cast to UNIQUEIDENTIFIERs; they sort with the last 4 bytes as most significant, so some byte swapping is necessary to prevent the performance degradation caused by index page splitting (I wrote a ULID implementation for MSSQL that handles this).
https://github.com/alizain/ulid
A similar solution called COMB identifiers dates back at least 15 years in the MSSQL community.
Watch out for sort order in MSSQL with binary ULIDs cast to UNIQUEIDENTIFIERs; they sort with the last 4 bytes as most significant, so some byte swapping is necessary to prevent the performance degradation caused by index page splitting (I wrote a ULID implementation for MSSQL that handles this).
https://github.com/alizain/ulid
Would it be useful to compress 128-bit UUIDs to fit them into narrower columns and reduce IO? I don't fully grasp the issues this would entail.
Looks like there's an old patent related to this:
https://www.google.com/patents/US5913217
Looks like there's an old patent related to this:
https://www.google.com/patents/US5913217
Who the heck uses a editor with tab completion to make changes to production database? As a DBA, I'm horrified...
"Use UUIDs as primary keys. They can be freely exposed without disclosing sensitive information, they are not predictable and they are performant."
When have a primary key ever been sensitive infomation ? And why would they be predictable ?
When have a primary key ever been sensitive infomation ? And why would they be predictable ?
If you use auto-incrementing IDs as both primary keys and account identifiers, which a lot of web applications do, it might be possible to identify admin and employee accounts because they're more likely to have the lower IDs. A recent Steam hack relied on this to allow bypassing the approval process for Greenlight[0].
[0]https://medium.com/swlh/watch-paint-dry-how-i-got-a-game-on-...
[0]https://medium.com/swlh/watch-paint-dry-how-i-got-a-game-on-...
UUIDs save you from accidentally relying on incrementing identifiers and they make it easier to keep them unique.
The downside is that they are really long and can’t be typed or remembered.
The downside is that they are really long and can’t be typed or remembered.
Use both. PK = int, APIID = UUID
Using integers, auto incrementing or not, reduces the resource pressure on indexing when doing complex joins.
When you need to expose a key, expose the APIID.
Using integers, auto incrementing or not, reduces the resource pressure on indexing when doing complex joins.
When you need to expose a key, expose the APIID.
That's the worst of both worlds. Writes are slower than ever since you have to update both indices on every insert. If you normalize your database you have to do a bunch of extra joins for every API response. If you denormalize it you have twice as many columns and either complex foreign key constraints (slowing down writes even further) or the possibility of getting your data into an inconsistent state.
If your relational database is experiencing on 64-bit hardware a lot more pressure indexing 128-bit columns than 32-bit columns, it may be time to consider looking for a better database.
this is all great, but what happens when you need to reference several uuids in an url?
Check out twitter snowflake for shorter distributed uuids
Snowflake has been retired in 2014 [1]. Are there any similar services around that are still maintained?
[1]: https://github.com/twitter/snowflake
[1]: https://github.com/twitter/snowflake
Aha yes the server has been retired - but the underlying principles were documented in a paper and there are quite a few implementations out there.
i expected a blockchain solution. disappointed
Your problem isn't auto-increment, it's what you're deciding to expose outside your database.
Many of your critiques stem from the simple assumption that primary keys will be shared externally (see final link below on why that's not necessarily a good idea). There's nothing wrong with using auto-increment primary keys, then creating a different, dedicated field to store external identifiers. Before UUID's were invented we used simple random numbers for the latter (converting to hex or base-64 in URL's if more compactness was desired) and they worked just fine.
I agree 100% with frik's comments. Keep in mind your team will be dealing with primary keys a LOT. In our own projects I've seen developers become a little less efficient when working with databases that overuse bulky UUID's. I don't feel they're particularly "human friendly". They take up more pixel width in SQL query results, leading to more back and forth scrolling. JOIN results starting with a whole screenfull of ID garble weren't uncommon. Unless your developers have eidetic memory they wind up doing more copy and pasting (with the associated cursor movements, clicks and ergonomic odometer ticks) to jot them down or share them. Ever tried to name a UUID over the phone? In the early days of your project, when you're doing a whole heck of a lot of raw interaction with your database, auto-increments can offer a slight advantage of being so much more compact, easier to remember, and simple to communicate. It may not sound like much of an edge, but it's one less friction point slowing down collaboration.
I think you might also be overlooking the fact that some UUID implementations are designed to be unique, not random, and depending on which you use might leak information or be predictable (https://blogs.msdn.microsoft.com/oldnewthing/20120523-00/?p=...). I'd be careful about sharing those primary keys unless you're sure they're generated from a decent [maybe cryptographically secure pseudo-] random number generator and aren't derived from the current time / MAC address. If you want to dive deeper there's a subtle tradeoff here between privacy and ease of sorting (e.g. in a clustered index).
I empathize with your argument that sequential keys can lend themselves to typos deleting the wrong record (which can be a pain even in non-production environments) and have seen arguments to the benefit of knowing UUID's in advance before a record is inserted (less constraints writing your SQL, reduced roundtrips) and ease of merging data between databases. It can also be deadly if your auto-increment generator reuses deleted keys and your system doesn't account for that. In the past I've created my own ID dispensing mechanisms optimized according to the features I wanted (e.g. sequential or random, reservable, globally or locally unique, human-friendly with number of digits growing in chunks, etc) but admittedly that could be overkill for your project.
I don't have anything personal against UUID's and don't mean to come off condescending, I just think you missed some aspects in the message you broadcast.
Here's a great, easy-to-read analysis of the pros/cons and when to use each: https://tomharrisonjr.com/uuid-or-guid-as-primary-keys-be-ca...
Many of your critiques stem from the simple assumption that primary keys will be shared externally (see final link below on why that's not necessarily a good idea). There's nothing wrong with using auto-increment primary keys, then creating a different, dedicated field to store external identifiers. Before UUID's were invented we used simple random numbers for the latter (converting to hex or base-64 in URL's if more compactness was desired) and they worked just fine.
I agree 100% with frik's comments. Keep in mind your team will be dealing with primary keys a LOT. In our own projects I've seen developers become a little less efficient when working with databases that overuse bulky UUID's. I don't feel they're particularly "human friendly". They take up more pixel width in SQL query results, leading to more back and forth scrolling. JOIN results starting with a whole screenfull of ID garble weren't uncommon. Unless your developers have eidetic memory they wind up doing more copy and pasting (with the associated cursor movements, clicks and ergonomic odometer ticks) to jot them down or share them. Ever tried to name a UUID over the phone? In the early days of your project, when you're doing a whole heck of a lot of raw interaction with your database, auto-increments can offer a slight advantage of being so much more compact, easier to remember, and simple to communicate. It may not sound like much of an edge, but it's one less friction point slowing down collaboration.
I think you might also be overlooking the fact that some UUID implementations are designed to be unique, not random, and depending on which you use might leak information or be predictable (https://blogs.msdn.microsoft.com/oldnewthing/20120523-00/?p=...). I'd be careful about sharing those primary keys unless you're sure they're generated from a decent [maybe cryptographically secure pseudo-] random number generator and aren't derived from the current time / MAC address. If you want to dive deeper there's a subtle tradeoff here between privacy and ease of sorting (e.g. in a clustered index).
I empathize with your argument that sequential keys can lend themselves to typos deleting the wrong record (which can be a pain even in non-production environments) and have seen arguments to the benefit of knowing UUID's in advance before a record is inserted (less constraints writing your SQL, reduced roundtrips) and ease of merging data between databases. It can also be deadly if your auto-increment generator reuses deleted keys and your system doesn't account for that. In the past I've created my own ID dispensing mechanisms optimized according to the features I wanted (e.g. sequential or random, reservable, globally or locally unique, human-friendly with number of digits growing in chunks, etc) but admittedly that could be overkill for your project.
I don't have anything personal against UUID's and don't mean to come off condescending, I just think you missed some aspects in the message you broadcast.
Here's a great, easy-to-read analysis of the pros/cons and when to use each: https://tomharrisonjr.com/uuid-or-guid-as-primary-keys-be-ca...
UUID are ugly, hard to remember in your head, take a lot of space, etc.
I favor auto increment IDs any day, except in specific situations. It's worth to consider to learn how other sites dealt with IDs in public, while internally using auto increment IDs. Short: there are several strategies.
But to each their own, if you love UUID, just use it, but don't write lame blog posts how terrible things are.
I favor auto increment IDs any day, except in specific situations. It's worth to consider to learn how other sites dealt with IDs in public, while internally using auto increment IDs. Short: there are several strategies.
But to each their own, if you love UUID, just use it, but don't write lame blog posts how terrible things are.
I always try to use a semantic key first, if it makes sense. For example, I see this in my current database:
id utility
1 electric
2 gas
3 water
What is the point of the numeric ids versus simply making the name the primary key? I'd argue there is none, plus there significant drawbacks.
Every time we start with the utility name and need to get the row id, the database needs to search on a non-primary key. This is going to be commonly used in JOINs (and tables designed with superfluous ids lend themselves to many gratuitous joins.) Optimizing the primary keys based on the typical data used to search (actual key data from the user's standpoint) will make your DB blazing fast.
Also, using integers everywhere (or even worse, UUIDs) destroys the readability of queries. How many integer ids could be used in a query and still be understandable? The id `3` could mean almost anything, but the id `water` brings meaning to queries. The benefits to maintainability are significant.
Lazy DB designers seem to have a bad habit of adding auto-ids to everything regardless of value. I even see joiner tables (that should be using the composite primary key of multiple foreign keys) using unnecessary auto ids! This is sloppy work.
In short, I have found significant benefits to using semantic keys first (if appropriate - there are definitely reasons to use incremental integer or UUID keys in many tables) for these reasons:
1. Fewer columns without loss of meaning 2. Fewer joins needed 3. Faster searches 4. More human-readable
id utility
1 electric
2 gas
3 water
What is the point of the numeric ids versus simply making the name the primary key? I'd argue there is none, plus there significant drawbacks.
Every time we start with the utility name and need to get the row id, the database needs to search on a non-primary key. This is going to be commonly used in JOINs (and tables designed with superfluous ids lend themselves to many gratuitous joins.) Optimizing the primary keys based on the typical data used to search (actual key data from the user's standpoint) will make your DB blazing fast.
Also, using integers everywhere (or even worse, UUIDs) destroys the readability of queries. How many integer ids could be used in a query and still be understandable? The id `3` could mean almost anything, but the id `water` brings meaning to queries. The benefits to maintainability are significant.
Lazy DB designers seem to have a bad habit of adding auto-ids to everything regardless of value. I even see joiner tables (that should be using the composite primary key of multiple foreign keys) using unnecessary auto ids! This is sloppy work.
In short, I have found significant benefits to using semantic keys first (if appropriate - there are definitely reasons to use incremental integer or UUID keys in many tables) for these reasons:
1. Fewer columns without loss of meaning 2. Fewer joins needed 3. Faster searches 4. More human-readable
[deleted]
Similarly, if you love auto-increment, use it.
Don't write lame HN comments deriding others opinions.
Don't write lame HN comments deriding others opinions.
I agree
So, you would rather have your primary keys be a 36 character UUID string instead of a 4-8 byte integer?
This is for every single row in your database and will increase the size of your database substantially.
This is for every single row in your database and will increase the size of your database substantially.
Don’t store UUIDs as strings.
Use the native UUID data type, which only uses 16 bytes (UUIDs are basically just 128 bit integers). That is still double the size of a 64 bit integer, but the trade off is that it can be random and universally unique.
Use the native UUID data type, which only uses 16 bytes (UUIDs are basically just 128 bit integers). That is still double the size of a 64 bit integer, but the trade off is that it can be random and universally unique.
... for databases that have a native UUID type. MySQL doesn't, and the choices are either store them as strings or store them as non readable bytes. Not the greatest options.
You can store them as bytes and use a function to convert them to/from a display format (which is what DBs with a built in UUID type do under the covers.)
Yes, and I've done that in the past; it's still somewhat laborious though.
>36 character UUID string
To clarify, UUID is stored in the db as 16 bytes (128 bits): https://en.wikipedia.org/wiki/Universally_unique_identifier#...
You would not store the 32 hex characters plus 4 hyphen characters as strings in the db. That UUID string format is for human readable display not disk storage.
(To downvoters, if 16 bytes is incorrect, please reply with what you believe to be the correct # of bytes for UUID.)
To clarify, UUID is stored in the db as 16 bytes (128 bits): https://en.wikipedia.org/wiki/Universally_unique_identifier#...
You would not store the 32 hex characters plus 4 hyphen characters as strings in the db. That UUID string format is for human readable display not disk storage.
(To downvoters, if 16 bytes is incorrect, please reply with what you believe to be the correct # of bytes for UUID.)
on MySQL or any database that doesn't have it as a datatype, its 36 char on latin charset. If you leave everything as UTF-8, then its x3 that amount.
Try it for yourself.
Try it for yourself.
I didn't downvote your previous comment but I'm guessing it got downvotes because the blog post you're criticizing is about PostgreSQL not MySQL. PostgreSQL can store UUID in 16 bytes.
As a side comment about MySQL, there are various techniques to store UUID more compactly as 16 bytes inside of a "binary(16)" type like this:
https://stackoverflow.com/a/28252188
... or similar technique shown at the bottom of the Percona blog:
https://www.percona.com/blog/2014/12/19/store-uuid-optimized...
As a side comment about MySQL, there are various techniques to store UUID more compactly as 16 bytes inside of a "binary(16)" type like this:
https://stackoverflow.com/a/28252188
... or similar technique shown at the bottom of the Percona blog:
https://www.percona.com/blog/2014/12/19/store-uuid-optimized...
This line seems to be the sole justification for the hyperbolic title. You might do tab-completed typo...
There's three justifications listed - the above (fairly minor, imo) one, information disclosure, and entity enumeration (which in this context is just another term for information disclosure).
The part on information disclosure has this line:
> all it takes to know your user count is to create an account. A number of social networks boasting impressive user bases were pwn3d this way
What? I would love a reference or explanation here. Who has been "pwn3d" by exposing their user count?
The "security through obscurity is bad" rhetoric has been rightly debunked plenty at this stage - obscurity is a valid component of layered security, but obscuring your table row count seems... odd. Why would that be a secret?
If you're worried about people being able to enter primary keys into a URL to access/scrape arbitrary records, you need better auth.