14 ms·

Why DuckDB is my first choice for data processing

310 points by tosh 10mo ago

tobilg 10mo ago
Built with DuckDB-Wasm: SQL Terminal in the browser https://terminal.sql-workbench.com https://terminal.sql-workbench.com
DangitBobby 10mo ago
Being able to use SQL on CSV and json/jsonl files is pretty sweet. Of course it does much more than that, but that's what I do most often with it. Love duckdb.
- samuell 10mo ago
  Indeed! I generally like awk a lot for simpler CSV/TSV processing, but when it comes to cases where you need things like combining/joining multiple CSV files or aggregating for certain columns, SQL really shines IME.
oulu2006 10mo ago
That's really interesting, I love the idea of being able to use columnar support directly within postgresql. I was thinking of using Citus for this, but possibly using duckdb is a better way to do. Citus comes with a lot more out of the box but duckdb could be a good stepping stone.
- biophysboy 10mo ago
  Its a really handy tool. I've queried basically everything you can w/ duckdb - csv, json, s3 buckets, MS SQL servers, excel sheets, pandas dataframes, etc - and have had very few issues.
clumsysmurf 10mo ago
DuckDB has experimental builds for Android ... I'm wondering how much work it would take to implement a Java API for it similar to sqlite (Cursor, etc).
- jtbaker 10mo ago
  Something different than https://duckdb.org/docs/stable/clients/java https://duckdb.org/docs/stable/clients/java?
  - clumsysmurf 10mo ago
    Android doesn't use JDBC.
- deleted 10mo ago
  [deleted]
smithclay 10mo ago
Agree with the author, will add: duckdb is an extremely compelling choice if you’re a developer and want to embed analytics in your app (which can also run in a web browser with wasm!) Think this opens up a lot of interesting possibilities like more powerful analytics notebooks like marimo (https://marimo.io/ https://marimo.io/) … and that’s just one example of many.
- canadiantim 10mo ago
  The wasm is pretty heavy data-wise tho, I’m hoping eventually it’ll be lighter for easier loading on not so good devices.
  - efromvt 10mo ago
    I'd really love a minimalist version, I'm not sure how small it's feasible for them to shrink it. As long as it doesn't get bigger and devices keep getting faster, I suppose?
- dimitrieh 10mo ago
  100% We recently created a survey website for the community survey results for Node-RED making it completely dynamic and segment-able. Creates lots of value and allows everyone to look at the data through their own lens. It uses DuckDB with WASM under the hood. Awesome technologies. https://nodered.org/community-survey/ https://nodered.org/community-survey/
tjchear 10mo ago
I’ve not used duckdb before nor do I do much data analysis so I am curious about this one aspect of processing medium sized json/csv with it: the data are not indexed, so any non-trivial query would require a full scan. Is duckdb so fast that this is never really a problem for most folks?
- biophysboy 10mo ago
  Zonemaps are created for columns automatically. I process somewhat large tables w/ duckdb regularly (100M rows) and never have any problems.
  - riku_iki 10mo ago
    that's true for duckdb native tables, but the question was about json.
    - biophysboy 10mo ago
      They said json and csv - it handles both!
      - riku_iki 10mo ago
        handles depends on size. But I tried to say there is no zonemaps for json.
- akhundelar 10mo ago
  Not a duckdb user, but I use polars a lot (mentioned in the article). Depends on your definition of medium sized, but for tables of hundreds of thousands of rows and ~30 columns, these tools are fast enough to run queries instantly or near instantly even on laptop CPUs.
- mpalmer 10mo ago
  I guess the question is: how much is medium? DuckDB can handle quite a lot of data without breaking a sweat. Certainly if you prefer writing SQL for certain things, it's a no-brainer.
biophysboy 10mo ago
I think my favorite part of duckdb is its flexibility. Its such a handly little swiss army knife for doing analytical processing in scientific environments (messy data w/ many formats).
s-a-p 10mo ago
"making DuckDB potentially a suitable replacement for lakehouse formats such as Iceberg or Delta lake for medium scale data" > I'm a Data Engineering noob, but DuckDB alone doesn't do metadata & catalog management, which is why they've also introduce DuckLake. Related question, curious as to your experience with DuckLake if you've used it. I'm currently setting up s3 + Iceberg + duckDB for my company (startup) and was wondering what to pick between Iceberg and DuckLake.
- biophysboy 10mo ago
  DuckLake is pretty new, so I guess it would depend on if you need a more mature, fully-featured app.
  - pattar 10mo ago
    I went to a talk by the Motherduck team about why they built DuckLake instead of leaning more in on Iceberg. The key takeaway is that instead of storing all the table metadata inside files on s3 and dealing with latency and file io they instead store all of that info inside a duckdb table. Seems like a good idea and worked smoothly when I tried, however it is not quite in a stable production state it is still <1.0. They have a nice talk about it on youtube: https://youtu.be/hrTjvvwhHEQ?si=WaT-rclQHBxnc9qV https://youtu.be/hrTjvvwhHEQ?si=WaT-rclQHBxnc9qV
    - willvarfar 10mo ago
      (I work a lot with BigQuery's BigLake adaptor and it's basically caching the metadata of the iceberg manifest and parquet footers in Bigtable (this is Google) so query planning is super fast etc. Really helps)
- nchagnet 10mo ago
  We're using ducklake with data storage on Google cloud storage and the catalog inside a postgres database and it's a breeze! It may not be the most mature product, but it's definitely a good setup for small to medium applications which still require a data lake.
noo_u 10mo ago
I'd say the author's thoughts are valid for basic data processing. Outside of that, most of claims in this article, such as: "We're moving towards a simpler world where most tabular data can be processed on a single large machine1 and the era of clusters is coming to an end for all but the largest datasets." become very debatable. Depending on how you want to pivot/ scale/augment your data, even datasets that seemingly "fit" on large boxes will quickly OOM you. The author also has another article where they claim that: "SQL should be the first option considered for new data engineering work. It’s robust, fast, future-proof and testable. With a bit of care, it’s clear and readable." (over polars/pandas etc) This does not map to my experience at all, outside of the realm of nicely parsed datasets that don't require too much complicated analysis or augmentation.
- hnthrowaway0315 10mo ago
  SQL is popular because everyone can learn and start using it after a while. I agree that Python sometimes is a better tool but I don't see SQL going away anytime. From my experience, the data modelling side is still overwhelmingly in SQL. The ingestion side is definitely mostly Python/Scala though.
- RobinL 10mo ago
  Author here. Re: 'SQL should be the first option considered', there are certainly advantages to other dataframe APIs like pandas or polars, and arguably any one is better in the moment than SQL. At the moment Polars is ascendent and it's a high quality API. But the problem is the ecosystem hasn't standardised on any of them, and it's annoying to have to rewrite pipelines from one dataframe API. I also agree you're gonna hit OOM if your data is massive, but my guess is the vast majority of tabular data people process is <10GB, and that'll generally process fine on a single large machine. Certainly in my experience it's common to see Spark being used on datasets that are no where big enough to need it. DuckDB is gaining traction, but a lot of people still seem unaware how quickly you can process multiple GB of data on a laptop nowadays. I guess my overall position is it's a good idea to think about using DuckDB first, because often it'll do the job quickly and easily. There are a whole host of scenarios where it's inappropriate, but it's a good place to start.
mrtimo 10mo ago
What I love about duckdb: -- Support for .parquet, .json, .csv (note: Spotify listening history comes in a multiple .json files, something fun to play with). -- Support for glob reading, like: select * from 'tsa20*.csv' - so you can read hundreds of files (any type of file!) as if they were one file. -- if the files don't have the same schema, union_by_name is amazing. -- The .csv parser is amazing. Auto assigns types well. -- It's small! The Web Assembly version is 2mb! The CLI is 16mb. -- Because it is small you can add duckdb directly to your product, like Malloy has done: https://www.malloydata.dev/ https://www.malloydata.dev/ - I think of Malloy as a technical persons alternative to PowerBI and Tableau, but it uses a semantic model that helps AI write amazing queries on your data. Edit: Malloy makes SQL 10x easier to write because of its semantic nature. Malloy transpiles to SQL, like Typescript transpiles to Javascript.
- oulipo2 10mo ago
  Malloy and PRQL (https://prql-lang.org/book/ https://prql-lang.org/book/) are quite cool
- newusertoday 10mo ago
  its 32 mb uncompressed and around 6MB compressed, its not that small https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm/dist/ https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm/dist/ it is also difficult to customize as compared to sqlite so for example if you want to use your own parser for csv than it becomes hard. But yes it provides lot of convenience out of the box as you have already listed.
- arjie 10mo ago
  This is a great sell. I have this annoyingly manual approach with a SQLite import and so on. This is great. Thank you!
- exographicskip 10mo ago
  Thanks for the excellent comment! Now excuse me while I go export my spotify history to play around with duckdb <3
  - 10mo ago
deleted 10mo ago
[deleted]
majkinetor 10mo ago
Anybody with experience in using duckdb to quickly select page of filtered transactions from the single table having a couple of billions of records and let's say 30 columns where each can be filtered using simple WHERE clausule? Lets say 10 years of payment order data. I am wondering since this is not analytical scenario. Doing that in postgres takes some time, and even simple count(*) takes a lot of time (with all columns indexed)
- morganherlocker 10mo ago
  I've used duckdb a lot at this scale, and I would not expect something like this to take more than a few seconds, if that. The only slow duckdb queries I have encountered either involve complex joins or glob across many files.
- cess11 10mo ago
  I'm not so sure the common index algorithms would work to speed up a count. How often is the table updated? If it's often, and it's also queried often for the count, then run the count somewhat often on a schedule and store the result separately, and if it isn't queried often, do it more seldom. From what you describe I'd expect a list of column-value pairs under a WHERE to resolve pretty fast if it uses indices and don't fish out large amounts of data at once.
film42 10mo ago
Just 10 minutes ago I was working with a very large semi-malformed excel file generated by a mainframe. DuckDB was able to load it with all_varchar (just keep everything a string) in under a second. I'm still waiting for Excel to load the file.
countrymile 10mo ago
It's worth noting that R has a great duckdb API as well. Saved me a lot of time when dealing with a 29GB CSV file and splitting it into separate parquet files on a low RAM ONS server a few months back. https://r.duckdb.org/ https://r.duckdb.org/
efromvt 10mo ago
DuckDB can't get enough love! Beyond being a really performant database, being so friendly [sql and devx] is really underrated and hard to pull off well and is key to it being so fun - you get a compounding ecosystem because it's so easy to get started. Hoping that they can manage to keep it vibrant without it slowing down the pace of innovation at all. The web/WASM integration is also fabulous. Looking forward to more "small engines" getting into that space to provide some competition and keep pushing it forward.
steve_adams_86 10mo ago
It has become a favourite tool for me as well. I work with scientists who research BC's coastal environment, from airborne observation of glaciers to autonomous drones in the deep sea. We've got heaps of data. A while back I took a leap of faith with DuckDB as the data-processing engine for a new tool we're using to transform and validate biodiversity data. The goal is to take heaps of existing datasets and convert them to valid Darwin Core data. Keyword being valid. DuckDB is such an incredible tool in this context. Essentially I dynamically build duckdb tables from schemas describing the data, then import it into the tables. If it fails, it explains why on a row-by-row basis (as far as it's able to, at least). Once the raw data is in, transformations can occur. This is accomplished entirely in DuckDB as well. Finally, validations are performed using application-layer logic if the transformation alone isn't assurance enough. I've managed to build an application that's way faster, way more capable, and much easier to build than I expected. And it's portable! I think I can get the entire core running in a browser. Field researchers could run this on an iPad in a browser, offline! This is incredible to me. I've had so much fun learning to use DuckDB better. It's probably my favourite discovery in a couple of years. And yeah, this totally could have been done any number of different ways. I had prototypes which took much different routes. But the cool part here is I can trust DuckDB to do a ton of heavy lifting. It comes with the cost of some things happening in SQL that I'd prefer it didn't sometimes, but I'm content with that tradeoff. In cases where I'm missing application-layer type safety, I use parsing and tests to ensure my DB abstractions are doing what I expect. It works really well! edit: For anyone curious, the point of this project is to allow scientists to analyze biodiversity and genomic data more easily using common rather than bespoke tools, as well as publish it to public repositories. Publishing is a major pain point because people in the field typically work very far from the Darwin Core spec :) I'm very excited to polish it a bit and get it in the hands of other organizations.
- anotherpaul 10mo ago
  What's the advantage over using Polars for the same task? It seems to me the natural competitor here and I vastly prefer the Polars syntax over SQL any day. So I was curious if I should try duckdb or stick with polars
rustyconover 10mo ago
DuckDB is awesome and Robin is too!
owlstuffing 10mo ago
100% agree. > Writing SQL code Language integration is paramount for med/lg projects. There's an experimental Java lang project, manifold-sql [1], that does the impossible: inline native DuckDB SQL + type-safety. """ [.sql/] SELECT station_name, count(*) AS num_services FROM 'http://blobs.duckdb.org/train_services.parquet' WHERE monthname(date) = 'May' GROUP BY ALL ORDER BY num_services DESC LIMIT 3 """ .fetch() .forEach(row -> out.println(row.stationName + ": " + row.numServices)); 1. https://github.com/manifold-systems/manifold/blob/master/docs/articles/duckdb_info.md https://github.com/manifold-systems/manifold/blob/master/doc...
uwemaurer 10mo ago
We use DuckDB to process analytics and feeds for Bluesky (https://bluefacts.app https://bluefacts.app) To get fast access to the query results we use the Apache Arrow interface and generate the code directly from DuckDB SQL queries using the SQG tool ( https://sqg.dev/generators/java-duckdb-arrow/ https://sqg.dev/generators/java-duckdb-arrow/)
- udkl 10mo ago
  I'm very interested to see a behind the scenes of your tech - do you guys have a writeup somewhere? You HN tool is also promising!
yakkomajuri 10mo ago
Been quite a fan of DuckDB and we actually even use it in production. But coincidentally today I was exploring memory usage and I believe I'm finding memory leaks. Anybody have similar experiences? Still debugging more deeply but looking reasonably conclusive atm.
- yakkomajuri 10mo ago
  As far as I can tell pretty conclusive results: https://github.com/duckdb/duckdb/issues/20569 https://github.com/duckdb/duckdb/issues/20569 If someone can explain this or has a fix for it I'd love to hear it!
  - fauigerzigerk 10mo ago
    You shouldn't expect a database system to release memory back to the OS just because it's no longer needed for some specific purpose. DuckDB has a memory_limit setting with a default of 80% of RAM. If you want to set a lower limit you can do something like SET memory_limit = '1GB'; https://duckdb.org/2024/07/09/memory-management https://duckdb.org/2024/07/09/memory-management
davidtm 10mo ago
It is the darling of laptop-based analysis. I’m always surprised that duckdb doesn’t get used more to enhance existing, and stubbornly bedded-in data pipelines. We use it in browser and on server at Count (https://count.co https://count.co) to make data warehouses more responsive for multiuser and agentic work.
PLenz 10mo ago
Duckdb is wonderful. I have several multi-TB pipelines that I moved over from spark and dask. It's so much easier to think in terms of knew machine that gets it's resources used efficiently instead of distributed/cloud processes. It even let me take some big data things back to on-prem from aws.
red2awn 10mo ago
It was mentioned that the performance of DuckDB is similar to that of Polars (among others). In that case why would one choose DuckDB over Polars? The only differentiator seems to be that you do the querying with standard SQL instead of the library specific APIs.
- dkdcio 10mo ago
  - performance is often better, especially on “out of core” (“streaming”, spill to disk data sizes). Polars has done a ton of work on their streaming engine but they’re still catching up - you don’t need to use Python (but Pythonic wrappers like Ibis exist; disclaimer I worked on Ibis, you can find my blogs on performance comparisons easily there); CLI, WASM, etc. w/o Python - governance: DuckDB as OSS is setup in a more sustainable way (DuckDB Labs + DuckDB Foundation). while there is a VC-backed company (MotherDuck), it doesn’t employ the primary developers/control the project in the same way the Polars company does - overall just simplicity and focus. tends to break less, solely focused on single-node, easy to extend, etc. — not trying to do a cloud product, distributing computing, supporting GPU execution
deleted 10mo ago
[deleted]
netcraft 10mo ago
I love duckdb, I use it as much as I can. I just wish that the support for node/bun was as good as python. And I wish that they would bundle it differently for node/bun - the way it is now it depends on a dynamic link to a library which means I cant bundle it into a bun executable.
- benpacker 10mo ago
  I get segfaults all the time when using it with Bun and the new Node neo api. What’s your recipe, are you not getting these?
fsdfasdsfadfasd 10mo ago
ctrl + f clickhouse doesn't return anything, surprising
- Onavo 10mo ago
  Clickhouse chDB is excellent, much less buggy than DuckDB https://clickhouse.com/chdb https://clickhouse.com/chdb
wswin 10mo ago
Comparison to Spark in terms of speed and simplicity is pretty unfair since Spark is designed for multi-node use and horizontal scaling isn't going anywhere.
- potamic 10mo ago
  What is duckdb better compared to? What were people using before it entered the scene?
lz400 10mo ago
I do a lot of data processing and my tool of choice is polars. It's blazing fast and has (like pandas) a lot of very useful functions that aren't in SQL or are awkward to emulate in SQL. I can also just do Python functions if I want something that's not offered. Please sell DuckDB to me. I don't know it very well but my (possibly wrong) intuition is that even giving equal performance, it's going to drop me to the awkwardness of SQL for data processing.
- bovinejoni 10mo ago
  I could anecdotally tell you it’s significantly faster and more concise for my workloads, but it’s a standalone executable so just try it out and benchmark for your use case. It doesn’t require fine tuning or even a learning curve
n_u 10mo ago
Question for folks in data science / ML space: Has DuckDB been replacing Pandas and NumPy for basic data processing?
nylonstrung 10mo ago
DuckDB is awesome. If you want it's power as a query engine but like to write python instead of SQL, I highly recommend using it as a backend for the Ibis dataframe library It let's you interchange pythonic dataframe syntax (like Pandas and Polars) with SQL that 'compile' down to SQL in DuckDB dialect And you can use those queries interchangably in postgres, sqlite, polars, spark, etc https://ibis-project.org/ https://ibis-project.org/
vivzkestrel 10mo ago
stupid question: - what is wrong with postgresql for doing this?
- jorin 10mo ago
  postgres (without extensions) is slower for analytical queries since it stores data as rows, not columns. Also, duckdb is an in-memory database so it's more comparable to sqlite than postgres.
  - vivzkestrel 10mo ago
    well what was wrong with apache cassandra? isnt that a columnar database too?
willtemperley 10mo ago
It's a great tool, but the dynamic loading of extensions in DuckDB makes working with code signing very difficult. Second, the spatial extension uses LGPL components which adds another headache for commercial apps. As such, it's not readily usable as a library, or set of libraries. I really prefer Apache's approach to analytics where it's possible to pick and choose the parts you need, and integrate them with standard package maangers. Need GB/S arrays over HTTP? Use Arrow Flight. Want to share self-describing structured arrays with files? Use Arrow IPC. Need to read Parquet? Add that package trait. Another potential issue with DuckDB is the typing at the SQL interface. Arrow allows direct access to primitive arrays, but DuckDB uses a slightly different type system at the SQL interface. Even small differences in type systems can lead to combinatoric type explosion. This is more a critiscm of SQL interfaces than DuckDB however. Additionally Arrow has native libraries in most mainstream languages.
falconroar 10mo ago
Polars has all of these benefits (to some degree), but also allows for larger-than-memory datasets.
microflash 10mo ago
I've been using DuckDB to process massive Excel files. Despite weird quirks [1], it has been great experience so far. I now use it to process CSV, JSON, Parquet files. It is very fast, and extremely approachable, thanks to SQL being the language for interaction. [1]: https://github.com/duckdb/duckdb-excel/issues/76 https://github.com/duckdb/duckdb-excel/issues/76
mastermage 10mo ago
I love duckdb for a way different reason than most. It has a much more expressive type system compared to Sqlite. Like Time/Date also Unions. Its like a mini postgress.
fastasucan 10mo ago
I dont get the either/or posts here about polars. Some times I do duckdb sql queries against a polars df, some times I convert a duckdb result to polars. Its great to use whatever is best for the situation.
- RobinL 10mo ago
  I think that's totally fine for individual work, but in larger data engineering teams it's less good to switch between tools because other people may have to maintain your code. That said, polars is good, and if the team agree to standardise on it then that's a totally reasonable choice. I guess one of my reservations is I've been (historically) burned by decisions within data eng teams to use pandas, causing all sorts of problems with data typing and memory and eventually having to rewrite it all. But I accept polars doesn't suffer from the same problems (and actually some of them are even mitigated in more recent versions of pandas)
morgul 10mo ago
I did large-scale molecular data engineering with DuckDB and Polars (https://github.com/scikit-fingerprints/MolPILE_dataset https://github.com/scikit-fingerprints/MolPILE_dataset, https://arxiv.org/abs/2509.18353 https://arxiv.org/abs/2509.18353 for those interested). Both were amazing, but for large-scale JOINs, only DuckDB didn't result in OOM. Really a pleasure to use too. I could also JOIN local CSV datasets, Postgres database, and even Excel files from chemists. All of this in Jupyter Notebook and really seamless Python integration. This also means that one can easily do heavy lifting in DuckDB, export to Polars variable, put into Plotly, and get an interactive plot. Neat stuff.
morgul 10mo ago
I did large-scale molecular data engineering with DuckDB and Polars (https://github.com/scikit-fingerprints/MolPILE_dataset https://github.com/scikit-fingerprints/MolPILE_dataset, https://arxiv.org/abs/2509.18353 https://arxiv.org/abs/2509.18353 for those interested). Both were amazing, but for large-scale JOINs, only DuckDB didn't result in OOM (tried Dask, DuckDB, Polars). Really a pleasure to use too. I could also JOIN local CSV datasets, Postgres database, and even Excel files from chemists. All of this in Jupyter Notebook and really seamless Python integration. This also means that one can easily do heavy lifting in DuckDB, export to Polars variable, put into Plotly, and get an interactive plot. Neat stuff.

310 points by tosh 10mo ago

tobilg 10mo ago

Built with DuckDB-Wasm: SQL Terminal in the browser https://terminal.sql-workbench.com https://terminal.sql-workbench.com

DangitBobby 10mo ago

Being able to use SQL on CSV and json/jsonl files is pretty sweet. Of course it does much more than that, but that's what I do most often with it. Love duckdb.

samuell 10mo ago
Indeed! I generally like awk a lot for simpler CSV/TSV processing, but when it comes to cases where you need things like combining/joining multiple CSV files or aggregating for certain columns, SQL really shines IME.

oulu2006 10mo ago

That's really interesting, I love the idea of being able to use columnar support directly within postgresql. I was thinking of using Citus for this, but possibly using duckdb is a better way to do. Citus comes with a lot more out of the box but duckdb could be a good stepping stone.

biophysboy 10mo ago
Its a really handy tool. I've queried basically everything you can w/ duckdb - csv, json, s3 buckets, MS SQL servers, excel sheets, pandas dataframes, etc - and have had very few issues.

clumsysmurf 10mo ago

DuckDB has experimental builds for Android ... I'm wondering how much work it would take to implement a Java API for it similar to sqlite (Cursor, etc).

jtbaker 10mo ago
Something different than https://duckdb.org/docs/stable/clients/java https://duckdb.org/docs/stable/clients/java?
- clumsysmurf 10mo ago
  Android doesn't use JDBC.
deleted 10mo ago
[deleted]

smithclay 10mo ago

Agree with the author, will add: duckdb is an extremely compelling choice if you’re a developer and want to embed analytics in your app (which can also run in a web browser with wasm!) Think this opens up a lot of interesting possibilities like more powerful analytics notebooks like marimo (https://marimo.io/ https://marimo.io/) … and that’s just one example of many.

canadiantim 10mo ago
The wasm is pretty heavy data-wise tho, I’m hoping eventually it’ll be lighter for easier loading on not so good devices.
- efromvt 10mo ago
  I'd really love a minimalist version, I'm not sure how small it's feasible for them to shrink it. As long as it doesn't get bigger and devices keep getting faster, I suppose?
dimitrieh 10mo ago
100% We recently created a survey website for the community survey results for Node-RED making it completely dynamic and segment-able. Creates lots of value and allows everyone to look at the data through their own lens. It uses DuckDB with WASM under the hood. Awesome technologies. https://nodered.org/community-survey/ https://nodered.org/community-survey/

tjchear 10mo ago

I’ve not used duckdb before nor do I do much data analysis so I am curious about this one aspect of processing medium sized json/csv with it: the data are not indexed, so any non-trivial query would require a full scan. Is duckdb so fast that this is never really a problem for most folks?

biophysboy 10mo ago
Zonemaps are created for columns automatically. I process somewhat large tables w/ duckdb regularly (100M rows) and never have any problems.
- riku_iki 10mo ago
  that's true for duckdb native tables, but the question was about json.
  - biophysboy 10mo ago
    They said json and csv - it handles both!
    - riku_iki 10mo ago
      handles depends on size. But I tried to say there is no zonemaps for json.
akhundelar 10mo ago
Not a duckdb user, but I use polars a lot (mentioned in the article). Depends on your definition of medium sized, but for tables of hundreds of thousands of rows and ~30 columns, these tools are fast enough to run queries instantly or near instantly even on laptop CPUs.
mpalmer 10mo ago
I guess the question is: how much is medium? DuckDB can handle quite a lot of data without breaking a sweat. Certainly if you prefer writing SQL for certain things, it's a no-brainer.

biophysboy 10mo ago

I think my favorite part of duckdb is its flexibility. Its such a handly little swiss army knife for doing analytical processing in scientific environments (messy data w/ many formats).

s-a-p 10mo ago

"making DuckDB potentially a suitable replacement for lakehouse formats such as Iceberg or Delta lake for medium scale data" > I'm a Data Engineering noob, but DuckDB alone doesn't do metadata & catalog management, which is why they've also introduce DuckLake. Related question, curious as to your experience with DuckLake if you've used it. I'm currently setting up s3 + Iceberg + duckDB for my company (startup) and was wondering what to pick between Iceberg and DuckLake.

biophysboy 10mo ago
DuckLake is pretty new, so I guess it would depend on if you need a more mature, fully-featured app.
- pattar 10mo ago
  I went to a talk by the Motherduck team about why they built DuckLake instead of leaning more in on Iceberg. The key takeaway is that instead of storing all the table metadata inside files on s3 and dealing with latency and file io they instead store all of that info inside a duckdb table. Seems like a good idea and worked smoothly when I tried, however it is not quite in a stable production state it is still <1.0. They have a nice talk about it on youtube: https://youtu.be/hrTjvvwhHEQ?si=WaT-rclQHBxnc9qV https://youtu.be/hrTjvvwhHEQ?si=WaT-rclQHBxnc9qV
  - willvarfar 10mo ago
    (I work a lot with BigQuery's BigLake adaptor and it's basically caching the metadata of the iceberg manifest and parquet footers in Bigtable (this is Google) so query planning is super fast etc. Really helps)
nchagnet 10mo ago
We're using ducklake with data storage on Google cloud storage and the catalog inside a postgres database and it's a breeze! It may not be the most mature product, but it's definitely a good setup for small to medium applications which still require a data lake.

noo_u 10mo ago

I'd say the author's thoughts are valid for basic data processing. Outside of that, most of claims in this article, such as: "We're moving towards a simpler world where most tabular data can be processed on a single large machine1 and the era of clusters is coming to an end for all but the largest datasets." become very debatable. Depending on how you want to pivot/ scale/augment your data, even datasets that seemingly "fit" on large boxes will quickly OOM you. The author also has another article where they claim that: "SQL should be the first option considered for new data engineering work. It’s robust, fast, future-proof and testable. With a bit of care, it’s clear and readable." (over polars/pandas etc) This does not map to my experience at all, outside of the realm of nicely parsed datasets that don't require too much complicated analysis or augmentation.

hnthrowaway0315 10mo ago
SQL is popular because everyone can learn and start using it after a while. I agree that Python sometimes is a better tool but I don't see SQL going away anytime. From my experience, the data modelling side is still overwhelmingly in SQL. The ingestion side is definitely mostly Python/Scala though.
RobinL 10mo ago
Author here. Re: 'SQL should be the first option considered', there are certainly advantages to other dataframe APIs like pandas or polars, and arguably any one is better in the moment than SQL. At the moment Polars is ascendent and it's a high quality API. But the problem is the ecosystem hasn't standardised on any of them, and it's annoying to have to rewrite pipelines from one dataframe API. I also agree you're gonna hit OOM if your data is massive, but my guess is the vast majority of tabular data people process is <10GB, and that'll generally process fine on a single large machine. Certainly in my experience it's common to see Spark being used on datasets that are no where big enough to need it. DuckDB is gaining traction, but a lot of people still seem unaware how quickly you can process multiple GB of data on a laptop nowadays. I guess my overall position is it's a good idea to think about using DuckDB first, because often it'll do the job quickly and easily. There are a whole host of scenarios where it's inappropriate, but it's a good place to start.

mrtimo 10mo ago

What I love about duckdb: -- Support for .parquet, .json, .csv (note: Spotify listening history comes in a multiple .json files, something fun to play with). -- Support for glob reading, like: select * from 'tsa20*.csv' - so you can read hundreds of files (any type of file!) as if they were one file. -- if the files don't have the same schema, union_by_name is amazing. -- The .csv parser is amazing. Auto assigns types well. -- It's small! The Web Assembly version is 2mb! The CLI is 16mb. -- Because it is small you can add duckdb directly to your product, like Malloy has done: https://www.malloydata.dev/ https://www.malloydata.dev/ - I think of Malloy as a technical persons alternative to PowerBI and Tableau, but it uses a semantic model that helps AI write amazing queries on your data. Edit: Malloy makes SQL 10x easier to write because of its semantic nature. Malloy transpiles to SQL, like Typescript transpiles to Javascript.

oulipo2 10mo ago
Malloy and PRQL (https://prql-lang.org/book/ https://prql-lang.org/book/) are quite cool
newusertoday 10mo ago
its 32 mb uncompressed and around 6MB compressed, its not that small https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm/dist/ https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm/dist/ it is also difficult to customize as compared to sqlite so for example if you want to use your own parser for csv than it becomes hard. But yes it provides lot of convenience out of the box as you have already listed.
arjie 10mo ago
This is a great sell. I have this annoyingly manual approach with a SQLite import and so on. This is great. Thank you!
exographicskip 10mo ago
Thanks for the excellent comment! Now excuse me while I go export my spotify history to play around with duckdb <3
- 10mo ago

deleted 10mo ago

[deleted]

majkinetor 10mo ago

Anybody with experience in using duckdb to quickly select page of filtered transactions from the single table having a couple of billions of records and let's say 30 columns where each can be filtered using simple WHERE clausule? Lets say 10 years of payment order data. I am wondering since this is not analytical scenario. Doing that in postgres takes some time, and even simple count(*) takes a lot of time (with all columns indexed)

morganherlocker 10mo ago
I've used duckdb a lot at this scale, and I would not expect something like this to take more than a few seconds, if that. The only slow duckdb queries I have encountered either involve complex joins or glob across many files.
cess11 10mo ago
I'm not so sure the common index algorithms would work to speed up a count. How often is the table updated? If it's often, and it's also queried often for the count, then run the count somewhat often on a schedule and store the result separately, and if it isn't queried often, do it more seldom. From what you describe I'd expect a list of column-value pairs under a WHERE to resolve pretty fast if it uses indices and don't fish out large amounts of data at once.

film42 10mo ago

Just 10 minutes ago I was working with a very large semi-malformed excel file generated by a mainframe. DuckDB was able to load it with all_varchar (just keep everything a string) in under a second. I'm still waiting for Excel to load the file.

countrymile 10mo ago

It's worth noting that R has a great duckdb API as well. Saved me a lot of time when dealing with a 29GB CSV file and splitting it into separate parquet files on a low RAM ONS server a few months back. https://r.duckdb.org/ https://r.duckdb.org/

efromvt 10mo ago

DuckDB can't get enough love! Beyond being a really performant database, being so friendly [sql and devx] is really underrated and hard to pull off well and is key to it being so fun - you get a compounding ecosystem because it's so easy to get started. Hoping that they can manage to keep it vibrant without it slowing down the pace of innovation at all. The web/WASM integration is also fabulous. Looking forward to more "small engines" getting into that space to provide some competition and keep pushing it forward.

steve_adams_86 10mo ago

It has become a favourite tool for me as well. I work with scientists who research BC's coastal environment, from airborne observation of glaciers to autonomous drones in the deep sea. We've got heaps of data. A while back I took a leap of faith with DuckDB as the data-processing engine for a new tool we're using to transform and validate biodiversity data. The goal is to take heaps of existing datasets and convert them to valid Darwin Core data. Keyword being valid. DuckDB is such an incredible tool in this context. Essentially I dynamically build duckdb tables from schemas describing the data, then import it into the tables. If it fails, it explains why on a row-by-row basis (as far as it's able to, at least). Once the raw data is in, transformations can occur. This is accomplished entirely in DuckDB as well. Finally, validations are performed using application-layer logic if the transformation alone isn't assurance enough. I've managed to build an application that's way faster, way more capable, and much easier to build than I expected. And it's portable! I think I can get the entire core running in a browser. Field researchers could run this on an iPad in a browser, offline! This is incredible to me. I've had so much fun learning to use DuckDB better. It's probably my favourite discovery in a couple of years. And yeah, this totally could have been done any number of different ways. I had prototypes which took much different routes. But the cool part here is I can trust DuckDB to do a ton of heavy lifting. It comes with the cost of some things happening in SQL that I'd prefer it didn't sometimes, but I'm content with that tradeoff. In cases where I'm missing application-layer type safety, I use parsing and tests to ensure my DB abstractions are doing what I expect. It works really well! edit: For anyone curious, the point of this project is to allow scientists to analyze biodiversity and genomic data more easily using common rather than bespoke tools, as well as publish it to public repositories. Publishing is a major pain point because people in the field typically work very far from the Darwin Core spec :) I'm very excited to polish it a bit and get it in the hands of other organizations.

anotherpaul 10mo ago
What's the advantage over using Polars for the same task? It seems to me the natural competitor here and I vastly prefer the Polars syntax over SQL any day. So I was curious if I should try duckdb or stick with polars

rustyconover 10mo ago

DuckDB is awesome and Robin is too!

owlstuffing 10mo ago

100% agree. > Writing SQL code Language integration is paramount for med/lg projects. There's an experimental Java lang project, manifold-sql [1], that does the impossible: inline native DuckDB SQL + type-safety. """ [.sql/] SELECT station_name, count(*) AS num_services FROM 'http://blobs.duckdb.org/train_services.parquet' WHERE monthname(date) = 'May' GROUP BY ALL ORDER BY num_services DESC LIMIT 3 """ .fetch() .forEach(row -> out.println(row.stationName + ": " + row.numServices)); 1. https://github.com/manifold-systems/manifold/blob/master/docs/articles/duckdb_info.md https://github.com/manifold-systems/manifold/blob/master/doc...

uwemaurer 10mo ago

We use DuckDB to process analytics and feeds for Bluesky (https://bluefacts.app https://bluefacts.app) To get fast access to the query results we use the Apache Arrow interface and generate the code directly from DuckDB SQL queries using the SQG tool ( https://sqg.dev/generators/java-duckdb-arrow/ https://sqg.dev/generators/java-duckdb-arrow/)

udkl 10mo ago
I'm very interested to see a behind the scenes of your tech - do you guys have a writeup somewhere? You HN tool is also promising!

yakkomajuri 10mo ago

Been quite a fan of DuckDB and we actually even use it in production. But coincidentally today I was exploring memory usage and I believe I'm finding memory leaks. Anybody have similar experiences? Still debugging more deeply but looking reasonably conclusive atm.

yakkomajuri 10mo ago
As far as I can tell pretty conclusive results: https://github.com/duckdb/duckdb/issues/20569 https://github.com/duckdb/duckdb/issues/20569 If someone can explain this or has a fix for it I'd love to hear it!
- fauigerzigerk 10mo ago
  You shouldn't expect a database system to release memory back to the OS just because it's no longer needed for some specific purpose. DuckDB has a memory_limit setting with a default of 80% of RAM. If you want to set a lower limit you can do something like SET memory_limit = '1GB'; https://duckdb.org/2024/07/09/memory-management https://duckdb.org/2024/07/09/memory-management

davidtm 10mo ago

It is the darling of laptop-based analysis. I’m always surprised that duckdb doesn’t get used more to enhance existing, and stubbornly bedded-in data pipelines. We use it in browser and on server at Count (https://count.co https://count.co) to make data warehouses more responsive for multiuser and agentic work.

PLenz 10mo ago

Duckdb is wonderful. I have several multi-TB pipelines that I moved over from spark and dask. It's so much easier to think in terms of knew machine that gets it's resources used efficiently instead of distributed/cloud processes. It even let me take some big data things back to on-prem from aws.

red2awn 10mo ago

It was mentioned that the performance of DuckDB is similar to that of Polars (among others). In that case why would one choose DuckDB over Polars? The only differentiator seems to be that you do the querying with standard SQL instead of the library specific APIs.

dkdcio 10mo ago
- performance is often better, especially on “out of core” (“streaming”, spill to disk data sizes). Polars has done a ton of work on their streaming engine but they’re still catching up - you don’t need to use Python (but Pythonic wrappers like Ibis exist; disclaimer I worked on Ibis, you can find my blogs on performance comparisons easily there); CLI, WASM, etc. w/o Python - governance: DuckDB as OSS is setup in a more sustainable way (DuckDB Labs + DuckDB Foundation). while there is a VC-backed company (MotherDuck), it doesn’t employ the primary developers/control the project in the same way the Polars company does - overall just simplicity and focus. tends to break less, solely focused on single-node, easy to extend, etc. — not trying to do a cloud product, distributing computing, supporting GPU execution

deleted 10mo ago

[deleted]

netcraft 10mo ago

I love duckdb, I use it as much as I can. I just wish that the support for node/bun was as good as python. And I wish that they would bundle it differently for node/bun - the way it is now it depends on a dynamic link to a library which means I cant bundle it into a bun executable.

benpacker 10mo ago
I get segfaults all the time when using it with Bun and the new Node neo api. What’s your recipe, are you not getting these?

fsdfasdsfadfasd 10mo ago

ctrl + f clickhouse doesn't return anything, surprising

Onavo 10mo ago
Clickhouse chDB is excellent, much less buggy than DuckDB https://clickhouse.com/chdb https://clickhouse.com/chdb

wswin 10mo ago

Comparison to Spark in terms of speed and simplicity is pretty unfair since Spark is designed for multi-node use and horizontal scaling isn't going anywhere.

potamic 10mo ago
What is duckdb better compared to? What were people using before it entered the scene?

lz400 10mo ago

I do a lot of data processing and my tool of choice is polars. It's blazing fast and has (like pandas) a lot of very useful functions that aren't in SQL or are awkward to emulate in SQL. I can also just do Python functions if I want something that's not offered. Please sell DuckDB to me. I don't know it very well but my (possibly wrong) intuition is that even giving equal performance, it's going to drop me to the awkwardness of SQL for data processing.

bovinejoni 10mo ago
I could anecdotally tell you it’s significantly faster and more concise for my workloads, but it’s a standalone executable so just try it out and benchmark for your use case. It doesn’t require fine tuning or even a learning curve

n_u 10mo ago

Question for folks in data science / ML space: Has DuckDB been replacing Pandas and NumPy for basic data processing?

nylonstrung 10mo ago

DuckDB is awesome. If you want it's power as a query engine but like to write python instead of SQL, I highly recommend using it as a backend for the Ibis dataframe library It let's you interchange pythonic dataframe syntax (like Pandas and Polars) with SQL that 'compile' down to SQL in DuckDB dialect And you can use those queries interchangably in postgres, sqlite, polars, spark, etc https://ibis-project.org/ https://ibis-project.org/

vivzkestrel 10mo ago

stupid question: - what is wrong with postgresql for doing this?

jorin 10mo ago
postgres (without extensions) is slower for analytical queries since it stores data as rows, not columns. Also, duckdb is an in-memory database so it's more comparable to sqlite than postgres.
- vivzkestrel 10mo ago
  well what was wrong with apache cassandra? isnt that a columnar database too?

willtemperley 10mo ago

It's a great tool, but the dynamic loading of extensions in DuckDB makes working with code signing very difficult. Second, the spatial extension uses LGPL components which adds another headache for commercial apps. As such, it's not readily usable as a library, or set of libraries. I really prefer Apache's approach to analytics where it's possible to pick and choose the parts you need, and integrate them with standard package maangers. Need GB/S arrays over HTTP? Use Arrow Flight. Want to share self-describing structured arrays with files? Use Arrow IPC. Need to read Parquet? Add that package trait. Another potential issue with DuckDB is the typing at the SQL interface. Arrow allows direct access to primitive arrays, but DuckDB uses a slightly different type system at the SQL interface. Even small differences in type systems can lead to combinatoric type explosion. This is more a critiscm of SQL interfaces than DuckDB however. Additionally Arrow has native libraries in most mainstream languages.

falconroar 10mo ago

Polars has all of these benefits (to some degree), but also allows for larger-than-memory datasets.

microflash 10mo ago

I've been using DuckDB to process massive Excel files. Despite weird quirks [1], it has been great experience so far. I now use it to process CSV, JSON, Parquet files. It is very fast, and extremely approachable, thanks to SQL being the language for interaction. [1]: https://github.com/duckdb/duckdb-excel/issues/76 https://github.com/duckdb/duckdb-excel/issues/76

mastermage 10mo ago

I love duckdb for a way different reason than most. It has a much more expressive type system compared to Sqlite. Like Time/Date also Unions. Its like a mini postgress.

fastasucan 10mo ago

I dont get the either/or posts here about polars. Some times I do duckdb sql queries against a polars df, some times I convert a duckdb result to polars. Its great to use whatever is best for the situation.

RobinL 10mo ago
I think that's totally fine for individual work, but in larger data engineering teams it's less good to switch between tools because other people may have to maintain your code. That said, polars is good, and if the team agree to standardise on it then that's a totally reasonable choice. I guess one of my reservations is I've been (historically) burned by decisions within data eng teams to use pandas, causing all sorts of problems with data typing and memory and eventually having to rewrite it all. But I accept polars doesn't suffer from the same problems (and actually some of them are even mitigated in more recent versions of pandas)

morgul 10mo ago

I did large-scale molecular data engineering with DuckDB and Polars (https://github.com/scikit-fingerprints/MolPILE_dataset https://github.com/scikit-fingerprints/MolPILE_dataset, https://arxiv.org/abs/2509.18353 https://arxiv.org/abs/2509.18353 for those interested). Both were amazing, but for large-scale JOINs, only DuckDB didn't result in OOM. Really a pleasure to use too. I could also JOIN local CSV datasets, Postgres database, and even Excel files from chemists. All of this in Jupyter Notebook and really seamless Python integration. This also means that one can easily do heavy lifting in DuckDB, export to Polars variable, put into Plotly, and get an interactive plot. Neat stuff.

morgul 10mo ago

I did large-scale molecular data engineering with DuckDB and Polars (https://github.com/scikit-fingerprints/MolPILE_dataset https://github.com/scikit-fingerprints/MolPILE_dataset, https://arxiv.org/abs/2509.18353 https://arxiv.org/abs/2509.18353 for those interested). Both were amazing, but for large-scale JOINs, only DuckDB didn't result in OOM (tried Dask, DuckDB, Polars). Really a pleasure to use too. I could also JOIN local CSV datasets, Postgres database, and even Excel files from chemists. All of this in Jupyter Notebook and really seamless Python integration. This also means that one can easily do heavy lifting in DuckDB, export to Polars variable, put into Plotly, and get an interactive plot. Neat stuff.