1.1B Taxi Rides Benchmark Using DuckDB | Hacker News Reader

1.1B Taxi Rides Benchmark Using DuckDB

tech.marksblogg.com

36 pointspella16 comments

I've played with (part of) this dataset too, or at least one like it. The CSV structure looks familiar. Note that while the actual CSV files are ~500GB, a lot of that is wasted bytes on stuff like addresses in which a lot of information is repeated as well as long dates/identifiers/numbers/coordinates being written out as strings.

If you write some custom software for it that uses binary representations and maps repetitive information to shorter IDs, you should be able to fit the entire dataset into 64GB of RAM (less than 60 bytes / row). At that point running most analytical queries on it becomes really quick even if you do not care about indexing the main data and just rely on tools like MapReduce. You could probably dump that representation into an mmap'ed file on a SSD and get similar results even with less RAM.

Should you still use a DB? Probably. But if your DB is a bigger hassle and significantly slower than one machine running some wonky code you wrote in an afternoon, it probably sucks. Now after reading the article I'm still not 100% sure whether the initial issue with DuckDB was performance or data related. If it's the former, then any DB running on modern hardware that starts choking handling a million anything doesn't deserve to be called a database in this day and age - at that point Microsoft Excel is your main competitor and offers a better user experience.

There’s a lot of DuckDB activity this week. Especially those using its WASM version inside browser. Observable Framework that was published month ago added DuckDB SQL support last week and a cross filtering visualization Mosaic library this week. Here they show one million taxi rides in their docs: https://observablehq.com/framework/lib/mosaic

One billion rides might just be too much for browser based DuckDB. Perhaps one could select only a few months of data. If split into smaller files DuckDB should be able to read only relevant parts.

I had the same issue with ingesting lots of csv files in a previous version (8.x), though it seemed to be hardware dependent; issue was only on windows not Ubuntu. I think setting the number of threads to low value helped. I had hoped these issues would be solved by v 0.10 but I am nevertheless a big fan of duckdb.

I tried DuckDb once some months ago after reading that it can handle json files. I don’t remember the exact failure details, but it crapped out parsing a json file only few MB large, with some buffer size errors. What’s the overall opinion of duckdb here?

DuckDB is ok at JSON data but it hasn’t worked around all the corner cases like Pandas has. Converting from a hierarchical format like JSON to a tabular format is always fraught. I find Pandas to be slightly more robust and faster.

However for any kind of tabular data, DuckDB blows everything out of the water. I’ve mostly been using it with Parquet and TSV files and the performance is just unbelievable. SQL that even Redshift struggles with can be done in DuckDB in no time. If the data is parquet, there is no need to reach for Spark. DuckDB is just way faster.

The level of SQL supported by duckdb is also outstanding, from pivot/unpivot, and friendly SQL constructs like group by all, order by all, it makes writing complicated SQL a pleasure.

I’m having the same issues, where I need to load json files one by one instead of loading them in a batch. It looks like memory is not freed as soon as the file is parsed.

Edit: setting threads to a low value as read in this thread solved my issue.

I'm unclear why this article is flagged. Mark's been doing these articles for years. This one is straight-up tech, like most of his posts.

It would be interesting to see the performance of protobuf vs Parquet in this case...not sure if DuckDB supports that...

Not yet, but I believe that Click house does.

Avro would be another contender in the space.

Parquet is a columnar format with columnar compression. With metadata and indexes. In many cases it needs to read a tiny fraction of Parquet file. Protobuf has zero chances.

How is query performance on DuckDB’s native format compared to parquet?

I wrote a page about this: https://duckdb.org/docs/guides/performance/file_formats

TL;DR: if your workload is aggregation-heavy, Parquet is pretty good. If you have many joins, the lack of fine-grained statistics will likely result in bad join plans.

(Disclaimer: I work at DuckDB Labs)

Nice summary how some development progresses. Sometimes it’s struggle after struggle after struggle.

It's about "how fast DuckDB can run the 1.1 billion taxi rides benchmark", with the bulk of the article dedicated to importing the data. It's not about use in production for schedule 1B rides or storing the records associated with these rides.

That is in part because DuckDB would not be used in an application supporting multiple concurrent clients.

It’s a desktop / single process analysis tool, not a replacement for what a ride sharing app would use for ride scheduling.

Ok, we've stuffed a 'benchmark' in the title above.

Reply on news.ycombinator.com