Paginer des gros volumes sans OFFSET: keyset pagination avec Laravel cursorPaginate
Optimisez la pagination Laravel avec cursorPaginate: keyset sans OFFSET, tri déterministe, index composite et liens next/prev. Tutoriel rapide.
Sommaire
Paginer des gros volumes sans OFFSET: keyset pagination avec Laravel cursorPaginate
Paginer des millions de lignes avec OFFSET/LIMIT finit toujours par coûter cher et par casser l’expérience utilisateur. La solution moderne consiste à utiliser la keyset pagination (pagination par curseur), qui lit “à partir de” un enregistrement pivot stable, au lieu de “sauter” N lignes. Avec Laravel, cursorPaginate simplifie énormément cette approche, à condition d’imposer un ordre strictement déterministe et de prévoir les bons index. Ce tutoriel montre la démarche de bout en bout, du schéma SQL au contrôleur, en passant par les tests et l’intégration front.
Objectif
L’objectif est de remplacer OFFSET/LIMIT par un curseur stable pour lister des données rapidement, sans trous ni doublons, même en cas d’insertions pendant la navigation. Concrètement, nous allons mettre en place cursorPaginate dans Laravel avec un tri déterministe (par exemple created_at DESC, id DESC), un index composite aligné avec ce tri et des URLs next/prev exposées proprement, y compris via l’en-tête HTTP Link pour un usage API.
Pourquoi abandonner OFFSET/LIMIT
Sur de grandes tables, les symptômes classiques d’OFFSET/LIMIT sont une lenteur exponentielle à mesure que la page s’enfonce (par exemple OFFSET 100000), et des anomalies de navigation comme des doublons ou des sauts lorsque de nouveaux enregistrements sont insérés. Une requête telle que SELECT * FROM posts ORDER BY created_at DESC LIMIT 30 OFFSET 200000 oblige le moteur à parcourir et ignorer 200000 lignes avant de renvoyer les 30 suivantes, ce qui devient prohibitif et instable s’il y a des insertions entre deux pages.
Avec la keyset pagination, on ne navigue plus “jusqu’à” un offset arbitraire, mais “à partir de” un enregistrement pivot connu. La page suivante est “tout ce qui est strictement avant X dans l’ordre défini”. Cela requiert un ordre strictement déterministe, ainsi qu’un index couvrant aligné avec cet ordre, mais le gain de performance et de stabilité est massif sur les gros volumes.
Concevoir un ordre déterministe et indexé
Le principe consiste à choisir un couple de colonnes qui impose un ordre total et reproductible. Un choix courant est created_at DESC puis id DESC, où created_at est monotonique et id sert de tie-breaker unique. Cette combinaison évite les égalités non résolues et supprime le non-déterminisme. Il est impératif d’éviter les valeurs NULL dans les colonnes d’ordre, car elles déplacent les lignes hors de l’ordre attendu et compliquent la comparaison.
Pour accélérer la lecture séquentielle, on crée un index composite aligné avec l’ordre. En pratique, MySQL 8 sait définir des index DESC, mais même un index ASC peut être utilisé pour un parcours descendant si toutes les colonnes partent dans le même sens. PostgreSQL gère aussi les index DESC et peut utiliser l’index pour les lectures inversées.
Exemple de migration Laravel pour un index composite générique sur posts(created_at, id). Si vous ciblez MySQL < 8 ou voulez rester simple, un index ASC fonctionne déjà bien pour created_at/id:
// database/migrations/2024_01_01_000000_add_index_on_posts_created_at_id.php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration {
public function up(): void
{
Schema::table('posts', function (Blueprint $table) {
// Index composite aligné avec l’ordre (ASC ici, utilisable pour DESC dans la plupart des cas)
$table->index(['created_at', 'id'], 'idx_posts_created_at_id');
});
// Si created_at peut être NULL, normalisez la donnée
// DB::table('posts')->whereNull('created_at')->update(['created_at' => now()]);
}
public function down(): void
{
Schema::table('posts', function (Blueprint $table) {
$table->dropIndex('idx_posts_created_at_id');
});
}
};Si vous souhaitez des index en ordre explicite DESC:
- MySQL 8:
use Illuminate\Support\Facades\DB;
DB::statement('CREATE INDEX idx_posts_created_at_id_desc ON posts (created_at DESC, id DESC)');- PostgreSQL:
use Illuminate\Support\Facades\DB;
DB::statement('CREATE INDEX idx_posts_created_at_id_desc ON posts (created_at DESC, id DESC)');Enfin, si votre modèle peut contenir des NULL dans created_at, corrigez les enregistrements existants et empêchez les futurs NULL via une contrainte ou une règle applicative, afin d’assurer un ordre strict.
Implémenter la requête Eloquent avec cursorPaginate
Commencez par définir un scope garantissant le tri déterministe. Cela centralise la règle d’ordre et évite les oublis de tie-breaker:
// app/Models/Post.php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Post extends Model
{
// ...
public function scopeOrdered($query, string $dir = 'desc')
{
$dir = strtolower($dir) === 'asc' ? 'asc' : 'desc';
return $query
->orderBy('created_at', $dir)
->orderBy('id', $dir); // tie-breaker unique
}
}Dans votre contrôleur, combinez les filtres, appliquez le scope, et utilisez cursorPaginate. Le curseur est véhiculé dans la requête via le paramètre cursor. Laravel le gère automatiquement, mais vous pouvez l’extraire et le passer explicitement si besoin:
// app/Http/Controllers/PostController.php
namespace App\Http\Controllers;
use App\Http\Resources\PostResource;
use App\Models\Post;
use Illuminate\Http\Request;
class PostController extends Controller
{
public function index(Request $request)
{
$perPage = (int) $request->integer('per_page', 30);
$cursor = $request->input('cursor'); // peut être null
$query = Post::query()
->when($request->filled('user_id'), fn ($q) => $q->where('user_id', $request->user_id))
->when($request->filled('type'), fn ($q) => $q->where('type', $request->type))
->ordered('desc');
$paginator = $query->cursorPaginate(
perPage: $perPage,
columns: ['*'],
cursorName: 'cursor',
cursor: $cursor
);
// Retourne une Resource Collection avec meta curseur
return PostResource::collection($paginator)
->additional([
'meta' => [
'next_cursor' => optional($paginator->nextCursor())->encode(),
'prev_cursor' => optional($paginator->previousCursor())->encode(),
'next_url' => $paginator->nextPageUrl(),
'prev_url' => $paginator->previousPageUrl(),
'per_page' => $perPage,
],
]);
}
}La Resource formatte chaque élément sans toucher à l’ordre:
// app/Http/Resources/PostResource.php
namespace App\Http\Resources;
use Illuminate\Http\Resources\Json\JsonResource;
class PostResource extends JsonResource
{
public function toArray($request): array
{
return [
'id' => $this->id,
'title' => $this->title,
'created_at' => $this->created_at?->toIso8601String(),
'author_id' => $this->user_id,
'type' => $this->type,
];
}
}Exposer des URLs next/prev et en-têtes Link
Dans le cas d’une API, exposez à la fois les URLs dans le JSON et via l’en-tête Link conforme à la RFC 5988. Vous pouvez construire l’en-tête dans le contrôleur au moment de répondre:
// app/Http/Controllers/PostController.php (extrait)
$response = PostResource::collection($paginator)
->additional([
'meta' => [
'next_cursor' => optional($paginator->nextCursor())->encode(),
'prev_cursor' => optional($paginator->previousCursor())->encode(),
'next_url' => $paginator->nextPageUrl(),
'prev_url' => $paginator->previousPageUrl(),
],
])->response();
$linkParts = [];
if ($next = $paginator->nextPageUrl()) {
$linkParts[] = "<{$next}>; rel=\"next\"";
}
if ($prev = $paginator->previousPageUrl()) {
$linkParts[] = "<{$prev}>; rel=\"prev\"";
}
if (! empty($linkParts)) {
$response->header('Link', implode(', ', $linkParts));
}
return $response;Côté client, remplacez la navigation par numéro de page par un bouton “Charger plus” qui ajoute les éléments en fin de liste. Ce modèle correspond parfaitement au paradigme du curseur et évite les incohérences de pages fixes.
Filtres sans casser l’index
Les filtres doivent préserver la sélectivité et l’utilisation de l’index composite. Placez vos clauses WHERE sur des colonnes situées avant l’ordre ou compatibles avec un index prefix. Par exemple, un filtre where(user_id = ?) suivi de whereBetween('created_at', ...) fonctionne correctement avant le orderBy(created_at, id), car le moteur peut restreindre le domaine puis parcourir l’index dans le bon ordre.
Voici un exemple Eloquent qui reste compatible avec un index prefix (user_id, created_at, id) si vous le créez:
$posts = Post::query()
->where('user_id', $request->user_id)
->whereBetween('created_at', [$request->from, $request->to])
->ordered()
->cursorPaginate(30);Évitez les filtres conduisant à un filesort, comme where('title', 'LIKE', '%foo') sur une colonne non indexée avec un wildcard en tête. Si vous devez filtrer sur une faible cardinalité ou un LIKE non sargable, faites une pré-sélection en sous-requête des IDs correspondants, puis appliquez un keyset sur ces IDs. En pratique, ça peut ressembler à “je liste d’abord les IDs éligibles dans un set restreint, puis je pagine de façon déterministe sur id/created_at”.
Pour valider vos hypothèses, exécutez EXPLAIN et vérifiez qu’un index range scan est utilisé et qu’il n’y a pas de Using filesort:
EXPLAIN SELECT id, created_at
FROM posts
WHERE user_id = 42
AND created_at BETWEEN '2025-01-01' AND '2025-12-31'
ORDER BY created_at DESC, id DESC
LIMIT 30;Dans Laravel/Tinker, vous pouvez inspecter la requête générée et l’EXPLAIN:
DB::enableQueryLog();
$posts = Post::where('user_id', 42)
->whereBetween('created_at', ['2025-01-01', '2025-12-31'])
->ordered()
->cursorPaginate(30);
dd(DB::getQueryLog());Joins et collections volumineuses
Lorsque vous devez enrichir les résultats avec des relations ou des agrégats lourds, séparez la pagination des clés et l’enrichissement.
Approche recommandée: paginer d’abord les clés, puis faire un JOIN ciblé. Par exemple, récupérez une page d’IDs ordonnés et chargez les données complètes ensuite en conservant l’ordre:
// 1) Paginer les clés (id) dans l’ordre déterministe
$idsPage = Post::select('id', 'created_at')
->when($request->filled('type'), fn ($q) => $q->where('type', $request->type))
->ordered()
->cursorPaginate(30);
$idList = collect($idsPage->items())->pluck('id')->all();
// 2) Récupérer les enregistrements complets + relations sans perdre l’ordre
$posts = Post::with(['author', 'tags'])
->whereIn('id', $idList)
->orderByRaw('FIELD(id, ' . implode(',', $idList) . ')') // MySQL/MariaDB
->get();
// Pour PostgreSQL, utilisez un ORDER BY CASE plutôt que FIELD():
// ->orderByRaw('CASE id ' . collect($idList)->map(fn($id, $i) => "WHEN {$id} THEN {$i}")->implode(' ') . ' END')Si la pagination concerne une relation N-N via une table pivot, paginez directement sur la table pivot avec un keyset dédié, par exemple post_user(created_at, id), puis joignez les tables principales pour l’enrichissement. L’important est que la table de pagination possède son propre ordre déterministe et index.
Évitez DISTINCT et GROUP BY dans la requête paginée, car ils forcent souvent un tri externe. Si vous avez besoin d’agréger, déplacez l’agrégation dans une CTE ou une sous-requête qui produit un set d’IDs, puis paginez sur ce set. En SQL PostgreSQL, cela peut prendre la forme d’un WITH agrégé puis d’un SELECT ordonné appliqué à la sortie; en Laravel, utilisez fromSub pour encapsuler la sous-requête avant d’appliquer cursorPaginate.
Enfin, pour l’eager loading, chargez les relations après la sélection paginée (->load()) ou dans une seconde requête basée sur les IDs; cela évite d’altérer l’ordre et de déclencher des stratégies de tri imprévues.
Trait réutilisable pour un ordre stable
Centraliser l’ordre déterministe dans un trait évite les divergences entre modèles.
// app/Models/Concerns/HasDeterministicOrder.php
namespace App\Models\Concerns;
use Illuminate\Database\Eloquent\Builder;
trait HasDeterministicOrder
{
public function scopeOrdered(Builder $query, string $dir = 'desc'): Builder
{
$dir = strtolower($dir) === 'asc' ? 'asc' : 'desc';
foreach ($this->orderColumns() as $col) {
$query->orderBy($col, $dir);
}
// S’assurer qu’un tie-breaker unique est inclus (ex: 'id')
if (! in_array('id', $this->orderColumns(), true)) {
$query->orderBy('id', $dir);
}
return $query;
}
protected function orderColumns(): array
{
// Personnalisez par modèle via override si besoin.
return ['created_at', 'id'];
}
}Utilisez ce trait sur tous les modèles exposés en liste publique (posts, events, invoices, etc.). Vous garantissez ainsi un ordre stable et cohérent dans toute l’application.
Tests: pas de doublons, pas de trous
Pour valider la robustesse, mettez en place une campagne de tests qui simule des données volumineuses et des insertions concurrentes. Commencez par un seeder qui injecte un grand volume avec des timestamps proches pour intensifier les égalités.
// database/seeders/HugePostsSeeder.php
namespace Database\Seeders;
use App\Models\Post;
use Illuminate\Database\Seeder;
use Illuminate\Support\Carbon;
class HugePostsSeeder extends Seeder
{
public function run(): void
{
$now = Carbon::now();
// 200k lignes en chunks pour éviter d’exploser la mémoire
$total = 200_000;
$chunk = 2_000;
for ($i = 0; $i < $total; $i += $chunk) {
$batch = [];
for ($j = 0; $j < $chunk; $j++) {
$batch[] = [
'title' => 'Post ' . ($i + $j + 1),
'user_id' => random_int(1, 1000),
'type' => ['news', 'blog', 'note'][array_rand(['a','b','c'])],
'created_at' => $now->copy()->subSeconds(random_int(0, 86400)),
'updated_at' => $now,
];
}
Post::insert($batch);
}
}
}Écrivez ensuite des tests automatiques. Un premier test concatène plusieurs pages consécutives et vérifie l’unicité des IDs pour détecter tout doublon. Un second compare le tri à une requête ORDER BY/limit sur un petit échantillon, afin de s’assurer que la logique est identique. Un troisième mesure la performance (temps et EXPLAIN) pour s’assurer de l’absence de filesort et de l’utilisation d’un index range scan.
Exemple de tests avec Pest:
// tests/Feature/PostCursorPaginationTest.php
use App\Models\Post;
use Illuminate\Pagination\CursorPaginator;
use Illuminate\Support\Facades\DB;
it('concatène 3 pages sans doublons ni trous', function () {
$page1 = Post::ordered()->cursorPaginate(30);
$page2 = Post::ordered()->cursorPaginate(30, ['*'], 'cursor', optional($page1->nextCursor())->encode());
$page3 = Post::ordered()->cursorPaginate(30, ['*'], 'cursor', optional($page2->nextCursor())->encode());
$ids = collect([$page1, $page2, $page3])
->flatMap(fn (CursorPaginator $p) => collect($p->items())->pluck('id'));
expect($ids->count())->toBe($ids->unique()->count());
});
it('respecte le même ordre que ORDER BY sur un petit échantillon', function () {
$expected = Post::orderBy('created_at', 'desc')->orderBy('id', 'desc')->limit(30)->pluck('id')->toArray();
$page = Post::ordered()->cursorPaginate(30);
$actual = collect($page->items())->pluck('id')->toArray();
expect($actual)->toBe($expected);
});
it('utilise un index range scan sans filesort', function () {
$sql = "EXPLAIN SELECT id, created_at FROM posts ORDER BY created_at DESC, id DESC LIMIT 30";
$plan = DB::select($sql);
$planText = json_encode($plan);
expect($planText)->not->toContain('Using filesort');
});Pour simuler des insertions pendant la navigation, récupérez la première page, insérez quelques enregistrements très “récents”, puis demandez la page suivante et vérifiez qu’elle ne réintroduit pas des IDs déjà vus ni ne “saute” d’items par rapport au flux logique du curseur.
Intégration front: “Charger plus” robuste
L’UX recommandée est un bouton “Charger plus” qui ajoute les éléments en fin de liste. Stockez le nextCursor renvoyé par l’API dans un data-attribute, et désactivez le bouton quand il n’y a plus de page suivante. Si vous offrez une navigation arrière, utilisez previousCursor. Réinitialisez le curseur lors d’un changement de filtres pour repartir de la première page et videz la liste pour éviter de mêler des datasets différents.
Exemple minimaliste en JavaScript:
const listEl = document.querySelector('#posts');
const btnEl = document.querySelector('#loadMore');
let nextCursor = null;
let prevCursor = null;
const perPage = 30;
async function fetchPage(cursor = null) {
const url = new URL('/api/posts', window.location.origin);
url.searchParams.set('per_page', perPage);
if (cursor) url.searchParams.set('cursor', cursor);
const res = await fetch(url.toString(), { headers: { Accept: 'application/json' } });
if (!res.ok) throw new Error('HTTP ' + res.status);
const json = await res.json();
json.data.forEach(post => {
const li = document.createElement('li');
li.textContent = `#${post.id} • ${post.title} • ${post.created_at}`;
listEl.appendChild(li);
});
nextCursor = json.meta?.next_cursor ?? null;
prevCursor = json.meta?.prev_cursor ?? null;
btnEl.disabled = nextCursor === null;
}
btnEl.addEventListener('click', () => {
if (nextCursor) fetchPage(nextCursor).catch(console.error);
});
// Prefetch de la première page au chargement
fetchPage().catch(console.error);Pour un scroll fluide, déclenchez un prefetch de la page suivante juste après le rendu, par exemple en utilisant IntersectionObserver pour anticiper quand l’utilisateur s’approche du bas de la liste.
Pièges courants et check-list
Le piège le plus fréquent est l’absence de tie-breaker unique. Si votre tri ne garantit pas l’unicité (par exemple created_at seul), vous obtiendrez un ordre non déterministe et un risque de doublons ou de trous. Assurez-vous d’avoir toujours une colonne unique en dernier critère, comme id. Évitez également d’empiler des orderBy arbitraires qui ne correspondent pas aux colonnes indexées: respectez exactement l’ordre pour lequel l’index composite a été conçu. Les clauses DISTINCT, GROUP BY ou UNION dans la requête paginée doivent être bannies; si elles sont nécessaires, préparez d’abord un set d’IDs dans une sous-requête ou une CTE, puis appliquez la keyset pagination sur ce set. Pensez aux timezones: normalisez created_at en UTC pour éviter qu’un tri basé sur des fuseaux différents ne fasse “rebondir” les enregistrements. Enfin, sur PostgreSQL, si une colonne d’ordre peut être NULL, précisez explicitement NULLS LAST ou NULLS FIRST selon votre besoin, ou mieux, supprimez la possibilité de NULL pour cette colonne en amont.
Checklist
Avant de déployer, relisez l’ordre et les index pour confirmer le tie-breaker unique, exécutez et testez tous les snippets (migrations, contrôleur, Resource, JS “Charger plus”), validez via EXPLAIN l’absence de filesort et la présence d’un index range scan, et publiez lorsque la navigation ne présente ni doublons ni trous sur plusieurs pages consécutives, y compris en cas d’insertions concurrentes.
Conclusion
La keyset pagination via cursorPaginate apporte une réponse simple et robuste aux limites d’OFFSET/LIMIT sur les gros volumes. En imposant un ordre strictement déterministe et en créant un index composite aligné, vous obtenez des listes rapides, stables et résistantes aux insertions. L’exposition des liens next/prev et l’UX “Charger plus” s’intègrent naturellement, tandis que les tests et l’analyse EXPLAIN vous garantissent des performances pérennes. Adoptez ce pattern partout où vous affichez de longues listes, et vous éviterez une classe entière de problèmes de scalabilité.
Ressources
La documentation Laravel sur la pagination par curseur explique l’API côté framework et les méthodes disponibles (nextCursor, previousCursor, nextPageUrl). https://laravel.com/docs/pagination#cursor-pagination
Une présentation claire de la “Keyset Pagination” et des implications sur les index se trouve sur Use The Index, Luke! https://use-the-index-luke.com/no-offset
La documentation MySQL 8 couvre les index en ordre descendant et leur impact. https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html
La documentation PostgreSQL détaille les index et l’ordre des valeurs NULL avec NULLS FIRST/LAST. https://www.postgresql.org/docs/current/queries-order.html
La RFC 5988 définit l’en-tête HTTP Link pour exposer les relations de pagination dans les API. https://www.rfc-editor.org/rfc/rfc5988