SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000 répond en quelques millisecondes sur une table de test, et en plusieurs secondes sur la table de production équivalente avec dix millions de lignes. C'est le piège classique de la pagination par OFFSET : elle fonctionne parfaitement en développement, où les volumes sont petits, et se dégrade de façon silencieuse en production, exactement au moment où les pages profondes (scroll infini, exports paginés, crawlers) commencent à solliciter la base.
Pourquoi OFFSET coûte de plus en plus cher
OFFSET n'est pas une opération de positionnement direct. Le moteur de base de données doit d'abord trier (ou parcourir un index déjà trié) toutes les lignes qui précèdent l'offset demandé, les compter, puis les jeter, avant de renvoyer les lignes réellement voulues. Avec OFFSET 100000 LIMIT 20, PostgreSQL lit et élimine 100 000 lignes pour n'en retourner que 20. Le coût croît linéairement avec la profondeur de page, même si un index couvre parfaitement la clause ORDER BY.
EXPLAIN ANALYZE
SELECT id, created_at, status
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;
Sur une table indexée sur created_at, le plan montre généralement un Index Scan qui semble efficace sur le papier, mais le actual time du nœud Limit révèle le vrai coût : PostgreSQL a dû avancer dans l'index sur 100 020 lignes avant de pouvoir en retourner 20. Plus la page demandée est profonde, plus ce coût augmente, sans aucune limite intrinsèque.
Le second coût caché, souvent oublié : si l'interface affiche un nombre total de pages, elle exécute généralement un SELECT COUNT(*) séparé, qui sur une grande table sans filtre sélectif nécessite un balayage complet équivalent en coût au reste de la requête, voire pire.
Diagnostiquer avant de changer quoi que ce soit
Avant de réécrire la pagination, il faut vérifier que c'est réellement OFFSET qui coûte cher, et pas autre chose (absence d'index sur la colonne de tri, par exemple). EXPLAIN (ANALYZE, BUFFERS) sur une requête de page profonde avec le volume réel de production donne la mesure exacte :
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, status
FROM orders
WHERE status = 'shipped'
ORDER BY created_at DESC
LIMIT 20 OFFSET 50000;
Si Buffers: shared hit ou read affiche un nombre élevé de blocs touchés pour ne retourner que 20 lignes, c'est la signature typique du coût d'OFFSET : le moteur a dû traverser une quantité de données largement disproportionnée par rapport au résultat retourné.
La pagination par curseur (keyset pagination)
L'alternative structurelle consiste à remplacer "donne-moi la page numéro N" par "donne-moi les lignes qui suivent ce point précis que j'ai déjà vu". Au lieu d'un offset numérique, le client transmet la valeur de la colonne de tri (et d'un tie-breaker unique) de la dernière ligne vue, et la requête repart de ce point avec une clause WHERE au lieu d'un OFFSET.
-- Première page
SELECT id, created_at, status
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Page suivante : on repart du dernier (created_at, id) vu, pas d'un offset numérique
SELECT id, created_at, status
FROM orders
WHERE (created_at, id) < ('2025-07-20 14:32:11', 48213)
ORDER BY created_at DESC, id DESC
LIMIT 20;
Le tie-breaker (id en plus de created_at) est indispensable : sans lui, deux lignes avec exactement le même created_at produiraient un ordre instable et risqueraient d'être dupliquées ou sautées entre deux pages. La comparaison sur un tuple (created_at, id) garantit un ordre total strict.
Avec un index composite qui couvre exactement cet ordre de tri, la requête devient un simple Index Scan qui part directement au bon endroit dans l'index, sans parcourir ni compter les lignes précédentes :
CREATE INDEX idx_orders_created_at_id ON orders (created_at DESC, id DESC);
Le coût de cette requête ne dépend plus de la profondeur de page : récupérer la page 5 ou la page 50 000 prend le même temps, puisque l'index permet de sauter directement au curseur sans balayer ce qui précède.
Implémenter le curseur côté application
Le changement ne se limite pas au SQL : la couche application doit encoder et décoder le curseur de façon opaque pour le client. Avec Knex par exemple, la requête de page suivante s'écrit directement à partir des valeurs reçues du client, encodées en base64 pour éviter que le client ne les manipule ou ne les interprète comme un simple numéro de page :
function encodeCursor(row) {
return Buffer.from(JSON.stringify({ createdAt: row.created_at, id: row.id })).toString('base64');
}
function decodeCursor(cursor) {
return JSON.parse(Buffer.from(cursor, 'base64').toString('utf8'));
}
async function getOrdersPage(db, { cursor, limit = 20 }) {
let query = db('orders').select('id', 'created_at', 'status').orderBy([
{ column: 'created_at', order: 'desc' },
{ column: 'id', order: 'desc' },
]).limit(limit);
if (cursor) {
const { createdAt, id } = decodeCursor(cursor);
query = query.where((qb) => {
qb.where('created_at', '<', createdAt)
.orWhere((qb2) => qb2.where('created_at', '=', createdAt).andWhere('id', '<', id));
});
}
const rows = await query;
const nextCursor = rows.length === limit ? encodeCursor(rows[rows.length - 1]) : null;
return { rows, nextCursor };
}
La traduction de la comparaison de tuple (created_at, id) < (x, y) en SQL portable (sans la syntaxe de tuple native de PostgreSQL) demande cette décomposition en OR, un peu plus verbeuse mais compatible avec les query builders qui ne génèrent pas de comparaison de tuple directement. Le contrat d'API expose alors un nextCursor opaque plutôt qu'un numéro de page, ce qui change la forme de la réponse côté client et doit être pensé dès la conception du contrat, pas ajouté après coup.
Le compromis à assumer
La pagination par curseur a un coût fonctionnel réel : elle rend impossible le saut direct vers une page arbitraire ("aller à la page 47"), puisqu'il n'existe pas de notion de numéro de page, seulement un point de départ et une direction. Pour une interface qui a réellement besoin de numéros de page cliquables, c'est une contrainte UX à négocier, pas un détail technique à ignorer. Côté API, le contrat change aussi : on expose un curseur opaque (souvent encodé en base64) plutôt qu'un simple numéro de page, ce qui demande d'adapter le client.
Il faut aussi garder la perspective : sur une table de quelques milliers de lignes, ou pour des pages peu profondes (les trois ou quatre premières pages d'une liste), le coût d'OFFSET reste négligeable. Remplacer systématiquement toute pagination par du keyset, même là où ça ne coûte rien, ajoute de la complexité pour un gain nul. Le bon réflexe est hybride : offset simple pour les cas à faible profondeur et faible volume, curseur pour les listes qui grossissent (flux d'activité, exports, API publiques consommées par scroll infini).
Quand la pagination par curseur ne suffit pas
Le curseur résout le coût du parcours, pas le coût d'un COUNT(*) total si l'interface en a toujours besoin pour afficher "sur 3 configurations de 1248 résultats". Dans ce cas, il faut soit accepter un compte approximatif (estimation via les statistiques de la table, pg_class.reltuples, suffisante pour un affichage indicatif), soit retirer purement et simplement le compte exact de l'interface, ce qui est souvent une décision produit, pas seulement technique. Par ailleurs, le curseur suppose une colonne de tri stable : trier dynamiquement sur une colonne calculée à la volée (un score de pertinence recalculé à chaque requête, par exemple) rend le curseur inutilisable en l'état, et demande de matérialiser ou de figer ce score pour que la comparaison reste cohérente entre deux pages.
À retenir
OFFSET coûte linéairement avec la profondeur de page parce que le moteur doit parcourir et jeter toutes les lignes précédentes avant de répondre, un coût invisible sur une base de dev et bien réel en production à grande échelle. La pagination par curseur, avec un index composite aligné sur l'ordre de tri et un tie-breaker unique, élimine ce coût au prix de perdre le saut direct vers une page numérotée : un compromis justifié pour les listes profondes et volumineuses, pas pour toute pagination par principe.
