Optimizing the file read performance of DuckDB

Consuming data from files stored on a disk is a common pattern in the data world, and we have seen many examples where DuckDB is used to read directly from file and network paths. It’s worth understanding some of the ways we can maximize performance when reading datasets from files stored on a disk. We will be exploring the clever ways DuckDB can optimize the reading of large datasets stored in files and the techniques for arranging them on a disk to improve reading speeds.

File partitioning

We learned about Hive partitioning in Chapter 2, a technique that allows you to organize files on disk by dividing a single table into smaller logical tables based on the values of a particular column. This column is known as the partition key, which frequently takes the form of a date component, dividing up records into different time periods, such as months and years. In a similar way to how DuckDB’s BRIN indexes leverage block...