Hacker News Reader

Comment by grey-area | Hacker News Reader

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).

View on HN

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.

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.

Not sure how something like:

https://dev.to/aws-heroes/scalable-sequence-for-postgresql-3...

Would interact with sharding?

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.

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).

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.

I suppose that's fair, though in my experience nobody seems to think it's crazy if the computer takes over 1s to respond to a human, so 200 ms is kind of small potatoes, and for most cases you can have a local read replica, so only writes need be slow.

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 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.

I agree there are solid use cases for distributed dbs, but they are relatively uncommon and not something most businesses need to think about.

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

Ah, I was using "enterprise" in the colloquial tech/B2B sense as a company of ~1000 people or so. There are about 8,000 businesses of that size in the US, but they employ about half the private workforce. It appears to me that Cassandra adoption is very heavy within that market segment, for instance.

(Keep in mind that pushing relational databases to 10k writes/sec is not optimal! Well before you get to that point, there are much more cost-effective options.)

On to a few other nits...

> 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.

Hmm ... I would estimate that the majority of deployed servers today use a distributed database, operating out of clusters for e.g. Google, Meta, Amazon, Netflix, Microsoft, CDN operators, and so on.

And, of course, HackerNews is also using distributed databases, so it's not a great counterexample...

> Re HN using other systems for search and api, that’s more related to supporting companies the vc funded - Algolia

HN uses Google BigQuery to support a company they VC funded? Anyway, this is "no true Scotsman"-ing databases.

Uncommon? How many people have a phone in their pocket? Any app that functions offline (for when you don't have cell service) and syncs between devices essentially has distributed nodes minting data.

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.

Those sound sensible uses for them.

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.

The article goes into a few reasons.

I read it. There's no convincing reason 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.

Indeed. There are a couple billion devices on the planet for which this is a common requirement.

UUIDs are one of the most misunderstood things in general, I think. Their entire point is that you don't need a central db to generate them.

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.

You don’t have to use a pk as public identifiers.

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.

You can be much simpler, you can always one-way-hash your primary keys (along with any pepper), if you wish to hide them. Still, use UUIDs freely.

One-way hash -> you need to store the result to look up based on it -> likely better off using random identifier, and not worry about hash weakness etc.

Random Identifiers -> UUIDs are (mostly) randomness, especially the v4 one. V7 leaks the time component, so you can hash that part (and randomize it again), of course in that case you'd need to have an explicit mapping and then lookup, but once you get to the internal identity you dont have to worry either.

As for the worries, most SHA family is good.

Reply on news.ycombinator.com