Pourquoi une pagination avec LIMIT et OFFSET peut être une mauvaise idée ?

La plupart du temps, les requêtes avec LIMIT et OFFSET sont utilisés couramment dans nos applications nécessitant une telle pagination et fonctionnent souvent très bien.

Sans logique apparente, sur une quantité de données importantes, ces requêtes peuvent mettre un certain temps à répondre plus vous avez de données. C'est ce que nous avons appris dernièrement.

Cas d'usage

Notre client possède une table de plus de 4 millions de produits dans une base PostgreSQL nécessitant une indexation dans Elasticsearch.

A une fréquence régulière, l'index Elasticsearch est recréé et l'ensemble des produits doivent être requêtés.

Pour cela, la base de données est sollicitée par lot pour maitriser l'empreinte mémoire.

Nous avons testé des valeurs d'OFFSET avec la requête suivante, pour mettre en évidence la détérioration des performances à mesure que l'OFFSET augmente.

    SELECT * FROM product OFFSET XXX LIMIT 1000;

Ce qui nous donne les résultats bruts suivant.

Et en image c'est très parlant...

Mais pourquoi OFFSET est-il lent ?

En fait, si une requête utilise la clause limit suivante : "LIMIT 50000, 100", elle demande à la base de données de parcourir 50 100 lignes et de supprimer les 50 000 premières.

Cette action a donc un impact sur les temps de réponse.

Comment optimiser vos requêtes paginées ?

Pour optimiser les requêtes avec la clause OFFSET, vous pouvez soit limiter le nombre de pages autorisées dans une vue de pagination, soit simplement ne pas utiliser OFFSET.

Une bonne alternative est la méthode Seek.

En termes simples, la méthode de recherche consiste à trouver une colonne ou un ensemble de colonnes unique qui identifie chaque ligne.

Ensuite, au lieu d'utiliser la clause OFFSET, vous pouvez simplement utiliser cette valeur unique comme signet qui représente alors la position de la dernière ligne. En le récupérant, il devient possible d'interroger l'ensemble des lignes suivantes en partant de cette position dans la clause WHERE.

Important:

• vous devez vous assurer de trier par colonnes uniques afin que l'ordre reste toujours le même entre les pages, sinon vous pourriez obtenir un comportement inattendu.

• Il doit y avoir un index sur la colonne / les colonnes de recherche.

Comparaison des performances entre les deux méthodes

    SELECT *
    FROM product 
    WHERE id > XXX
    ORDER BY id
    LIMIT 1000;

    XXX étant le dernier id récupéré via la requête précédente.

Ce qui est intéressant d'observer ici, ce n'est pas uniquement les meilleures performances de la méthode Seek, mais qu'elle est également plus stable, quelle que soit la distance à laquelle vous effectuez la pagination.

Conclusion

Pour l'utilisation d'une pagination de gros volumes (mais vraiment très gros volumes...) en batch processing avec un comportement prédictif, nous recommandons donc d'utiliser la méthode Seek plutôt que la clause OFFSET. C'est ce qui nous a permis d'optimiser drastiquement la performance sur notre cas d'usage.

Par contre, pour une pagination dans un admin type sonata ou easyadmin avec un comportement non prédictif, il est toujours nécessaire et suffisant d'utiliser OFFSET.