Exporting millions of rows without running out of memory

Problem: "How do I export 20 million rows every night without loading them all into memory?" — the question that kills a naive SELECT * + res.json() approach the first time it runs against production-sized data.

When to use DataEgress for this

• Your table is large enough that SELECT * FROM table either times out, OOMs the process, or takes long enough that you'd want it running in the background with visible progress, not blocking a request.
• You want the pagination logic (page size, cursor advancement, when to stop) handled for you, driven by a query you still write and control.

When NOT to use DataEgress for this

• Your dataset is a few thousand rows. This is architecture for a problem you don't have yet — a plain query + CSV response is faster to ship and easier to reason about.

Architecture

You write fetchPage({ tenantId, cursor, limit }) — a single page's worth of rows, using whatever cursor strategy fits your schema (an incrementing ID, a composite key, updatedAt, whatever). DataEgress calls it in a loop:

cursor = null
loop:
  { rows, nextCursor } = await fetchPage({ tenantId, cursor, limit: 5000 })
  write rows to the output file (CSV: streamed to disk; Parquet: encoded
    into a row group immediately)
  if nextCursor is null: done
  cursor = nextCursor

At no point does DataEgress hold the full dataset as JS objects in memory — only one page (default 5,000 rows) at a time. Verified at 1,000,000 rows: completes in ~3.4 seconds for CSV, ~5 seconds for Parquet, with the process's memory footprint bounded by page size, not total row count.

Full implementation

const events = defineDataset({
  id: "events",
  fetchPage: async ({ tenantId, cursor, limit }) => {
    // Cursor-based pagination on an indexed, monotonic column — the pattern
    // that scales. Avoid OFFSET-based pagination on large tables; it gets
    // slower as the offset grows.
    const rows = await db.query(
      `SELECT * FROM events WHERE tenant_id = $1 AND id > $2 ORDER BY id ASC LIMIT $3`,
      [tenantId, cursor ? Number(cursor) : 0, limit]
    );
    return {
      rows,
      nextCursor: rows.length < limit ? null : String(rows[rows.length - 1].id),
    };
  },
});

Common errors

• Using OFFSET instead of a cursor — works, but gets quadratically slower as the offset grows on large tables. Prefer a monotonic column (auto-increment id, or a composite (created_at, id) key) and WHERE id > $cursor.
• Returning nextCursor when there are no more rows — causes an infinite loop, since DataEgress keeps calling fetchPage until it gets null. Return null explicitly once rows.length < limit.
• A slow fetchPage on one page timing out the whole run — DataEgress doesn't impose its own per-page timeout in V1; a pathologically slow query will make the export slow, not fail outright, unless your own DB client times out first.

Working example

See examples/node for a minimal Node script version without a web framework.