Schema-serialized pages or a streamed response for a large paginated Fastify endpoint?

Short answer

Keep the response schema and Fastify's compiled serializer. With a page cap of a few hundred rows, the memory argument for streaming does not apply — it is a technique for unbounded exports, not bounded pages. Catch undeclared fields by letting Fastify's response validation fail loudly in development, and skip COUNT(*) unless a client genuinely needs it.

Why streaming gains you nothing here

Streaming a Readable from the database driver exists to avoid buffering an unbounded result set. Your querystring schema already bounds the page at a few hundred rows, so the entire payload is, at most, a few hundred small objects — typically well under a few megabytes. You can confirm this directly:

const before = process.memoryUsage().heapUsed;
const rows = await db.query(/* bounded page query */);
console.log('heap delta MB:', (process.memoryUsage().heapUsed - before) / 1e6);

If the delta is single-digit megabytes, buffering is a non-issue. In exchange for streaming you would lose the compiled serializer, lose response validation, and inherit the awkward problem of surfacing a mid-stream database error after headers have already been flushed — at that point the only honest option is aborting the connection, which clients experience as a truncated response rather than a clean 500.

Catching undeclared fields during development

The silent-drop behavior you describe happens because fast-json-stringify only emits declared properties. The accepted guardrail is Fastify's response validation, which runs before serialization: if the handler returns an object with properties not permitted by the schema (assuming the schema does not allow additional properties), validation fails and Fastify returns a 500 with a validation error. To make this visible instead of mysterious, wire an error handler in development:

app.setErrorHandler((err, req, reply) => {
  if (err.validation) {
    req.log.error({ validation: err.validation }, 'response schema mismatch');
  }
  reply.send(err);
});

A fast unit test that injects a row with an extra property and asserts a 500 confirms the guardrail is active. Note the caveat: this only works if the payload goes through reply.send() with a response schema attached. Anything written via reply.raw bypasses both validation and serialization entirely, so undeclared fields will slip through silently there.

Is COUNT(*) worth it?

Usually not, on a millions-row table. A full COUNT(*) is roughly O(N) work per request (exact cost depends on the engine, indexes, and filtering), paid on every page fetch just to render a total. Practical alternatives, in rough order of preference:

1. Omit it. Cursor pagination only needs "has next page," which you get by fetching page size + 1 rows.
2. Compute it on demand — only when the client explicitly requests totalCount, or only on the first page.
3. Approximate or cache it — many databases expose planner estimates, or you can cache a count with a short TTL if exactness is not required.

Recommendation

Schema-serialized pages via reply.send(), response validation surfaced through a dev error handler, cursor-based pagination without a total count by default. Reserve streaming for a separate, explicitly unbounded export endpoint if one ever exists — and treat its post-headers error handling as a design problem of its own.