DuckDB Internals Part 1(greybeam.ai) |
DuckDB Internals Part 1(greybeam.ai) |
Thanks for the clarification, LLM!!
I've never been that strong of an engineer. TIL that at one of my internships I was building DuckDB but for the company's private use cases. Well, trying to anyways. I didn't really get the whole picture, the pieces did not fit into place.
Didn't get the return offer obviously, probably because I didn't make the connection (or really a coherent narrative of what I was building). RIP. You live and you learn, I guess.
SQLite and DuckDB solve different problems and actually complement each other quite well. SQLite is excellent for transactional workloads (OLTP), while DuckDB shines for analytical workloads (OLAP), especially time-series data and aggregations.
We’ve been using both side-by-side in an open-source project for about two years: SQLite for configuration and transactional data, DuckDB for historical sensor data and analytics. So far, it’s been a very good combination.
I can see applications having valid reasons to use both. You can use SQLite as the catalog in duck lake systems, for example. SQLite is your metadata record, DuckDB is your ingestion/scanning/aggregating/joining engine.
Most of these projects use JSON(L) files for storage, and duckdb to process them.
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.
I found with using various tricks I can get the zstd parquet to be up to 10x (or more) smaller than an equivalent Postgres table - but obviously the exact compression ratios will depend on the kind of data you have and how well your Postgres table is normalized.
We didn't know that for GP3 disks, you can increase not only IOPS but also Read/Write Throughput [1] which by default is 125 MB/s. So by default we were not seeing the performance we expected.
Once we increased the throughput of the EBS, it was amazing. So if you are not seeing the performance you read about online when using DuckDB, it may be something like that.
[1] https://docs.aws.amazon.com/emr/latest/ManagementGuide/emr-p...
Is Amazon running on super outdated legacy networking?
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 getting metrics on Claude skills usage and then dive into use-cases by looking at the transcripts
Other engineers that had never touched DuckDB were so impressed with how easy it is for AI agents to write queries on our dataset
I agree, and the dirty (not so) secret big data providers like Snowflake try to hide: the majority of your work is not big data and WILL fit on your local machine. My last company was spending $2M/yr on contract with Snowflake, and another million between Fivetran and Matillion. Of the 1200 clients using analytics maybe 2 had enough data to warrant "infinite scalability" and a dozen wanted Snowflake because they already had corporate warehouses in Snowflake (they probably didn't need it either). Turns out the Extract and Load could be handled by bog-standard C# code and a bunch of SQL, while almost everyone was better off with a DuckDB database running locally, often in the browser. You've probably heard YAGNI before (You Ain't Gonna Need It) but it's even more likely with "Big Data". #SmallDataConvert
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.
Nice! How do you set things up so that your engineers's claude code sessions upload to S3? Thanks for the help in advance
We added that to the managed settings for our Claude instance as a “base” plugin and provision it to all machines using JAMF
A non-enterprises version of that would be to add this hook in your main repository’s .claude folder
It also works great for data that doesn't fit on my laptop.
duckdb is becoming a kind of data superglue between a lot of data ecosystems (GIS, observability, analytics, lakehouses, object storage, etc) that don't talk to each other typically, and it's worth checking out in 2026.
* https://github.com/duckdb/extension-template * https://duckdb.org/community_extensions/
I'm not very good at C++, but coupled with the extension template and codex I got a basic version of my extension working within an hour. Go for it!
UPDATE my_table
SET x = file1.x,
y = file2.y
FROM 'first_file.csv' file1
LEFT JOIN 's3://my_bucket/second_file.parquet' file2
ON file1.id = file2.id
WHERE mytable.id = file1.id;This was a major factor in my initial adoption. Since then it has stuck because it’s also absurdly capable, versatile, and fast.
If it wasn’t so easy to use I suspect I wouldn’t have adopted it when I did. The ergonomics are crazy. It still impresses me regularly.
DuckDB is fast for some specific workloads. If you use it for most other things, it is at least an order of magnitude slower than SQLite. It also has some limitations in terms of what SQL it will currently run (e.g. I immediately ran into an issue with recursive queries). That will probably get better with time.
[1] If you search HN for "sqlite" and "duckdb" you get 4,310 hits and 2,398 hits respectively. That's a very heavy skew, considering SQLite is everywhere and had been around for a quarter century, while DuckDB effectively appeared on the scene two years ago.
SQLite is awesome and I would love to see more posts about it, but the reality is one of the major reasons it's awesome is the no-drama/stability/it just works. DuckDB is seeing a lot of development on many fronts so there's a lot more to learn and talk about right now.
Yes, it's specifically promoted as DBMS for OLAP workload. And it's usually compared to ClickHouse, another analytical DBMS. So people who use it know why it's good.
duckdb is ~7 years old by now. it was quite popular long before it became 1.0. heck, even motherduck has been founded 4 years ago.
> it's optimized for the kind of queries that scan millions of rows to filter, aggregate, and join — not the kind that look up a single record by primary key
This is where Arrow wins I think. Arrow CPP for example has very portable builds and the C interface is very usable for building bindings.
DuckDB is excellent, but it’s more a black box than a library.
Edit: after a conversation with a robot, it would seem that the DuckDB and ArrowCPP C APIs are complimentary, so it's very possible to have Arrow CPP and DuckDB to coexist in an app, each with its own strength. Arrow CPP doen't have a simple SQL story for example.
So being more specific, I don't know how I could get a static build of DuckDB to work with Parquet and httpfs (i.e. query S3) working in an app store environment. It was a day's work to get Arrow CPP to call back into Swift for the transport layer.
However I do now see that DuckDB recently provided an extension point for providing your own transport layer, so my point might well be moot for that reason [2].
[1] https://github.com/duckdb/duckdb/issues/16190 [2] https://github.com/duckdb/duckdb/pull/17464
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.
Still a bit raw, but getting there
Not sure DuckDB will provide any benefit to my specific case, but I'll check it out.
Which can outperform a generic solution like this of course, but it’s not less work to make faster for most cases.
Also duckdb can give you access to an in memory representation (e.g. `fetch_arrow_table()`) so you have less “language data structure wrapping” overhead. And you can do filtering yourself on that. In most cases the “where” statements will win though.
I still need to wrap my head around how that works, but it's a fascinating development.
But until then, separate tools for separate purposes isn't a bad place to be when those tools are both fantastic.
> ..In-process means there's no server. You don't connect to DuckDB; you load it as a library inside your program, the same way you'd load NumPy or Polars
Does it mean it can perform all statistical computations as well if I want to use for algo trading?
create pgsql.table as (select * from mysql.table)
all data types automatically inferred. you dont have to define it manually.
bonus: latest version already support pager. proTips: use pspg.
Can you expand upon it? You mean claude code use it to store its memory/state or it can do business queries using DuckDB.
You can also write a skill that CC can re-use if you're analyzing a lot of similar data sets with minor variance.
Better perf + SQL is that mostly it?
It has connectors for Postgres & other stores, so I find it faster to connect to a Postgres instance, pull all of the data from a table (even if the table is like 50GB - if you have 30 cores on the machine it will pull from Postgres using 30 cores in parallel, so it will only take a minute or two) - and then any analytical queries on the data are 10+ times faster in DuckDB over native Postgres (GROUP BY, regexp_replace, count(distinct...) etc).
There are other embeddable options out there but I found DuckDb fit better for the potentially massive datasets, and also because of how naturally it ingests the types of data we work with, some of its unique features, and how trivial it was to learn and integrate with the project.
Otherwise I use it almost daily for doing guardrailed data exploration with LLMs. I prefer SQL over random DSLs in AWS or Sentry or what have you. I’ll ingest the data I need and just run SQL against it. I mentioned in another comment that I’ll tend to store more useful data (especially data I export routinely, like infra cost reports) on S3 and use a Rill instance to do basic exploration in a GUI (it will query remote parquet files).
THis will give you some experience and you'll start to see applicable problem spaces for DuckDB in product areas, especially anything with BI or DW.
* fastapi + duckdb + parquet for the backend for a relatively high profile website
* wasm duckdb + react for a few visualization websites
* yaml driven ETL from lots of sources, principally ugly spreadsheets, into usable data. More T than E or L really
I do a lot of experiments with regexes, and if you get used to the RE2 syntax that DuckDB uses, you can see up to 10-100x uplift in terms of speed compared to Postgres on things like regexp_matches(), regexp_extract(), etc (depending on query/table/machine specifics). It has quite powerful scripting with custom Macros, fixes a lot of annoyances of SQL for me compared to Postgres.
I think if you have access to a machine with a lot of RAM / cores and a beefy data set, then it's basically like a RAMdisk version of Snowflake running locally on your machine.
(and of course the fact that it makes it convenient to read CSV/parquet, read/write from S3, etc) - it's a very ergonomic tool.
"10-100x uplift in terms of speed compared to Postgres on things like regexp_matches()"
was about, so I checked, and DuckDB's regexp_matches() is not the same as PostgreSQL's regexp_matches(). DuckDB's version "Returns true if string contains the regexp pattern, false otherwise." [1] while PostgreSQL's "returns a set of text arrays of matching substring(s)" [2].
I think the closest think in PostgreSQL to DuckDB's regexp_matches() is `string ~ pattern` or `regexp_like(string, pattern)`.
[1] https://duckdb.org/docs/lts/sql/functions/regular_expression... [2] https://www.postgresql.org/docs/current/functions-matching.h...
It’s not really a database in the traditional sense, there is no ACID complexity, it’s a library that lets use write SQL to query a tabular data file.
I don't think so.
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 session_id
FROM lagged
)
SELECT
user_id,
session_id,
MIN(event_time) AS session_start,
MAX(event_time) AS session_end,
COUNT(*) AS event_count
FROM sessions
GROUP BY ALL
ORDER BY user_id, session_start;
result = (
df.sort(["user_id", "event_time"])
.with_columns(
session_id=(
pl.when(pl.col("event_time").diff().is_null())
.then(1)
.when(pl.col("event_time").diff().dt.total_minutes() > 30)
.then(1)
.otherwise(0)
.cum_sum()
.over("user_id")
)
)
.group_by(["user_id", "session_id"])
.agg(
session_start=pl.col("event_time").min(),
session_end=pl.col("event_time").max(),
event_count=pl.col("event_time").count(),
)
.sort(["user_id", "session_start"])
)Other dataframe libraries work directly on duckdb also, like Ibis.
FWIW, I think SQLAlchemy does also.
In any case though, I find both the SQL and the code there equally readable.
SQL has been around since the dawn of databases. I am happy to see a trend away from pandas.
Performance is definitely one of them, but it also has inconsistent and duplicated methods, inconsistent defaults (e.g. some methods are inplace by default), copy by reference issues, I could go on.
It was an early winner in an extremely popular language. That's really the main thing going for it, but alternatives have been a long time coming.
I can write SQL everywhere. I write SQL from within my R scripts, I write SQL from within my Python scripts. SQL is THE language for querying databases, others came and went, SQL will stay with us for the next decades. Even the dbplyr people say (parapfhrased): If you can't express what you want with the tidyverse, just write your SQL and load the result as a dataframe.
For data I reference frequently, and especially which I know will grow over time, I’ve started using Rill because it makes ad-hoc exploration very smooth and low-friction.
My process tends to be something like:
1. Explore logs or some other at least somewhat structured dataset
2. Use Claude to find useful patterns and determine how I might benefit from this data in ways I wasn’t yet aware
3. See how often it’s useful for decision making
4. If it’s frequently useful, formalize it as a view in my Rill instance and refine the models to maximize their utility
There are companies that write cluster computing engines with duckdb as the byte-cruncher at their heart, but usually it's more like NumPy, Pandas or Polars on steroids. Or SQLite, but for running OLAP queries.
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
your comment would have been valid 10 years ago, maybe.
At the other end of the spectrum, working with random data on "what if?" and exploration tasks with DuckDB is fun again. it's so straightforward and fast, with tools and functions for pretty much everything.
Dangerous thing to assert. It’ll happily run SQL that works, but doesn’t necessarily correspond to intentions or unstated assumptions about the data.
It can only emit SQL and the json spec of the chart.
Since shipping I've reviewed dozens of queries and charts it produces answering the user. I'm yet to catch sonnet off guard.
For now building the 10% of the SaaS that you need still leaves you operating 100% of a new service/process
Thanks in Advance
You probably don't realize this, but you're asking one of the hardest questions when starting a business, and one of the questions others are least likely to be able to answer for you.
"finding" a niche, and connecting to the business folks inside that 'niche' is hard, and is inherently a personal journey.
There's an old writing adage, "Write about what you know", and the same adage works in business: Do business with what you know.
Your question goes into another issue that you have to resolve when building a business: going into a platform specialization necessarily means folks know about that platform or they know they need you to solve a problem they have with that platform.
In general, there are two ways out of each problem:
1. Build an ecosystem with DuckDB at its center that solves a business problem that a particular niche cares about. 2. Build a reputation solving problems with DuckDB that would attract those that know they have a problem with DuckDB.
Honestly, best of luck here, becoming successful at business is hard if you're not already in tune with why folks buy and ensuring you're selling something they want to buy from you.
There is a theory called diffusion of innovation. The simple explanation is that there are 5 different cohorts of buyers. Early adopters, visionaries, pragmatists, conservatives and laggards. Early adopters and visionaries are risk takers, who will make bold moves to achieve order of magnitude results. This is called the early market, which represents 13% of the market. The pragmatists and conservatives make up the mainstream market which is about 70%.
In order to get into the mainstream market, you need solid adoption from the early market.
To choose a niche, you need to develop a solution that fits nicely into the buyers expectations for different types of market participants. There is the market alternative and product alternative. The market alternative is the solution that owns the highest proportion of market share. The product alternative is innovative tech that challenges superiority to the market alternative.
You need to introduce a solution that fits in between those participants to stand out.
To choose a solution, go to industry trade events and talk to people about high value problems that aren’t solved by current participants. That is the purpose of industry associations, to solve difficult problems.
Visionaries and early adopters love new vendors. They will champion you through their organization if your solution will help them meet their goals.
Good luck
Define the smallest market possible or something like that. I’m not sales though.
You mean come up with a solution that fits for a usecase and write a blog or make a video of it?
If they are off the radar then how do people approach them?