Cutting Memory in Python Data Jobs: Dtypes, Chunking, Arrow, and Streaming

The alert said the nightly job was killed by the OOM killer. The instance had 64 GB. The input Parquet file was 4.2 GB. Somebody had already tried the standard remedy — double the instance — and it had been killed again at 128 GB.

The file expanded to roughly 31 GB in memory, mostly because two string columns with low cardinality were stored as Python objects. A merge then copied a large fraction of the frame. Fixing the dtypes at read time and pruning eleven unused columns brought steady state to 3.4 GB and peak to about 6, and the job ran comfortably on 16.

That ratio is typical. Parquet on disk is compressed and dictionary-encoded; pandas in memory is often five to eight times larger, and object-dtype strings are where most of the gap comes from. Before you resize anything, find out where the bytes went.

This post is the order I work in: measure, prune at the reader, fix dtypes, then decide between chunking, an out-of-core engine, and just paying for a bigger machine.

Measure before you change anything

df.info() lies about object columns by default. It reports the pointer array, not the strings those pointers reference. Use memory_usage(deep=True), and look at it per column.

def memory_report(df: pd.DataFrame, top_n: int = 15) -> pd.DataFrame:
    """Per-column deep memory, largest first."""
    usage = df.memory_usage(deep=True).drop("Index", errors="ignore")
    report = pd.DataFrame({
        "bytes": usage,
        "mb": (usage / 1024**2).round(1),
        "dtype": [str(df[c].dtype) for c in usage.index],
        "nunique": [df[c].nunique() if usage[c] > 10**7 else None
                    for c in usage.index],
    }).sort_values("bytes", ascending=False)
    total = report["mb"].sum()
    print(f"total: {total:.1f} MB across {len(df):,} rows "
          f"({total * 1024**2 / max(len(df), 1):.0f} bytes/row)")
    return report.head(top_n)

The nunique column is the actionable part. A string column with 25 distinct values across 40 million rows is a category waiting to happen, and that single change is often a 90 percent reduction on that column.

For peak rather than steady state, watch the process while it runs. Peak is what the OOM killer responds to, and it is usually two to three times steady state because of copies:

import tracemalloc, resource

tracemalloc.start()
result = run_pipeline()
current, peak = tracemalloc.get_traced_memory()
print(f"python peak: {peak / 1024**3:.2f} GB")
# RSS includes Arrow and NumPy buffers that tracemalloc misses.
print(f"process peak RSS: "
      f"{resource.getrusage(resource.RUSAGE_SELF).ru_maxrss / 1024**2:.2f} GB")
tracemalloc.stop()

Track both. tracemalloc misses allocations made outside the Python allocator, which on a data job is most of them.

Prune at the reader

The cheapest byte is the one you never read. Column and row filtering belongs in the reader call, not in a line after it.

# Reads all 84 columns, then discards 78. Peak is the full width.
df = pd.read_parquet("events.parquet")
df = df[["user_id", "event_type", "event_ts", "amount"]]

# Reads 4 columns. Row groups for other columns are never touched.
df = pd.read_parquet(
    "events.parquet",
    columns=["user_id", "event_type", "event_ts", "amount"],
    filters=[("event_date", ">=", "2026-05-01")],   # row-group pruning
)

filters uses Parquet row-group statistics to skip entire groups, which is the same min/max pruning that columnar warehouses do. It only works if the file was written sorted or partitioned on that column — the exact concern covered in the BigQuery partitioning guide, applied to local files.

For CSV the equivalent is usecols plus an explicit dtype map. Never let read_csv infer types on a large file: inference reads a sample, guesses, and frequently produces object for a column that is really a small integer with a few blanks.

df = pd.read_csv(
    "events.csv",
    usecols=["user_id", "event_type", "event_ts", "amount"],
    dtype={
        "user_id": "int32",
        "event_type": "category",
        "amount": "float32",
    },
    parse_dates=["event_ts"],
)

Dtypes: where the wins are

flowchart TD
  col["Column"] --> kind{"What kind?"}
  kind -->|"Text, low cardinality"| cat["category: 10 to 50x smaller"]
  kind -->|"Text, high cardinality"| arrow["string[pyarrow]: 3 to 5x smaller"]
  kind -->|"Integer"| down["Downcast to int32 or int16"]
  kind -->|"Float"| f32["float32 if precision allows"]
  kind -->|"Boolean with nulls"| bool8["boolean, not object"]
  kind -->|"Timestamp"| dt["datetime64 with the coarsest unit you need"]

Strings first. Numeric downcasting is real but usually a second-order effect.

Strings are the headline. An object-dtype column stores a pointer array plus a separate Python str object per value, each carrying about 49 bytes of overhead before its characters. Forty million short strings can exceed 3 GB.

s = pd.Series(["enterprise", "smb", "startup"] * 10_000_000)

s.memory_usage(deep=True) / 1024**2              # ~1,900 MB (object)
s.astype("string[pyarrow]").memory_usage(deep=True) / 1024**2   # ~370 MB
s.astype("category").memory_usage(deep=True) / 1024**2          # ~30 MB

Use category when cardinality is low relative to row count — my rough threshold is under 50 percent unique, and the win is dramatic under 1 percent. Use string[pyarrow] for everything else; it stores contiguous UTF-8 bytes with an offsets array, no per-value Python object, and it is faster for string operations too.

Two cautions on category. Concatenating two frames with different category sets produces object unless the categories are unified first, which silently undoes the savings. And a high-cardinality column stored as category costs more than plain strings, because you pay for the dictionary and the codes.

Numeric downcasting is straightforward and safe if you check ranges rather than guessing:

def downcast_numeric(df: pd.DataFrame) -> pd.DataFrame:
    for col in df.select_dtypes(include=["int64"]).columns:
        df[col] = pd.to_numeric(df[col], downcast="integer")
    for col in df.select_dtypes(include=["float64"]).columns:
        # float32 has ~7 significant digits. Fine for metrics,
        # not for currency where you need exact cents.
        df[col] = pd.to_numeric(df[col], downcast="float")
    return df

Keep money in float64 or Decimal. float32 gives about seven significant digits, which starts losing cents somewhere above 100,000 dollars, and finance will find it before you do.

Arrow-backed everything. Pandas 2.x can use Arrow for all dtypes via dtype_backend="pyarrow", which brings proper null support for integers and booleans, no float upcasting on nulls, and zero-copy handoff to Parquet, DuckDB, and polars.

df = pd.read_parquet("events.parquet", dtype_backend="pyarrow")

The tradeoff is compatibility: some third-party libraries still expect NumPy-backed frames and will convert, copying everything. I use it for pipeline internals and convert at the boundary if a consumer needs NumPy.

Peak memory: copies are the real problem

Steady-state size is what you measured. Peak is what kills you, and the gap is copies.

# Each of these doubles the frame briefly.
df2 = df.copy()
df = df.merge(other, on="user_id")        # builds a new frame
df = df.sort_values("event_ts")           # may allocate a full copy
df = pd.concat([df, more])                # allocates the combined size

# Reduce peak: drop references as soon as they are dead.
df = df.merge(other, on="user_id")
del other
gc.collect()

Three habits that cut peak substantially:

Filter before joining. A merge allocates a result sized by the join output. Filtering the right side to the rows and columns you actually need shrinks both the hash table and the result.

Avoid concat in a loop. Appending in a loop is quadratic in allocations — each iteration copies everything so far. Collect the pieces in a list and concatenate once, or write each piece to Parquet and read the dataset back.

Watch the merge fan-out. A merge on a non-unique key produces more rows than either input, and memory follows row count. This is the pandas version of the SQL fan-out bug, with the same fix: assert the join key is unique on the side you assume it is, before the merge. The fan-out post covers why this is a correctness problem too, not only a memory one.

# Pandas will tell you, if you ask.
df = left.merge(right, on="user_id", how="left", validate="many_to_one")
# Raises MergeError if right has duplicate user_id values.

validate= is free and I add it to every merge. It converts a silent memory explosion plus wrong numbers into an immediate, specific exception.

Chunking, and when it does not work

When the data genuinely does not fit, process it in pieces. This works cleanly for row-independent operations — filtering, mapping, per-row enrichment, writing out.

import pyarrow.parquet as pq

def transform_streaming(src: str, dst: str, batch_rows: int = 500_000):
    """Row-independent transform, constant memory regardless of file size."""
    reader = pq.ParquetFile(src)
    writer = None
    try:
        for batch in reader.iter_batches(
            batch_size=batch_rows,
            columns=["user_id", "event_type", "event_ts", "amount"],
        ):
            df = batch.to_pandas()
            df = df[df["amount"] > 0]
            df["amount_usd"] = df["amount"] * 1.08

            table = pa.Table.from_pandas(df, preserve_index=False)
            if writer is None:
                writer = pq.ParquetWriter(dst, table.schema, compression="zstd")
            writer.write_table(table)
    finally:
        if writer is not None:
            writer.close()

Aggregations are the hard case, because a global result depends on all rows. Two workable patterns:

# Two-pass: accumulate partial aggregates, combine at the end.
partials = []
for batch in reader.iter_batches(batch_size=500_000,
                                 columns=["region", "amount"]):
    df = batch.to_pandas()
    partials.append(
        df.groupby("region", observed=True)["amount"]
          .agg(["sum", "count"])
    )

combined = pd.concat(partials).groupby(level=0).sum()
combined["mean"] = combined["sum"] / combined["count"]

sum, count, min, and max combine associatively, so this is exact. mean works by carrying sum and count separately, as above. Median, exact distinct count, and quantiles do not combine — those need either a full pass in a real engine or an approximate sketch such as HyperLogLog or a t-digest.

At that point, ask whether you should be chunking at all. Reaching for two-pass aggregation in Python is often a sign that a query engine is the right answer:

# DuckDB: out-of-core group-by over a dataset larger than memory,
# reading directly from Parquet with projection and predicate pushdown.
import duckdb

result = duckdb.sql("""
    select region, count(*) as n, sum(amount) as total,
           median(amount) as median_amount
    from read_parquet('s3://bucket/events/*.parquet')
    where event_date >= '2026-05-01'
    group by region
""").df()

DuckDB spills to disk, parallelizes, and handles the aggregations that do not combine. The result frame is small, so pandas only ever sees the output. This is the single biggest win available for most memory-bound aggregation jobs — the DuckDB post goes into the broader pattern.

Polars is the other answer, particularly with collect(streaming=True) and sink_parquet, and it keeps you in a dataframe API rather than SQL. The polars migration post covers what that transition costs.

Pitfalls

df.info() without memory_usage="deep". Undercounts object columns dramatically, which is exactly the column type you are trying to find.

Optimizing dtypes after loading. astype after read_parquet still peaks at the unoptimized size. Set dtypes at the reader.

category on a high-cardinality column. Costs more than plain strings once cardinality approaches row count, and slows down joins.

Concatenating categoricals with different categories. Silently falls back to object, undoing the savings. Use union_categoricals or set matching CategoricalDtype first.

float32 for currency. Around seven significant digits. Rounding errors show up in reconciliation, and they are hard to argue with.

Chunking a stateful operation. Sorting, ranking, deduplication across the whole dataset, and window functions cannot be done correctly one chunk at a time. Sort in the engine or use an external sort.

Assuming del frees memory immediately. It drops the reference; the allocator may keep the arena. RSS often does not fall until the process exits, which is fine as long as the peak is under your limit.

Leaving intermediate frames alive. A notebook or a long function that holds df1 through df6 keeps all six allocated. Reassign to the same name or delete as you go.

FAQ

How much smaller is Parquet on disk than in memory?

Typically five to eight times, sometimes more. Parquet is compressed and dictionary-encoded; a naive pandas load materializes every value. A 5 GB file needing 30 GB of RAM is ordinary, not pathological.

Should I use category or string[pyarrow]?

Compare distinct count to row count. Under a few percent unique, category wins by a wide margin. Above roughly half, use string[pyarrow]. In between, either is fine and string[pyarrow] is safer because it has no concat gotcha.

Is pyarrow dtype backend production ready?

Yes for pipeline internals in pandas 2.x, with a compatibility caveat. Some libraries still convert Arrow-backed frames to NumPy, which copies everything and can spike memory at the boundary. Convert deliberately at the edge rather than being surprised.

When should I stop optimizing and just scale up?

When the engineering time exceeds the compute cost. A job that runs nightly on a 64 GB instance costs a few hundred dollars a year; if optimizing takes two days, scale up. If it runs hourly on a 512 GB instance, optimize.

Does Dask solve this?

Dask handles larger-than-memory pandas workloads by partitioning, and it works. For single-machine problems, DuckDB and polars are usually faster with far less operational complexity. Dask earns its place when you genuinely need a cluster and want to stay in the pandas API. For heavier distributed work, the PySpark guide covers the other end of that spectrum.

How do I find peak memory in a scheduled job?

Log RSS at checkpoints with psutil.Process().memory_info().rss, or run under memray in a staging environment for an allocation flame graph. Container metrics also expose peak working set, which is what your orchestrator’s OOM killer reads.

What this means for your pipelines

Memory problems in Python data jobs are rarely mysterious once you measure. The distribution is consistent: object-dtype strings dominate, columns nobody uses are being loaded, and a merge or a sort creates a copy that doubles the peak. Fixing those three things routinely cuts a job by 80 percent, and it takes an afternoon.

The order matters. Prune columns at the reader, because bytes never read cost nothing. Set dtypes at the reader, because converting afterward still peaks at the unoptimized size. Then attack peak by filtering before joins, validating merge keys, and dropping intermediates. Only after that should you consider chunking, and when you do, notice whether you are reimplementing a query engine — because if you are writing two-pass aggregation logic in Python, DuckDB or polars will do it correctly, in parallel, with spilling, in five lines of SQL.

And keep the economics in view. Scaling the instance is a real option and sometimes the right one. What is not defensible is scaling repeatedly without ever running a per-column memory report, which is how a job ends up on a 128 GB machine to process a 4 GB file. Measure once, and you usually get the machine back.