Stop Loading Everything Into RAM: Python Analytics with DuckDB

Every Python data engineer hits the same wall. You write a script using Pandas to process a dataset. It works perfectly on your local machine with a 500MB sample. You deploy it to production. The dataset grows to 15GB. The container runs out of memory, and the kernel quietly kills your process.

We have spent years trying to solve this by throwing more hardware at the problem. We overprovision EC2 instances with 64GB of RAM or rewrite everything in PySpark. Both options are expensive and painful to maintain. We at Coding Macaw stopped fighting memory limits and changed our execution engine entirely.

The Problem with Eager Execution

The main issue with Pandas is eager loading. When you call pd.read_parquet(), Python attempts to load the entire uncompressed file into memory before executing a single calculation.

If you just need to group by a single column and sum the results, loading the other 50 columns into a Pandas DataFrame is a massive waste of server resources. Your script is doing heavy lifting that the storage layer should be doing.

As seen in Figure 1 below, this model creates massive spikes in RAM usage that inevitably lead to container instability when processing data on a production scale.

The Fix: In-Process SQL

DuckDB is an in-process SQL OLAP database. It runs inside your Python script exactly like SQLite does. The difference is that DuckDB is columnar and built specifically for analytical queries. It can query Parquet files directly from disk without loading the whole file into RAM.

You get the power of a data warehouse right inside your Python container.

Let us look at how this changes the code. We want to process a directory containing dozens of Parquet files representing 50GB of user events.

import duckdb

# Connect to a temporary in-memory database instance
con = duckdb.connect()

# Query the Parquet files directly from disk using SQL
query = """
SELECT 
    user_id,
    count(*) as total_events,
    sum(purchase_amount) as total_spent
FROM read_parquet('s3://my-production-bucket/events_2025/*.parquet')
WHERE event_type = 'checkout'
GROUP BY user_id
HAVING total_spent > 1000
ORDER BY total_spent DESC
LIMIT 100
"""

# Execute the query and return the result as a PyArrow table
result = con.execute(query).arrow()

print(result)

Why This Architecture Works

Look closely at the code above. We do not load the raw Parquet files into a dataframe first. We pass a file path directly to the read_parquet function inside the SQL string. DuckDB pushes the filters (only checking the 'checkout' event type) and the aggregations down to the storage layer. It scans the files, ignores the columns we do not need, and streams the relevant data through its execution engine in small batches.

The final result is a tiny PyArrow table containing exactly 100 rows. Your peak memory usage stays flat throughout the entire operation. Furthermore, PyArrow integrates perfectly with the rest of the Python ecosystem, allowing you to convert those final 100 rows to a Pandas DataFrame later if you really need to plot them.

The Takeaway

You do not always need a distributed cluster to process big data. An in-process columnar engine can handle hundreds of gigabytes on a standard server. It keeps your infrastructure simple, your deployment times fast, and your AWS bill incredibly low.

If your team is running into memory bottlenecks, drop the heavy frameworks and give DuckDB a try.

How are you handling out-of-memory errors in your analytics pipelines right now? Let me know in the comments below.