PostgreSQL and UUID as Primary Key

there is unlikely significant performance degradation for int vs big int, but it will be huge PITA, if 10 years later and tons of legacy code written that table will grow over 4B rows..

Using 32 bit ints for IDs is insane in today’s world. If an attacker can control record generation, e.g. creating a record via API, then they can easily exhaust your ID space. A lot of kernel vulnerabilities stem from using incrementing 32 bit integers as an identifier. If you’re considering using 32 bits for an ID, don’t do it!

Defaulting to 64-bit integers internally is to me a matter of mechanical sympathy, it has little to do with row capacity. It’s just a word size that current CPUs and memory architectures like working with.

I read your post and hear echoes of "Who would ever need more than 2 digits for the year in this timestamp column?"

Never again.

Postgres doesn't support unsigned ints last I looked, so it's actually a ~2 billion limit.

Secondly, it's not XXXk rows currently-you have to consider the complete lifetime of the table. When rows are deleted/created and how often.

So what you've said is true, but the set of appropriateness for smallint is a much smaller than expected.

The problems from unnecessary bigserial is nothing compared to the pain of insufficient serial.

This is especially true for PostgreSQL which increments sequences for upserts (even if no records are inserted).

That's how I've hit 32-bit limits on tables that had only a couple million rows.

---

I would only use 32-bit for very selective items that is used in a lot of FKs, like a tenant ID.

This is simply bad advice. There are very, very few cases where you shouldn’t use a bigserial/bigint as a primary key. Perhaps the only case is where it is impossible for the rows to grow beyond a specific bound: an “AWS region” table or a “countries” table. These are almost always lookup tables of some kind, and the domain makes it impossible for it to grow past a certain bound.

Outside of that, put absolutely no thought into it and pick a bigint/bigserial.

Trying to be clever about this ignores the fact that tables and their usage change, and what you believe now will not be correct later.

And when you realise you’ve been too clever, it’s 3am on a Sunday, your production database has exploded and nothing works. You now need to rewrite 30 tables and their foreign keys ASAP with upper management breathing down your neck.

An int PK does not give you 4 billion rows. For example, in Postgres every time an insert transaction has to rollback, the id's that would have been used by that insert are discarded and gone forever. Likewise people often don't realize that Postgres' very convenient upsert syntax (insert on conflict do update) will consume id's from the sequence _every time it runs_. If you do an upsert with 100 rows, it will peel off 100 new id's just in case they're needed... if they aren't used, they're discarded. This can chew through 4 billion id's really, really fast.

Personally I would only use an int PK in a table where you know the bounds of the data with very high confidence. Like, at my last job the software involved working with our client's physical store locations. If we managed to capture the entire North American market for the type of business that we worked with, we'd be looking at about 50k stores.

As uuid v7 hold time information, they can help bad actors for timing attacks or pattern recognition because they contain a time information linked to the record.

You can guess the time the system took between 2 uuid v7 id's.

They can only be used if they're not shown to the user. (so not in the form mysite.com/mypage? id=0190854d-7f9f-78fc-b9bc-598867ebf39a)

A big serial starting at a high number can't provide the time information.

You're saving storage space but potentially leaking details. Is that ok for your application? No one can answer but your org.

The comment above warns against it due to the embedded timestamp info as a info leak risk. Perhaps that was a problem for them in some circumstance.

Take all the Roman alphabet apart from the vowels - 21 characters and length 6 gives you 100 million possibilities which is plenty for most applications.

You can still get vaguely offensive sequences like FKNNGR or BLKCNT, but at some point you have to put this down not to your software being offensive or hateful but to humans finding patterns in randomness.

Do you ensure that your software does not form ridiculous numbers? Imagine that some christian person gets "666" number. What a scandal.

Do you ensure that your software does not form ridiculous words in every language? Or just another US-centric thing?

The idea of avoiding identifiers to be ridiculous is ridiculous to me, honestly...

not form ridiculous words

Depends on what you mean by ridiculous.

For example https://sqids.org/ ensures that there are no profanities in the generated ids. And it allows you to add additional words that you want to avoid.

Each object has an external key and an internal key. This separation allows you to migrate to other layouts, technologies, etc. without breaking your customer's links or records.

Internally, your database looks like:

  User
    ID - uint128
    external_id - UUID (of some sort)
    name - string

  Post
    ID - uint128
    UserId - uint128 (User.ID)
    external_id - UUID
    ...

e.g.

  SELECT count(*) FROM
   Post JOIN User 
   ON User.ID = Post.UserID
  WHERE 
   User.external_id = <%USERID_FROM_REQUEST%>;

  // Don't forget to bind USER_ID_FROM_REQUEST instead of 
  // concating the string, no Johny-Tables here!

Why “bigint generated always as identity” instead of bigserial, instead of Postgres' uuid data type?

Postgres' UUID datatype: https://www.postgresql.org/docs/current/datatype-uuid.html#D...

django.db.models.fields.UUIDField: https://docs.djangoproject.com/en/5.0/ref/models/fields/#uui... :

class UUIDField: A field for storing universally unique identifiers. Uses Python’s UUID class. When used on PostgreSQL and MariaDB 10.7+, this stores in a uuid datatype, otherwise in a char(32)

[...] Lookups on PostgreSQL and MariaDB 10.7+: Using iexact, contains, icontains, startswith, istartswith, endswith, or iendswith lookups on PostgreSQL don’t work for values without hyphens, because PostgreSQL and MariaDB 10.7+ store them in a hyphenated uuid datatype type.

From the sqlalachemy.types.Uuid docs: https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqla... :

Represent a database agnostic UUID datatype.

For backends that have no “native” UUID datatype, the value will make use of CHAR(32) and store the UUID as a 32-character alphanumeric hex string.

For backends which are known to support UUID directly or a similar uuid-storing datatype such as SQL Server’s UNIQUEIDENTIFIER, a “native” mode enabled by default allows these types will be used on those backends.

In its default mode of use, the Uuid datatype expects Python uuid objects, from the Python uuid module

From the docs for the uuid Python module: https://docs.python.org/3/library/uuid.html :

class uuid.SafeUUID: Added in version 3.7.

safe: The UUID was generated by the platform in a multiprocessing-safe way

And there's not yet a uuid.uuid7() in the uuid Python module.

UUIDv7 leaks timing information ( https://news.ycombinator.com/item?id=40886496 ); which is ironic because uuids are usually used to avoid the "guess an autoincrement integer key" issue

Why?

I agree and apologise for writing bigserial out of ossified habit. Identity columns hew to the SQL standard, bigserial is a pg-ism. I actually do still use it when the ORM’s DDL generator prefers as much (looking at you Rails), there’s no gain from fighting the middleware.

For other readers: the two are almost the same in behaviour, they differ primarily in that identity columns don’t need a USAGE grant on their associated sequence, and the ALWAYS makes it harder (but despite the term not actually impossible) to override the default value on row insert.

That's not true, you can increment by 2, 10, 100, or any number. I'm not saying that's necessarily the best solution, but it's not true that you can't use it.

Offhand, I remember _reading_ about that but haven't ever used it in practice so please test and confirm...

# Postgres can Index Function Results (including what you described)

# Postgres does not magically know to USE those results. To use them you must E.G. JOIN (probably works for WHERE?) or ORDER or LIMIT by the same parameter that went into the Index (which is how it's matched up).

Generally, the Primary Key should either be 'small' (BigInt can count as such) and only used internally or be something strongly relevant to how the records in that table can be limited for most (or the most) queries.

100% this. Context is everything, and advice without understanding context should be suspect at best.

That said, I've found that contexts can change. Personally I started with the database being local (send single). It the migrated to remote (web), after that to distributed and from there to mobile.

As contexts gave changed I've found we needed to switch to UUID (and the switch was a pain.) A lot of our problems would simply not exist if we'd used uuid from the start.

So now my "default advice" is uuid, unless there's a -specific- reason not to. For me, performance is not that reason (your database outlives multiple generations of hardware, and faster hardware will dwarf optimisation-via-data-type.)

And don't get me started on natural versus surrogate identifiers- that ship sailed decades ago.)

Postgres is happier with sequence ID's, but keeping Postgres happy isn't the only design goal.

It literally is the one thing in the entire stack that must always be happy. Every stateful service likely depends on it. Sad DBs means higher latency for everyone, and grumpy DBREs getting paged.

The whole point of UUID is distributed creation. There's nothing about random ones (UUIDv4) that makes it better for this purpose.

Random UUID's are super useful when you have distributed creation of UUID's, because you avoid conflicts with very high probability and don't rely on your DB to generate them for you

See Snowflake IDs for a scheme that gives you the benefit of random UUIDs but are strictly increasing. Which is really UUIDv7 but fits in your bigint column. No entropy required.

Using completely random UUIDs is IMO the worst choice

Can't say that without context. I've worked in systems where even the version bits were randomized, and for good reason (i know, technically, no UUID anymore).

Reconfiguring tables to use a different kind of unique ID (primary key in this context) can be a much bigger pain than an ordinary column rename if it is in use by foreign key constraints.

The info leak, specifically, is [https://en.m.wikipedia.org/wiki/German_tank_problem].

This is exceptionally rare in most projects.

I know of only one person in my entire career that had to do this. And they managed it just fine despite working with auto-incrementing big ints.

Yet some folks advocate that all projects should pay an expensive insurance against this elusive event of two databases being merged.

Yup, not many people seem to know about CACHE vals and/or sequence reservation. It's a problem that I've had to explain a couple of times already to colleagues. And unfortunately hidden assumptions are oft the most dangerous kind.

lol, I of course don't mean that I have no issues, performance or otherwise. Only that I have never had an issue with UUIDs in postgres

This gave me a good chuckle, and has generally been my experience. Systems grow often in unpredictable or unintuitive ways.

You can pay the cost for something upfront, and the cost of maintaining it, and in the long term paid too much for something you didn't actually need.

Alternatively you can wait to pay it until you're certain you need it but the work involved has become much more significant, in which it can cost more than it would have to have built and maintained it from the beginning.

Compounding the issue is the build-up-front scenario costs fade with time and you don't really think about them, but build-when-you-need-it always creates a stir even if the costs are less overall than build-up-front.

Either way something will go wrong no matter how many times you predict where the cards will fall.

But then by default you are leaking potentially business sensitive data with your id if you are using it as public facing, which is unsecure design by default.

I would rather have secure data by default and opt in to optimise when it is clear this info is fine to leak.

yes absolutely- that system was built a long time ago before the uuid type - you should def not store things in chars (and probably shouldnt use char at all, use text)

was saying that even with that poor implementation we still were not having issues using uuids

And migrating all of this later on can be a right pain so it's worth getting it right up-frong

I've never had to move from uuids to integers. I've had to move from integers to uuids plenty of times though.

What you stated makes intuitive sense, but it does make me wonder why the RFC states the following in the security considerations:

Implementations SHOULD NOT assume that UUIDs are hard to guess. For example, they MUST NOT be used as security capabilities (identifiers whose mere possession grants access). Discovery of predictability in a random number source will result in a vulnerability.

https://datatracker.ietf.org/doc/html/rfc9562#name-security-...

"it certainly will eventually" - I think even that is underselling how unlikely it is for Git 256-bit hashes to collide. I calculated (taking into account the birthday paradox), that even if 8 billion people on Earth each created a Git commit every second, that they would have to do that non-stop for 1,588,059,911 trillion years before there's a 50% chance that any of the two commits have the same hash. Our sun is predicted to only last 0.005 trillion years more.

√(2^256)×1.1774÷8000000000÷3600÷24÷365 = 1,588,059,910,945,875,138,261

UUIDs are more likely to collide but still basically impossible. You'd have to generate √(2^122)×1.1774 = 2714899559048203259 to have 50% chance of a collision. Just to store the UUIDs for that database would take 39,506,988 TB of space. If you aren't thinking about your database not fitting on millions of drives, don't think about UUID collisions.

I've used 128 secure-random bits for ages, not caring for any of the UUID version nonsense. Per the birthday paradox, I need to have 2*64 entries in my tables to reach 50% collision probability, and it will be a while before I can afford that much storage anyhow.

You don't need to worry about a collision in a UUIDv4 that you created on your server. But I have seen a surprising number of applications that took a UUID generated client side and basically upserted it. Allowing taking over resources who's ID was known via the insert API (even if the update API has proper access control).

Why should they be done in application code?

you can (and often should) do the actual generation of them in application code

I can absolutely see scenarios where it would be desirable or convenient to create the these IDs in the application code. However, I can equally assert that you can, and often should, let the database be the source of truth for record identity when using database surrogate keys for that purpose... but I won't.

The truth is there are many kinds of architectures and many kinds of applications the details of which have a direct bearing on where or where not certain operations should be conducted. In many environments in which I work, the idea of "the application" can be murky. In many enterprise environments databases can be driven by and integrated into many applications, possibly from different software developers and vendors with varying degrees of influence in how they internally operate. In such environments the database can be the great unifier and placing substantial control of data in the database itself can make sense in these heterogenous application environments: but it's very much a circumstantial call.

I would expect if there really were one true way to develop application or how to use databases to achieve goals, we could well be asking why they built these functions in the first place... I suspect it's because there are a reasonable number of valid use cases to justify the lift. I'd like to think my use cases fit the envisioned valid use cases thus my interest and question.

It looks like the feature had made good progress with the contention really being around the fact that the standard hadn't yet become an RFC. However, the new UUID standard did reach that milestone back in early May, but the feature kinda went radio silent... at least insofar as any public record... up to a couple days ago when the assigned reviewer/committer was removed.

But no word on if it's delayed, or there's some problem, etc. It's still tagged as 17... and some of the related work has been committed, but not the UUIDv7 itself so it seems.

Information leakage since they have a timestamp component. Some people may not care, but plenty of folks do. As others have said, anything security related likely should UUIDv4. UUIDv7 is basically an engineering compromise on security (they leak a timestamp) vs performance (random reads and writes to an index aren’t as performant as localized reads and writes).

Information leakage like the other person said but also if you ever move to a distributed db v7 uuids can lead to hotspots.

1. UUIDv7 allows to extract timestamp

2. UUIDv7 allows to predict first half, if you know the timestamp.

3. UUIDv7 provides 62 bits of randomness compared to 122 bits for UUIDv4.

Whether that's a problem for your particular use-case or not, it's up for you to decide. I don't think that UUIDv7 is "insecure", it just provides different trade-offs and in some situations it might be less secure compared to UUIDv4, but I hardly see any attack vector where you could issue 2^62 requests to brute-force the ID.

That's what the RFC states:

Timestamps embedded in the UUID do pose a very small attack surface. The timestamp in conjunction with an embedded counter does signal the order of creation for a given UUID and its corresponding data but does not define anything about the data itself or the application as a whole. If UUIDs are required for use with any security operation within an application context in any shape or form, then UUIDv4 (Section 5.4) SHOULD be utilized.

https://datatracker.ietf.org/doc/html/rfc9562#name-security-...

The important point is that UUIDv7 is even worse (and unsuitable) for that application so anything prohibited UUIDv4 for those reasons would not be okay with v7 either!

If your API is relying purely on the user ID for AuthZ, you have much bigger problems.

Also auto increment IDs that are exposed publicly cause [https://en.m.wikipedia.org/wiki/German_tank_problem].

The benchmark seems to include generation in the total time. Not sure that's a useful comparison of how b-tree index behaves in each case (as UUIDv7 has fewer bits of randomness, it's also cheaper to generate).

To contribute if I may:

Page fragmentation from UUIDv4 can be desirable for SQLite because marking the same page as dirty can give SQLite little opportunity to sync it to disk. Causing contention. In this scenario your Write Ahead Log (WAL) file just grows and grows.

SQLite introduced WAL2 which means switching between 2 WAL files instead of one, helping to mitigate unbounded contention. But it's recent. Most tools don't have SQLite supporting that yet.

You could probably just use random uuids and then encode/decode them to ULID whenever you read/write them.

Type 4 guids are a reasonable choice.

I personally prefer the extra 128 bits of entropy, but I have no evidence that says it's more secure in practice.

This is a valid point but it highly depends on the use case and larger context on whether this will be relevant or not. If you have a table where you fetch multiple rows close to each other in paginated manner this would be relevant for the performance but if you only fetch individual records by uuid, data locality wouldn’t increase the performance.

Ah, of course. Thanks!