This is a misunderstanding of the purpose of UUIDv7. The whole point is to keep rows physically close, so you can very quickly extract an entire time range from a number of database shards, with minimal reads and compute. And be able to generate them without a centralized server. That's all there was to it, and something you'd only worry about when serving billions of records every hour.
They are also not supposed to be used as public IDs, just like you wouldn't use auto-incrementing IDs that way.
(I wrote a library for this 13y ago [1], around the time Twitter, Instagram et al started reaching enough scale to think about this kind of optimization)
If they’re not supposed to be public ids, what advantage do they have over serial integers for a central db (the vast majority of the web runs this way).
Others have mentioned their difficulty in distributed systems; but even for traditional enterprise workloads, serial IDs don't scale very well. They force all writes through a single, transactional bottleneck, so you can't shard your data. UUIDv7 and friends lets you guarantee that records get a unique identifier without that coordination cost. There are also scenarios where you need to have access to an ID before the write to the database succeeds.
Of course, lot of event/log data never hits a relational database at a all.. it gets piped right to a data lake. But those records still need IDs, too.
Many years ago, I was working at a small shop that used Oracle databases. We had different tenants for different customers. However, we still wanted records across different tenants to have unique IDs, so (as I recall) all writes to all tenants phoned out to a central sequence generating function. This became a major source of latency for us. This is jut one of many ways that serial IDs become annoying if you're doing anything other than running a 1990's-style RBDMS on a single server.
Anyway, once you try to solve this problem, there are lots of schemes that give you (almost certainly) unique IDs without coordination. Whether you pick UUIDv4 or v7 etc.etc depends on other factors. But one nice feature of UUIDv7 is that it is kinda, sorta serial, which is helpful for e.g. partitioning to work well.
On the other hand, one modern server is 10,000x faster than a 1990s server with 10,000x the capacity, and modern RDBMSs are better at executing queries and give you more options for index types, so the 1990s single-server approach goes very, very far.
I suppose that over time we'll also see more data sovereignty laws, which gives a pretty natural partition key (i.e. for most data you needn't have a global serial because putting them in one database where you'd be worried about conflicts would be illegal anyway, and if you do have some place where you can do those joins, you can adjoin a partition id).
Postgres/MySQL and friends are incredibly good at handling the kind of workloads they were designed for (transactional reads and writes, and perhaps some aggregate queries if you can plan your indexes in advance.) You can scale that quite far, although if you get big enough you will eventually run out of room there (around 10k writes/sec or so.)
However, relational databases are a poor fit for writing logs, for performing analytical queries, and various other purposes that most reasonably large businesses today need. You can try to use your relational DB for them, but you will create real performance issues very fast; and even if you can get the DB to scale up for the load, you'll end up paying way more money that you would from using the right kind of DB for that workload.
I think you may be assuming that insertion requests are not being distributed across long distances. Software, servers, and storage may be faster, but nobody’s broken the speed of light yet.
Every time a discussion like this comes up I am reminded of Microsoft's "RID master" used in Active directory. A central server issues out chunks of ID's to each server on demand (usually when a watermark is reached), so each server always has a pool of ID's available to use.
Are there any extensions for PostgreSQL that do this?
This is actually a very interesting question. I assume that you are referencing extensions that add shards to Postgres, since it runs on a single server out of the box. You made me curious how those extensions/rewrites handle sequences.
It looks like Citus (one such extension that runs shards on separate workers) does something a little like what you described. Each node gets an ID, which becomes the high bits of the sequences it generates. So serial IDs are monotonic within a shard, but not across shards.
Aurora PostgreSQL Limitless uses different abstractions, but also ends up reserving different chunks of IDs for different routers to use.
I use this pattern in mysql. One row, and one update query gets you a block of IDs. Don't want to attempt to write it out here without looking, but the trick is using the last insert id in the query (and on the result).
It's one query, so shouldn't require a plugin I hope.
I mean, I'd say for 90% of enterprise or high traffic workloads, a single big db (1990s style as you call it) works very, very well and has done for a long time. Sharding and using distributed dbs is not often actually necessary.
To take one example this high traffic website (HN) uses one db and serial integer ids as you can see in the urls. A single master db with read-only followers can scale very well even for higher workloads than most businesses ever see. There are a lot of businesses trying to solve this problem (distributed ids) that shouldn't be and will in fact never need it.
As we can see from the article UUIDv4 is not suitable as a pk, certainly not where you want good performance, the performance is significantly worse than v7 or integer pks, and v7 isn't really suitable if you want to hide business data because of the time stamps. So distributed dbs are the best use-case, and those are not often necessary IMO.
The killer app for UUIDv7 is logs. High-volume, distributed, non-public data that you probably want to partition by time as well. Log volume typically grows much, much faster than transactional writes, and any reasonably big organization is going to want to keep that data out of their relational database.
As far as relational databases go, another common workload they're a bad fit for is analytical queries. If you employ any data analysts or operations staff, they can bring even the biggest deployment of Postgres/MySQL to its knees pretty fast with relatively simple queries. Read replicas buy you a little runway there, but they're just not a good or cost-effective way to power BI workloads.
> To take one example this high traffic website (HN) uses one db
First of all, HN does not use a single database! E.g. it powers search with Algolia and API queries via Big Query, and that's just for external traffic.
Second, HN is not a high (write) traffic website. It's a forum. We can actually see from those serial IDs that there are about ~50 million all time comments. HN is about 20 years old; that works out to about 5 writes per minute. Of course growth has increased over time, but it's still probably less than 1 write per second.
So, I'd agree with you that lots of interesting websites can power their primary application write traffic with a traditional relational database, but I think you'll find that those properties end up needing several other databases anyway for other purposes.
Two major use cases I've used for this are distributed databases, so no coordination of series is required, and a specialized case of this, offline data creation for syncing at a later time.
I think unfortunately the vast majority of people misuse them as opaque public identifiers and pks for a single db, which is what this article is talking about.
What's bad about using them as opaque public ids? The only information that's leaked is approximate time of creation which is moot for most applications.
Things like age of an account/record-in-an-account can help find "higher value" targets and/or "less secure"/"more likely to be currently retired/inactive" targets.
serialized ids are in most cases a mistake from business point of view revealing the amount of entities created, e.g. the amount of new user registrations each day. Just create a new user to sample.
Some of the bottle neck of having a central system issuing them one by one can be alleviated by high-low schemes (multiple the received seq. by a number, e.g. 1000 and use a local counter), still unnecessary effort.
If this is your use-case, private serial pk and fully random public identifiers is a better trade off than uuid. V4 is inefficient as a pk and v7 leaks info too.
Many business don’t really have super secret customers/growth and don’t need to hide that info, but if you feel you do you can transition to uuidv4 (not v7) for public identifiers while keeping an efficient pk.
There is a good discussion of the tradeoffs here [0]. It is often fine expose UUIDv7 publicly, e.g. if the event time is not a secret and you use good entropy for the non-timestamp bits.
However, sometimes you might decide that you care, and use a random identifier instead. In those cases you probably won't benefit from a second UUIDv7 identifier, since you're already paying the cost for a primary key index, but perhaps if you are also doing certain kinds of range queries and have a certain kind of storage engine you still get something out UUIDv7.
In other words, it depends. And it might not matter at all if you don't have much data, and/or it's not being generated in a distributed way, and/or you're not doing range queries or partitions in a way that can take advantage of it.
> If you need to list things ordered by creation time very often, it’s useful if the primary index gives that behavior, removing the need for another index.
I thought the main reason UUIDv7 was created with a time component is to keep temporally related rows together in the index pages. Randomly distributed row ids seem OK until the entire index no longer fits in RAM.
It's not just index pages. Generally databases write records into pages, which are then grouped into extents. If the primary key gives an ordering you can create new pages and start filling them up in an orderly fashion. If it's random, you are doing random inserts into pages everywhere all the time, then you start running out of room in pages and have to start splitting them. UUIDv7 solves the most painful parts of this problem in general and is a much better option for a Primary Key than UUIDv1-4.
Assuming the database uses a UUID sort order that reflects UUIDv1 time-based sorting (the last version with timestamps and even then temporal ordering of UUIDs was only one possible sort order; v1 also included machine IDs and some databases optimized for locality by machine for reasons that made sense with v1 but also relate to why we gave up on v1 because machine IDs were problematic privacy issues in wider usage). Many of the databases implementing explicit v7 support are choosing time orders for v7 sorting, and only time orders.
Postgresql uses pages too. The records aren't kept in any kind of order, but the indexes are. So uuidv7 is still useful for postgresql because it means inserts are less likely to be spread across many pages in the primary index.
Yeah, this is 99% of the benefit for me. And it's not theoretical. My company saw performance issues using pure random IDs with MySQL way back in 2015; and consequently rolled our own UUIDv7 that we're still using to this day.
I suppose it's a problem if the spec actually says they're monotonically incrementing because it's clear that isn't guaranteed. But that was never a property I wanted or expected out of them anyway.
All of the other issues here sound like misunderstandings or misuses of ids, IMHO
As well as keeping rows of similar age roughly together, which may create access efficiencies but should not be relied upon, relatively ordered keys massively reduces page splits as the index grows (this can be particularly significant for clustered indexes as implemented by MS SQL and others) because you are always adding items to the end of the index. This can reduce the size of the index overall, potentially a big problem if working in a situation where your data+indexes do not fit comfortably in RAM (you don't want your precious RAM half full of half-empty index pages).
Yes you can shrink it down when things get bad, but if your index is large enough that you care then it is large enough that this reorganisation is going to be costly in time and may slow your system while it happens (or cause a complete lock if your DB doesn't support, or optionally supports and you don't select the option, this as an online operation.
One of my favorite things about UUIDv7: the package https://pypi.org/project/uuid7/ does not implement compliant UUIDv7s, it's from a wholly incompatible draft format. That was not a fun debugging session.
ricardobeat 1 day ago
They are also not supposed to be used as public IDs, just like you wouldn't use auto-incrementing IDs that way.
(I wrote a library for this 13y ago [1], around the time Twitter, Instagram et al started reaching enough scale to think about this kind of optimization)
[1] https://github.com/ricardobeat/unik
grey-area 1 day ago
tibbar 1 day ago
Of course, lot of event/log data never hits a relational database at a all.. it gets piped right to a data lake. But those records still need IDs, too.
Many years ago, I was working at a small shop that used Oracle databases. We had different tenants for different customers. However, we still wanted records across different tenants to have unique IDs, so (as I recall) all writes to all tenants phoned out to a central sequence generating function. This became a major source of latency for us. This is jut one of many ways that serial IDs become annoying if you're doing anything other than running a 1990's-style RBDMS on a single server.
Anyway, once you try to solve this problem, there are lots of schemes that give you (almost certainly) unique IDs without coordination. Whether you pick UUIDv4 or v7 etc.etc depends on other factors. But one nice feature of UUIDv7 is that it is kinda, sorta serial, which is helpful for e.g. partitioning to work well.
ndriscoll 18 hours ago
I suppose that over time we'll also see more data sovereignty laws, which gives a pretty natural partition key (i.e. for most data you needn't have a global serial because putting them in one database where you'd be worried about conflicts would be illegal anyway, and if you do have some place where you can do those joins, you can adjoin a partition id).
tibbar 11 hours ago
However, relational databases are a poor fit for writing logs, for performing analytical queries, and various other purposes that most reasonably large businesses today need. You can try to use your relational DB for them, but you will create real performance issues very fast; and even if you can get the DB to scale up for the load, you'll end up paying way more money that you would from using the right kind of DB for that workload.
otterley 13 hours ago
chaz6 21 hours ago
Are there any extensions for PostgreSQL that do this?
tibbar 20 hours ago
It looks like Citus (one such extension that runs shards on separate workers) does something a little like what you described. Each node gets an ID, which becomes the high bits of the sequences it generates. So serial IDs are monotonic within a shard, but not across shards.
Aurora PostgreSQL Limitless uses different abstractions, but also ends up reserving different chunks of IDs for different routers to use.
rplnt 13 hours ago
It's one query, so shouldn't require a plugin I hope.
e12e 19 hours ago
https://dev.to/aws-heroes/scalable-sequence-for-postgresql-3...
Would interact with sharding?
grey-area 17 hours ago
To take one example this high traffic website (HN) uses one db and serial integer ids as you can see in the urls. A single master db with read-only followers can scale very well even for higher workloads than most businesses ever see. There are a lot of businesses trying to solve this problem (distributed ids) that shouldn't be and will in fact never need it.
As we can see from the article UUIDv4 is not suitable as a pk, certainly not where you want good performance, the performance is significantly worse than v7 or integer pks, and v7 isn't really suitable if you want to hide business data because of the time stamps. So distributed dbs are the best use-case, and those are not often necessary IMO.
tibbar 11 hours ago
As far as relational databases go, another common workload they're a bad fit for is analytical queries. If you employ any data analysts or operations staff, they can bring even the biggest deployment of Postgres/MySQL to its knees pretty fast with relatively simple queries. Read replicas buy you a little runway there, but they're just not a good or cost-effective way to power BI workloads.
> To take one example this high traffic website (HN) uses one db
First of all, HN does not use a single database! E.g. it powers search with Algolia and API queries via Big Query, and that's just for external traffic.
Second, HN is not a high (write) traffic website. It's a forum. We can actually see from those serial IDs that there are about ~50 million all time comments. HN is about 20 years old; that works out to about 5 writes per minute. Of course growth has increased over time, but it's still probably less than 1 write per second.
So, I'd agree with you that lots of interesting websites can power their primary application write traffic with a traditional relational database, but I think you'll find that those properties end up needing several other databases anyway for other purposes.
grey-area 12 minutes ago
Many businesses outsource distributed logs for example as it’s not a core concern.
10k writes a second is a problem that a very small number of systems have relative to all deployed web servers, the normal case is far closer to HN.
If you grow close to that level you have the resources to think about more complex solutions. Most businesses will never get close to it.
Re HN using other systems for search and api, that’s more related to supporting companies the vc funded - Algolia.
https://www.ycombinator.com/companies/algolia
surye 1 day ago
grey-area 1 day ago
I think unfortunately the vast majority of people misuse them as opaque public identifiers and pks for a single db, which is what this article is talking about.
cpburns2009 15 hours ago
WorldMaker 13 hours ago
grey-area 14 hours ago
cpburns2009 14 hours ago
nodamage 1 day ago
xxs 17 hours ago
Some of the bottle neck of having a central system issuing them one by one can be alleviated by high-low schemes (multiple the received seq. by a number, e.g. 1000 and use a local counter), still unnecessary effort.
grey-area 16 hours ago
If this is your use-case, private serial pk and fully random public identifiers is a better trade off than uuid. V4 is inefficient as a pk and v7 leaks info too.
Many business don’t really have super secret customers/growth and don’t need to hide that info, but if you feel you do you can transition to uuidv4 (not v7) for public identifiers while keeping an efficient pk.
xxs 1 hour ago
globular-toast 1 day ago
l5870uoo9y 1 day ago
tibbar 19 hours ago
In other words, it depends. And it might not matter at all if you don't have much data, and/or it's not being generated in a distributed way, and/or you're not doing range queries or partitions in a way that can take advantage of it.
[0] https://news.ycombinator.com/item?id=45622045
klysm 16 hours ago
hackingonempty 1 day ago
I thought the main reason UUIDv7 was created with a time component is to keep temporally related rows together in the index pages. Randomly distributed row ids seem OK until the entire index no longer fits in RAM.
NyxWulf 1 day ago
WorldMaker 13 hours ago
srcreigh 1 day ago
Except for Postgres
thayne 1 day ago
pkulak 1 day ago
jrs235 19 hours ago
zeendo 1 day ago
I suppose it's a problem if the spec actually says they're monotonically incrementing because it's clear that isn't guaranteed. But that was never a property I wanted or expected out of them anyway.
All of the other issues here sound like misunderstandings or misuses of ids, IMHO
dspillett 1 day ago
Yes you can shrink it down when things get bad, but if your index is large enough that you care then it is large enough that this reorganisation is going to be costly in time and may slow your system while it happens (or cause a complete lock if your DB doesn't support, or optionally supports and you don't select the option, this as an online operation.
computing 1 day ago
rrr_oh_man 1 day ago
btown 1 day ago
https://pypi.org/project/uuid-utils/ is what you want :)
https://github.com/stevesimmons/uuid7/issues/1