Discussion :

[biatho]

Bonjour à tous,

Je vous présente mon soucis :
Je dois gérer un site d'ecommerce (pour le fun ). Des utilisateurs se connectent et réalisent des commandes. Une des specs est de conserver un historique de toutes les commandes réalisées par cette utilisateur. Cependant je me retrouve dans le cas où l'utilisateur supprime son compte mais je souhaite tout de même conserver son historique de commande afin d'établir des statistiques de commandes sur une longue durée.

J'ai pensé tout d'abord faire une table Historique où sont stockés l'id d'une commande, l'id de l'utilisateur et une date. Cependant si l'utilisateur supprime son compte l'id dans la table Historique n'a pas plus de références. Par conséquent je perds le tupple associé...
Dans un deuxième temps, je pensais faire 2 tables de "mapping", une entre utilisateur et historique et l'autre entre commande et historique résolvant mon problème de perte d'id dans la table historique mais cela permet d'associer 1 historique de commande à 2 utilsateurs différents (cas impossible et non désiré).

Je voulais donc savoir si vous aviez une idée ..?

[champomy62]

Bonjour,

De nos jours, nous ne supprimons plus vraiment une donnée. Si ton utilisateur supprime son compte, tu as plusieurs possibilités pour ca mais la plus simple, c'est d'avoir un colonne 'is_deleted'. Si ton utilisateur supprime son compte, tu mets a jour cette colonne, et s'il se réinscrit tu active cette colonne et en plus il retrouvera son historique de commande si tu en as envie.

[biatho]

Le soucis est que je travaille sur projet un peu confidentiel et que l'histoire du site est juste un cas similaire à mon véritable soucis. On ne supprime peut être plus les données de nos jours mais ma table "utilisateur" génère près de 4 Tera de données par jour. Cette table est analysée par les utilisateurs qui vont ensuite la flusher ...

[Waldar]

Ceux qui ont fait la modélisation de votre application l'ont très probablement ratée pour générer 4 To de données par jour.

[champomy62]

Ma première réaction a été comme celle de Waldar.

Etes-vous sur du chiffre avancé ? Comment l'avez-vous obtenu ? Quel SGBDR ?

Je vais revenir pour répondre à ta question initiale.

[champomy62]

L'historisation n'est pas quelque chose de simple.


Tu as proposé une solution:

Dans un deuxième temps, je pensais faire 2 tables de "mapping", une entre utilisateur et historique et l'autre entre commande et historique résolvant mon problème de perte d'id dans la table historique mais cela permet d'associer 1 historique de commande à 2 utilsateurs différents (cas impossible et non désiré).

Tu peux faire un trigger ou une simple requête pour vérifier cette contraire. Si historique n° 1233454 a déjà un utilisateur, alors tu empêche l'enregistrement.

Sinon tu crée une table 'historique_utilisateurs' et une autre 'historique_commandes'. Et les deux tables sont reliées uniquement entre-elles.
Donc même si un utilisateur supprime son compte tu aura l'historique de ses commandes. Et tes statistiques ne se feront que sur les tables d'historiques.
Mais cela demande beaucoup plus de boulot et tu maintiens les données en double.

Je reste quand même sur la première solution que je t'ai donné.

A voir les solutions possibles si un expert passe par ici.

[SQLpro]

Bonjour à tous,

Je vous présente mon soucis :
Je dois gérer un site d'ecommerce (pour le fun ). Des utilisateurs se connectent et réalisent des commandes. Une des specs est de conserver un historique de toutes les commandes réalisées par cette utilisateur. Cependant je me retrouve dans le cas où l'utilisateur supprime son compte mais je souhaite tout de même conserver son historique de commande afin d'établir des statistiques de commandes sur une longue durée.

J'ai pensé tout d'abord faire une table Historique où sont stockés l'id d'une commande, l'id de l'utilisateur et une date. Cependant si l'utilisateur supprime son compte l'id dans la table Historique n'a pas plus de références. Par conséquent je perds le tupple associé...
Dans un deuxième temps, je pensais faire 2 tables de "mapping", une entre utilisateur et historique et l'autre entre commande et historique résolvant mon problème de perte d'id dans la table historique mais cela permet d'associer 1 historique de commande à 2 utilsateurs différents (cas impossible et non désiré).

Je voulais donc savoir si vous aviez une idée ..?

Il est dommage que vous soyez sur PostGreSQL piour ce faire, car la norme SQL 2011 à prévue cela avec les "temporal tables" technique aujourd'hui présente dans tous les bons SGBDR (Oracle, IBM DB2, SQL Server 2016...).
http://cs.ulb.ac.be/public/_media/te...ressql2011.pdf

Tout cela n'est pas si simple... En effet il faut pouvoir disposer d'opérateurs spécialisés pour retrouver la temporalité des données (Clause FROM étendue du SELECT avec FOR SYSTEM_TIME et les opérateurs AS.. OF..., TO..., BETWEEN..., CONTAINED...), les données évoluant de manière asynchrones dans les différentes tables lors des mises à jour...
A lire par exemple pour DB2 : http://www.ibm.com/developerworks/da...2temporaldata/

Je suis en cours d'écriture d'un article sur le sujet, qui devrait compter environ 60 pages (j'en suis à 42 !!!).

Vous pouvez cependant vous intéresser au livre de Snodgrass qui fait le tour de la question et est disponible en ligne : http://hornad.fei.tuke.sk/~genci/Vyu...20in%20SQL.pdf

A +

[biatho]

Bonjour,

Tout d'abord merci à tous pour vos réponse !!

Pour répondre au "4 Tera", j'ai bien eu confirmation qu'à terme les données générées seront de cette taille. J'aimerais bien vous expliquer pourquoi mais le contexte dans lequel je travaille ne me le permet pas. Pour faire au plus simple, imaginez que je doive enregistrer l'historique de déplacement, les actions, les comportements de près de 500 objets. Si je perds la connexion avec un des objets et quil n'a pas été reconnue je dois stocker cet objet pour identification ultérieure. Après cette deuxième passe, la table est vidée mais je souhaite conserver les historiques et tout le tintoin qui va avec des objets identifiés. Je ne suis peut être pas très clair mais ceci est mon véritable problème.

Je vais me pencher sur le livre de SQLPro car la technique "is_deleted" n'est malheureusement pas applicable. (je l'ai testée).

Merci encore !!

[SQLpro]

Bonjour,

Tout d'abord merci à tous pour vos réponse !!

Pour répondre au "4 Tera", j'ai bien eu confirmation qu'à terme les données générées seront de cette taille. J'aimerais bien vous expliquer pourquoi mais le contexte dans lequel je travaille ne me le permet pas. Pour faire au plus simple, imaginez que je doive enregistrer l'historique de déplacement, les actions, les comportements de près de 500 objets. Si je perds la connexion avec un des objets et quil n'a pas été reconnue je dois stocker cet objet pour identification ultérieure. Après cette deuxième passe, la table est vidée mais je souhaite conserver les historiques et tout le tintoin qui va avec des objets identifiés. Je ne suis peut être pas très clair mais ceci est mon véritable problème.

Je vais me pencher sur le livre de SQLPro car la technique "is_deleted" n'est malheureusement pas applicable. (je l'ai testée).

Merci encore !!

Ah ! c'est donc 4 To à terme et non par jour... Sinon, vous alliez battre le record mondial ! Actuellement je pense que c'est la base de Pann Starrs qui est la plus grosse du monde... Pour info : 400 To par an sur 12 clusters SQL Server...

Quelques remarque au sujet de :
1) vous parlez d'objets connectés
2) vous parlez d'une volumétrie dite VLDB
Dans ce cas je vous conseille fortement d'abandonner PostGreSQL au profit d'un SGBDR mieux calibré. En effet :

• qui dit objet connecté implique un fonctionnement 24h/24h avec peu d'heures creuses et surtout AUCUNE interruption !
• qui dit plusieurs dizaines à centaines de To, implique des outils efficace de gestion des volumes de données

Malheureusement PostGreSQL ne possède ni l'un ni l'autre... En effet :
1) La gestion du MVCC de PostGreSQL sur de forts volumes exige un arrêt du serveur pour maintenance (nettoyage des lignes dupliquées) ce que fait "le bon coin" l'une des plus grosse BD PostGreSQL
2) PostGreSQL ne sait pas faire des requêtes multithreadées. Avec un fort volume de données, ceci est impératif (parallélisation d'exécution des requêtes, des E/S...)
3) PostGreSQL ne sait pas faire certaines opérations "OnLine", par exemple l'ajout de colonne, la modification d'une table... etc. Sur de fort volume, créer un index, ajouter une colonne ou une contrainte bloque la table parfois TRÈS LONGTEMPS. Ceci est incompatible avec un service continu des données. La plupart des bons SGBDR savent faire de l'indexation ON LINE (PG commence tout juste à savoir le aire, mais c'est encore très limité) et du DDL ONLINE (ALTE TABLE par exemple)
4) PostGreSQL ne dispose pas d'un système de stockage intégré, permettant de gérer les volumes, faire du "capacity planning", placer des storages en READ ONLY, effectuer du partitionnement à la volée et à chaud... Tout choses indispensable pour gérer de forts volumes de données. En particulier le partitionnement dans PostGreSQL est inepte et inutilisable en pratique. À me lire : Partitionner une table : comparaisons PostGreSQL vs SQL Server
5) Bien que PostGreSQL dispose d'un embryon de moyen pour assurer un basculement d'un serveur à l'autre en cas de crash, ceci doit être fait manuellement et par conséquent induit une indisponibilité d'autant plus grande qu'il faudra trouver le DBA et lui demander d'agir après analyse de la situation... Avec d'autres SGBDR, le basculement est automatique voir synchrone (pas de perte de données) et immédiat (pas de latence). C'est en particulier le cas de SQL Server avec les technique du mirroring ou de "AlwaysOn".

En conclusion, je vous conseille de revoir votre projet et de changer de SGBDR... Sinon vous allez au devant de problèmes insolubles et couteux, bien plus qu'une simple licence d'un bon SGBDR !

En sus, vous aurez votre historisation automatique et le top serait que cette dernière soit sur une partition différente, voir une multi partition avec des partitions en Read Only pour les données les plus anciennes...
Le moins cher et de loin étant MSQ SQL Server qui assure toutes ces fonctions avec un cout de licence de 2 à 24 fois moins cher qu'Oracle et 4 à 50 fois moins cher que DB2. Vous pouvez tester SQL Server 2016 dont la version beta 2.3 est disponible pour tests : http://www.microsoft.com/en-us/downl....aspx?id=48119

Pour information, un de mes clients utilise SQL Server pour gérer et analyser le déplacement d'une flotte d'objet de l'ordre du 50 000, en relevant les points de passage environ toute les 3 secondes. Conservé sur trois jour en ligne, cela représente plus de 2 milliards de ligne... et 100 Go... L'historique plus ancien étant géré dans des partitions par semaines de l'ordre de 200 Go chacune...

A +

[champomy62]

C'est sur que SQL SERVER est bien équipé par default. +1

5) Bien que PostGreSQL dispose d'un embryon de moyen pour assurer un basculement d'un serveur à l'autre en cas de crash, ceci doit être fait manuellement et par conséquent induit une indisponibilité d'autant plus grande qu'il faudra trouver le DBA et lui demander d'agir après analyse de la situation... Avec d'autres SGBDR, le basculement est automatique voir synchrone (pas de perte de données) et immédiat (pas de latence). C'est en particulier le cas de SQL Server avec les technique du mirroring ou de "AlwaysOn".

Source: http://www.channelbp.com/content/la-...vec-postgresql

[champomy62]

D'ailleurs le partitionnement a-t-il était améliore dans les dernières versions de postgres ?

[SQLpro]

C'est sur que SQL SERVER est bien équipé par default. +1

Source: http://www.channelbp.com/content/la-...vec-postgresql

PG Pool c'est plus fait pour du load balancing et répartition de charge que de la haute dispo. Cela toujours parce que PG ne sait pas faire des requêtes parallélisé sur plusieurs thread au sein d'une même instance. Et a vouloir trop répliquer sur différents nœuds le système s'écroule en mode synchrone ou bien n'est pas utilisable en basculement auto sans perte de données.

par exemple avec SQL Server et pour des raisons de performance, il n'est pas possible d'avoir plus de 2 à 4 nœuds synchrone en HA...

Il n'y a pas de miracle !

A +

[biatho]

Merci beaucoup pour toutes ces informations très pertinentes !! j'en prends note et je fais mes tests de mon côté

Juste pour info, mes cours de BDD remontent un peu, changer à la volée une clef étrangère dans un tupple ne viole aucune contrainte de BDD ?

Merci encore !!

[Waldar]

changer à la volée une clef étrangère dans un tupple ne viole aucune contrainte de BDD ?

Si vous parlez de modifier la valeur, tant qu'elle est bien présente dans la table référencée il n'y a aucun problème.

Si vous parlez de changer la définition de la clef, c'est un autre problème qu'il faudrait décrire plus.

[estofilo]

En conclusion, je vous conseille de revoir votre projet et de changer de SGBDR... Sinon vous allez au devant de problèmes insolubles et couteux, bien plus qu'une simple licence d'un bon SGBDR !

C'est le pipeau habituel que tu ressers à chaque fois fois, plus ou moins copié-collé. Au mieux tu es mal informé, au pire tu le fais exprès.

Si on te suit, la réponse à toute question sur PostgreSQL est d'acheter SQL-server à la place.

Si ces réponses aident quelqu'un, ça ne doit pas tellement être ceux qui posent ces questions, mais plutôt ceux qui ont un produit à fourguer (et d'ailleurs plusieurs produits, parce qu'il faut faire tourner SQL server sur le seul système qui le supporte, du même vendeur).

[SQLpro]

C'est le pipeau habituel que tu ressers à chaque fois fois, plus ou moins copié-collé. Au mieux tu es mal informé, au pire tu le fais exprès.

Si on te suit, la réponse à toute question sur PostgreSQL est d'acheter SQL-server à la place.

Si ces réponses aident quelqu'un, ça ne doit pas tellement être ceux qui posent ces questions, mais plutôt ceux qui ont un produit à fourguer (et d'ailleurs plusieurs produits, parce qu'il faut faire tourner SQL server sur le seul système qui le supporte, du même vendeur).

Je veut bien être gentil, mais cite moi une seule entreprise qui utilise un serveur PG 24h/24 7j/7 pour une base de données relationnelle avec forte volumétrie ?
Moi j'en connais aucune et les exemples que j'ai eu ont été catastrophique. Pour info l'une de ceux que j'ai passé de PG à SQL Server est même cité comme bonne expérience avec PG alors que ça a tellement foiré que l’éditeur lui même disait qu'il fallait éviter un SGBDR et passer avec des données en fichier !!!!


Aujourd'hui le système de surveillante des crues du grand delta du Rhône (SPCGD AQUAREEL) est avec SQL Server 2008 et n'a connu aucune interruption de service depuis le début de sa mise en place, le passage des service PACK et autres mises à jour se faisant alternativement entre les deux serveur dupliqués en live par bascule entre les serveurs ...
Paratronic a du abandonné son système qui aujourd'hui est développé par Synapse...
Ce système dont j'ai été le conseillé est aujourd'hui implanté ou en cours d'implantation dans une dizaine de SPC en France, mais aussi depuis peu à l'étranger et remplace même des systèmes basés sur Oracle horriblement couteux en licences...

Pour info il m'arrive de prêcher en faveur de PostreSQL plus que tu ne le croit. Il suffit d'aller voir dans d'autres forums !
http://www.developpez.net/forums/d10...geographiques/
http://www.developpez.net/forums/d95...estion-ventes/
http://www.developpez.net/forums/d10...sql-arguments/
http://www.developpez.net/forums/d15...ordonnees-gps/
http://www.developpez.net/forums/d15...seule-requete/
http://www.developpez.net/forums/d15...server-2003-a/
...

J'essaye d'être honnête et pas intégriste du libre, comme hélas beaucoup de ceux qui défendent le libre et ne voit pas que ce libre est finalement récupéré par des entreprises comme EntrepriseDB lorsqu'il a atteint ses limites (parallélisme, pas de hint de requêtes, peu d'outil d'admin audit tuning ..)


A +

[CetTer]

Cependant je me retrouve dans le cas où l'utilisateur supprime son compte mais je souhaite tout de même conserver son historique de commande afin d'établir des statistiques de commandes sur une longue durée.

Vous pouvez peut-être contacter les développeurs d'Ashley Madison, j'ai cru comprendre qu'ils avaient résolu de façon statisfaisante ce cas de figure; sinon légalement, du moins techniquement parlant.

Sinon vous pourriez aussi essayer un triggeur sur les UPDATE qui logue les anciennes valeurs dans une table unique avec champ "hstore" dans les clés sont les colonnes modifiées, associées à un timestamp et la clé du tuple. Même si celui-ci disparaît il devrait être possible de comparer les valeurs entre elles. J'ai été amené à travailler sur un base PG ayant ce type de système, je m'attendais à des performances catastrophiques, mais cela était encore acceptable.
Mais avec 4TA de données par jour (il s'agît des texture bitmap pour reconstruire Paris à l'échelle 1 en imprimante 3D?), je crains que même les solution commerciales gérant nativement le partionnement aient un peu du mal à passer à l'échelle.

[estofilo]

Il n'y a pas de problème particulier en SQL à supprimer une référence d'une commande à un client.
C'est directement supporté par la clause on delete set null d'une contrainte d'intégrité référentielle.

Par exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
create table client(id int primary key);
 
create table commande (
   id_commande serial,
   id_cli int,
   date_commande timestamptz default now()
);
 
alter table commande add constraint lien_client foreign key (id_cli)
   references client(id) on delete set null;
 
-- création d'un client
insert into client values(1);
insert into commande(id_cli) values(1);
 
-- Ce qu'il y a dans  commande
select * from commande;
 id_commande | id_cli |         date_commande         
-------------+--------+-------------------------------
           1 |      1 | 2015-09-04 19:43:45.550832+02
 
-- effacement du client
delete from client where id=1;
 
-- Ce qu'il y a maintenant dans  commande
select * from commande;
 id_commande | id_cli |         date_commande         
-------------+--------+-------------------------------
           1 |        | 2015-09-04 19:43:45.550832+02

Résultat: la commande est encore là, mais sans numéro de client.

Une table historique n'est pas nécessaire pour arriver à ce résultat.

[estofilo]

mais cite moi une seule entreprise qui utilise un serveur PG 24h/24 7j/7 pour une base de données relationnelle avec forte volumétrie ?

Je ne collectionne pas les "use case", je suis développeur, pas commercial, mais puisque tu cites
le bon coin, regardons leur dernière présentation:
http://fr.slideshare.net/jlb666/pgda...ez-leboncoinfr

Je ne sais pas d'où sort cette histoire d'arrêter la ou les bases la nuit, peut-être quand ils ont démarré en 2007,
mais il n'en est pas question dans les présentations récentes.

Quant à des projets partis en vrille sur une techno X et repris en main sur une techno Y, ça arrive tout le temps quelque que soit X ou Y, et la majeure partie du temps c'est à cause de l'incompétence et des méconnaissances des personnes/des organisations, et non de la techno X ou Y.

[SQLpro]

Je ne collectionne pas les "use case", je suis développeur, pas commercial, mais puisque tu cites
le bon coin, regardons leur dernière présentation:
http://fr.slideshare.net/jlb666/pgda...ez-leboncoinfr

Je ne sais pas d'où sort cette histoire d'arrêter la ou les bases la nuit, peut-être quand ils ont démarré en 2007,
mais il n'en est pas question dans les présentations récentes.

Je leur avait posé la question il y un un peu plus d'un an... Et c'est ce qu'il m'ont répondu.
Si tu passe une annonce la nuit elle est mise en fichier dans une file d'attente et sera absorbée lorsque le serveur PG sera de nouveau accessible une fois la phase de maintenance terminée.
Cette phase est rendue nécessaire car AUTOVACUUM pose trop de problème et génère de multiples deadlocks. Aussi font_ils le nettoyage des versions de ligne obsolètes du MVCC la nuit.

Tu remarqueras trois choses :
1) il y a 20 serveur PG (une gabegie d'argent sur le hardware : DL 980, SSD, fusion IO...)
2) 5 jours d'interruption de la pseudo HD Slony (slide 33)
3) ils vont entamer leurs 3e refonte architecturale (quels couts ???) pour absorber la croissance

Pour comparaison, CDISCOUNT qui a un volume plus important (près de 20 To) n'utilise qu'un seul serveur SQL Server en dehors de ceux répliquant pour la HD...
http://download.microsoft.com/docume...ount-webV5.pdf

A +