DuckDB with Python Guide: Query Multi-Gigabyte CSV/Parquet Files at Lightning Speed Without OOM Errors - ITFROMZERO - Share tobe shared! Need to process multi-gigabyte CSV or Parquet files on low-spec hardware? Learn how to use DuckDB in Python for blazing-fast analytics without memory crashes.

Table of Contents

A 2:00 AM Out-of-Memory Nightmare

My PagerDuty alarm went off repeatedly in the middle of the night. The transaction log aggregator bot on our staging server had just been taken down by the Linux OOM Killer. Checking the terminal revealed a familiar culprit: a Python script was using Pandas to load an 8.5GB CSV file (~35 million rows) directly into a VPS with only 4GB of RAM.

Previously, a common workaround was dumping the data into SQLite and running SQL queries. However, for aggregation tasks (GROUP BY, AVG, COUNT DISTINCT) across tens of millions of records, SQLite crawls due to its row-oriented storage. Pandas, on the other hand, immediately runs out of memory. This is where DuckDB comes to the rescue: an embedded database as lightweight as SQLite, but built with exceptional analytical processing (OLAP) power.

How Does DuckDB Differ from SQLite?

Many refer to DuckDB as “the SQLite for Analytics.” It requires no daemon service, zero user/password configuration, and runs directly inside your Python process.

DuckDB’s true power stems from three core architectural pillars:

Columnar Storage: Unlike SQLite, which scans every row entirely, DuckDB reads only the required columns. When computing SUM(revenue) across 30 million rows, the engine only reads the revenue column from disk and ignores everything else, cutting I/O by 80–90%.
Vectorized Execution Engine: Data is processed in vectorized chunks (around 2,048 values) that fit entirely within CPU L1/L2 cache, fully utilizing SIMD instruction sets on modern processors.
Out-of-Core Processing (Streaming to Disk): If a dataset exceeds physical RAM, DuckDB automatically chunks and spills temporary buffers to disk rather than crashing your application.

Hands-on DuckDB with Python

1. Setting Up the Environment

DuckDB is packaged as a standalone binary with no external C++ dependencies:

pip install duckdb pandas pyarrow

2. Querying CSV / Parquet Files Directly from Disk

You don’t need to INSERT data into a database first. DuckDB can read and scan files directly from disk:

import duckdb

# Initialize a temporary in-memory connection
con = duckdb.connect(database=':memory:')

# Scan an 8.5GB CSV file directly without loading everything into RAM
query_csv = """
SELECT 
    status_code,
    COUNT(*) AS total_requests,
    ROUND(AVG(response_time_ms), 2) AS avg_latency
FROM 'server_logs.csv'
GROUP BY status_code
HAVING total_requests > 1000
ORDER BY total_requests DESC;
"""

# Return results as a DataFrame in just 2-3 seconds
df_result = con.execute(query_csv).fetchdf()
print(df_result)

DuckDB automatically infers data types and leverages all available CPU cores for parallel execution.

3. Zero-Copy Integration with Pandas DataFrames

If you already have a DataFrame in memory, DuckDB can query it directly via Apache Arrow memory pointers without any data copying overhead.

import pandas as pd
import duckdb

# Mock orders DataFrame
orders_df = pd.DataFrame({
    'order_id': range(1, 6),
    'customer_id': ['C101', 'C102', 'C101', 'C103', 'C102'],
    'amount': [250.0, 120.5, 310.0, 89.9, 450.0]
})

# DuckDB automatically discovers the orders_df variable in the local scope
query = """
SELECT 
    customer_id,
    SUM(amount) AS total_spent,
    COUNT(order_id) AS total_orders
FROM orders_df
GROUP BY customer_id
ORDER BY total_spent DESC;
"""

summary = duckdb.sql(query).df()
print(summary)

4. Persistent Storage

To persist analytics results to disk for reuse across other processes, simply replace ':memory:' with a specific file path:

import duckdb

con = duckdb.connect('analytics.duckdb')

# Aggregate multiple Parquet files matching a pattern into a single table
con.execute("""
CREATE TABLE IF NOT EXISTS daily_metrics AS 
SELECT * FROM 'logs/metrics_2026_*.parquet';
"""
)

total_rows = con.execute("SELECT COUNT(*) FROM daily_metrics;").fetchone()[0]
print(f"Successfully loaded {total_rows:,} records.")

con.close()

5. Controlling RAM Usage on Resource-Constrained Servers

On resource-constrained servers, it is best practice to explicitly configure memory caps and thread limits:

import duckdb

con = duckdb.connect('warehouse.duckdb')

# Limit maximum RAM to 2GB and use 4 CPU threads
con.execute("SET max_memory = '2GB';")
con.execute("SET threads = 4;")

# Export aggregated results directly to a ZSTD-compressed Parquet file
con.execute("""
COPY (
    SELECT 
        date_trunc('day', timestamp) AS report_date,
        user_id,
        COUNT(event_id) AS purchase_count,
        SUM(total_amount) AS revenue
    FROM 'raw_events_*.csv'
    WHERE event_type = 'PURCHASE'
    GROUP BY 1, 2
) TO 'daily_purchase_summary.parquet' (FORMAT PARQUET, COMPRESSION ZSTD);
"""
)

print("Processing complete! Safely exported to Parquet without exceeding 2GB RAM.")

When Should You Choose DuckDB, SQLite, or PostgreSQL?

Each database engine is tailored to solve a distinct problem:

Choose SQLite for: Mobile apps, local desktop apps, storing configurations, or lightweight CRUD systems (OLTP). SQLite is optimized for individual row-level read and write operations.
Choose PostgreSQL / MySQL for: Web applications requiring concurrent multi-client connections over a network, granular user permissions, and strict ACID transaction guarantees.
Choose DuckDB for: Statistical analytics, processing large log files (CSV, Parquet, JSON), building local ETL pipelines in Python, or serving as an analytical query engine for dashboards without spinning up a complex Spark cluster.

Conclusion

Replacing Pandas with DuckDB eliminated OOM crashes in our log processing pipeline while slashing runtime from 15 minutes to just 8 seconds on the exact same budget VPS. If you are struggling with multi-gigabyte datasets on your local machine, DuckDB is undoubtedly worth adopting today.