Pourquoi OFFSET m’a fait défaut : implémenter la pagination par curseur de bout en bout avec PostgreSQL, Drizzle, tRPC et Next.js 16

Les premières failles sont apparues lorsque j’ai dépassé les cent mille lignes. Mon flux, jusque-là réactif et construit autour d’une simple requête LIMIT … OFFSET …, a commencé à se comporter de manière imprévisible. Les nouvelles lignes étaient insérées plus vite que les utilisateurs ne pouvaient faire défiler le contenu : certaines pages affichaient des éléments en double, d’autres en omettaient, et les temps de chargement augmentaient fortement. J’ai fini par comprendre que le problème ne venait ni d’un cache défaillant ni d’un index manquant, mais de la stratégie de pagination utilisée depuis le début. Cet article explique comment je suis passé d’une pagination par décalage à une approche par curseur fondée sur un keyset, comment j’ai optimisé la base de données et comment l’intégration avec Next.js 16, tRPC et TanStack Query m’a permis de proposer un défilement infini.

Quand LIMIT/OFFSET atteint ses limites

Sur le papier, la pagination par décalage paraît élégante. Il suffit de choisir une taille de page, de calculer le décalage à partir du numéro de page et de l’appliquer après une clause ORDER BY :

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 :

1. 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.

2. 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 entièrement la notion de « numéro de page ». Au lieu d’ignorer un nombre arbitraire de lignes, la requête reprend après le dernier élément de la page précédente. Il faut choisir un ordre de tri stable, généralement un horodatage accompagné d’un identifiant unique, récupérer ces champs sur la dernière ligne renvoyée, les encoder dans un curseur et les utiliser dans la clause WHERE de la requête suivante. Par exemple :

-- 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.

J’ai choisi un keyset à deux colonnes : published_at (un horodatage) et id (un UUID v7 monotone). Chaque curseur est un objet JSON { date, id } encodé en base64. Il reste ainsi opaque pour le client tout en fournissant au backend les informations nécessaires pour reprendre précisément la requête. Côté client, je traite le curseur comme une simple chaîne opaque. Seul le serveur est responsable de son encodage et de son décodage.

eyJwdWJsaXNoZWRfYXQiOiAiMjAyNS0xMi0xMiAxMjoxMjoxMiIsICJpZCI6ICI4MmVlY2FkNC1hMWM5LTQyNWEtOTQ1Zi1hMDM2NGE4MjM5YzEifQ==

{
   "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 à la pagination par keyset m’a obligé à traiter un chantier jusque-là reporté : les index et les plans d’exécution. Lorsque la clause ORDER BY vaut published_at DESC, id DESC, un index composite doit couvrir ces colonnes dans le même ordre et la même direction :

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 :

1. 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.

2. 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.

3. 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.

4. 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 :

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 :

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 :

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 :

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 simplement 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 automatiquement la pagination. Il déduplique également 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 comporte des compromis. Les utilisateurs ne peuvent plus accéder directement à la « page 37 », puisque les pages ne sont plus repérées par un décalage. Mon utilitaire de pagination a aussi dû gérer davantage de cas limites : que se passe-t-il si la dernière page contient moins d’éléments ? Comment traiter les suppressions ? J’ai choisi de toujours renvoyer meta.hasNext et de laisser le client désactiver le bouton « Charger plus » lorsque cette valeur vaut false. Pour les suppressions, j’accepte que quelques écarts puissent apparaître, un compromis raisonnable pour un flux en direct.

Enseignements et prochaines étapes

Cette expérience m’a appris que la pagination n’est pas seulement un sujet d’interface : c’est une question d’architecture de bout en bout. Dès que mon jeu de données s’est mis à évoluer comme un tapis roulant plutôt que comme une bibliothèque, la stratégie par décalage a cessé de fonctionner. La migration vers la pagination par curseur a demandé une coordination entre le schéma de la base de données, les utilitaires de requête, la couche API et le frontend. En contrepartie, j’ai obtenu un flux cohérent et fluide qui suit le rythme de mon ingestion.

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

1. 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.

2. Créer les bons index. Sans index composite correspondant au keyset et au sens du tri, la pagination par keyset sera lente.

3. 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.

4. 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.

5. Vous appuyer sur vos outils. Drizzle, tRPC et TanStack Query fournissent tous des primitives pour la pagination par keyset. Utilisez-les pour gérer l’essentiel du travail.

La pagination par décalage n’est pas mauvaise : elle est rapide à mettre en œuvre et convient aux petits jeux de données statiques. Mais lorsque votre application ingère des données en temps réel et compte des centaines de pages, une autre approche devient nécessaire. La pagination par keyset demande davantage de réflexion, mais apporte cohérence et performances.

Bon développement !