
Learn how to implement efficient offset and cursor pagination in NestJS and Prisma for high-performance PostgreSQL queries.
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.
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.
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.
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,
},
};
}
}
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.
Choosing the right pagination strategy depends entirely on your user experience goals:
If you need help architecting a robust backend or optimizing your database queries, feel free to get in touch.
Tagged with
Leave a comment