A 3 AM Incident and the 12GB CSV Dilemma
Our system has a cron job that runs at 3 AM to aggregate access logs and event tracking data from our web server clusters. Six months ago, daily log volume hit the 12GB mark in raw CSV format.
The consequences hit fast. The script, running the familiar pd.read_csv() call, ate through all 16GB of VPS RAM in just 4 minutes. CPU pinned at 100%. The Linux kernel invoked the OOM Killer, terminating the process instantly.
At that point, I had two options. One was upgrading the RAM to 32GB or 64GB, costing an extra $40–$60 a month in server bills. The second was redesigning our storage format and data pipeline from the ground up. I chose the latter: migrating everything to Apache Parquet and adopting PyArrow as the core processing engine.
Parquet is a columnar storage format, fundamentally different from the row-oriented structure of CSV. Three key architectural advantages make Parquet vastly superior:
- Superior Data Compression: Values within the same column share the same data type, allowing algorithms like Snappy or ZSTD to compress them far more efficiently than raw text. A 12GB CSV file shrinks down to just 1.4GB–1.8GB, saving over 85% of disk space.
- Projection and Predicate Pushdown: When querying 3 columns from a 50-column table, PyArrow reads only the exact byte ranges for those 3 columns from disk, skipping the remaining 47 entirely. With CSV, the parser has to scan every single byte from beginning to end.
- Zero-Copy Reads: PyArrow directly memory-maps files from the C++ Arrow C Data Interface into the Python runtime. This eliminates serialization overhead and memory reallocation, delivering massive read speedups.
After 6 months of running stably in production, job runtime dropped from 18 minutes down to 42 seconds. Peak memory usage stabilized at 780MB instead of maxing out at 16GB. Below is the detailed implementation guide.
Environment Setup
PyArrow provides pre-built binary wheels on PyPI for Linux, macOS, and Windows. To enable high-ratio compression, install the zstandard library as well:
# Install PyArrow and the zstandard compression library
pip install pyarrow zstandard
# Optional: install pandas for backward compatibility when exporting DataFrames
pip install pandasOnce installed, quickly verify the compression codecs in a Python shell:
import pyarrow as pa
import pyarrow.parquet as pq
print(f"PyArrow version: {pa.__version__}")
print(f"ZSTD supported: {pa.Codec.is_available('zstd')}")
print(f"Snappy supported: {pa.Codec.is_available('snappy')}")If both checks return True, your environment is ready to handle heavily compressed Parquet files.
Production-Ready Implementation: Reading, Writing, and Optimizing
1. Writing Parquet with an Explicit Schema
The most common mistake when writing Parquet files is relying on automatic type inference. This wastes CPU cycles and often leads to schema mismatches across log batches. Always define an explicit pa.schema upfront:
import pyarrow as pa
import pyarrow.parquet as pq
from datetime import datetime
# 1. Define an explicit schema, selecting the smallest appropriate data types
schema = pa.schema([
('event_id', pa.string()),
('user_id', pa.int64()),
('action', pa.string()),
('latency_ms', pa.float32()), # Use float32 instead of float64 to cut memory usage by 50%
('created_at', pa.timestamp('ms')),
('is_bot', pa.bool_())
])
# 2. Sample array data
data = [
pa.array(['evt_101', 'evt_102', 'evt_103']),
pa.array([10001, 10002, 10003]),
pa.array(['login', 'view_item', 'checkout']),
pa.array([45.2, 120.8, 310.5], type=pa.float32()),
pa.array([datetime.now(), datetime.now(), datetime.now()], type=pa.timestamp('ms')),
pa.array([False, False, True])
]
# 3. Construct a PyArrow Table
table = pa.Table.from_arrays(data, schema=schema)
# 4. Write to disk using ZSTD level 3 and a 50,000-row group size
pq.write_table(
table,
'events_2026_10.parquet',
compression='zstd',
compression_level=3,
row_group_size=50000
)
print("Parquet file written successfully!")The row_group_size parameter is critical. Setting it too small (under 5,000 rows) causes metadata bloat and noticeably degrades read speeds. Conversely, packing millions of rows into a single row group eliminates parallelism and partition filtering benefits. A threshold of 50,000 to 100,000 rows is the sweet spot for most backend logging systems.
2. Optimized Reads: Projection and PyArrow Compute
Never read the entire file if you only need a few metrics. Use the columns parameter to load only the required data:
import pyarrow.parquet as pq
import pyarrow.compute as pc
# Load only 2 columns into memory: user_id and latency_ms
table_subset = pq.read_table(
'events_2026_10.parquet',
columns=['user_id', 'latency_ms']
)
print(f"Record count: {table_subset.num_rows}")
print(f"RAM usage: {table_subset.nbytes / (1024 * 1024):.2f} MB")
# Compute directly using PyArrow Compute in C++, without converting to pandas
avg_latency = pc.mean(table_subset['latency_ms'])
print(f"Average latency: {avg_latency.as_py():.2f} ms")Pro tip: performing calculations directly with pyarrow.compute keeps data in its underlying C++ memory layout. This avoids costly memory copies to NumPy or pandas, saving significant CPU overhead during recurring jobs.
3. Data Partitioning (Hive Partitioning)
As data grows from millions to hundreds of millions of records, dumping everything into a single file creates an I/O bottleneck. Instead, organize data into directory trees such as action=login/ or date=2026-10-27/:
import pyarrow.dataset as ds
import pyarrow.parquet as pq
# Write dataset automatically partitioned by the action column
pq.write_to_dataset(
table,
root_path='events_partitioned',
partition_cols=['action'],
compression='zstd'
)
# Read data via the Dataset API with Hive partitioning
dataset = ds.dataset('events_partitioned', format='parquet', partitioning='hive')
# Filter data: PyArrow only reads the action=checkout folder, skipping the rest
checkout_filter = ds.field('action') == 'checkout'
filtered_table = dataset.to_table(filter=checkout_filter)
print(f"Scanned checkout events: {filtered_table.num_rows}")Benchmarking and Monitoring
Never guess when it comes to performance. Measure disk usage and metadata parsing times directly using the script below:
import os
import time
import pyarrow.parquet as pq
file_path = 'events_2026_10.parquet'
# 1. Physical file size on disk
size_mb = os.path.getsize(file_path) / (1024 * 1024)
print(f"[Disk Usage] File size: {size_mb:.2f} MB")
# 2. Read metadata without loading the entire data payload
parquet_file = pq.ParquetFile(file_path)
meta = parquet_file.metadata
print(f"[Metadata] Total rows: {meta.num_rows:,}")
print(f"[Metadata] Row group count: {meta.num_row_groups}")
print(f"[Metadata] Compression codec: {meta.row_group(0).column(0).compression}")
# 3. Benchmark read latency for a specific column
t0 = time.perf_counter()
col_data = parquet_file.read(columns=['user_id'])
elapsed = (time.perf_counter() - t0) * 1000
print(f"[Benchmark] Read time for 'user_id': {elapsed:.2f} ms")
print(f"[Memory] Column memory usage: {col_data.nbytes / (1024 * 1024):.4f} MB")When deploying to production, monitor these three key metrics:
- Compression Ratio: Compare in-memory size (
table.nbytes) against file size on disk. Typical system logs should achieve compression ratios between 1:4 and 1:7 with ZSTD level 3. - Small Files Problem: A 50MB file containing over 500 row groups is a red flag. The overhead of parsing metadata headers will wipe out the advantages of Parquet. When encountering this, compact smaller batches before writing them to disk.
- Peak Memory (RSS): Profile your script on Linux using
/usr/bin/time -v python script.py. Monitor the Maximum resident set size line. This metric should remain steady day over day rather than scaling linearly with overall dataset volume.
Migrating from CSV to Parquet with PyArrow eliminates I/O bottlenecks at their source. For medium to large-scale data workflows, it provides an effective way to maximize existing hardware resources before spending money on server upgrades.
