frankie boyle's beanbag chair of death

you can tell someone's a database admin, because any question results in either "that would be slow", or just "no"

if you don't believe me, you can ask them to do something very simple, "can i get an exact count of how many records are in the database" and you'll usually get "a sort of" then an "approximate" and then they'll mumble something about stale reads before you get to the classic answer of "no"—and any attempt to outsmart them will result in "that would be slow"

(forgive me for tech posting, but i'm about to explain something)

this sort of problem raises its head in numerous places: "why is my youtube video stuck at 417 views", "why do my likes keep disappearing", or more classically "my inbox says i have one unread message but it's empty."

the problem isn't that everyone in computers is bad at computers (for once), the problem is that sometimes the most simple sounding problems have entirely fiendish answers. to explain, i'm afraid i'll have to use a metaphor. (again, forgive me)

instead of counting records in a table, let's imagine you're asked to count people at a venue.

the obvious method "go through the venue, counting everyone in attendance" has a small problem, someone could leave after you start counting, or arrive without you noticing, so the full answer is "lock the door, count everyone, unlock the door", and guess what: that would be slow.

if you have a large venue with hundreds of people, locking the doors isn't an option. instead, the smart move is to "count people entering and exiting", which works assuming you only have one door. you got more doors? you got more problems.

if you ask for the count at door 1, then ask for the count at door 2, well, someone could have arrived or left from door 1 by the time you get the count at the second door. again, the solution is "lock the doors while you get the counts", but again, that isn't a great option, so perhaps you put in a little radio at the door, every time someone comes in or leaves, you tell the office.

again, "that would be slow". imagine you have the venue the size of a stadium, with several entrances and hundreds of people coming and going. the overhead of coordinating every person who leaves or arrives becomes expensive very quickly.

ok, ok. let's say we ask everyone at the doors to write down the count at a given time. assuming their watches are all relatively in sync, we can get a reasonably accurate count of how many people were in the venue, just not how many people are in the venue right now.

if you don't need a count right this moment, and are ok with having a count that's a little bit out of date by the time you get it, this problem is a whole lot easier. this is the bit where the dba mumbles something about stale data.

now imagine instead of one venue with lots of doors, it's hundreds of venues, with people coming and going every minute, or even every second, and worse? sometimes the phonelines between the venues don't work, or there's a long delay between saying something and hearing something. even if you ask people to write down the count, they might not be able to report it in time.

and hopefully the idea of simultaneously locking every venue to force accurate counts seems like a bad idea. you might not get every venue to lock their doors, you might have missing reports, or worse, you might not get every venue to unlock their doors when they're done. this is when you get to say "no" instead of "that would be slow"

"no", you can't stop the world to get an accurate snapshot, but you could use a lot of bookkeeping and coordination to do so. unfortunately, "that would be slow."

in simpler terms: welcome to hell. once you add latency and failure to the mess that is concurrency, there are no good answers, only suffering. although getting a count on a database table is relatively easy when you've only got three records and two users, everything falls apart once the numbers get big.

hopefully that's explained why counting is so hard. concurrency means adding overhead for coordination. latency makes everything slower, and failure handling makes things near impossible.

and if you're wondering, dbas don't always start off saying "no" or "that would be slow"—it's just when people ask for an explanation, they don't want to learn why it won't work, they're trying to outsmart you, asking you to teach them in the most adversarial way possible.

it gets old very quickly.