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) 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) 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: The Dataset: 847 million individual purchase events
120 million unique customers
450,000 products
About 180 GB of raw data 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: The Test: 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 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. PostgreSQL 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. ClickHouse 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. DuckDB MongoDB took 11 hours. ELEVEN HOURS. I started the load before bed, and it was still going at breakfast. MongoDB 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. Snowflake 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: 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 ClickHouse: 0.3 seconds ClickHouse: DuckDB: 0.4 seconds DuckDB: PostgreSQL: 43 seconds PostgreSQL: TimescaleDB: 12 seconds TimescaleDB: MongoDB: 2 minutes, 17 seconds (then crashed) MongoDB: Snowflake: 1.8 seconds Snowflake: Elasticsearch: 8 seconds Elasticsearch: MySQL: I stopped it after 5 minutes MySQL: 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: PostgreSQL: Query time: 43 seconds
Cost per query: $0.02
Monthly cost (100 queries/day): $60 Query time: 43 seconds Cost per query: $0.02 Monthly cost (100 queries/day): $60 ClickHouse: ClickHouse: Query time: 0.3 seconds
Cost per query: $0.03
Monthly cost (100 queries/day): $90 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? more expensive 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: DuckDB: Query time: 0.4 seconds
Cost per query: $0.00
Monthly cost: $0 (runs on the application server we already have) 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. weird Test: Find customer #8472's purchases 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) ClickHouse: 0.9 seconds (scanned everything, found 47 purchases) ClickHouse: PostgreSQL: 0.2 seconds (had an index, jumped right to them) PostgreSQL: DuckDB: 1.2 seconds (scanned everything like ClickHouse) DuckDB: MongoDB: 0.1 seconds (built for exactly this) MongoDB: 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. PostgreSQL: ClickHouse: Didn't care at all. Still 0.3 seconds per query. Just burned through CPU like it was free. ClickHouse: 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. DuckDB: everything Snowflake: Charged me $12 for 10 minutes of work. Then sent me a cheerful email about "compute cost optimization tips." Snowflake: 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. MongoDB: 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. PostgreSQL: 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. ClickHouse: DuckDB: Read the docs in 2 hours. Built a prototype in a day. Training cost: basically none. DuckDB: Snowflake: Called sales three times to understand our bill. Still not 100% sure how their pricing works. Snowflake: 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) 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. column 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 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) Kept PostgreSQL for the transactional stuff (user accounts, orders, product catalog) Kept PostgreSQL for the transactional stuff Added DuckDB for the analytics dashboard (runs right next to our app, no new infrastructure) Added DuckDB for the analytics dashboard Pre-calculated the expensive queries once per hour (stored the results, served them fast) Pre-calculated the expensive queries once per hour 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) 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 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) 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) 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) 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.

Reddit

I Spent Two Weeks Testing 8 Databases With the Same Question - Here's What Actually Happened

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

5 Data Pipeline Anti-Patterns That Silently Wreck Your Stack (And How I Fixed Them)

10 Best Datasets for Geospatial Analytics (Open and Public Access)

3 Industries Harnessing the Power of Big Data: Healthcare, Law, and Retail

4 Tips To Become A Successful Entry-Level Data Analyst

4 Ways in Which Predictive Analytics in Insurance is Paving the Way for the Future

5 Benefits of Dashboard Software for Business Intelligence in 2024

5 Data Pipeline Anti-Patterns That Silently Wreck Your Stack (And How I Fixed Them)

10 Best Datasets for Geospatial Analytics (Open and Public Access)

3 Industries Harnessing the Power of Big Data: Healthcare, Law, and Retail

4 Tips To Become A Successful Entry-Level Data Analyst

4 Ways in Which Predictive Analytics in Insurance is Paving the Way for the Future

5 Benefits of Dashboard Software for Business Intelligence in 2024

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps