DuckDB over Pandas/Polars

The test case of a simple aggregation is a good example of an important data science skill knowing when and here to use a given tool, and that there is no one right answer for all cases. Although it's worth noting that DuckDB and polars are comparable performance-wise for aggregation (DuckDB slightly faster: https://duckdblabs.github.io/db-benchmark/ ).

For my cases with polars and function piping, certain aspects of that workflow are hard to represent in SQL, and additionally it's easier for iteration/testing on a given aggregation to add/remove a given function pipe, and to relate to existing tables (e.g. filter a table to only IDs present in a different table, which is more algorithmically efficient than a join-then-filter). To do the ETL I tend to do for my data science workin pandas/polars in SQL/DuckDB, it would require chains of CTEs or other shenanigans, which eliminates similicity and efficincy.

Re: DuckDB over Pandas/Polars

#5

Post by ramraj07 » Wed, Nov 06, 2024, 2:02 AM UTC

I am just using duckdb on a 3TB dataset in a beefy ec2, and am pleasantly surprised at its performance on such a large table. I had to do some sharding to be sure but am able to match performance of snowflake or other cluster based systems using this single machine instance.

To clarify Clickhouse will likely match this performance as well, but doing things on a single machines look sexier to me than it ever did in decades.

Re: DuckDB over Pandas/Polars

#6

Post by nomilk » Wed, Nov 06, 2024, 2:10 AM UTC

ramraj07 wrote: post #5

I am just using duckdb on a 3TB dataset in a beefy ec2, and am pleasantly surprised at its performance on such a large table. I had to do some sharding to be sure but am able to match performance of snowflake or other cluster based systems using this single machine instance. To clarify Clickhouse will likely match this performance as well, but doing things on a single machines look sexier to me than it ever did in de…

Where does your data reside, is it on an attached EBS volume, or in S3, or somewhere else?

I had some spare time and tinkered with duckdb with a 70GB dataset, but just getting the 70GB on to the EC2 took hours. Would be pretty rocking if duckdb team could somehow set up a ~1TB sized demo that anyone can setup and try for themselves in, say, under an hour.

Re: DuckDB over Pandas/Polars

#7

Post by Jgrubb » Wed, Nov 06, 2024, 2:11 AM UTC

ramraj07 wrote: post #5

I am just using duckdb on a 3TB dataset in a beefy ec2, and am pleasantly surprised at its performance on such a large table. I had to do some sharding to be sure but am able to match performance of snowflake or other cluster based systems using this single machine instance. To clarify Clickhouse will likely match this performance as well, but doing things on a single machines look sexier to me than it ever did in de…

Huge fan of Clickhouse, but the minute you have to deal with somebody else's CSV is when Duck wins over Clickhouse.

Re: DuckDB over Pandas/Polars

#8

Post by pjot » Wed, Nov 06, 2024, 2:16 AM UTC

riku_iki wrote: post #3

xiaodai wrote: post #2

lack of UDF is an issue

they have UDFs: https://duckdb.org/docs/api/python/function.html

And macros! Which you can overload too

https://duckdb.org/docs/sql/statements/create_macro#overload...

Re: DuckDB over Pandas/Polars

#9

Post by lopatin » Wed, Nov 06, 2024, 2:19 AM UTC

I think the competition for the future is between DuckDB and Polars. Will we stick with the DataFrame model, made feasible by Polars's lazy execution, or will we go with in-process SQL a la DuckDB? Personally I've been using DuckDB because I already know SQL (and DuckDB provides persistence if I need it) and don't want to learn a new DataFrame DSL but I'd love to hear other the experience of other people.

Re: DuckDB over Pandas/Polars

#10

Post by DiscreteTom » Wed, Nov 06, 2024, 2:29 AM UTC

nomilk wrote: post #6

ramraj07 wrote: post #5

I am just using duckdb on a 3TB dataset in a beefy ec2, and am pleasantly surprised at its performance on such a large table. I had to do some sharding to be sure but am able to match performance of snowflake or other cluster based systems using this single machine instance. To clarify Clickhouse will likely match this performance as well, but doing things on a single machines look sexier to me than it ever did in de…

Where does your data reside, is it on an attached EBS volume, or in S3, or somewhere else? I had some spare time and tinkered with duckdb with a 70GB dataset, but just getting the 70GB on to the EC2 took hours. Would be pretty rocking if duckdb team could somehow set up a ~1TB sized demo that anyone can setup and try for themselves in, say, under an hour.

I tried to spread large dataset into thousands of files on S3 and use StepFunctions Distributed Map to launch thousands of Lambda instances to process those files in parallel, using DuckDB (or other libs) in Lambda. The parallel loading and processing is way faster than doing this in a single big EC2 instance.