The email came in at 3:47 p.m. on a Friday.
"Our analytics dashboard is taking 4 minutes to load. Users are complaining. Can we fix this?"
I looked at our AWS bill. We were spending $8,000 a month on a database that was basically doing math on spreadsheets. Expensive math!
My boss asked the question every engineer dreads: "What if we just... used a different database?"
Sure. Easy. Which one?
That weekend, I made a list:
- ClickHouse (everyone says it's fast)
- DuckDB (the new hotness)
- PostgreSQL (what we're using now)
- Snowflake (what our competitors use)
- TimescaleDB (someone on Reddit recommended it)
- MongoDB (because why not)
- Elasticsearch (we already have it)
- MySQL (just to see how bad it could be)
Then I did what any reasonable person would do: I decided to test them all with the same data and see what happened.
This is that story.
The Setup: Same Question, Eight Different Answers
Think of it this way: Imagine you needed to count every red car that drove past your house last month.
You could:
- Write it down in a notebook as they pass (like a traditional database)
- Take photos of each car and sort them later (like a document database)
- Just count the red ones and ignore everything else (like a column-store database)
Each method works. But which one is fastest? Which one costs the least? Which one breaks when your neighbor starts parking 50 red cars on your street?
I needed to know.
The Dataset: I grabbed 6 months of real e-commerce data from our warehouse:
- 847 million individual purchase events
- 120 million unique customers
- 450,000 products
- About 180 GB of raw data
The Test: Five simple questions that every analytics dashboard asks:
- How much money did we make each day?
- What were our top 10 products this month?
- Which customers spent the most?
- What's our average order value by country?
- Show me a customer's full purchase history
The kind of questions that sound simple until you have 847 million rows to look through.
Monday Morning: Loading the Data
First surprise: they don't all eat data the same way.
PostgreSQL took 4 hours to load everything. Like watching paint dry, but the paint is numbers.
ClickHouse finished in 47 minutes. It compressed the data so much that 180 GB became 23 GB. I checked three times to make sure nothing was missing.
DuckDB didn't even want to "load" the data. It just... read it directly from the files. "Why move it?" it seemed to ask. Fair point.
MongoDB took 11 hours. ELEVEN HOURS. I started the load before bed, and it was still going at breakfast.
Snowflake was the weirdest. I uploaded the files, and it said, "Thanks, I'll get back to you." Then it did some processing in the background that I couldn't see. The whole thing felt like dropping off your car at a mechanic who won't let you watch.
Cost so far: $47 in compute time just to move data around.
Tuesday: The First Query
"How much money did we make each day?"
This is the simplest possible question. Add up all the purchases; group them by date. Done.
Results:
- ClickHouse: 0.3 seconds
- DuckDB: 0.4 seconds
- PostgreSQL: 43 seconds
- TimescaleDB: 12 seconds
- MongoDB: 2 minutes, 17 seconds (then crashed)
- Snowflake: 1.8 seconds
- Elasticsearch: 8 seconds
- MySQL: I stopped it after 5 minutes
Let me put that in perspective.
ClickHouse answered in the time it takes you to blink. PostgreSQL—the database we were currently using—took as long as it takes to microwave a burrito.
MongoDB had to think about it longer than my coffee break, then gave up entirely.
But here's where it gets interesting.
The Hidden Costs
Speed isn't everything. Sometimes the fastest car has the worst gas mileage.
I ran each query 100 times and measured what it actually cost:
PostgreSQL:
- Query time: 43 seconds
- Cost per query: $0.02
- Monthly cost (100 queries/day): $60
ClickHouse:
- Query time: 0.3 seconds
- Cost per query: $0.03
- Monthly cost (100 queries/day): $90
Wait. ClickHouse was more expensive per query, even though it was 140 times faster?
Yes. Because it uses way more CPU power. It's like comparing a Prius to a Ferrari—the Ferrari is faster, but it drinks gas.
DuckDB:
- Query time: 0.4 seconds
- Cost per query: $0.00
- Monthly cost: $0 (runs on the application server we already have)
This changed everything.
Wednesday: The Complex Questions
The next queries were harder. "Show me a specific customer's purchase history" required looking through all 847 million rows to find maybe 50 purchases.
This is where the differences got weird.
Test: Find customer #8472's purchases
- ClickHouse: 0.9 seconds (scanned everything, found 47 purchases)
- PostgreSQL: 0.2 seconds (had an index, jumped right to them)
- DuckDB: 1.2 seconds (scanned everything like ClickHouse)
- MongoDB: 0.1 seconds (built for exactly this)
PostgreSQL—the "slow" database—just won.
Because sometimes the right tool isn't the fastest at everything. It's the fastest thing you actually need.
Thursday: When Everything Broke
I wanted to see what happens when you push these systems too hard.
So I did something stupid: I ran all five queries at the same time, 50 times each.
PostgreSQL: Slowed down proportionally. 43 seconds became 3 minutes. Predictable.
ClickHouse: Didn't care at all. Still 0.3 seconds per query. Just burned through CPU like it was free.
DuckDB: Crashed. Just completely died. Turns out, when you run it inside your application, it shares resources with everything else. When I overloaded it, I overloaded everything.
Snowflake: Charged me $12 for 10 minutes of work. Then sent me a cheerful email about "compute cost optimization tips."
MongoDB: I'm honestly not sure what it was doing. The queries were running, but the results seemed... wrong? Like it was dropping data? I never figured it out.
Friday: The Part Nobody Talks About
You know what nobody mentions in those "Database Comparison" blog posts?
Maintenance.
PostgreSQL: Our DevOps team already knows it. Training cost: $0.
ClickHouse: I had to learn a whole new query language. The documentation felt like it was translated from Russian by someone who was annoyed at me. Training cost: probably $10k if we hire an expert.
DuckDB: Read the docs in 2 hours. Built a prototype in a day. Training cost: basically none.
Snowflake: Called sales three times to understand our bill. Still not 100% sure how their pricing works.
What I Actually Learned
After two weeks of testing, here's what I figured out:
1. There's No "Best" Database
It's like asking, "What's the best vehicle?"
Are you:
- Moving one person across town? (Bike)
- Moving a family? (Car)
- Moving 40 people? (Bus)
- Moving 500 people? (Airplane)
Different jobs need different tools.
2. Column-Stores Are Genuinely Magic (For Certain Things)
ClickHouse and DuckDB store data by column instead of by row.
Imagine a spreadsheet with a million rows. If you want to add up one column, you don't need to look at the other columns at all. Column stores figured this out. Row stores didn't.
For "add up all the sales" queries, this is 100-1000x faster.
For "show me everything about customer #8472" queries, it's actually slower.
3. Speed Costs Money (Sometimes)
The fastest databases often use the most resources. ClickHouse was running our simple queries faster than I could blink, but it was also using 16 CPU cores to do it.
DuckDB was almost as fast but ran inside our existing application. No new servers. No new costs.
4. The Real Cost Isn't the Database
It's the time your team spends:
- Learning it
- Debugging it
- Explaining to your boss why the bill went up
PostgreSQL was "slow" at our aggregate queries. But it was fast enough. And our team already knew how to use it.
Sometimes "good enough and familiar" beats "perfect and confusing."
What I Actually Did
Remember that $8,000/month bill?
I didn't replace the database.
Instead, I:
- Kept PostgreSQL for the transactional stuff (user accounts, orders, product catalog)
- Added DuckDB for the analytics dashboard (runs right next to our app, no new infrastructure)
- Pre-calculated the expensive queries once per hour (stored the results, served them fast)
New monthly cost: $8,100 ($100 for the DuckDB server)
Dashboard load time: 4 minutes → 0.8 seconds
Boss's reaction: "Why didn't we do this sooner?"
Engineer's answer: "Because sometimes you need to test eight databases to find out you only needed 0.2 of one."
The Actual Benchmark Numbers (For the Nerds)
If you want the raw data, here's what I measured:
Query 1: Daily Revenue Aggregation (847M rows → 180 results)
ClickHouse: 0.31s ($0.03/query)
DuckDB: 0.42s ($0.00/query)
Snowflake: 1.80s ($0.12/query)
TimescaleDB: 12.00s ($0.02/query)
PostgreSQL: 43.00s ($0.02/query)
Elasticsearch: 8.00s ($0.04/query)
MySQL: 300.00s+ (stopped it)
MongoDB: 137.00s (then crashed)
Query 2: Top 10 Products by Revenue
ClickHouse: 0.28s
DuckDB: 0.51s
Snowflake: 2.10s
PostgreSQL: 67.00s
TimescaleDB: 18.00s
Query 3: Customer Lookup (Find 1 customer's 47 purchases)
MongoDB: 0.11s (indexed)
PostgreSQL: 0.23s (indexed)
MySQL: 0.31s (indexed)
ClickHouse: 0.94s (full scan)
DuckDB: 1.18s (full scan)
TimescaleDB: 0.41s (indexed)
Data Loading Time (180 GB)
DuckDB: 0m (reads files directly)
ClickHouse: 47m (compressed to 23 GB)
MySQL: 89m
TimescaleDB: 142m
PostgreSQL: 238m
Snowflake: ??? (async, took ~30m but unclear)
MongoDB: 687m (11+ hours)
Storage Efficiency (180 GB input)
ClickHouse: 23 GB (87% compression)
DuckDB: 180 GB (0%, reads source files)
PostgreSQL: 340 GB (89% overhead - indexes)
MongoDB: 520 GB (189% overhead)
Snowflake: 41 GB (77% compression)
TimescaleDB: 89 GB (51% overhead)
Conclusion
For aggregating millions of rows, use a column store (ClickHouse/DuckDB).
For finding specific records: use a row-store with indexes (PostgreSQL/MongoDB).
For everything: use both and stop trying to make one database do everything.
The thing nobody tells you about databases: The best one isn't the fastest one. It's the one your team can actually use without calling you at 3 a.m. Choose wisely.