Cursor-Based Pagination in NestJS (Goodbye OFFSET)

Every activity feed works beautifully in the demo and falls over in production the same way: someone pages deep. The first few screens of logs scroll instantly, then an auditor jumps to page 500, the query stalls, the database CPU spikes, and your API starts handing out timeouts. That's the OFFSET wall, and cursor-based pagination is how you get past it for good.

Here's the whole idea up front: stop telling the database how many rows to skip, and start telling it where you left off. LIMIT 20 OFFSET 100000 makes Postgres read and discard 100,000 rows before returning 20 — work that grows with depth. A cursor seeks straight to your position using an index, so page 5,000 costs the same as page 1. It's the same lesson as every performance post we write: most "scaling" problems are query problems wearing a costume.

Why OFFSET Falls Apart (and Drifts)

The naive query degrades because of what the engine actually does:

1-- Reads 100,020 rows, throws away 100,000, returns 20. Gets worse every page.
2SELECT * FROM activity_logs
3ORDER BY created_at DESC
4LIMIT 20 OFFSET 100000;

There's no magic jump to row 100,001 — the database walks the ordered set and discards everything before your offset. That's O(N) in the depth you've paged to. We once took a client dashboard from 8 seconds to 340ms without sharding or a rewrite — just an index, a better join, and killing patterns exactly like this one. (More on that in PostgreSQL performance for SaaS dashboards.)

OFFSET has a second, quieter bug: data drift. Insert a new log while a user sits on page 3, and every row shifts down one — their next page repeats a record. Delete one and a record vanishes from their view entirely. For an append-heavy feed, OFFSET isn't just slow, it's wrong.

How Cursor-Based Pagination Works

Instead of an offset, you keep a pointer to the last row's sort values and seek past it:

1-- Index seek to the exact position — roughly O(log N), flat at any depth
2SELECT * FROM activity_logs
3WHERE (created_at, id) < ('2026-06-29T10:00:00.000Z', 542010)
4ORDER BY created_at DESC, id DESC
5LIMIT 20;

The engine uses the index to land on the row directly. No scan, no discarded rows, and because you're anchored to a real row rather than a position, new inserts above you don't cause drift. Markus Winand's No Offset is the canonical write-up on the index theory, and Milan Jovanović has a good deep dive on why cursor pagination stays fast.

Choose a Stable, Unique, Sortable Cursor

A production cursor must be stable, strictly unique, and sortable. createdAt is sortable but not unique — two events in the same millisecond share a timestamp and a timestamp-only cursor will drop or duplicate one at the boundary. Bind it to a unique tiebreaker and index the pair:

1CREATE INDEX idx_logs_pagination ON activity_logs (created_at DESC, id DESC);

That composite index is what makes the seek above actually a seek and not a sort.

NestJS: Opaque Token Serialization

Don't leak your internal sort columns to clients — encode the cursor as an opaque base64 token:

1import { BadRequestException } from '@nestjs/common';
2
3export interface CursorPayload {
4  createdAt: string;
5  id: number;
6}
7
8export class CursorSerializer {
9  static serialize(payload: CursorPayload): string {
10    return Buffer.from(JSON.stringify(payload)).toString('base64url');
11  }
12
13  static deserialize(token: string): CursorPayload {
14    try {
15      return JSON.parse(Buffer.from(token, 'base64url').toString('utf-8')) as CursorPayload;
16    } catch {
17      throw new BadRequestException('Invalid pagination cursor.');
18    }
19  }
20}

Validate the incoming query with a NestJS DTO and class-validator so limit can't be abused:

1// src/common/dto/cursor-pagination.dto.ts
2import { IsOptional, IsString, IsInt, Min, Max } from 'class-validator';
3import { Type } from 'class-transformer';
4
5export class CursorPaginationDto {
6  @IsOptional() @IsString()
7  cursor?: string;
8
9  @IsOptional() @Type(() => Number) @IsInt() @Min(1) @Max(100)
10  limit = 20;
11}

The Prisma Keyset Query

Decode the token into an explicit keyset WHERE — the version that mirrors the SQL above and stays portable:

1// src/activity/activity.service.ts
2import { Injectable } from '@nestjs/common';
3import { PrismaService } from '../prisma/prisma.service';
4import { CursorPaginationDto } from './dto/cursor-pagination.dto';
5import { CursorSerializer } from '../common/cursor-serializer';
6
7@Injectable()
8export class ActivityService {
9  constructor(private readonly prisma: PrismaService) {}
10
11  async getLogs({ cursor, limit }: CursorPaginationDto) {
12    const where = cursor
13      ? (() => {
14          const c = CursorSerializer.deserialize(cursor);
15          const createdAt = new Date(c.createdAt);
16          // (created_at, id) < (cursor.created_at, cursor.id)
17          return {
18            OR: [
19              { createdAt: { lt: createdAt } },
20              { createdAt, id: { lt: c.id } },
21            ],
22          };
23        })()
24      : {};
25
26    // Fetch one extra row to detect whether a next page exists.
27    const rows = await this.prisma.activityLog.findMany({
28      where,
29      take: limit + 1,
30      orderBy: [{ createdAt: 'desc' }, { id: 'desc' }],
31    });
32
33    const hasNextPage = rows.length > limit;
34    const data = hasNextPage ? rows.slice(0, limit) : rows;
35    const last = data.at(-1);
36
37    return {
38      data,
39      meta: {
40        hasNextPage,
41        nextCursor: hasNextPage && last
42          ? CursorSerializer.serialize({ createdAt: last.createdAt.toISOString(), id: last.id })
43          : null,
44      },
45    };
46  }
47}

The take: limit + 1 trick is the cheapest way to know if there's a next page without a second query — you fetch one extra row, and its existence is the answer. This pattern slots straight into an enterprise audit log, which is exactly the high-volume, append-only data that punishes OFFSET.

Next.js Infinite Scroll

On the client, the cursor maps cleanly to an infinite feed — keep the token, append the results:

1import { useState, useEffect } from 'react';
2
3export function ActivityFeed() {
4  const [items, setItems] = useState<any[]>([]);
5  const [cursor, setCursor] = useState<string | null>(null);
6  const [loading, setLoading] = useState(false);
7
8  async function loadMore(next: string | null) {
9    if (loading) return;
10    setLoading(true);
11    const res = await fetch(`/api/activity?limit=20${next ? `&cursor=${next}` : ''}`);
12    const { data, meta } = await res.json();
13    setItems((prev) => [...prev, ...data]);
14    setCursor(meta.nextCursor);
15    setLoading(false);
16  }
17
18  useEffect(() => { loadMore(null); }, []);
19
20  return (
21    <>
22      <ul>{items.map((log) => <li key={log.id}>{log.message}</li>)}</ul>
23      {cursor && <button onClick={() => loadMore(cursor)} disabled={loading}>Load more</button>}
24    </>
25  );
26}

OFFSET vs Cursor: When to Use Which

I'm not going to hand you a benchmark table with false precision — the numbers depend entirely on your rows, indexes, and hardware. The shape is what's reliable: OFFSET climbs with depth, cursor stays flat. That's not a micro-optimization, it's a different algorithm.

The honest takeaway: cursor pagination isn't automatically "better" — it's the right tool for feeds and the wrong tool for a numbered admin table that never goes past page 10. Use OFFSET where it's harmless, reach for keyset the moment your list is an append-heavy feed, and design the response shape (a consistent paginated envelope) before you have a million rows, not after page 500 starts timing out on a customer's screen.