DuckDB – Data power tools for your laptop, now in Clojure (2023)

3 hours ago

DuckDB is integrated with Clojure's tech.ml.dataset (TMD) for in-memory column-major data processing.
DuckDB provides a batched C interface for inserts and queries, enabling large joins.
A 50GB CSV file with 400 million rows was loaded into DuckDB in under 2 minutes, reducing to 18GB with indexes.
Joining 400M rows with a colors table (1.4 billion rows) took 2.5 seconds on a laptop.
DuckDB supports zero-copy query pathway for efficient reduction without memory overload.
DuckDB automatically creates minmax (BRIN) indexes and ART indexes for unique keys.
DuckDB is MIT licensed, written in C++11 (~100k LOC), and has an open development model.
The integration allows functional data science on laptops without resorting to distributed clusters.

DuckDB outperforms SQLite on writes by 4x to 15x and on read cliffs by 100x on a $16/month server.
Read cliffs (maximum rows for dashboard queries) moved from 1M/100k/100k rows (SQLite) to 100M/10M/10M rows (DuckDB) for metrics, spans, and logs respectively.
DuckDB stored 1 billion metric points in 10.8 GB on disk and ingested them in under an hour.

DuckPGQ is a DuckDB community extension for graph workloads, supporting the SQL/PGQ standard (SQL:2023).
It provides high-performance, in-process graph analytics by leveraging DuckDB's efficiency.
The extension is easy to install and integrate with DuckDB, requiring no additional dependencies.

CSV is a universally supported, easy-to-generate storage format but has issues like escaping, precision loss, and lack of type safety.
Sqlite3 is recommended as a robust alternative, offering low overhead, type safety, accessibility, and public domain status.
Other formats like Parquet, HDF5, JSON, CBOR, and LMDB have specific use cases but often fail in universal accessibility or performance.

PMB is a local memory system for AI agents that stores decisions, lessons, and project facts in a SQLite file you own.
It works offline with no API keys or cloud, providing memory recall in about 35 ms.
PMB automatically injects relevant memory before the agent thinks and journals work afterward, without requiring the agent to call tools.

l is a new runtime for k4, q, and qSQL that maintains syntax compatibility with existing code.
It executes on a modern engine optimized for compressed vectors, SIMD, and automatic parallelism, reducing data movement.
Performance benchmarks show significant speedups, such as 3.57x geomean in master suite and 3.16x total query speedup versus DuckDB.

DuckDB outperforms SQLite on writes by 4x to 15x and on read cliffs by 100x on a $16/month server.
Read cliffs (maximum rows for dashboard queries) moved from 1M/100k/100k rows (SQLite) to 100M/10M/10M rows (DuckDB) for metrics, spans, and logs respectively.
DuckDB stored 1 billion metric points in 10.8 GB on disk and ingested them in under an hour.

DuckPGQ is a DuckDB community extension for graph workloads, supporting the SQL/PGQ standard (SQL:2023).
It provides high-performance, in-process graph analytics by leveraging DuckDB's efficiency.
The extension is easy to install and integrate with DuckDB, requiring no additional dependencies.

CSV is a universally supported, easy-to-generate storage format but has issues like escaping, precision loss, and lack of type safety.
Sqlite3 is recommended as a robust alternative, offering low overhead, type safety, accessibility, and public domain status.
Other formats like Parquet, HDF5, JSON, CBOR, and LMDB have specific use cases but often fail in universal accessibility or performance.

PMB is a local memory system for AI agents that stores decisions, lessons, and project facts in a SQLite file you own.
It works offline with no API keys or cloud, providing memory recall in about 35 ms.
PMB automatically injects relevant memory before the agent thinks and journals work afterward, without requiring the agent to call tools.

l is a new runtime for k4, q, and qSQL that maintains syntax compatibility with existing code.
It executes on a modern engine optimized for compressed vectors, SIMD, and automatic parallelism, reducing data movement.
Performance benchmarks show significant speedups, such as 3.57x geomean in master suite and 3.16x total query speedup versus DuckDB.

Hasty Briefsbeta