I just started doing this last week!

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!

Just curious whether one can earn money making these exts?

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).

>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

Nice! How do you set things up so that your engineers's claude code sessions upload to S3? Thanks for the help in advance

Can you please expand more on the claude analysis part. What exactly you analysed and what outcome it helped with ?

Yup. And an extra benefit that you can treat any file like a table, so you can also do something like

  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;

What do you use it for? I’m perpetually interested in using DuckDB, but it doesn’t seem to do anything I need.

I can't confirm this, I have several instances which have statically linked extensions...

DuckDB has been probably my most used tool in 2026 - if you're comfortable with SQL it's incredible at quickly prototyping and slicing / dicing data.

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.

Here is the thing, it’s a write only single file format. If you need to run analytical queries it’s optimized for reading, you just open a file and query for the parts you want. If you have multiple clients that read and write data to the database, you should use postgresql.

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.

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

It depends on your task. In analytics where you need to scan lots of data points within few columns, then columnar storage is very much the best. But for transactional workloads where you have to deal with specific entities, row based would be more advantageous. There are hybrid systems that try to be both at the same time but in my experience they end not doing either very well.

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.

You’d end up implementing your own home grown version of hash join and query pushdown (skipping parquet row groups entirely) etc and your own home grown heuristics in selecting the right one (planning)

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.

The SELECT machinery is the product with databases! SQL often the shortest description of the processing logic, and the database has an efficient local execution engine that can prune/reduce data read based on the plan. Very hard to match in app, especially when joins get involved.

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.

What an incredible way to build software

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 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"])
  )

Pandas has lots and lots of problems.

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.

Why would you prefer Python and Pandas over good old SQL? Pandas is so verbose and hard to debug, most of the times struggle to be performant on small datasets.

SQL has been around since the dawn of databases. I am happy to see a trend away from pandas.

I wrote a blog post a while back to address this question here: https://www.robinlinacre.com/recommend_duckdb/

> duckdb is so nice coupled with claude code

Can you expand upon it? You mean claude code use it to store its memory/state or it can do business queries using DuckDB.

I wouldn’t see it as a replacement.

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.

OLAP vs OLTP. Sure you could use one for the other, but they have ideal use cases. Updating a single record in SQLite is going to be more efficient than doing the same in DuckDB.

They seem similar at a glance but they’re quite different. You can think of SQLite as a transactional database while DuckDB is better used as an analytical database.

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.