There's a storage cost to the primary key, its index, any foreign keys, and any of their indexes. UUIDs aren't a magic bullet, there are trade-offs, and it's important to understand them.ġ6-byte UUIDs are larger than 4-byte integers or 8-byte big integers. Reasons to not use UUIDs for primary keys But if you do, and you use auto-incrementing IDs, you'll have to contend with sequence generation that doesn't produce collisions. "But primary keys shouldn't be exposed to the front-end!" But you need some kind of ID for the front-end to interact with, so what are you going to use?Īuto-incrementing IDs are difficult in a multi-master system.įirst, please don't run a multi-master system. And if that database is supporting a RESTful web service you are likely to end up with endpoints like /users/:id that someone might be able to brute force. The same thinking could be applied to user accounts which is even more dangerous. If you're designing a database for something like e-commerce, using an auto-incrementing ID could accidentally expose things like: how many orders exist in the system, at what rate orders are made, and so on. If that data ever needed to be migrated somewhere else, or combined with other data, you're likely to run into collision issues.Īuto-incrementing IDs expose internal information. When inserting multiple related entities to database at the same time it's easier to generate IDs ahead of time instead of needing to: start a transaction, insert the parent entity, return the last inserted row ID, then insert all the other entities.Īuto-incrementing IDs are only unique to the database and table that they came from. UUIDs can be generated before insert time. Having an auto-incrementing ID such as "1234" isn't meaningful on its own, especially in a database with dozens of tables. There's a lot of value in having an identifier refer to only one entity. UUIDs are unique across tables and databases. V10.1 (2003) introduced SYS_GUID() which is close enough Reasons to use UUIDs for primary keys v8.3.0 (2008) introduced the uuid-ossp module and its functions such as uuid_generate_v1() and uuid_generate_v4().
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |