Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

DuckDB over Pandas/Polars

pgrs.net

Post reply on HN
Print view View original

21–30 of 43 posts

Re: DuckDB over Pandas/Polars

#21

Post by bbkane »

halfcat wrote: post #17
lopatin wrote: post #9

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.

I really like the dataframe approach. I think it’s because I like REPL-driven-development where I can drop into the REPL and work through how to transform the data interactively. To be fair, it can nearly always be done in SQL also (unless it’s ML or some Python-specific thing like that), but the SQL with nested queries and numerous CTEs is harder for me to wrap my brain around. If I were betting, I’d pick DuckDB, be…

I'm with you. I also like the IDE niceties like autocomplete and docs on hover that don't really work on SQL

Re: DuckDB over Pandas/Polars

#23

Post by wenc »

bbkane wrote: post #21
halfcat wrote: post #17

Earlier quoted context omitted.

I really like the dataframe approach. I think it’s because I like REPL-driven-development where I can drop into the REPL and work through how to transform the data interactively. To be fair, it can nearly always be done in SQL also (unless it’s ML or some Python-specific thing like that), but the SQL with nested queries and numerous CTEs is harder for me to wrap my brain around. If I were betting, I’d pick DuckDB, be…

I'm with you. I also like the IDE niceties like autocomplete and docs on hover that don't really work on SQL

I'm hoping someone writes a Python LSP that understands DuckDB SQL.

I use DuckDB and I typically write correct SQL, but having LSP assistance would greatly enhance my quality of life.

Re: DuckDB over Pandas/Polars

#24

Post by wenc »

ramraj07 wrote: post #15
nomilk wrote: post #6

Earlier quoted context omitted.

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.

Local drives. DONT USE EBS! you’ll incur a huge IO charge. You have to choose instances with attached nvme storage which means one of the storage optimized instances. Reading the data off s3 will mean you will be slower than offerings like snowflake. Snowflake has optimized the crap out of doing analytics in s3, so you can’t beat it with something as simple as duckdb. Importantly you need the data in some distributed…

Also, I learned that Hive-partitioned Parquet on S3 is much slower than on disk.

S3 is high latency unless you use for S3 Express Zones (the low latency version).

We used EFS (not EBS) and it was much faster.

Re: DuckDB over Pandas/Polars

#25

Post by wenc »

jgalt212 wrote: post #22

At what database size does it make sense to move from SQLite to DuckDB? My use case is off-line data analysis, not query / response web app.

It's not so much about size but about usage pattern.

If your workloads require fast writes and reads, SQLite will probably work fine.

If you're looking to run analytic, columnar queries (which tend to involve a lot of aggregation and joins on a few columns (say less than 50) at a time), then DuckDB is way more optimized.

Oversimplifying, Sqlite is more OLTP and DuckDB is more OLAP.

Re: DuckDB over Pandas/Polars

#26

Post by ramraj07 »

wenc wrote: post #24
ramraj07 wrote: post #15

Earlier quoted context omitted.

Local drives. DONT USE EBS! you’ll incur a huge IO charge. You have to choose instances with attached nvme storage which means one of the storage optimized instances. Reading the data off s3 will mean you will be slower than offerings like snowflake. Snowflake has optimized the crap out of doing analytics in s3, so you can’t beat it with something as simple as duckdb. Importantly you need the data in some distributed…

Also, I learned that Hive-partitioned Parquet on S3 is much slower than on disk. S3 is high latency unless you use for S3 Express Zones (the low latency version). We used EFS (not EBS) and it was much faster.

Test out the nvme drives though. It’s blazing.

Re: DuckDB over Pandas/Polars

#27

Post by Incipient »

swasheck wrote: post #13
nomilk wrote: post #6

Earlier quoted context omitted.

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.

we use partitioned parquet files in s3. we use a csv in the bucket root to track the files. i’m sure there’s a better way but for now the 2tb of data are stored cheaply and we get fast reads by only reading the partitions we need to read.

I'm curious how much simpler to build, manage, and run vs cost it would be to simply running a database on a large vultr/DO instance and paying for 2tb of storage?

I feel like you'd get away with the whole thing for around $500/mo depending on how much compute was needed?

Re: DuckDB over Pandas/Polars

#28

Post by wodenokoto »

lopatin wrote: post #9

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.

I'm very split. There's a lot of interactive exploration and data transformations that SQL lends itself to poorly (try transposing in SQL - not fun!) but I really like the idea of data system that is language agnostic like DuckDB

Re: DuckDB over Pandas/Polars

#29

Post by ramraj07 »

Incipient wrote: post #27
swasheck wrote: post #13

Earlier quoted context omitted.

we use partitioned parquet files in s3. we use a csv in the bucket root to track the files. i’m sure there’s a better way but for now the 2tb of data are stored cheaply and we get fast reads by only reading the partitions we need to read.

I'm curious how much simpler to build, manage, and run vs cost it would be to simply running a database on a large vultr/DO instance and paying for 2tb of storage? I feel like you'd get away with the whole thing for around $500/mo depending on how much compute was needed?

You just need to try it once to see the issue. Merely loading this amount of data onto a Postgres db will be hell.

Re: DuckDB over Pandas/Polars

#30

Post by wodenokoto »

> Note that DuckDB automatically figured out how to parse the date column.

It kinda did and it kinda didn't. Author got lucky that Transaction.csv contained a date where the day was after the 12th in a given month. Had there not been such a date, DuckDB would have gotten the dates wrong and read it as dd/mm/yyyy.

I think a warning from DuckDB would have been in order.

Post reply on HN