Concevoir et optimiser le stockage d'un jeu de données public ouvert

Ce que vous saurez faire

Prenez un vrai jeu de données public de 582 849 lignes et concevez un stockage que des mesures permettent de justifier. Vous listez les façons de lire les données et vérifiez chaque règle sur les fichiers bruts avant que la base l’impose. Ensuite, vous chargez les données avec une migration, mesurez les requêtes réelles et choisissez les index selon les mesures. À la fin, vous modifiez le schéma sans risque pour de nouvelles données. Vous vérifiez aussi la sauvegarde par un exercice de restauration.

Étude de casPièce de portfolio

Jeu de données

Boston 311 Service Requests, 2024 et 2025, et une semaine du nouveau système 311 de la ville

Licence

Open Data Commons PDDL 1.0 (domaine public)

Durée

Environ 4 h

S’exécute dans

VS Code, Python 3.12+, Docker

Compétences utilisées

• Inventaire des modes d'accès
• Contraintes vérifiées sur les données brutes
• Préparation et chargement
• Migrations en SQL simple
• Requêtes paramétrées
• Pagination par clé
• Index mesurés et partiels
• Sauvegarde et exercice de restauration

Télécharger le projet de départ (.zip)Projet terminé (.zip)

Décompressez le projet de départ, ouvrez le dossier dans VS Code et suivez les étapes ci-dessous. Elles commencent par un nouvel environnement virtuel.

Le projet de départ contient les outils du cours. Il comprend les paramètres, l’exécuteur de migrations et un pool de connexions (pool). Il comprend aussi le téléchargement et sa somme de contrôle, le banc d’essai, le script des plans et les scripts de sauvegarde. Vous écrivez l’inventaire, les migrations, le chargement, les requêtes et les tests. Le projet terminé montre le résultat, si vous bloquez ou voulez comparer. Le code utilise la licence MIT. Vous pouvez donc publier votre version.

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

Ce que vous devez savoir

Cette étude de cas utilise tout le cours, avec des données qui ne viennent pas de Larkfield. Voici les leçons sur lesquelles elle s’appuie le plus :

• Les cinq questions de l’inventaire, dans Inventaire des modes d’accès.
• CHECK (vérification), UNIQUE (unicité) et les clés étrangères, dans Contraintes d’intégrité.
• Les paramètres et un seul fichier pour tout le SQL, dans Requêtes sûres.
• L’exécuteur de migrations, ses sommes de contrôle et -- migrate: no-transaction (sans transaction), dans Migrations.
• Les plans, les index composites et les pages par clé, dans Lire un plan de requête, Indexer de façon réfléchie et Limiter la taille des résultats.
• Le manifeste de sauvegarde et l’exercice, dans Restauration et conservation.

Cette page enseigne trois choses absentes du cours : une table de préparation, un index partiel et une contrainte ajoutée comme NOT VALID (non validée).

Ce qu’il vous faut sur votre ordinateur

• Python 3.12 ou une version plus récente, VS Code et Docker Desktop (ou votre propre PostgreSQL 18, comme dans De SQLite à PostgreSQL).
• Environ 1 Go d’espace disque libre : 341 Mo de téléchargements, une base d’environ 300 Mo et une sauvegarde de 41 Mo.

Testé sur macOS (Apple M4, 16 Go, macOS 26.6, PostgreSQL 18.6 dans Docker, Python 3.12.13). Les commandes Windows et Linux suivent celles du cours, mais elles n’ont pas été testées.

La situation

Le service 311 d’une ville reçoit les signalements de problèmes dans les espaces publics. Par exemple : un nid-de-poule, une collecte de déchets manquée, un lampadaire éteint ou une voiture sur un trottoir. Chaque signalement devient une demande de service. Une équipe municipale la traite et la ferme une fois le travail terminé.

Rosa dirige l’équipe de données du bureau des opérations du 311. Aujourd’hui, toutes les équipes lisent les demandes dans un fichier de tableur exporté chaque nuit. Les listes de travail des équipes prennent du temps à s’ouvrir, et la carte publique montre les données d’hier. Personne ne vérifie si un nouveau signalement répète une demande ouverte. Ken, un superviseur d’équipe, pose la question à l’origine du projet. « Pourquoi ma liste prend-elle des secondes à s’ouvrir? Je veux seulement les 50 demandes les plus anciennes. »

Rosa veut une seule base PostgreSQL pour les listes de travail, la carte et les rapports. Ses règles doivent correspondre aux données réelles de la ville. Il faut accélérer ses requêtes lentes et le prouver. Il faut tester sa sauvegarde. Les personnes sont fictives. Les demandes sont réelles : Boston les publie comme données ouvertes.

Ce que vous allez créer

Vous créez boston311-storage : une conception de base de données, les scripts de chargement et de requête, et les mesures justifiant chaque choix.

1. Un inventaire des modes d’accès : qui lit quoi, à quelle fréquence et en quelle quantité.
2. Un rapport sur les règles des fichiers bruts, et un schéma limité aux règles que les données respectent.
3. Un chargement : les fichiers CSV de la ville dans une table de préparation, puis dans les tables, en une transaction.
4. Des requêtes pour chaque mode d’accès : uniquement des paramètres, avec des pages par clé.
5. Des index mesurés : une référence de base, les candidats un à un, puis une migration avec les meilleurs.
6. Une modification du schéma pour le nouveau système 311 de la ville, écrite pour ne pas bloquer la table.
7. Une sauvegarde et un exercice de restauration avec 7 vérifications.

Les données

Les données sont les demandes de service 311 de la ville de Boston, sur son portail de données ouvertes, Analyze Boston. La ville les publie sous la licence Open Data Commons Public Domain Dedication and License (PDDL 1.0). Vous pouvez les utiliser, les modifier et les partager sans condition. Nous nommons quand même la source. Le fichier DATASET.md du projet contient la fiche complète du jeu de données.

Pourquoi Boston? Nous avons comparé les données 311 de cinq villes, que DATASET.md liste. Les données de San Francisco sont aussi du domaine public, mais leur exportation reflète les données du moment. Aucune somme de contrôle ne peut donc rester valide. Les conditions de Chicago ne constituent pas une licence standard. Boston publie un fichier fixe par année. Le projet peut ainsi vérifier chaque téléchargement avec SHA-256, une empreinte des octets exacts.

Fichier	Lignes	Taille
boston_311_2024.csv	306 756	182 Mo
boston_311_2025.csv	276 093	159 Mo
data/boston_311_new_system_2026-10-01_07.csv (dans le projet)	3 540	1,1 Mo

Chaque fichier annuel contient 30 colonnes : l’identifiant du dossier, les dates et heures (ouverture, cible, fermeture), l’état et le motif de fermeture. Viennent ensuite le type, son motif et son sujet, la file et son service municipal. Puis le lieu : adresse, quartier, secteur électoral, districts et point. Le troisième fichier couvre une semaine du nouveau système 311 de la ville, lancé en août 2025. Vous le découvrirez à la fin. Le projet le fournit sans les commentaires en texte libre et les liens vers les photos, comme le permet la PDDL.

Deux faits comptent pour le stockage. Les dates et heures n’ont aucun fuseau horaire : elles sont à l’heure locale de Boston. Et l’état est un instantané : les fichiers ont été publiés le 18 septembre 2026. Une demande ouverte à cette date peut être fermée maintenant.

Préparer le projet

Les étapes utilisent VS Code et son terminal. Toutes les commandes s’exécutent dans le dossier du projet.

1. Téléchargez le projet de départ dans l’encadré en haut de cette page. Décompressez-le dans votre dossier de projets, par exemple ~/projects. Vous obtenez le dossier boston311-storage.

2. Dans VS Code, choisissez File > Open Folder (fichier, ouvrir un dossier), puis ouvrez boston311-storage. Ouvrez ensuite un terminal : Terminal > New Terminal (terminal, nouveau terminal).

3. Créez et activez un environnement virtuel, puis installez les paquets :

   macOS

   python3 -m venv .venv
   source .venv/bin/activate
   python -m pip install -r requirements.txt

   Windows

   python -m venv .venv
   .venv\Scripts\Activate.ps1
   python -m pip install -r requirements.txt

   Linux

   python3 -m venv .venv
   source .venv/bin/activate
   python -m pip install -r requirements.txt

   L’invite commence par (.venv). Les paquets sont psycopg 3.3.6 avec son pool de connexions, pytest et ruff.

4. Copiez le fichier de paramètres. Ouvrez ensuite .env et remplacez change-me-boston par votre propre mot de passe aux trois endroits :

   macOS

   cp .env.example .env

   Windows

   Copy-Item .env.example .env

   Linux

   cp .env.example .env

5. Démarrez PostgreSQL. Le projet Compose s’appelle boston311 et utilise le port 5433. Il peut ainsi fonctionner avec la base de ticket-api, sur le port 5432 :

   docker compose up -d db

6. Téléchargez les deux années :

   python -m scripts.download

   Sortie

   boston_311_2024.csv: downloading 182 MB ...
   boston_311_2024.csv: 182 MB in 5 s, checksum OK
   boston_311_2025.csv: downloading 159 MB ...
   boston_311_2025.csv: 159 MB in 4 s, checksum OK

Ce que cela nous apprend. Vous avez les mêmes octets que ceux utilisés pour cette page. Si la ville publie un fichier révisé, la somme de contrôle change et le script s’arrête avec un message. Vous pouvez alors accepter le nouveau fichier avec --accept-changed (accepter les changements). Vos nombres seront toutefois différents de ceux de cette page.

Étape 1 : l’inventaire des modes d’accès

Avant de créer une table, notez comment les données sont lues. L’équipe de Rosa a consulté les équipes de terrain, l’équipe de la carte et les gestionnaires. Le résultat se trouve dans docs/access-patterns.md :

	Mode d’accès	Qui	Forme	Fréquence	Lignes
P1	Une demande par son identifiant	une personne au téléphone	case_id = ?	toute la journée	1
P2	Liste de travail : demandes ouvertes d’une file, les plus anciennes d’abord, 50 par page	équipes de terrain	ouvertes, une file, par opened_at	toute la journée, souvent	50 sur un maximum de 61 333
P3	Fil local : demandes d’un quartier, les plus récentes d’abord, 50 par page	carte publique, analystes	un quartier, par opened_at en ordre décroissant	souvent; les analystes parcourent beaucoup de pages	50 sur un maximum de 77 047
P4	Vérification des doublons : demandes ouvertes du même type à la même adresse, dans les 30 derniers jours	le système, avant d’enregistrer une nouvelle demande	adresse, type, ouvertes, récentes	environ 800 par jour	de 0 à quelques lignes
P5	Tableau de bord des retards : demandes ouvertes dont la date cible est passée, par service municipal	gestionnaires	un décompte des demandes ouvertes	toutes les quelques minutes	11
P6	Rapport mensuel : demandes et proportion traitée à temps par service municipal	gestionnaires, public	un décompte sur une année	une fois par mois	environ 150

Les écritures représentent environ 800 nouvelles demandes par jour. Une actualisation nocturne à partir du fichier municipal ferme et rouvre aussi des demandes. P2 et P4 doivent être à jour immédiatement. P6 peut dater de la veille.

L’inventaire oriente déjà la conception. P1 nécessite seulement la clé primaire. P2, P3 et P4 ont chacun besoin d’un accès rapide à quelques lignes. P5 et P6 lisent la majeure partie de la table. Un index ne peut donc probablement pas les aider. Vous testerez chacune de ces hypothèses.

Étape 2 : vérifier les règles avant de les écrire

Le problème : une règle que les données ne respectent pas

Le premier schéma de Rosa imposait une règle à ward : un nombre de 1 à 22, comme les secteurs électoraux de Boston. Le premier chargement s’est arrêté avec cette erreur, sans charger de données :

Output (end, shortened): the first load

psycopg.errors.CheckViolation: new row for relation "service_requests" violates check constraint "requests_ward_range"
DETAIL:  Failing row contains (101005234149, 2024-01-03 13:49:00+00, ..., null, 0, 1, null, ...)

Le chargement s’exécute dans une seule transaction. Rien n’a donc été chargé à moitié. C’est bien. Mais l’erreur montre seulement la première ligne incorrecte. Combien y en a-t-il d’autres, et de combien de sortes? Les corriger une erreur à la fois nécessite un chargement par problème.

L’idée : une table de préparation et un rapport sur les règles

Pensez à la salle des douanes d’un aéroport. Chaque bagage entre d’abord dans cette salle. Les agents y vérifient les bagages avant de les laisser entrer dans la ville.

Une table de préparation joue ce rôle. Elle contient les colonnes du fichier brut, toutes de type text (texte), pour que COPY (copier) accepte chaque ligne. Ensuite, SQL compte les lignes qui enfreignent chaque règle souhaitée, avant que la règle existe. Une règle que 0 ligne enfreint peut devenir une contrainte. Une règle que des lignes enfreignent nécessite une décision.

scripts.check_rules copie les deux fichiers dans staging_requests et compte les violations de chaque règle candidate :

python -m scripts.check_rules

Sortie

Copied boston_311_2024.csv: 306,756 rows in 1.0 s
Copied boston_311_2025.csv: 276,093 rows in 1.0 s
582,849 raw rows. Rows (or groups) that break each candidate rule:
  each case ID appears once                                       0
  case ID is 12 digits                                            0
  status is Open or Closed                                        0
  closed <=> has a closing time                                   0
  closed <=> has a closure reason                               464
  closed after opened                                             0
  target time after opened                                        0
  ward written as a plain number                            432,892
  ward between 1 and 22 (after removing 'Ward ' and zeros)      262
  council district between 1 and 9                              324
  ZIP code is 5 digits                                            0
  point in or near Boston                                         0
  each type has one reason and subject                            0
  each queue has one department                                   0
  each type has one department                                  116

Chaque règle correspond à un décompte SQL. Par exemple, « fermée ⇔ possède un motif de fermeture » correspond à count(*) … WHERE (case_status = 'Closed') <> (closure_reason IS NOT NULL). Les 15 décomptes mènent à trois décisions :

Le rapport sur les règles décide du sort de chacune avant que la base l'impose : l'imposer, nettoyer les données d'abord, ou accepter que ce n'est pas une règle.

Description textuelle du diagramme

Deux fichiers CSV passent par COPY vers une table de préparation de 30 colonnes texte. Un script compte les lignes qui enfreignent chaque règle. Huit règles ont 0 violation : elles deviennent des contraintes (CHECK, UNIQUE, NOT NULL, clés étrangères). Trois nécessitent d’abord un nettoyage. Le secteur électoral, écrit de trois façons dans 432 892 lignes, devient un nombre. Le secteur électoral 0 (262 lignes) et le district du conseil municipal 0 (324 lignes) deviennent NULL. Les contraintes vérifient ensuite les plages. Deux règles ne correspondent pas aux données réelles. 464 lignes enfreignent « fermée si et seulement si elle possède un motif de fermeture » (143 rouvertes, 321 fermées en lot en juin 2024). Et plusieurs services municipaux traitent 116 des 175 types. Ces règles ne deviennent pas des contraintes, et DATASET.md les explique.

La règle qui n’est pas vraie

Le motif de fermeture semblait offrir une règle sûre : une demande fermée dit pourquoi, et une demande ouverte ne le dit pas. Testez-la sur la table chargée (vous la chargez à l’étape 3), dans psql :

ALTER TABLE service_requests ADD CONSTRAINT requests_reason_matches_status
    CHECK ((status = 'closed') = (closure_reason IS NOT NULL));

Sortie

ERROR:  check constraint "requests_reason_matches_status" of relation "service_requests" is violated by some row

Output: status against reason

 status | has_reason | count
--------+------------+--------
 closed | f          |    321
 closed | t          | 462388
 open   | f          | 119997
 open   | t          |    143

Les 143 demandes ouvertes ont toutes un motif qui commence par « Case Closed. » (dossier fermé) : elles ont été fermées, puis rouvertes. Les 321 demandes fermées sans motif ont toutes été fermées les 10, 13 et 17 juin 2024 : une fermeture en lot. Ce sont des événements réels du travail municipal. Une contrainte rejetterait 464 demandes réelles, et les 143 demandes rouvertes figurent dans les listes de travail des équipes. Ce n’est donc pas une règle. Vous conservez les lignes et notez ce constat dans DATASET.md.

Comparez avec le secteur électoral. Le secteur électoral 0 n’existe pas. Donc, 0 signifie « inconnu ». NULL dit exactement cela, et la règle de plage devient vraie. Le nettoyage convient quand la valeur a un seul sens clair. Abandonner la règle convient quand le monde ne la suit pas.

Étape 3 : le schéma et le chargement

Le schéma suit le rapport. Les types et les files ont leurs propres tables. Le rapport a prouvé qu’un type possède toujours un seul motif et sujet, et une file un seul service municipal. Voici la table des demandes :

migrations/001_service_requests.sql (from line 22)

CREATE TABLE service_requests (
    case_id        text PRIMARY KEY,
    opened_at      timestamptz NOT NULL,
    sla_target_at  timestamptz,
    closed_at      timestamptz,
    status         text NOT NULL,
    on_time        text NOT NULL,
    closure_reason text,
    title          text,
    type_id        smallint NOT NULL REFERENCES case_types,
    queue_id       smallint NOT NULL REFERENCES queues,
    source         text NOT NULL,
    neighborhood   text,
    ward           smallint,
    council_district smallint,
    pwd_district   text,
    zipcode        text,
    address        text,   -- a street address or an intersection, without the city
    latitude       double precision,
    longitude      double precision,

    CONSTRAINT requests_case_id_format CHECK (case_id ~ '^[0-9]{12}$'),
    CONSTRAINT requests_status_known CHECK (status IN ('open', 'closed')),
    -- A closed request has a closing time; an open one has none.
    CONSTRAINT requests_closed_at_matches_status
        CHECK ((status = 'closed') = (closed_at IS NOT NULL)),
    CONSTRAINT requests_closed_after_opened CHECK (closed_at >= opened_at),
    CONSTRAINT requests_target_after_opened CHECK (sla_target_at >= opened_at),
    CONSTRAINT requests_on_time_known CHECK (on_time IN ('ontime', 'overdue')),
    CONSTRAINT requests_ward_range CHECK (ward BETWEEN 1 AND 22),
    CONSTRAINT requests_council_district_range CHECK (council_district BETWEEN 1 AND 9),
    CONSTRAINT requests_zipcode_format CHECK (zipcode ~ '^[0-9]{5}$'),
    -- A point is either complete and in or near Boston, or missing.
    CONSTRAINT requests_point_complete CHECK ((latitude IS NULL) = (longitude IS NULL)),
    CONSTRAINT requests_point_in_boston
        CHECK (latitude BETWEEN 42.2 AND 42.45 AND longitude BETWEEN -71.2 AND -70.95)
);

Les dates et heures utilisent timestamptz, un instant précis. Le texte municipal « 2024-01-04 10:43:17 » est à l’heure locale de Boston. Le chargement le lit donc avec AT TIME ZONE 'America/New_York'. Sans cela, chaque heure aurait un décalage de 4 ou 5 heures. Huit colonnes brutes qu’aucun mode d’accès ne lit ne sont pas chargées : deux liens photo, une autre forme de l’adresse, la géométrie et quatre autres districts.

scripts/load.py applique les migrations et place les fichiers dans la table de préparation. Il transfère ensuite les lignes avec un seul INSERT … SELECT. Ces lignes du script sont les règles de nettoyage :

scripts/load.py (part of INSERT_REQUESTS)

    s.open_dt::timestamp AT TIME ZONE 'America/New_York',
    lower(s.case_status),
    replace(nullif(s.case_title, ''), '&', '&'),
    nullif(nullif(regexp_replace(s.ward, '^Ward ', ''), '')::smallint, 0),
    nullif(nullif(s.city_council_district, ''), '0')::smallint,

Exécutez-le, avec les migrations jusqu’à 001 seulement. Les index viendront plus tard, après les mesures :

python -m scripts.load --to 001

Sortie

applied  001_service_requests
Database is up to date (1 migrations).
Copied boston_311_2024.csv: 306,756 rows in 0.9 s
Copied boston_311_2025.csv: 276,093 rows in 0.8 s
Staged 582,849 rows; loaded 582,849 requests in 10.0 s.
Cleaned: ward written 'Ward N' 374,953, ward with a leading zero 57,679, ward 0 -> NULL 262, council district 0 -> NULL 324, '&' -> '&' 109,802.
case_types 175, queues 172, service_requests 582,849. Database: 203 MB.

Ce que cela nous apprend. 582 849 lignes sont entrées, et 582 849 demandes sont sorties. Les contraintes n’ont rien rejeté, car le rapport avait trouvé tous les problèmes avant le chargement. La table occupe 166 Mo, et la base 203 Mo. Le chargement supprime la table de préparation à la fin.

Étape 4 : un seul fichier pour les requêtes

boston311/queries.py contient une fonction par mode d’accès. Les valeurs sont toujours transmises comme paramètres, comme dans Requêtes sûres. Les listes utilisent des pages par clé, comme dans Limiter la taille des résultats. Voici la liste de travail, P2 :

boston311/queries.py (P2)

def open_queue_sql(queue: str, *, limit: int = 50, after: str | None = None) -> tuple[str, dict]:
    params = {"queue": queue, "limit": limit + 1}
    keyset = ""
    if after:
        params["after_at"], params["after_id"] = decode_cursor(after)
        keyset = "AND (r.opened_at, r.case_id) > (%(after_at)s, %(after_id)s)"
    return (
        f"""
        SELECT {LIST_COLUMNS}
        FROM service_requests r
        WHERE r.status = 'open'
          AND r.queue_id = (SELECT queue_id FROM queues WHERE name = %(queue)s)
          {keyset}
        ORDER BY r.opened_at, r.case_id
        LIMIT %(limit)s
        """,
        params,
    )

La chaîne formatée insère seulement des éléments fixes de ce fichier : la liste des colonnes et la condition de pagination par clé. Chaque valeur est transmise comme paramètre. Chaque requête possède une fonction *_sql qui renvoie le SQL et ses paramètres. Ainsi, scripts.explain montre le plan de la requête exacte que le code exécute. Une ligne supplémentaire (limit + 1) indique si une page suivante existe. P3 a la même forme, avec les demandes les plus récentes d’abord et < dans la pagination par clé.

Étape 5 : mesurer la référence de base

Une charge de travail fixe

Une mesure n’a de sens que si vous pouvez la répéter. boston311/workload.py produit toujours les mêmes appels à partir des données, selon des règles fixes, sans nombres aléatoires. Il prend 50 identifiants de demande, les 20 files ouvertes les plus chargées et chaque quartier comptant plus de 5 050 demandes. Il ajoute une page éloignée, après 5 000 lignes, et 50 demandes ouvertes pour la vérification des doublons. scripts.bench exécute la charge de travail 5 fois, avec 4 appels simultanés dans un pool de connexions (pool). Il affiche la médiane de chaque mode d’accès.

python -m scripts.bench

Output: keys only

Indexes on service_requests: service_requests_pkey
5 passes, 4 at a time. Median and p95 per call:
  P1 one request         50 calls  median     0.953 ms  p95     1.316 ms
  P2 work queue          20 calls  median    59.536 ms  p95    73.681 ms
  P3 area feed           19 calls  median    61.978 ms  p95    85.056 ms
  P3 deep page, OFFSET    5 calls  median    63.532 ms  p95    77.955 ms
  P3 deep page, keyset    5 calls  median    63.445 ms  p95    83.115 ms
  P4 duplicates          50 calls  median    66.125 ms  p95    91.646 ms
  P5 overdue              1 calls  median    35.901 ms  p95    37.118 ms
  P6 monthly report       1 calls  median   360.473 ms  p95   369.935 ms

D’autres tâches utilisaient l’ordinateur pendant ces exécutions. Observez donc les tendances, pas les temps exacts. Vos chiffres seront différents. Une deuxième exécution complète, depuis une base de données vide, a donné les mêmes tendances. P1 utilise la clé primaire et est rapide. P2, P3 et P4 prennent chacun environ 60 ms pour retourner 50 lignes ou moins. Même la page obtenue par pagination par clé (keyset) est lente, car rien n’est encore ordonné.

Pourquoi P2 est lent

python -m scripts.explain queue

Output (some lines)

Limit  (cost=25842.06..25848.00 rows=51 width=85) (actual time=27.913..29.898 rows=51.00 loops=1)
  ->  Gather Merge  (cost=25837.91..25930.73 rows=797 width=85) (actual time=27.911..29.892 rows=51.00 loops=1)
        Workers Planned: 2
        ->  Sort  (cost=24837.88..24838.71 rows=332 width=85) (actual time=26.649..26.655 rows=41.00 loops=3)
              Sort Key: r.opened_at, r.case_id
              ->  Parallel Seq Scan on service_requests r  (cost=0.00..24826.81 rows=332 width=85) (actual time=0.761..24.489 rows=20444.33 loops=3)
                    Filter: ((status = 'open'::text) AND (queue_id = (InitPlan 1).col1))
                    Rows Removed by Filter: 173839
Execution Time: 29.929 ms

Trois processus lisent toute la table. Ensemble, ils gardent les 61 333 demandes de stationnement ouvertes (20 444 chacun) et les trient pour en retourner 51. Notez l’estimation : le planificateur prévoyait 797 lignes, pas 61 333. Il prépare le plan avant que la sous-requête trouve l’identifiant de la file. Il suppose donc une file moyenne. Souvenez-vous de cela quand un plan vous surprend.

Étape 6 : essayer les index candidats, un à la fois

scripts.candidates crée un index candidat et exécute 5 fois les requêtes auxquelles il est destiné (EXPLAIN ANALYZE, médiane). Ensuite, il supprime l’index et essaie le suivant. Chaque candidat est évalué seul, sur les mêmes données.

P2, la file de travail et l’index partiel

Candidat	Taille	Plus grande file (61 333 demandes ouvertes)	File plus petite (2 376 demandes ouvertes)
aucun	—	29,2 ms	23,1 ms
(queue_id)	4,09 Mo	42,5 ms	2,9 ms
(status, queue_id)	4,16 Mo	20,7 ms	0,900 ms
(queue_id, opened_at, case_id)	28,93 Mo	5,8 ms	0,039 ms
le même, WHERE status = 'open'	5,98 Mo	0,032 ms	0,027 ms

L’index sur queue_id seul rend la grande file plus lente que sans index. Il trouve les 124 285 demandes de cette file, ouvertes ou fermées, et récupère chaque ligne à un endroit aléatoire. (status, queue_id) trouve le bon groupe, mais doit encore le trier. L’index composite qui respecte l’ordre est rapide, mais occupe 29 Mo. Il passe aussi sur chaque demande fermée de la file.

La dernière ligne présente un nouveau type d’index. Pensez au chariot des livres retournés dans une bibliothèque. Il ne contient pas tous les livres, seulement ceux qui attendent d’être replacés. Un index partiel est un index avec une clause WHERE : seules les lignes qui correspondent ont une entrée. La file de travail demande seulement les demandes ouvertes, et seulement 21 % des demandes sont ouvertes. L’index garde donc seulement celles-ci, triées par file et par date :

Un index partiel garde seulement les lignes que la requête demande. Ici, ce sont les demandes ouvertes, déjà triées par file et par date.

Description textuelle du diagramme

Dix demandes réelles de deux petites files, dont 3 ouvertes. L’index complet (queue_id, opened_at, case_id) contient 10 entrées. Pour trouver les demandes ouvertes de la file 168, la base de données passe sur ses 5 entrées fermées. L’index partiel, WHERE status = ‘open’, contient seulement les 3 demandes ouvertes, déjà dans l’ordre : la réponse, rien d’autre. Quand une demande se ferme, son entrée quitte l’index; quand elle se rouvre, l’entrée revient. Sur l’ensemble des données, les deux index occupent 28,9 Mo et 6,0 Mo.

Une requête peut utiliser un index partiel seulement si son propre WHERE implique la condition de l’index. P2 contient status = 'open' dans le texte SQL, donc le planificateur voit que la condition convient. L’index partiel rend les deux files environ 900 fois plus rapides que sans index, pour 6 Mo. Le coût : une requête sur les demandes fermées ne peut pas l’utiliser.

P3, le fil du quartier : deux pages, deux réponses

La règle d’Indexer de façon réfléchie dit : les colonnes avec = d’abord, puis l’ordre. Voici pourquoi toute la règle compte. La charge de travail comprend des quartiers très actifs, comme Dorchester (77 047 demandes). scripts.explain comprend aussi un quartier peu fréquent, Chestnut Hill, avec 27 demandes. Faites d’abord une prédiction : quel index à une seule colonne sera rapide pour chacun?

• Aucun index
• Une colonne
• Les deux : (neighborhood, opened_at DESC, case_id DESC)

La première page du fil du quartier pour Dorchester (77 047 demandes), avec chaque index candidat. EXPLAIN ANALYZE, médiane de 5 exécutions.

Description textuelle du graphique

Un diagramme à quatre barres verticales pour la page de Dorchester. Aucun index : 26,6 ms. Un index sur neighborhood : 28,3 ms. Un index sur opened_at : 0,047 ms. L’index composite sur les deux : 0,020 ms.

• Aucun index
• Une colonne
• Les deux : (neighborhood, opened_at DESC, case_id DESC)

Les mêmes candidats pour Chestnut Hill (27 demandes). L'index rapide à une seule colonne n'est plus le même.

Description textuelle du graphique

Un diagramme à quatre barres verticales pour la page de Chestnut Hill. Aucun index : 25,1 ms. Un index sur neighborhood : 0,022 ms. Un index sur opened_at : 25,9 ms. L’index composite : 0,015 ms.

(opened_at) parcourt toutes les demandes, des plus récentes aux plus anciennes, et garde celles du quartier. À Dorchester, il en trouve 51 rapidement. Chestnut Hill en a seulement 27, moins qu’une page. Le parcours ne remplit donc jamais la page et traverse tout l’index. (neighborhood) fait l’inverse : il trouve immédiatement les 27 lignes de Chestnut Hill, mais doit trier les 77 047 de Dorchester. Seul l’index composite est rapide pour les deux. Il regroupe chaque quartier et place déjà les demandes les plus récentes en premier. C’est aussi le plus grand, à 36 Mo. Un test limité à la page du quartier très actif aurait choisi (opened_at), laissant les petits quartiers lents.

P4, P5 et P6

Pour la vérification des doublons, (address) donne 0,030 ms et (address, type_id) WHERE status = 'open' donne 0,029 ms. Les deux sont rapides, car une adresse a peu de demandes. L’index partiel occupe la moitié de l’espace (3,39 Mo contre 7,69 Mo), donc il gagne.

Le tableau de bord des retards compte les demandes ouvertes dont l’échéance est dépassée. La plupart des demandes ouvertes sont en retard. Un index partiel sur sla_target_at ralentit donc la requête : 53,8 ms contre 35,1 ms sans index. Le rapport mensuel lit une année entière. Aucun des deux n’obtient d’index. Environ 35 ms toutes les quelques minutes et 0,3 seconde une fois par mois conviennent. S’ils prennent plus de temps, exécutez-les selon un horaire ou sur une copie de la base, sans ajouter d’index.

Le coût des index

Chaque écriture doit mettre à jour chaque index. L’actualisation nocturne ferme environ 10 000 demandes et en ajoute 10 000 nouvelles. scripts.candidates l’a chronométrée dans des transactions qu’il a ensuite annulées :

L'actualisation (10 000 demandes fermées, 10 000 nouvelles) avec la clé primaire seulement, avec les 3 index choisis (45,8 Mo) et avec les 10 candidats (109,2 Mo). Médiane de 3 exécutions.

Description textuelle du graphique

Un diagramme à barres verticales du temps d’actualisation. Clés seulement : 1,476 s. Les 3 index choisis, 45,8 Mo : 1,404 s. Les 10 candidats, 109,2 Mo : 1,771 s.

Avec les 3 index choisis, la différence restait dans les fluctuations de cet ordinateur partagé. L’actualisation était 5 % plus rapide dans cette exécution, et 9 % plus lente dans la deuxième. Les 10 candidats rendaient l’actualisation 1,2 et 1,4 fois plus lente dans les deux exécutions, sans accélérer les requêtes. Les index partiels aident ici aussi : une demande fermée les quitte, donc ils restent petits.

Erreur courante

L'index qui a ralenti la file

Symptôme : après l’ajout d’un index sur queue_id, la file de travail la plus chargée prend 42,5 ms au lieu de 29,2 ms. Les petites files sont devenues plus rapides, donc personne ne l’a remarqué au début.

Cause : l’index trouve toutes les demandes de la file, ouvertes ou fermées : 124 285 pour la plus chargée. La base de données récupère chaque ligne à un endroit aléatoire, puis filtre et trie. Pour autant de lignes, une lecture ordonnée de toute la table coûte moins cher. Un test sur une petite file montrait seulement le gain.

Diagnostic : mesurez la valeur la plus fréquente et une valeur peu fréquente, comme le fait la charge de travail. Puis lisez le plan de la plus lente avec python -m scripts.explain queue.

Correction : un index qui correspond à toute la requête : les colonnes avec =, puis l’ordre, et un WHERE pour les lignes voulues. Ici : (queue_id, opened_at, case_id) WHERE status = 'open'.

Étape 7 : migration 002 et comparaison équitable

Les gagnants vont dans une seule migration. CREATE INDEX CONCURRENTLY crée un index sans bloquer les écritures. Le fichier s’exécute donc hors d’une transaction, comme dans Migrations :

migrations/002_workload_indexes.sql (from line 7)

CREATE INDEX CONCURRENTLY IF NOT EXISTS requests_open_queue_idx
    ON service_requests (queue_id, opened_at, case_id) WHERE status = 'open';
CREATE INDEX CONCURRENTLY IF NOT EXISTS requests_area_idx
    ON service_requests (neighborhood, opened_at DESC, case_id DESC);
CREATE INDEX CONCURRENTLY IF NOT EXISTS requests_open_address_idx
    ON service_requests (address, type_id) WHERE status = 'open';

python -m boston311.migrate --to 002
python -m scripts.bench

La commande de migration a pris 0,89 s pour les trois index. Ensuite, la même charge de travail a utilisé les mêmes données, avec le même code :

Mode d’accès	Clés seulement	Avec 002
P1 une demande	0,953 ms	0,689 ms
P2 file de travail	59,5 ms	0,761 ms
P3 fil du quartier	62,0 ms	0,779 ms
P3 page éloignée, OFFSET 5000	63,5 ms	1,7 ms
P3 page éloignée, pagination par clé	63,4 ms	0,836 ms
P4 doublons	66,1 ms	0,678 ms
P5 retards	35,9 ms	30,2 ms
P6 rapport mensuel	360,5 ms	317,2 ms

Le banc d’essai mesure depuis Python. Ses temps incluent donc l’aller-retour vers la base : environ 0,7 ms, même pour une recherche par clé. Dans la base de données, les plans prennent beaucoup moins de temps. La page de la file de travail prend 0,028 ms. Le fil du quartier prend 0,020 ms. P2, P3 et P4 sont passés d’environ 60 ms à moins de 1 ms. P5 et P6 n’ont pas changé, comme prévu.

Avec l’index, la page éloignée confirme la leçon sur la pagination. OFFSET 5000 lit 5 050 entrées d’index et en jette 5 000 (rows=5050.00 dans son plan) : 1,0 ms dans la base. La page par clé saute directement à sa position : 0,020 ms, pour la page 2 ou la page 1 000.

Étape 8 : nouvelles données du nouveau système de la ville

Le problème : des données que le schéma n’a jamais vues

En août 2025, Boston a lancé un nouveau système 311. Son fichier a d’autres noms de colonnes et des heures en UTC avec un fuseau horaire. Ses identifiants de demande ressemblent à BCS-00253943. Il a six statuts : « Closed » (fermé), « In progress » (en cours), « Needs Reallocation » (réaffectation nécessaire), « Submit » (soumettre), « Apply » (appliquer) et « Create » (créer). La base de données de Rosa doit accueillir les nouvelles demandes à côté des anciennes. Le schéma refuse :

Output: one new-system row, inserted in psql

ERROR:  new row for relation "service_requests" violates check constraint "requests_case_id_format"

La règle était vraie pour l’ancien système. Le monde a changé, donc la règle doit changer aussi, avec une migration.

Migration 003 : changer une règle sans bloquer la table

ADD CONSTRAINT … CHECK lit chaque ligne en maintenant le verrou le plus fort. Aucune requête ne peut lire la table avant la fin. Sur une grande table, c’est encore le blocage de Migrations. PostgreSQL offre une méthode en deux étapes. NOT VALID ajoute la règle pour les nouvelles lignes seulement, avec un verrou bref. VALIDATE CONSTRAINT vérifie ensuite les anciennes lignes avec un verrou plus faible, permettant aux lectures et écritures de continuer.

migrations/003_new_case_system.sql (from line 10)

ALTER TABLE service_requests ADD COLUMN source_system text NOT NULL DEFAULT 'legacy';
ALTER TABLE service_requests
    DROP CONSTRAINT requests_case_id_format,
    ADD CONSTRAINT requests_case_id_format CHECK (
        (source_system = 'legacy' AND case_id ~ '^[0-9]{12}$')
        OR (source_system = 'bcs' AND case_id ~ '^BCS-[0-9]{8}$')
    ) NOT VALID;
ALTER TABLE service_requests VALIDATE CONSTRAINT requests_case_id_format;

Le fichier commence par -- migrate: no-transaction, donc chaque instruction est validée séparément. La validation ne garde pas le verrou de l’instruction précédente. La nouvelle colonne a une valeur par défaut constante. PostgreSQL la stocke donc une seule fois, sans réécrire les 582 849 lignes. La nouvelle règle est plus stricte que l’ancienne : chaque identifiant doit respecter le format de son propre système.

La commande de migration a pris 0,38 s ici. Sur cette table, la méthode sûre apporte peu de gains. Cette habitude compte quand la table est dix fois plus grande.

Charger la semaine, deux fois

scripts/load_new.py fait correspondre le nouveau fichier au schéma. « Closed » devient closed (fermé), et les cinq autres statuts deviennent open (ouvert). Le nom du service devient le type, et l’équipe devient la file. Il utilise INSERT … ON CONFLICT (case_id) DO UPDATE. Une demande déjà présente est donc mise à jour, plutôt qu’ajoutée de nouveau :

python -m boston311.migrate
python -m scripts.load_new data/boston_311_new_system_2026-10-01_07.csv
python -m scripts.load_new data/boston_311_new_system_2026-10-01_07.csv

Output (the two loads)

boston_311_new_system_2026-10-01_07.csv: 3,540 rows; 3,540 new requests, 0 updated, in 0.1 s.
boston_311_new_system_2026-10-01_07.csv: 3,540 rows; 0 new requests, 3,540 updated, in 0.1 s.

Exécutez maintenant tout le pipeline sur les nouvelles données. La vérification des doublons, P4, a comparé chaque nouvelle demande aux demandes ouvertes avant elle. 83 des 3 540 nouvelles demandes avaient une demande du même type ouverte à la même adresse dans les 30 jours précédents. Le plan de P4 utilise requests_open_address_idx et prend 0,25 ms. La charge de travail, exécutée de nouveau avec les nouvelles lignes, a gardé ses tendances : P2 1,0 ms, P3 0,9 ms, P4 0,8 ms.

Un constat ne concerne pas la vitesse. Le nouveau système nomme les quartiers différemment :

Output: one area, several names

              neighborhood              | legacy | new_system
----------------------------------------+--------+------------
 Allston                                |   2452 |        104
 Allston / Brighton                     |  45993 |          0
 Brighton                               |   4311 |        183
 South Boston                           |   5577 |        205
 South Boston / South Boston Waterfront |  47057 |          0

Le fil du quartier « South Boston / South Boston Waterfront » s’arrête en décembre 2025, mais le quartier reçoit encore des demandes. Aucune contrainte ne peut détecter cela : les deux noms sont du texte valide. Seule la question de l’inventaire, « comment lit-on ces données? », révèle le problème. Une table qui fait correspondre les noms aux quartiers sera la prochaine migration.

Étape 9 : sauvegarder et le prouver

La sauvegarde et l’exercice utilisent les scripts du cours de Restauration et conservation, avec deux nouvelles vérifications. Le manifeste stocke les réponses de trois requêtes de la charge de travail. L’exercice vérifie que la base restaurée donne les mêmes réponses et que la file de travail utilise encore son index.

python -m scripts.backup
python -m scripts.restore_drill backups/boston311-20261010-054448.dump

Sortie

Backup: backups/boston311-20261010-054448.dump (40.6 MB, 2.78 s)
Manifest: backups/boston311-20261010-054448.json: 3 migrations, case_types 228, queues 202, service_requests 586,389
A backup is not proven until a restore works: python -m scripts.restore_drill backups/boston311-20261010-054448.dump
PASS  dump matches its manifest
PASS  pg_restore finished: 4.0 s
PASS  same migrations: 3 of 3
PASS  same rows and contents in every table: case_types 228, queues 202, service_requests 586,389
PASS  every constraint and index is valid
PASS  the work queue uses its index
PASS  workload answers are the same: P2, P3 and P5
Restore drill: 7 of 7 checks passed.

Votre nom de fichier contient votre propre heure. Un constat réel a guidé l’exercice. Une base restaurée n’a aucune statistique pour le planificateur : pg_stats avait 0 ligne pour service_requests. Le compte des retards s’exécutait alors dans un seul processus, en 63,5 ms. Après ANALYZE, il utilisait deux processus parallèles et prenait 36,5 ms. L’exercice exécute donc ANALYZE avant de vérifier un plan. Votre procédure de restauration devrait faire de même.

Exécuter les tests

python -m pytest

Sortie

........................                                                 [100%]
24 passed in 0.35s

Les tests créent leur propre base, boston311_test, avec les migrations et 304 lignes réelles choisies selon une règle fixe (tests/fixtures/). Ils vérifient que le chargement garde chaque ligne et applique chaque règle de nettoyage. Ils vérifient, par son nom, que chaque contrainte rejette sa ligne invalide. Ils vérifient que les pages par clé ne sautent ni ne répètent jamais une ligne. Cela reste vrai même si une nouvelle demande arrive entre deux pages. Ils vérifient qu’une valeur ne devient jamais du SQL. Et ils vérifient que le chargement du nouveau système ne compte rien deux fois. Dans le projet de départ, les tests ont d’abord besoin de votre scripts/load.py.

Constats et limites

• L’inventaire a déterminé les index. Trois modes d’accès ont obtenu un index chacun; deux n’en ont reçu aucun, volontairement. P2, P3 et P4 sont passés d’environ 60 ms à moins de 1 ms dans la charge de travail.
• Mesurez les extrêmes. Un index semblait bon sur une petite file, mais ralentissait la grande. Un autre était rapide pour un quartier très actif et lent pour un petit quartier.
• Un index partiel convient à une requête sur un petit ensemble qui change, comme les demandes ouvertes. Il est plus petit et plus rapide, mais seules les requêtes avec la même condition peuvent l’utiliser.
• Une contrainte énonce un fait sur le monde. Comptez d’abord. Nettoyez une valeur qui a un seul sens clair, et retirez une règle que le monde ne respecte pas.
• Limites de ces chiffres : un portable partagé, des caches déjà remplis et 4 clients simultanés. Le coût d’écriture des 3 index restait dans les fluctuations. Un vrai service doit faire les mêmes tests sur son propre matériel.

Utilisation responsable

• Une demande n’est pas un problème. Plusieurs personnes signalent le même nid-de-poule. Comptez les demandes, et appelez-les des demandes.
• Qui fait les signalements. Le 311 montre où les gens signalent des problèmes, pas seulement où les problèmes se trouvent. Les employés municipaux ont fait 42 % des demandes de 2024. Ne classez pas les quartiers selon le nombre de demandes comme si ce nombre mesurait leur état ou leurs habitants.
• Un instantané. Les statuts sont ceux que la ville a publiés. Un tableau de bord doit montrer la date de l’instantané.
• Deux systèmes. L’ancien et le nouveau système ont des catégories et des noms de quartiers différents. Une tendance qui traverse août 2025 ne compare pas des données équivalentes.
• Texte libre et lieux. Les titres et les raisons de fermeture sont les textes de la ville. Les adresses désignent des logements et des entreprises. Le projet ne stocke aucun nom de personne ayant fait un signalement. Ne joignez pas les données à d’autres sources pour découvrir qui a signalé quoi.

Faites-en un projet de portfolio

Ce projet montre ce que les employeurs recherchent chez un ingénieur de données ou de systèmes dorsaux. Sa conception repose sur les modes d’accès. Ses contraintes sont vérifiées sur des données réelles. Ses migrations ne bloquent pas les accès. Ses index sont mesurés, et sa restauration est testée.

Quoi mettre dans le dépôt :

boston311-storage/
  README.md            what it is, the design choices, how to run and check it, the numbers
  DATASET.md           the source, the licence (PDDL), the files' SHA-256, the changes, the limits
  LICENSE              MIT, for the code
  docs/access-patterns.md
  migrations/          001 schema, 002 indexes, 003 the new system
  boston311/           config, migrate, db (the pool), staging, queries, workload
  scripts/             download, check_rules, load, load_new, bench, explain, candidates, backup, restore_drill
  tests/               24 tests and the two fixtures
  data/                the new-system week (the 2024 and 2025 files are downloaded)

1. Créez un dépôt Git à partir de votre dossier de projet, puis poussez-le vers GitHub. .gitignore exclut .env, les fichiers bruts et les sauvegardes.
2. Exécutez python -m scripts.bench --json results/after.json. Mettez vos tableaux avant et après dans le README, avec le nom de votre ordinateur.
3. Assurez-vous que python -m pytest réussit à partir d’un nouveau clone, et indiquez-le dans le README.

Comment le décrire en entrevue : « J’ai conçu le stockage PostgreSQL pour 583 000 demandes réelles du service Boston 311, à partir d’un inventaire des modes d’accès. J’ai d’abord vérifié chaque contrainte sur les fichiers bruts. Puis, j’ai choisi trois index à partir de mesures. Ils ont réduit les temps d’environ 60 ms à moins de 1 ms pour trois opérations. C’étaient la file de travail, le flux du secteur et la vérification des doublons. Un exercice de restauration avec sept vérifications confirme que la sauvegarde fonctionne. »

Pour aller plus loin : chacune de ces options est une bonne prochaine étape.

• Noms des quartiers. Une table qui associe les noms des deux systèmes à un même secteur, et une migration qui l’utilise.
• L’actualisation nocturne. Exécutez load_new chaque jour sur le fichier complet du nouveau système de la ville. Mesurez l’actualisation et la taille des index pendant une semaine.
• Le rapport mensuel. Une vue matérialisée par mois, actualisée la nuit. Mesurez P6 avant et après.
• Recherche sur une carte. Des demandes situées à une certaine distance d’un point, avec un index spatial (PostGIS). Mesurez cette recherche comme vous avez mesuré P3.

Vérification des connaissances

Vérifiez votre compréhension

Le rapport des règles a trouvé 464 lignes qui enfreignent « fermée si et seulement si elle a un motif de fermeture ». Il a aussi trouvé 262 lignes avec le district 0. Pourquoi une règle est-elle devenue une contrainte après nettoyage, mais pas l'autre?

Afficher la réponse

Le district 0 n’existe pas. Cette valeur a donc un sens clair : « inconnu ». Stocker NULL conserve ce sens, et la règle (un district de 1 à 22) est alors respectée. La règle du motif de fermeture ne tient pas dans la réalité. On a rouvert 143 demandes qui conservent leur ancien motif. On en a fermé 321 en bloc sans motif. Une contrainte rejetterait des demandes réelles, dont certaines sont dans les files des équipes. Les lignes restent donc, et la fiche du jeu de données les explique.

Vérifiez votre compréhension

Un index sur queue_id a ralenti la file la plus occupée (42,5 ms contre 29,2 ms). Quel index a corrigé cela, et pourquoi seules les requêtes sur les demandes ouvertes peuvent-elles l'utiliser?

Afficher la réponse

L’index partiel (queue_id, opened_at, case_id) WHERE status = 'open'. Il contient seulement les demandes ouvertes, déjà triées par file et par date. Les 51 premières entrées forment donc la page : 0,032 ms, pour 6 Mo. Le planificateur utilise un index partiel seulement si le WHERE de la requête implique la condition de l’index. La file de travail indique status = 'open'. Une requête sur les demandes fermées, ou sur toutes les demandes, ne peut pas l’utiliser.

Vérifiez votre compréhension

Avec seulement la page de Dorchester, un index sur opened_at semblait presque aussi bon que l'index composite (0,047 ms). Qu'a montré la page de Chestnut Hill, et qu'est-ce que cela enseigne sur les charges de travail?

Afficher la réponse

Avec (opened_at), Chestnut Hill a pris 25,9 ms, aussi longtemps que sans index. L’index parcourt toutes les demandes, des plus récentes aux plus anciennes. Chestnut Hill n’en a que 27, moins qu’une page. Le parcours traverse donc tout l’index. L’index composite (neighborhood, opened_at DESC, case_id DESC) était rapide pour les deux. Une charge de travail doit inclure les extrêmes de chaque mode d’accès : une valeur fréquente et une valeur rare. Sinon, elle mène au choix d’un index qui ne convient pas à certains utilisateurs.

Récapitulatif

• Commencez par l’inventaire des modes d’accès : P1 à P6, qui lit quoi, à quelle fréquence et en quelle quantité.
• Préparez les fichiers bruts sous forme de texte, et comptez les lignes qui enfreignent chaque règle envisagée. Appliquez la règle, nettoyez puis appliquez-la, ou acceptez que ce n’est pas une règle.
• Chargez les données dans une seule transaction, avec les règles de nettoyage en SQL et les heures interprétées dans le fuseau horaire de la ville.
• Mesurez une charge de travail fixe, puis les index envisagés un à un, avec des valeurs fréquentes et rares. Trois ont gagné : un index partiel pour la file de travail, un composite pour le flux du secteur, un partiel pour la vérification des doublons.
• Modifiez les règles sans risque pour les nouvelles données : une valeur par défaut constante, NOT VALID, puis VALIDATE. Chargez avec ON CONFLICT, pour qu’un deuxième chargement n’ajoute rien.
• Vérifiez la sauvegarde avec un exercice qui vérifie aussi les plans et les réponses, puis exécutez ANALYZE après une restauration.

Leçon suivante : Recherche vectorielle dans une collection de documents ouverte