Ishaan Pandey

Posted on Mar 21 • Originally published at ishaaan.hashnode.dev

Sending a Million Rows from the Backend: Streaming, Batching, Compression & Protocol Buffers

#backenddevelopment #node #performance #systemdesign

Sending a Million Rows from the Backend: Streaming, Batching, Compression & Protocol Buffers

Your database has a million rows. Your client needs them. What could go wrong?

Everything, actually. If you try to serialize a million rows into a single JSON response, you'll blow up your server's memory, timeout the request, make the client wait forever, and then crash the browser trying to parse a 500 MB JSON blob.

Sending large datasets is a problem that touches every layer: the database, the serialization format, the transport protocol, compression, and how you structure the data flow. Get any of these wrong and your system falls over.

This guide covers every major approach — from pagination basics to gRPC streaming, from backpressure management to Parquet exports. With real code, real benchmarks, and real-world examples.

Let's get into it.

The Problem: Why One Giant JSON Response Is a Terrible Idea

Here's the naive approach:

app.get('/api/users', async (req, res) => {
  const users = await db.query('SELECT * FROM users'); // 1M rows
  res.json(users); // 💀
});

Let's trace what happens:

┌──────────────────────────────────────────────────────┐
│              The Disaster Timeline                     │
├──────────────────────────────────────────────────────┤
│                                                       │
│  1. Database query executes                           │
│     └─ All 1M rows loaded into server memory          │
│     └─ ~500 MB - 2 GB of RAM consumed                 │
│                                                       │
│  2. JSON.stringify() runs                             │
│     └─ Blocks the event loop for 5-30 seconds         │
│     └─ Allocates another ~500 MB for the string       │
│                                                       │
│  3. Response sent over the network                    │
│     └─ Client waits for entire payload                │
│     └─ 500 MB over the wire (even compressed: ~50 MB) │
│     └─ Timeout risk: most proxies cut at 30-60s       │
│                                                       │
│  4. Client receives and parses                        │
│     └─ JSON.parse() on 500 MB string                  │
│     └─ Browser allocates ~1-2 GB for parsed objects   │
│     └─ Tab crashes or becomes unresponsive            │
│                                                       │
│  Peak server memory: ~1-2 GB PER REQUEST              │
│  10 concurrent requests = 10-20 GB = server OOM       │
└──────────────────────────────────────────────────────┘

The Numbers

Metric	1K Rows	100K Rows	1M Rows
JSON size (uncompressed)	~500 KB	~50 MB	~500 MB
JSON size (gzipped)	~50 KB	~5 MB	~50 MB
Serialization time	~5 ms	~500 ms	~5,000 ms
Parse time (client)	~10 ms	~1,000 ms	~10,000 ms
Server memory	~2 MB	~200 MB	~2 GB
Network time (100 Mbps)	~4 ms	~400 ms	~4,000 ms

At 1M rows, every step is a bottleneck. You need a different strategy.

Technique 1: Pagination

The simplest solution: don't send everything at once.

Offset Pagination

-- Page 1
SELECT * FROM users ORDER BY id LIMIT 50 OFFSET 0;

-- Page 2
SELECT * FROM users ORDER BY id LIMIT 50 OFFSET 50;

-- Page 200
SELECT * FROM users ORDER BY id LIMIT 50 OFFSET 9950;

// Express endpoint
app.get('/api/users', async (req, res) => {
  const page = parseInt(req.query.page) || 1;
  const limit = Math.min(parseInt(req.query.limit) || 50, 200);
  const offset = (page - 1) * limit;

  const [rows] = await db.query(
    'SELECT * FROM users ORDER BY id LIMIT ? OFFSET ?',
    [limit, offset]
  );

  const [countResult] = await db.query(
    'SELECT COUNT(*) as total FROM users'
  );

  res.json({
    data: rows,
    pagination: {
      page,
      limit,
      total: countResult[0].total,
      totalPages: Math.ceil(countResult[0].total / limit),
    },
  });
});

The problem with offset pagination: the database still scans and discards rows. OFFSET 999950 means the DB reads 999,950 rows and throws them away. At scale, this gets slow.

Cursor-Based (Keyset) Pagination

Far more efficient — uses an indexed column as a cursor:

-- First page
SELECT * FROM users ORDER BY id LIMIT 50;

-- Next page (cursor = last row's id from previous page)
SELECT * FROM users WHERE id > 12345 ORDER BY id LIMIT 50;

app.get('/api/users', async (req, res) => {
  const limit = Math.min(parseInt(req.query.limit) || 50, 200);
  const cursor = req.query.cursor; // last seen ID

  let query, params;
  if (cursor) {
    query = 'SELECT * FROM users WHERE id > ? ORDER BY id LIMIT ?';
    params = [cursor, limit + 1]; // fetch one extra to check hasMore
  } else {
    query = 'SELECT * FROM users ORDER BY id LIMIT ?';
    params = [limit + 1];
  }

  const [rows] = await db.query(query, params);
  const hasMore = rows.length > limit;
  const data = hasMore ? rows.slice(0, -1) : rows;

  res.json({
    data,
    pagination: {
      nextCursor: hasMore ? data[data.length - 1].id : null,
      hasMore,
    },
  });
});

Pagination Comparison

Aspect	Offset	Cursor	Keyset
Performance at depth	O(offset + limit)	O(limit)	O(limit)
Jump to page N	Yes	No	No
Consistent with inserts/deletes	No (rows shift)	Yes	Yes
Requires unique sort column	No	Yes	Yes
Implementation complexity	Simple	Moderate	Moderate
Works with complex sorts	Yes	Tricky	Tricky

Pagination is the right answer for most use cases. But when the client genuinely needs all the data (exports, analytics, bulk operations), you need streaming.

Technique 2: Streaming Responses

Instead of building the entire response in memory, stream rows as they become available.

NDJSON (Newline-Delimited JSON)

Each line is a valid JSON object. The client processes rows incrementally:

{"id": 1, "name": "Alice", "email": "alice@example.com"}
{"id": 2, "name": "Bob", "email": "bob@example.com"}
{"id": 3, "name": "Charlie", "email": "charlie@example.com"}

// Express + streaming from database
app.get('/api/users/stream', async (req, res) => {
  res.setHeader('Content-Type', 'application/x-ndjson');
  res.setHeader('Transfer-Encoding', 'chunked');

  const stream = db.query('SELECT * FROM users').stream();

  stream.on('data', (row) => {
    res.write(JSON.stringify(row) + '\n');
  });

  stream.on('end', () => {
    res.end();
  });

  stream.on('error', (err) => {
    console.error('Stream error:', err);
    if (!res.headersSent) {
      res.status(500).json({ error: 'Stream failed' });
    } else {
      res.end();
    }
  });
});

Client-side consumption:

async function consumeNDJSON(url, onRow) {
  const response = await fetch(url);
  const reader = response.body.getReader();
  const decoder = new TextDecoder();
  let buffer = '';

  while (true) {
    const { done, value } = await reader.read();
    if (done) break;

    buffer += decoder.decode(value, { stream: true });
    const lines = buffer.split('\n');
    buffer = lines.pop(); // keep incomplete line in buffer

    for (const line of lines) {
      if (line.trim()) {
        onRow(JSON.parse(line));
      }
    }
  }

  // Process any remaining data
  if (buffer.trim()) {
    onRow(JSON.parse(buffer));
  }
}

// Usage
const rows = [];
await consumeNDJSON('/api/users/stream', (row) => {
  rows.push(row);
  if (rows.length % 1000 === 0) {
    updateProgress(rows.length);
  }
});

Node.js Streams with Transform

A more idiomatic Node.js approach using stream pipelines:

import { Transform, pipeline } from 'node:stream';
import { promisify } from 'node:util';

const pipelineAsync = promisify(pipeline);

app.get('/api/users/stream', async (req, res) => {
  res.setHeader('Content-Type', 'application/x-ndjson');

  const dbStream = db.query('SELECT * FROM users').stream();

  const toNDJSON = new Transform({
    objectMode: true,
    transform(row, encoding, callback) {
      callback(null, JSON.stringify(row) + '\n');
    },
  });

  try {
    await pipelineAsync(dbStream, toNDJSON, res);
  } catch (err) {
    console.error('Pipeline error:', err);
  }
});

Server-Sent Events (SSE)

Good for pushing data to the client with automatic reconnection:

app.get('/api/users/sse', async (req, res) => {
  res.setHeader('Content-Type', 'text/event-stream');
  res.setHeader('Cache-Control', 'no-cache');
  res.setHeader('Connection', 'keep-alive');

  const stream = db.query('SELECT * FROM users').stream();
  let count = 0;

  stream.on('data', (row) => {
    count++;
    res.write(`data: ${JSON.stringify(row)}\n\n`);

    // Send progress events every 1000 rows
    if (count % 1000 === 0) {
      res.write(`event: progress\ndata: ${JSON.stringify({ count })}\n\n`);
    }
  });

  stream.on('end', () => {
    res.write(`event: complete\ndata: ${JSON.stringify({ total: count })}\n\n`);
    res.end();
  });

  // Handle client disconnect
  req.on('close', () => {
    stream.destroy();
  });
});

// Client
const source = new EventSource('/api/users/sse');
const rows = [];

source.onmessage = (e) => {
  rows.push(JSON.parse(e.data));
};

source.addEventListener('progress', (e) => {
  console.log(`Received ${JSON.parse(e.data).count} rows`);
});

source.addEventListener('complete', (e) => {
  console.log(`Done! Total: ${JSON.parse(e.data).total}`);
  source.close();
});

Fastify Streaming

Fastify has great streaming support out of the box:

import Fastify from 'fastify';
import { Readable } from 'node:stream';

const app = Fastify();

app.get('/api/users/stream', async (request, reply) => {
  const dbStream = db.query('SELECT * FROM users').stream();

  const ndjsonStream = new Readable({
    read() {},
  });

  dbStream.on('data', (row) => {
    ndjsonStream.push(JSON.stringify(row) + '\n');
  });

  dbStream.on('end', () => {
    ndjsonStream.push(null);
  });

  reply.type('application/x-ndjson');
  return reply.send(ndjsonStream);
});

Streaming Memory Comparison

Traditional (buffer everything):

  Memory ─┐
     2 GB │          ┌──────────┐
     1 GB │     ┌────┤  Stringify│──┐
  500 MB  │ ┌───┤ DB  │          │  ├──── Send
          │ │   │Query│          │  │
     0    └─┴───┴─────┴──────────┴──┴──→ Time

Streaming:

  Memory ─┐
    20 MB │  ┌─┐ ┌─┐ ┌─┐ ┌─┐ ┌─┐ ┌─┐
    10 MB │──┤ ├─┤ ├─┤ ├─┤ ├─┤ ├─┤ ├──
          │  └─┘ └─┘ └─┘ └─┘ └─┘ └─┘
     0    └────────────────────────────→ Time
          Each chunk: ~few KB
          Total memory: ~10-20 MB constant

The key insight: with streaming, memory usage is constant regardless of dataset size. You process rows one (or a few) at a time.

Technique 3: HTTP Streaming with Transfer-Encoding: chunked

Under the hood, HTTP streaming uses chunked transfer encoding. This is what makes NDJSON and SSE work.

Normal HTTP Response:
  Content-Length: 524288000  ← Server must know total size upfront
  [... 500 MB of data ...]

Chunked Transfer:
  Transfer-Encoding: chunked

  1a4\r\n                    ← Chunk size in hex (420 bytes)
  {"id":1,"name":"Alice"...}\n
  {"id":2,"name":"Bob"...}\n
  \r\n
  1b2\r\n                    ← Next chunk
  {"id":3,"name":"Charlie"...}\n
  ...
  \r\n
  0\r\n                      ← Zero-length chunk = end
  \r\n

In Express, this happens automatically when you use res.write() instead of res.json():

app.get('/api/data', (req, res) => {
  // res.json(bigData)   — buffers everything, sends Content-Length
  // res.write(chunk)     — streams, uses Transfer-Encoding: chunked

  res.setHeader('Content-Type', 'application/json');
  res.write('[');  // Start JSON array

  let first = true;
  const stream = db.query('SELECT * FROM items').stream();

  stream.on('data', (row) => {
    if (!first) res.write(',');
    first = false;
    res.write(JSON.stringify(row));
  });

  stream.on('end', () => {
    res.write(']');
    res.end();
  });
});

Note: streaming a JSON array like this means the client can't parse until the entire array is received (no incremental JSON.parse). That's why NDJSON is usually better for streaming.

Technique 4: gRPC Server Streaming

gRPC with Protocol Buffers is purpose-built for streaming large datasets efficiently.

Define the Protocol

// users.proto
syntax = "proto3";

package users;

service UserService {
  // Unary — traditional request/response
  rpc GetUser (GetUserRequest) returns (User);

  // Server streaming — server sends many messages
  rpc ListUsers (ListUsersRequest) returns (stream User);

  // Bidirectional streaming
  rpc SyncUsers (stream SyncRequest) returns (stream SyncResponse);
}

message ListUsersRequest {
  int32 batch_size = 1;
  string filter = 2;
}

message User {
  int64 id = 1;
  string name = 2;
  string email = 3;
  double balance = 4;
  int64 created_at = 5;
}

Server Implementation (Node.js)

import grpc from '@grpc/grpc-js';
import protoLoader from '@grpc/proto-loader';

const packageDef = protoLoader.loadSync('users.proto');
const proto = grpc.loadPackageDefinition(packageDef);

const server = new grpc.Server();

server.addService(proto.users.UserService.service, {
  listUsers: async (call) => {
    const batchSize = call.request.batch_size || 1000;
    const dbStream = db.query('SELECT * FROM users').stream();
    let batch = [];

    dbStream.on('data', (row) => {
      call.write({
        id: row.id,
        name: row.name,
        email: row.email,
        balance: row.balance,
        created_at: row.created_at.getTime(),
      });
    });

    dbStream.on('end', () => {
      call.end();
    });

    dbStream.on('error', (err) => {
      call.destroy(err);
    });

    // Handle client cancellation
    call.on('cancelled', () => {
      dbStream.destroy();
    });
  },
});

server.bindAsync(
  '0.0.0.0:50051',
  grpc.ServerCredentials.createInsecure(),
  () => server.start()
);

Client Consumption

const client = new proto.users.UserService(
  'localhost:50051',
  grpc.credentials.createInsecure()
);

const stream = client.listUsers({ batch_size: 1000 });
const rows = [];

stream.on('data', (user) => {
  rows.push(user);
});

stream.on('end', () => {
  console.log(`Received ${rows.length} users`);
});

stream.on('error', (err) => {
  console.error('Stream error:', err);
});

Why gRPC Is Faster

JSON over HTTP:
  {"id":12345,"name":"Alice Johnson","email":"alice@example.com","balance":1234.56}
  = 82 bytes (human-readable, lots of repeated keys)

Protocol Buffers:
  08 B9 60 12 0D 41 6C 69 63 65 20 4A 6F 68 6E 73 6F 6E ...
  = ~45 bytes (binary, no keys, varint encoding)

Savings: ~45% smaller per row
At 1M rows: ~40 MB saved on the wire

Technique 5: WebSockets for Real-Time Large Data

When you need bidirectional communication or real-time updates on large datasets:

import { WebSocketServer } from 'ws';

const wss = new WebSocketServer({ port: 8080 });

wss.on('connection', (ws) => {
  ws.on('message', async (message) => {
    const request = JSON.parse(message);

    if (request.type === 'fetch-all') {
      const stream = db.query('SELECT * FROM transactions').stream();
      let batch = [];
      let batchCount = 0;

      stream.on('data', (row) => {
        batch.push(row);

        // Send in batches of 500 to reduce message overhead
        if (batch.length >= 500) {
          ws.send(JSON.stringify({
            type: 'batch',
            data: batch,
            batchNumber: ++batchCount,
          }));
          batch = [];
        }
      });

      stream.on('end', () => {
        // Send remaining rows
        if (batch.length > 0) {
          ws.send(JSON.stringify({
            type: 'batch',
            data: batch,
            batchNumber: ++batchCount,
          }));
        }
        ws.send(JSON.stringify({
          type: 'complete',
          totalBatches: batchCount,
        }));
      });
    }
  });
});

When to Use WebSockets vs HTTP Streaming

Aspect	HTTP Streaming	WebSocket
Direction	Server → Client	Bidirectional
Connection overhead	New connection per request	Persistent connection
Resumability	Must restart	Can implement resume
Proxy compatibility	Excellent	Sometimes problematic
Browser support	Universal	Universal
Backpressure	HTTP flow control	Manual implementation
Best for	One-time data exports	Real-time dashboards, live feeds

Technique 6: Batching Strategies

Optimal Chunk Size

Too small = overhead per batch dominates. Too large = high memory usage. The sweet spot depends on row size:

┌────────────────────────────────────────────────────┐
│          Throughput vs Batch Size                    │
│                                                     │
│  Throughput                                         │
│  (rows/s)                                           │
│     │         ┌──────────────┐                      │
│  80K│         │              │                      │
│     │      ┌──┘              └──────────┐           │
│  60K│   ┌──┘                            └──┐        │
│     │┌──┘                                  └──┐     │
│  40K││                                        └──   │
│     ││                                              │
│  20K││                                              │
│     │└─────┬──────┬─────┬──────┬──────┬─────┬───    │
│     0     100    500   1000   5000  10000  50000    │
│                  Batch size (rows)                   │
│                                                     │
│  Sweet spot: 500–5,000 rows per batch               │
│  (depends on row size and network conditions)       │
└────────────────────────────────────────────────────┘

// Adaptive batching based on row size
function calculateBatchSize(avgRowSizeBytes) {
  const TARGET_CHUNK_KB = 256; // target ~256 KB per batch
  const targetBytes = TARGET_CHUNK_KB * 1024;
  const batchSize = Math.floor(targetBytes / avgRowSizeBytes);
  return Math.max(100, Math.min(batchSize, 10000)); // clamp 100–10,000
}

Backpressure-Aware Batching

If you send faster than the client can consume, you'll buffer everything in memory anyway. This is backpressure — and you need to handle it:

app.get('/api/export', async (req, res) => {
  const dbStream = db.query('SELECT * FROM orders').stream();

  dbStream.on('data', (row) => {
    const canContinue = res.write(JSON.stringify(row) + '\n');

    if (!canContinue) {
      // The write buffer is full — pause the database stream
      dbStream.pause();

      // Resume when the buffer drains
      res.once('drain', () => {
        dbStream.resume();
      });
    }
  });

  dbStream.on('end', () => res.end());
});

This is critical. Without backpressure handling, a slow client causes the server to buffer everything in memory — defeating the purpose of streaming.

Technique 7: Compression

Compressing the response can reduce bandwidth by 70-95% for JSON data.

Compression Algorithms Compared

Algorithm	Compression Ratio	Compress Speed	Decompress Speed	Best For
gzip	Good (70-80%)	Moderate	Fast	General purpose, widest support
Brotli	Better (75-85%)	Slow	Fast	Static assets, pre-compressed
zstd	Better (75-85%)	Fast	Very Fast	Real-time compression, logs
lz4	Moderate (60-70%)	Very Fast	Very Fast	Speed-critical, large volumes

Benchmarks with 1M Rows of User Data

Original JSON:      487 MB (100%)
┌───────────────────────────────────────────────────┐
│ gzip -6:           52 MB (10.7%)  compress: 4.2s  │
│ gzip -9:           48 MB ( 9.9%)  compress: 8.1s  │
│ brotli -6:         43 MB ( 8.8%)  compress: 6.5s  │
│ brotli -11:        38 MB ( 7.8%)  compress: 42s   │
│ zstd -3:           49 MB (10.1%)  compress: 1.8s  │
│ zstd -9:           42 MB ( 8.6%)  compress: 5.2s  │
│ NDJSON + gzip -6:  54 MB (11.1%)  compress: 4.0s  │
│ Protobuf (raw):   265 MB (54.4%)  serialize: 1.2s │
│ Protobuf + gzip:   31 MB ( 6.4%)  total: 3.1s     │
└───────────────────────────────────────────────────┘

Protobuf + gzip is the most compact option. Plain gzip gives the best effort-to-reward ratio.

Enabling Compression in Express

import compression from 'compression';

// Global compression middleware
app.use(compression({
  level: 6,                    // gzip level (1-9)
  threshold: 1024,             // only compress responses > 1 KB
  filter: (req, res) => {
    if (req.headers['x-no-compression']) return false;
    return compression.filter(req, res);
  },
}));

// Or per-route with streaming
import { createGzip } from 'node:zlib';

app.get('/api/export', async (req, res) => {
  res.setHeader('Content-Type', 'application/x-ndjson');
  res.setHeader('Content-Encoding', 'gzip');

  const gzip = createGzip({ level: 6 });
  const dbStream = db.query('SELECT * FROM users').stream();

  const transform = new Transform({
    objectMode: true,
    transform(row, enc, cb) {
      cb(null, JSON.stringify(row) + '\n');
    },
  });

  pipeline(dbStream, transform, gzip, res, (err) => {
    if (err) console.error('Pipeline failed:', err);
  });
});

Streaming Compression Considerations

When compressing a stream, the compressor needs enough data to build its dictionary. With very small chunks, compression ratio suffers:

// Bad: compress each row individually (poor ratio)
stream.on('data', (row) => {
  const compressed = gzipSync(JSON.stringify(row));
  res.write(compressed);
});

// Good: let the gzip stream accumulate data internally
const gzip = createGzip({ level: 6, flush: constants.Z_SYNC_FLUSH });
pipeline(dataStream, gzip, res, () => {});
// Z_SYNC_FLUSH ensures data is flushed periodically so the client
// can start processing without waiting for the entire stream

Technique 8: Serialization Format Comparison

JSON isn't the only option. Here's how formats compare:

Size Comparison (1M Rows)

Format	Size	Parse Time (client)	Schema Required
JSON	487 MB	~10s	No
NDJSON	487 MB	~10s (streamable)	No
Protocol Buffers	265 MB	~2s	Yes (.proto)
MessagePack	310 MB	~4s	No
CBOR	320 MB	~4s	No
Avro	250 MB	~2s	Yes
FlatBuffers	280 MB	~0s (zero-copy)	Yes

MessagePack Example

Binary JSON — smaller, faster, drop-in replacement:

import msgpack from '@msgpack/msgpack';

// Server
app.get('/api/users/msgpack', async (req, res) => {
  const users = await db.query('SELECT * FROM users LIMIT 10000');
  const encoded = msgpack.encode(users);

  res.setHeader('Content-Type', 'application/x-msgpack');
  res.send(Buffer.from(encoded));
});

// Client
const response = await fetch('/api/users/msgpack');
const buffer = await response.arrayBuffer();
const users = msgpack.decode(new Uint8Array(buffer));

When to Use What

┌─────────────────────────────────────────────────────┐
│                Format Decision Tree                  │
│                                                      │
│  Do you need browser-native parsing?                 │
│  ├─ Yes → JSON or NDJSON                             │
│  └─ No                                               │
│     │                                                │
│     ├─ Do you control both client and server?         │
│     │  ├─ Yes → Protocol Buffers (best size + speed)  │
│     │  └─ No → MessagePack (schemaless, still small)  │
│     │                                                │
│     └─ Do you need zero-copy access?                  │
│        ├─ Yes → FlatBuffers                           │
│        └─ No → Protocol Buffers                       │
└─────────────────────────────────────────────────────┘

Technique 9: Database-Level Optimization

The fastest byte is the one you never send. Optimize at the database level first.

Streaming from PostgreSQL

import pg from 'pg';
import QueryStream from 'pg-query-stream';

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

app.get('/api/export', async (req, res) => {
  const client = await pool.connect();

  try {
    const query = new QueryStream(
      'SELECT id, name, email FROM users ORDER BY id',
      [],
      { batchSize: 1000 } // fetch 1000 rows at a time from PG
    );

    const stream = client.query(query);

    res.setHeader('Content-Type', 'application/x-ndjson');

    stream.on('data', (row) => {
      res.write(JSON.stringify(row) + '\n');
    });

    stream.on('end', () => {
      client.release();
      res.end();
    });

    stream.on('error', (err) => {
      client.release();
      res.status(500).end();
    });
  } catch (err) {
    client.release();
    throw err;
  }
});

PostgreSQL COPY Command

For bulk exports, COPY is orders of magnitude faster than SELECT:

import { pipeline } from 'node:stream/promises';
import copyFrom from 'pg-copy-streams';

app.get('/api/export/csv', async (req, res) => {
  const client = await pool.connect();

  try {
    res.setHeader('Content-Type', 'text/csv');
    res.setHeader('Content-Disposition', 'attachment; filename="export.csv"');

    const copyStream = client.query(
      copyFrom.to("COPY users TO STDOUT WITH (FORMAT csv, HEADER true)")
    );

    await pipeline(copyStream, res);
  } finally {
    client.release();
  }
});

COPY bypasses the query executor and reads directly from the storage layer. Benchmarks:

1M rows export:
  SELECT + JSON serialize:  ~12 seconds
  SELECT + stream:          ~8 seconds
  COPY to CSV:              ~2 seconds
  COPY to binary:           ~1.2 seconds

MySQL Streaming

import mysql from 'mysql2';

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'mydb',
});

app.get('/api/export', (req, res) => {
  res.setHeader('Content-Type', 'application/x-ndjson');

  const stream = pool.query('SELECT * FROM users')
    .stream({ highWaterMark: 1000 });

  stream.on('data', (row) => {
    const ok = res.write(JSON.stringify(row) + '\n');
    if (!ok) {
      stream.pause();
      res.once('drain', () => stream.resume());
    }
  });

  stream.on('end', () => res.end());
  stream.on('error', (err) => {
    console.error(err);
    res.end();
  });
});

Connection Pool Sizing

Long-running streaming queries tie up connections. Size your pool carefully:

const pool = new pg.Pool({
  max: 20,                    // max connections
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
});

// For export endpoints, consider a separate pool with fewer connections
const exportPool = new pg.Pool({
  max: 5,  // limit concurrent exports
  idleTimeoutMillis: 60000,
  statement_timeout: 300000,  // 5 min timeout for long queries
});

Technique 10: Caching Large Responses

CDN Caching

For data that doesn't change frequently, cache at the edge:

app.get('/api/reports/daily', async (req, res) => {
  res.setHeader('Cache-Control', 'public, max-age=3600, s-maxage=86400');
  res.setHeader('CDN-Cache-Control', 'max-age=86400'); // Cloudflare/Vercel
  res.setHeader('Vary', 'Accept-Encoding');

  const data = await generateDailyReport();
  res.json(data);
});

Redis Caching for Computed Results

import Redis from 'ioredis';
const redis = new Redis();

app.get('/api/analytics/summary', async (req, res) => {
  const cacheKey = `analytics:summary:${req.query.date}`;

  // Check cache first
  const cached = await redis.get(cacheKey);
  if (cached) {
    res.setHeader('X-Cache', 'HIT');
    res.json(JSON.parse(cached));
    return;
  }

  // Compute the expensive result
  const result = await computeAnalytics(req.query.date);

  // Cache for 1 hour
  await redis.set(cacheKey, JSON.stringify(result), 'EX', 3600);

  res.setHeader('X-Cache', 'MISS');
  res.json(result);
});

ETags for Conditional Requests

When the client already has data and just needs to check for updates:

import crypto from 'node:crypto';

app.get('/api/dataset/:id', async (req, res) => {
  const data = await getDataset(req.params.id);
  const etag = crypto
    .createHash('md5')
    .update(JSON.stringify(data.lastModified))
    .digest('hex');

  res.setHeader('ETag', `"${etag}"`);

  // If client has the same version, send 304
  if (req.headers['if-none-match'] === `"${etag}"`) {
    res.status(304).end();
    return;
  }

  res.json(data);
});

Technique 11: File-Based Alternatives

Sometimes the best API response is a URL to a file.

Pre-Generated CSV/Parquet Export

import { S3Client, PutObjectCommand, GetObjectCommand } from '@aws-sdk/client-s3';
import { getSignedUrl } from '@aws-sdk/s3-request-presigner';

const s3 = new S3Client({ region: 'us-east-1' });

app.post('/api/export/request', async (req, res) => {
  const exportId = crypto.randomUUID();

  // Queue the export job (could use Bull, SQS, etc.)
  await exportQueue.add('generate-export', {
    exportId,
    query: req.body.query,
    format: req.body.format || 'csv',
  });

  res.json({
    exportId,
    status: 'processing',
    statusUrl: `/api/export/${exportId}/status`,
  });
});

// Background job handler
async function handleExport(job) {
  const { exportId, query, format } = job.data;

  // Stream from DB to S3
  const dbStream = db.query(query).stream();
  const chunks = [];

  for await (const row of dbStream) {
    chunks.push(formatRow(row, format)); // CSV line, etc.
  }

  await s3.send(new PutObjectCommand({
    Bucket: 'my-exports',
    Key: `exports/${exportId}.${format}`,
    Body: chunks.join(''),
    ContentType: format === 'csv' ? 'text/csv' : 'application/octet-stream',
  }));

  await db.query(
    'UPDATE exports SET status = ?, completed_at = NOW() WHERE id = ?',
    ['completed', exportId]
  );
}

// Status + download endpoint
app.get('/api/export/:id/status', async (req, res) => {
  const [export_] = await db.query(
    'SELECT * FROM exports WHERE id = ?',
    [req.params.id]
  );

  if (export_.status === 'completed') {
    const url = await getSignedUrl(s3, new GetObjectCommand({
      Bucket: 'my-exports',
      Key: `exports/${export_.id}.csv`,
    }), { expiresIn: 3600 });

    res.json({
      status: 'completed',
      downloadUrl: url,
      expiresIn: 3600,
    });
  } else {
    res.json({ status: export_.status });
  }
});

When to Use File Exports

Data size → Response strategy:

  < 1 MB        Direct JSON response
  1-50 MB       Streaming NDJSON / compressed JSON
  50-500 MB     Streaming + compression, or file export
  > 500 MB      File export (S3 presigned URL)
  > 5 GB        Parquet/columnar format + file export

Parquet for Analytical Data

Parquet is a columnar format — much more efficient than CSV for analytical queries:

import parquet from 'parquetjs';

async function exportToParquet(query, filePath) {
  const schema = new parquet.ParquetSchema({
    id: { type: 'INT64' },
    name: { type: 'UTF8' },
    email: { type: 'UTF8' },
    balance: { type: 'DOUBLE' },
    created_at: { type: 'TIMESTAMP_MILLIS' },
  });

  const writer = await parquet.ParquetWriter.openFile(schema, filePath);
  const stream = db.query('SELECT * FROM users').stream();

  for await (const row of stream) {
    await writer.appendRow(row);
  }

  await writer.close();
}

// File size comparison for 1M rows:
// CSV:     ~450 MB
// JSON:    ~487 MB
// Parquet: ~85 MB (columnar compression is very effective)

Technique 12: Backpressure Deep Dive

Backpressure is the most important concept in streaming. If you don't handle it, streaming gives you zero benefit.

What Is Backpressure?

Without backpressure:

  Database        Server Buffer        Network        Client
  [100K rows/s]→  [████████████]  →   [slow 10K/s]→  [processing]
                   ↑ growing!
                   Eventually: OUT OF MEMORY

With backpressure:

  Database        Server Buffer        Network        Client
  [paused]    →   [██         ]  →    [10K rows/s]→  [processing]
  [10K rows/s]→   [██         ]  →    [10K rows/s]→  [processing]
                   ↑ stays small!
                   Memory: constant

Implementing Backpressure in Node.js

Node.js streams have backpressure built in — but only if you use them correctly:

import { pipeline, Transform } from 'node:stream';
import { promisify } from 'node:util';

const pipelineAsync = promisify(pipeline);

app.get('/api/stream', async (req, res) => {
  const dbStream = db.query('SELECT * FROM big_table').stream();

  const serialize = new Transform({
    objectMode: true,
    highWaterMark: 100,  // buffer at most 100 objects

    transform(row, encoding, callback) {
      // callback() signals "I'm ready for more"
      // If downstream is full, Node pauses upstream automatically
      callback(null, JSON.stringify(row) + '\n');
    },
  });

  // pipeline() handles backpressure automatically between all streams
  await pipelineAsync(dbStream, serialize, res);
});

Manual Backpressure (When Not Using pipeline)

function streamWithBackpressure(readable, writable) {
  return new Promise((resolve, reject) => {
    readable.on('data', (chunk) => {
      const canWrite = writable.write(chunk);

      if (!canWrite) {
        // Writable buffer is full — stop reading
        readable.pause();
        writable.once('drain', () => {
          // Buffer drained — resume reading
          readable.resume();
        });
      }
    });

    readable.on('end', () => {
      writable.end();
      resolve();
    });

    readable.on('error', reject);
    writable.on('error', reject);
  });
}

Real-World Examples

How Stripe Exports Transactions

Stripe's data export system is a great example of handling massive datasets:

Request an export via API → returns an export ID
Async processing — Stripe generates the file in the background
Polling or webhook — client checks status or receives a webhook
Download via signed URL — the file is hosted on cloud storage

Client                    Stripe API                Background Worker
  │                          │                            │
  ├─ POST /exports ─────────►│                            │
  │◄── 202 { id: "exp_1" }──┤                            │
  │                          ├─ Queue export job ────────►│
  │                          │                            ├─ Query DB
  │                          │                            ├─ Stream to S3
  ├─ GET /exports/exp_1 ────►│                            ├─ Compress
  │◄── { status: "pending" }─┤                            │
  │                          │                            │
  │  ... wait ...            │                            │
  │                          │◄── Job complete ───────────┤
  ├─ GET /exports/exp_1 ────►│                            │
  │◄── { status: "complete", │                            │
  │     download_url: "..." }│                            │
  │                          │                            │
  ├─ GET download_url ──────►│ (S3 presigned URL)         │
  │◄── [CSV file] ──────────┤                            │

This pattern is ideal for large exports because:

No request timeout issues (async processing)
Server memory stays constant (stream to storage)
Client gets a fast download from CDN/S3
Exports can be cached and re-downloaded

How Analytics Dashboards Stream Data

Tools like Metabase and Grafana use a hybrid approach:

Initial load: Server-side aggregation (don't send raw rows, send aggregated results)
Drill-down: Stream detail rows on demand with pagination
Real-time updates: WebSocket for live metrics, SSE for feed-style data
Export: Background file generation for full datasets

// Aggregation endpoint — fast, small response
app.get('/api/dashboard/summary', async (req, res) => {
  const summary = await db.query(`
    SELECT
      DATE_TRUNC('day', created_at) as day,
      COUNT(*) as count,
      SUM(amount) as total,
      AVG(amount) as average
    FROM transactions
    WHERE created_at > NOW() - INTERVAL '30 days'
    GROUP BY day
    ORDER BY day
  `);
  res.json(summary); // ~30 rows, < 5 KB
});

// Detail endpoint — streamed, on demand
app.get('/api/dashboard/details/:day', async (req, res) => {
  res.setHeader('Content-Type', 'application/x-ndjson');

  const stream = db.query(
    'SELECT * FROM transactions WHERE DATE(created_at) = $1',
    [req.params.day]
  ).stream();

  pipeline(
    stream,
    new Transform({
      objectMode: true,
      transform(row, enc, cb) { cb(null, JSON.stringify(row) + '\n'); },
    }),
    res,
    () => {},
  );
});

Decision Framework

How much data are you sending?
│
├─ < 1,000 rows (~500 KB)
│   └─ Just send JSON. Don't overthink it.
│       Add gzip compression and call it a day.
│
├─ 1,000 – 50,000 rows (~500 KB – 25 MB)
│   └─ Paginate (cursor-based) with reasonable page sizes.
│       Add compression. Consider pagination + caching.
│
├─ 50,000 – 500,000 rows (~25 MB – 250 MB)
│   └─ Stream with NDJSON + gzip compression.
│       Use database-level streaming (QueryStream).
│       Handle backpressure properly.
│       Consider server-side aggregation first.
│
├─ 500,000 – 5,000,000 rows (~250 MB – 2.5 GB)
│   └─ File export (async job → S3 presigned URL).
│       Or gRPC streaming with Protocol Buffers.
│       Parquet format for analytical data.
│       Never try to send this as a single HTTP response.
│
└─ > 5,000,000 rows
    └─ Always file export. Consider Parquet/columnar format.
        Use database COPY command for extraction.
        Split into multiple files if needed.
        Consider whether the client really needs all this data
        (hint: they probably need an aggregation).

Quick Reference

Technique	Complexity	Best For	Memory Usage
Pagination	Low	Most CRUD APIs	Constant (page size)
NDJSON streaming	Medium	Real-time processing	Constant (~10-20 MB)
SSE	Medium	Live feeds, progress	Constant
gRPC streaming	High	Microservices, binary data	Constant
File export (S3)	Medium-High	Large exports, async	Constant (stream to S3)
WebSocket batching	High	Real-time dashboards	Batch size dependent
COPY + CSV	Low	Bulk DB exports	Constant

Putting It All Together

Here's a production-grade export endpoint that combines multiple techniques:

import { pipeline } from 'node:stream/promises';
import { Transform } from 'node:stream';
import { createGzip } from 'node:zlib';
import QueryStream from 'pg-query-stream';

app.get('/api/v2/export', async (req, res) => {
  const format = req.query.format || 'ndjson'; // ndjson | csv
  const compress = req.accepts('gzip');

  // Set response headers
  const contentTypes = {
    ndjson: 'application/x-ndjson',
    csv: 'text/csv',
  };
  res.setHeader('Content-Type', contentTypes[format]);

  if (compress) {
    res.setHeader('Content-Encoding', 'gzip');
  }

  if (format === 'csv') {
    res.setHeader('Content-Disposition', 'attachment; filename="export.csv"');
  }

  // Database stream with cursor
  const client = await pool.connect();
  const query = new QueryStream(
    'SELECT id, name, email, amount, created_at FROM orders ORDER BY id',
    [],
    { batchSize: 2000 }
  );
  const dbStream = client.query(query);

  // Serialize to chosen format
  let isFirst = true;
  const serialize = new Transform({
    objectMode: true,
    transform(row, enc, cb) {
      if (format === 'csv') {
        if (isFirst) {
          this.push('id,name,email,amount,created_at\n');
          isFirst = false;
        }
        this.push(`${row.id},"${row.name}","${row.email}",${row.amount},${row.created_at}\n`);
      } else {
        this.push(JSON.stringify(row) + '\n');
      }
      cb();
    },
  });

  // Build pipeline
  const streams = [dbStream, serialize];
  if (compress) streams.push(createGzip({ level: 6 }));
  streams.push(res);

  try {
    await pipeline(...streams);
  } catch (err) {
    if (err.code !== 'ERR_STREAM_PREMATURE_CLOSE') {
      console.error('Export error:', err);
    }
  } finally {
    client.release();
  }
});

Wrapping Up

Sending a million rows efficiently comes down to a few principles:

Don't buffer everything in memory — stream from source to destination
Compress the wire format — gzip alone saves 90%+
Use the right serialization — JSON is fine for most cases, Protobuf for performance-critical paths
Handle backpressure — or your streaming is just buffering with extra steps
Consider whether you need to send all the data — aggregation, pagination, and server-side filtering solve most problems before they start
Use file exports for truly large datasets — async job + presigned URL is battle-tested

The most common mistake is trying to optimize the transport when you should be questioning whether you need to send that much data in the first place. A well-crafted aggregation query that returns 50 rows will always beat streaming a million.

But when you genuinely need to move a million rows — now you know how.

Let's Connect!

If you found this guide helpful, I'd love to connect with you! I regularly share deep dives on system design, backend engineering, and software architecture.

Connect with me on LinkedIn — let's grow together.

Drop a comment, share this with someone who needs this, and follow along for more guides like this!