Choosing Pagination Strategies for tRPC APIs

The Pagination Performance Gap

When building data-heavy interfaces with tRPC, the primary challenge is balancing user navigation needs with database performance. As datasets grow, the method used to fetch subsets of data—pagination—becomes a critical bottleneck. Choosing the wrong strategy leads to slow response times (latency) or inconsistent data (duplicate items) as users scroll through lists.

Comparing Offset vs. Cursor Pagination

The decision usually falls between Offset-based pagination (using skip/take) and Cursor-based pagination (using a pointer to a specific record). The following table outlines the technical trade-offs.

Feature	Offset-based	Cursor-based
Implementation	Simple (skip/take)	Moderate (cursor/take)
DB Complexity	O(n) - Scans skipped rows	O(1) - Direct index lookup
Data Stability	Unstable (Items shift on insert)	Stable (Pointer remains fixed)
Navigation	Jump to specific page	Infinite scroll / Next-Prev

Engineering Trade-offs

Offset Pagination is ideal for small datasets or administrative panels where a user must jump to a specific page (e.g., "Page 42"). However, because the database must scan through every row it is told to "skip," performance degrades linearly as the offset increases. If a user requests the 10,000th record, the database still processes the first 9,999 before discarding them.

Cursor Pagination solves the performance issue by using a unique, sequential identifier—the cursor—to mark the exact spot where the last fetch ended. The database jumps directly to that record via an index. The trade-off is that you lose the ability to jump to an arbitrary page number; you can only move forward or backward relative to the cursor.

Implementation Example: Cursor-based Pagination

This example assumes tRPC v10+ and a database ORM like Prisma. The goal is to implement a stable, performant list of users.

import { initTRPC } from '@trpc/server';
import { z } from 'zod';

const t = initTRPC.create();

export const appRouter = t.router({
  getUsers: t.procedure
    .input(
      z.object({
        limit: z.number().min(1).max(100).default(20),
        cursor: z.string().nullish(), // The ID of the last item from the previous page
      })
    )
    .query(async ({ input }) => {
      const { limit, cursor }
n      // Fetch limit + 1 to determine if there is a next page
      const items = await prisma.user.findMany({
        take: limit + 1,
        cursor: cursor ? { id: cursor } : undefined,
        orderBy: { id: 'asc' },
      });

      let nextCursor: typeof cursor | undefined = undefined;

      if (items.length > limit) {
        const nextItem = items.pop();
        nextCursor = nextItem?.id;
      }

      return {
        items,
        nextCursor,
      };
    }),
});

Deployment and Verification

To deploy this, run the tRPC server with the appropriate database permissions to read the target table. On the client, the nextCursor returned by the server should be passed back into the cursor input for the subsequent request.

Verification Steps:

• Type Check: Verify that the client-side TypeScript types for getUsers strictly require a number for limit and a string or null for cursor.
• Stability Test: Load a page of data, insert a new record into the database that would appear on that page, and fetch the next page. With cursor pagination, you should not see the same record twice.
• Performance Check: Compare the response time of a request with a high offset (e.g., skip: 50000) against a request with a cursor pointing to the same depth. The cursor request should maintain a near-constant response time.

Limitations

Cursor pagination requires a column that is both unique and sequentially sortable (like an auto-incrementing ID or a timestamp). If you need to sort by a non-unique field (e.g., "Last Name"), you must create a composite cursor—combining the sort field and the unique ID—to prevent skipping records with identical values.