Zero‑Copy Parquet in DuckDB: Boost Data‑Science Performance without the Memory Cost

The Problem: Memory‑Intensive Parquet Queries

Data‑science workflows often start by loading a Parquet file into a dataframe, then applying transformations or aggregations. In a typical scenario, a 5 TB Parquet table is read into memory, which can exhaust a laptop’s RAM or a cluster’s node memory. Even when a user only needs a handful of columns, many engines still materialise the whole file or perform a full scan before pruning.

Traditional solutions involve using a distributed engine (Spark, Flink) or a columnar database that supports zero‑copy reading. However, setting up a cluster, managing dependencies, and handling distributed execution can be overkill for many use cases.

DuckDB’s Zero‑Copy Solution

DuckDB, a lightweight analytical database, introduced a zero‑copy Parquet reader in release 0.8.0. The reader streams data directly from the Parquet file into the query engine’s vectorised pipeline, avoiding an intermediate in‑memory copy. Column pruning is applied at the file level, so only the requested columns are read. The result is:

• Fast I/O: the engine reads only the necessary pages.
• Low memory: RAM usage scales with the size of selected columns, not the entire file.
• Ease of use: embed in Python with a single call to duckdb.connect() and run SQL.

Under the hood, DuckDB uses the parquet::read_parquet API, which maps Parquet columns to Arrow arrays without copying data. The execution plan remains columnar and vectorised, similar to what Spark SQL achieves.

A Concrete Python Example

Below is a minimal, reproducible example. Replace sample.parquet with a path to a real Parquet file.

import duckdb

# Connect to an in‑memory DuckDB instance
con = duckdb.connect()

# Read only the columns we need – here we assume a large table with columns a, b, c, d
query = """
SELECT a, b
FROM read_parquet('sample.parquet')
WHERE c > 100
LIMIT 10
"""

# Execute and fetch results
result = con.execute(query).fetchall()
print(result)

Key points:

• read_parquet is a DuckDB function that returns a virtual table.
• Only columns a and b are read; c is used only for filtering and is not materialised.
• The operation runs in a single process without spawning a JVM or a Spark driver.

Verifying Zero‑Copy and Performance

DuckDB provides a profiler that exposes memory usage per operator. To confirm the zero‑copy behaviour, run the following snippet:

import duckdb
con = duckdb.connect()

# Start the profiler
con.execute('PRAGMA profiler = ON;')

# Run the same query
con.execute("SELECT a, b FROM read_parquet('sample.parquet') LIMIT 5;")

# Fetch profiler output
prof = con.execute('PRAGMA profiler').fetchdf()
print(prof[['operator', 'bytes_allocated']])

The bytes_allocated column will show memory consumption for each operator. In a zero‑copy scenario, the ParquetReader row should have a value close to the size of the selected columns, not the full file.

Another quick check is to monitor system memory before and after the query using psutil or OS tools. The difference should be minimal compared to loading the entire file into a dataframe.

Trade‑offs and Limitations

While zero‑copy reading is powerful, it is not a silver bullet. Consider the following:

• Selected columns still consume RAM: If you query a wide column (e.g., a JSON blob), the memory footprint will be proportional to that column’s size.
• Schema evolution: Adding new columns to a Parquet file after it has been queried may cause the engine to re‑plan the query. Explicitly selecting columns can mitigate surprises.
• File format compatibility: The feature currently supports Parquet 1.0.0+; older or experimental codecs may not be handled.
• Parallelism limits: Zero‑copy reading is single‑threaded by default. For massive parallel scans, a distributed engine may still be preferable.

Despite these caveats, for most data‑science pipelines that focus on a subset of columns from large Parquet tables, zero‑copy reading offers a substantial win.

Actionable Takeaway

To adopt DuckDB’s zero‑copy Parquet reader in your workflow:

1. Ensure you run DuckDB 0.8.0 or newer: import duckdb; print(duckdb.__version__).
2. Embed DuckDB in your Python script or Jupyter notebook with duckdb.connect().
3. Use read_parquet in your SQL queries and prune columns aggressively.
4. Validate memory usage with PRAGMA profiler or OS tools.
5. Document any schema changes to avoid unexpected scan costs.

By following these steps, you can process terabyte‑scale Parquet datasets on a single machine, keeping memory usage low and query latency fast.