0

I'm trying to choose a data type for primary key in a relational database (MySQL 8) for a SaaS application in the Cloud. The problem I'm having is trusting the information I've found online. I've only found two or three good resources as new as 2017, while the rest are anywhere from 3 to 9 years ago. The debate between two main options, either a UUID or an auto-incrementing integer, is quite a draw between yay and nay. There's also another option to use an auto-inc integer with a server id as a composite key. I've listed the pros and cons (that I care about) of each type below, however, I don't know what is still relevant, and what are the best practices.

For example, is it as simple as "use a UUID for Cloud, use auto-inc id for just about everything else"?

UUID primary key ( stored as BINARY(16) )

Pros

  1. Unique across databases
    • basically a requirement for cloud databases
  2. Can generate from client
    • extremely useful for inserting multiple records where each relies on knowing the id of the previous one
  3. Less likely to cause issues if switching to, or using in addition to, a NoSQL database in the future, and keeping the public facing API the same
    • In a REST API, an endpoint containing the id will still be valid

Cons

  1. screws up indexing, due to the random nature of UUIDs
  2. larger data type, which can really add up when referenced as other tables' foreign keys
  3. cost of generating the UUID?
  4. performance gets bad on really large tables

Auto-Increment Integer Id (AIID) primary key

Pros

  1. Sequential, meaning better performance and indexing
  2. Smaller data type, meaning:
    • Less storage requirements
    • Will be way more optimal when referenced as a foreign key in other tables, many times all over the database

Cons

  1. Sequential, which... is bad for cloud databases

AIID - Server Instance Id composite primary key

Pros

  1. Unique across databases
    • Unless cloud servers/instances are removed and re-added with the same id...
  2. Avoids the pitfalls of UUID

Cons

  1. May not carry over to other databases well
  2. Slightly more complex queries
    • I actually only use this with intermediate tables in many-to-many relationships, so I don't know for certain

put on hold as off-topic by mustaccio, hot2use, LowlyDBA, McNets, Paul White Feb 11 at 23:29

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Tip of the iceberg - the question or comments reveal an underlying issue that would need extensive investigation by a consultant or database vendor support team: issues like this do not fit the SE Q&A model well. For more information see this meta post." – Paul White
If this question can be reworded to fit the rules in the help center, please edit the question.

0

Nothing has changed over the years in this area except that RAM sizes have gotten bigger, hence the Con for UUIDs has diminished a little.

You say "Cloud", but you have not stated whether that involves more than one server for your application.

If you don't need to have independent clients (or whatever), don't use UUIDs. If you do need such, there are other ways:

Have a server that delivers ids. All clients have to talk to this server briefly, then it scales reasonably well.

Have the UUIDs somewhat time-oriented. This can be easily done by rearranging the bits in type 1 uuids via the new functions in MySQL 8, or manually, see functions here. Alternatively, you could devise ids that start with the time, then a sequence, then the client's id.

Another flavor: A id-server that dispenses 100 ids at a time. Each client can cruise along for some time before needing to request another 100.

  • So, the app is just a collection of services - a front end, a backend API, a database.. they will all be hosted in the Cloud. Each being run on their own instance, whether a server or container within a server. – slanden Feb 9 at 20:38
  • I thought about that; Having a server that delivers ids, but at that point you could just use integer because it'll be unique again. The problem is the dependency on that one server. – slanden Feb 9 at 20:41
  • @slanden - All your services duplicated for redundancy (HA)? Are the database servers replicated for HA? The id-server could be replicated for HA. I know of a popular photo site that depends on a single (plus replica) server for ids. – Rick James Feb 10 at 0:23
  • I'm hosting on a Platform as a Service and not sure about the answers at this time, but I guess duplicated for handling higher traffic, many connections to the database if/when the app grows. I could do it that way but the question is more.. is there a rule of thumb for deciding which solution to employ? – slanden Feb 10 at 2:22
  • @slanden - How many queries per second are you anticipating? How many GB of data will it grow to? – Rick James Feb 10 at 18:41

Not the answer you're looking for? Browse other questions tagged or ask your own question.