1. The Problem
Initial Python ingestion script executed `cursor.fetchall()` on a 500,000 row table. Memory usage spiked to 4.2 GB, triggering Linux OOM killer at 2:14 AM.
2. What I Initially Thought
3. What I Learned
Client-side cursors pull the entire query result set into Python application memory at once. Server-side named cursors (Psycopg2 `cursor(name='...')`) fetch records lazily in fixed chunks of N records.
4. What I Built
Resilient batch extraction engine utilizing Psycopg2 server-side chunking and Tenacity decorator for automatic retry on transient DB network drops.
@retry(stop=stop_after_attempt(5), wait=wait_random_exponential(min=1, max=60))
def extract_chunked_data(conn, query, batch_size=10000):
with conn.cursor(name="ingest_stream_cursor") as cursor:
cursor.itersize = batch_size
cursor.execute(query)
while rows := cursor.fetchmany(batch_size):
yield process_rows(rows)5. The Experiment
Running `cursor.fetchall()` on 500,000 rows. Python container RAM hit 4.2 GB; failed on transient DB restarts.
Refactored to named server-side cursors (`itersize=10000`) with exponential backoff retries.
Container RAM stayed flat at 140 MB regardless of table size; transient DB reconnects handled automatically.
6. What Went Wrong
Forgot to set `itersize` on the named cursor initially, causing Psycopg2 to default to 2000 rows per round trip, which created network round-trip overhead.
7. Engineering Decision & Trade-offs
Enforced server-side cursor streaming with 10,000-row batch windows as standard policy across all Python ingestion microservices.
8. What I Would Do Differently in Production
In production enterprise pipelines, use connection pools like PgBouncer to prevent opening thousands of short-lived database connections during batch extractions.
Questions I Can Now Answer Confidently in an Interview:
- What is the difference between client-side and server-side cursors in PostgreSQL?
- Why is exponential backoff with random jitter necessary when retrying database operations?
- How do stream generators in Python preserve low memory usage during large ETL extractions?