@radekmieBy Radosław Miernik · Published on · Comment on Reddit
While planning yet another feature, one of the designers asked, “How many values do we expect to see in this dropdown?” You, the only person with database access at this meeting, run a trivial query and reply, “Six, on average.” Everyone’s happy, everyone continues with their day.
And on the first day it lands on production, you see this horror with more than a thousand options to choose from. “You said it’ll be six!” Well, you were right, but not right right. That’s where being “technically correct” is not enough.
Today, we’ll talk about the alternatives and their outcomes. Sort of a statistics 101, but with little-to-zero formal language. As the title suggests, we’ll arrive at histograms, but let’s not jump the gun.
The major problem with averages is that they can’t handle outliers nicely. An outlier is something unusual and far from normal, e.g., this one enterprise client of yours, who makes up for 20% of your total traffic.
The thing is that given enough difference between an outlier and the rest, they will impact the average significantly. Remember that outliers don’t necessarily have to be large; the few clients who left last year and now produce zero traffic are affecting the average as well.
Once you realize an average is not enough, the first Google search suggests using standard deviation. The idea is simple: instead of a single number, we’ll think about a range where most of the data fits. (Approximately 68%, assuming a normal distribution. If you’re not sure what that means, “most”.)
How does it look in practice? If the standard deviation is low (compared to the average), then most of your data is close to each other; if it’s high, then the data is spread more. We just don’t know spread how.
A different direction would be to look at certain percentiles instead. While there are multiple formal definitions (nitty-gritty details), the idea is simple: the 60th percentile represents a value below which 60% of all values are.
This allows us to put a number on the Pareto principle, which I guess you heard a lot about while listing all the edge cases you came up with. If we go back to our dropdown example, we could say, “97.4% of our users have 27 options or less. However, the maximum is over a thousand.” A much more informed decision.
Let’s take a real-life example: API response times. The design team is wondering whether the loading state of this new bulk edit view will appear for just a moment or if we expect it to stay on for a while. Let’s get the numbers in:
| Case A | Case B | |
|---|---|---|
| Average | 100ms | 100ms |
| Standard deviation | 80ms | 10ms |
| 90th percentile | 300ms | 120ms |
| 95th percentile | 500ms | 160ms |
| 99th percentile | 2500ms | 190ms |
As you can see, the average is the same. Standard deviation is already telling us something – case A is spread far more. Then, the percentiles show that case B is “fast enough” for “most” users. I’d say A needs a better loading state than B; the latter will be fine with just a spinner on the submit button.
Even if you understand the shortcomings of all the aforementioned metrics, always remember to visualize the data anyway. Just a quick-n-dirty graph will do. Check out this lovely Anscombe’s quartet:
In short, these are four different datasets with almost identical statistics but completely different distributions. If you’re in your dinosaur phase (whether you like it or not), check the Datasaurus dozen, too.
Finally, let’s talk histograms. So far, we have thought about “summarizing” the data we’re given, but not really how it “looks” like. Of course, we can chart it, but if one of the axes is large, then the chart may get unreadable.
Instead, we group the values into a handful of “buckets” (or “bins”), representing some range. Of course, we can dive into it as granular as we need (or rather as we’re asked to). From my experience, just a handful of buckets is enough to make a decision (I usually start with ten).
You can also mix and match what you learned today! If instead of the current one, we count all the previous buckets, we end up with a kind of percentile-like chart. Look at these beauties:
Let’s face the dropdown options once again. Knowing that a basic histogram would help us, let’s make one from the data we have. One way would be to throw it into your Excel-like tool of choice.
I’m fine with text form, and I’d like to have it right now (we’re still at that meeting, remember?), so let’s try to do it directly in the database. Luckily, MongoDB comes up equipped with $bucketAuto:
// Data shape: {
// _id: ObjectId(),
// tags: ['Name 1', ...],
// tenantId: ObjectId(),
// }
;
The result looks like this:
We no longer see the average, standard deviation, or percentiles, but we can easily calculate them too, using $avg, $stdDevPop, and $percentile (I’ll leave that as an exercise for you, dear reader).
Of course, it’s not rocket science, so if you’re using any SQL database, a group by using the bucket will work just fine. And you don’t have to be worried about the performance – all we need is the single number we’re putting into the buckets, so you should be fine with running it ad-hoc without an index.
That’s it for today! Hopefully, it wasn’t too formal, and you’ll spread the love for histograms further. Or at least make informed decisions more often.
“Yeah, sorry, I zoned out. How many options we had there again…?”