You know the feeling. You’ve written a perfectly reasonable SQL query. Your dashboard depends on it. Your boss is waiting. And somewhere deep in your database, the engine has started a slow, grinding march toward an answer that may never come.
The query is COUNT(DISTINCT user_id). And it’s not just crawling, it’s eating your production box alive while a little spinner mocks you from the screen.
You don’t need an exact number. You need a good one. And refusing to accept that is costing you real money.
The dirty secret of modern data engineering is that the most expensive operation in your entire stack isn’t some complex machine learning pipeline or a sophisticated recommendation algorithm. It’s the humble COUNT(DISTINCT) — a piece of SQL so common it’s in the first tutorial you ever read, and so expensive it can take down your entire analytics platform.
Here’s why: to count distinct values exactly, your database has to track every single unique value it has ever seen. Every user ID. Every session token. Every transaction hash. It’s building a massive, never-ending list that grows with every row you have ever ingested. There’s no shortcut. There’s no index magic. It’s just a brute-force, memory-hogging, time-destroying exercise in bureaucratic accounting.
And here’s the paradox that makes it even more absurd: you probably don’t need the answer it’s dying to give you.
Let me paint you a picture. You’re analyzing monthly active users for a product with 20 million installs. Your query returns 4,991,203. The board meeting is about to start. The CEO asks if the number is trending up. You say yes. The meeting moves on.
Would that presentation have gone differently if the number was 5,000,847? Would anyone in that room have noticed the difference? Or made a different decision? The answer is no. But somehow, you—and thousands of engineers just like you—are burning hours of compute time every single day to chase a precision that has zero impact on any human being’s decision-making.
The real bottleneck isn’t your query planner. It’s your cultural assumption that exact answers are always worth the wait.
This is where the engineering world gets a little uncomfortable. Because admitting you don’t need exact counts feels like admitting you’re sloppy. It feels like the kind of thing that gets you yelled at in a code review. But it’s actually the opposite: choosing approximation isn’t a failure of rigor. It’s a demonstration of maturity.
There’s a whole family of probabilistic algorithms built for exactly this problem. They have names like HyperLogLog, and they do something almost magical: they give you an answer that’s within 1-2% of the exact count, using a fraction of the memory and a fraction of the time. Instead of listing every single user ID, they use smart hashing and a clever bit of math to estimate the number of uniques you’ve seen. It’s not a guess. It’s a scientifically engineered approximation that’s good enough for 99% of real-world use cases.
Now, I can already hear the objection forming in your head. “What about my finance report? What about regulatory compliance?”
Fine. There are cases where exactness truly matters. If you’re counting transactions for an audit, or preparing a settlement statement, you absolutely need the exact number. Keep your COUNT(DISTINCT) for those. But here’s the thing you need to hear: 99% of the COUNT(DISTINCT) queries in your warehouse don’t fall into that category.
They’re growth metrics. Product analytics. Marketing dashboards. Trend lines. And for all of those, the difference between 4,991,203 and 5,000,847 is literally noise. It’s the difference between a 4.9% growth rate and a 5.0% growth rate. It’s inconsequential to any strategic decision you will ever make.
So why are we obsessed with it? Why do we cling to this false god of precision?
Because it’s easy. Because it’s the default. Because no one ever got fired for using COUNT(DISTINCT). And because admitting that an approximation is good enough feels like a loss of control.
But here’s what you’re actually losing: you’re losing speed, you’re losing scalability, and you’re losing the ability to get answers in real time. You’re building a system that can’t keep up with your ambition, and you’re doing it because of a theoretical preference for exactness that doesn’t match the practical needs of your business.
I’ve watched teams spend entire sprints optimizing a single dashboard query that nobody looks at more than once a week. I’ve seen engineers burn thousands of dollars in cloud compute to make a metric slightly more precise, for a decision that was already made based on a gut feeling. All because they were afraid to be the one who said, “Close enough.”
Your database isn’t slow because it’s a bad tool. It’s slow because you’re asking it a question it doesn’t need to answer.
Here’s what I want you to do next time you’re staring at a query that’s been running for forty-five minutes: stop. Ask yourself what decision this number is actually informing. If the answer doesn’t depend on the last 5,000 rows, then you don’t need COUNT(DISTINCT) — you need a smarter approach.
Use an approximation. Use a cache. Use a pre-aggregated rollup. Use the estimate. Trust the math that says 99% accuracy is enough.
The people who build genuinely scalable systems have figured this out. They don’t treat every query like a legal document. They treat it like a tool for making decisions. And they understand that the best tool for the job is often the one that’s fast enough to be useful, not the one that’s exact enough to be perfect.
Let go of the precision. Your query will finish. Your dashboard will load. Your infrastructure bill will drop. And you’ll finally have time to work on things that actually matter.
Because in the real world, the best answer is the one you can get before the deadline.
FAQ
Q: I understand approximations are faster, but what if my boss genuinely needs the exact number for a report?
A: Then keep COUNT(DISTINCT) for that specific query. But be honest with yourself and your boss about how often that's actually true. In most organizations, the exact number is needed for less than 1% of analytical queries. The rest are trend lines and growth metrics where 1-2% error is invisible. The goal isn't to eliminate exactness — it's to stop using it as a default for everything.
Q: What's the practical difference between using HyperLogLog and just running COUNT(DISTINCT) on a sample of data?
A: They're fundamentally different approaches. Sampling reads a subset of rows and extrapolates, which can miss rare values entirely. HyperLogLog reads every row but uses a probabilistic hash to estimate cardinality with a bounded error rate (typically under 2%). It's more accurate than sampling for distinct counts, and it uses a fixed, tiny amount of memory — about 1.5KB for a 64-bit register set. That's the tradeoff: you get precision without the memory explosion.
Q: If exactness is so rarely needed, why does the industry still default to COUNT(DISTINCT) everywhere?
A: Inertia, mostly. It's the first tool every SQL developer learns, and it's embedded in countless BI tools as a default aggregation. Admitting you don't need it feels like admitting you're not rigorous. But the engineering community is slowly shifting — modern data warehouses like Snowflake and BigQuery have native approximation functions built in, and they're fast. The culture is changing, just slower than the tooling.