Vérifier que SQLAlchemy ne fait pas n'importe quoi #107

Rien de très violent, mais quelques problèmes récents (genre #105 et l'histoire du nombre de topics des membres) montrent qu'on ne sait pas vraiment ce que SQLAlchemy fait de nos modèles. Faudrait qu'on puisse contrôler que c'est pas n'importe quoi.

Plan :

Vérifier que la structure de la base PostgreSQL est correcte
Trouver un moyen de debugger les requêtes
Vérifier quelques requêtes qui manipulent beaucoup de données
Documenter la méthode pour savoir comment procéder quand dans le futur on aura des problèmes de perfs

Il faudrait trouver un équivalent de la Django Debug Toolbar pour flask-sqlalchemy, ça permettrait de timer les requètes SQL, et tout ça.

Cheatsheet pour la vérification dans PostgreSQL.

Afficher les détails de chaque table (\d+ <table>). Vérifier que les champs, les index ainsi que les contraintes répondent au modèle mental qu'on a de la bdd.

Liste des tables testées avec les infos non triviales :

attachment (pas d'index sur comment_id)
comment (index sur thread_id)
forum
group (index sur name pour l'unicité)
group_member (pas de clé primaire)
group_privilege
guest
member (index sur email, name et norm pour l'unicité)
notification (pas d'index sur owner_id)
poll (choices est encodé en bytearray moche)
pollanswer (pas d'index sur poll_id, choice est encodé en bytearray moche)
post (pas d'index sur author_id, ni sur date_created/date_modified)
program
special_privilege (index sur mid)
thread (owner_post non matérialisé)
title
topic (pas d'index sur forum_id)
trophy (index sur name et description - ?)
trophy_member (pas de clé primaire, pas d'index sur uid)
user

Bonne nouvelle donc, la base PostgreSQL est pas mal. La seule vériable inconnue donc c'est la façon dont SQLAlchemy génère ses JOIN pour les requêtes, ce qu'on peut toujours ajuster à la main dans le code.

En attendant, pour les problèmes que j'ai notés en gras :

Pas d'index sur attachment.comment_id : nécessaire pour lister les pièces jointes d'un message donné.
Pas d'index sur notification.owner_id : nécessaire pour lister les notifications d'un membre connecté.
Pas d'index sur pollanswer.poll_id : nécessaire pour calculer les résultats.
Pas d'index sur post.author_id : probablement nécessaire pour lister les topics, programmes, etc. d'un membre connecté.
Pas d'index sur post.date_created/post.date_modified : nécessaire pour lister les topics récents.
**thread.owner_post**pas matérialisé : il doit donc être obtenu par une requête. On s'en sert pas souvent dans le code mais ultimement pour chaque message affiché sur une page de forum on teste si l'utilisateur peut le modifier/supprimer, ce qui va chercher son topic parent, ce qui passe par là.
Pas d'index sur topic.forum_id : nécessaire pour afficher les pages de chaque forum.
Pas d'index sur trophy.uid : nécessaire pour lister les trophées d'un membre donné.

Cheatsheet pour la vérification dans PostgreSQL. Afficher les détails de chaque table (`\d+ <table>`). Vérifier que les **champs**, les **index** ainsi que les **contraintes** répondent au modèle mental qu'on a de la bdd. Liste des tables testées avec les infos non triviales : - [x] `attachment` (**pas d'index sur `comment_id`**) - [x] `comment` (index sur `thread_id`) - [x] `forum` - [x] `group` (index sur `name` pour l'unicité) - [x] `group_member` (pas de clé primaire) - [x] `group_privilege` - [x] `guest` - [x] `member` (index sur `email`, `name` et `norm` pour l'unicité) - [x] `notification` (**pas d'index sur `owner_id`**) - [x] `poll` (`choices` est encodé en bytearray moche) - [x] `pollanswer` (**pas d'index sur `poll_id`**, `choice` est encodé en bytearray moche) - [x] `post` (**pas d'index sur `author_id`, ni sur `date_created`**/**`date_modified`**) - [x] `program` - [x] `special_privilege` (index sur `mid`) - [x] `thread` (**`owner_post` non matérialisé**) - [x] `title` - [x] `topic` (**pas d'index sur `forum_id`**) - [x] `trophy` (index sur `name` et `description` - ?) - [x] `trophy_member` (pas de clé primaire, **pas d'index sur `uid`**) - [x] `user` Bonne nouvelle donc, la base PostgreSQL est pas mal. La seule vériable inconnue donc c'est la façon dont SQLAlchemy génère ses `JOIN` pour les requêtes, ce qu'on peut toujours ajuster à la main dans le code. En attendant, pour les problèmes que j'ai notés en gras : - Pas d'index sur **`attachment.comment_id`** : nécessaire pour lister les pièces jointes d'un message donné. - Pas d'index sur **`notification.owner_id`** : nécessaire pour lister les notifications d'un membre connecté. - Pas d'index sur **`pollanswer.poll_id`** : nécessaire pour calculer les résultats. - Pas d'index sur **`post.author_id`** : probablement nécessaire pour lister les topics, programmes, etc. d'un membre connecté. - Pas d'index sur **`post.date_created`**/**`post.date_modified`** : nécessaire pour lister les topics récents. - **`thread.owner_post`**pas matérialisé : il doit donc être obtenu par une requête. On s'en sert pas *souvent* dans le code mais ultimement pour chaque message affiché sur une page de forum on teste si l'utilisateur peut le modifier/supprimer, ce qui va chercher son topic parent, ce qui passe par là. - Pas d'index sur **`topic.forum_id`** : nécessaire pour afficher les pages de chaque forum. - Pas d'index sur **`trophy.uid`** : nécessaire pour lister les trophées d'un membre donné.

Puisque j'ai ajouté tous les index, et qu'avec la Flask Debug Toolbar on a bien optimisé le temps de génération (qui est dominé par la BDD) en réduisant notamment le risque du « problème des N+1 requêtes » via des lazy="join" bien placés, je ferme cette issue. On pourra refaire du tuning plus tard.