When using UUIDs, should I also use AUTO_INCREMENT?

Question

We're building a new web app that will have an offline iPad/Android app version on a number of local devices that will involve inserts of new data. As such we require the use of UUIDs to allow for the necessary two-way synchronization with the master database. For this we'll be storing the UUID as a BINARY(16) primary key.

The problem I've learned after researching is that the time required for non-sequential primary key inserts will increase over time and that these inserts will lead to fragmentation (as answered here). The benefit to AUTO_INCREMENT is that new rows will usually just be added to the end of the table and so will not run into the speed problems with UUIDs.

My question is whether or not it is a better idea to use an AUTO_INCREMENT column as the primary key and then have the UUID column as a non-null unique index? Presumably this will have the speed benefits of sequential inserts whilst retaining the necessary UUIDs required for synchronizing distributed databases.

The one issue I can see with this is that the UUID needs to be used as a reference (using foreign key constraints) to other tables (i.e. a list of problems attached to an inspection which in turn is attached to a site, all of which are involved in inserts and so all of which require UUIDs). Semantically, it makes more sense for the primary key to be the reference, but as its a distributed system we can't use AUTO_INCREMENTS for these. Are there drawbacks to using a (non-null) unique index, rather than primary key, for these references (and, of course, the JOINs that will come with them)?

It might also be worth noting that the master (online) database uses MySQL (InnoDB) and the distributed (offline) databases use SQLite.

Edit:

Considering that it is perhaps better to have the UUID as a primary key (as that's semantically what it is), would I gain the benefit of sequential inserts if I set the UUID as a primary key and the AUTO_INCREMENT column as a non-null unique index? Or is it only the primary key that is of relevance when determining where to insert a new row?

Answer 1

Using autoincrements as primary plus an uuid column is a valid model, but you would still have to struggle with some problems the autoincrements brings, it all depends on how you do the synchros.

Anyway I've been working with uuid's as primary keys (my current database have half a million records) and it's still pretty fast, it only slow downs a bit on the inserts, but unless you have very high volumes of inserts daily it shouldn't scare you.

If you use Sql-Server another solution you could have a look at is the Sequential UUIDs, which have a slightly greater collision chances than normal UUID's, but the absolute collision chances are still pretty low, and as they are partially sequential that covers the problems with the fragmentation.

Answer 2

Once you have a big distributed data warehouse, if you use UUID or GUID as unique key and use it in join later on, it is not good.

Instead of using UUID or GUID, please create sequential surrogate key in your master database or in your data pipeline.

Share our project experience as a reference. We have 300 Billion records saved in parallel data warehouse, in our system, auto incremental key even not supported. We use 8 bytes bigint as primary key (actually unique key in our system is not supported either, but that's not affect logical uniqueness), when we processing file and load file, we use 3 bytes to generate file ID, which is 2^24 files, we have about 2,000 files need to load per day, so, 2^24 can support about 25 years, if it is not wrong.

We use the rest of 4 bytes as row id, which is 4 billion rows, we don't have 4 billion rows in any file. We reserve 1 byte. During the ETL processing, we only need to track the file ID in the master database, which support auto incremental ID, when we need to generate record ID when processing file, we combine FileID+reserve 1 byte+4 bytes rowID.

Answer 3

From https://uuid.fyi/uuidorint

UUID

Pros

• Globally unique.
• Stateless, it can be generated on the fly.
• Secure since malicious user can't guess the ID.
• Version 1 UUID stores timestamp info, could be useful sometimes.

Cons

• Not readable.
• 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. This is because it requires reordering the rows in order to place the newly inserted row at the right position inside the clustered index. On the other hand, PostgreSQL uses heap instead of clustered primary key, thus using UUID as the PK won't impact PostgreSQL's insertion performance.

Auto Increment Integer/Sequence

Pros

• Readable. This is especially valuable if we would expose it externally. Thinking of issue id, obviously, issue-123 is much more readable than issue-b1e92c3b-a44a-4856-9fe3-925444ac4c23.

Cons

• It can't be used in the distirbuted system since it's quite likely that different hosts could produce exactly the same number.
• It can't be generated on the fly. Instead, we must consult the database to figure out the next available PK.
• Some business data can be exposed, since the latest ID could represent the total number of inventory. Attackers can also scan the integer range to explore leakage (though it shouldn't happen if ACL is implemented correctly).

Answer 4

You could use a composite primary key, composed of an auto-incrementing bigint ID value assigned by the offline client plus a bigint ID assigned to the client. So you'd have entry 15 made on client 1235.

A client would preferably request its ID from the server before the first edits are made, for example when it first retrieves the server's master data.