Less I/O

TL;DR

Here's the whole post in one breath: nearly every optimization you'll ever reach for is a version of one idea — Less I/O — move and store less data. And you rarely build it yourself. Run one ordinary query and dozens of inherited optimizations fire beneath you — the planner skips files, the format reads a single column, the CPU cache keeps the hot bytes a nanosecond away — each invented by people you'll never meet, over decades. Every one of them is doing the same thing.

In practice, that one idea just wears three hats:

• Data movement: ship code to data, vectorize, prune partitions → move fewer bytes.
• Data storage: columnar layout, Parquet encodings, tight ClickHouse types → store fewer bytes.
• Data model: pre-aggregate, denormalize for the query, partition by access pattern → look at fewer bytes.

That's the entire idea — skim the diagram and you've got the gist. The rest of this post is the tour: why moving data is so expensive, and how each layer actually pulls it off, with examples from Hadoop, SIMD, Parquet, ClickHouse, a banking data model, a Spark crash that turned on this exact principle — and an honest caveat on the rare day you're CPU-bound, not I/O-bound.

01The cost of moving a byte

Here is the one idea that powers everything else. A byte of data can live in many places — a CPU register, the tiny L1 cache, the larger L2 and L3 caches, main memory (RAM), a local SSD, or across the network on another machine's disk. Computing on that byte requires bringing it close to the CPU.

The catch: the cost of fetching a byte grows by orders of magnitude — in big multiplicative jumps — the further away it lives. Not 2× further, not 10× — often thousands to millions of times slower. This isn't a minor tuning detail; it's the dominant force in pipeline performance.

Log-scaled bars are honest but they hide the shock. So here's the same data rescaled to human time: imagine an L1 cache hit takes 1 second. Everything else stretches to match.

02Less I/O in data movement

The first lever is the obvious one: physically move fewer bytes, over shorter distances, more efficiently. Three classic techniques.

Data locality — move the code to the data

Code is tiny. Data is huge. So when Hadoop pioneered "big data," its key insight was to ship the computation (a few KB of code) to the machines that already hold the data (gigabytes on local disk) — instead of streaming terabytes across the network to a central compute node. Spark, Presto, and every modern engine inherit this idea.

Vectorization (SIMD) — touch the data once, do more per touch

Once data is in the CPU, there's still overhead in processing it one value at a time — every operation carries a per-instruction cost. SIMD (Single Instruction, Multiple Data) amortizes that cost by letting one instruction operate on a whole batch of values — 4, 8, 16 at a time — instead of one. Columnar engines love this: a column is just a tight array of identical-type values, which is exactly what SIMD wants to chew through.

Partition pruning — never read what you can skip

The cheapest read is the one that never happens. If a table is physically partitioned by, say, event_date, then a query filtered to one day can skip entire folders of files. The engine reads metadata, realizes 11 of 12 months are irrelevant, and never opens them.

03Less I/O in data storage

If movement is about where bytes go, storage is about how many bytes exist — and, just as important, in what order you can read them. Two physical facts run this section: reading sequentially is far cheaper than reading randomly, and reading only the columns you need beats reading whole rows. Which layout actually wins depends entirely on how you query.

Sequential reads beat random reads — by 10–100×

Before columns vs rows, there's a more primitive fact. A sequential read streams contiguous bytes: the device does one big transfer, the OS prefetches ahead, and the per-request overhead is amortized across megabytes. A random read scatters tiny fetches across the medium — on a spinning disk every jump pays a ~10 ms seek, so the penalty is brutal (often 100×+); on an SSD there's no seek, but you still lose prefetching and pay per-operation overhead, so it's smaller but real (~2–10×). Across the spectrum, expect one to two orders of magnitude.

So the whole game of physical storage is one move: turn your dominant access pattern into a long sequential scan of just the bytes you need. Partitioning, sort/clustering keys, big row groups, and the row-vs-columnar choice are all ways to play it.

Row vs columnar: it depends on the access pattern

There's no universal winner here — it's a battle decided by usage, and each format is really just trying to make its kind of access sequential:

• Columnar wins for analytics (OLAP). Queries are narrow and tall — a few columns across millions of rows. Storing each column contiguously turns a 2-of-20-column query into a couple of long sequential scans, skips the other 18 columns entirely, and compresses each one hard because its values are homogeneous.
• Row wins for transactions (OLTP). Queries are wide and short — "fetch or update this one customer's whole record." Storing the full row contiguously means a single sequential read grabs every field at once; a columnar store would have to gather that row from 20 scattered stripes — random I/O, the slow path.

A columnar column-scan and a row-store point lookup are, deep down, the same move: read one contiguous run instead of seeking all over. They just optimize for opposite shapes of query. Most data pipelines feed analytics, so the rest of this section lives in columnar land — but if your workload is point lookups and frequent updates, a row store (or a key-value store) is the Less-I/O choice.

Parquet, concretely — the clever bits

Parquet is the workhorse columnar format, and it is clever in two complementary ways: it shrinks the bytes (encoding + compression) and it skips the bytes (statistics, pages, and clustering). The layout is a set of nested boxes, and every box is a chance to do one or the other:

Two stages, in order: encode, then compress. People blur these together, but Parquet does them as distinct steps. Encoding re-expresses values using their structure — a dictionary for repeated strings, run-length for repeats, delta for slowly-changing numbers, bit-packing to spend only as many bits as a value needs. Compression then runs a general byte-squeezer (Snappy, ZSTD, gzip) over the already-encoded bytes. Encoding understands your data; compression doesn't care what the bytes mean.

Then a codec squeezes what's left. The trade, stated honestly: you spend CPU time to buy back I/O time. On an I/O-bound scan that's almost a free lunch — the CPU is idle waiting on the disk or network anyway, so the cycles spent decompressing hide behind the wait, and the job finishes sooner because there were fewer bytes to move. The right codec depends on which resource is actually scarce:

For big analytics scans you're almost always I/O-bound, so ZSTD is the usual sweet spot: a strong ratio with decompression fast enough to keep the scan fed.

Pages — the unit of skipping. Inside a column chunk, values are split into pages (~1 MB each, plus a dictionary page). Every page records min/max stats, and modern Parquet writes a separate page index (a column index + offset index, kept in the footer) so a reader can consult per-page min/max without touching the pages, then seek straight to the few that can match — pruning one level finer than the row group. Sorted data makes this lethal: tight, non-overlapping ranges mean a selective filter opens a handful of pages out of thousands.

And here's the part that makes Parquet genuinely elegant: the page isn't just the unit of skipping — it's the unit of compression too. Each page is encoded and compressed on its own, so once the page index has pruned down to the few pages that can match, those are the only pages the reader decompresses. Skip and un-squeeze happen at the same fine grain — you never pay CPU to decompress bytes you were always going to throw away. That alignment is exactly why the CPU-for-I/O trade above stays cheap in practice.

Z-order — pruning on more than one column

Min/max pruning only works if the values you filter on are clustered. Sort a file by account_id and filters on account_id fly — but a filter on date now reads everything, because every page holds the full range of dates. A single sort key only clusters the first column; everything after it is scattered.

Z-ordering (a Morton-order space-filling curve) fixes that. It interleaves the bits of several columns into one ordering, so rows that are near each other in any of those dimensions tend to land in the same row-group. You give up the perfect clustering of a single sort key in exchange for good clustering on several columns at once — exactly what you want when different queries filter on different fields. Lakehouse table formats (Delta Lake, Iceberg, Hudi) apply it when they write the underlying Parquet.

ClickHouse types — pick the smallest type that fits

ClickHouse takes the "fewer bytes" idea to the column-type level. The data type you choose directly sets how many bytes each value costs on disk and in memory. A few habits that quietly halve (or 8×) storage:

04Less I/O in the data model

This is the lever most people underuse — and it has the highest ceiling. Movement and storage tricks shave a query; the data model decides how much data the query ever needs to look at. In banking, where tables of transactions run into the billions of rows, modeling for Less I/O is the difference between a dashboard that loads in 200 ms and one that times out.

The principle: model for the question you'll actually ask, not for textbook normalization. Normalization is wonderful for transactional correctness; analytics often wants the opposite — pre-joined, pre-aggregated, partitioned by how you filter.

The modeling moves that buy the most

A handful of patterns I reach for again and again — all variations of "decide the answer ahead of time":

• Incremental materialized views — keep an aggregate up to date as data arrives instead of recomputing from scratch (ClickHouse's AggregatingMergeTree, dbt incremental models, Spark Structured Streaming).
• Sort key = scan/filter order — physically ordering a table by the columns you filter on lets min/max statistics prune aggressively. A balance table sorted by (account_id, day) turns a lookup into a near-point read.
• Denormalize the hot path — fold the one or two dimension columns a query always needs straight into the fact table, so the frequent query never pays for a join.
• Roll up by grain — keep daily summaries, then monthly ones; a "last 2 years by month" report reads ~24 rows instead of two years of raw transactions.

05When the work funnels to one node — a Spark story

Everything so far has been about spreading work out and reading less. This is the failure mode that breaks both at once: data quietly funneling onto a single node. And it's the reason I finally sat down to write this.

I'd been meaning to for a while — five years as a data engineer, and now from the other side of the table as a solution architect, where I mostly watch the same handful of principles decide whether a system flies or crawls. The actual nudge came from a junior DE on my team.

His Spark job kept dying with an out-of-memory error on the driver. He'd asked Claude Code, and — credit where it's due — it pointed straight at the culprit in seconds: a broadcast join. Disable it (drop spark.sql.autoBroadcastJoinThreshold to -1) and the crash disappears.

The answer was right, and it was fast. The problem was that nobody believed it. By every number the team could see, the broadcast side was only ~10 MB — right around Spark's default auto-broadcast threshold, and laughably small for a driver with gigabytes of heap. How could broadcasting 10 MB possibly OOM the driver? The suggestion looked obviously wrong, so they kept bumping driver memory instead — and kept crashing.

Then another engineer stepped in with the missing piece — and it's genuinely fundamental: that "10 MB" is an estimate, not a measurement. Because Spark evaluates lazily, at planning time it hadn't yet built the relation it was about to broadcast. Catalyst propagates an estimated sizeInBytes through the logical plan and compares that guess against the threshold. After a chain of lazy transformations — reads, filters, joins — the estimate can drift far from reality. And even when it's about right on disk, Parquet is compressed: once the relation is collected to the driver, decompressed, and deserialized into JVM objects (with their per-row and per-object overhead), the real heap footprint can be many times the "10 MB" the planner reported.

# Why Spark broadcast it — and why "10 MB" lied
spark.sql.autoBroadcastJoinThreshold = 10MB     # default: broadcast a join side whose ESTIMATED size is under this

# The dim side's plan-time ESTIMATE was ~10 MB  → Spark auto-broadcast it.
# That estimate is heuristic — the planner sized it from stats, not the materialized object.
# On the driver: decompress + deserialize into JVM objects → many× bigger → OOM.

# ✓ The fix:
spark.conf.set("spark.sql.autoBroadcastJoinThreshold", -1)   # disable → distributed sort-merge join, nothing funnels to the driver

So the fix was right and the metadata was misleading. Disabling the broadcast — so Spark falls back to a distributed sort-merge join that never funnels the table through one node — is exactly the correct move. But you can only trust that move if you understand that the broadcast decision rides on a lazily-estimated size, not the real one. Without that fundamental, a correct answer looks like a bad guess, and you talk yourself out of it.

Two footnotes for the Spark-deep: newer engines re-check join sizes at runtime via Adaptive Query Execution — but it still sizes from compressed shuffle data, so the driver-heap blow-up can survive. And if the code carries an explicit broadcast() hint, it overrides the threshold entirely — then the fix is to remove the hint, not tune the config. (The deeper root cause: missing column statistics, which a COMPUTE STATISTICS / cost-based-optimizer pass would have sharpened.)

The other classic driver-OOM is the same sin worn differently — collect() or toPandas() pulling a big distributed result onto the driver:

from pyspark.sql.functions import col, sum

# ✕ BAD — pulls every matching row onto the driver's single heap
rows = (spark.read.parquet("s3://bank/transactions")
              .filter(col("txn_date") >= "2026-01-01")
              .collect())                       # all partitions → driver → OOM

# ✓ GOOD — keep the reduction distributed; the driver only coordinates
(spark.read.parquet("s3://bank/transactions")
      .filter(col("txn_date") >= "2026-01-01")    # predicate pushdown (+ partition pruning if partitioned by date)
      .groupBy("account_id").agg(sum("amount"))   # reduced on the executors
      .write.parquet("s3://bank/daily_agg"))      # each executor writes its own share

The cure for the crash was pure Less I/O: stop funneling the table onto one node. But the reason it stalled wasn't the fix — it was that "is this answer actually right?" is a question only fundamentals can settle, especially when the surface signals (a reassuring "10 MB") point the other way. A correct fix that contradicts a comforting metric gets ignored by a team without the fundamentals to explain that metric away.

And honestly, that's the moment that made me want to write all this down. The AI was fast — it surfaced the right fix in seconds, faster than most engineers would. But "fast" isn't the same as "trusted." The fix sat ignored because the team couldn't yet explain why a 10 MB estimate could blow up a multi-gigabyte heap. The thing that finally unblocked them wasn't more compute or a better prompt — it was an engineer who held the fundamentals and could say, with confidence, "the estimate is lying, and here's the mechanism."

That's the shape of the job now, the way I see it: AI gives you candidate answers at remarkable speed; fundamentals are what let you accept the right one you didn't expect — and reject the confident one that's wrong. The faster the assistant gets, the more valuable the engineer who can adjudicate it becomes. Tools change every year; the principles under them — like Less I/O — barely move in a decade. That's where I'd still tell any DE to invest.

06When it's not I/O — the honest caveat

I lean on Less I/O as the default lens for one empirical reason: nearly every slow pipeline I've actually touched has been I/O-bound — waiting on bytes, not on math. But the exception deserves a name, because when it hits, the cure is the opposite.

Occasionally you flip to CPU-bound — the bytes arrive fast enough, and the bottleneck is the work done per byte. The usual culprits:

• Compression turned up too high — gzip-9 or a high ZSTD level shrinks storage beautifully, then bottlenecks the scan on decompression. You bought fewer bytes and paid for them in CPU.
• Heavy per-row work — complex UDFs, regex, JSON parsing, hashing — especially in Python, where you fall off the vectorized path.
• Tiny data, big logic — once the dataset fits in cache there's no I/O left to remove; you're simply computing.

The tell is cheap to read: watch where the time goes. High disk/network wait → I/O-bound, reach for everything above. Pinned CPU with the disk idle → stop removing bytes and start removing work — a lighter codec, a vectorized expression, push the logic into the engine instead of a row-by-row UDF.

A thousand knobs, one lens

Here's the trap the surface area sets. Every layer you've seen comes with its own pile of knobs — codec and compression level, row-group and page size, partition key, sort key, Z-order columns, join strategy, broadcast threshold, column types, materialized views. Hundreds of settings across a dozen tools, each with its own docs and folklore, and a new one landing every release. You could spend a career memorizing them and still meet a stranger tomorrow.

You don't have to — because they all rhyme. When a layer misfires — a broadcast that shouldn't have been, a partition key that doesn't match how you filter, a collect() that drags the world to one node — the symptom looks different every time, but the cure is always the same: find where data is moving that it didn't need to, and take that work away. The knobs are just a thousand specific answers; Less I/O is the one question that tells you which knob to reach for, and why.

That's the case for carrying a mental model instead of a checklist: a checklist grows with the number of tools, a principle doesn't. The tools will keep changing — new formats, new engines, an AI that hands you the fix in seconds. The lens that tells you whether to trust that fix won't. So next time you're staring at a slow pipeline and don't know where to start, skip the knob-hunt and ask the only question that has ever mattered: where am I moving data I didn't need to — and how do I move less of it?