Cursor pagination in Next.js App Router with Drizzle ORM -- large datasets without the offset slowdown

Offset pagination (LIMIT 20 OFFSET 980) is simple and fine for small tables. As a table grows past 100k rows, Postgres has to scan and discard every row before the offset. A page flip deep into a large dataset takes hundreds of milliseconds even with an index.

Cursor pagination avoids the scan. Instead of "skip N rows", you say "give me rows where created_at < [cursor]". Postgres hits the index directly and never reads rows you will not return.

This post shows the full pattern: a keyset query in Drizzle ORM, a cursor codec, and a URL-driven server component that composes cleanly with the DDD-lite module structure.

The keyset query

Cursor pagination is a WHERE clause, not an offset.

// modules/post/post.repo.ts
import { db } from '@/db/drizzle';
import { postTable } from '@/db/schema';
import { desc, lt, and, eq } from 'drizzle-orm';
import type { PostCursorFilter } from './post.types';
 
const PAGE_SIZE = 20;
 
export async function findPostsCursor(filter: PostCursorFilter) {
  const { cursor, authorId } = filter;
 
  const conditions = [eq(postTable.published, true)];
 
  if (authorId) {
    conditions.push(eq(postTable.authorId, authorId));
  }
 
  if (cursor) {
    const { createdAt } = decodeCursor(cursor);
    conditions.push(lt(postTable.createdAt, createdAt));
  }
 
  const rows = await db
    .select()
    .from(postTable)
    .where(and(...conditions))
    .orderBy(desc(postTable.createdAt))
    .limit(PAGE_SIZE + 1); // fetch one extra to detect next page
 
  const hasNextPage = rows.length > PAGE_SIZE;
  const posts = hasNextPage ? rows.slice(0, PAGE_SIZE) : rows;
  const nextCursor = hasNextPage
    ? encodeCursor(posts[posts.length - 1])
    : null;
 
  return { posts, nextCursor };
}

The limit + 1 trick is standard: fetch one more row than the page size, then check if it came back. If it did, there is a next page -- drop the extra before returning.

The cursor codec

A cursor is an opaque string the client sends back to request the next page. Encode the sort column value -- never a row number or offset.

// modules/post/post.repo.ts (continued)
 
function encodeCursor(post: { createdAt: Date }): string {
  return Buffer.from(post.createdAt.toISOString()).toString('base64url');
}
 
function decodeCursor(cursor: string): { createdAt: Date } {
  return { createdAt: new Date(Buffer.from(cursor, 'base64url').toString()) };
}

base64url keeps the cursor URL-safe without percent-encoding. If your timestamps are not unique -- bulk inserts can cause collisions -- add a second sort column (typically id) and encode both values in the cursor tuple.

The composite index

For the keyset query to stay fast at scale, add an index that matches the sort order:

// modules/post/post.schema.ts
import { index, sql } from 'drizzle-orm/pg-core';
 
export const postCursorIndex = index('idx_posts_cursor')
  .on(postTable.createdAt.desc())
  .where(sql`${postTable.published} = true`);

A partial index on published = true is smaller and faster than a full-table index when the majority of rows are unpublished drafts. Run npm run db:generate && npm run db:migrate after adding it.

The types

// modules/post/post.types.ts
export interface PostCursorFilter {
  cursor?: string;
  authorId?: string;
}
 
export interface PostCursorPage {
  posts: Post[];
  nextCursor: string | null;
}

The service

No changes to the service contract -- cursor pagination is a query detail, not business logic:

// modules/post/post.service.ts
import { findPostsCursor } from './post.repo';
import type { PostCursorFilter } from './post.types';
 
export async function getPostsCursor(filter: PostCursorFilter) {
  return findPostsCursor(filter);
}

The server component

Cursor state lives in a ?cursor= search param, the same way page number lives in ?page= for offset pagination:

// app/(main)/blog/page.tsx
import { getPostsCursor } from '@/modules/post';
import { PostList } from '@/components/post-list';
import { CursorPaginationControls } from '@/components/cursor-pagination-controls';
 
interface Props {
  searchParams: Promise<{ cursor?: string }>;
}
 
export default async function BlogPage({ searchParams }: Props) {
  const { cursor } = await searchParams;
  const { posts, nextCursor } = await getPostsCursor({ cursor });
 
  return (
    <>
      <PostList posts={posts} />
      <CursorPaginationControls nextCursor={nextCursor} />
    </>
  );
}

The pagination control is a plain <Link> -- no client state, no infinite scroll library, no hydration overhead:

// components/cursor-pagination-controls.tsx
'use client';
import Link from 'next/link';
import { usePathname, useSearchParams } from 'next/navigation';
 
interface Props {
  nextCursor: string | null;
}
 
export function CursorPaginationControls({ nextCursor }: Props) {
  const pathname = usePathname();
  const params = useSearchParams();
 
  if (!nextCursor) return null;
 
  const next = new URLSearchParams(params);
  next.set('cursor', nextCursor);
 
  return (
    <Link href={`${pathname}?${next}`}>
      Load more
    </Link>
  );
}

The URL is the cursor -- bookmarkable, shareable, and server-component-friendly. No useState, no useEffect, no re-hydration surprises.

Cursor vs offset: when to use each

Use cursor pagination when:

• The table has more than 50k rows
• Page stability matters (a new row inserted mid-browse should not shift the page boundaries a user is already on)
• You are building a "load more" or infinite scroll UI

Stick with offset pagination when:

• Users need to jump to an arbitrary page number ("go to page 7")
• The dataset is small enough that scan cost is negligible
• You need backwards navigation (cursor pages are forward-only without additional state)

Takeaway

Cursor pagination in Drizzle ORM is three things: a WHERE clause on your sort column, a limit + 1 check for detecting the next page, and a base64url cursor that travels in the URL. Add the partial index, wire the cursor through search params, and your paginated queries stay fast regardless of how large the Neon DB table grows. Apply the same repo, service, page structure to any module in the DDD-lite pattern -- the shape is identical to offset pagination, just a different clause.