<commit_before>+++
image_alt = "Looking down the coastline towards Pacifica"
image_orientation = "portrait"
# image_url = "/photographs/nanoglyphs/026-ids/salesforce-park@2x.jpg"
image_url = "/photographs/nanoglyphs/026-ids/pacifica@2x.jpg"
published_at = 2021-06-21T04:31:56Z
title = "Identity Crisis: Sequence v. UUID as Primary Key"
+++

Last month, Cybertec put together an excellent piece on the use of [UUIDs vs. `bigserial` vs `IDENTITY`](https://www.cybertec-postgresql.com/en/uuid-serial-or-identity-columns-for-postgresql-auto-generated-primary-keys/) as primary keys in a Postgres database. Although freshly written, its only the latest talking point in a long-standing discussion amongst DBAs and database nerds that's been ongoing for years -- should you use integers (AKA serials, AKA sequences) or UUIDs as primary keys?

Its publication was quite timely for me, as I'd been considering it very recently in the context of our own application, which I joined to find was using UUIDs, contrary to what I probably would have chosen had I been there at the beginning. It'd come up elsewhere as well. Years ago when we published the [Heroku HTTP API design guide](/fragments/heroku-http-api-design-guide), we suggested [using UUIDs to identify all resources](https://github.com/brandur/heroku-http-api-design#provide-resource-uuids), but without any real rational for _why_, and quite fairly, someone had recently opened a GitHub issue on the repository to challenge the point.

Over the years since the guide was written I'd starting leaning further back in the direction of integers being the right answer, but now that I've read into the subject more deeply, I'm less convinced that there's a right answer at all. There are nominal benefits on each side, and some pitfalls that should clearly be avoided, but when used mindfully, both integers and UUIDs are quite workable, with appropriately levered UUIDs maybe edging out a little ahead (read on).

### UUIDs, V4, ups and downs (#uuids)

```
CREATE TABLE has_uuid_pkey (
   id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
   ...
);
```

UUIDs are 128-bit sequences that are canonically represented in hex-encoded string form like `63bb4648-0b42-497f-9ad8-571cd60ee4f3`. It's worth remembering that although we're used to seeing them as strings, when they're being stored (properly) in a database, to the machine they're not all that different from any other sequence of bits -- they're twice the length as a standard 64-bit integer, but stored and compared similarly.

When we're talking about UUIDs, we're often talking about _V4_ UUIDs, which is a fancy way of saying that the entire UUID byte sequence is generated randomly, as opposed to using an algorithm based on clock sequence or anything else.

UUIDs as primary key aren't a slam drunk, but do have some advantages:

* The fact that they're random means that they don't rely on a single sequence to be generated. Multiple entities can generate IDs independently, but still store them to a shared data store without clobbering each other. There's no single bottleneck that could one day be a problem for scaling or performance.

* They're opaque from a user-facing perspective. With sequences, users may be able to make inferences about how much data is being generated by watching them increment over time. Malicious users might take advantage of them to try attacks based on ID iteration or collision.

* They occasionally reduce operational accidents. Especially in smaller programs it's not unheard of drop into `psql` and fix something manually in the database, and accidentally use an integer ID on the wrong table while doing so. Because integer sequences start from 1 and share a range everywhere, that might mean that you change the wrong entry. I'll be the first to admit that this has happened to me, although luckily without any catastrophic fallout. It doesn't happen with UUIDs because there's enough entropy to make the chance of overlap ~zero system-wide. If you update or delete on the wrong table with an ID that's a UUID, nothing will happen.

But these upsides are heavily counterbalanced by serious performance considerations:

* Because V4 UUIDs are random, new entries might be inserted anywhere in an index (most commonly a B-tree). That means having to bring more pages into memory and more cache misses. With a `bigserial`, because all new numbers are in close succession, the index is almost always operating on just a handful of pages near the end.

* Relatedly, with UUIDs writing to pages all over the place, those modified pages all need to be sent the write-ahead log (WAL) for replication. This results in an amplification effect where more WAL needs to be written, which means more data to replicate across network links, and an increased likelihood of rockiness in production.

At smaller scale, neither of these is likely to cause much trouble. Postgres is very efficient, and working with UUIDs will mostly be fine. However, in a hot system doing a lot of inserts, the effect is significant. Here are some charts from 2nd Quadrant showing the lopsidedness of using random UUIDs versus more deterministic identity techniques. With a large data set, notice how poorly randomness performs on transaction throughput and how much much additional WAL it produces:

<img src="/photographs/nanoglyphs/026-ids/uuid-tps@2x.png" alt="Transactions per second comparison" class="img_constrained" loading="lazy">

<img src="/photographs/nanoglyphs/026-ids/uuid-wal-medium-normalized@2x.png" alt="WAL size comparison" class="img_constrained" loading="lazy">

_(These from [2nd Quadrant](https://www.2ndquadrant.com/en/blog/sequential-uuid-generators/). In the first graph, see how the blue bars (random UUIDs) for medium/large data sets are so much smaller than the other strategies, meaning fewer inserts are possible. In the second, see how random produces orders of magnitude more WAL volume. Actually, the comparisons are a little more complex than UUID vs. `bigserial` but give you the right general idea -- more below.)_

---

{{NanoglyphSignup .InEmail}}
<commit_msg>A few more edits<commit_after>+++
image_alt = "Looking down the coastline towards Pacifica"
image_orientation = "portrait"
# image_url = "/photographs/nanoglyphs/026-ids/salesforce-park@2x.jpg"
image_url = "/photographs/nanoglyphs/026-ids/pacifica@2x.jpg"
published_at = 2021-06-21T04:31:56Z
title = "Identity Crisis: Sequence v. UUID as Primary Key"
+++

Last month, Cybertec put together an excellent piece on the use of [UUIDs vs. `bigserial` vs `IDENTITY`](https://www.cybertec-postgresql.com/en/uuid-serial-or-identity-columns-for-postgresql-auto-generated-primary-keys/) as primary keys in a Postgres database. Although freshly written, its only the latest talking point in a long-standing discussion amongst DBAs and database nerds that's been ongoing for years -- should you use integers (AKA serials, AKA sequences) or UUIDs as primary keys?

Its publication was quite timely for me, as I'd been considering it very recently in the context of our own application, which I joined to find was using UUIDs, contrary to what I probably would have chosen had I been there at the beginning. It'd come up elsewhere as well. Years ago when we published the [Heroku HTTP API design guide](/fragments/heroku-http-api-design-guide), we suggested [using UUIDs to identify all resources](https://github.com/brandur/heroku-http-api-design#provide-resource-uuids), but without any real rational for _why_, and quite fairly, someone had recently opened a GitHub issue on the repository to challenge the point.

Over the years since the guide was written I’d starting leaning further back in the direction of integers being the right answer, but now that I’ve read into the subject more deeply, I’m less convinced that there’s a right answer at all. There are small advantages over the other on each side, and some pitfalls that should clearly be avoided, but when used mindfully, both integers and UUIDs make quite workable solutions, with (in my opinion) appropriately levered UUIDs edging out a little ahead.

### UUIDs, V4, ups and downs (#uuids)

```
CREATE TABLE has_uuid_pkey (
   id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
   ...
);
```

UUIDs are 128-bit sequences that are canonically represented in hex-encoded string form like `63bb4648-0b42-497f-9ad8-571cd60ee4f3`. It's worth remembering that although we're used to seeing them as strings, when they're being stored (properly) in a database, to the machine they're not all that different from any other sequence of bits -- they're twice the length as a standard 64-bit integer, but stored and compared similarly.

When we're talking about UUIDs, we're often talking about _V4_ UUIDs, which is a fancy way of saying that the entire UUID byte sequence is generated randomly, as opposed to using an algorithm based on clock sequence or anything else.

UUIDs as primary key aren't a slam drunk, but do have some advantages:

* The fact that they're random means that they don't rely on a single sequence to be generated. Multiple entities can generate IDs independently, but still store them to a shared data store without clobbering each other. There's no single bottleneck that could one day be a problem for scaling or performance.

* Similarly, they're useful for cooperation between entirely different services. One service can generate an ID for an object that it wants to create on another, and then use that ID for idempotent operation (i.e. to guarantee that no duplicates are created even across repeated calls).

* They're opaque from a user-facing perspective. With sequences, users may be able to make inferences about how much data is being generated by watching them increment over time. Malicious users might take advantage of them to try attacks based on ID iteration or collision.

* They occasionally reduce operational accidents. Especially in smaller programs it's not unheard of drop into `psql` and fix something manually in the database, and accidentally use an integer ID on the wrong table while doing so. Because integer sequences start from 1 and share a range everywhere, that might mean that you change the wrong entry. I'll be the first to admit that this has happened to me, although luckily without any catastrophic fallout. It doesn't happen with UUIDs because there's enough entropy to make the chance of overlap ~zero system-wide. If you update or delete on the wrong table with an ID that's a UUID, nothing will happen.

But these upsides are heavily counterbalanced by serious performance considerations:

* Because V4 UUIDs are random, new entries might be inserted anywhere in an index (most commonly a B-tree). That means having to bring more pages into memory and more cache misses. With a `bigserial`, because all new numbers are in close succession, the index is almost always operating on just a handful of pages near the end.

* Relatedly, with UUIDs writing to pages all over the place, those modified pages all need to be sent the write-ahead log (WAL) for replication. This results in an amplification effect where more WAL needs to be written, which means more data to replicate across network links, and an increased likelihood of rockiness in production.

At smaller scale, neither of these is likely to cause much trouble. Postgres is very efficient, and working with UUIDs will mostly be fine. However, in a hot system doing a lot of inserts, the effect is significant. Here are some charts from 2nd Quadrant showing the lopsidedness of using random UUIDs versus more deterministic identity techniques. With a large data set, notice how poorly randomness performs on transaction throughput and how much much additional WAL it produces:

<img src="/photographs/nanoglyphs/026-ids/uuid-tps@2x.png" alt="Transactions per second comparison" class="img_constrained" loading="lazy">

<img src="/photographs/nanoglyphs/026-ids/uuid-wal-medium-normalized@2x.png" alt="WAL size comparison" class="img_constrained" loading="lazy">

_(These from [2nd Quadrant](https://www.2ndquadrant.com/en/blog/sequential-uuid-generators/). "Small" data set transacts on an empty table, "medium" fits in memory, and "large" exceeds memory. In the first graph, see how the blue bars (random UUIDs) for medium/large data sets are so much smaller than the other strategies, meaning fewer inserts are possible. In the second, see how random produces orders of magnitude more WAL volume. Actually, the comparisons are a little more complex than UUID vs. `bigserial` but give you the right general idea -- more below.)_

---

{{NanoglyphSignup .InEmail}}

{'fraction_non_alphanumeric': 0.05651280429971546, 'fraction_numerical': 0.009958899778691115, 'mean_word_length': 4.756596906278435, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 7, 'https://': 6, 'lorem ipsum': 0, 'www.': 4, 'xml': 0}, 'pii_count': 8, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6702110', 'n_tokens_mistral': 3361, 'n_tokens_neox': 3120, 'n_words': 1816}