When to use DuckDB for analytical workloads

Context

Every analytical project faces a decision: where to process the data. The conventional options — PostgreSQL for structured queries, Pandas for flexible analysis — both have known limits. PostgreSQL requires server provisioning and schema management. Pandas crashes on datasets exceeding available memory.

For medium-scale analytical workloads (1M to 100M rows), neither is ideal.

Why DuckDB

DuckDB fills the gap between in-memory DataFrames and full database deployments. It runs in-process, uses columnar storage, supports full SQL, and handles datasets larger than available RAM by leveraging disk-based spill-to-storage. No server to configure, no connection pool to manage.

Production pattern

The most effective pattern I have used is DuckDB as a transformation engine positioned between raw data storage and the analysis layer:

import duckdb

con = duckdb.connect(':memory:')

con.execute("""
  CREATE VIEW analytics AS
  SELECT
    region,
    date_trunc('month', date) as month,
    sum(revenue) as total_revenue,
    count(distinct customer_id) as customers
  FROM read_parquet('data/*.parquet')
  GROUP BY region, month
""")

result = con.execute("SELECT * FROM analytics WHERE total_revenue > 100000").fetchdf()

This pattern keeps raw data in Parquet files (cheap storage, portable) and uses DuckDB only for the transformation and query layer. The data never enters a traditional database unless it needs to serve a production API.

When not to use DuckDB

DuckDB is not a replacement for PostgreSQL. It does not support concurrent writes, has no server mode for network access, and lacks user authentication. If multiple services need to query the same dataset simultaneously, or if the data must be updated transactionally, use PostgreSQL.

I use DuckDB for:

• Analytical pipelines that produce reports or dashboards
• Exploratory analysis on datasets that exceed Pandas memory limits
• Transformation layers in batch ETL where the output feeds a serving database

I use PostgreSQL for:

• Production APIs that serve data to downstream consumers
• Systems requiring concurrent read/write access
• Data that must survive process restarts

Measured impact

In the NetTension project, DuckDB processed 1.8M+ Eurostat indicators joined with 41,937 rows of regulatory data. Total query time for the full analytical pipeline: under 30 seconds on a laptop. Equivalent Pandas operations would have required chunking and manual memory management. Equivalent PostgreSQL would have required provisioning a database instance.