DuckDB: SQLite for analytics, not a vector database

An analytical database embedded like SQLite — no server, one file or in memory — but built for scans, aggregations and joins, not transactions: SQLite for analytics.

Where SQLite stores a row together, DuckDB stores each column separately:

row-oriented:   [id=1, name=Alice, age=30] [id=2, name=Bob, age=25]
column-oriented: id: [1,2,3]  name: [Alice,Bob,Carol]  age: [30,25,40]

SELECT AVG(age) FROM users then reads one column, not every column of every row. It also executes vectorized — chunks of around 1024 rows, not one row at a time — using the CPU cache better and allowing SIMD.

What it is good at

Querying files without importing:

1
2
3

SELECT * FROM 'sales.csv';
SELECT COUNT(*) FROM read_csv('sales.csv');
SELECT * FROM 'data.parquet';

Parquet is the strong case — often without loading the whole dataset into memory, making DuckDB a local engine over data lakes and Iceberg, Delta Lake, Hive and Parquet layouts.

The SQL is analytical: GROUP BY aggregations, joins, window functions such as ROW_NUMBER() OVER (PARTITION BY country ORDER BY score DESC), and CREATE TABLE clean_data AS SELECT … for local ETL. In Python it reads Pandas, Polars and Arrow data directly:

1
2

import duckdb
duckdb.sql("SELECT * FROM my_dataframe WHERE value > 100")

Throughput: hundreds of millions of rows on a laptop is realistic for scans, filters, joins and aggregations — near much larger systems.

What it is not

Not a web application database: user accounts, carts, banking, high-concurrency OLTP belong in PostgreSQL, MySQL or MariaDB. Writes are supported but not the design centre; many simultaneous writers are outside its remit.

Not a vector database: array types, embedding storage and similarity-search extensions exist but are peripheral next to Milvus, Qdrant or Weaviate. Production retrieval with millions of embeddings wants a dedicated vector store; the two are complementary.

The common split: SQLite for application data, DuckDB for reporting, log analysis and Parquet.