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

21–30 of 40 posts

Re: DuckDB Isn't Just Fast

#21

Post by sethammons »

belter wrote: post #12
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.

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,…

> 7100 rows

For analytic queries, I don't feel that is anywhere near what I've seen at multiple companies who gave opted for columnar storage. That would be at most a few seconds of incoming data.

With so few rows, I would not be surprised if you could use standard command line tools to get the same results from a text file of similar size in an acceptable time.

Re: DuckDB Isn't Just Fast

#22

Post by belter »

sethammons wrote: post #21
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,…

> 7100 rows For analytic queries, I don't feel that is anywhere near what I've seen at multiple companies who gave opted for columnar storage. That would be at most a few seconds of incoming data. With so few rows, I would not be surprised if you could use standard command line tools to get the same results from a text file of similar size in an acceptable time.

Certainly not. We typically would talk about hundreds of millions to billions of rows.

Just trying to reproduce the use case...Also tried with the Parquet file from the support ticket. Around 90,000 rows...

Re: DuckDB Isn't Just Fast

#23

Post by sirfz »

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.

In general DuckDB is great but I had similar out-of-memory issues specifically when using "distinct on" and "unnest". In such cases I usually delegate some queries to chdb / clickhouse-local instead.

DuckDB has been evolving nicely, I especially like their SQL dialect (things like list(column1 order by column2), columns regex / replace / etc) and trust they'll eventually resolve the memory issues (hopefully).

Re: DuckDB Isn't Just Fast

#24

Post by LunaSea »

belter wrote: post #12
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.

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 very easily be optimised to take little amounts of space (hash the DISTINCT ON key, and replace in-place the already seen values if the value of "a" is larger than the one that already exists.)

Re: DuckDB Isn't Just Fast

#26

Post by uptime »

observablehq.com has built in support for duckdb, and I have found it to be very easy to use. Getting windowing and cte and derived columns is great and being able to just refer to sql query cells as an array of rows makes things much easier for me than breaking out into js right away.

Someone wrote an export function, so I can make a select into a table and grab that as csv to use elsewhere.

I wish for Simon Willison to adopt duckdb as he has with sqlite to see what he would create!

Re: DuckDB Isn't Just Fast

#27

Post by koromak »

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 containers reading from a segmented dataset in EFS is a really solid solution, you can get sub second performance over 6 billion rows / 10000 columns with proper management and reasonably restrictive queries.

Another option is to just deploy a couple huge EC2 instances that can fully fit the dataset. Costs here were about the same, but with a little more pain in server management. But the speed man, its just unbelievable.

Re: DuckDB Isn't Just Fast

#28

Post by koromak »

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…

To note, we migrated from Redshfit, which had 7-30 second performance. Our current managed solution is something like 1 - 5. Duckdb just smashes everything else, at least on our data.

Re: DuckDB Isn't Just Fast

#29

Post by belter »

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…

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
  duckdb    10963.74
  duckdb     8586.76
  duckdb     7613.86
  duckdb     6749.53
  duckdb     5990.96
  duckdb     5293.35
  duckdb     4205.53
  duckdb     3153.59
  duckdb     1482.86
  duckdb      386.29 -> End Query
So yes, there are some opportunities for optimization here :-)

Re: DuckDB Isn't Just Fast

#30

Post by [deleted] »

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.

[deleted]
Post reply on HN