La pagination par décalage vous ment après la page cinquante

L'affirmation

LIMIT 20 OFFSET 5000 a deux défauts, et le plus connu est le moindre. Oui, la requête ralentit linéairement, puisque la base doit produire puis jeter cinq mille lignes pour vous en remettre vingt. Le pire défaut, c'est que sous des écritures concurrentes elle saute et duplique des lignes en silence : un élément inséré pendant qu'une personne navigue décale d'un cran toutes les pages suivantes, si bien que la ligne 41 apparaît sur deux pages et la ligne 60 sur aucune. Pour tout ce qu'un utilisateur fait défiler, qu'une exportation parcourt ou qu'un client d'API synchronise, la pagination par jeu de clés corrige les deux défauts d'un coup.

Démontrez le défaut de performance sur vos propres données

EXPLAIN ANALYZE SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100;
EXPLAIN ANALYZE SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 200000;

Sur une table d'un million de lignes indexée sur id, la première requête revient bien sous la milliseconde et la seconde prend des dizaines de millisecondes — le plan montre l'index parcouru sur 200 020 entrées pour en jeter 200 000. Le coût croît avec le décalage, indéfiniment. Les pages profondes sont d'ailleurs exactement ce que demandent les robots et les clients d'API mal élevés : vos requêtes les plus lentes proviennent donc de votre trafic le moins précieux.

Démontrez le défaut d'exactitude

Ouvrez deux sessions. Dans la première, lisez la page un, du plus récent au plus ancien :

SELECT id FROM orders ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 0;

Dans la seconde, insérez une commande. De retour dans la première, lisez la page deux avec OFFSET 20. La ligne qui était 20e en page un est désormais 21e au total — et réapparaît en tête de page deux. Supprimez plutôt une ligne, et un enregistrement disparaît entièrement de la séquence. Aucune erreur n'est levée dans un cas comme dans l'autre. Une exportation bâtie ainsi compte en double et en moins, et une équipe de finances finira par trouver l'écart avant vous.

La pagination par jeu de clés

Au lieu de compter des lignes à sauter, retenez où vous vous êtes arrêté et demandez les lignes au-delà de ce point :

-- page 1
SELECT id, created_at, total
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- pages suivantes : renvoyez les valeurs de la derniere ligne
SELECT id, created_at, total
FROM orders
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

La comparaison de lignes (created_at, id) < ($1, $2) est la forme idiomatique, et Postgres exploite un index composite sur (created_at DESC, id DESC) pour sauter directement à la position. Chaque page coûte le prix de la page un, à n'importe quelle profondeur, et les insertions ou suppressions ne peuvent pas déplacer la fenêtre : le curseur est ancré à des valeurs, pas à un compte.

Deux règles préservent l'exactitude. La clé de tri doit être unique, d'où l'ajout de id comme départage — created_at seul sautera des lignes partageant le même horodatage. Et le curseur remis aux clients devrait être opaque — les valeurs de clé en base64 — pour que personne ne se construise sur ses entrailles.

Les compromis, franchement

• Pas de saut à la page 37. Le jeu de clés donne suivant et précédent, pas l'accès aléatoire. Pour le défilement infini, les exportations et la synchronisation d'API, cela ne coûte rien. Pour une vraie interface de saut de page, il faut le décalage — mais consultez d'abord vos statistiques : dans chaque produit que nous avons mesuré, la navigation directe vers une page profonde est une erreur d'arrondi, et « suivant » plus la recherche couvrent ce que les gens font réellement.
• Le compte total est une décision distincte. Un COUNT(*) sur un grand ensemble filtré peut coûter plus cher que la page elle-même. Affichez « suivant » jusqu'à une page vide, ou montrez une estimation tirée de pg_class.reltuples quand l'approximatif suffit.
• Les tris multicolonnes exigent la clé complète dans le curseur. Trier par statut puis par date signifie que le curseur transporte les deux, plus l'identifiant. Mécanique, mais obligatoire.

La place de chacune

Le décalage convient à une petite table d'administration de quelques centaines de lignes, où les numéros de page sont réellement utiles et les écritures rares. Tout le reste — listes destinées à la clientèle, défilement infini, exportations CSV, rattrapages de rappels HTTP, toute API qu'un partenaire parcourra — passe au jeu de clés. La migration est contenue : une forme de requête et un paramètre de curseur, et la latence des pages profondes part en même temps que les mystères de rapprochement.