Choosing a Postgres primary key
supabase.com
supabase.com
Randomly concludes "the best time-based ID seems to be xid" without saying why or comparing to others e.g. ksuid, UUIDv7 etc ("xid" is only mentioned twice in the entire blog, first in the above statement and second a link to the reference implementation). Equally unfortunate that they picked "xid" as their supposed "best" because Postgres has an internal identifier that is also called "xid" and is very much NOT to be used as a primary key !
Downplays the many issues with "serial", including somehow thinking the word might "might" has a place next to the words "not want to expose them to the world though" .... you DON'T, full stop. Exposing predictable identifiers to the world is never a good thing.
I'm not really sure what that blog post is supposed to be achieving really. I didn't learn anything.
I also had to do some support tickets recently and because of the issues copying uuids from the screen they insisted on screenshots that included the url bar showing uuid. 100% trying to tell them the ID over try the phone or even rekey would run into issues.
Give me back my sequential invoice ids!
... Maybe though. Even ID's in the millions are probably easier to read than a long ass GUID.
Worth bearing in mind that an unsigned 32-bit integer sequence can uniquely identify 4,294,967,295 records. If you're really storing that many records (let alone enough to exhaust an even bigger integer sequence), the length of the identifier is probably the least of your worries :)
Even if one uses UUID as primary key, it should have separate invoice_no (in whatever formatting they require such as 2023/001, or 1001,1002...) which is the human readable and referenced number.
This is especially important if you are developing a multi-tenant system where the invoice number, say 2023/001, may exist for more than one tenant.
In one of our systems we’ve seen customer guess at other accounts by just incrementing the sequence.
The rule of thumb I used to use is if an Id is going to be used for lookups or being exposed externally use uuid otherwise us sequential.
The hard thing about the above rule is that it’s hard to tell when you are designing the db if the id will be used externally/for lookups or not. Requirements change later.
There are much better solutions for this, like server-side prepared queries that do not simply return on an ID but rather as the result of a join, or just proper security practices in general, rather than reactively making something that was once guessed simply harder to guess.
Also, something like an ID is in my experience something that will be referred to orally between two human beings when discussing a problem, like referring to user 6201 or discussing invoice 540567. When you switch to UUIDs you're basically also saying "no human will ever have to say this out loud".
It's a pretty powerful implementation of capability based security.
And then people naively copy operating system design methodology into web APIs... Unrevokable leakable know-it-and-you're-in secrets are a bad idea.
What can they do with that guess? A sensible access control would not even let them see if the account is active, cancelled, or invalid.
Even if there are no vulnerabilities in access controls it can also prevent competitors from knowing how busy your platform is. If I register a new account on your system and I get ID 57854 and a week later I register another and get ID 57978 then I know 124 new users have signed up in that time.
1 - https://cheatsheetseries.owasp.org/cheatsheets/Insecure_Dire...
If the company has more than one unit that creates invoices than just add unit ID, so it becomes invoice_id/unit_id/year. Still unique and very much readable.
https://en.wikipedia.org/wiki/German_tank_problem#Historical...
I think there are many situations where you don't want to expose predictable identifiers, but there are also examples where predictable identifiers may actually be beneficial.
It causes meetings with people who think all predictable identifiers are a problem.
Sometimes it doesn't matter. Example below:
There is a saying for the examples you and others are posting ...."The exception rather than the rule"
Posting contrived examples in order to attempt to prove a point. For the majority of cases, a random ID remains the better option.
But unfortunately developers still treat security as an afterthought. They continue to use "serial" because of what can only be described as sheer ignorance, i.e. demonstrably false misunderstandings about database technology.
Case in point, I placed an order on an e-commerce site a couple of weeks ago. I was not best pleased to be given a tracking URL that read: "https://example.com/order/WEB-nnnnn", where nnnn was clearly an incrementing number. Such a thing is inexcusable in 2023 !
It took a lot of strength to resist the temptation !
If your argument That a guid is better used here then you’re wrong. If the endpoint is not secure it doesn’t matter if your used a sequential id or a random id/guid. You could brute force a discovery.
You should always validate the input and verify the accessed invoice belongs to the person requesting it.
Sometimes it's not 'sensitive', and even when it is, it doesn't really matter.
An invoice id, doesn't matter. You need to be logged in to access it, and when logged in, you can only access your own invoice. If you're going to try discover other invoices, well you know the user who is trying to hit random urls to find an invoice that doesn't belong to him. If you want to prevent random signups from doing it, block users who don't have => 1 paid order from accessing the page entirely.
Claiming "Exposing predictable identifiers to the world is never a good thing." tho is just FUD. There are use cases. But it's not 'never' a good thing.
>> Claiming "Exposing predictable identifiers to the world is never a good thing." tho is just FUD
Fully agree, this post is a great example: I can increment the id in the url and see the next post - no harm done.
If I had a hundred bucks every time I've seen a website fail to do this access check... I'd be a thousand-aire.
You're going to need some type of readable relatively small ID anyway, because things like "Hi there, I have a question about order b1a354c5-ac2b-4990-a189-1f2b4f537b09 I placed on your website" doesn't really work.
as base64url (RFC 4648 §5): saNUxawrSZChiR8rT1N7CQ (22 octets)
as base85 (RFC 1924 §4): p&bTO@+boru-j3)#beDJ (20 octets)
as QR code: data:text/plain;charset=utf-8;base64,4paI4paA4paA4paA4paA4paA4paIIOKWiOKWhOKWiCDilogg4paIDQrilogg4paI4paI4paIIOKWiCAg4paE4paA4paI4paA4paI4paADQrilogg4paA4paA4paAIOKWiCAg4paI4paI4paA4paA4paI4paIDQriloDiloDiloDiloDiloDiloDiloAg4paA4paA4paE4paA4paIIOKWgA0K4paA4paI4paA4paA4paAIOKWhOKWiOKWhCDiloDiloDiloTiloDiloQNCuKWgCDiloDilogg4paI4paAICDiloQgIOKWgOKWiOKWhA0K4paA4paA4paI4paA4paIIOKWiOKWhOKWhOKWiCDilojiloDiloDiloANCuKWgOKWgOKWgCDiloDiloDiloAg4paA4paAICDiloAg4paA
I described another scheme I've used in the past in another comment: https://news.ycombinator.com/item?id=34454430
However, I agree with you - sensitive information should not be easily guessable even if other security mechanisms are in place (and they absolutely should be of course).
What I am really reacting to is rules of thumb - "always do X and you will be ok". The issue is, it is often easier (and far more valuable) to understand the real reason behind things than to remember the rule of thumb.
If your ID is going to be exposed to a human you have to admit that random UUID's are kinda clumsy.
One other pro to UUID's that I don't see discussed is you can generate them client side and be 99.9999999% assured that they won't collide when stored in the DB. I kinda forget our use case for this but I think it was to make "create" and "update" REST calls a lot easier.
One advantage is that you only need to resolve it once at the edge and then internally you use the external facing value. Are there any others I’m missing?
I agree, I would have loved a deeper dive in xid since it seemed to clearly outperform everyone else.
If you use a random id, you cannot quickly know what other ids exist which makes looking for unauthorized access to objectids much harder.
Depending on how highly aesthetic URLs are valued, it's not unlikely that after being in business for a while, the density of your keyspace will mean that even random IDs are found.
A better rationale for disconnecting public and private IDs is to make certain types of database migration a little bit easier. I don't think it's a huge win though, it's a chunk of work to maintain multiple IDs, do the required inderictions all the time, ensure public IDs are always sent to front end, and so on, while adding a translation layer as part of a database migration delays this kind of work until it's actually needed, if ever.
Preventing competitors from estimating the size of your business (or of your customer's businesses, if generating sequential IDs on their behalf) is one big reason for having unguessable public IDs.
In your example those simple, sequential IDs should have no impact on security.
Exposing a serial number can also be competitive intelligence. Number of users, transactions, etc.
Sometimes this matters.
It can give competitors the ability to estimate the size/success/etc of various aspects of your business.
This was a major motivation for a certain online retailer to generate non-sequential IDs.
Some other interesting examples in an old HN thread: https://news.ycombinator.com/item?id=7278198
Also, during debugging, it's a lot nicer to look at short primary keys than to have UUIDs flooding the screen.
I personally stick to UUIDs in pretty much all cases, with the exception of where there are justified and benchmarked performance reasons not to.
Sometimes the external identifier is needed due to interaction with external systems, so it isn't really your choice as the DB/app designer.
>, record bloat,
Depending on the DB, the opposite can be true. In SQL Server if the integer key is the clustering key, which is usually the case for a table's primary key then you may get a smaller DB then using a UUID alone because the clustering key is included in all non-clustered indexes on the table (so the 4 or 8 bytes saved by just having the UUID and not also an integer ID is quickly lost to a larger amount of bloat).
> and having to roundtrip to the DB before you know the ID of a record.
For internal use you should just use the integer ID for the most part, the UUID or similar being for external references.
Unless of course the UUID is for security purposes (making enumerating records impractical for instance) in which case you'll be using the UUID in your own application directly. In these case you shouldn't look up the ID, just query by the UUID. Usually you are wanting to access the base record anyway so that isn't an extra JOIN, and if you are just referring to child tables (so wouldn't need to reference the main entity if not looking up the internal ID) the extra JOIN is usually insignificant, pulling back a single row, compared to the latency hit of a full round-trip to lookup the ID separately.
> I stick to UUIDs … with the exception of where there are justified … performance reasons not to.
A perfectly valid approach.
Though it does vary by DB, and what looks like a smell to someone with most posgres experience is often the more efficient method elsewhere, so you need to take that into account when looking at other projects (or working on your own if support for varied DBs in the backend is desired or a requirement).
For example if you have a situation where you really need the high performance of an integer ID like in your SQL Server example, why introduce a UUID into the equation at all? If you are in the unusual situation of needing such extreme performance that you're worrying about 4 vs 8 bytes on the PK, but also need to obfuscate the public facing ID, _potentially_ int+UUID makes sense. But in my experience that is that a pretty rare situation and there are other things you can do such as using a shorter randomly generated ID such as snowflake, random integers with collision detection (depending on write patterns) or encrypting your ID at the application layer (caveats emptor but unless you're relying on IDs for absolute security they shouldn't be a problem).
However, defaulting to autoinc int as FK and publicly visible UUID for "user friendly" ID seems like an odd thing to do. It seems like one of the least useful ID schemes.
> For internal use you should just use the integer ID for the most part, the UUID or similar being for external references.
I disagree. UUIDs are very useful even as internal identifiers in any area where performance isn't your top concern.
UUIDs are better than autoincs in almost every way except being slightly less performant. You could argue for a strictly internal ID that the security and uniqueness advantages don't matter very much, but I think it's better to default to the safest option in case your ID inadvertently becomes public at some point, or indeed in case you want to one day make public a previously internal-only record. But even if you know, for sure, that your ID will only ever be internal, they still make it possible to merge datasets easily, they're easy to correlate across multiple systems, are easier to find in log files, and can be generated at the application layer, which can make a big difference in transaction time in some cases.
The only reasons not to use UUIDs are that they are ugly and marginally slower, which for the vast majority of entities doesn't particularly matter. If you're at the point where the rest of your code is so optimal that the UUID is causing you problems, or your records are so tiny that the UUID is a big overhead, then that is an exception that I am very happy to make, but it rarely applies. Too often people assume that the overhead of a UUID is worse than it is because of how long they look, but then when you actually benchmark it, it's swings and roundabouts.
Besides, in most cases where I've defaulted to integers, I've come to regret that decision a few years down the line, where some complex system migration or new business requirement would have ended up much easier if we'd just bit the UUID bullet earlier on.
> I disagree. UUIDs are very useful even as internal identifiers in any area where performance isn't your top concern.
If performance isn't your top concern, having both seems fine. If it is, the int is probably faster anyway.
Performance-wise, bigserial is probably a lot faster than UUID as a PK, even if you also have a UUID secondary index. PKs are used in tons of places in the DB and in your server-side code. The DBMS is also gonna be optimized around the regular way of doing things.
What problem do you get from bigserial? The one thing I can think of is, if you're trying to merge two databases together for whatever reason, you can't just copy the entire rows. So you copy all cols except the ID, let them get new IDs, and use the secondary identifiers when copying in related tables. It's more work, but you don't do this often, and if you do, you can automate it.
As for the other advantages of UUID, I and others have covered many of them above: security, fewer roundtrips, shardable, easier to find in logs, data warehousing, backups, disaster recovery, etc. etc. The advantages of UUIDs are so great that my view is, in any serious app, you actually need to justify _not_ using them with concrete performance data that shows why using ints is a worthwhile trade-off. There are cases where ints make sense.
However my main point is, as I said, that having both ints AND uuids is of very limited usefulness.
Oops, yeah, that's true.
The point of having both is that you're already using the bigserial as the PK, and you also have opaque, external-facing identifiers that refer to particular rows in some tables without you having to expose any PKs. Might only be some tables, might be some other kind of string rather than a UUID. There may even be multiple ways for users to refer to something in your system; you keep that all separate from your PKs.
> security, fewer roundtrips, shardable, easier to find in logs, data warehousing, backups, disaster recovery
I see security pros/cons on both sides; exposing PKs to users does feel wrong to me though. I don't see how UUID PKs reduce roundtrips; if your API takes a UUID, you don't have to convert it to a row ID right away, only when you're actually querying what data the client wants. If you're printing row keys in logs, you ought to prefix them either way (like "user:35" or "user:deadbeef-..."). For backups/recovery, I haven't found UUIDs helpful, maybe cause I never want to just copy whole rows.
A sharded DB takes special consideration and could go many different ways, so defaulting to UUIDs in anticipation of sharding one day is probably not going to help when that day comes. In some setups, the PK is just for that one node, and you have a global ID across nodes (which may be a composite of node-local PK + shard ID). Or you're switching to a specialized, not-so-relational DBMS for horizontal scaling. Like, serial IDs are a terrible idea in Google Spanner.
> you actually need to justify _not_ using them with concrete performance data
For what it's worth, I encountered this situation in a DB with millions of rows. UUID PKs were significantly increasing our overall application latency, so I switched us to bigserials. I'd rather not put newer systems on a track to hit that hurdle later on. It can start being a noticeable problem well before you're thinking about sharding.
The primary key is included in all indexes, including non-clustered indexes, so in some cases there can be quite a large difference between UUID and integer PKs in terms of index size.
UUID PKs are also more susceptible to fragmentation.
Postgres tables are more like what SQL Server calls a heap table (one without a clustering key). Some of the issues that make clustered tables the standard recommendation in SQL Server are very similar to those that make VACUUM a requirement in postgres. IIRC postgres tables are more efficient than SQL Server's heap tables in most cases because they are the only option so are actively optimised for, where in SQL Server head tables are generally (in all but the few circumstances where they are more efficient) considered a second class type.
xxxx-xxxx-xxxxxxxx-xxxx-CUST
xxxx-xxxx-xxxxxxxx-xxxx-ADDR
It makes seas of UUIDs much easier to reason about
Depends on your tolerance of wasted disk space for binary vs char, but you can shorten the binary to base64 or use a record as a primary key if you want.
Other advantages of UUIDs:
- they can be generated by clients or by the server safely
- they can be concurrently and distributedly generated without a central sequence blocking/locking ID generation
- they are distinct/unique across system migrations and mergers of systems / data / table
- time UUIDs can encode some info about when generated which can help with forensics / debugging in production
- no database-specific behavior for sequence generation, no extra database object for the sequence
- probably helps with data warehouse / data oceans for keeping data distinct and tracing back to source system
- similarly to that, for integrating systems, also makes the ids unique across system boundaries
- they are a bit more secure as stated elsewhere
As you suggest, I sometimes incorporate type information into the ID to convey a bit more context, often in the form of a URL (org.com/customer/xxxxx). If you do need to put a UUID in the UI, depending on the constraints of the application, it might make sense to just display the first group of characters from the UUID and separately handle the very rare collisions you may encounter, similar to git and its short SHAs.
For any situation where there will be transcription or copying IDs between systems manually, I will typically add another group that incorporates some metadata about where the ID came from (similar to the type code you mention) and a check digit, but obviously I try to avoid any situation that involves transcribing a UUID.
But if you are at all worried that you'll get within a couple of orders of magnitude of MAXINT32 in the lifetime of your application then you should immediately jump to 64-bit values. Doubling is often just noise and the cost of refactoring if you approach MAXINT32 much faster than expected is more or a problem than the extra storage cost of bigger keys.
Yes, that's why you use bigserial.
What's the common use case for the others? I can imagine for weird performance reasons you might want to pick special PKs, but that implies you're exposing them to clients, which you almost never want. The only more reasonable thing I can think of is a UUIDv4 for a special sharded database.
1. You can create a series of related rows without hitting the DB to obtain the next integer.
2. During development, if you mix up ids you will get an error/empty result. With integer keys you might get a row you didn't intend to get, hiding the error.
UUIDs there is stored in Oracle 'raw' datatype which to me is the worst combination possible, basically string stored in small binary blob, due to binary nature all needs to be converted to hex all the time for matching and readability, atrocious performance on stored procedures. Absolutely worst DB design I've seen in past 20 years, and we talk about expensive core anonymization service of top big banking package.
But this give a significant performance benefit for storage (storing in the display format gives 4 bits per byte, or less if you include decorations like the '-' characters, rather than 8) and more importantly when joining (it doesn't need to convert for this, so on a 64-bit architecture each comparison is a pair of 64-bit compares in the CPU) rather than a more complex string comparison.
If you are converting between string and binary representations more often than on input to your stored procedures or for output, then something is very wrong (the query planner are likely not able to use indexes that it could too, so scanning instead of seeking).
But if you want to talk about Oracle's usability, there are much larger fish to fry. I wouldn't recommend anybody to use that database.
This is how I would summarize a security perspective.
* autoincrement id: leaks information about the system as a whole. Users can attack each other. Might be suitable for an internal-only application or an application that doesn't care about leaking this information and goes to great effort to be resilient to users attacking each other.
* timestamp + random id: leaks information about the time the individual record was created. An attacker can attempt to learn sensitive information about an individual. Suitable for a record that is already publicly shared with its time (e.g. a tweet). Might be suitable otherwise if ids are not public. That is only the record creator can view the id and you don't send out links with the ids to the user (particularly over insecure channels such as email).
* random id: does not leak information. suitable for any use case that is okay with the performance implications (of a non-sortable fragemented index).
The post also completely ignores foreign keys.
It is an absolute advantage to size and speed to have foreign keys to be int4/int8 and not an email address or UUID.
I used encryption instead because I can reverse it.
So with a customer email of 'martin@arp242.net' and an object ID of 52 you end up with 1563 + 52 = 1615, or 18v in base-36. You can add a "base number" to make it a but larger, e.g. 50,000 so it becomes "13tr".
I'm sure people can figure this scheme out with enough effort; it's certainly not cryptographically secure, but it's "hidden enough" for many purposes, not much longer than numeric IDs (shorter in many cases), doesn't require any special DB-fu, and is reversible if you know the customer (which you usually do).
What I meant (and have done in the past) is to encrypt/decrypt the auto-incrementing ID at the application boundaries.
If the OP is really using a one-way hash then yes, you would have to store both IDs.
In this project I do not believe the IDs were always encrypted when being sent to the user. So we sometimes had to guess whether we received an encrypted ID vs a regular integer ID because it is possible for the encryption algo that was used to return a sequence of numbers.
I’ve seen uuid4 which replaces the first 4 bytes with a timestamp. It was mentioned to me that this strategy allows postgres to write at the end of the index instead of arbitrarily on disk. I also presume it means it has some decent sorting.
[inspiration](https://github.com/tvondra/sequential-uuids/blob/master/sequ...)
As the link posted above mentions, you can alternatively use a timestamp-based prefix that wraps around after all the bits have been used. This one still leaks possible creation times of the record, so it's on par or better compared to UUIdv6, ULID, etc. (because here the exact creation time can't necessarily be deduced).
In all of these UUID solutions apart from the fully random v4, you are trading of the better index performance with some level of information leakage about the record the ID is associated with.
With UUIDs any service can generate an ID itself and tell downstream services about it in parallel—even if one of them is down, slow, or needs retrying.
The other advantage of UUIDs is in completely decoupled environments that need to be able to share entities with each other. In this situation, serialization of activity is not a concern at all - we simply wish to prevent collisions of keys across the way.
Speaking of. Does pg have any mechanism to protect double creates while using auto increments? Is there any way to provide a request-id from the client?
And what does "index terribly" mean? You can index UUID columns just fine, so is it a performance concern? What is the concern?
But this still doesnt matter, right? If you want time ordering you'd prolly have some field like `created_at`
If your index happens to be in a different order (the position in the index is uncorrelated to the position of the row), you're gonna be thrashing pages. This isn't unique to UUID keys - text keys or anything other than current timestamp or serial id are going to have the same problem.
It's really only an issue once your index size exceeds available shared RAM. If you can fit your table's index completely in memory, you probably won't notice. But once you exceed that threshold, performance starts falling off a cliff.
If those rows have keys that sort near each other, you're changing a few pages.
If those rows have keys that are all over the keyspace, you're changing roughly 1000*k pages.
The answer is almost always "use biginteger identity", and almost never "use integer serial".
UUIDs have a place but are often better suited in larger, distributed and more complex data stores than postgres.
Using `xid` is such a poor choice I'm surprised it was even mentioned.
The "key" thing to remember is you don't have to expose your primary key to the world. Use UUIDs or shortcodes or whatever for external representations. Use bigints internally. This will prevent a world of pain.
The ids generated are nice readable integers. Generally in sorted order, though not a guarantee, and you end up with gaps sometimes if a client doesn’t give out all its numbers before it’s restarted.
Would anyone be interested in a super robust version of that as a service?
What I see a lot in practice is a bigint numeric id for internal use (better for joins, FKs) and also a textual token for public use, perhaps with a typed prefix indicate the type of record it's identifying (U-AS234FDS for User, etc)
Main benefits to this: Avoids accidental duplication (happens so much). Avoids additional round trips to fetch the id to make a mutation.
Of course if you work on something where you don’t know what it is yet (actually humans are a good example for that) uuid or int might make sense but I hear so many times picking non semantic as a default.
I find that there actually rarely is something defining the thing you're working on. The concept of "immutable identity" is rarely a useful thing in digitalized systems:
- being able to create a new digital entity for the same real-life entity is almost always useful and expected ("the setup of this user is all messed up, just disable it and create a new one")
- attributes that you thought were immutable actually are not ("surely the 'originally scheduled time' of an event is an immutable property" - except when you have a bug and events are scheduled at the wrong time and you need to fix data)
- the concept of "immutable identity" is often pretty subjective in the real world. We generally agree that a person has an immutable identity, sure, but is a 9am appointment that's moved to a week later the same appointment, or a new one? Depends on who you ask, depends on what purposes you need this concept of "identity" for.
Yep, happens to the best of us. Never mess around with this, just use a bigserial.
They had nice sub-10 million integers as order numbers, so we used that as primary key for the order table (and as fk for 10+ child tables). Last year they changed ERP system, and now order numbers are much longer and can contain dashes.
Not the worst change, as converting integer to varchar is lossless, but we had to go over all the views and our code to make sure it could handle it.
SKUS, emails, etc all seemed liked good keys. They're always unique right?
Until one day when they decided to rename some skus, they suddenly want family accounts, you realize you really do want the ability to have duplicates so you can keep historical copies without ripping up your entire database.
Semantics change. A UUID/whatever does not.
I've learned you should never ever use a natural key. PKs are extremely difficult or near impossible to replace depending on your application, and if that really means using serial id's, or UUIDs, or extra lookups - it's worth doing that instead of using natural keys.
Requirements change, or index sizes get bloated and hamper performance, or you need a nice, short ID for URLs , or foreign keys get more complicated with compound primary keys, or ...
Natural keys are nice in theory, but not so much in practice.
Especially if we are talking about active databases where large migrations are a burden.
It's the "Ship of Theseus" paradox [1]. Choosing a semantic key means mixing identity and attribute, while a synthetic key solves by assuming "constitution is not identity".
Since a digital system is a model of the world, a synthetic key allows the system to address objects in this internal model without assuming a particular interpretation of identity in the real world. E.g., it's often the case that you do need to have two "customer" entries in your system that represent the same physical "person" in the world, and this is ok because the concept of "customer" is useful and sufficient for your model, and the physical person isn't.
More often than not, people get caught on this trap in relational databases and object oriented modelling. This can be seen in books and lectures that use Customer-Order-Product relations to teach databases, or Car-Engine to teach OO.
The analogy went further when the partner team asked, can we have a "stable ID" that doesn't change even if that identifier changes. Our team was close to exposing our new row-level keys again. I asked, if the name changes, is it the same thing still? What if the name and the other attributes change? This isn't like a social media post that obviously has an identifier; we're modeling physical objects. Why do you need this feature again? Turns out they didn't need the feature, or even quite understand what they were asking.
The way our application is, really we didn't have to expose any identifier with guarantees about uniqueness.
Of course, nothing is ever perfect and natural keys have their issues, especially when migrating data, but there’s something about them that’s always “clicked” with how I reason about software systems.
My experiences with semantic keys has been awful. I've been told "Oh this [property] will either never need to change, and never have duplicates" of properties you'd think really should never change, several times.
Somehow a different department decided to change the skus. I've seen emails need to either have duplicates or be changed. I've seen a few instances of order numbers from the ERP system having duplicates under certain circumstances. A few cases like GTINs where every item should already have a unique one assigned - until we have an item that is missing one and will never be assigned one.... The big one is needing to archive things or keep histories of objects - if you use a synthetic key it's super easy to just add a "active" flag and you have to change very little code.
I totally get wanting to avoid extra queries and extra steps, but if the need to change a pk _ever_ occurs it's terrible to near impossible (in the case of external dependencies). Many frameworks like django make it fairly easy to naturally add that extra step without needing to make spaghetti to do your lookups
If you see a user_id column, you know it's going to link to user.id. if you see email_id, you know it's going to link to email.id, etc, etc. There's a lot of value in having a predictable schema.
Did you know that sometimes the same social security number is assigned to multiple persons? In this case a person can change social security number. Good luck updating all of your database foreign keys in this case.
Relation theory has the concept of superkeys, which are a set of columns that uniquely determine a row. But these are not useful, since for example, the set all all columns should uniquely determine a row. What is useful is "candidate keys", which are minimal superkeys, with any columsn not necessary to be unique removed.
There are two ways to determine candidate keys. One is empirically by analyzing the data. If you do it that way, then the "candidate" naming is appropriate, since it is possible that some sets of columns are unique simply by chance, not by fundamental nature, and unique by chance is not what we want.
Alternatively you can use domain knowledge and logic to determine candidate keys. Candidate keys determined by logic are true keys, since no duplicates should ever occur unless the requirements or fundamental nature of the data changes. This means that ideally, all such keys should have a unique constraint placed on them (although the implicit unique constraint from marking as a primary key will works for one of these keys). Adding unique constraints for all logically determined candidate keys is the ideal way to avoid accidental duplication.
Within the database and within the application, you ideally want to only use small keys that are unlikely to change, and are unlikely to ever become non-unique. Keys that change tend to cause headaches with updates if referenced elsewhere, and you can have undesirable race condition issues with application logic on changing keys.
Similarly, for keys likely to become non-unique from changing requirements, using them within the database means a much bigger refactor later if they become no longer unique. But if you never use those value to reference within the database, then simply dropping the unique constraint is easy. Impact on application code may vary, from potentially no change needed at all, to much more significant changes, depending on the data in question and how the application uses it.
Large keys that don't change, and are extremely unlikely to ever become non-unique are conceptually fine, but have the practical problem of being large, and thus undesirable to reference from all over the database from a file size perspective. This is especially true of multi-column keys which also tend to be inconvenient from a query writing perspective.
Another important issue is that many identifiers that are supposed to be universally unique, like UPCs, ISBNs etc, are not actually always unique. These things do end up getting occasionally reused, usually accidentally. If you are using that everywhere as your primary key, and eventually come across such a scenario, it is a real nightmare to refactor everything to use a different key in order to be able to handle this. While if you are using some surrogate key almost everywhere, it becomes a lot more feasible to handle this with things like having "lookup by UPC" screens show a list of options when you stumble upon one that happens to have a duplicate.
Depending on the data, but assuming most data isn't big data:
- Use integers internally, maybe suffixed by a shard-id to prevent collision, but keep order.
- Use (random) external ids to access from the outside.
Note that certain uuids will still leak some information: time between records, number of machines, etc.
I wrote about my experience using ulids the other day, specifically with Postgres and some of the dis/advantages you get with it.
It's a deeper dive into ulids than this article is, and shows some real world issues that crop up:
https://blog.lawrencejones.dev/ulid/
That said, and spoiler alert: I'd probably go with bigint-sequence backed text IDs if I were choosing this over again.
I saw xid make the rounds about a year ago, and the promise of a pseudo-sortable 12-byte identifier that is "configuration free" struck me as a bit far-fetched.
In particular, I wondered if the xid scheme gives you enough entropy to be confident you wouldn't run into collisions. UUIDv4 doesn't eat a full 16 bytes of entropy for nothing. For example, if you look at the machine ID component of xid, it does some version of random assignment (either pulling the first three bytes from /etc/machine-id, or from a hash of the hostname). 3 bytes is 16777216 values, i.e., with 600 hosts you have a 1% chance of running into a collision. Probably too close for comfort?
There are settings where you can build some defense-in-depth against ID collisions, like a uniqueness constraint in your DB (effectively a centralized ticketing system). But there are many settings where that kind of thing wouldn't be practical. Off the top of my head, I'm thinking of monitoring-type applications like request or trace IDs.
So far https://philzimmermann.com/docs/human-oriented-base-32-encod... has the best trade-offs I've ever seen.
(Though for encoding numbers, where small numbers deserve a shorter representation, Base58 can be nice too.. z-base-32's alphabet is still more user-friendly. https://github.com/tv42/base58 )
Combined with a machine identifier you can obtain globally unique identifiers that are totally ordered.
V5 are predictable uuids. That combine a ns uuid and a string, via sha1 based one way mapping resulting in a uuid.
For some of these simpler extensions, we're looking at using AWS's TLE (https://github.com/aws/pg_tle), which would allow user-contributed extensions. If we can pull that off, we'll probably look again at the current set of extensions we offer and then see which ones can be ported to a TLE instead
Smart data types for keys, enums instead of varchars etc helps to keep indexes small.
I don’t see a point in trying to hide the ID. Either it’s public or it’s private and should be verified before being accessed.
Exposing sequential numbers tells your competition the size of your company's user base, user activity levels, and growth.
https://en.wikipedia.org/wiki/German_tank_problem#Historical...
I don’t know about performance but I think in most cases that is not a big concern anyway.