6 ms·

I went through this trade off at my last job. I started off migrating my adhoc queries to duckdb directly from delta tables. Over time, I used duckdb enough to

by kianN 2y ago

I went through this trade off at my last job. I started off migrating my adhoc queries to duckdb directly from delta tables. Over time, I used duckdb enough to do some performance tuning. I found that migrating from Delta to duckdb's native file format provided substantial speed wins. The author focuses on read/write performance on Delta (makes sense for the scope of the comparison). I think if an engineer is considering switching from spark to duckdb/polars for their data warehouse, they would likely be open to data formats other than Delta, which is tightly coupled to the spark (and even more so to the closed-source Databricks implementation). In my use case, we saw enough speed wins and cost savings that it made sense to fully migrate our data warehouse to a self managed duckdb warehouse using duckdb's native file format.

thenaturalist 2y ago
Thanks for sharing, very intersting! I'm thinking the same wrt dropping Parquet. I don't need concurrent writes, which seems to me about the only true caveat DuckDB would have. Two other questions I am asking myself: 1) Is there a an upper file size limit in duckdb's native format where performance might degrade? 2) Are there significant performance degradations/ hard limits if I want to consolidate from X DuckDB's into a single one by programmatically attaching them all and pulling data in via a large `UNION ALL` query? Or would you use something like Polars to query over N DuckDB's?
- kianN 2y ago
  I can offer my experience with respect to your two questions, though my use case is likely atypical. 1) I haven't personally run into upper size limits to the point of non linear performance degradation. However, some caveats to that are (a) most my files are in the range of 2-10gb with a few topping out near 100gb. (b) I am running a single r6gd metal as the primary interface with this which has 512 gb of ram. So, essentially, any one of my files can fit into ram. Even given that setup, I will mention that I find myself hand tuning queries a lot more than I was with Spark. Since duckdb is meant to be more lightweight the query optimization engine is less robust. 2) I can't speak too much towards this use case. I haven't had any occasion to query across duckdb files. However, I experimented on top of delta lake between duckdb and polars and never really found a true performance case for polars in my (again atypical use case) set of test. But definitely worth doing your own benchmarking on your specific use case :)
  - thenaturalist 2y ago
    Thanks for sharing! :)
RobinL 2y ago
Interesting. So what does that look like on disk? Possibly slightly naively I'm imagining a single massive file?
benjaminwootton 2y ago
To state the obvious, Delta is an open standards format which should be widely supported. Databricks have also bought into Iceberg and will probably lead with that or unify the two in future.
- kianN 2y ago
  There is an open source Delta that is a very good library. This is not the same as Databricks' implementation and there are at times compatability issues. For example, by default if you write a delta table using Databricks' dbr runtimes, that table is not readable by the open source Delta because due to the "deletion vectors" optimization that is only accessible within Databricks. That aside, I was more pointing out that Delta, particularly via a commercial offering, is a data format biased towards Spark in terms of performance, since it is being developed primarily by Databricks as a part of the spark ecosystem. If you are plan to use Delta regardless of your compute engine, it makes perfect sense as a benchmark. However, for certain circumstances, the performance wins could be (in my case was) worth it to switch data formats.
  - mwc360 2y ago
    OSS Delta support deletion vectors. The problem is that the OSS Deltalake (based on Delta-rs) python library does not and this prevents engines like DuckDB and Polars from writing to suck tables. I'm pretty sure DV is in OSS since 3.1

by kianN 2y ago

thenaturalist 2y ago

Thanks for sharing, very intersting! I'm thinking the same wrt dropping Parquet. I don't need concurrent writes, which seems to me about the only true caveat DuckDB would have. Two other questions I am asking myself: 1) Is there a an upper file size limit in duckdb's native format where performance might degrade? 2) Are there significant performance degradations/ hard limits if I want to consolidate from X DuckDB's into a single one by programmatically attaching them all and pulling data in via a large `UNION ALL` query? Or would you use something like Polars to query over N DuckDB's?

kianN 2y ago
I can offer my experience with respect to your two questions, though my use case is likely atypical. 1) I haven't personally run into upper size limits to the point of non linear performance degradation. However, some caveats to that are (a) most my files are in the range of 2-10gb with a few topping out near 100gb. (b) I am running a single r6gd metal as the primary interface with this which has 512 gb of ram. So, essentially, any one of my files can fit into ram. Even given that setup, I will mention that I find myself hand tuning queries a lot more than I was with Spark. Since duckdb is meant to be more lightweight the query optimization engine is less robust. 2) I can't speak too much towards this use case. I haven't had any occasion to query across duckdb files. However, I experimented on top of delta lake between duckdb and polars and never really found a true performance case for polars in my (again atypical use case) set of test. But definitely worth doing your own benchmarking on your specific use case :)
- thenaturalist 2y ago
  Thanks for sharing! :)

RobinL 2y ago

Interesting. So what does that look like on disk? Possibly slightly naively I'm imagining a single massive file?

benjaminwootton 2y ago

To state the obvious, Delta is an open standards format which should be widely supported. Databricks have also bought into Iceberg and will probably lead with that or unify the two in future.

kianN 2y ago
There is an open source Delta that is a very good library. This is not the same as Databricks' implementation and there are at times compatability issues. For example, by default if you write a delta table using Databricks' dbr runtimes, that table is not readable by the open source Delta because due to the "deletion vectors" optimization that is only accessible within Databricks. That aside, I was more pointing out that Delta, particularly via a commercial offering, is a data format biased towards Spark in terms of performance, since it is being developed primarily by Databricks as a part of the spark ecosystem. If you are plan to use Delta regardless of your compute engine, it makes perfect sense as a benchmark. However, for certain circumstances, the performance wins could be (in my case was) worth it to switch data formats.
- mwc360 2y ago
  OSS Delta support deletion vectors. The problem is that the OSS Deltalake (based on Delta-rs) python library does not and this prevents engines like DuckDB and Polars from writing to suck tables. I'm pretty sure DV is in OSS since 3.1