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

DuckDB Isn't Just Fast

csvbase.com

Post reply on HN
Print view View original

31–40 of 40 posts

Re: DuckDB Isn't Just Fast

#31

Post by wenc »

dmw_ng wrote: post #18
LunaSea wrote: post #4

I know I'm repeating my self (it must be my third comment on HN about this topic), but this does not match my experience at all. DuckDB will error-out with an out-of-memory exception in very simple DISTINCT ON / GROUP BY queries. Even with a temporary file, an on-disk database and not keeping the initial order. On any version of DuckDB.

I've had similar times with DuckDB, it feels nicer to use on the surface but in terms of perf and actual function I've had a better experience with clickhouse-local.

Are you using it for simple SQL retrieval or complex analytic queries? They’re both similar for the former use case, but DuckDB — being an analytic engine — supports the latter use case much better.

Re: DuckDB Isn't Just Fast

#32

Post by wenc »

LunaSea wrote: post #24
belter wrote: post #12

Earlier quoted context omitted.

Curious about your post, and DuckDB, since have seen many previous post here on HN about it...I just took 10 min to do some quick tests, and experiment with DuckDB for the first time. :-) While I am a Linux user, tried this on a available Windows 10 machine (I know...Yuck!) 1) Setup and install no prob. Tracked the duckdb process memory usage with PowerShell, like this: Get-Process -Name duckdb | Select-Object Name,…

Quick example: ``` COPY ( SELECT DISTINCT ON (b, c) * FROM READ_PARQUET('input.parquet') ORDER BY a DESC ) TO 'output.parquet' ( FORMAT PARQUET, COMPRESSION 'ZSTD' ) ; ``` Where the input file has 25M rows (500Mb in Parquet format) containing 4 columns, a and b are BIGINTs and c and d are VARCHARs. On a Mac Book Pro M1 with 8GB of RAM (16x the original file size), the query will not finish. This is a query that could…

What is the error message you’re getting? Or is it simply that the query will not finish? (Does it continue to run?)

Parquet files are compressed, and many analytic operations require more memory than the on disk size. When you don’t have enough ram DuckDb has to switch to out of core mode which is slower. (It’s the classic performance trade off)

8gb of ram is not enough usually to expect performance from analytic operations - I usually have minimum 16. My current instance is remote which has 256gb ram. I never run out of ram and DuckDb never fails and runs super fast.

Re: DuckDB Isn't Just Fast

#33

Post by mritchie712 »

koromak wrote: post #27

I very, very nearly migrated to a full Duckdb solution for customer-facing historical stock data. It would have been magical, and ridiculously, absurdly, ungodly fast. But the cloud costs ended up being close to a managed analytics solution, with significantly more moving parts (on our end). But I think thats just our use case, going forward I'd look at duckdb as an option for any large-scale datasets. Using ECS/EKS…

Did you check the cost to run it on Motherduck (https://motherduck.com/)?

Re: DuckDB Isn't Just Fast

#34

Post by sagarm »

dgan wrote: post #2

Really, is this what's getting praised? I mean specifically the first point: the whole "just paste the url into the DB" - thing, + inferring the column names. That looks like the laziest and shakiest basis, and if I ever saw that in production i d be both stunned and scared

It would be perfectly reasonable IMO to put your metadata elsewhere and still use DuckDB for processing.

Re: DuckDB Isn't Just Fast

#35

Post by LunaSea »

belter wrote: post #29
LunaSea wrote: post #24

Earlier quoted context omitted.

Quick example: ``` COPY ( SELECT DISTINCT ON (b, c) * FROM READ_PARQUET('input.parquet') ORDER BY a DESC ) TO 'output.parquet' ( FORMAT PARQUET, COMPRESSION 'ZSTD' ) ; ``` Where the input file has 25M rows (500Mb in Parquet format) containing 4 columns, a and b are BIGINTs and c and d are VARCHARs. On a Mac Book Pro M1 with 8GB of RAM (16x the original file size), the query will not finish. This is a query that could…

With the information in your example... I created a parquet file with 50 million rows, random data, same data types. Parquet file is 391 MB on disk (NTFS) Query will complete, but in aprox 3,5 to 4 min, you will need up to 14 GB of memory. (4 Core, Win10, 32GB RAM). You can see below, memory usage in MB, throughout the query, sampled at 15 sec interval. duckdb 321.01 -> Start Query duckdb 6302.12 duckdb 13918.04 duck…

Thanks for the benchmarks! :)

Indeed, 14GB seems really high for a 400MB Parquet file, that's a 35x multiple on the base file size.

Of course, the data is compressed on disk, but even the uncompressed data isn't that large so I believe indeed that quite a lot of optimisations are still possible.

Re: DuckDB Isn't Just Fast

#36

Post by PartiallyTyped »

belter wrote: post #16
Woshiwuja wrote: post #13

Earlier quoted context omitted.

Ty! Never got into the analytics side of things

Have a look at this section of the AWS Redshift documentation (also a columnar database) to understand the advantages of these types of systems: https://docs.aws.amazon.com/redshift/latest/dg/c_columnar_st... Or the advantage of columnar file formats, like ORC or Parquet, for analytical queries. Normally you are only interested in a few columns.

Columnar formats allow for some pretty interesting optimizations with respect to filtering by rewriting and pushing down constraints / checks; and evaluating multiple of them simultaneously, pulling as little as necessary.

Re: DuckDB Isn't Just Fast

#37

Post by wenc »

LunaSea wrote: post #35
belter wrote: post #29

Earlier quoted context omitted.

With the information in your example... I created a parquet file with 50 million rows, random data, same data types. Parquet file is 391 MB on disk (NTFS) Query will complete, but in aprox 3,5 to 4 min, you will need up to 14 GB of memory. (4 Core, Win10, 32GB RAM). You can see below, memory usage in MB, throughout the query, sampled at 15 sec interval. duckdb 321.01 -> Start Query duckdb 6302.12 duckdb 13918.04 duck…

Thanks for the benchmarks! :) Indeed, 14GB seems really high for a 400MB Parquet file, that's a 35x multiple on the base file size. Of course, the data is compressed on disk, but even the uncompressed data isn't that large so I believe indeed that quite a lot of optimisations are still possible.

It’s also the aggregation operation. If there are many unique groups it can take a lot of memory.

Newer DuckDbs are able to handle out of core operations better. But in general just because data fits in memory doesn’t mean the operation will — and as I said 8GB is very limited memory so it will entail spilling to disk.

https://duckdb.org/2024/03/29/external-aggregation.html

Re: DuckDB Isn't Just Fast

#38

Post by vgt »

koromak wrote: post #27

I very, very nearly migrated to a full Duckdb solution for customer-facing historical stock data. It would have been magical, and ridiculously, absurdly, ungodly fast. But the cloud costs ended up being close to a managed analytics solution, with significantly more moving parts (on our end). But I think thats just our use case, going forward I'd look at duckdb as an option for any large-scale datasets. Using ECS/EKS…

Co-founder and head of produck at MotherDuck here - would love to chat. We're running DuckDB in a serverless fashion, so you're only paying for what you consume.

Feel free to reach out to tino@motherduck.com.

Re: DuckDB Isn't Just Fast

#39

Post by felipemesquita »

RobinL wrote: post #20
felipemesquita wrote: post #9

DuckDB has great ergonomics for moving data between different databases and making copies for local analysis. The one thing that differed in my experience with it from the author’s is how much of the Postgres sql dialect (and extensions) it supports. Attempting to run my Postgres analytics sql code in duckdb errors out on most json operations - to be fair, the DuckDB json functions have cleaner names than jsonb_path_…

You may know this already but the postgres extension[1] may help: If I understand it correctly, when you use it it: - Pulls the minimal data required (inferred from the query) from postgres into duckdb - Executes your query using duckdb execution engine BUT, if your postgres function is not supported by DuckDB I think you can use the `postgres_execute` [2] to execute the function within postgres itself I'm not sure w…

Thanks for the suggestion! As I understand, you can only postgres_execute against a running Postgres db. It does work and I’ve used it in my tests, I think I could get around the limitations that I ran into by running a pg instance alongside DuckDB. For now I think I’ll stick with just pg, as I was looking into DuckDB to replace pg in my local analytic workloads: load data from rest apis, dump into a database and use sql in a custom dbt-like pipeline to build the tables for analysis in bi tools. Unfortunately, many endpoints return xml data and much of the sql I’ve already written deals with json, meaning it would have to be adapted to work with DuckDB.

Re: DuckDB Isn't Just Fast

#40

Post by panyang »

koromak wrote: post #27

I very, very nearly migrated to a full Duckdb solution for customer-facing historical stock data. It would have been magical, and ridiculously, absurdly, ungodly fast. But the cloud costs ended up being close to a managed analytics solution, with significantly more moving parts (on our end). But I think thats just our use case, going forward I'd look at duckdb as an option for any large-scale datasets. Using ECS/EKS…

What do you mean by segmented dataset in EFS?
Post reply on HN