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

DuckDB – An in-process SQL OLAP database management system

duckdb.org

Post reply on HN
Print view View original

51–60 of 104 posts

Re: DuckDB – An in-process SQL OLAP database management system

#51

Post by chrisjc »

wasd wrote: post #34

Our database is heroku postgresql database. What's the best way to get this working with DuckDB? I see there's a postgresql connector but I'm not totally following how to deploy it. Would I just spin up a dyno with the docker image / custom build pack and connect it to the DB?

Doesn't postgres have a columnar option? If so, you could prob get better performance for your analytical interactions if you switched some tables to columnar.

Otherwise check out postgres scanner. https://github.com/duckdblabs/postgres_scanner

They have a blog entry about it too: https://motherduck.com/blog/duckdb-ecosystem-newsletter-two/

Re: DuckDB – An in-process SQL OLAP database management system

#52

Post by tracker1 »

jakewins wrote: post #18
RobinL wrote: post #13

Earlier quoted context omitted.

I'd love to hear any real world experiences of anyone who's tried to run jobs that would usually require a spark cluster on a single machine with loads of cores and memory. How big can you go, and how does speed compare to Spark? (I'm guessing significantly faster from my experience using Duckdb on smaller machines)

I used it as glue for a monthly job, last week, processing ~1B event records, juggling them a bit and pushing them as 2M parquet segment files to S3. Mixed experience, would definitely not put it in a system that isn’t ok crashing frequently. It segfaults on Alpine (argh, C++!), and force exits the whole NodeJS process when it gets unexpected HTTP responses from S3. In an archive run of 2M pushes it’ll crash the proc…

Node + AWS for data ingress has been pretty painful in my experience (mostly dynamo feeds from large csv (whois database)). In the end, rewrote in C# (core 2) and it was able to complete more reliably. I'm guessing that go and rust would also be better. I like node, really like JS, but I just think that maybe the AWS libraries aren't that great in the space. It would run for 3-5 hours, then just blow up unexpectedly, even with plenty of memory overhead, and not really bandwidth limited, with appropriate retries and slowdown for dynamo rejections.

If I never have to write ETL pipelines again, I won't be upset about it.

Re: DuckDB – An in-process SQL OLAP database management system

#53

Post by cdavid »

__mharrison__ wrote: post #50

Not trying to troll, but assuming proficiency in python, when would someone prefer this to say Pandas (or Polars)? I've written a lot of OLAP queries (wrote a materialization layer for MonetDb and Postgres years ago). I find Pandas so much easier to work with for semi complicated work.

The obvious one is speed/data size: you can handle much larger data with duckdb compared to pandas. Depending on the data, maybe 10x larger, sometimes even more.

From an ergonomics perspective, I find pandas much harder to use casually than SQL. When I was an IC and was using it a lot, I was proefficient in it. But now that I code maybe 5 hours / month at work, I can't really do anything non trivial besides basic stuff/pivots. OTOH, I never really forget SQL.

Re: DuckDB – An in-process SQL OLAP database management system

#54

Post by epberry »

We just switched to DuckDB from Postgres to analyze AWS billing data and wrote up our experience, https://www.vantage.sh/blog/querying-aws-cost-data-duckdb

Arguably Postgres was never the right tool to use for this analysis but nonetheless I was surprised at how much faster DuckDB was.

Re: DuckDB – An in-process SQL OLAP database management system

#55

Post by benjaminwootton »

SnowflakeOnIce wrote: post #12

DuckDB is terrific. I'm bullish on its potential for simplifying many big data pipelines. Particularly, it's plausible that DuckDB + Parquet could be used on a large SMP machine (32+ cores and 128GB+ memory) to deal with data munging for 100s of gigabytes to several terabytes, all from SQL, without dealing with Hadoop, Spark, Ray, etc. I have successfully used DuckDB like above for preparing an ML dataset from about…

DuckDB is a relational OLAP store. If you want to do transformations on relational data using SQL then I think nowadays you would look at the modern data stack and do it with DBT.

If you have genuinely big and unstructured data then of course you need a cluster and would reach for Spark.

If you have smallish data then maybe DuckDB has a role because working with SQL is nicer than Pandas. But a lot of time you actually need the complexity of Pandas to do the transformation you need.

DuckDB is neat but I still can’t quite convince myself of a killer use case.

Re: DuckDB – An in-process SQL OLAP database management system

#56

Post by benjaminwootton »

nerdponx wrote: post #22
SnowflakeOnIce wrote: post #12

DuckDB is terrific. I'm bullish on its potential for simplifying many big data pipelines. Particularly, it's plausible that DuckDB + Parquet could be used on a large SMP machine (32+ cores and 128GB+ memory) to deal with data munging for 100s of gigabytes to several terabytes, all from SQL, without dealing with Hadoop, Spark, Ray, etc. I have successfully used DuckDB like above for preparing an ML dataset from about…

As far as I can tell, DuckDB is an alternative to "data frame" libraries like Data.table, Polars, Pandas, etc. Is that the case? What makes DuckDB a better choice than, say, Polars?

SQL is easier and more natural to work with than Pandas.

Re: DuckDB – An in-process SQL OLAP database management system

#57

Post by 0cf8612b2e1e »

krimpenrik wrote: post #47
SnowflakeOnIce wrote: post #12

DuckDB is terrific. I'm bullish on its potential for simplifying many big data pipelines. Particularly, it's plausible that DuckDB + Parquet could be used on a large SMP machine (32+ cores and 128GB+ memory) to deal with data munging for 100s of gigabytes to several terabytes, all from SQL, without dealing with Hadoop, Spark, Ray, etc. I have successfully used DuckDB like above for preparing an ML dataset from about…

Recently tried the GUI tool for ducks, forgot what's it called, something like 'Tab' and was quite disappointed. I feel duckdb needs a good tool like sqliteviewer to really take off.

DBeaver supports duckdb.

Re: DuckDB – An in-process SQL OLAP database management system

#58

Post by mcdonje »

kiratp wrote: post #30

Its disappointing that C++ was chosen to build something that is going to live in-process. Rust would have been so much safer. All the segfaults you get when running DucDB supports this statement.

I agree about Rust's memory safety advantage over C++, but I disagree that it's disappointing from a project perspective. Some DB experts made a good DB using a performant language they're comfortable with.

You can't make project choices in a vacuum, and you can't assume others can either. People have limited time. The choice they were facing was probably not C++ vs Rust, but C++ vs nothing because they didn't have time to learn a new language before starting their project.

Also, their first release was in 2019, so they probably heard of it, but that's around the beginning of its recent spike in popularity. It's starting to be viewed as a good long term option, but back then a lot of people were still wondering if it was a fad.

I'm learning Rust, and I'm a big fan, but this is a bad take.

Re: DuckDB – An in-process SQL OLAP database management system

#59

Post by 89vision »

benjaminwootton wrote: post #55
SnowflakeOnIce wrote: post #12

DuckDB is terrific. I'm bullish on its potential for simplifying many big data pipelines. Particularly, it's plausible that DuckDB + Parquet could be used on a large SMP machine (32+ cores and 128GB+ memory) to deal with data munging for 100s of gigabytes to several terabytes, all from SQL, without dealing with Hadoop, Spark, Ray, etc. I have successfully used DuckDB like above for preparing an ML dataset from about…

DuckDB is a relational OLAP store. If you want to do transformations on relational data using SQL then I think nowadays you would look at the modern data stack and do it with DBT. If you have genuinely big and unstructured data then of course you need a cluster and would reach for Spark. If you have smallish data then maybe DuckDB has a role because working with SQL is nicer than Pandas. But a lot of time you actuall…

> working with SQL is nicer than Pandas

Really? I prefer working with dataframe apis. You get a nice sql-like paradigm plus all the control structures of the runtime.

Re: DuckDB – An in-process SQL OLAP database management system

#60

Post by Master_Odin »

tracker1 wrote: post #52
jakewins wrote: post #18

Earlier quoted context omitted.

I used it as glue for a monthly job, last week, processing ~1B event records, juggling them a bit and pushing them as 2M parquet segment files to S3. Mixed experience, would definitely not put it in a system that isn’t ok crashing frequently. It segfaults on Alpine (argh, C++!), and force exits the whole NodeJS process when it gets unexpected HTTP responses from S3. In an archive run of 2M pushes it’ll crash the proc…

Node + AWS for data ingress has been pretty painful in my experience (mostly dynamo feeds from large csv (whois database)). In the end, rewrote in C# (core 2) and it was able to complete more reliably. I'm guessing that go and rust would also be better. I like node, really like JS, but I just think that maybe the AWS libraries aren't that great in the space. It would run for 3-5 hours, then just blow up unexpectedly,…

There was a bug that was recently fixed in node 16.17+ that was causing hard node processes crashes when doing stuff with S3 and I think had to do with receiving multiple packets at once or something.
Post reply on HN