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.<p>DuckDB will error-out with an out-of-memory exception in very simple DISTINCT ON / GROUP BY queries.<p>Even with a temporary file, an on-disk database and not keeping the initial order.<p>On any version of DuckDB.
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_query - also, DuckDB has no support for handling xml, so all xpath calls fail as well.
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
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.<p>Someone wrote an export function, so I can make a select into a table and grab that as csv to use elsewhere.<p>I wish for Simon Willison to adopt duckdb as he has with sqlite to see what he would create!
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.<p>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.<p>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.