Travis Horn

When picking a primary key for a table in a database, sometimes there is an obvious choice. If you’re creating a table of users, and users authenticate using their email address, you probably want the email address to be unique amongst all users, so it may be a good choice for a primary key. This strategy has some drawbacks though. For example, if the user wants to change their email address, you can allow it, and you can even cascade that change to other tables which relate to it. But you also effectively lose the relation from the old email address to the new one. For example, if a user changes their email address from alice@example.com to bob@example.com, can you obviously tell that the bob@example.com user used to be alice@example.com? Perhaps in your application, you don’t care, but perhaps you do. Another drawback is in case you need to share user identifiers in something like a URL parameter. The URL example.com/user/bob@example.com will need to be escaped correctly. In addition, that URL exposes Bob’s email address, which in many cases you might not want to do.

For that reason, database administrators and developers often use a surrogate key that is generated by the database when the record is created.

A very common strategy for a surrogate key is to use an auto-incrementing integer. There are a lot of benefits to this. For one, the identifier never has to change, no matter what the user decides to do with their account. If a user creates an account as is automatically assigned the identifier 7, then they can always stay 7 even if they change their email, change their username, deactivate their account, reactivate their account, and more. Another benefit is that it’s a small amount of data. It doesn’t take a lot of space to store compared to other possible unique identifiers. It’s also sequential, so you can sort by it to sort from by relative creation time.

However, it’s very predictable. If you provide a URL route that looks like example.com/document/237, users could easily guess that other documents may be able to be accessed via tampering with the URL. They might try example.com/document/236 or example.com/document/238.

I’ll share a story that highlights another downside to using auto-incrementing integers as primary keys. One time in my career, a database got corrupted. While we were working on recovering the corrupted data, I needed to get the application back up and running as soon as possible. So I restored a backup from an earlier time. This restored service.

The data since the last backup was temporarily lost, and we were working to recover it, but in the meantime the application was up and new database records were being restored. However, since the IDs were auto-incrementing integers, and the database had been restored from an earlier point in time, the newly inserted rows actually shared integers that were supposed to be unique with the corrupted data we were recovering. So when it came time to re-insert the recovered data, there was a lot of confusion over identifiers that needed manual correction.

Since then, I’ve tended to default to using UUIDs as a primary key. Specifically, UUID v4. These are randomly generated identifiers that have an astronomically low collision rate, meaning you can generate as many as you need, as fast as you need, forever, and virtually never run into the issue of the same identifier being generated twice.

UUIDs solve a lot of the problems that auto-incrementing integers face. However, there are some downsides.

TODO: give example downside

So choosing a primary key and a surrogate key strategy will always come down to your specific needs for your specific application.

In September of 2024, MariaDB released version 11.7, which introduced support for generating UUID v7 natively. Since then, I’ve switched over from using UUID v4 to UUID v7 and I’ve been happy.

Why UUID v7? It adds some order to the randomness. Which is beneficial in many cases. With UUID v4, when you insert a new record, the database must insert that ID into a random location in the index tree. This causes page splitting which causes more time to be spend on disk input/output. But with UUID v7, new IDs are always “greater” than previous ones. They are appended to the end of the index, giving you some of the same benefits as an auto-incrementing integer.

In addition, the creation time is encoded into the identifier, which eliminates the need for a separate created_at column.

The biggest drawback is data leakage. If the identifier is public, the creation time is public. This could be a big problem in some use cases. If it’s not a problem for you, you may consider switching over to UUID v7 like I did for most tables across my databases.

But it is still crucial to note. So here’s an explicit warning: If leaking the creation time is dangerous for your application, avoid UUID v7.

Consider this example. In the past I may have defined a table for users like so:

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  -- Creation time tracked separately from identifier above
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  email_address VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

Or like this:

CREATE TABLE users (
  -- Application logic must generate the UUID v4 in older versions of MariaDB
  id UUID NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  email_address VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

Now, I define tables like this:

CREATE TABLE users (
  id UUID NOT NULL DEFAULT (uuid_v7()),
  created_at DATETIME(3) GENERATED ALWAYS AS (
    FROM_UNIXTIME(
      CONV(LEFT(HEX(id), 12), 16, 10) /1000
    )
  ) VIRTUAL,
  email_address VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

The created_at column is a virtual column that’s calculated on the fly from the id. This saves disk space and makes sure the creation time never drifts away from the identifier, as it is directly tied to it.

Wow, that created_at column looks complicated! You can copy and paste it wholesale and it will just work, but it’s helpful to be technically aware of how it works so here’s a breakdown.

The MariaDB UUID data type is stored as binary (16 bytes) on the disk to save space. HEX(id) converts it to hexadecimal.

With UUID v7, the first 48 bits represent the timestamp. Since 1 hexadecimal character represents 4 bits, we need the first 12 characters. 48 / 4 = 12. We get that with LEFT(…, 12).

Then, we convert that hexadecimal to decimal with CONV(…, 16, 10). This converts from hex (base-16) to decimal (base-10).

Since MariaDB’s date functions work with seconds, not milliseconds, we divide the result by 1000 to do that conversion.

Finally, FROM_UNIXTIME(...) takes the timestamp in seconds and converts it into a standard DATETIME format.

Inserting data is done as per usual.

INSERT INTO users (email_address) VALUES ('travis@travishorn.com');

Check out what the inserted data looks like.

SELECT * FROM users;

The result:

The created_at creation time is calculated on the fly.

Here’s something cool: If necessary, you can still index these values! Even though the column is not stored on disk (it’s VIRTUAL), MariaDB allows you to create an index on it.

If you add an index, calculated values inside the index tree will be persisted, making queries fast.

Even if you don’t place an index on created_at, if you need fast sorting by creation time, you can order by id.

SELECT * FROM users ORDER BY id;

Since UUID v7 has an order to it, and MariaDB knows this, it stores and retrieves them in a logical way that makes it fast.