Pourquoi il faut éviter d'utiliser LIMIT OFFSET pour la pagination dans PostgreSQL (et quelle alternative utiliser à la place)

Lors du développement d'une interface web, il est courant d'avoir à gérer de grands volumes de données. L'une des méthodes permettant d'optimiser la récupération des données consiste à utiliser la pagination, c'est-à-dire à charger les enregistrements de manière incrémentielle, par exemple à mesure que l'utilisateur fait défiler la page vers le bas. À chaque requête, un nombre fixe d'éléments est récupéré (limit), à partir d'une position spécifique dans la liste (offset).

 non définiSELECT
    *
FROM
    <table>
OFFSET
    <Y>
LIMIT
    <X>;

La solution la plus simple, et la plus couramment utilisée, consiste à ajouter des clauses LIMIT et OFFSET à vos requêtes SQL. Cependant, cette approche devient rapidement problématique à mesure que les volumes de données augmentent. Dans cet article, je vais vous expliquer pourquoi cette méthode n’est pas optimale sous PostgreSQL, et comment la remplacer par une stratégie de pagination plus efficace basée sur les index.

Le problème avec LIMIT OFFSET

Utiliser LIMIT 10 OFFSET 1000 pour récupérer la page 101 peut sembler anodin, mais PostgreSQL effectue en coulisses bien plus d’opérations que vous ne le pensez :

• Balayage inutile: PostgreSQL doit lire et trier les 1 010 premières lignes, écarter les 1 000 premières, puis renvoyer les 10 lignes demandées. Cela représente une charge importante en termes de lectures sur disque et d’utilisation du processeur.
• Latence accrue: plus l’offset est élevé, plus la requête est lente. À mesure que la pagination progresse, les temps de réponse se dégradent considérablement.
• Résultats incohérents: si les données changent pendant que l’utilisateur navigue entre les pages (insertions, suppressions), certains enregistrements peuvent être dupliqués ou ignorés. (Par exemple : je suis à la page 10, un enregistrement de la page 1 est supprimé ; ensuite, lorsque je passe à la page 11, le premier enregistrement de la page 11, qui s’est désormais déplacé à la fin de la page 10, n’apparaît jamais).

Pagination par jeu de clés : la meilleure alternative

Au lieu d’utiliser un décalage arbitraire, nous pouvons paginer à l’aide d’une valeur connue issue d’un index, généralement un identifiant incrémenté automatiquement ou un horodatage (created_at). C’est ce qu’on appelle communément la pagination par jeu de clés.

Exemple

Pagination basée sur l'OFFSET (non recommandée)

 non définiSELECT
    *
FROM
    users
WHERE
    id > 12345
ORDER BY
    id
LIMIT
    10;

Pagination par ensemble de clés (recommandée)

 désactivéSELECT
    *
FROM
    users
WHERE
    id > 12345
ORDER BY
    id
LIMIT
    10;

L'idée est d'utiliser la dernière valeur de la page précédente (par exemple, id = 12345) pour charger la suivante. Cette requête :

• Exploite pleinement l'index sur id
• Ignore les lignes précédentes sans les analyser
• est nettement plus rapide et plus évolutive

Tests de performance

Pour être plus concret, prenons l'exemple d'une table contenant environ 2 millions de lignes. Nous allons essayer de récupérer les lignes comprises entre 1 000 000 et 1 000 010.

Requête basée sur OFFSET (non recommandée)

 non définiSELECT
    *
FROM
    "client_data"."ean"
ORDER BY
    id
OFFSET
    1000000
LIMIT
    10;

Analyse du plan d'exécution :

Le plan d'exécution indique un balayage d'index sur ean_pkey, mais sans aucune condition de filtrage. PostgreSQL lit l'intégralité de la table, trie toutes les lignes, ignore les 1 000 000 premières et renvoie les 10 lignes suivantes.

➡️ Cela entraîne un coût total très élevé (~548k) et rend la navigation en profondeur extrêmement inefficace.

Requête basée sur un ensemble de clés (recommandée)

sans ensemble declés SELECT
    *
FROM
    "client_data"."ean"
WHERE
    id > '2609901045790'
ORDER BY
    id
LIMIT 10;

Analyse du plan d'exécution :

Grâce à la condition id > ..., PostgreSQL déclenche un balayage d'index sur ean_pkey. L'exécution commence immédiatement après la clé spécifiée et ne lit que les 10 lignes pertinentes. Le plan affiche un coût minimal (0,56 à 7,40), ce qui indique une utilisation efficace de l’index. Il n’est pas nécessaire de parcourir l’intégralité de l’index, ce qui rend la requête très performante et stable, même à grande échelle.

Avantages

• ✅ Performances constantes, quelle que soit la profondeur des pages
• ✅ Utilisation optimale des index
• ✅ Résultats stables, même en cas de modifications simultanées
• ✅ Meilleure expérience utilisateur, en particulier sur mobile ou avec le défilement infini

Limites

Cette méthode présente quelques contraintes :

• Nécessite une colonne à la fois triée et unique (généralement « id »)
• Nécessite un peu plus de logique pour la pagination vers l'arrière (par exemple, inverser l'ordre de tri et utiliser le premier ID comme référence), mais cela reste simple à mettre en œuvre.
• Pas de suivi intégré du numéro de page actuel

Cependant, dans la plupart des cas, ces compromis sont mineurs par rapport aux gains en termes de performances et de fiabilité.

Mise en œuvre concrète : EagleAI

Pour illustrer un cas d'utilisation concret, voici comment nous avons mis en œuvre la pagination par ensemble de clés chez EagleAI, en utilisant Python FastAPI pour le backend et Next.js pour le frontend.

Le backend renvoie :

• L'index de la liste (utilisé pour accéder à la page précédente)
• Le dernier index (pour passer à la page suivante)
• Le numéro de la page actuelle (que nous pouvions auparavant calculer à l'aide de offset + limit)
• Une valeur booléenne indiquant si nous sommes sur la dernière page (pour l'affichage côté client)

Pour changer de page, l'interface utilisateur renvoie l'ID de référence (last_seen_id) (soit le premier, soit le dernier élément de la liste, selon la direction) au backend, accompagné de la direction (« next » ou « prev »). Voici la logique du backend :

 Python
def get_page(
    db_session: Session,
    limit: int,
    last_seen_id: str | None = None,
    direction: Literal["next", "prev"] = "next",
):
    """Récupérer une page de produits."""
    is_next = direction == "next"
    order = order_by.asc() si is_next sinon order_by.desc()

    query = select(Product)

    si last_seen_id :
        comparator = Product.id > last_seen_id si is_next sinon
Product.id < last_seen_id
       query = query.where(comparator)

   query = query.order_by(order).limit(limit)
    products = db_session.execute(query).scalars().all()

    return products if is_next else list(reversed(products))

Conclusion

Si votre base de données contient plus de quelques milliers de lignes, ou si vous souhaitez offrir une expérience utilisateur rapide et fluide, il est temps d’abandonner LIMIT OFFSET.

Passer à la pagination basée sur les index est simple, et c’est un changement qui s’avère payant à long terme.