3.9 Bigger-than-memory data

What this is and why it exists

Datasets outgrow a laptop's memory long before they justify a cluster, and the gap between those two points is where most real work happens. A twenty-gigabyte file on a sixteen-gigabyte machine is an ordinary Tuesday with the right tools, and a week-long distributed detour with the wrong instinct. This topic is those tools, and the storage format that makes all of them work better.

The vocabulary

• Chunking — reading a file in pieces and processing each in turn.
• Streaming — processing a piece at a time without ever holding the whole.
• Lazy evaluation — building up a description of the work and running it only when a result is demanded.
• Query optimiser — the component that rearranges that description into something cheaper before running it.
• Columnar storage — values stored column by column rather than row by row.
• Parquet — the standard columnar file format, with types and compression built in.
• Projection pushdown — reading only the columns a query mentions.
• Filter pushdown — skipping parts of a file that cannot contain matching rows.
• Partitioning — splitting data into directories by a column, so whole files can be skipped.

The mental model

Three levers, in order of how much they buy for how little effort.

Store it columnar. This is the enabling trick and it is one line of code. A row-oriented file must be read entirely to reach any column; a columnar one is stored column by column, so a query touching three of forty columns reads three-fortieths of the bytes. DuckDB describes doing exactly this — "projection pushdown into the Parquet file itself" meaning "only the columns required for the query are read" — along with "filter pushdown into the Parquet reader", which lets it skip sections outright using the statistics the format stores. Add partitioning by a column you filter on constantly, such as date, and whole files never open. Converting a CSV to Parquet once, at the start of a project, frequently removes the memory problem before it appears.

Then process in pieces rather than all at once. pandas can read a file in chunks, and the pattern is old and reliable: read a chunk, reduce it to what you need, discard it, accumulate. It works for any file of any size, and its limitation is that it is your own bookkeeping — you are the one making sure the per-chunk results combine correctly, which is fine for a sum and awkward for a median.

Then let a lazy engine do the bookkeeping. Polars builds a query plan rather than executing each line: it "takes each line of code, adds it to the internal query graph and optimizes the query graph", and the work happens when you call collect. Two consequences follow, and both are the point. The optimiser can push your filters and column selections down into the file read, so data you filter out is never loaded at all — an ordering you would have had to arrange by hand. And for data beyond memory, Polars can "process the data in batches using streaming mode", by passing the streaming engine to collect.

DuckDB is the other answer and often the shortest one: it runs SQL directly over local files, with no server and no import step. Its documentation notes that reading a Parquet file needs no function call at all — "if your file ends in .parquet, the function syntax is optional" — and that "multiple files can be read at once by providing a glob or a list of files". So a directory of a hundred Parquet files is one query, and the previous topic's SQL is immediately useful on files sitting on your disk.

Then the instinct to resist, which the module description names outright. A twenty-gigabyte file is not a cluster problem. Reaching for a distributed framework brings a cluster to configure, a different execution model to learn, serialisation costs, and debugging that happens somewhere else — a week of detour for a query that DuckDB answers in one line on the machine you already have. Distributed tools earn their place when data genuinely does not fit on one machine or when many people must query the same store concurrently. Below that, the honest order is: columnar first, then lazy or SQL over files, then a bigger machine — renting one for an afternoon costs less than the detour — and only then a cluster.

What you should now be able to explain or do

Say what columnar storage changes and name the two pushdowns it enables. Convert a project from CSV to Parquet and say what you gained beyond speed. Process a file in chunks and name the bookkeeping you are now responsible for. Explain what a lazy engine does with your code before running it, and the two benefits that follow. Query a directory of files with SQL and no import step. Give the honest escalation order and say where a cluster actually begins to earn its cost.

Check yourself