streaming-export-safety

Streaming Export / Large Object Memory Safety

When building data in-memory in one go exceeds 10,000 rows / 10 MB, you must switch to a streaming API. Otherwise, common consequences include:OOM, Full GC storms, leftover temporary files, and HTTP timeouts.

Decision: When is streaming mandatory?

Data volume	Recommended mode	Notes
≤ 10,000 rows / ≤ 5 MB	In-memory building OK	XSSFWorkbook / pandas / regular JSON serialization
10,000 ~ 100,000 rows	Strongly recommendedstreaming	A single machine can handle it, but there is an OOM risk
> 100,000 rows / > 50 MB	Mandatorystreaming + async	The in-memory model will blow up
Unknown data volume (user-driven)	Mandatorystreaming + upper-limit protection	Always design for the worst case

Core judgment: Can the upper limit be estimated? If the estimate exceeds 10,000 rows, use streaming.

Trap #1: Excel large data → XSSFWorkbook full loading OOM

Sniffing signals

Immediately suspect the following patterns when seen in code review:

new XSSFWorkbook() + loop createRow exceeds 10,000 iterations
EasyExcel.write(...).sheet().doWrite(data) in data.size() > 10000
openpyxl.Workbook()(without write_only=True) + loop append
excelize.NewFile() + SetCellValue with many iterations
ExcelJS.Workbook()(without using the stream submodule)

Correct approach: Java + Apache POI SXSSF

// ❌ XSSF 全量内存（10w 行直接 OOM）
XSSFWorkbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet();
for (int i = 0; i < 100_000; i++) {
    Row row = sheet.createRow(i);
    row.createCell(0).setCellValue("data");
}

// ✅ SXSSF 流式（windowSize=100，超出窗口写临时文件）
SXSSFWorkbook wb = null;
try {
    wb = new SXSSFWorkbook(100);
    wb.setCompressTempFiles(true);
    Sheet sheet = wb.createSheet();
    for (int i = 0; i < 100_000; i++) {
        Row row = sheet.createRow(i);
        row.createCell(0).setCellValue("data");
    }
    try (OutputStream os = new FileOutputStream("out.xlsx")) {
        wb.write(os);
    }
} finally {
    if (wb != null) {
        wb.dispose();
    }
}

SXSSF limitations (mustbe communicated to callers)

❌ Cannot randomly access rows that have been flushed (once beyond windowSize, that row is unreadable)
❌ Does not support automatic formula calculation (requires pre- evaluator.evaluateAll()）
❌ Does not support cloneSheet, and does not support merging multiple Sheets that have already been written out
⚠️ Temporary files are by default in java.io.tmpdir; in container environments pay attention to disk quotas
⚠️ Must dispose(), otherwise temporary files are left behind

EasyExcel (Alibaba) streaming write

// ✅ EasyExcel 默认就是流式，分批写入
EasyExcel.write("out.xlsx", DataDTO.class)
    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
    .sheet("data")
    .doWrite(() -> {
        // 分页拿数据，每次返回一批
        return loadBatch(pageNo++, BATCH_SIZE);
    });

Core APIs in other languages

Language	Streaming API	Key differences
Python	`openpyxl.Workbook(write_only=True)`	❌ Cannot use `ws.cell()`, can only `ws.append(row)`，cannot be written back
Python	`pandas.read_excel(chunksize=N)`	Read side batches; write side uses `ExcelWriter(engine='openpyxl')`
Go	`f.NewStreamWriter("Sheet1")` (excelize v2.x)	Must `sw.Flush()`, otherwise data is lost
Node.js	`new ExcelJS.stream.xlsx.WorkbookWriter({filename})`	Each row `row.commit()`, finally `workbook.commit()`

See references/multi-lang-examples.md

Trap #2: CSV large file → whole-file readlines / List concatenation

Incorrect example

// ❌ 一次性读入全部行
List<String> lines = Files.readAllLines(Paths.get("huge.csv"));

// ❌ 一次性构建全部字符串
StringBuilder sb = new StringBuilder();
for (Record r : records) sb.append(r.toCsv()).append("\n");
Files.write(path, sb.toString().getBytes());

Correct approach

// ✅ 读：BufferedReader 逐行
try (BufferedReader reader = Files.newBufferedReader(path)) {
    String line;
    while ((line = reader.readLine()) != null) {
        processLine(line);
    }
}

// ✅ 写：OpenCSV CSVWriter 逐行 flush
try (CSVWriter writer = new CSVWriter(new FileWriter(path))) {
    for (Record r : records) {
        writer.writeNext(r.toArray());
    }
}

Core APIs in other languages

Language	Write	Read
Python	`csv.writer(f).writerows(iter)`	`csv.reader(f)` iterator / `pandas.read_csv(chunksize=N)`
Go	`csv.NewWriter(f)` + `w.Flush()` must be called	`csv.NewReader(f).Read()` line by line
Node.js	`csv-stringify` stream	`csv-parse` stream

Trap: Go's csv.Writer does not flush automatically,forgetting to call w.Flush() leaves all data in the buffer。

Pitfall #3: Exporting large JSON arrays → ObjectMapper.writeValueAsString

Incorrect example

// ❌ 整数组先序列化为 String，再写文件
List<Order> orders = repo.findAll(); // 100w 行
String json = objectMapper.writeValueAsString(orders);  // OOM
Files.write(path, json.getBytes());

Correct approach: JsonGenerator streaming

// ✅ Jackson JsonGenerator 流式写
try (JsonGenerator gen = objectMapper.getFactory()
        .createGenerator(new FileOutputStream(path), JsonEncoding.UTF8)) {
    gen.writeStartArray();
    int page = 0;
    Page<Order> orderPage;
    do {
        orderPage = repo.findAll(PageRequest.of(page++, 1000));
        for (Order o : orderPage.getContent()) {
            objectMapper.writeValue(gen, o);
        }
    } while (orderPage.hasNext());
    gen.writeEndArray();
}

Recommended alternative: JSONL (line-delimited JSON)

For extremely large data volumes, choose the output format JSONL(one JSON object per line) — it is friendlier than a standard JSON array:

{"id":1,"name":"a"}
{"id":2,"name":"b"}

Downstream can parse line by line without needing to read the entire file first.

Core APIs in other languages

Language	Streaming API
Python	`orjson` single dumps + write `.jsonl`; or `ijson` stream reading
Go	`json.NewEncoder(w).Encode(v)` object by object (naturally produces JSONL)
Node.js	`JSONStream.stringify()`

Pitfall #4: Large PDF reports → full-document prerendering

Incorrect example

// ❌ 一次性把所有 Page 加到 Document，再 save
PDDocument doc = new PDDocument();
for (int i = 0; i < 10_000; i++) {
    doc.addPage(buildPage(data.get(i)));  // 全部 Page 在内存
}
doc.save(path);

Correct approach: write in batches + flush

// ✅ iText 流式：每个 Page 写完立即 flush
try (PdfDocument doc = new PdfDocument(new PdfWriter(path))) {
    Document layout = new Document(doc);
    for (Order o : orders) {
        layout.add(buildParagraph(o));
        if (orderIndex++ % 100 == 0) {
            doc.getWriter().flush();  // 100 条 flush 一次
        }
    }
}

Core APIs in other languages

Language	Streaming API	Key operations
Python	`ReportLab Canvas`	After each page `c.showPage()` then `c.save()` incremental write
Go	`gofpdf.New(...)` + `pdf.AddPage()`	`OutputFileAndClose(path)` write out in one go (when memory is large consider `unidoc` streaming)
Node.js	`PDFKit`	`doc.pipe(fs.createWriteStream(path))`

Note: many PDF libraries (such as PDFBox, jsPDF)do not have a true streaming API — the entire document must stay in memory. For large data volumes consider:

Split into multiple PDFs
Switch to a professional streaming library (iText commercial, ReportLab)
Go async + file storage

Pitfall #5: Large data export synchronously blocks the HTTP thread

Problem

A synchronous export > 30s will be cut off with 504 by Nginx / the gateway; the HTTP thread is occupied for a long time, affecting other requests.

Incorrect example

// ❌ Controller 同步生成 + 返回
@GetMapping("/export/orders")
public ResponseEntity<byte[]> exportOrders() {
    byte[] excel = buildExcelInMemory();  // 占用 HTTP 线程 60s+
    return ResponseEntity.ok().body(excel);
}

Correct approach: task-based + polling/notification

// ✅ 接口立即返回任务 ID
@PostMapping("/export/orders")
public ExportTaskDTO export() {
    String taskId = exportTaskService.submit(...);
    return new ExportTaskDTO(taskId, "PROCESSING");
}

// ✅ 异步执行 + 写到对象存储
@Async("exportExecutor")
public void execute(String taskId, ...) {
    try (SXSSFWorkbook wb = new SXSSFWorkbook(100)) {
        // 流式写
        File temp = streamToFile(wb);
        String url = ossClient.upload(temp);
        taskRepo.complete(taskId, url);
    }
}

// ✅ 查询接口
@GetMapping("/export/tasks/{id}")
public ExportTaskDTO query(@PathVariable String id) { ... }

See async-task-pattern skill — the state machine, timeouts, retries, and idempotency of asynchronous tasks

General checklist

Whether exports whose estimated data volume exceeds 10,000 rows use a streaming API
Whether temporary file cleanup is done in try-finally / defer / with / using (SXSSF must dispose()）
Whether the temporary directory has checked the disk quota (in container environments /tmp may be very small)
Whether the limitations of the streaming API have been communicated to the caller (cannot write back / formulas not supported / must flush)
Whether the export interface is asynchronous (task ID + polling / WebSocket notification)
Error recovery: whether temporary files are cleaned up when streaming writes are interrupted
Whether a data volume upper limit is set (to prevent users from passing pageSize=999999）
Whether large files are uploaded to object storage rather than returned from memory

Related skill

async-task-pattern — the state machine, timeouts, and idempotency of asynchronous tasks
query-performance-safety — prevent N+1 and overly long IN clauses during export
field-mapping-safety — field mapping consistency during DTO conversion

Rule provenance

> 📋 本回复遵循：`streaming-export-safety` - [章节]

About this skill

Decision: When is streaming mandatory?

Trap #1: Excel large data → XSSFWorkbook full loading OOM

Sniffing signals

Correct approach: Java + Apache POI SXSSF

SXSSF limitations (mustbe communicated to callers)

EasyExcel (Alibaba) streaming write

Core APIs in other languages

Trap #2: CSV large file → whole-file readlines / List concatenation

Incorrect example

Correct approach

Core APIs in other languages

Pitfall #3: Exporting large JSON arrays → ObjectMapper.writeValueAsString

Incorrect example

Correct approach: JsonGenerator streaming

Recommended alternative: JSONL (line-delimited JSON)

Core APIs in other languages

Pitfall #4: Large PDF reports → full-document prerendering

Incorrect example

Correct approach: write in batches + flush

Core APIs in other languages

Pitfall #5: Large data export synchronously blocks the HTTP thread

Problem

Incorrect example

Correct approach: task-based + polling/notification

General checklist

Related skill

Rule provenance