Streaming Large PostgreSQL Queries with Cursors — Where Bun 1.2's SQL API Meets Backpressure

Last quarter, I hooked up an API to query an event audit log. It was a simple endpoint that pulled payloads from the events table — which accumulates tens of millions of rows a day — for a given time range and rendered them in an admin UI. Two days after deployment, the container RSS exceeded 4GB and ultimately triggered an OOM restart. At first I brushed it off as "the query being a bit slow," but after going back through the logs I realized the problem was in the driver layer.

The issue was straightforward. The pattern was: fetch hundreds of thousands of rows all at once into memory with SELECT id, actor_id, payload FROM events WHERE created_at > '2026-04-01', finish processing, then send the response. It wasn't that the DB was slow — the driver was buffering the entire result set and consuming the heap. With the payload column being JSONB averaging a few KB each, even a modest row count easily ballooned into gigabytes.

In this post, we'll use Bun 1.2's built-in SQL API as a starting point to walk through code that optimizes index scans with cursor-based pagination and keeps memory stable with backpressure control. If you've been using pg or postgres.js on Node.js, everything here applies directly.

Why This Combination, Why Now

Bun 1.2 Built-in SQL Client

One of the notable changes when Bun 1.2 was released in January 2025 was a PostgreSQL client you can use without any external packages. It's a native driver written in Zig that speaks the binary wire protocol directly, and comes with automatic prepared statements, query pipelining, and connection pooling out of the box.

import { SQL } from "bun";
 
const sql = new SQL("postgres://user:pass@localhost/db");
 
const rows = await sql`SELECT id, name FROM users WHERE active = ${true}`;

Tagged template literals automatically prevent SQL injection for parameter values. However, when dynamically composing query structure — such as column names or table names like sql`SELECT ${userColumnName} FROM ...` — the vulnerability still exists, so separate whitelist validation is required. Later versions are said to expand this into a single interface covering MySQL, MariaDB, and SQLite as well, but for the exact version and supported scope, it's recommended to check the Bun official SQL documentation.

So How Much Faster Is Bun Than Node.js?

Honestly, it's worth answering this question before moving on. The short answer is: in sections where the DB is the bottleneck, the effect of switching runtimes is limited. Most of the time is spent on network round-trips and DB execution, so the large gaps seen in HTTP server benchmarks don't reproduce directly. Results vary significantly across benchmarks depending on workload and hardware, making it hard to cite a single reliable number.

So the core of this post isn't a Bun endorsement — it's the streaming + cursor pattern that applies regardless of which runtime you use. The argument is: change your query strategy before changing your runtime.

The Problem with Offset Pagination

To understand why LIMIT 100 OFFSET 10000 is slow, just look at what PostgreSQL actually does. The offset approach scans the first 10,100 rows and discards the first 10,000. The further back you go in pages, the more it discards.

Sequin's post comparing keyset cursors vs offsets shows that as the table grows larger and pages go deeper, the offset approach sees scan volume grow linearly, whereas the cursor approach converges to constant time by locating the start point with a single index seek. The specific numbers vary greatly by schema, indexes, and hardware, so refer to the original benchmark conditions directly — here, just keep in mind the tendency for the gap to grow exponentially as the table size increases.

The Core Query for Cursor-Based Pagination

SELECT id, created_at, payload
FROM events
WHERE (created_at, id) > ($1, $2)
ORDER BY created_at ASC, id ASC
LIMIT 500;

The (created_at, id) compound condition is the key. Using a single column can cause missing or duplicate rows when multiple rows share the same timestamp, so a unique id is used as the second sort key. To use this pattern, a composite index matching the sort keys is required.

CREATE INDEX idx_events_created_at_id ON events(created_at ASC, id ASC);

Streaming and Backpressure

"Streaming" data means processing results in chunks rather than fetching everything at once. A concept often overlooked here is backpressure.

When the producer (DB) is faster than the consumer (application), buffers pile up. Backpressure is the mechanism that regulates the producer's send rate to match the consumer's processing speed. When this doesn't work correctly in Node.js streams, it silently eats memory.

The sequence below shows the flow of server-side cursor (DECLARE CURSOR + FETCH) approach. The async generator approach introduced later is fundamentally different in that it repeats a SELECT with a cursor condition instead of FETCH, so keep that distinction in mind.

The for await...of async iterator expresses this flow naturally. The next request won't go out until await processBatch(rows) completes.

Looking at the Actual Code

1. Keyset Pagination with Bun.SQL + Generator

This approach doesn't open a server-side cursor — it's a client-side loop that repeatedly calls SELECT with a cursor condition. The advantage is that it doesn't hold a transaction or occupy a connection.

import { SQL } from "bun";
 
const sql = new SQL("postgres://user:pass@localhost/db");
 
async function* fetchInCursor(lastId = 0, lastCreatedAt = new Date(0)) {
  while (true) {
    const rows = await sql`
      SELECT id, created_at, payload
      FROM events
      WHERE (created_at, id) > (${lastCreatedAt}, ${lastId})
      ORDER BY created_at ASC, id ASC
      LIMIT 500
    `;
 
    if (rows.length === 0) break;
 
    yield rows;
 
    lastCreatedAt = rows.at(-1).created_at;
    lastId = rows.at(-1).id;
  }
}
 
for await (const batch of fetchInCursor()) {
  await processBatch(batch);
}

One thing to watch out for. The code above passes rows.at(-1).created_at back as a parameter for the next query — whether a timestamptz column maps to a JavaScript Date or comes back as a string depends on the driver implementation. As of early 2026, Bun.SQL is documented to return timestamptz as a Date object, but it's safer to log and verify how it actually maps in your local schema before moving on. If it comes back as a string, you'll need to wrap it in new Date(...) or add explicit casting ($1::timestamptz) to avoid unexpected timezone issues.

Even if processBatch includes async operations (file writes, external API calls, etc.), backpressure is applied automatically — await blocks the flow.

2. postgres.js Cursor Streaming (Bun Compatible)

porsager/postgres works with Bun and officially supports streaming via server-side cursors.

import postgres from "postgres";
 
const sql = postgres("postgres://user:pass@localhost/db");
 
async function streamLargeTable() {
  const cursor = sql`SELECT * FROM large_table ORDER BY id ASC`.cursor(100);
 
  for await (const rows of cursor) {
    await processBatch(rows);
  }
}

Internally it uses DECLARE CURSOR + FETCH N. Memory usage is proportional to the chunk size even with tens of millions of rows, since the entire result set is never loaded into the driver buffer.

Another option in the Node.js ecosystem serving the same purpose is pg-query-stream. It exposes a Readable stream on top of pg and streams results using a server-side cursor. It appears in the decision flowchart below, so just keep the name in mind.

3. Handling PostgreSQL Server-Side Cursors Directly

For cases that require the finest-grained control, you can work at the SQL level directly. The reason this example alone receives sql as a parameter is explicit. DECLARE CURSOR must maintain the same connection within a transaction, so this represents the situation where the caller must acquire a dedicated connection within a transaction scope and pass it in. If you grab a new connection from the module-scope pool each time as you would with a regular query, the cursor gets closed.

async function fetchWithServerCursor(tx) {
  await tx`
    DECLARE log_cursor CURSOR FOR
    SELECT id, ts, message FROM logs
    WHERE ts > '2026-01-01'
    ORDER BY ts ASC, id ASC
  `;
 
  while (true) {
    const rows = await tx`FETCH 1000 FROM log_cursor`;
    if (rows.length === 0) break;
 
    await processBatch(rows);
  }
 
  await tx`CLOSE log_cursor`;
}
 
await sql.begin(async (tx) => {
  await fetchWithServerCursor(tx);
});

sql.begin(...) (or your driver's transaction helper) handles automatic rollback on exceptions, so there's no need to wrap the function body in another try/catch. This approach is the most memory-efficient, but remember that the cursor occupies one connection while it's open — if your pool size is 20, the concurrency ceiling for large queries is also 20.

4. Combining with HTTP Streaming Response

For use cases like large CSV exports, you can connect the DB stream directly to the HTTP response. The following is a conceptual example — for production use, proper CSV escaping is essential.

function toCsvCell(value) {
  if (value === null || value === undefined) return "";
  const s = typeof value === "string" ? value : JSON.stringify(value);
  if (/[",\n\r]/.test(s)) {
    return `"${s.replace(/"/g, '""')}"`;
  }
  return s;
}
 
export default {
  port: 3000,
  async fetch(req) {
    const stream = new ReadableStream({
      async start(controller) {
        try {
          controller.enqueue("id,created_at,payload\n");
 
          for await (const batch of fetchInCursor()) {
            for (const row of batch) {
              const line = [
                toCsvCell(row.id),
                toCsvCell(row.created_at.toISOString()),
                toCsvCell(row.payload),
              ].join(",") + "\n";
              controller.enqueue(line);
            }
          }
 
          controller.close();
        } catch (err) {
          controller.error(err);
        }
      },
    });
 
    return new Response(stream, {
      headers: {
        "Content-Type": "text/csv",
        "Transfer-Encoding": "chunked",
      },
    });
  },
};

Two things are key changes from the initial draft. First, toCsvCell escapes commas, newlines, and double-quotes — without this, a single comma in the payload breaks the CSV. Second, the start body is wrapped in try/catch to explicitly call controller.error(err). Without this, after the 200 OK header has already gone out, the stream silently drops and the client misinterprets it as a successful CSV — a silent data loss scenario.

Bun's Response accepts a ReadableStream directly and streams chunks to the client. Only chunk-sized data lives in server memory at any given time.

Which Approach to Use and When

pg-query-stream is a natural choice for teams already using the pg ecosystem who want to introduce streaming with minimal changes. For new code, postgres.js's .cursor() with its cleaner API is the straightforward pick.

Trade-offs

Honestly, this pattern has its own caveats.

Common Mistakes

Tuning chunk size by gut feeling: The optimal batch size depends on the average row size (hundreds of bytes vs tens of KB), network latency, and processing complexity. Rather than picking a number upfront, profiling to find the knee of the throughput-memory curve is more reliable. Too small and round-trips become the bottleneck; too large and a single chunk causes a memory spike — approach it by narrowing the range between those extremes.

Server-side cursor without a transaction: DECLARE CURSOR by default only lives within a transaction (with the exception of WITH HOLD). Using it outside a BEGIN ... COMMIT block closes the cursor immediately.

Missing await: for await (const batch of cursor) { processBatch(batch) } — dropping await eliminates backpressure and batches pile up in parallel. This is the classic pattern of streams silently eating memory.

Missing index on cursor column: Without a composite index on (created_at, id), keyset queries can actually be slower than offset. Always verify it's using an index scan with EXPLAIN.

When This Pattern Makes Sense, and How Deep to Go

This pattern isn't a silver bullet. If result sets are in the hundreds to thousands of rows, a plain offset + LIMIT is sufficient, and the cost of designing a keyset index and managing cursor protocol is outright over-engineering. The rough signals are:

• Responses exceed tens of thousands of rows, or the payload column is in the KB range
• The UX involves deep pagination like back-office or audit logs
• The result is large from the start, like data exports, overnight batch jobs, or migrations

If any of these signals apply, the cursor + backpressure combination is worth considering. Conversely, if the use case is just a list UI showing the first few pages, there's no reason to introduce it.

Here are some directions worth exploring further. First, using COPY (SELECT ...) TO STDOUT can make CSV/binary streaming even faster than cursors — for text export use cases, it's definitely worth benchmarking. Second, for workloads where results are consumed repeatedly, consuming an event stream via logical replication (LISTEN/NOTIFY, pgoutput) is the more fundamental solution rather than cursors. Third, Bun's built-in SQL native streaming API is being discussed in Issue #25307, so following its progress means you may eventually be able to drop the postgres.js dependency.

To summarize the immediate prescription: using Bun's built-in SQL for regular queries and postgres.js's .cursor() for large streaming queries in combination is the most practical setup as of early 2026. Change your query strategy before overhauling your runtime — most OOMs are already gone by then.

References

• Bun 1.2 Official Release Notes
• Bun SQL Official Documentation
• Bun SQL native iterator/stream — GitHub Issue #25307
• porsager/postgres — GitHub
• pg-query-stream — node-postgres
• Keyset Cursors, Not Offsets, for Postgres Pagination — Sequin
• Using DECLARE CURSOR to Reduce Memory Consumption — Cybertec PostgreSQL
• Your Node.js Streams Aren't Backpressuring — Frontend Masters
• Processing 1 Million SQL Rows to CSV using Node.js Streams — DEV Community