Ha, a site I worked on hit this limit for the "follow relationships" table - had to build a new compound key table to migrate to, with triggers to dual read/write, to unbreak everything. In a few hours of "wtf" -> "oh crap" -> "well I guess we gotta do it right this time" and quick coding.
And then I pulled apart PT-OSC to make it more... less incredibly stupid about resource use, so it wouldn't cause too much load while it backfilled. And let it run for about 6 weeks.
Good luck! It's a fun problem to have - excess success, and a light puzzle to solve :)
p0w3n3d · 2h ago
Hacker News helps me everyday break my information bubble. Archive Of Our Own is something that I wouldn't walk into when wandering through the internet
chii · 2h ago
> I wouldn't walk into when wandering through the internet
it's interesting that some people are on the internet but is very well insulated! AO3 is very well known for me...
diggan · 32m ago
> it's interesting that some people are on the internet but is very well insulated
Not sure I'd call it "insulated", the internet is just very, very vast, even when considering "just" the English-speaking web. Then you have all the other "versions" out there too that are kind of hidden to most people :)
Anecdotal, but also first time I heard about AO3, and I'd consider myself having broad interests and generally well-read, although my interests doesn't include fanfiction so maybe not so weird I haven't heard about it before.
jorvi · 2m ago
Its very much a gendered thing. If you have lots of female (online) friends and late night topics with them ended up trending spicy, you might hear of AO3.
FWIW the vast majority of writing on there is decidedly mediocre. There is also an even more inferior alternative called Wattpad.
parlortricks · 1h ago
this is the first i've heard of it
zerocrates · 2h ago
Is it faster to convert a column like this to unsigned? Obviously assuming you don't use negative IDs in the application.
That's much more of a "kick the can down the road" solution to only double your usable range, but if all positive the values in the rows shouldn't actually have to change, just the column metadata, so it could theoretically be more or less instantaneous. I guess in practice this doesn't happen; the server would rather use its generic "rebuild the table" alter method for changing a column type.
But it seems like you could reasonably do it if it's a signed-to-unsigned change and there's no negative values and there's an index on the column to make checking that fact fast. Or one of those third-party/lower-level type tools could let you do it without any checking.
adamcharnock · 1h ago
An interesting idea! I suspect a major speed up would come from the fact that the column is staying the same size. So (I assume) far fewer bytes would need to be moved around.
12_throw_away · 2h ago
For anyone who feels like looking up exactly what this bookmark was pointing to: I did, and very much wish I hadn't!
nikanj · 40m ago
I know I'll regret this, but how do you navigate to bookmark by column id?
That article was from 2014, it has many more views now (about 5.6 billion).
RainyDayTmrw · 4h ago
It's kinda impressive that they got to 2 billion rows - with indexes, no less - without falling over.
jiggawatts · 36m ago
Point queries — typical of this kind of app - scales as log(n) in the number of rows. (Assuming a typical b-tree database index.)
This kind of workload cheerfully “scales” to your disk capacity.
olivermuty · 1h ago
Uh, the bookmark that broke it all was to a part of the internet I have yet to experience since getting online some 30 years ago. Alphas and betas and omegas, it was a wild ride.
madaxe_again · 2h ago
This is like seeing a brick wall 40 miles down a straight road and yet still managing to drive into it, and then blaming the wall.
darkwater · 2h ago
I guess that whoever maintains that infra simply hadn't thought of it or was not aware. It's not something you get for free in a monitoring system with some agent like disk usage for example. You need to know and remember you have a hard limit on IDs and be aware at which ID you are.
ohdeargodno · 2h ago
Ao3 doesn't have a dude getting slack alerts by a dozen monitoring agents. It's one of the last holdouts of the old, more personal internet. Hell, it's even certain that they forgot or even didn't know that the type was an unsigned int.
And that's perfect. Blame the wall too, because it was running just fine. It's a site to write (mostly porn), with better uptime and more daily users than most of the companies posted on HN daily.
camel-cdr · 1h ago
I wasn't sure what the percentage of porn is, so I counted the number of works for each maturity rating:
4,247,583: Teen And Up Audiences
4,173,082: General Audiences
2,816,083: Explicit
2,271,446: Mature
1,676,061: Not Rated
charcircuit · 3h ago
>to fix it they have to migrate the entire database to use a different type for bookmark IDs... except of course this will take a while because there are two Billion Of Them Lol
You can shard them between 2 tables. Then migrate them to a single one later.
ohdeargodno · 2h ago
There's no SLA for Harry Styles porn. Run the migration, lock the table for two days and redo the same in 13 years when you get to 4 billion bookmarks.
camel-cdr · 1h ago
> There's no SLA for Harry Styles porn
But what about my good night's sleep? How can I go to bed without reading about my favorite blorbos?
ohdeargodno · 1h ago
Real ones use bookmarks to find them ag- ah, shit.
Real ones back them up in a single .txt file
kijin · 1h ago
In 13 years, the Unix timestamp will probably be a much bigger problem.
notorandit · 4h ago
> typical database column
Typical for 70s and 80s.
Honestly, designing a 21st century database is a different thing if compared to back then.
You can use 128 bit integers, provided that you really want to use integers.
And maybe you put a timestamp along.
shakna · 1h ago
Salesforce is a rather popular platform.
Its defaults are also either a 18-character ID, or a 32bit integer. So, unless you take the effort to actually fight Apex, you're gonna hit this problem sooner or later.
throwawaysoxjje · 1h ago
Nah I made the same mistake back in 2009 for a system that was storing behavior events during malware analysis.
You don’t often expect to have two billion of something until you do.
9dev · 1h ago
It's not like those two billion things just materialise in your database, right? Someone must have watched that graph climb, and climb, and climb, approaching the limit.
detaro · 1h ago
If they have that graph and remember the limit they choose 15 years ago... It's not something you think about constantly running a mostly stable code-wise site.
jarofgreen · 2h ago
or use UUID/GUIDS, many databases (eg PostgreSQL) and frameworks (eg Django) support them.
dwedge · 2h ago
Using uuids can cause lots of problems with indexing, fragmentation, row size and index size
j16sdiz · 3h ago
let's use 128bit integer and handle them like floats in php!
and maybe put a 32bit timestamp along and pretend it can somehow store more than a 32bit integer can.
And then I pulled apart PT-OSC to make it more... less incredibly stupid about resource use, so it wouldn't cause too much load while it backfilled. And let it run for about 6 weeks.
Good luck! It's a fun problem to have - excess success, and a light puzzle to solve :)
it's interesting that some people are on the internet but is very well insulated! AO3 is very well known for me...
Not sure I'd call it "insulated", the internet is just very, very vast, even when considering "just" the English-speaking web. Then you have all the other "versions" out there too that are kind of hidden to most people :)
Anecdotal, but also first time I heard about AO3, and I'd consider myself having broad interests and generally well-read, although my interests doesn't include fanfiction so maybe not so weird I haven't heard about it before.
FWIW the vast majority of writing on there is decidedly mediocre. There is also an even more inferior alternative called Wattpad.
That's much more of a "kick the can down the road" solution to only double your usable range, but if all positive the values in the rows shouldn't actually have to change, just the column metadata, so it could theoretically be more or less instantaneous. I guess in practice this doesn't happen; the server would rather use its generic "rebuild the table" alter method for changing a column type.
But it seems like you could reasonably do it if it's a signed-to-unsigned change and there's no negative values and there's an index on the column to make checking that fact fast. Or one of those third-party/lower-level type tools could let you do it without any checking.
It's Dead Dove though.
https://arstechnica.com/information-technology/2014/12/gangn...
This kind of workload cheerfully “scales” to your disk capacity.
And that's perfect. Blame the wall too, because it was running just fine. It's a site to write (mostly porn), with better uptime and more daily users than most of the companies posted on HN daily.
You can shard them between 2 tables. Then migrate them to a single one later.
But what about my good night's sleep? How can I go to bed without reading about my favorite blorbos?
Real ones back them up in a single .txt file
Typical for 70s and 80s.
Honestly, designing a 21st century database is a different thing if compared to back then.
You can use 128 bit integers, provided that you really want to use integers. And maybe you put a timestamp along.
Its defaults are also either a 18-character ID, or a 32bit integer. So, unless you take the effort to actually fight Apex, you're gonna hit this problem sooner or later.
You don’t often expect to have two billion of something until you do.
and maybe put a 32bit timestamp along and pretend it can somehow store more than a 32bit integer can.