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

DuckDB Internals Part 1

greybeam.ai

Post reply on HN
Print view View original

51–60 of 165 posts

Re: DuckDB Internals Part 1

#51

Post by DanielHB »

throwaway7783 wrote: post #12
bdcravens wrote: post #8

Earlier quoted context omitted.

Primarily the ability to work directly with data in its native format (CSV for example) without needing ETL.

How does this work in a production setup? Can this be set up like a server, or is it mostly for individual users to play around with data?

In my previous job (working with electric vehicles) we had a AWS batch job that pulled all data from S3[1] into containers (one container per vehicle) and then push that data into duckdb then run some basic queries and data analysis.

The key thing is that this scaled horizontally pretty much forever, since each vehicle had a fixed amount of data per year we could tightly control the performance characteristics of the analysis. Adding more vehicles didn't make things slower, just linearly more expensive.

I vaguely remember the data from those containers also being used to process some aggregate analysis (like the each vehicle-container would output some data that would be consumed by another job that did aggregates). But I don't remember the specifics.

[1]: I believe we used JSONL or parquet format, but I didn't work in that part of the stack directly

Re: DuckDB Internals Part 1

#52

Post by conradkay »

snissn wrote: post #32

I'm just curious - is duckdb too slow for people? This benchmark from clickhouse shows it being fairly slow compared to some options: https://jsonbench.com/

That's for their `JSON` data types. In DuckDB it's just a string meaning lots of queries will have to do JSON parsing on every row, but the inserts are very fast. Definitely a bit of a footgun and when you actually just need STRUCT or MAP.

There's a talk about ClickHouse's approach from its creator: https://www.youtube.com/watch?v=xHj9mysh0GI , but the gist is that it maintains (sub)columns to store different paths in the JSON

In other ways DuckDB has very good JSON support, like you can do `CREATE TABLE name AS `SELECT * FROM 'data.json';` and it'll infer the schema when possible.

Re: DuckDB Internals Part 1

#53

Post by ai_fry_ur_brain »

tobyhinloopen wrote: post #50

The only reason I know and use DuckDB is because my (internal, private-use-only, experimental) vibe coded projects use it a ton. I didn't pick it - LLMs did. Until this article, I wasn't aware of what it actually is capable of. Most of these projects use JSON(L) files for storage, and duckdb to process them.

What an incredible way to build software

Re: DuckDB Internals Part 1

#54

Post by mootothemax »

tobyhinloopen wrote: post #50

The only reason I know and use DuckDB is because my (internal, private-use-only, experimental) vibe coded projects use it a ton. I didn't pick it - LLMs did. Until this article, I wasn't aware of what it actually is capable of. Most of these projects use JSON(L) files for storage, and duckdb to process them.

If you haven’t investigated storing in parquet format - and it doesn’t break other consumers that need your jsonl formatted files - it could be worth trialling for your use case. You’ll see vastly smaller file sizes (even more so if you use zstd compression), and querying time will shoot up.

Usual caveats apply, but as a general rule it’s held up well for me. Only downside is that inspecting the results moves from vi on the output file to duckdb and a select * from.

Re: DuckDB Internals Part 1

#55

Post by IshKebab »

refactor_master wrote: post #39
holografix wrote: post #24

Why is DuckDB so popular when one can use Python + Pandas? Better perf + SQL is that mostly it?

The better question is, why is DuckDB so popular when one can use Polars which has a sane, lintable, typesafe API compared to the mess that is SQL: WITH lagged AS ( SELECT *, LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time FROM events ), sessions AS ( SELECT *, SUM(COALESCE((date_diff('minute', prev_time, event_time) > 30)::INT, 1)) OVER (PARTITION BY user_id ORDER BY event_time) AS sessi…

That does look nicer if you have a Parquet file and want to analyze it. But DuckDB is also a database - if you want a persistent, reliable and mutable data store I don't think Polars would be suitable would it? (Genuine question - you sound like an expert and I'm not.)

Re: DuckDB Internals Part 1

#56

Post by ndr »

zurfer wrote: post #44
0xferruccio wrote: post #17

DuckDB is amazing for any sort of fast data analysis when the data is small enough that it can fit on your laptop Recently at work I've been using it to analyse the Claude code sessions of every engineer at our company (that we upload to S3) and it's been extremely helpful to help us find gaps in devex and have clear metrics to back up the impact of fixing them Another thing it's been really useful for has been getti…

Like sqlite, duckdb is underappreciated as a production database. You can totally run it on servers or even "serverless" and do some heavy data transformations or with the right server size work with large scale datasets (up to a TB compressed seems fine).

This. I've recently used both duckdb and sqlite to power a dashboard for a small restaurant of a family member. It converts all their sales to a very tiny parquet files, daily.

The file fits in memory and can do all sort of computation in the browser itself. The backend is extremely simple, it just loads the JS and serves the parquet files.

It was also trivial to let the owner do their own queries, just give the schema to an LLM and let it use the charting library, no data hallucinations. If they need it in the dashboard they can either use that one or ask me to review that query.

To be honest, given how simple some things became, it's been really fun to work on.

Re: DuckDB Internals Part 1

#58

Post by creatorpilot »

0xferruccio wrote: post #17

DuckDB is amazing for any sort of fast data analysis when the data is small enough that it can fit on your laptop Recently at work I've been using it to analyse the Claude code sessions of every engineer at our company (that we upload to S3) and it's been extremely helpful to help us find gaps in devex and have clear metrics to back up the impact of fixing them Another thing it's been really useful for has been getti…

[flagged]

Re: DuckDB Internals Part 1

#59

Post by mcv »

Is everything becoming columnar? Parquet stores data per column instead of per row because it improves compression. I get that. Arrow apparently is columnar, and now DuckDB also gets its efficiency by treating data as columns instead of rows?

I still need to wrap my head around how that works, but it's a fascinating development.

Post reply on HN