DuckDB 0.9 | Hacker News Reader

DuckDB 0.9

duckdb.org

174 pointsnnx59 comments

We use DuckDB to provide Python/Pandas access to data in our feature store. It helped us beat Databricks (45X), Vertex (13X), and Sagemaker (10X) in reading into Pandas from the Feature Store.

The recent support in DuckDB for "AS OF" has really helped our performance when doing point-in-time joins across tables.

https://www.hopsworks.ai/post/python-centric-feature-service...

I have been using duckdb files as caches infront of sql server for server side queries. Initially the cache was redis, but duckdb worked much better as the very same query that goes to sql can be send down to duckdb.

do you have more details on how you're doing this? i'd love to implement something like this

seconded.. very interested in this approach as well!

We have a restful server that accepts odata query. We translate that to sql using sqlalchemy (it’s a python stack). The application is a financial risk system with billions of rows. The query usually fetches data from sqlserver, does some manipulation using pandas dataframes, then serve it as either json or csv. We added duckdb as a cache distributed across many files (a request cannot return data from more than one file) then that very same odata query goes into duckdb. Applies the standard select, filter, group by or pivot and return a dataframe. In most cases duckdb was twice faster than sqlserver. Apologies about any bad grammar/spelling errors, typing from tiny phone in bed

You may shave some time replacing pandas with polars.

How? Do you replicate the whole db?

Not the whole db. Only data related to the day itself. A duckdb file is cached generated from a restful url (e.g /api/v1/risk/date/scheme) so that resides in a risk-scheme-date-uuid.duckdb) then a query will find the latest risk-scheme-date-*.duckdb and based on certain filters return a subset of the data. It’s more complex than that of course as the db is opened in read only to allow concurrent reads, a set of tmp tables are created on each read request to represent user rights where we filter returned data based on what the user is allowed to see. Each duckdb file is around 50mb and usually a newer file of matching scheme is written every 10 minutes, then the old ones are purged when they’re older than two hours

Just realised DuckDB names each of their releases after different species of ducks. This probably reveals how little I know of duck species but aren't they going to run out of names for future releases pretty soon? How many duck species are there?

There's over 130[0] according to birdspot

[0]: https://www.birdspot.co.uk/identifying-birds/types-of-ducks

That's lower than I expected and it'll be fun in a few decades to see what they do. Maybe we'll have to go looking for some new ducks.

Oracle's going to kill off the naming scheme when they buy DuckDB in 2031, so we're safe.

Perhaps after naming enough ducks they’ll toss in a goose.

Yes, that's a pretty small number. I would assume that Duckdb will have >130 releases in the future.

There are many! https://www.thespruce.com/duck-species-list-385436

If they run out of duck species, then can move on to duck preparations. Peking duck, smoked tea duck, duck l'orange etc.

If they run out of duck names they may move to chicken names without anyone noticing ...!

I've recently toyed with server-side SQLite with LiteFS [1] to enable replication, and it has worked quite well for me. Are there any similar plans for distributed setups for DuckDB?

[1] https://fly.io/docs/litefs/

You can store your data as parquet file(s) and query these on separate nodes handling multiple queries. Or for the giant data create a split parquet datasets and speed up the queries.

duckdb is up there alongside redis in my top list of amazing open source technologies. the aws extensions will help w/ ec2 boxes operating on iam roles, and it looks like there are a lot of other quality of life improvements around performance and size of data. stoked for this release!

i'm excited about duckdb and have used it for a few "what-if?" projects here and there but keep getting bitten by the file version breaking changes. i appreciate that it's under rapid development and that there's a way to export and import to mitigate it. excited for when that portion stabilizes a bit so that i can roll out some of those prototypes in a more formal and meaningful way for my team

Stabilizing storage is high priority for us! You won’t notice anything new while using DuckDB, but we’ve actually rewritten our entire serialization framework in this release. This was a big step towards stable storage

I recommend using ClickHouse instead of DuckDB.

It has been around since 2016, and it covers and extends the feature set of DuckDB with a huge margin. Worth noting that it never has breaking changes in its table format MergeTree.

I'm tracking the progress of DuckDB and see that it is modeled after ClickHouse, but does not approach it in terms of feature completeness, stability, or performance.

The closest to DuckDB option is to use its self-contained version, clickhouse-local: https://clickhouse.com/blog/extracting-converting-querying-l... or an embedded version, chdb: https://github.com/chdb-io/chdb

You should really get into a habit of disclosing your biases, as you seem to make an appearance in every DuckDB related thread with the same comments:)

(Head of Produck and Co-founder at MotherDuck)

Thank you! The topic interests to me, and I usually don't hesitate to comment. PS. I'm the developer of ClickHouse and CTO at ClickHouse Inc.

We've absolutely loved using duckDB, have paired it with PRQL to enable writing really simple to read pipelines that can be composed easily.

Next up adding a caching layer in.

Anyone know if duckdb is considering adding some sort of caching mechanism? i.e. determining which queries or intermediate queries to store in-memory?

Would be interesting to write this as an extension

We at MotherDuck are doing this!

While we're running vanilla duckdb, our extension does sopen heart surgery on some of the components, to make it suitable for a multi tenant serverless cloud service.

(Co-founder and head of produck at MotherDuck)

What is your use case for DuckDB?

DuckDB is really nice for local data processing! Exciting to see the new features and momentum building.

What is the situation with and roadmap for using JSON columns in DuckDB?

A friend has a use case involving searching structured data (like everyone). I think it's basically reports on different companies, so have fields like name, location, market cap, etc. They come as JSON documents, with very similar structures, which change slowly over time as fields are added and removed. He needs to give users an interface to these, where they can filter on various fields ("show me all small hairdressers in Minnesota" etc).

He currently dumps the JSON documents into ElasticSearch, and queries that. It works, but my gut feeling is that ElasticSearch is wildly less efficient than the theoretical optimum for this. Could DuckDB be an alternative?

The easiest thing would be if he could put whole JSON documents into a JSON column. Less easy would be breaking them up into real columns; i know that DuckDB is able to do this automatically, but it feels like a brittle thing to depend on.

I did some playing around with searching data from JSON, comparing a single JSON column to automatic 'proper' columns, and the proper columns were massively faster. Is there scope for making queries on JSON much faster, using indexes, or some setting i missed, or something?

Is DuckDB even likely to be a good solution for this? It's intended for analytics, whereas this is more of a retrieval / search / filtering use.

JSON columns are a kludge. What's really needed is Dynamic Relational (see link). JSON columns are second-class citizens in such RDBMS. Dynamic Relational would make all columns just as powerful. A startup could use something like the Zend business model to make an open-source version and a for-fee enterprise version for scaling. https://www.reddit.com/r/CRUDology/comments/12ari2l/dynamic_...

Old Reddit link, so you don't need an app on mobile: https://old.reddit.com/r/CRUDology/comments/12ari2l/dynamic_...

One thing that bit and puzzled me was autoloading of the extensions.

If you are compiling applications with clang (or zig), some (all?) extensions will not work, because C++ does not have a stable ABI. And thus an application linked with lld/zig cc will fail to load an extension linked with ld/gcc (aka the official duckdb extensions).

We found this the hard way after enabling zig-c++ as the C++ compiler in prod. Took some time to recompile (and statically link) the extensions we actually use, but now the system is much better off.

I would prefer a default that does not download anything from the internet (especially .so files), but that's quite easy to change when you know it.

Still wip to actually poke with duckdb for myself. :)

Serious question, what is the easiest way to show-case DuckDB? My partner loves ducks and I want to do something with DuckDB but I don't know where to start, do I download a large dataset and explore it using DuckDB

I'd say find an angle that adds value for you (ideally both since you want the answer AND you can benefit from learning) random ideas can be: * C++: extension to interface with GitHub workflows results (say how many times job X passed) * JavaScript: browser-based library to visualize data * Python: crunch geospatial data (say your own GPS data) * ...

We use duckdb-wasm as an in-browser telemetry store for an IoT use case. beats the performance of other data stores in your browser and much simpler than manually wrangling arrays

DuckDB also has potential for IoT/embedded use cases as a data buffer with its compression and efficient reads, instead of just sqlite. but for that it needs to free up disk after expiring data

Honest question here, how does it compare to clickhouse-local?

We plan to update our results on ClickBench at some point, as we’ve made some improvements there in this release. I’d expect performance to be similar

I just discovered Hacker News offers a JSON API, ducking awesome, if only there was a nice tool to crunch data as JSON files...

I think I have just found out yet another great DuckDB-weekend project!

It's definitely a fun dataset to explore.

Dale from ClickHouse wrote a pretty extensive blog series on the Hacker News dataset, ingest approach, some queries of interest...

Could be a good bit of reading alongside the project?

https://clickhouse.com/blog/getting-data-into-clickhouse-par...

Thanks, cool read, I took a couple of ideas, will see where we land.

Btw, it's already happening:

Go to https://shell.duckdb.org, and type

FROM 'https://hacker-news.firebaseio.com/v0/item/37663308.json';

Querying hacker news, from a browser tab (passing through a bunch of database and Web technology that make it possible for DuckDB to be executed within a browser tab)

woah! Didn't know this existed https://shell.duckdb.org, it's pretty cool!

SQL is a bad language for writing complex queries. Sometimes need to read inside out or bottom up, etc to make sense of it. Wish duckdb had a more procedural api.

CTE solves this problem. I prefer SQL expressions because they're composable. If you want to do things procedurally, you can just bind a result to a dataframe with duckdb.

How about the Relational API[0] or the new PySpark compatible API[1] added in this release?

[0] https://duckdb.org/docs/archive/0.8.1/api/python/relational_... [1] https://github.com/duckdb/duckdb/pull/8083

SQL could be friendlier for complex queries. We have recently made a talk on the improvements to SQL to simplify complex expressions: https://www.youtube.com/watch?v=zhrOYQpgvkk

Does Motherduck have distributed storage, query, and caching layers?

For compute specifically MotherDuck is a scale-up, not scale-out system [0]

Storage is naturally distributed due to underlying IaaS.

Rather, that seems to be an implementation detail. What use cases are relevant is the key. Are we chasing big Apple-sized workloads? No. [1]

That said, scaling-up on EC2, especially with our one-instance-per-user architecture, fits vast majority of workloads we've seen in our past lives building Exabyte-scale systems.

[0] https://motherduck.com/blog/the-simple-joys-of-scaling-up/ [1] https://motherduck.com/blog/big-data-is-dead/

Attempting to present the inability to scale out as a sort of advantage sounds strange to me. The problem with duckdb is that it does not work as a database server. Occasional data analysis may be more or less ok, but you don't need duckdb for that - you can use proven, boring open-source technologies, such as ClickHouse.

> Attempting to present the inability to scale out as a sort of advantage sounds strange to me.

it is a scope, not inability. Building single node system is much easier than distributed for fast complex algos, meaning team can ship more features.

> boring open-source technologies, such as ClickHouse.

to me ClickHouse is not boring, it is fragile and failing all the time with OOMs in various places.

As mentioned in another comment: developers of Clickhouse speaking ill of competitors without disclosing their bias does not convince me of Clickhouse.

> in our past lives building Exabyte-scale systems.

so, how did you scale to exabyte with one server?..

see, we're not targeting these use cases at all :)

I have gotten so many fatal errors when I last tried DuckDB that I don't want to give it another try before 1.0 again. It shows that it's implemented in C++ imo.

I’m curious — I’ve been using DuckDB for 2 years now on large Parquet datasets and rarely encountered any fatal errors (and being C++ hasn’t seem to have been an issue in my experience) — what errors have you encountered and what data were you querying?

It is hard (close to impossible) to write a memory-safe code in C++.

In ClickHouse, we approach this with continuous fuzzing. When we tried to integrate duckdb as one of the storage engines into ClickHouse, our CI system immediately found an uninitialized memory read inside it: https://github.com/duckdb/duckdb/issues/7433

@wenc, the op might have meant the native storage format of DuckDB, which does not use Parquet.

I wasn't using Parquet, but rather ingested some data - not even a lot - and ran some queries and got severe errors and DuckDB crashed. I can't remember the exact errors since it's been a while and I haven't used it since.

Related HN Posts in Markdown Table Format

  | Title Snippet                   | YYYY-MM | comments | HN URL                                        |
  | ------------------------------- | ------- | --------:| --------------------------------------------- |
  | "DuckDB 0.8.0"                  | 2023-05 |        0 | https://news.ycombinator.com/item?id=35974719 |
  | "DuckDB for Swift"              | 2023-05 |        0 | https://news.ycombinator.com/item?id=35711430 |
  | "Calculate the Digits of Pi..." | 2023-03 |        1 | https://news.ycombinator.com/item?id=35153824 |
  | "...in-process OLAP DBMS "      | 2023-02 |      103 | https://news.ycombinator.com/item?id=34741195 |
  | "Modern Data Stack in a Box..." | 2022-10 |        5 | https://news.ycombinator.com/item?id=33191938 |
  | "Querying Postgres Tables..."   | 2022-09 |       39 | https://news.ycombinator.com/item?id=33035803 |
  | "...embeddable SQL database..." | 2020-09 |      160 | https://news.ycombinator.com/item?id=24531085 |
  | "DuckDB: SQLite for Analytics"  | 2020-05 |       67 | https://news.ycombinator.com/item?id=23287278 |

Reply on news.ycombinator.com