Remplacer OFFSET par une pagination par curseur avec PostgreSQL, Drizzle et tRPC

Les problèmes sont apparus au-delà de cent mille lignes. Le flux utilisait LIMIT … OFFSET …. À mesure que de nouvelles lignes entraient, certaines pages répétaient un élément et d’autres en sautaient. Les pages profondes ralentissaient aussi. Le cache et les index n’étaient pas seuls en cause. La stratégie de pagination ne convenait plus à un jeu de données alimenté en continu. Je l’ai remplacée par un curseur fondé sur un keyset, puis reliée à Drizzle, tRPC, TanStack Query et Next.js 16.

Quand LIMIT/OFFSET atteint ses limites

La pagination par décalage est directe. Elle calcule un décalage à partir du numéro de page et l’applique après ORDER BY :

SQL

SELECT *
FROM items
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 60;

Cette méthode fonctionne bien avec de petits jeux de données, mais deux problèmes fondamentaux apparaissent à grande échelle :

Les performances diminuent lorsque le décalage augmente. PostgreSQL doit parcourir et ignorer OFFSET lignes avant de renvoyer le moindre résultat. Une requête de test avec LIMIT 10 OFFSET 100 000 oblige la base de données à écarter cent mille lignes pour n’en retourner que dix. Sur une table d’un million de lignes, les mesures montrent que la latence augmente presque linéairement avec le décalage : une requête avec un décalage de 500 000 est des centaines de fois plus lente qu’avec un décalage de 0.
Les résultats deviennent incohérents lorsque les données changent. Entre la récupération de la page 1 (OFFSET 0) et celle de la page 2 (OFFSET pageSize), toute insertion ou suppression décale l’ensemble des résultats. De nouvelles lignes peuvent repousser les lignes existantes et provoquer des doublons ou des omissions. Un article consacré à l’optimisation de la pagination SQL indique que des lignes peuvent être dupliquées ou entièrement ignorées lorsque des requêtes OFFSET successives coïncident avec des insertions ou des suppressions. La base de données doit aussi produire l’ensemble des résultats pour appliquer le décalage, ce qui coûte cher avec des jointures complexes.

J’ai subi ces deux problèmes. À mesure que mon pipeline d’ingestion accélérait, le flux répétait parfois des articles ou omettait un lot entier. Mes métriques montraient également que chaque page supplémentaire était plus lente que la précédente. OFFSET n’était plus adapté à un jeu de données vivant et en croissance continue.

Pagination par keyset (fondée sur un curseur)

La pagination par keyset, aussi appelée pagination par curseur, supprime la notion de "numéro de page". La requête reprend après le dernier élément de la page précédente. Elle utilise un ordre stable, généralement un horodatage accompagné d’un identifiant unique. Le backend récupère ces deux champs sur la dernière ligne, les encode dans un curseur et les applique à la clause WHERE suivante.

SQL

-- first page
SELECT *
FROM items
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- subsequent page
SELECT *
FROM items
WHERE (created_at, id) < (cursor.created_at, cursor.id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Comme le tri repose toujours sur des colonnes déterministes, les nouvelles lignes insérées après le curseur courant n’apparaîtront jamais dans les pages précédentes. Selon le guide de Drizzle ORM, la pagination par curseur produit des résultats cohérents, sans lignes ignorées ni dupliquées, et se montre plus efficace que le décalage puisqu’elle ne doit pas parcourir puis ignorer les lignes antérieures. Le même guide précise qu’il n’est plus possible d’accéder directement à une page arbitraire et que la clause WHERE devient plus complexe. Ces compromis conviennent à un flux consulté de manière séquentielle.

Mon keyset utilise deux colonnes, published_at, un horodatage, et id, un UUID v7 monotone. Chaque curseur est un objet JSON { date, id } encodé en base64. Le client le traite comme une chaîne opaque. Seul le serveur l’encode, le décode et l’utilise pour reprendre la requête.

Plain text

eyJwdWJsaXNoZWRfYXQiOiAiMjAyNS0xMi0xMiAxMjoxMjoxMiIsICJpZCI6ICI4MmVlY2FkNC1hMWM5LTQyNWEtOTQ1Zi1hMDM2NGE4MjM5YzEifQ==

JSON

{
   "published_at": "2025-12-12 12:12:12",
   "id": "82eecad4-a1c9-425a-945f-a0364a8239c1"
}

Optimisation de la base de données et index

Le passage au keyset m’a obligé à revoir les index et les plans d’exécution. Avec ORDER BY published_at DESC, id DESC, un index composite doit couvrir ces colonnes dans le même ordre et la même direction.

SQL

CREATE INDEX idx_items_published_at_id
ON items (published_at DESC, id DESC);

Sans index correspondant, PostgreSQL ne peut pas éviter un parcours complet. La documentation de Drizzle souligne que les colonnes de tri doivent être correctement indexées pour rendre la pagination par curseur efficace. J’ai également conservé une colonne tsvector distincte pour la recherche en texte intégral, accompagnée d’un index GIN, afin que les filtres de recherche ne dégradent pas la requête de pagination.

Au-delà des index, j’ai dû réfléchir aux clés composites. Si deux articles ont le même horodatage published_at, un tri fondé uniquement sur cet horodatage n’est pas déterministe. Le critère secondaire id garantit un ordre stable. La clause WHERE qui récupère la page suivante devient (published_at < lastDate) OR (published_at = lastDate AND id < lastId). Cette approche est souvent appelée pagination par keyset, car la combinaison {published_at, id} identifie chaque ligne de manière unique.

Construire la couche de gestion du curseur

La couche de gestion du curseur se situe entre l’API et la base de données. Elle assume les responsabilités suivantes :

Décoder le curseur reçu. Lorsque le client transmet une chaîne en base64, je la décode en un objet { date, id }. En l’absence de curseur, je récupère la première page.
Normaliser la valeur de limit. Je plafonne la taille des pages à une valeur raisonnable, par exemple 100, afin de protéger la base de données.
Générer la clause WHERE. À partir du curseur décodé, je construis le filtre de keyset (published_at < date) OR (published_at = date AND id < id). Le filtre est omis si aucun curseur n’est fourni.
Récupérer limit + 1 lignes. La ligne supplémentaire permet de déterminer s’il existe une page suivante sans exécuter une coûteuse requête COUNT(*). Je retire cette ligne puis renvoie les autres comme page courante. Si elle existe, ses valeurs {date, id} sont encodées dans nextCursor.

Ces étapes résident dans un module utilitaire, que j’ai appelé pagination.ts, et sont réutilisées dans toute la couche de base de données. Centraliser la logique du curseur réduit les risques de bogues.

Implémenter la requête avec Drizzle ORM

Drizzle facilite l’écriture de requêtes SQL dont les types sont vérifiés. Voici une version simplifiée de ma requête getArticles :

TypeScript

export async function getArticles(db: Database, params: GetArticlesParams) {
  const pagination = buildPaginationState(params);
  const filters = buildFilters(params, pagination);

  const rows = await applyFilters(
    db
      .select({ ...articles, source: { ...sources } })
      .from(articles)
      .innerJoin(sources, eq(articles.sourceId, sources.id))
      .orderBy(desc(articles.publishedAt), desc(articles.id)),
    filters,
  ).limit(pagination.limit + 1);

  return buildPaginatedResult(rows, pagination, { date: 'publishedAt', id: 'id' });
}

La fonction buildFilters compose des filtres facultatifs comme category, sentiment, search et sourceId avec le prédicat du keyset. Je trie par publishedAt DESC, id DESC, demande limit + 1 lignes, puis laisse buildPaginatedResult découper les résultats et encoder le curseur.

Du côté de l’ingestion, mes robots d’exploration envoient les nouveaux articles avec une requête POST vers une route Hono authentifiée. Les données sont validées à l’aide d’un schéma Zod (createArticleSchema), puis persistées avec Drizzle. Utiliser Zod aux frontières de l’API et de la base de données garantit que les mêmes contraintes s’appliquent partout.

Exposer le flux avec tRPC

J’expose mes requêtes de base de données à travers un routeur tRPC. Chaque procédure utilise un schéma Zod pour valider ses entrées et renvoie un résultat typé. Mon articlesRouter se présentait ainsi :

TypeScript

export const articlesRouter = createTRPCRouter({
  list: protectedProcedure
    .input(getArticlesSchema)
    .query(({ ctx, input }) => getArticles(ctx.db, input)),
  create: protectedProcedure
    .input(createArticleSchema)
    .mutation(({ ctx, input }) => createArticle(ctx.db, input)),
  // other procedures
});

Comme tRPC déduit le type de sortie de getArticles, mes composants React peuvent importer RouterOutputs["articles"]["list"]["items"][number] et connaître précisément les champs disponibles. La chaîne du curseur est transmise telle quelle. SuperJSON sérialise les objets Date sans les convertir en chaînes. La documentation de tRPC précise que la procédure utilisée avec useInfiniteQuery doit accepter une entrée cursor, ce qui correspond au champ cursor?: string | null de mon schéma.

Préchargement côté serveur avec Next.js 16

Les composants serveur de Next.js 16 permettent de précharger les requêtes tRPC pendant la requête initiale. Dans mon composant serveur, j’appelle prefetch avant le rendu :

TypeScript

export async function ArticlesPage() {
  prefetch(trpc.articles.list.infiniteQueryOptions({ limit: 12 }));

  return (
    <HydrateClient>
        <ArticlesFeed />
    </HydrateClient>
  );
}

L’état déshydraté est transmis à HydrateClient afin que le client réutilise les données initiales sans aller-retour supplémentaire. Ce modèle accélère le premier affichage et garantit une hydratation correcte des curseurs et des dates.

Charger les pages progressivement avec TanStack React Query

Côté client, j’utilise le hook useInfiniteQuery de TanStack autour du client tRPC :

TypeScript

const trpc = useTRPC();
const query = useInfiniteQuery(
    trpc.articles.list.infiniteQueryOptions(
      {
        limit: 12,
      },
      {
        getNextPageParam: (lastPage) => (lastPage.meta.hasNext ? lastPage.meta.nextCursor : null),
        initialCursor: null,
      },
    ),
  );

const articles = React.useMemo(
    () => query.data?.pages.flatMap((page) => page.items) ?? [],
    [query.data],
);

useInfiniteQuery gère l’état de la pagination. Lorsque l’utilisateur clique sur "Charger plus", j’appelle query.fetchNextPage(). Le hook inspecte l’objet meta renvoyé par le serveur et transmet le nextCursor opaque. TanStack Query met chaque curseur en cache séparément. Une modification des filtres réinitialise donc la pagination. Il déduplique aussi les requêtes et suit les états de chargement.

Ce qui a changé après la migration

Après la migration, deux améliorations ont été immédiatement visibles :

Résultats stables. Les nouveaux articles ajoutés en tête du flux ne provoquent ni doublons ni éléments manquants. Chaque page est ancrée aux dernières valeurs published_at et id rencontrées. Les insertions, mises à jour ou suppressions entre deux requêtes ne réorganisent donc pas les résultats. L’analyse de ReadySet sur la pagination par curseur indique que celle-ci reste efficace même à grande profondeur et produit des résultats stables malgré l’insertion ou la suppression d’enregistrements entre deux requêtes de page.
Performances prévisibles. Le défilement loin dans le flux n’entraîne plus un coût en O(n). Grâce à un keyset indexé, la requête accède directement à la page suivante au lieu d’écarter des milliers de lignes. La même analyse de ReadySet souligne que la pagination par curseur évite de parcourir et d’ignorer des lignes, ce qui réduit la charge de la base de données et la consommation de ressources.

Cette approche a des limites. Les utilisateurs ne peuvent plus ouvrir directement la "page 37", puisque les pages ne sont pas repérées par un décalage. L’utilitaire doit aussi gérer les dernières pages incomplètes et les suppressions. Je renvoie toujours meta.hasNext. Le client désactive le bouton "Charger plus" lorsque cette valeur vaut false. J’accepte que des suppressions créent quelques écarts dans un flux en direct.

Enseignements et prochaines étapes

La pagination touche le schéma de la base, les requêtes, l’API et le frontend. Quand le jeu de données a commencé à évoluer en continu, le décalage a produit des pages incohérentes. Le curseur a demandé des changements dans chaque couche, mais le flux ne répète plus les lignes déplacées par de nouvelles insertions.

Si vous envisagez une migration similaire, commencez par les étapes suivantes :

Choisir votre keyset. Sélectionnez des colonnes qui identifient chaque ligne de manière unique et correspondent à l’ordre souhaité, généralement un horodatage et une clé primaire.
Créer les bons index. Sans index composite correspondant au keyset et au sens du tri, la pagination par keyset sera lente.
Encapsuler la logique de pagination. Centralisez l’encodage et le décodage du curseur, ainsi que la génération de la clause WHERE, dans un module utilitaire.
Utiliser des schémas partagés. Définissez les schémas Zod, ou leur équivalent, une seule fois et réutilisez-les dans le backend et le frontend. Vous éviterez ainsi les divergences de types et garantirez une transmission correcte du curseur.
Utilisez les outils existants. Drizzle, tRPC et TanStack Query fournissent des briques pour la pagination par keyset.

La pagination par décalage reste simple et convient aux petits jeux de données stables. Une ingestion continue et des pages profondes changent le besoin. Le keyset demande un ordre total et un curseur, mais évite de parcourir toutes les lignes précédentes.

La pagination par curseur a stabilisé ce flux alimenté en continu et évité le coût des décalages profonds. Elle impose un ordre total, un index composite correspondant et un curseur opaque validé par le serveur.

Elle ne remplace pas toujours OFFSET. Une interface qui doit ouvrir directement une page arbitraire garde un besoin différent. Pour un fil chronologique ou un défilement infini, le keyset correspond mieux à la manière dont les données sont lues.

Remplacer OFFSET par une pagination par curseur avec PostgreSQL, Drizzle et tRPC

Quand LIMIT/OFFSET atteint ses limites

Optimisation de la base de données et index

Construire la couche de gestion du curseur

Implémenter la requête avec Drizzle ORM

Exposer le flux avec tRPC

Préchargement côté serveur avec Next.js 16

Charger les pages progressivement avec TanStack React Query

Ce qui a changé après la migration

Enseignements et prochaines étapes

Articles liés

Partager des composants UI dans un monorepo Next.js 16 avec Bun et Turborepo