DuckDB Isn't Just Fast

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, @{Name="Memory (MB)";Expression={[math]::round($_.WorkingSet64/1MB,2)}}

Section in the blog: "FIRST ANALYTICS PROJECT"

Table has 7100 rows as seen like this:

   SELECT COUNT(*) AS RowCount FROM netflix;

   The one below, being one example:

    SELECT DISTINCT ON (Title) 
      Title, 
      "As of", 
      Rank 
    FROM netflix 
    ORDER BY Title, "As of" DESC;

I also tested, with the parquet file from the thread mentioned below by mgt19937. It is 89475 rows. Did some complex DISTINCT ON / GROUP BY on it, without seeing neither explosive memory use or something similar to a memory leak.

Do you have a more specific example?

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).

Thanks for repeating yourself; this comment potentially influences my decision-making about DuckDB. Good to know about the negatives too.

At what data volumes does it start erroring out? Are these volumes larger than RAM? Is there a minimal example to reproduce it? Is this ticket related to your issue? https://github.com/duckdb/duckdb/issues/12480

In my experience, recent versions work well for out-of-core computation (I use it frequently). What is the size of the Parquet file that you are using? - I can't find the details in you previous comments.

Sounds interesting. Is there a open issue for this? I found https://github.com/duckdb/duckdb/issues/8505 but it seems that that specific issue is closed.

Sounds like the very early versions of EF Core which did not translate C# GroupBy into SQL GROUP BY, but instead loaded the /entire/ data set into memory and did the aggregate there.

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.

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 whether you can e.g do a CTE pipeline that starts with postgres_execute, and then executes Duckdb sql in later stages of the pipeline

[1] https://duckdb.org/docs/extensions/postgres.html#running-sql... [2]https://duckdb.org/docs/extensions/postgres.html#the-postgre...

It is a really useful feature for ad hoc examination of datasets. Not every data-analysis is going into a massive, mission critical production build chain. Quite often you just want to look at the data in a REPL and IMO that sort of usage could bear to get a little easier

Inference for column names and datatypes seems only relevant for csv? Other formats convey that metadata, as e.g. parquet...

csv metadata inference in duckdb is amazing. There is some research in this domain and duckdb does a great job there, but yes there might be some really strange csv files, which require manual intervention.

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

What's wrong with the URL in the query?

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.

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.

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

What do you mean by segmented dataset in EFS?

DuckDB is optimised for analytical tasks, whereas SQLite isn't.