Why does a large CSV export run out of memory in a monolith?

Most export endpoints start the same way: query the table, loop over the results, join them into a CSV string, and return it. That works for a tenant with 5,000 rows. When the largest tenant reaches tens of millions of rows, the same code allocates memory in proportion to the report, and the process gets OOM-killed or stalls in garbage collection while the other requests in the monolith wait.

The naive path holds the data in memory three times:

  1. The driver buffers the result set. By default, many Postgres client libraries read the entire result into client memory before your code sees the first row. A for row in results loop looks lazy, but the full result set has already been transferred and stored.
  2. The ORM builds objects. Each row becomes a model instance with attribute dictionaries, type conversion, and often identity-map tracking. That costs far more memory than the raw tuple.
  3. The CSV is built as one string. Writing into an in-memory buffer and returning it at the end keeps the full encoded output alive, often next to the objects that produced it.

The target design is a bounded-memory pipeline:

  • Source: a Postgres cursor or a paginated query that yields rows on demand.
  • Batch: a fixed number of rows held at once.
  • CSV writer: encodes each batch.
  • Sink: the HTTP response, a file, or object storage. It receives each encoded batch, and the batch is then released.

In this design, peak memory usage depends on batch size and row width. It does not depend on report size.

This guide is for backend and platform engineers whose report generation grows with tenant data and needs to stream large CSV exports without resizing the whole fleet for one customer.

import csv
import io
import psycopg2

DSN = "postgresql://report_user:example-password@db.internal:5432/app"
QUERY = "SELECT id, email, total, created_at FROM orders WHERE tenant_id = %s ORDER BY id"
HEADER = ["id", "email", "total", "created_at"]

def naive_export(conn, tenant_id):
    # Client-side cursor: execute() transfers and stores the whole result set.
    with conn.cursor() as cur:
        cur.execute(QUERY, (tenant_id,))
        rows = cur.fetchall()           # every row in memory at once
    buf = io.StringIO()
    writer = csv.writer(buf)
    writer.writerow(HEADER)
    writer.writerows(rows)
    return buf.getvalue()               # the full CSV as one string

def stream_export(conn, tenant_id, batch_size=5000):
    # Named cursor = server-side cursor: rows stay in Postgres until fetched.
    # It needs an open transaction (psycopg2's default when autocommit is off).
    with conn.cursor(name="orders_export") as cur:
        cur.execute(QUERY, (tenant_id,))
        yield ",".join(HEADER) + "\r\n"
        while True:
            batch = cur.fetchmany(batch_size)  # source -> bounded batch
            if not batch:
                break
            buf = io.StringIO()
            csv.writer(buf).writerows(batch)   # encode only this batch
            yield buf.getvalue()               # hand to sink, then release

if __name__ == "__main__":
    conn = psycopg2.connect(DSN)
    try:
        # Sink is a file here; an HTTP streaming response works the same way.
        with open("orders_tenant_42.csv", "w", newline="") as sink:
            for chunk in stream_export(conn, tenant_id=42):
                sink.write(chunk)
    finally:
        conn.close()

Compares a naive psycopg2 export that loads every row and builds one CSV string with a streaming version that uses a server-side named cursor and fetchmany, so memory is bounded by batch size rather than report size.

How do you stream rows with a Postgres cursor?

A cursor lets Postgres hold the result set on the server while your process pulls a fixed number of rows at a time. Open a transaction, DECLARE export_cur NO SCROLL CURSOR FOR SELECT ..., then loop on FETCH 2000 FROM export_cur until it returns zero rows, and close the cursor and commit at the end. Without WITH HOLD, the cursor only exists inside that transaction.

Most drivers wrap this pattern. In psycopg2, passing a name to cursor() creates a server-side cursor, and itersize controls the batch. The PostgreSQL JDBC driver only streams when autocommit is off and a fetch size is set. Otherwise it reads the full result into memory. Default cursors in many drivers buffer everything client-side, so don't assume streaming. To verify it, run the export against a large table and check pg_stat_activity. You should see repeated FETCH statements, and process memory should stay flat instead of climbing before the first row arrives.

Batch size trades memory against round trips. Peak memory is roughly batch size times average row width. Narrow rows can use batches in the thousands. Rows that carry wide text or JSON columns need smaller batches.

The cost is a long-lived transaction. On the primary, it holds back the xmin horizon, so vacuum can't remove dead tuples while the export runs. On a hot standby, the cursor's query can be cancelled by replication conflicts. If you enable hot_standby_feedback to prevent those cancellations, the bloat shifts back to the primary. Avoid this approach for exports that run for many minutes on high-churn tables, and for exports that need to survive connection drops.

import csv
import psycopg2

DSN = "postgresql://export_user:change-me@db.internal:5432/app"
BATCH_ROWS = 2000  # lower this if rows carry wide text/JSON columns
COLUMNS = ["id", "customer_id", "total_cents", "created_at"]


def export_orders(out_path):
    conn = psycopg2.connect(DSN)
    try:
        # `with conn` runs the block in a single transaction and commits at the end.
        # Without WITH HOLD, the server-side cursor exists only inside that transaction.
        with conn, conn.cursor(name="export_cur", scrollable=False) as cur:
            # Passing a name makes psycopg2 issue DECLARE ... CURSOR on the server.
            # An unnamed cursor would buffer the entire result set client-side.
            cur.itersize = BATCH_ROWS
            cur.execute(
                "SELECT id, customer_id, total_cents, created_at "
                "FROM orders ORDER BY id"
            )
            with open(out_path, "w", newline="") as f:
                writer = csv.writer(f)
                writer.writerow(COLUMNS)
                # Each pass through the loop pulls rows from a local batch. When the
                # batch runs out, psycopg2 sends another FETCH for itersize rows.
                # Watch pg_stat_activity during a run: you should see repeated
                # FETCH statements while this process's memory stays flat.
                for row in cur:
                    writer.writerow(row)
        # Leaving the block closes the cursor and commits, which ends the
        # long-lived transaction and releases the xmin horizon.
    finally:
        conn.close()


if __name__ == "__main__":
    export_orders("/tmp/orders_export.csv")

A psycopg2 named (server-side) cursor streams a query to CSV in batches of itersize rows inside one transaction, so client memory stays bounded and the transaction ends as soon as the export finishes.

When should you use keyset pagination or COPY instead of a cursor?

Reach for keyset pagination when the export runs long enough that holding one transaction open becomes a problem. Each batch is its own short query: WHERE id > :last_id AND id <= :max_id ORDER BY id LIMIT 5000, with the last id of each batch fed into the next. Unlike OFFSET, which reads and discards every skipped row, a seek on an indexed key starts each batch at the right place. Later pages cost about the same as the first.

Because there is no single snapshot, you need to set a boundary yourself. Before the first batch, read max(id) (or now() if you paginate on (created_at, id)) and use it as the upper bound. This stops rows inserted during the export from leaking into it. It does not freeze updates or deletes: a row changed between batches is exported in its newer state, and a deleted row is skipped. If the report needs exact point-in-time totals, use a cursor instead.

Use COPY (SELECT ...) TO STDOUT WITH CSV HEADER when the output is a pure SQL projection. Postgres formats the CSV itself, so no per-row work happens in the application. Use your driver's copy-out API and write each chunk to the destination as it arrives. Do not call a helper that returns the whole result as one buffer. Keep in mind that application-level formatting, row-level permission filtering, and localization are skipped, so all of it must be expressed in SQL. Also note that COPY is a single statement, so it holds one transaction open just like a cursor.

Approach Memory Consistency Transaction duration Flexibility Speed
Server-side cursor Flat (one batch) Single snapshot Whole export Full app logic Good
Keyset pagination Flat (one batch) Bounded, not a snapshot Per batch Full app logic Good, needs index
COPY ... TO STDOUT Flat if streamed Single snapshot Whole export SQL only Fastest
import csv
import psycopg  # psycopg 3

DSN = "postgresql://export_user:change-me@db.internal:5432/app"
BATCH = 5000

def keyset_export(path):
    # autocommit: each batch runs as its own short transaction
    with psycopg.connect(DSN, autocommit=True) as conn, open(path, "w", newline="") as f:
        writer = csv.writer(f)
        writer.writerow(["id", "customer", "total"])
        # Fix the upper bound once so rows inserted mid-export are excluded
        max_id = conn.execute("SELECT coalesce(max(id), 0) FROM orders").fetchone()[0]
        last_id = 0
        while True:
            rows = conn.execute(
                "SELECT id, customer, total FROM orders"
                " WHERE id > %s AND id <= %s ORDER BY id LIMIT %s",
                (last_id, max_id, BATCH),
            ).fetchall()
            if not rows:
                break
            writer.writerows(rows)  # app-level formatting/permission checks go here
            last_id = rows[-1][0]   # seek point for the next batch

def copy_export(path):
    # One statement, one transaction; Postgres produces the CSV itself
    with psycopg.connect(DSN) as conn, open(path, "wb") as f:
        with conn.cursor() as cur:
            with cur.copy(
                "COPY (SELECT id, customer, total FROM orders ORDER BY id)"
                " TO STDOUT WITH (FORMAT csv, HEADER)"
            ) as copy:
                for chunk in copy:  # write each chunk as it arrives; never buffer the whole export
                    f.write(chunk)

Using psycopg 3, a keyset export bounded by a max(id) captured up front, alongside a COPY TO STDOUT export that streams CSV chunks straight to a file instead of collecting them in memory.

How do you write CSV incrementally without building it in memory?

Fetching rows lazily only helps if the output side is equally lazy. The most common way to lose that benefit is accumulating the file before sending it: appending to a string, collecting rows in a list and joining at the end, or writing into an in-memory buffer that is never drained. Each of these makes peak memory proportional to the export size again, which undoes the cursor work.

Write the header once, then pass each row to a real CSV writer as it arrives. Use your language's standard CSV library rather than joining fields with commas. Values that contain commas, double quotes, or embedded newlines must be quoted and escaped correctly, and hand-rolled formatting usually gets this wrong only on the rare rows nobody tested. Point the writer at the destination stream (a response body, file, or object-storage upload) and flush in chunks of tens of kilobytes. That keeps the buffer bounded without paying for one syscall per row.

Keep the work done per row small:

  • Push joins and formatting into SQL. Let Postgres format dates, concatenate names, and resolve lookups. Each row should arrive ready to write.
  • Select only exported columns. Hydrating whole ORM entities loads unused fields, sets up change tracking, and may trigger lazy-loaded relations. That last case is the classic N+1 query, which multiplies database round trips by the row count.
  • Allocate little per row. Use plain tuples or arrays, not intermediate dictionaries or DTOs.

Once this pattern is wrapped in one writer component that takes a query and a header list, every new report reduces to writing a SQL statement. Quoting, flushing, and memory behavior are then tested once and reused by every report.

import csv
import io
import psycopg2

FLUSH_CHARS = 64 * 1024  # bounded buffer; avoids one write call per row

def stream_csv(conn, query, params, header, out):
    """Reusable writer: any report is just a query plus a header list."""
    buf = io.StringIO()
    writer = csv.writer(buf)  # quotes commas, double quotes and embedded newlines
    writer.writerow(header)   # header written exactly once
    # Named cursor = server-side cursor: rows arrive in batches, not all at once
    with conn.cursor(name="csv_export") as cur:
        cur.itersize = 2000
        cur.execute(query, params)
        for row in cur:            # each row is a plain tuple, ready to write
            writer.writerow(row)
            if buf.tell() >= FLUSH_CHARS:
                out.write(buf.getvalue())  # drain the chunk to the destination
                buf.seek(0)
                buf.truncate()
    out.write(buf.getvalue())  # final partial chunk
    out.flush()

# Joins and formatting happen in Postgres; only exported columns are selected.
ORDERS_SQL = """
    SELECT o.id,
           to_char(o.created_at, 'YYYY-MM-DD'),
           c.first_name || ' ' || c.last_name,
           o.total_cents / 100.0
    FROM orders o
    JOIN customers c ON c.id = o.customer_id
    WHERE o.created_at >= %s
"""

conn = psycopg2.connect("dbname=shop user=report_reader host=db.internal")
with conn, open("orders.csv", "w", newline="", encoding="utf-8") as f:
    stream_csv(conn, ORDERS_SQL, ("2026-01-01",),
               ["order_id", "order_date", "customer", "total"], f)

A reusable Python function that reads from a server-side cursor and writes each row through csv.writer into a bounded buffer flushed in roughly 64 KB chunks, with joins and formatting pushed into the SQL query.

Should the export stream to the HTTP response or run as a background job?

The writer can feed two sinks: the HTTP response or a file produced by a background job. The choice depends on how long the export runs, not on how many bytes it produces.

A streamed response is simplest when the export finishes well within your request timeout. Every hop matters, though. A reverse proxy or load balancer may buffer the whole response before forwarding it, which moves the memory problem off your app server. It may also close the connection if no bytes arrive within its idle timeout while a slow query is still planning. Disable response buffering for the export route and send the header row immediately.

Once a 200 and the headers are sent, you cannot change the status code. If the query fails at row 400,000, the client receives a truncated file that looks valid. To make truncation detectable, end the file with a trailer row containing the row count, or a checksum the consumer can verify. For large or slow exports, these mitigations are not enough. Use a job instead.

Factor Streamed HTTP response Background job
Typical size and duration Small to medium, finishes within timeouts Large or slow
Failure visibility Truncated file, needs trailer or count Job marked failed, retried
Proxy timeouts Must be tuned per route Not involved
Moving parts Few Queue, storage, links, notifications

A job writes to a temp file or a multipart object-storage upload, then sends a download link. That adds several pieces to build and run. You need a queue. You need lifecycle rules that delete old exports. You need expiring signed URLs and a notification path. Retries must also be idempotent, keyed by export ID, so a worker crash doesn't produce duplicate files or emails.

How do you handle backpressure end to end?

Every stage between the database and the client runs at its own speed. A mobile client on a weak connection, or an object-storage upload with a slow multipart part, can easily drain bytes more slowly than Postgres produces rows. If the writer keeps fetching anyway, the excess has to go somewhere, usually an in-process buffer that grows until the worker dies. The fix is to have the writer block. When the downstream sink is full, the next cursor fetch should wait, which leaves rows in Postgres where they cost no application memory.

Confirm the framework actually blocks

Many streaming response APIs accept an iterator or generator but say little about flow control. In Node, write() returns false when the internal buffer passes its high-water mark, and you must wait for drain before writing again. A pipeline that ignores the return value buffers without limit. In async Python frameworks, check that the server awaits each chunk send before pulling the next item from your generator. To test this, request an export with curl --limit-rate 10k and watch the worker's resident memory. It should stay flat, and the database session should spend most of its time idle between fetches.

Check the proxy layer too

Correct application code doesn't guarantee flat memory end to end. A reverse proxy or load balancer can read the whole upstream response into memory or a temp file before forwarding it. That breaks backpressure and can hide the problem from your app metrics. In nginx, disable proxy_buffering for export routes or send X-Accel-Buffering: no. For any other layer, repeat the rate-limited curl test through the real ingress path, and watch both the proxy's disk and memory and the time to first byte.

How do you verify flat memory and run exports safely in production?

Measure memory at several report sizes

Run the same export at three sizes, for example 10,000, 1 million and 10 million rows, and sample the worker's resident memory (RSS) every second. A correct streaming path plateaus within the first few batches and stays at that level until the export finishes. If peak memory grows with row count, something is still buffering.

Illustrative estimate. Assume rows average 200 bytes as CSV and the cursor fetches 1,000 rows per batch. Each batch then holds about 200 KB of text, plus driver and writer buffers, so the export should add only a few megabytes above baseline at any size. A buffered implementation at 10 million rows would hold about 2 GB of raw CSV, before language object overhead.

While exports run, query pg_stat_activity for export sessions with an old xact_start. A long-open transaction holds back the xmin horizon, so vacuum cannot remove dead rows and tables bloat.

Operational safeguards

  • Isolate the read. Run exports on a read replica or a dedicated connection pool. Long queries on a replica can be cancelled by recovery conflicts, so build in retries.
  • Set statement_timeout on the export role or session. A runaway query then fails cleanly instead of holding a connection open indefinitely.
  • Cap concurrency per tenant. One customer requesting twenty reports should wait in a queue, not exhaust the pool that serves normal requests.
  • Clean up on failure. Delete temp files and abort partial multipart uploads in a finally path. Orphaned parts otherwise accumulate storage cost without anyone noticing.

These checks replace a recurring pattern: an OOM page, followed by a one-off patch for whichever report triggered it, repeated on the next large customer. A size-independent memory test in CI catches buffering regressions before they reach production.

Impact on development cost

The main cost change is that export memory no longer depends on data size. With buffering, the largest customer's largest report determines instance memory for every node that might run an export. With streaming, memory depends on batch size, which you choose and which stays fixed.

Illustrative estimate (assumed numbers, not a benchmark)

Assumed inputs:

  • Largest export: 5,000,000 rows
  • In-memory size per row (ORM object plus CSV string): 2 KB
  • Streaming batch size: 1,000 rows
  • Buffering-related incidents: 6 per year
  • Engineering time per incident (response, patch, review): 10 hours

Memory formulas:

  • Buffered peak ≈ rows × bytes per row = 5,000,000 × 2 KB ≈ 10 GB
  • Streamed peak ≈ batch size × bytes per row + fixed overhead = 1,000 × 2 KB ≈ 2 MB + overhead

In the buffered case, every worker that can serve an export needs roughly 10 GB of headroom on top of its normal working set. That headroom sits unused almost all the time, but you pay for it on every node. In the streamed case, export workers can use the same instance size as the rest of the tier.

Engineering time follows the same pattern: incidents per year × hours per incident = 6 × 10 = 60 engineer-hours per year spent on reactive work. These hours tend to repeat because each patch fixes one report rather than the buffering pattern.

The one-time cost is converting existing exports to cursors and incremental writers, and adding the CI memory test. Put your own row counts, row sizes and incident history into the formulas above to see whether that conversion pays for itself within your planning horizon.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to stream large csv export, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Changing a route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • Consumers learn about a breaking change at review time rather than from production errors.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.