The author isn't suggesting abandoning UUIDs, he is suggesting using something like UUIDv7 which preserves locality. For most use-cases, this seems like a very reasonable recommendation with no significant downsides I can think of.
Whether that tradeoff makes sense probably depends.
You can also do that to get opaque identifiers from an auto-increment primary key.
One of the best things about UUIDs is that if your front end tries to create a resource and fails due to a bad connection, you can simply resend that exact same resource for creation again (with the same UUID) and you don't need to worry about duplicate records being inserted into the database since you would get an ID collision; it results in much simpler/cleaner code which is idempotent and deterministic by default.
With auto-incrementing IDs, if there is a connection issue or other failure, it's not possible to easily figure out if a resource was already created or not since only the database knows what the ID of that resource was; the client doesn't know it until the resource has been successfully created in the database and the ID was send back to the client.
Just because the client did not receive a successful response from the server, it does not mean that the resource was not created; and if it was, you cannot know since the client has no way of referencing that resource in a unique way. It just seems completely wrong that the client (which created the resource) cannot reference it until the database has inserted it.
It's a hack to pretend that resource creation starts in the database, when in fact, it starts on the front end.
Even in a single-host setup, it can be a problem because what happens if your process crashes and restarts just after a resource was created in the database but before the ID was sent to the client (with success message)? You would end up with a duplicate record in your DB after the retry since your newly restarted server would not have the UUID in its memory (even though the resource was in fact already created on the server a few milliseconds before the last crash).
With the Redis (or similar) solution, you need to make sure that the request UUIDs expire after some time and are cleaned up to avoid memory bloat which is a pain... I mean that complex solution probably uses up a lot more resources than just using UUIDs in the database as IDs.
If you add a column to store params as well then you can also do better validation:
> Responding when a customer changes request parameters on a subsequent call where the client request ID stays the same
> We design our APIs to allow our customers to explicitly state their intent. Take the situation where we receive a unique client request token that we have seen before, but there is a parameter combination that is different from the earlier request. We find that it is safest to assume that the customer intended a different outcome, and that this might not be the same request. In response to this situation, we return a validation error indicating a parameter mismatch between idempotent requests. To support this deep validation, we also store the parameters used to make the initial request along with the client request identifier.
https://aws.amazon.com/builders-library/making-retries-safe-...
Out of curiosity, what makes UUIDs more elegant or beautiful than just plain old integers?
How do you justify such an argument in the real world without people looking at you in disgust? This is the dumbest argument I've ever heard.
In a lot of cases that can be a pain due to overlapping integer primary keys and large parent-child table sets.
> To be able to generate keys independently of the database
> To move sets of related records between different databases without having to deal with renumbering everything
For most applications you are better off using a sensible structured key, like incrementing an integer or similar, and encrypting it if you want to obscure it. Encrypting a 128-bit value is approximately free on modern CPUs.
This is a must if you don't want to couple your domain logic and DB. I want to generate a record with an ID inside my domain layer without depending on the DB and doing awkward DB save - DB read.
The loss of locality for reads is bad, especially for data sets that don't fit into cache / RAM (while the active set would).
Where it really bites you is writes, because it can trigger pretty massive write amplification.
Imagine you have 128 GB index on UUID column, that's ~16M pages (8kB) and insert 1M random values. Congrats! You've probably just wrote 8GB to the WAL, because of FPW and stuff like that. With serial IDs we'd write a fraction of that. It doesn't take much to hit max_wal_size and trigger a checkpoint, starting a new cycle with FPWs. Got a replica? Well, now you need to send the WAL over network. Is the bandwidth limited (another DC?), sorry to hear that. Is the replica sync and you have to wait. Well, that's unfortunate.
In other words, the lack of locality seems like a detail but at scale it's actually a damn huge deal.
Auto-incrementing IDs simply don't scale beyond a single host so I don't see how they can be good for scalability. Too many devs conflate raw performance with scalability. They are not the same at all - In fact, high scalability often incurs a performance overhead.
Not hyper scale, but good enough for failover or a 3-5 node setup.
mysql> CREATE TABLE animals (
-> id MEDIUMINT NOT NULL AUTO_INCREMENT,
-> name CHAR(30) NOT NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.34 sec)
mysql> INSERT INTO animals (name) VALUES
-> ('dog'),('cat'),('penguin'),
-> ('lax'),('whale'),('ostrich');
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM animals;
+----+---------+
| id | name |
+----+---------+
| 3 | dog |
| 6 | cat |
| 9 | penguin |
| 12 | lax |
| 15 | whale |
| 18 | ostrich |
+----+---------+
6 rows in set (0.00 sec)If you wanted to (practically speaking anyway) overcome the requirement that K is known, you could borrow prefix based counting from the p-adics.
Also, I don't think the blog (or me) suggested going back to using autoincrement IDs. There are other (better) options.
So with high enough concurrent load it's effectively all random IO and that's the primary thing worth optimizing for.
The GP was talking about the first graph here: https://www.2ndquadrant.com/en/blog/on-the-impact-of-full-pa...
If you care about the insert location, why not just add a brin index on a timestamp field and use that instead of assuming that the index is sequential.
The main source of write amplification comes from updating random pages of the btree index. Imagine inserting 10 random UUID values into a large index - it's pretty likely those will go into 10 different leaf pages. And every first update of a page after a checkpoint (which typically happens every 30 minutes or so), we have to write a FPI (i.e. the whole 8kB page) to WAL. So because btrees are based on ordering, random values end up on random leaf pages, causing write amplification.
If you have BRIN index on UUID column, this does not happen, because the index is not based on ordering but location in the table. If the 10 rows get appended to the same table page, that'll be just 1 write, with one FPI.
This is why BRIN does not have the write amplification issue. But it's also a bit pointless, because BRIN on random data is pretty useless for querying (Well, at least the minmax indexes, are. Let's ignore BRIN bloom indexes here.)
If you create BRIN on timestamp, that's not going to have write amplification problem, and it'll be good for querying. The thing is - BTREE would not have write amplification problem either, because the timestamps are going to be sequential (hence no updates to random leaf pages).
What this means in practice, of course, is that you shouldn't expect to do application driven pagination with UUID keys either. You would need to expose some other boundary marker with a total order that works well with btrees. And this could bring you back to "leaking" predictable key material that you were trying to hide by adopting UUIDs...
Your solution just doesn't really make any sense. The time ordered UUID suggested in the blog post makes way more sense.
Is there some value to UUIDs over ULIDs that make these discussions largely revolve around Autoincrement vs UUIDs rather than Autoincrement vs ULIDs(and friends)?
[1]: Again, totally out of my wheel house.
edit: Apparently UUIDv7 exists, which is similar to ULID, so my question pertains to UUIDv5 and below, i think.
One place we're avoiding ULIDs (and other counters) is in publicly-facing IDs. Preferring random to help keep them unguessable. (say what you will about security-through-obscurity).
So we do ULIDs for private IDs. Random UUIDs for public IDs. Seems to work well.
You don't have such issues with randomly generated ids, unless someone obtains a full dump of your database (in which case you will have to think about bigger problems anyway).
And the reason for that was to have lexicographically sortable IDs (even if not monotonic, which would require an extra server) so we didn’t have to index a timestamp column for chronological ordering. The alternative was to have a traditional integer sequence but it’s not always ideal to expose those on your API.
That also applied to our test suite so the choice was to use KSUID but still index a timestamp for accuracy, or just use an alternative with more precision.
Native DB support is irrelevant to me for randomized bits unless it affects storage, sorting, paging, etc. Does it?
You can also write a plpgsql function to generate these ULIDs in the database.
I ran into this last year - storing them as UUIDs works great, unless all of the drivers for the language you're using (Go, in my case) try to cast/validate those bytes as a UUID before you can access them.
I mentioned only WAL for simplicity, but it also has to modify and write out the index pages themselves, and write them out eventually. And that's going to be mostly random I/O. Flash storage is good at handling that, ofc, but if things are adding up like this ...
Not to mention you still have to copy the WAL over network to replica, or perhaps to multiple replicas. And if you have physical backups with PITR, you gotta keep all the WAL somewhere too.
UUIDs belong where you can't afford that simplicity. Where you e.g. cannot coordinate the creation of your primary keys. Or where you cannot allow them to be predictable. There you pay the price.
In practice I noticed that the size of PKs and their poor locality start to play a role only after a huge basket of lower-hanging fruit has been collected. There are relatively few places where their role is dramatic.
You often don't realize you you have non-simple needs until your application is reasonably mature, and in production. If you already picked integer keys, you now either forever deal with the issues caused by not using UUIDs, or you deal with the unknown-but-non-zero pain of converting to UUIDs.
You are writing your data into a DBMS. Coordinating the creation of primary keys is one of the cheapest tasks around, if you can't do that, how is your database still online?
> Or where you cannot allow them to be predictable.
You don't need to export your PKs for the rest of the world. You can have non-predictable data outside of your PK. Yes, a different column will still have some of the problems with index maintenance, but it becomes a much smaller problem if only one table cares about the value.
But sometimes you have to make these keys public, e.g. as user or other resource IDs. You want to make them UUIDs so they won't be predictable. Not having to join everywhere with the UUID-to-artificial-PK table may be a bigger performance win than the losses from larger size of UUIDs.
Sometimes you have a distributed / sharded system, and don't want the keys to clash, and also avoid assigning ranges. Sometimes you have to accept someone else's ID, not originating in your system. In cases like that, large random numbers, e.g. UUID v4, work reasonably well.
Of course when you just have one DB, and a relative slow stream of new rows, it's easy to fully control PK creation. And this covers the majority of practical cases.
https://github.com/estuary/flow/blob/master/supabase/migrati...
we use macaddr8 instead of bigint, because it has a postgres serialization / JSON encoding which lossless-ly round-trips with browsers and it works well with PostgREST. The same CANNOT be said for bigint, which is a huge footgun.
I find auto-incrementing IDs far more elegant in many ways. They are much easier to make sense of for users, they give you some loose metadata (ordering, sometimes a rough time range) which can be handy in debugging stuff.
At my previous workplace most things were auto-incrementing IDs, and while we got bitten by them a few times, there was significant debugging value in seeing a user ID as an integer. Internally, order IDs and customer support ticket IDs were also auto-incrementing, but we offset them so that even numbers were orders and odd numbers were support tickets, and this helped quite a bit with debugging or even just non-technical users sending each other references. Not that this can't be done with prefixes on UUIDs with a bit of extra work.
I wouldn't necessarily recommend auto-incrementing IDs or UUIDs over the other, but I don't think one is more elegant at all.
> to optimize access times by a millisecond or two. UUID is totally worth the cost.
2ms * 20 queries is 40ms, which could take a typical page load for a web app from 150ms to 190ms, which is quite a regression.
Not to mention the distributed database situation.
In fact I like that enough that I sometimes use ULIDs for secondary unique keys just so I have something to log, even if the primary key is numeric.
It's nice that you can store ULIDs in Postgres natively as UUIDs, and it's really nice to have a timestamp embedded in the ULID if you ever need it... but it's also really tempting to use it in public-facing stuff and thus leak your creation timestamp.
UUID actually performs much better than I thought based on the author's example with a COUNT query. COUNT queries aren't very efficient because typically, all records are traversed; here we're talking about a 50% slowdown on 10 million records... How often do you need to access 10 million contiguous records? Most of the time, for user-facing apps, you'll be accessing 100 contiguous records at most and, in fact, most queries will access a single record... I suspect that the average per-query performance loss for a typical app is probably less than 5%. Also, you could simply index by a separate date/timestamp field if you need records to be ordered by time and probably won't incur any performance cost.
IMO, unless you're building an app for high-frequency trading, auto-incrementing IDs aren't worth the pain and lack of flexibility.