What kind of problems did you encounter?
Integers don't scale because you need a central server to keep track of the next integer in the sequence. UUIDs and other random IDs can be generated distributed. Many examples, but the first one that comes to mind is Twitter writing their own custom UUID implementation to scale tweets [0]
[0]: https://blog.twitter.com/engineering/en_us/a/2010/announcing...
They most assuredly do scale. [0]
Also, Slack is built on MySQL + Vitess [1], the same system behind PlanetScale, which internally uses integer IDs [2].
[0]: https://www.enterprisedb.com/docs/pgd/latest/sequences/#glob...
[1]: https://slack.engineering/scaling-datastores-at-slack-with-v...
[2]: https://github.com/planetscale/discussion/discussions/366
It reminds me a bit of the microservices trend. People tried to mimic big tech companies but the community slowly realized that it’s not necessary for most companies and adds a lot of complexity.
I’ve worked at a variety of companies from small to medium-large and I can’t remember a single instance where we wish we used integer ids. It’s always been the opposite where we have to work around conflicts and auto incrementing.
I've personally ran MySQL in RDS on a mid-level instance, nowhere near close to maxing out RAM or IOPS, and it handled 120K QPS just fine. Notably, this was with a lot of UUIDv4 PKs.
I'd wager with intelligent schema design, good queries, and careful tuning, you could surpass 1 million QPS on a single instance.
If you're using MySQL maybe integer ids make sense, because it scales differently than PostgreSQL.
If you're referring to not having conflicts between distributed nodes, that's a solved problem as well – distribute chunked ranges to each node of N size.
If you can't manage minor levels of coordination because your database is on fire, the problem is that your database is on fire.
In general you shouldn't need to make a roundtrip to produce an ID.
The distributed database needs a coordination system anyway, so it's not an additional point.
> In general you shouldn't need to make a roundtrip to produce an ID.
Did you forget the context over the last week? We're already talking about reserving big chunks to remove the need to make a roundtrip to produce an ID. There would instead be something like one roundtrip per million IDs.
Nope! Distributed databases do not necessarily need a "coordination system" in this sense. Most wide-scale distributed databases actually cannot rely on this kind of coordination.
> Did you forget the context over the last week? We're already talking about reserving big chunks to remove the need to make a roundtrip to produce an ID. There would instead be something like one roundtrip per million IDs.
OK, it's very clear that you're speaking from a context which is a very narrow subset of distributed systems as a whole. That's fine, just please understand your experience isn't broadly representative.
I'm assuming a system that tracks nodes and checks for quorum(s), because if you let isolated servers be authoritative then your data integrity goes to hell. If you have that system, you can use it for low-bandwidth coordinated decisions like reserving blocks of ids.
Am I wrong to think that most distributed databases have systems like that?
> OK, it's very clear that you're speaking from a context which is a very narrow subset of distributed systems as a whole. That's fine, just please understand your experience isn't broadly representative.
Sure, but the first thing you said in this conversation was "Whatever is distributing the chunks is still a point of central coordination." which is equally narrow, so I wasn't expecting you to suddenly broaden when I asked why that mattered.
Not sure why.
> because if you let isolated servers be authoritative then your data integrity goes to hell
Many AP systems maintain data integrity without central authorities or quorums for data.
> Am I wrong to think that most distributed databases have systems like that?
No, not wrong! Just that it's one class of distributed systems, among many.
This was nice because if the script failed half way through I could easily lookup which ids were already imported and continue where I left off.
The point is, this property of UUIDs occasionally comes in handy and it’s a life saver.
postgres=# CREATE TABLE foo(id INT, bar TEXT);
CREATE TABLE
postgres=# INSERT INTO foo (id, bar) VALUES (1, 'Hello, world');
INSERT 0 1
postgres=# ALTER TABLE foo ALTER id SET NOT NULL, ALTER id ADD GENERATED
ALWAYS AS IDENTITY (START WITH 2);
ALTER TABLE
postgres=# INSERT INTO foo (bar) VALUES ('ACK');
INSERT 0 1
postgres=# TABLE foo;
id | bar
----+--------------
1 | Hello, world
2 | ACK
(2 rows)I’m sure there’s a way to get it to work with integer ids but it would have been a pain. With UUID’s it was very simple to generate.
> I’m sure there’s a way to get it to work with integer ids but it would have been a pain. With UUID’s it was very simple to generate.
IME, if something is easy with RDBMS in prod, it usually means you’re paying for it later. This is definitely the case with UUIDv4 PKs.
But I get you like integers so whatever works for you, I just don't think they're the right tradeoff for most projects.
- client-side generation (e.g. can reduce complexity when doing complex creation of data on the client side, and then some time later actually inserting it into to your db)
- sequential ids leak competitive information: https://en.wikipedia.org/wiki/German_tank_problem
- Global identification (being able to look up an unknown thing by just an id - very useful in log searching / admin dashboards / customer support tools)