DuckDB: File Formats and Performance Optimizations
3 min read

DuckDB: File Formats and Performance Optimizations

455 words
Categories:
Databases
Tags:
#duckdb #sql #optimization #parquet #csv #performance

Lately I’ve been working quite a bit with DuckDB, and one of the things that interests me most is understanding how to optimize performance according to the file format we’re using.

It’s not the same working with Parquet, compressed CSV, or uncompressed CSV. And the performance differences can be dramatic.

Let’s review the key optimizations to keep in mind when working with different file formats in DuckDB.

Parquet: Direct Query or Load First?

DuckDB has advanced Parquet support, including the ability to query Parquet files directly without loading them into the database. But when should you do one or the other?

In Favor of Direct Parquet Query

Basic statistics available: Parquet uses columnar storage and contains basic statistics like zonemaps. This allows DuckDB to apply optimizations like projection pushdown and filter pushdown. Workloads combining projection, filtering, and aggregation work very well on Parquet.

Storage considerations: Loading data from Parquet requires approximately the same space as the DuckDB file. If disk space is limited, direct query on Parquet is a good option.

Against Direct Parquet Query

Lack of advanced statistics: The DuckDB database format has hyperloglog statistics that Parquet doesn’t have. These improve cardinality estimation accuracy, especially important in queries with many joins.

Tip: If DuckDB produces a suboptimal join order on Parquet files, try loading the Parquet into DuckDB tables. The improved statistics will help obtain a better join order.

Repeated queries: If you plan to execute multiple queries on the same dataset, it’s worth loading the data into DuckDB. Queries will always be somewhat faster, amortizing the initial load time.

Row Group Size in Parquet

DuckDB works better with Parquet files that have row groups of 100K-1M rows each.

Why? Because DuckDB can only parallelize over row groups. If a Parquet has a single giant row group, it can only be processed by one thread.

Recommendations Summary

Parquet

  • ✅ Direct query for projection/filter/aggregation workloads
  • ✅ Load into DuckDB for many joins or repeated queries
  • ✅ Use row groups of 100K-1M rows
  • ✅ Keep files between 100 MB - 10 GB
  • ✅ Prefer Snappy/LZ4/zstd over gzip

CSV

  • ✅ Read .csv.gz directly (DO NOT uncompress first)
  • ✅ Disable sniffer for many small files with same schema
  • ✅ Consider converting to Parquet for analytical workloads

Conclusion

What has struck me most working with DuckDB is that traditional intuitions about data processing sometimes don’t apply.

The .csv.gz case is the perfect example. You’d think uncompressing first would be faster, but the reality is that reading the compressed file directly is faster.

And that’s key when working with large data. Load and process times matter, and understanding these optimizations can make a substantial difference in your data pipeline performance.

References

Comments

Latest Posts

11 min

2159 words

Every query starts with a plan. Every slow query probably starts with a bad one. And more often than not, the statistics are to blame. But how does it really work?

PostgreSQL doesn’t run the query to find out — it estimates the cost. It reads pre-computed data from pg_class and pg_statistic and does the maths to figure out the cheapest path to your data.

In the ideal scenario, the numbers read are accurate, and you get the plan you expect. But when they’re stale, the situation gets out of control. The planner estimates 500 rows, plans a nested loop, and hits 25,000. What seemed like an optimal plan turns into a cascading failure.

4 min

767 words

The problem: httpfs ignores your environment variables

If you work with DuckDB and the httpfs extension to read remote Parquet files, CSVs from S3, or any HTTP resource, you probably assume that the HTTP_PROXY and HTTPS_PROXY environment variables work just like every other tool. Curl respects them. wget respects them. Python requests respects them. Node.js respects them.

DuckDB does not.

I ran into this while working in a corporate environment with a mandatory proxy. I had a script reading Parquet files from Google Cloud Storage using httpfs, and it simply would not work. No clear error, no descriptive timeout, just silence. Meanwhile, a curl to the same resource with the same environment variables returned data without issue.

5 min

911 words

A few months ago, when Anthropic launched their MCP (Model Context Protocol), I knew we’d see interesting integrations between LLMs and databases. What I didn’t expect was to see something as polished and functional as ClickHouse’s AgentHouse so soon.

I’m planning to test this demo soon, but just reading about it, the idea of being able to ask a database questions like “What are the most popular GitHub repositories this month?” and getting not just an answer, but automatic visualizations, seems fascinating.

9 min

1802 words

Apache Iceberg v3: Revolution in Geospatial Data for Modern Analytics

The recent ratification of the Apache Iceberg v3 specification marks a significant milestone in the open data ecosystem, especially in the realm of geospatial data. This update not only consolidates Iceberg as the leading standard in open table formats, but introduces native geospatial capabilities that promise to transform how we handle location and mapping data at scale.

The Challenge of Geospatial Data in the Current Ecosystem

Before diving into Iceberg v3’s innovations, it’s crucial to understand the fragmented landscape that existed in geospatial data handling. As Jia Yu, Apache Sedona PMC Chair and Wherobots Co-Founder notes, the final functionality is the result of exhaustive community research that reviewed numerous projects and technologies with geospatial support.