Limiter la taille des résultats

Ce que vous saurez faire

Faites retourner à la liste de billets de soutien une seule page de taille limitée, avec une seule requête. Gardez la même vitesse à la page 1 et à la page 1 000. Prouvez le gain avec les mêmes données et la même charge de travail qu’avant.

Avant de commencerCe qu’il faut savoir et avoir sous la mainEnviron 55 minutes

Ce que vous devez savoir

• Lire un plan et connaître les trois index de la migration 008, vus dans Lire un plan de requête et Indexer de façon réfléchie.
• Connaître le code de la liste de billets dans ticket_api/repository.py : list_query avec OFFSET (décalage), et deux requêtes supplémentaires par billet, vus dans Requêtes sûres.
• Connaître les jointures et les sous-requêtes dans FROM, vues dans Joindre des tables et Des requêtes lisibles, dans le cours SQL.

Ce qu’il vous faut sur votre ordinateur

• Le projet après la dernière leçon : migration 008 appliquée et bench-before.json enregistré. python -m pytest affiche 79 passed (79 tests réussis).
• PostgreSQL en cours d’exécution, avec le grand jeu de données chargé.

Cette leçon modifie quatre fichiers de ticket_api/ et le contrat de l’API, et ajoute deux fichiers de tests. À la fin, votre projet correspond à l’instantané end-of-m04, et python -m pytest affiche 87 passed (87 tests réussis).

Une liste d’achats et un signet

Vous avez besoin de 41 articles du magasin. Vous pourriez aller au magasin à pied, acheter un article, rentrer chez vous et recommencer 41 fois. Chaque trajet est court, mais les déplacements s’accumulent. Ou vous écrivez une liste et faites un seul trajet.

Vous lisez un long livre et vous vous êtes arrêté à la page 1 001. Demain, vous pourriez ouvrir le livre au début et compter 1 000 pages. Ou vous l’ouvrez à votre signet. Compter prend plus de temps avec chaque page lue; le signet prend le même temps à chaque page.

La liste de billets fait le premier choix dans les deux cas, et transporte aussi plus que nécessaire.

Le problème : 41 trajets, une page qui compte et toutes les colonnes

Après 008, la liste de billets prend 13,3 ms par page. C’est rapide, mais le code a encore trois problèmes.

La requête N+1. Le code obtient une page de 20 billets avec une seule requête. Ensuite, pour chaque billet, il envoie deux autres requêtes : le nom du client et la dernière exécution de l’IA. Cela fait 1 + 20 + 20 = 41 requêtes, soit 41 allers-retours à la base de données. On appelle cela la requête N+1 : une requête pour la liste, puis N autres, une par ligne. C’est l’erreur de performance la plus courante dans le code qui lit des lignes liées dans une boucle.

OFFSET. Pour afficher la page 2, le code demande LIMIT 20 OFFSET 20 : sauter 20 lignes, puis en retourner 20. PostgreSQL ne peut pas passer directement à la ligne 21. Il doit trouver les 20 premières lignes, les sauter, puis retourner les 20 suivantes. Pour une page éloignée du début, c’est lent. Le cours a mesuré la page 1 001 de la liste des billets d’expédition fermés (OFFSET 20000), avec l’index. Le plan a lu 20 020 lignes pour en retourner 20, en 4,4 ms. Le point de terminaison a pris 21,5 ms.

OFFSET donne aussi des pages incorrectes quand des billets arrivent. Essayez-le dans psql avec le grand jeu de données. La transaction fait arriver un nouveau billet entre deux pages, puis annule tout :

BEGIN;
SELECT ticket_id, created_at FROM tickets WHERE status = 'open' AND team = 'billing'
ORDER BY created_at DESC, ticket_id DESC LIMIT 3 OFFSET 0;
INSERT INTO tickets (ticket_id, customer_id, subject, body, channel, team, priority)
VALUES ('T-400001', 'C-0001', 'Charged twice', 'A new ticket arrives.', 'email', 'billing', 2);
SELECT ticket_id, created_at FROM tickets WHERE status = 'open' AND team = 'billing'
ORDER BY created_at DESC, ticket_id DESC LIMIT 3 OFFSET 3;
ROLLBACK;

 ticket_id |       created_at
-----------+------------------------
 T-399991  | 2026-10-08 20:44:09+00
 T-399988  | 2026-10-08 20:39:23+00
 T-399979  | 2026-10-08 20:23:56+00
(3 rows)

INSERT 0 1
 ticket_id |       created_at
-----------+------------------------
 T-399979  | 2026-10-08 20:23:56+00
 T-399955  | 2026-10-08 19:50:11+00
 T-399954  | 2026-10-08 19:50:09+00
(3 rows)

T-399979 figure sur les deux pages. Le nouveau billet est arrivé en haut et a décalé chaque ligne d’une position vers le bas. Donc, « sauter 3 lignes » saute maintenant une ligne de moins qu’avant. Un agent qui parcourt la liste voit un billet deux fois. Quand un billet ferme, l’agent en manque plutôt un.

Toutes les colonnes. La requête de la liste utilise SELECT *. Cela inclut body, le texte complet de chaque billet, que la liste n’affiche jamais. Le plan indique width=201 : environ 201 octets par ligne. Migrations a montré un deuxième coût : après l’ajout d’une colonne par une migration, un SELECT * préparé peut échouer.

Les idées : une requête, une pagination par clé, des colonnes nommées

Une requête avec des jointures. Demandez tout ce que la page affiche dans une seule requête. Une jointure fournit le nom du client. La dernière exécution de l’IA est plus difficile : chaque billet nécessite son exécution la plus récente, et une seule. LEFT JOIN LATERAL le permet. LATERAL permet à une sous-requête dans FROM d’utiliser les colonnes de la ligne qui la précède. Elle s’exécute donc une fois par billet, avec son ticket_id. LEFT conserve les billets sans exécution, avec des colonnes d’exécution vides.

La même page de 20 billets, avant et après cette leçon. Les temps sont des médianes sous la charge de travail du cours, avec les index de 008.

Description textuelle du diagramme

Avant, l’API envoie 41 requêtes à PostgreSQL pour une page de billets de facturation ouverts. Une requête obtient les 20 billets. Ensuite, 20 requêtes obtiennent les noms des clients et 20 obtiennent les dernières exécutions de l’IA, un billet à la fois. Cela fait 41 allers-retours par page, et 13,3 ms par requête avec les index. Après, l’API envoie une seule requête : billets JOIN clients, et LEFT JOIN LATERAL pour la dernière exécution. Cela fait un aller-retour par page, et 2,8 ms par requête avec les index.

Pagination par clé (keyset). Au lieu de « sauter 20 000 lignes », dites « commencer après cette ligne ». La liste est triée par created_at DESC, ticket_id DESC. La dernière ligne d’une page sert donc de signet : son heure et son identifiant. La page suivante demande les lignes qui viennent après elle dans cet ordre :

WHERE (t.created_at, t.ticket_id) < ('2026-06-26 16:38:57+00', 'T-314017')

C’est une comparaison de lignes : elle compare d’abord les heures, puis les identifiants seulement quand les heures sont égales. Deux billets peuvent avoir la même heure. L’identifiant les départage, et aucune ligne n’est perdue ni répétée. La comparaison utilise les mêmes colonnes et le même ordre que l’index de 008. La base de données cherche donc une fois dans l’index, atteint le signet et poursuit la lecture. C’est la pagination par clé : on trouve la page par sa clé, sans compter.

Page 1 001 de la liste des billets d'expédition fermés. OFFSET lit chaque ligne avant la page; la pagination par clé passe au signet. Temps des plans mesurés dans le cours.

Description textuelle du diagramme

La liste des billets d’expédition fermés, 20 billets par page, avec l’index (status, team, created_at DESC, ticket_id DESC). Avec OFFSET 20000, la base de données lit 20 000 entrées d’index et les écarte, puis retourne les 20 suivantes. Il reste ensuite 56 140 entrées. Elle lit 20 020 lignes, et le plan prend 4,4 ms. Plus la page est éloignée, plus elle lit de lignes. Avec la pagination par clé, après la ligne (2026-06-26 16:38:57, T-314017), une recherche dans l’index saute les 20 000 premières entrées. La base de données lit 21 lignes : 20 pour la page et 1 pour savoir qu’une page suivante existe. Le plan prend 0,085 ms, à la page 1 ou 1 001.

L’API donne le signet au client sous forme de curseur : la valeur next (suivant) de chaque page. Le client la renvoie dans after (après). Le curseur contient l’heure et l’identifiant, joints par | et encodés en base64 pour tenir dans une URL. Ce n’est ni un secret ni une signature. Un client qui le modifie obtient la page après une autre ligne, ou une erreur.

Nommez les colonnes. Énumérez seulement les colonnes que la page affiche. C’est une projection. La nouvelle requête retourne 12 colonnes; le plan indique width=104 au lieu de width=201.

La nouvelle requête de liste de Larkfield

Voici le nouveau list_query dans ticket_api/repository.py. Les colonnes viennent en premier :

ticket_api/repository.py (part)

LIST_COLUMNS = """
    t.ticket_id, t.customer_id, c.name AS customer_name, t.subject, t.team, t.priority,
    t.status, t.created_at, t.message_count, t.last_message_at,
    r.model AS last_run_model, r.status AS last_run_status
"""

ticket_api/repository.py (part)

    where, params = [], {"limit": limit + 1}
    for column, value in (("status", status), ("team", team), ("customer_id", customer_id)):
        if value is not None:
            where.append(f"t.{column} = %({column})s")
            params[column] = value
    if after:
        params["after_at"], params["after_id"] = decode_cursor(after)
        where.append("(t.created_at, t.ticket_id) < (%(after_at)s, %(after_id)s)")
    sql = (
        f"SELECT {LIST_COLUMNS}"
        " FROM tickets t JOIN customers c ON c.customer_id = t.customer_id"
        " LEFT JOIN LATERAL (SELECT a.model, a.status FROM ai_runs a WHERE a.ticket_id ="
        " t.ticket_id"
        " ORDER BY a.created_at DESC LIMIT 1) r ON true"
        + (" WHERE " + " AND ".join(where) if where else "")
        + " ORDER BY t.created_at DESC, t.ticket_id DESC LIMIT %(limit)s"
    )

Trois détails comptent :

• limit + 1. La requête demande 21 lignes pour une page de 20. Si la 21e ligne revient, une page suivante existe. Alors, list_tickets retourne les 20 premières et un curseur next créé à partir de la 20e ligne. Sinon, next est vide. Une ligne supplémentaire coûte moins cher qu’une deuxième requête qui compte.
• Les valeurs restent des paramètres, comme l’exige Requêtes sûres. L’heure et l’identifiant du curseur sont transmis dans %(after_at)s et %(after_id)s. decode_cursor lève InvalidCursor pour une valeur que l’API n’a pas créée. Le gestionnaire d’erreurs la transforme en 422 invalid_request (requête invalide) avec fields: ["after"].
• La page a une taille maximale. limit doit être entre 1 et 100, comme avant. Un client ne peut pas demander 300 000 lignes dans une page.

L’API change aussi : offset disparaît, after est nouveau, et la réponse contient next au lieu de next_offset. Donc, contract/openapi.json change, et le test du contrat le vérifie. Voici le plan de la nouvelle requête, sur le grand jeu de données avec 008, mesuré dans le cours :

Limit  (cost=1.13..195.36 rows=21 width=104) (actual time=0.011..0.074 rows=21.00 loops=1)
  Buffers: shared hit=164
  ->  Nested Loop Left Join  (cost=1.13..15743.11 rows=1702 width=104) (actual time=0.011..0.074 rows=21.00 loops=1)
        Buffers: shared hit=164
        ->  Nested Loop  (cost=0.71..6662.02 rows=1702 width=89) (actual time=0.008..0.039 rows=21.00 loops=1)
              Buffers: shared hit=80
              ->  Index Scan using tickets_queue_idx on tickets t  (cost=0.42..4842.91 rows=1702 width=77) (actual time=0.006..0.010 rows=21.00 loops=1)
                    Index Cond: ((status = 'open'::text) AND (team = 'billing'::text))
                    Index Searches: 1
                    Buffers: shared hit=17
              ->  Index Scan using customers_pkey on customers c  (cost=0.29..1.07 rows=1 width=19) (actual time=0.001..0.001 rows=1.00 loops=21)
                    Index Cond: (customer_id = t.customer_id)
                    Index Searches: 21
                    Buffers: shared hit=63
        ->  Limit  (cost=0.42..5.32 rows=1 width=23) (actual time=0.001..0.002 rows=1.00 loops=21)
              Buffers: shared hit=84
              ->  Index Scan using ai_runs_ticket_idx on ai_runs a  (cost=0.42..10.21 rows=2 width=23) (actual time=0.001..0.001 rows=1.00 loops=21)
                    Index Cond: (ticket_id = t.ticket_id)
                    Index Searches: 21
                    Buffers: shared hit=84
Planning:
  Buffers: shared hit=6
Planning Time: 0.081 ms
Execution Time: 0.081 ms

Lisez-le de bas en haut, avec ce que vous avez appris dans la dernière leçon. L’index de la file fournit 21 billets dans l’ordre. Pour chacun, une boucle imbriquée cherche son client par clé (loops=21) et sa dernière exécution avec ai_runs_ticket_idx (loops=21). Chaque étape est une recherche dans un index. La requête entière lit 164 pages en 0,08 ms (médiane de 5 mesures). Les 20 recherches du code N+1 sont toujours là, mais dans une seule requête, sans aller-retour à Python entre elles.

Mesurer avant d’optimiser

Une requête est-elle toujours plus rapide que 41? Mei a mesuré les deux versions du code, avec et sans index, sur le grand jeu de données. La charge de travail était identique : 5 passages de 200 requêtes, 4 à la fois.

• Sans index
• Avec 008

Le point de terminaison de la liste de billets sous la charge de travail du cours (scripts.bench : la page des billets ouverts de chacune des 5 équipes, 200 requêtes, 4 à la fois, médiane de 5 passages), sur le grand jeu de données. N+1 est le code du module 3 (41 requêtes, OFFSET); une requête est le code de cette leçon.

Description textuelle du graphique

Un diagramme à colonnes du temps médian par requête. Sans index : le code N+1 prend 375 ms, et le code à une requête prend 1 148 ms. Avec les index de 008 : le code N+1 prend 13,3 ms, et le code à une requête prend 2,8 ms.

Sans index, la requête unique est 3 fois plus lente que les 41 requêtes : 1 148 ms contre 375 ms. Avec les index, elle est presque 5 fois plus rapide : 2,8 ms contre 13,3 ms. Le même changement aide ou nuit, selon les autres conditions. Donc, « moins de requêtes, c’est plus rapide » est une supposition, pas une règle. Le cas d’échec ci-dessous montre pourquoi. Mesurez chaque changement, un à la fois, avec les mêmes données et la même charge de travail.

Voici le parcours complet du module sous cette charge de travail : 375 ms → 13,3 ms → 2,8 ms par page, et de 10,6 à 1 334,4 requêtes par seconde. La page éloignée, après la ligne 20 000, prend 2,9 ms avec le code de cette leçon. Le code du module 3 prenait 21,5 ms avec OFFSET 20000.

Exercice guidé : une requête, des pages par clé

1. Exécutez l’exemple OFFSET de cette leçon dans psql et trouvez le billet qui figure sur les deux pages.

2. Copiez les fichiers de cette leçon depuis l’instantané end-of-m04 :

   macOS / Linux

   S=../nextia-learning-labs/C20/snapshots/end-of-m04
   for f in ticket_api/repository.py ticket_api/tickets.py ticket_api/models.py ticket_api/errors.py contract/openapi.json tests/test_indexes.py tests/test_pagination.py; do cp "$S/$f" "$f"; done

   Windows (PowerShell)

   $S = "..\nextia-learning-labs\C20\snapshots\end-of-m04"
   foreach ($f in 'ticket_api\repository.py', 'ticket_api\tickets.py', 'ticket_api\models.py', 'ticket_api\errors.py', 'contract\openapi.json', 'tests\test_indexes.py', 'tests\test_pagination.py') { Copy-Item "$S\$f" $f }

   Ouvrez ticket_api/repository.py et trouvez list_query, list_tickets, encode_cursor et decode_cursor.

3. Exécutez les tests : python -m pytest. Puis exécutez seulement les nouveaux tests du module, avec leurs noms : python -m pytest tests/test_indexes.py tests/test_pagination.py -v.

4. Affichez le plan de la nouvelle requête : python -m scripts.explain queue.

5. Mesurez avec la même charge de travail qu’au début du module et enregistrez le résultat :

   python -m scripts.bench --json bench-after.json

6. Comparez les deux fichiers. Cette ligne fonctionne dans les deux terminaux :

   python -c "import json; a, b = (json.load(open(f)) for f in ('bench-before.json', 'bench-after.json')); print('same workload:', all(a[k] == b[k] for k in ('paths', 'requests', 'concurrency', 'pool'))); [print(k, a[k], '->', b[k]) for k in ('median_ms', 'p95_ms', 'per_second', 'queries_per_request')]"

7. Exécutez python -m scripts.bench une fois de plus pour vérifier que le résultat se répète.

8. Démarrez l’API avec fastapi dev. Dans un deuxième terminal, demandez trois billets d’expédition fermés. Si votre .env définit API_KEY, ajoutez -H "X-API-Key: <your key>"; sous Windows, tapez curl.exe :

   curl -s 'http://127.0.0.1:8000/v1/tickets?status=closed&team=shipping&limit=3'

   Demandez ensuite la page suivante : ajoutez &after=<the next value> à la même adresse. Enfin, envoyez un curseur invalide : curl -s 'http://127.0.0.1:8000/v1/tickets?after=not-a-cursor'. Arrêtez l’API avec Ctrl+C.

Afficher le résultat attendu

Étape 1 : T-399979 est la dernière ligne de la page 1 et la première de la page 2. Étape 3 : 87 passed. Les nouveaux tests comprennent test_the_ticket_list_uses_the_queue_index, test_the_pages_together_have_every_ticket_once, test_a_new_ticket_does_not_shift_the_next_page, test_a_broken_cursor_is_422 et test_the_page_size_has_a_maximum. L’étape 4 montre le plan de cette leçon : Nested Loop Left Join (jointure externe gauche par boucle imbriquée), trois étapes Index Scan (parcours d’index), aucun Seq Scan (parcours séquentiel).

Étapes 5 et 6, mesurées par l’auteur sur une nouvelle copie du projet. Vos nombres seront différents; comparez seulement vos propres exécutions.

same workload: True
median_ms 390.4 -> 2.9
p95_ms 428.4 -> 6.6
per_second 10.2 -> 1168.5
queries_per_request [41] -> [1]

L’étape 7 donne une médiane proche de celle de l’étape 5 : encore 2,9 ms dans la même exécution. Étape 8, la première page (exécution de l’auteur, sur une ligne, abrégée ici au premier billet et à la fin) :

{"items":[{"ticket_id":"T-398266","customer_id":"C-17526","customer_name":"Theo Gomez","subject":"Change delivery address","team":"shipping","priority":3,"status":"closed","created_at":"2026-10-06T20:27:26Z","message_count":1,"last_message_at":"2026-10-07T13:46:26Z","last_run_model":"keywords-1.1","last_run_status":"ok"}, … ],"next":"MjAyNi0xMC0wNlQxOToyOTo0MyswMDowMHxULTM5ODIyNw"}

La page suivante commence par T-398225, le billet après T-398227 dans l’ordre de la liste. La valeur next se décode en 2026-10-06T19:29:43+00:00|T-398227 : l’heure et l’identifiant du dernier billet de la première page. Le curseur invalide donne 422 :

{"error":{"code":"invalid_request","message":"The value of after is not valid.","request_id":"bc473ae19341","fields":["after"]}}

Vérification du module : une amélioration reproductible

L’étape 6 vérifie que le module est terminé. Elle compare deux exécutions de la même charge de travail : mêmes requêtes, même ordre, même nombre de requêtes simultanées, mêmes données. same workload: True (même charge de travail : vrai) le prouve. Entre les deux fichiers, seuls 008 et le code de cette leçon ont changé. Le résultat est plusieurs fois plus rapide, avec 1 requête par requête HTTP au lieu de 41. L’étape 7 montre que le résultat se répète. Une seule exécution rapide pourrait être de la chance; deux exécutions aux résultats proches constituent une mesure.

Les tests protègent désormais le changement. test_indexes.py vérifie que la liste et la requête de dernière exécution peuvent utiliser leurs index. test_pagination.py parcourt toutes les pages et vérifie que chaque billet apparaît exactement une fois, avec une requête par page. Il ajoute aussi un billet entre deux pages.

À vous de jouer : une page plus grande

Changez une condition : la taille de la page. Faites d’abord une prédiction : une page de 100 billets contient 5 fois plus de lignes qu’une page de 20. Est-elle 5 fois plus lente? Mesurez les deux sur la liste des billets d’expédition fermés :

python -m scripts.bench --path "/v1/tickets?status=closed&team=shipping&limit=20"
python -m scripts.bench --path "/v1/tickets?status=closed&team=shipping&limit=100"

Afficher un exemple de réponse

Dans l’exécution de l’auteur, la médiane était de 3,1 ms pour 20 billets et de 4,4 ms pour 100. Vos nombres diffèrent, mais la grande page est beaucoup moins que 5 fois plus lente. Chaque requête a un coût fixe : la requête HTTP, le trajet à la base de données, le démarrage de la requête SQL. Les lignes elles-mêmes coûtent peu ici, car chacune nécessite trois recherches dans des index. Le coût augmente quand même avec les lignes : JSON, réseau et mémoire du client. C’est pourquoi l’API limite limit à 100 : un client ne peut pas transformer une requête en copie de la table. Avec OFFSET, une grande taille de page masquerait aussi une page éloignée lente. Avec la pagination par clé, chaque page coûte la même chose.

Erreur courante

Une requête, et 3 fois plus lente

Symptôme : Amira réécrit la liste en une seule requête sur une copie de la base sans les index de 008. scripts.bench indique database queries per request: 1 (une requête à la base par requête HTTP), comme prévu. Mais la médiane est d’environ 1 148 ms, contre 375 ms pour les 41 requêtes.

Cause : la sous-requête LATERAL s’exécute une fois par billet, soit 21 fois. Sans ai_runs_ticket_idx, chaque exécution parcourt les 449 734 exécutions de l’IA au complet. Le code N+1 faisait aussi 20 parcours semblables, mais chacune de ses requêtes séparées obtenait deux processus parallèles. Une sous-requête dans une boucle imbriquée n’en obtient pas. Chaque parcours prenait donc deux fois plus de temps.

Diagnostic : affichez le plan de la requête. Voici l’exécution du cours sur le grand jeu de données sans 008 (les dernières lignes du plan) :

        ->  Limit  (cost=16101.68..16101.69 rows=1 width=23) (actual time=15.833..15.833 rows=1.00 loops=21)
              Buffers: shared read=220080
              ->  Sort  (cost=16101.68..16101.69 rows=2 width=23) (actual time=15.832..15.833 rows=1.00 loops=21)
                    Sort Key: a.created_at DESC
                    Sort Method: quicksort  Memory: 25kB
                    Buffers: shared read=220080
                    ->  Seq Scan on ai_runs a  (cost=0.00..16101.67 rows=2 width=23) (actual time=10.168..15.828 rows=1.57 loops=21)
                          Filter: (ticket_id = t.ticket_id)
                          Rows Removed by Filter: 449732
                          Buffers: shared read=220080

Execution Time: 404.769 ms

loops=21 sur un Seq Scan : 21 parcours complets, environ 15,8 ms chacun, et 220 080 pages lues pour une page de billets. Voilà la boucle imbriquée de l’avertissement de la dernière leçon, en action.

Correction : ajoutez l’index dont l’étape interne a besoin, ai_runs (ticket_id, created_at DESC). Puis mesurez de nouveau : 2,8 ms. En général, changez une chose à la fois et mesurez chaque étape. Si l’index était venu en premier, la réécriture aurait semblé un gain évident. Dans l’ordre inverse, elle semblait une perte.

Quand utiliser la pagination par clé, et ses coûts

Utilisez la pagination par clé (keyset) pour les listes parcourues page par page, dans un ordre fixe, pendant l’arrivée de nouvelles lignes : files d’attente, fils, exportations. Utilisez une seule requête avec des jointures lorsque le code lit des lignes liées dans une boucle. Nommez les colonnes dans chaque requête envoyée par une API.

Les coûts :

• Impossible de passer directement à la page 57. La pagination par clé permet seulement de passer à la page suivante. Une interface qui affiche des numéros de page nécessite OFFSET, ou une autre méthode. Pour les petites listes, OFFSET convient.
• Le tri doit être unique et correspondre à l’index. Sans ticket_id pour départager les égalités, deux billets ayant la même heure pourraient disparaître ou se répéter entre les pages. Si le tri change, le curseur et l’index doivent changer aussi.
• Les clients doivent changer. L’ancien paramètre offset disparaît, et FastAPI ignore les paramètres qu’il ne connaît pas. Lors de l’exécution de l’auteur, …&limit=3&offset=20000 a renvoyé 200 avec la première page, pas les lignes après la ligne 20 000. Un ancien client ne voit aucune erreur, seulement la même page à nouveau. Prévenez les clients avant un tel changement, et donnez-leur une version ou une date.
• Un curseur peut devenir périmé. Si le billet repéré est supprimé, la page suivante fonctionne toujours, car la comparaison utilise seulement les valeurs, pas la ligne.

De bout en boutConcevoir et optimiser le stockage d'un jeu de données public ouvertÉtude de cas

L’étude de cas Boston 311 compare une page OFFSET et une page par clé loin dans les 77 047 demandes d’un quartier.

Vérification des connaissances

Vérifiez votre compréhension

Pourquoi OFFSET 20000 ralentit-il quand on avance dans les pages, alors que la pagination par clé prend le même temps aux pages 1 et 1 001?

Afficher la réponse

Avec OFFSET, la base de données ne peut pas situer la ligne 20 001 sans lire les 20 000 lignes précédentes. Elle lit donc 20 020 lignes et en écarte 20 000. La pagination par clé lui donne une valeur à chercher : (created_at, ticket_id) < (…). Avec l’index sur (status, team, created_at DESC, ticket_id DESC), elle trouve cette position en une recherche et lit 21 lignes. Le travail dépend de la taille de la page, pas de sa position dans la liste.

Vérifiez votre compréhension

La nouvelle requête cherche encore 20 fois la dernière exécution d'IA, comme le code N+1. Pourquoi est-elle plus rapide?

Afficher la réponse

Les recherches se font dans une seule requête, à l’intérieur de la base de données. Le code N+1 envoyait 41 instructions distinctes. Chacune fait un aller-retour entre Python et PostgreSQL, avec sa propre analyse, sa planification et son résultat. Avec les index, chaque recherche prend quelques microsecondes. Les allers-retours occupaient donc la majeure partie du temps. Résultat mesuré : 13,3 ms avec 41 requêtes, 2,8 ms avec une seule.

Vérifiez votre compréhension

Un collègue dit : « Une seule requête est toujours plus rapide que plusieurs. » Répondez à l'aide des mesures de ce module.

Afficher la réponse

Pas toujours. Sans les index, la liste obtenue par une seule requête prenait 1 148 ms, contre 375 ms pour 41 requêtes. Sa sous-requête LATERAL parcourait tout ai_runs 21 fois, sans processus parallèles. Avec les index, la requête unique était plus rapide : 2,8 contre 13,3 ms. L’ordre des changements et les données déterminent le résultat. Mesurez donc chaque changement avec les mêmes données et la même charge de travail. Lisez le plan lorsqu’un résultat vous surprend.

Récapitulatif

• La requête N+1 envoie une requête pour une liste, puis une par ligne. Une seule requête avec une jointure et LEFT JOIN LATERAL récupère ensemble la page, les noms et les dernières exécutions.
• OFFSET lit et écarte chaque ligne avant la page, et les pages se décalent quand des lignes arrivent. La pagination par clé commence après les valeurs (created_at, ticket_id) de la dernière ligne, selon l’ordre de l’index : 0,085 ms pour toute page.
• L’API renvoie un curseur next (suivant) et accepte after (après); une page contient au plus 100 lignes; la requête nomme ses 12 colonnes au lieu d’utiliser SELECT *.
• Mesurez avant d’optimiser : sans index, une seule requête était 3 fois plus lente que 41. Avec les index : 375 → 13,3 → 2,8 ms par page, sous la même charge de travail.
• bench-before.json et bench-after.json prouvent une amélioration reproductible. Votre projet correspond à end-of-m04 : 87 passed (87 tests réussis). Ensuite : les fichiers, dans le stockage objet.

Leçon suivante : Modèles de stockage objet