Pagination & Streaming Large Results

Large result sets are the most common performance problem in database MCP servers. An AI model requesting "all orders" from a table with 10 million rows will timeout the query, exhaust server memory, and overflow the model context window. Pagination — exposing page and limit as URI template parameters — lets the model request manageable chunks. The server enforces a maximum page size (typically 500-1000 rows) and returns metadata indicating total count and whether more pages exist.

Cursor-based pagination is superior to offset-based for large datasets. Offset pagination (OFFSET 50000 LIMIT 500) requires the database to scan and discard 50,000 rows, which gets slower as the offset increases. Cursor pagination (WHERE id > $lastId ORDER BY id LIMIT 500) uses an index seek and performs consistently regardless of depth. The MCP server returns the last ID in each page as a cursor, and the client passes it back to fetch the next page. This maps naturally to MCP URI templates: db://orders{?cursor,limit}.

Streaming is the ultimate solution for truly large result sets — data that should not be fully materialized in memory. MCP does not natively support streaming resource responses (the protocol returns complete content), but you can simulate it with chunked resources. Return the first chunk with a nextCursor pointer, and the client reads subsequent chunks by following the cursor. Each chunk is a complete, valid JSON document. This keeps memory usage constant on both the server and client, regardless of total result set size.

const MAX_PAGE_SIZE = 500;

// Cursor-based pagination resource
server.resource(
  "orders-paginated",
  new ResourceTemplate("db://orders{?cursor,limit}", { list: undefined }),
  async (uri, params) => {
    const limit = Math.min(Number(params.limit) || 100, MAX_PAGE_SIZE);
    const cursor = params.cursor;

    let query: string;
    let queryParams: (string | number)[];

    if (cursor) {
      query = `SELECT * FROM orders WHERE id > $1
               ORDER BY id ASC LIMIT $2`;
      queryParams = [cursor, limit + 1]; // Fetch one extra to detect hasMore
    } else {
      query = `SELECT * FROM orders ORDER BY id ASC LIMIT $1`;
      queryParams = [limit + 1];
    }

    const result = await pool.query(query, queryParams);
    const hasMore = result.rows.length > limit;
    const rows = hasMore ? result.rows.slice(0, limit) : result.rows;
    const nextCursor = hasMore ? rows[rows.length - 1].id : null;

    // Get total count (cached — expensive query)
    const totalResult = await pool.query(
      "SELECT COUNT(*) AS total FROM orders"
    );

    return {
      contents: [{
        uri: uri.href,
        mimeType: "application/json",
        text: JSON.stringify({
          items: rows,
          total: Number(totalResult.rows[0].total),
          cursor: nextCursor,
          hasMore,
          pageSize: limit,
        }),
      }],
    };
  }
);

1. Enforce page size limits Set a MAX_PAGE_SIZE constant (500-1000 rows) and clamp the limit parameter. Never let the model request unbounded result sets.
2. Use cursor-based pagination Replace OFFSET/LIMIT with WHERE id > $cursor ORDER BY id LIMIT $n. Return the last ID as the cursor for the next page.
3. Return pagination metadata Include total, cursor, hasMore, and pageSize in every paginated response. The model needs this to navigate the full dataset efficiently.