If the table itself has only a few columns, the extra primary key space overhead will become more significant. For Auto Increment Integer, when stored as Long format, it occupies 8 bytes. Thinking of issue id, obviously, issue-123 is much more readable than issue-b1e92c3b-a44a-4856-9fe3-925444ac4c23. This is especially valuable if we would expose it externally. Using auto increment integer/serial as the primary key in your SQL database is also quite common and every major database engine provides the native support. On the other hand, PostgreSQL uses heap instead of clustered primary key, thus using UUID as the primary key won't impact PostgreSQL's insertion performance. This is because it requires reordering the rows to place the newly inserted row at the right position inside the clustered index. For database like MySQL, Oracle, which uses clustered primary key, version 4 randomly generated UUID will hurt insertion performance if used as the primary key.People design their own UUID format to fix this and there is also a draft proposal to standardize it. Though v1 UUID format contains timestamp, it encodes the timestamp using little-endian in that the least significant time appears first, which renders the UUID hard to sort according to creation time. Not naturally sortable according to creation time. Version 1 UUID stores timestamp info, could be useful sometimes.However, your security team would always insist that a public accessible UUID path does not meet the security standard. A sense of secure since malicious user can't guess the ID.Stateless, it can be generated on the fly.Easy for migrating data between systems since collision is only theoratically possible. No false positive for finding items using log. Most of the time, people either choose v4 (random UUID) or v1 (timestamp UUID) There are 5 standard UUID formats nowadays. And 99% of the time, developers need to choose between either UUID or Auto Increment Integer/Serial.ĭevelopers may not realize initially, but choosing the primary key type can have consequential impact down the road and it's almost impossible to switch afterwards.Ĭhoosing a proper primary key format requires a good understanding of both the business requirements as well as the underlying database system, so that the schema designer can make the educated tradeoff. One of the first things when designing a new SQL database schema is to decide which type of primary key to use. Tl dr choosing Auto Increment Integer 95% of the time for readability.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |