DuckDB 0.8.1: 1.4B Row Join on a Laptop in 2.5s via Clojure

DuckDB's vectorized SQL engine now integrates with Clojure's tech.ml.dataset (TMD), enabling a 1.4 billion row join in 2.5 seconds on a laptop. The integration uses batched C bindings, making large-scale relational data processing feasible in a functional programming environment.

The Problem: 50GB CSV, 400M Rows

The team at TechAscent faced a common data science pain: processing a 50GB CSV with 400 million rows of transaction data. Their in-memory column-major platform, TMD, couldn't handle it directly. They needed a relational database for out-of-memory storage and fast queries, but existing options like Postgres via JDBC were clunky — row-to-column conversion through a non-batched API was inefficient.

DuckDB Enters the Scene

DuckDB, an in-process OLAP database, appeared in a GitHub issue in May 2021. By December 2021, they had minimal C bindings integrated, but all query results had to fit in memory. Two years later, DuckDB has matured significantly. The C interface now provides batched inserts and queries, enabling processing of very large joins.

Loading 50GB in Under 2 Minutes

Loading the 50GB CSV into DuckDB took 1 minute 50 seconds, compressing it to an 18GB file (including indexes). Here's the command:

$ time duckdb data.ddb 'CREATE TABLE data AS FROM "data.csv";'

That's 400 million rows in under two minutes. The resulting database includes all indexes automatically created by DuckDB.

Querying from Clojure

Accessing DuckDB from Clojure through TMD is straightforward:

(require '[tmducken.duckdb :as duckdb])
(require '[tech.v3.dataset :as ds])

(duckdb/initialize!)
(def db (duckdb/open-db "data.ddb"))
(def conn (duckdb/connect db))

(time (duckdb/sql->dataset conn "SELECT COUNT(*) AS n FROM data"))
;; "Elapsed time: 10.305756 msecs"

A count over 400 million rows returns in 10 milliseconds.

The 1.4B Row Join

Now the real test: joining 400 million transactions with a color mapping table (35,179 rows) to answer a business question. The join produces 1.4 billion rows and completes in 2.5 seconds:

(time (duckdb/sql->dataset conn "SELECT COUNT(*) FROM data INNER JOIN colors ON data.sku = colors.sku;"))
;; "Elapsed time: 2486.620275 msecs"

Then, answering "How many items of each color were sold in March 2021?" takes just over a second:

(time (duckdb/sql->dataset conn "SELECT color, COUNT(*) FROM data INNER JOIN colors ON data.sku = colors.sku WHERE data.year='2021' AND data.month='3' GROUP BY color;"))
;; "Elapsed time: 1077.723309 msecs"

Results: red 5,714,223; yellow 5,652,010; black 5,720,753; blue 5,750,846; white 5,689,916; green 5,816,655; purple 5,671,959.

Zero-Copy Streaming for Arbitrary Processing

DuckDB's integration supports a zero-copy query pathway, ideal for reducing over results without materializing them all in memory. This example streams all transactions for a specific SKU, sorted chronologically, in about a second:

(time
  (reduce (fn [eax ds] (conj eax (ds/row-count ds))) []
          (duckdb/sql->datasets conn "SELECT * FROM data WHERE sku='sku-50-5-5' ORDER BY inst")))
;; "Elapsed time: 1067.480751 msecs"

With {:reduce-type :zero-copy-imm}, the same query runs slightly faster and uses less memory.

DuckDB's Technical Tidbits

What This Means for Developers

This integration bridges the gap between functional data processing and relational SQL. You can now handle out-of-memory datasets without leaving your Clojure environment. The batched C interface is key — it avoids the performance pitfalls of row-by-row processing.

Next Steps

If you're working with large datasets in Clojure, try the tmducken integration. The full example code is in the original post. DuckDB's speed and TMD's flexibility make a powerful combination for local data science.