T-SQL Tuesday #202 : The Outage(s) I Won't Forget

The Stack Overflow database has a long lineage, and a lot more warts than what's exposed in Stack Exchange Data Explorer (SEDE). Many of the core tables were created a decade before I joined the staff in 2021, and they grew far larger than could have been envisioned back then.

One of those tables is UserHistory. This table uses an identity column as the clustered index and primary key, and records all kinds of information about each user's activity on the site. From changing your profile picture, to earning a new privilege, to changing a preference, to something as simple as logging in or out. Each event generates at least one new row in UserHistory. At peak popularity, this table would grow quite quickly and, as the application became more complex, more and more categories of activity and state would get written there.

I remember multiple times in my career writing "identity column overflow predictor"-style scripts. I can't seem to find any public evidence of those at the moment; probably tucked away in employers' source control systems. The closest I could find to what I remember writing is "How do I easily find IDENTITY columns in danger of overflowing?" by Keith Walton or this script by Rich Benner.

Yet, somehow, it wasn't on our radar.

And then…

One night, as if we had no way to predict or prevent it, the table hit the upper bound of the int data type. I want to say this was late 2022, but I really don't remember. Activity on Stack Overflow, at least for logged-in users, ground to a halt. Logins failed, as did anything else that would add a row to UserHistory. The exceptions caused a storm that blew up our monitoring, produced alerts that overwhelmed inboxes and Slack for every team, and even took down the status page. Ultimately the servers were so pegged dealing with exceptions that anonymous users who should have been unaffected also couldn't use the site.

This was a pickle. The most correct solution would have been to hop into the DeLorean and not choose int in the first place. The next most correct would have been to widen the column to bigint, which I've talked about before, though Andy Mallon's more recent walk-through is more straightforward, and got a well-deserved visibility boost recently. But that complexity during a late-night "site is down!" fire was daunting: lots of indexes, inbound foreign keys, and other constraints; lots of Int32 variables and structures littered throughout the application tiers; and lots of side effects such as the code that drives SEDE, APIs, and the data dump. Too much to consider while I felt managers, the CTO, and a still-healthy userbase collectively staring at us, demanding the site return to full functionality immediately or, at most, within the next minute.

The team working the incident decided together that we'd take the easy way out: reseed the table to -2 billion. There were a couple of edge cases we'd have to fix (e.g. one-off assumptions that old ID < new ID), but otherwise this solution had an immediate impact: the exceptions stopped and the site was back to normal! We all simultaneously cheered and berated both ourselves and each other. We calculated that the reseed had bought us roughly 12 years of runway to pay off the technical debt we'd just created; we could generate about two billion new negative IDs before we'd start colliding with the positive IDs already in the table. And we immediately stopped thinking about it, aside from the monitoring we put in place to check daily for any table coming close to its limit.

Some other background

As a controller of large amounts of personal data for millions of users, we had to adhere to several regulations, including GDPR. I spent a good amount of my time there creating and perfecting our "forget me" functionality, which would exhaustively go out and remove from all databases any PII belonging to any user who asked. It would first disassociate them from their posts, comments, chat messages, and a whole slew of other data. Ultimately, it would hard delete the primary user row and all of the user's history. (Why not just perform a soft delete? Well, fun fact: the arbitrary surrogate UserId that the Stack Overflow database generates for you also becomes part of your PII. It has to be completely removed.)

Every once in a while, the team who processed forget me requests would accidentally run this routine for the wrong user. We had a runbook for restoring a user if this was caught early enough, by pulling data from a restored copy of the most recent backup. It wasn't exhaustive, but it would re-associate the user with their posts, comments, and chat history, restore the user row, and re-insert all their history (using SET IDENTITY_INSERT ON).

You may sense where this is going…

I didn't remember this date, but meta can sometimes help out: On June 15, 2023, a wrongly-deleted user was restored using the runbook, but the last step for UserHistory ("reseed") wasn't there or, maybe, a dummy like me just forgot. And went to bed. Only to be disturbed minutes later by my phone blowing up.

Yes, this caused a second, almost identical outage

When the deleted user's history was restored, some of those original identity values we had to restore were positive. SQL Server adjusted the identity's current value to account for the values we'd just inserted. The next normal insert picked up from there, right back near the positive int ceiling we'd escaped from months earlier.

The most direct fix was still simple: reseed the table back to negative.

Again.

The first outage was because we had failed to notice that an identity column was approaching the upper bound. Our emergency fix bought us roughly two billion more values and, even at the rate the table was growing, years to implement a permanent solution.

The second outage was because SET IDENTITY_INSERT ON couldn't possibly care less about the little arrangement we'd made with ourselves. And the race back to the upper bound didn't give our monitoring enough time to catch it. We weren't inventorying every identity column across every database every second of every day, and it got there almost immediately.

Neither of these outages required an obscure SQL Server bug, a hardware failure, or some once-in-a-career concurrency problem. The first was an integer doing exactly what integers do; the second was an identity doing exactly what identities do. SQL Server behaved perfectly reasonably both times.

I was the unreliable component, and I managed to turn years of runway into minutes.

What I took away from it

Three short lessons:

• Capacity isn't just about disk space. Data types have finite capacity just like disk space, memory, and many of the other things we spend countless hours monitoring and tuning. The sys.identity_columns catalog view makes it easy to periodically inventory identity usage and raise a flag well before a column approaches the limit of its data type.
   
• Emergency fixes often create future obligations. I still believe that reseeding was the right choice during the first outage, because it was the only way to get us back online immediately. What bit us in the end is that we allowed it to become invisible. Anything capable of changing the identity's current value needed to know about our little arrangement, too.
   
• Runbooks need to be complete… and followed completely. I still don't remember whether I forgot to put that step into the runbook, or put it there and forgot to run it, but there shouldn't have been a way to forget that step and still declare victory.

Most of the incidents I worked during my time at Stack Overflow are distant memories. But I remember these two.

The first time, we pushed an ID right to the edge of a 2,147,483,647-foot cliff. The second time, we took a shortcut that provided over two billion chances to make sure it would never happen again. And we found a way to burn through all of them.

Monitor your identity columns. And if your emergency fix depends on an invariant that isn't obvious from the schema, monitor that too.