MD. NAZMUL ISLAM
HomeServicesPortfolioCase StudiesBlogAboutContact
ResumeLoginSign Up
NestJS Prisma Pagination Strategies for Large Tables
  1. Home
  2. Blog
  3. NestJS Prisma Pagination Strategies for Large Tables
Back to Blog
NestJSPrismaPostgreSQLNode.jsBackend

NestJS Prisma Pagination Strategies for Large Tables

November 18, 20264 min read2 views0 comments

Learn how to implement efficient offset and cursor pagination in NestJS and Prisma for high-performance PostgreSQL queries.

NestJS Prisma Pagination Strategies for Large Tables

When building backend APIs with NestJS, Prisma, and PostgreSQL, one of the first performance bottlenecks you will encounter is data pagination. If you have thousands or millions of records in a table, fetching them incorrectly can easily bring your database to its knees.

I have run into this issue many times when building backend systems for clients. A simple endpoint returning a list of items starts off fast during development, but slows down drastically as the database grows. In this guide, I want to share how I handle pagination properly using NestJS and Prisma, comparing offset-based pagination with cursor-based pagination so you can choose the right approach for your app.

The Problem with Offset Pagination

Offset pagination is what most developers start with. It relies on skip and take parameters in Prisma. Your frontend sends a page number, you calculate the offset, and query the database.

async function getUsers(page: number, limit: number) {
  const skip = (page - 1) * limit;
  return prisma.user.findMany({
    skip,
    take: limit,
    orderBy: { createdAt: 'desc' },
  });
}

This looks clean and works wonderfully for small tables. However, PostgreSQL still has to read and discard all the skipped rows under the hood. As your skip number grows into the tens of thousands, query execution times spike because of sequential scans or heavy index traversals.

Moving to Cursor-Based Pagination

For large tables or infinite scroll feeds—like the data feeds I built for JobsiteX—cursor-based pagination is a much better choice. Instead of skipping rows, you use a unique identifier (usually a primary key or timestamp) of the last item from the previous batch.

Here is how you implement cursor pagination in Prisma:

async function getUsersCursor(cursorId?: string, limit = 10) {
  return prisma.user.findMany({
    take: limit + 1, // Take one extra to check if there's a next page
    skip: cursorId ? 1 : 0,
    cursor: cursorId ? { id: cursorId } : undefined,
    orderBy: { id: 'asc' },
  });
}

By passing the ID of the last fetched record as a cursor, PostgreSQL can jump directly to that index location without scanning all preceding rows. This keeps query times consistently fast, no matter how large your table gets.

Creating a NestJS Service for Pagination

Let us wrap this into a reusable service inside a NestJS module. Keeping your pagination logic clean makes it easy to apply across different entities.

import { Injectable } from '@nestjs/common';
import { PrismaService } from './prisma.service';

@Injectable()
class PaginationService {
  constructor(private prisma: PrismaService) {}

  async paginateUsers(cursor?: string, take = 20) {
    const results = await this.prisma.user.findMany({
      take: take + 1,
      ...(cursor && {
        skip: 1,
        cursor: { id: cursor },
      }),
      orderBy: { id: 'asc' },
    });

    let nextCursor: string | null = null;
    if (results.length > take) {
      const nextItem = results.pop();
      nextCursor = nextItem.id;
    }

    return {
      data: results,
      meta: {
        nextCursor,
      },
    };
  }
}

Indexing for Maximum Performance

No matter which pagination strategy you choose, your PostgreSQL database needs proper indexes to perform well. If you order your queries by createdAt or id, ensure those fields have indexes in your Prisma schema:

model User {
  id        String   @id @default(uuid())
  email     String   @unique
  createdAt DateTime @default(now())

  @@index([createdAt])
}

Without an index on the sorting column, PostgreSQL is forced to perform a disk sort on every single request, destroying your API performance.

Summary

Choosing the right pagination strategy depends entirely on your user experience goals:

  • Use offset pagination if you need page numbers for small administrative tables where total count matters.
  • Use cursor pagination for infinite scrolls, mobile feeds, and large datasets where performance is critical.

If you need help architecting a robust backend or optimizing your database queries, feel free to get in touch.

Tagged with

NestJSPrismaPostgreSQLNode.jsBackend

Comments

Leave a comment

You must be signed in to comment.

MD. NAZMUL ISLAM

Full-Stack & React Native Developer

Send a project enquiry

Navigation

  • Home
  • About
  • Services
  • Case Studies
  • Blog
  • Contact
  • 한국어 (Korean)

Services

  • Web Development
  • Mobile App Development
  • Custom Software & SaaS
  • SEO & Marketing
  • Domain & Hosting

© 2026 MD. Nazmul Islam. All rights reserved.

Privacy Policy·Terms of Service