Operations needs to export 100,000 order rows at once, but the API keeps running out of memory—do we have to make them export in batches?

Operations needing to export 100,000 order rows at once while the API runs out of memory does not mean they can only be told to export slowly in batches. Based on 2026 project delivery experience, the root cause is usually three things stacked together: reading the entire result set into memory at once, building all row objects at once, and then serializing the whole file out at once. Split the export into “streaming read, batched transform, streaming write,” and memory usage changes from growing with the row count to mainly tracking the single-batch size; a typical range for one batch is 500–2,000 rows.

The problem with exports draining memory is often not the row count itself

An export pipeline goes through several stages: query, result-set loading, entity construction, table assembly, serialization, and response writing. Each stage can accumulate the full data set in memory. Row count is only an amplifier; what really determines usage is “how much data resides in memory at the same time.” Split 100,000 rows into batches of 1,000, and the theoretical resident amount drops to roughly the scale of one batch.

This matters because an export OOM does not only mean the export fails. In a shared deployment, once the instance restarts, order placement, listing, and payment callback queries on the same machine all wobble with it. From project delivery experience, the export feature itself is not complex; the hard part is that it quietly ties the stability of the whole service to itself.

• Typical amplification point 1: the query uses select * and pulls back remarks, snapshots, and large JSON fields together; a single row may be 5–10 times the size of the fields actually needed for display.
• Typical amplification point 2: using an ORM to query an entity list, then iterating to convert it into export objects, means two full copies of the data exist in memory at the same time.
• Typical amplification point 3: the table component first builds all rows in memory and writes the file only at the end; exporting 100,000 rows can occupy hundreds of MB to several GB.
• Typical amplification point 4: export requests and online APIs share one connection pool; long export transactions hold connections, and other APIs cannot get in line.

Synchronous direct export, batched streaming, or async jobs: how to choose among the three

Use “whether the user can wait” and “whether the data is large” as two decision axes, and export approaches roughly fall into three tiers. The basis for the split is: once waiting time exceeds the gateway’s and the user’s patience, no amount of optimization to a synchronous approach will keep it from being interrupted by a timeout. According to common 2026 delivery conventions, you can first do an initial screen with the following set of experience ranges.

• Synchronous direct export: suitable for hundreds to a few thousand rows; user waits seconds to a dozen or so seconds; low implementation cost. The main cost is that when the same API has no row limit, operations can casually apply a broad filter and turn it into a full export.
• Batched streaming synchronous export: suitable for a few thousand to tens of thousands of rows; user waits tens of seconds to a few minutes; medium implementation cost. The main cost is longer request duration; gateway timeouts, network interruptions, and user refreshes can all make the export run for nothing.
• Async job: suitable for tens of thousands to hundreds of thousands of rows; user waits minutes and needs to poll progress; higher implementation cost. The main cost is that you need to build a task table, state machine, failure retries, and expiration cleanup.

The boundary can be summarized in one sentence: forcing async jobs for a few thousand rows or fewer often costs more than it is worth; insisting on synchronous export beyond 10,000 rows makes timeouts and OOM only a matter of time.

Implementation checklist: the three-stage export split

Divide into read, transform, and write stages because memory is blown up by “holding everything at the same time.” After splitting, each stage holds only its own small portion, and the problem changes from “what do we do with 100,000 rows” to “how large is a batch and how often do we flush.”

1. Read stage: use a cursor or query in batches by primary-key range; a typical range for one batch is 500–2,000 rows; select only the columns the export truly needs and bypass large fields; use a read replica when possible so the export does not slow down the primary database.
2. Transform stage: as soon as a batch of rows is converted to text or cells, hand it to the write stage immediately instead of accumulating a list in memory; put dictionary translation and date formatting here, but be careful not to query the dictionary once per row, which turns into N+1 queries.
3. Write stage: use a component that supports streaming writes or CSV and flush as you write, writing directly to the response stream or a temporary file, flushing the buffer once per batch; if a write fails, record the batch position so it can resume if needed.

Every stage needs limits: a per-batch row limit, a per-export row limit, a single-file size limit, and an overall timeout limit. If a limit is exceeded, switch to async or prompt the user to narrow the conditions. Acceptance can look at two criteria: when exporting 100,000 rows, the process peak memory does not visibly rise linearly with row count and, by experience, stays within the hundreds-of-MB range; during the export, the P95 of other APIs on the same instance does not visibly rise.

A common constraint at the delivery site is a tight schedule, a limited budget, and a database that is still a single primary. The usual approach is to get the first version through with read-replica reads, 1,000 rows per batch, and synchronous CSV export; the typical waiting range is a few minutes, and the cost is that it can only run during off-peak hours and operations may retry. If you ignore the time window and directly export 100,000 rows on the primary during peak hours, the P95 of the online listing API will rise accordingly, and ultimately you still need to rework the query time window and batch arrangement.

Common pitfalls and counterexamples

When exports go wrong, it is usually not because streaming was not written, but because several details were not covered. The following are repeatedly encountered at delivery sites.

• Only increasing heap memory: no OOM in the short term, at the cost of longer GC pauses and other APIs on the same instance slowing down during the export.
• Using offset for deep pagination in batches: later pages have to scan all preceding rows, making the export slower and slower; advancing by primary-key range or cursor is more stable.
• No cancellation mechanism: when the user closes the page, the background job keeps running, wasting connections and CPU, and this easily causes cascading issues during export peaks.
• Async jobs without idempotency: the user double-clicks and two files are generated, doubling database pressure; a common practice is to deduplicate with a request ID or task lock.
• Writing files to local disk: in a multi-instance deployment, a download request may land on an instance that does not have the file; a common practice is to store it in object storage and then provide a download link with an expiration time.
• Using Excel for all data: heavy format, high time cost; for pure verification scenarios CSV is more suitable and opens faster.

Applicable scenarios and boundaries

Scenarios suitable for async export: operations reconciliation, batch verification, audit archiving, and data extraction before migration. Their characteristics are many fields, tens of thousands of rows, and a need for repeatable downloads and audit trails. According to enterprise project delivery habits, for backend exports you first confirm with operations the per-export row count and acceptable waiting time, then decide whether to go synchronous or async, avoiding rework after delivery because operations casually filters for the full data set.

The cases that do not need a heavyweight solution are equally clear: hundreds to a few thousand rows, exported occasionally, with synchronous waiting acceptable—just use batched streaming synchronous export directly. No task table, message notifications, or scheduled cleanup are needed. For internal data extraction where the row count is still uncertain, adding a row limit and filter constraints first is more effective than piling on architecture first.

Boundary in one sentence: how heavy the export solution should be depends on the combination of three variables—export row count scale × export frequency × acceptable waiting. If all three are small, use a lighter approach.

Frequently asked questions

For exporting 100,000 rows, is CSV or Excel better?

For pure data verification, prefer CSV: smaller size, faster writes, lower memory usage. Use Excel when you need formatting, multiple sheets, or something to show non-technical colleagues, and make sure it writes in a streaming way.

Should the export API have its own rate limiting?

Rate limiting or queuing is recommended. By experience, running 2–3 export jobs simultaneously on a single instance is enough to crowd out the connection pool; queuing excess requests or switching them to async is more stable.

After an async export is complete, how should operations be notified?

Polling task status on the page at an experience range of 2–5 second intervals and showing a download button is relatively reliable; email easily goes to spam, and SMS is only necessary when the task takes a very long time.

How long should exported files be kept on the server?

Decide based on compliance requirements and disk cost; a typical range is 3–30 days, with automatic cleanup on expiration and the ability to regenerate, avoiding historical files filling up the disk.

If exports are slow, will adding an index fix it?

Not necessarily. Indexes improve the query stage; if the time is spent in object construction and file writing, adding an index helps little. Look at the time distribution first, then decide what to change.

If you need to start now, first set the export row limit, batch size, and timeout, then decide whether to go synchronous or async; when the row count does not exceed a few thousand, there is no need to rush into a task table and notification mechanism. Before delivery, run a load test with data close to the real scale, confirm that other APIs do not visibly slow down during the export, and then hand it over to operations for long-term use.