Building an ERP Export Engine on a 3 GB Server

Building an ERP Export Engine on a 3 GB Server

How I built a Node.js export engine for BuildEnfra on a 3 GB server using batched PostgreSQL reads, streaming XLSX, BullMQ jobs, and pooled Chrome rendering.

performancestreamingnodejspostgresqlprismapuppeteerexceljsbullmqsystem designcase study

Two unfiltered material issue note exports pushed production memory from 57% to 77%. Each request loaded about 44,900 records with their relations, took at least eight seconds, and returned just 300 rows.

This happened at BuildEnfra, a construction ERP I work on at Tracks And Towers Infratech. It runs a Next.js front end and an Express API backed by Prisma and PostgreSQL. In production, two Node workers share a container with 3 GB of memory and 1.5 CPUs. More about my work on BuildEnfra.

I built a shared Node.js export engine for 20 high-volume lists. It reads PostgreSQL in batches, streams spreadsheet rows, and sends large jobs to BullMQ. For PDF, it reuses Chrome and renders large documents in parts. Each layer has a separate limit so exports leave room for the ERP's API traffic. The code samples omit production wiring.

What changed

WorkloadPrevious pathRebuilt path
Material issue notes300 rows returned, 13 s p95 in production logs44,929 records exported in the benchmark, ~0.7 s to acknowledge the job and 4–6 s to build the XLSX
Issue request export~44,660 SQL statements29 statements
Head-office purchase requisition export~74,600 SQL statements52 statements
20 simultaneous PDF requests on Windows dev5.2–5.8 GB across Chrome processes, up to 13 failures in some runs~455 MB across Chrome processes, all 20 completed in the measured run
300-row requisition PDF1,600 KB, 25 pages255 KB, 22 pages after the layout and rendering changes

The old export timings came from production logs. I benchmarked the new spreadsheet path separately against production data from a machine about 27 ms from the database. The job acknowledgement is when the user gets a response. The XLSX build time measures file generation. The Chrome figures came from a Windows dev benchmark that measured the whole Chrome process tree. The 5.2–5.8 GB result shows why the old design was a poor fit for a 3 GB container.

The three problems

1. The row cap came after the expensive read. Select-all loaded every row and its relations into memory, then kept only 300 for the file. One list had about 45,000 rows.

2. Some exports ran one query per row. Old query counts reached roughly 44,660 statements for issue requests and 74,600 for head-office purchase requisitions. Those counts exposed where to fix the data access pattern before tuning the file writer.

3. Every PDF launched its own Chrome. There was no admission limit. In the Windows dev test, 20 simultaneous requests spawned 146–149 Chrome processes. In some runs, up to 13 failed to launch a browser.

The fixes followed the failure modes. I batched reads to cut query fan-out, streamed Excel rows to release cell objects early, and capped how many PDFs could render at once. For large exports, I moved the work to a queue so it could wait its turn without holding an HTTP request open.


1. Fix the export order, then fetch rows in batches

I gave each list a small data source and kept batching, file writing, and job handling in the shared engine. A source reuses the screen's filter builder where applicable, selects ordered IDs, and then fetches the fields that format needs. That leaves list-specific choices, such as grouping tracker rows by vehicle or keeping only the finalised quote, with the source.

  1. One query for the ordered IDs. It applies the screen's filters, search and sort.
  2. Small queries for the full export columns, 2,000 IDs at a time.
Code
interface PreparedExport {
  count(): Promise<number>;
  fetchIds(take: number): Promise<RecordId[]>;
  loadRows(ids: RecordId[]): Promise<Map<RecordId, ExportRow[]>>;
}

Why IDs instead of OFFSET. PostgreSQL still computes the rows it skips, so deep offsets can get expensive. With separate page queries, inserts or deletes can also shift the page boundaries. Capturing the ordered IDs up front gives the exporter a fixed list to work through. Reassemble each batch in that ID order because WHERE id = ANY(...) does not preserve it. The ID list itself grows with the export size, and later queries can still see changed or deleted rows. If an export needs a point-in-time snapshot, choose a consistency strategy explicitly. PostgreSQL pagination and transaction isolation.

Why join in batches. The old paths loaded some relations row by row. Issue requests now load each batch with a joined query and took 29 statements end to end in the benchmark. Head-office purchase requisitions still need several queries per batch for related totals and took 52 statements end to end. Prisma can load relations with different strategies, so inspect the SQL your own query runs. Prisma query optimization.

Count only when you must. To decide between "build now" and "build in the background", the engine fetches limit + 1 ids. The COUNT(*) runs only if that extra id comes back.

Use a unique tie-breaker such as id for a deterministic order when many rows share the same timestamp.

2. Read-ahead: keep the database busy while you write

Fetching a batch costs a network round trip, and writing it costs CPU. I kept up to three reads in flight so the writer could work while the next batch loaded. The generator consumes those reads in order, even when a later query finishes first.

Code
async function* readExportRows(loadRows, ids, batchSize = 2000, readAhead = 3) {
  const inFlight: Promise<ExportRow[]>[] = [];
  let next = 0;
  const fill = () => {
    while (inFlight.length < readAhead && next < ids.length) {
      const batchIds = ids.slice(next, next + batchSize);
      next += batchSize;
      const pending = Promise.resolve().then(async () => {
        const rowsById = await loadRows(batchIds);
        return batchIds.flatMap((id) => rowsById.get(id) ?? []);
      });
      // Attach a handler now. Awaiting the original promise still propagates errors.
      void pending.catch(() => {});
      inFlight.push(pending);
    }
  };
  fill();
  for (let batch = inFlight.shift(); batch; batch = inFlight.shift()) {
    const rows = await batch;
    fill(); // queue the next query before writing this batch
    yield rows; // the consumer writes while the next batch loads
  }
}

What this bounds. The example holds up to three prefetched batches plus the batch being written. At 2,000 IDs per batch, that is up to 8,000 IDs represented by the current and prefetched batches. A purchase order or quote request can expand to several output rows per ID. The full ID list, row width, writer buffers and other exports still count against the worker's memory budget.

An early failure or a disconnected client can leave prefetched queries running. The rejection handler in the example prevents an unhandled rejection. It does not cancel those queries. Production code should stop scheduling new work and account for outstanding reads during cleanup.

3. Stream rows as the workbook is built

The idea. The usual way to build an Excel file is to create a Workbook, add every row, then call writeBuffer(). That keeps an object for every cell of the whole sheet in memory until the end. A streaming writer instead commits each row as soon as it is written: the row is serialised to XML, compressed into the output, and released.

Code
// output is the writable sink that collects the finished XLSX bytes.
const wb = new ExcelJS.stream.xlsx.WorkbookWriter({
  stream: output,
  useStyles: true,
});
const ws = wb.addWorksheet('Issue Notes');

for await (const batch of readExportRows(loadRows, ids)) {
  for (const row of batch) ws.addRow(toCells(row)).commit();
}
ws.commit();
await wb.commit();

Committing rows releases ExcelJS's row objects. The writer still returns a Buffer for the finished file, so this streams workbook construction while keeping output bytes in memory. The 44,929-record issue note export peaked at about 109 MB of extra Node heap for XLSX and 40 MB for CSV. The XLSX reading includes garbage the runtime had yet to collect. I use those figures as workload measurements, because row width, compression and retained bytes affect the result. ExcelJS streaming I/O and Node.js streams.

Style reuse made a difference. On a 45,000-row styled worksheet, building new style objects per cell made writing about three times slower. I shared styles for plain cells, money and totals.

What changes when you stream. You can't go back to a row you've already committed:

  • Totals are kept as running sums and written once, at the end.
  • Column widths are sized from the first 200 rows.

CSV encodes rows directly. Some old paths built an XLSX file and parsed it back to produce CSV, so a dedicated writer eliminated the extra work. It also preserves the UTF-8 BOM that Excel uses to decode currency symbols and non-Latin names correctly.

4. Queue large exports and control admission

Building a large file still takes CPU time even with a streaming writer. For the paged lists, Excel and CSV exports can run inline through 5,000 records. With a client that supports background export, 5,001–100,000 records go to a dedicated BullMQ queue backed by Redis. The client shows progress, downloads the file when it is ready, and keeps it available on the Downloads page. PDFs have a separate cutoff because rendering has a different cost profile.

Three details make this robust:

  • Global concurrency of 1 on the export queue. At most one job from that queue runs across its workers. This trades throughput for headroom on the shared container. Synchronous requests still consume capacity separately. BullMQ global concurrency.
  • Deduplication by request hash. A repeat request from the same user can join a running job or reuse that user's finished file. The request fingerprint includes the list, filters, search, columns and format. The user can also request a fresh version explicitly.
  • A per-user limit enforced with a Postgres advisory lock. "At most 2 live jobs per user" is a check-then-insert, and two fast clicks can both pass the check. Taking a transaction-scoped lock keyed on the user makes those two requests run one after the other:
Code
SELECT pg_advisory_xact_lock(hashtext($1)); -- $1 = user id; held until COMMIT
-- now count the user's live jobs and insert the new one, race-free

Finished files go to private storage, can only be downloaded by their owner, and expire after seven days. If Redis or the job table is unavailable, Excel and CSV fall back to an inline build. PDFs fall back to a capped inline export with a notice. The spreadsheet fallback keeps the feature available, but a queue outage can shift expensive work back onto the API. That is a remaining operational risk.

5. One pooled browser instead of one per PDF

PDFs are HTML rendered by headless Chrome (Puppeteer). Chrome is by far the most expensive thing on the server, and starting one per PDF is what produced 148 processes.

The idea: treat the browser like a database connection pool. Each Node process keeps one Chrome, started on first use, and every render borrows a short-lived piece of it.

  • A browser context per render. The context provides separate browser storage. Closing it cleans up the pages for that render. Chrome still shares process resources, which is why the pool also needs a lifecycle. Puppeteer browser contexts.
  • A weighted semaphore per worker. Each worker has 2 units. A small PDF takes 1 and a large one takes both. With two workers, the container can admit up to four small renders, two large ones, or a mix within those per-worker limits.
Code
class WeightedSemaphore {
  private used = 0;
  private queue: { weight: number; resolve: () => void }[] = [];
  constructor(
    private capacity: number,
    private maxQueue: number,
  ) {}

  async acquire(weight: number) {
    if (this.used + weight <= this.capacity && this.queue.length === 0) {
      this.used += weight;
      return;
    }
    if (this.queue.length >= this.maxQueue) throw new BusyError(); // → 503 + Retry-After
    await new Promise<void>((resolve) => this.queue.push({ weight, resolve }));
  }

  release(weight: number) {
    this.used -= weight;
    // FIFO: wake waiters in order while they fit
    while (
      this.queue.length &&
      this.used + this.queue[0].weight <= this.capacity
    ) {
      const next = this.queue.shift()!;
      this.used += next.weight;
      next.resolve();
    }
  }
}
  • A bounded queue with a deadline. The production queue allows up to 20 waiting requests and a 20-second wait. When admission fails, the server answers 503 with Retry-After. The simplified semaphore above shows admission order and capacity. A timed-out waiter also needs to be removed so it can't later acquire a slot.
  • Separate render and cleanup deadlines. Inline renders time out after 60 s. If the context or browser won't close within 5 s, the process tree is killed. A Chrome crash gets one retry on a fresh browser. Template errors and timeouts stay final at the render layer.
  • A lifecycle. Chrome closes after 60 s idle and is recycled after 50 renders or 15 minutes. On shutdown, the process drains active HTTP requests before closing the shared browser. Per-render logs separate time spent waiting for a slot from time spent rendering and distinguish cold starts from warm renders.

In the Windows dev stress test, 20 concurrent requests completed with 9–10 Chrome processes and about 455 MB across the browser process trees. The requests arrived together and the semaphore limited how many rendered at a time.

6. Render large PDFs in parts

Page count was the better signal. On Windows dev, Chrome memory tracked printed pages more closely than row count or HTML size. The observed range was roughly 7–13 MB per page for those layouts.

Rows in one PDFChrome peak
5000.41–0.66 GB
1,0000.64–1.0 GB
10,000 in one render7–14.5 GB, and 2 of 5 layouts failed
10,000 as 500-row parts0.58–0.67 GB

The idea: render a large PDF as many small ones, then merge them.

  • Each part renders 500 rows with the same template.
  • Serial numbers carry on from the previous part.
  • The letterhead prints only on the first part, and the grand total only on the last.
  • The parts are joined with pdf-lib.
Code
const merged = await PDFDocument.create();
for (let start = 0; start < rows.length; start += 500) {
  const part = await renderPdf(buildHtml(rows, { start, end: start + 500 }));
  const doc = await PDFDocument.load(part);
  const pages = await merged.copyPages(doc, doc.getPageIndices());
  pages.forEach((p) => merged.addPage(p));
}

The page-number problem. Chrome numbers pages within one render, so every part would start at page 1. I rendered the parts without footers, then overlaid page numbers after the merge, when the total page count was known. In one Windows dev test with 10,000 synthetic goods receipt rows, the 20 parts produced a 1,010-page PDF in 25.5 s with a 568 MB Chrome private-memory peak. Node still holds the selected records and the merged document, so measure its memory separately.

For supported lists, the PDF cutoff is 500 records inline and 501 to 10,000 records in a job. A record can print as several rows, so the limit is a routing rule. Page density still guides the rendering budget.

7. PDF tagging trades file size for document structure

Puppeteer's PDF options default to tagged output. Tags describe structure such as headings, tables and reading order to assistive technology. Generating that structure for thousands of cells also has a rendering cost. Puppeteer PDF options.

With tagged: false, the tested table exports were 80–90% smaller and rendered 25–45% faster. A pixel comparison across 25 layout and size combinations found no visual differences. Approval documents such as purchase orders, bills and payslips saw about a 21% size reduction.

Pixel parity only checks appearance. Turning off tags changes what assistive technology can read, so keep them wherever readers need that structure or accessibility requirements apply.


Verify the data before calling it faster

Speed means nothing if the numbers in the file change. Three habits carried the project:

  • Compare through the real handlers. I ran the old and new handlers against the same dev data, read the XLSX cells back, parsed CSV, and captured the PDF HTML. The review covered 75 request bodies across all three formats, plus targeted cases for inventory, procurement and machinery. It caught regressions in rounding, date formatting, and empty sheets that a faster benchmark would have missed.
  • Test the job lifecycle. On dev, I checked duplicate submissions, owner-only downloads, stalled jobs, expiry cleanup, and the paths for Redis or the job table being unavailable.
  • Check printed output separately. I compared chunked and single-render PDFs for six layouts at 1,200 rows. The text order, serial numbers and totals matched. The letterhead and grand total appeared once, and page numbers carried through the merged file. A separate full-column layout audit caught IDs that needed break points at slashes and hyphens.

Roll out the server before the web

I designed the rollout so older clients could still use their existing capped requests. The new server understands the old row limit and applies it when reading the data. The updated web opts into jobs and handles the progress and download responses. The rollout order is to create the job table and private storage, deploy the server, then enable the new path in the web client. A runtime switch can route PDF requests back to the old browser handler if the shared browser causes a regression.

What I traded for predictable exports

  • On one list, a full export now takes about 3 s, where small filtered exports used to take 0.3 s. It now returns all 2,175 records instead of 300.
  • Small lists run 4–6 queries instead of 2: an access check, the id query, then the rows.
  • Marking a background job as failed at its deadline doesn't cancel its underlying database or storage work. Cancellation remains separate from job status.

The first comparison covers different amounts of work. The second is overhead the new path adds even on small lists. The last gap matters when sizing a worker for retries and slow dependencies.

Takeaways

  1. Start with the failure mode. Use query counts to find database fan-out, heap measurements to find retained rows, and concurrent tests to measure Chrome's footprint.
  2. Give each layer its own limit. Bound rows in flight, jobs admitted, and renders running. Measure file buffers and merge memory separately.
  3. Treat correctness as part of the benchmark. Compare equivalent inputs and outputs, and label latency, memory, and failure rates with the workload they came from.

What's next

The report screens still build whole workbooks in memory. A one-year report takes 13–18 s and about 0.9 GB. They're next, with the same playbook: narrow reads, streamed writing, and no re-scanning per sheet.