MotherDuck Blog

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)}} 2) Used this simulated netflix table dataset available from an S3 bucket, as used in this example blog. Installed the aws extension not the one mentioned in the blog: https://motherduck.com/blog/duckdb-tutorial-for-beginners/

Section in the blog: "FIRST ANALYTICS PROJECT"

Table has 7100 rows as seen like this:

SELECT COUNT(*) AS RowCount FROM netflix; 3) Did 8 different queries using DISTINCT ON / GROUP BY

The one below, being one example: SELECT DISTINCT ON (Title) Title, "As of", Rank FROM netflix ORDER BY Title, "As of" DESC; I am not seeing any out of memory or memory leak from these quick tests.

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?

Can you comment more on how you use persistent tries with unicity? And what do you mean by "the other way around"?

I can't speak to latency fully as of yet. But in my own incremental implementation of a Prolly Tree, it appears to have the same Big-O bounds as a Btree (as you said). But I can see there are places that it has to do a bit more work.

- Given the nature of merkle tree nodes identified by their hash, you have to go do look ups, rather than just following pointers.

- The tree has to touch a swath of nodes from the bottom up, due to potential rebalancing. Unlike a Btree, where you just change a single node seam from root to leaf, even with splits and merges.

- Prolly Trees are constructed from the bottom up, where you have to traverse across a layer. To find the next sibling, you sometimes have to travel up to the parents and come back down--hence subject to the occasional height traversal in the worse case. This doesn't happen often, especially if the cascading effect on insertion I talked about doesn't happen.

But like you said, maybe there are other factors that dominate the latency equation. It's hard for me to say at this point. Relevant post:

https://motherduck.com/blog/perf-is-not-enough/

MotherDuck | NYC, Amsterdam, San Francisco, or Seattle (Onsite preferred) | Software Engineers in AI, Database, and Ecosystems | Full-Time | $140K – $275K + Equity | https://motherduck.com/

Don’t let the quirky name fool you! We’re on a mission to radically transform the data analyst user experience. We think we can empower and delight analysts using a novel cloud architecture built on top of DuckDB, an open-source foundation with a vibrant community. Our team is a diverse mix of passionate, collaborative, and empathetic data industry veterans from Google, Amazon, Meta, Snowflake, SingleStore, Databricks, and the like.

We're expanding our focus on AI, and are looking for Python/C++ engineers to help us bring the power of large language models into the hands of database users. Check out the open role here: https://jobs.ashbyhq.com/MotherDuck/b485b0d6-2cbe-4083-b253-f38bdca144f9

We also have open positions in our Database and our Ecosystems teams. Come work with us and disrupt the future of data analytics. https://motherduck.com/careers/

> small dataset (100GB)

What counts as large or small definitely varies a lot depending on the context of the conversation/analysis.

MotherDuck's "Big Data is Dead" post [0] sticks in mind:

> The general feedback we got talking to folks in the industry was that 100 GB was the right order of magnitude for a data warehouse. This is where we focused a lot of our efforts in benchmarking.

Another point of reference is [1]

> [...] Umbra achieves unprecedentedly low query latencies. On small data sets, it is even faster than interpreter engines like DuckDB

> TPC-H Small Dataset = 866k tuples, sf 0.1

[0] https://motherduck.com/blog/big-data-is-dead/

[1] https://db.in.tum.de/~kersten/Tidy%20Tuples%20and%20Flying%20Start%20Fast%20Compilation%20and%20Fast%20Execution%20of%20Relational%20Queries%20in%20Umbra.pdf?lang=de

There's been some good implicit/explicit discussion about SoA on this post [1]. For anyone curious, there are a few different terms that refer to basically the same thing:

- Struct of Arrays (SoA) vs Array of Structs (AoS);

- Row-major order vs column-major order; and

- Row-based vs column based / columnar (in databases)

Most code has arrays of structs (or "lists of objects", the effect is the same), and most databases are row based (same thing). But many game engines use SoA and keep heterogeneous elements together. Some databases like DuckDB do this too, this is an article about the pros and cons of columnar storage in DBs [2].

1. https://news.ycombinator.com/item?id=49012056

2. https://motherduck.com/learn/columnar-storage-guide/

Multiple projects are coming to the same point it seems. Motherduck has been marketing "dives" since the beginning of the year (https://motherduck.com/blog/duck-dive-and-answer/) and in the Louie.ai team, we have been iterating on different patterns for similar needs. I'm getting the feeling that the answer to SaaS apps as fixed UIs over databases being dead because of coding agents means just the fixed dashboard pattern is dead, not SaaS, and BYO UI is part of the new table stakes.

I'm curious where the pattern will go. My sense is there is a split between cathedrals vs bazaar for approach here, where cathedrals are quite rigid app builders, think framer/wix, while bazaars focus a layer below for more flexibility but less integrated.