Stocker les informations "Élément lu / en signet" avec PostgreSQL

Salut les agrumes,

Je m’amuse en ce moment à me développer mon propre lecteur RSS, et ce faisant j’arrive face à une problématique sur laquelle j’aimerais avoir votre avis.

J’ai mes données en base de données (du PostgreSQL pour être exact), et mon instance principale est destinée à être hébergée sur une machine assez mal pourvue en terme d’I/O disque. C’est un lecteur RSS multi-utilisateurs : chaque utilisateur voit seulement les flux qu’il a choisi de voir, et l’état de lecture n’influe pas sur les autres utilisateurs (je peux marquer un élément comme lu sans qu’il le soit pour mes amis).

J’ai donc besoin de savoir, pour chaque entrée de flux, si l’utilisateur présentement connecté a déjà lu ou non cette entrée de flux. De la même manière, chaque entrée de flux peut être mise en signet pour être facilement retrouvée (signets à plat, sans dossier). Le but c’est d’arriver à faire en sorte que ça fonctionne sans que le système explose en vol dès que je vais essayer de monter en puissance. Les volumétries sont : des centaines de milliers d’entrées de flux à terme ; idéalement des dizaines d’utilisateurs sans trop se poser de question existentielle. Statistiquement, pour N utilisateurs dans le système et E entrées, on devrait avoir environ (E/N) entrées lues par utilisateur – je pars du principe que les ensembles de flux de chaque utilisateur se recoupent assez mal, et qu’il y a peu de flux qui seront lus par tout le monde. Je suppose aussi que les utilisateurs sont actifs et lisent presque tous les éléments auxquels ils sont abonnés. Enfin, il n’y a pas de ménage des vieilles entrées.

Je vois trois solutions :

1. Une table read_entry (et une autre bookmarked_entry qui contiennent uniquement la paire (entry_id, member_id). L’entrée est considérée comme lue (respectivement en signet) pour l’utilisateur s’il y a une entrée dans cette table. C’est optimal en terme de stockage, mais de ce que j’ai compris de PostgreSQL, inefficace en terme de requêtes, parce que ça implique à faire des left outer join sur ces tables en recherchant les résultats null, ce que le moteur SQL ne saurait pas optimiser.
2. Une table member_entry avec entry_id, member_id et deux booléens read et bookmarked. Toutes les paires (membre, entrée de flux) y sont sauvegardées et il suffit de filtrer sur l’un ou l’autre des booléens pour faire la requête, ce qui peut s’indexer très facilement. Par contre, cette table est par définition un produit cartésien et peut atteindre rapidement une taille considérable (je pars du principe qu’on a pas à gérer les cas où elle est mal remplie).
3. Et une solution non applicable qui consiste à dupliquer chaque entrée pour chaque utilisateur (si N personne sont abonnées au flux, chaque entrée du flux est présente N fois). En particulier parce que je stocke le contenu complet des entrées, ce qui peut vite faire exploser le truc avec des flux qui contiennent les articles entiers.

Pour l’instant je n’ai aucun vrai problème de performances (un utilisateur, 50 000 entrées), mais quand même je serais curieux de savoir ce que vous vous choisirez (ça peut être une solution 4 à laquelle je n’ai pas pensé) pour être paré dans le futur, surtout si je choisis d’ouvrir l’instance à pas mal d’autres de personnes (par exemple, des membres de ZdS).

Salut,

Dans ta solution 1, tu as aussi besoin d’une table member_entry où tu aurais les entrées auxquelles l’utilisateur est "abonné" mais n’a ni lu, ni mis en signet, non ? Donc tu as le même problème que dans le deuxième cas où cette table aura potentiellement un grand nombre d’entrées.

Mais de toute façon, je trouve que la deuxième solution me semble la meilleure. Elle est claire et les requêtes seront simples à écrire et comme tu le dis, Postgresql pourra indexer ça très facilement. Et je ne me fais pas de souci pour la taille, je pense que Postgresql peut facilement gérer un nombre considérable d’entrées pour une table si simple. Mais tu peux aussi faire un test en chargeant un set conséquent de données (par exemple, en utilisant generate_series) et voir ce que ça donne. Et même si un jour ta table devient trop grande, tu pourras toujours la partitionner (par member_id par exemple).

Non, parce que le lien « normal » entre un utilisateur et les entrées de flux est : utilisateur --(N..M)-> flux --(0..N)-> entrée (un utilisateur peut voir toutes les entrées de tous les flux auquel il s’est abonné).

je pars du principe que les ensembles de flux de chaque utilisateur se recoupent assez mal

Donc normalement, ta solution 3 pourrait être appliquée.

Personnellement, je pense que ta première solution est la meilleure d’un point de vu relationnel. Je ne comprend pas bien cette histoire de rechercher “les résultats null” et d’optimisation. Je vois que ce sont des indexes et qu’à-priori le moteur SQL devrait savoir optimiser ce cas là.

Je préfère éviter de toutes façons pour éviter de devoir dupliquer des lignes entières à la souscription d’un nouvel utilisateur sur un flux existant.

Justement : on recherche les éléments d’une table qui ne sont pas dans une jointure donnée, dont par définition c’est quelque chose qui n’est pas indexable. D’où ma réflexion.

J’ai fait pas mal de tests (j’espère que mon SSD n’a pas trop pris cher…) et j’ai opté pour une solution 4.

J’ai créé une table de « statistiques » dénormalisée avec les champs (utilisateur, catégorie, feed, entrée, lu, en signet). Elle me permet de faire directement tous les regroupements dont j’ai besoin, en particulier ceux pénibles du style « Lister toutes les catégories de cet utilisateur avec le nombre d’entrées non lues dans chaque catégorie ». Ça demande un peu de boulot pour garder la table cohérente et à jour (comme toute donnée dénormalisée), mais c’est toujours mieux que des requêtes tordues pour avoir les données ou une table qui serait un vrai produit cartésien – que je ne peux avoir que dans le pire des cas, celui où j’aurais tous les utilisateurs qui seraient abonnés à tous les flux, et donc une ligne par paire (utilisateur, entrée).

PostgreSQL étant très performant, je peux mettre beaucoup d’entrées dans cette table (j’ai un test à plus de 100 millions) tout en gardant des temps de réponses très satisfaisant tant que j’ai les index qui vont bien. Ce qui veut dire qu’avec cette solution, j’aurai d’autres problèmes avant d’être limité par les performances de mes requêtes.

À ce sujet, j’ai l’impression que je suis presque obligé de créer un index ad hoc pour chaque requête différente, ce qui est lourd et risque de ralentir sérieusement les insertions.

Mais j’ai essayé avec un index général, ou avec un index par colonne, et aucun de ces cas n’est capable de me donner de bonnes performances. Même si le plan d’exécution me monter bien que deux index sont utilisés (avec un « et » logique entre les deux), le résultat est très sous-optimal par rapport à un index qui contient toutes et uniquement les colonnes nécessaires à la requête.

Si quelqu’un ici s’est déjà penché sur cette question, ça m’intéresse.

PS : En fait, l’union d’index individuels est parfois efficace et parfois non… j’ai l’impression que ça dépend de la cardinalité des index en question. Dans tous les cas, le plus lent (et de loin) reste la récupération de données « froides » au fond d’une grosse table (les colonnes non indexées et qui ne sont pas en cache parce que pas lues depuis longtemps).

PPS : Je ne sais pas comment PostgreSQL se débrouille, mais contrairement à ce que j’avais lu, les requêtes du type ... t1 left outer join t2 on t1.a = t2.a where t2.b is null ont des performances très correctes. Par contre elles restent verbeuses et délicates à comprendre. C’est peut-être quelque chose qui a été rendu efficace entre la version concernée par mes lectures (dont je ne me souviens plus) et la version 14 que j’utilise.