Re: Migration d'une base simple vers une base utilisant des schemas

Bonjour à tous,

Nous avons une application (écrite avec Ruby on Rails) qui utilise une base
PG.

La plupart des lignes des tables ont une clé "user_id" et le tout commence
à grossir assez sérieusement. (J'ai une table en particulier qui contient
déjà plus de 26 millions de lignes)

Je pense que je pourrais améliorer les performances et la sécurité du
système en isolant les users et leurs données dans des schemas
(corrigez-moi si je me trompe)

Il existe des outils dans Ruby On Rails comme le gem "Appartment" qui sait
faire ça tout bien et de manière assez transparente

Les questions maintenant : si j'arrive à disons 2000 ou 3000 users et que
chaque schema contient environ 50 tables (+ une dizaine de tables dans
"public") est-ce que ça va poser un problème à PG une base de 100 000
tables avec leurs index ?

Et dernière question : est-ce qu'il existe une manière maline de migrer les
données de la base existante vers la nouvelle avec des schemas ?

L'idée que j'ai là tout de suite serait d'arriver à faire script SQL
contenant un dump "par user" qu'il n'y aurait plus qu'à restaurer dans le
bon schema.

Que pensez-vous de tout ça ?

D'avance merci pour votre aide,

--
Pierre Yager

La limitation du nombre maximum de tables se produira au niveau kernel, car
chaque table est un fichier sur disque. J'ai eu des problèmes de
performance à partir de 2000 tables. On utilise alors différents
tablespaces, mais cela devient vite ingérable, au sens où on écrit des
triggers et des functions tout le temps pour la moindre opération de
maintenance ou de migration.

Intuitivement, cela me semble une mauvaise modélisation de votre problème.
Avez vous 100 000 classes dans votre instance Rails ?

Le 29 janvier 2013 10:07, Pierre Y. <pierre(dot)y(at)gmail(dot)com> a écrit :

> Bonjour à tous,
>
> Nous avons une application (écrite avec Ruby on Rails) qui utilise une
> base PG.
>
> La plupart des lignes des tables ont une clé "user_id" et le tout commence
> à grossir assez sérieusement. (J'ai une table en particulier qui contient
> déjà plus de 26 millions de lignes)
>
> Je pense que je pourrais améliorer les performances et la sécurité du
> système en isolant les users et leurs données dans des schemas
> (corrigez-moi si je me trompe)
>
> Il existe des outils dans Ruby On Rails comme le gem "Appartment" qui sait
> faire ça tout bien et de manière assez transparente
>
> Les questions maintenant : si j'arrive à disons 2000 ou 3000 users et que
> chaque schema contient environ 50 tables (+ une dizaine de tables dans
> "public") est-ce que ça va poser un problème à PG une base de 100 000
> tables avec leurs index ?
>
> Et dernière question : est-ce qu'il existe une manière maline de migrer
> les données de la base existante vers la nouvelle avec des schemas ?
>
> L'idée que j'ai là tout de suite serait d'arriver à faire script SQL
> contenant un dump "par user" qu'il n'y aurait plus qu'à restaurer dans le
> bon schema.
>
> Que pensez-vous de tout ça ?
>
> D'avance merci pour votre aide,
>
> --
> Pierre Yager
>

--
Baptiste Manson
Inovia - Paris - www.inovia. <http://www.inovia-team.com>fr
http://twitter.com/#!/inoviateam
baptiste(dot)manson(at)inovia-team(dot)com
(mobile) 00+33 6 62 13 82 18
(land) 00+33 1 75 51 37 48

Bonjour,

"Pierre Y." <pierre(dot)y(at)gmail(dot)com> writes:
> Je pense que je pourrais améliorer les performances et la sécurité du
> système en isolant les users et leurs données dans des schemas
> (corrigez-moi si je me trompe)

Cela peut être une bonne chose égalemment pour une future solution dite
de « scale out » : lorsqu'on arrive aux limites des capacités d'un seul
serveur de bases de données, on répartie les données sur plusieurs
serveurs.

Cependant, cela mérite de bien valider que le modèle actuel est pris en
défaut, car la complexité du système peut vite devenir un problème. En
particulier, est-ce qu'une politique d'archivage plus sévère,
éventuellement accompagnée d'un serveur d'archives, ne serait pas plus
efficace ici ?

> Il existe des outils dans Ruby On Rails comme le gem "Appartment" qui sait
> faire ça tout bien et de manière assez transparente
>
> Les questions maintenant : si j'arrive à disons 2000 ou 3000 users et que
> chaque schema contient environ 50 tables (+ une dizaine de tables dans
> "public") est-ce que ça va poser un problème à PG une base de 100 000
> tables avec leurs index ?

À priori les outils tels que psql (\d) et pgAdmin et pg_dump vont être
impactés, en particulier sur des versions moins récentes de PostgreSQL.
Il se peut en particulier que le temps requis pour faire une sauvegarde
logique explose de manière exponentielle.

--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

Bonjour,

même pour SGBD propriétaire (style Oracle pour ne pas le citer), avoir 100
000 tables en ligne pose des problèmes.
Le premier étant la performance d'accès au dictionnaire !
Pour moi, 100 000 tables = problème de conception. Dans SGBDR, il y a le R
de "relation", comment peut-on se retrouver avec un modèle aussi éclaté ?

Cdt

Le 29 janvier 2013 12:51, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> a écrit
:

> Bonjour,
>
> "Pierre Y." <pierre(dot)y(at)gmail(dot)com> writes:
> > Je pense que je pourrais améliorer les performances et la sécurité du
> > système en isolant les users et leurs données dans des schemas
> > (corrigez-moi si je me trompe)
>
> Cela peut être une bonne chose égalemment pour une future solution dite
> de « scale out » : lorsqu'on arrive aux limites des capacités d'un seul
> serveur de bases de données, on répartie les données sur plusieurs
> serveurs.
>
> Cependant, cela mérite de bien valider que le modèle actuel est pris en
> défaut, car la complexité du système peut vite devenir un problème. En
> particulier, est-ce qu'une politique d'archivage plus sévère,
> éventuellement accompagnée d'un serveur d'archives, ne serait pas plus
> efficace ici ?
>
> > Il existe des outils dans Ruby On Rails comme le gem "Appartment" qui
> sait
> > faire ça tout bien et de manière assez transparente
> >
> > Les questions maintenant : si j'arrive à disons 2000 ou 3000 users et que
> > chaque schema contient environ 50 tables (+ une dizaine de tables dans
> > "public") est-ce que ça va poser un problème à PG une base de 100 000
> > tables avec leurs index ?
>
> À priori les outils tels que psql (\d) et pgAdmin et pg_dump vont être
> impactés, en particulier sur des versions moins récentes de PostgreSQL.
> Il se peut en particulier que le temps requis pour faire une sauvegarde
> logique explose de manière exponentielle.
>
> --
> Dimitri Fontaine
> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
>
>
> --
> Sent via pgsql-fr-generale mailing list (pgsql-fr-generale(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-fr-generale
>

--

*dbSQWare |* www.dbsqware.com *|* *Exploitez vos bases de données en toute
sérénité …*

Je viens de me rendre compte que j'avais répondu en personne à ceux qui
sont intervenus dans cette discussion dans prendre la peine de mettre la
liste en copie.

Veuillez m'en excuser. Je vais essayer d'y penser pour les prochains
messages.

(De manière générale, il ressort que 2000 schemas * 50 tables ou la même
chose en utilisant le partionnement grâce à l'héritage de tables ca va
faire beaucoup trop de tables. Sachant que je ne compte pas m'arrêter à
2000 clients, je crois qu'il faut que je continue à réfléchir...)

2013/1/29 Pierre Y. <pierre(dot)y(at)gmail(dot)com>

> Bonjour à tous,
>
> Nous avons une application (écrite avec Ruby on Rails) qui utilise une
> base PG.
>
> La plupart des lignes des tables ont une clé "user_id" et le tout commence
> à grossir assez sérieusement. (J'ai une table en particulier qui contient
> déjà plus de 26 millions de lignes)
>
> Je pense que je pourrais améliorer les performances et la sécurité du
> système en isolant les users et leurs données dans des schemas
> (corrigez-moi si je me trompe)
>
> Il existe des outils dans Ruby On Rails comme le gem "Appartment" qui sait
> faire ça tout bien et de manière assez transparente
>
> Les questions maintenant : si j'arrive à disons 2000 ou 3000 users et que
> chaque schema contient environ 50 tables (+ une dizaine de tables dans
> "public") est-ce que ça va poser un problème à PG une base de 100 000
> tables avec leurs index ?
>
> Et dernière question : est-ce qu'il existe une manière maline de migrer
> les données de la base existante vers la nouvelle avec des schemas ?
>
> L'idée que j'ai là tout de suite serait d'arriver à faire script SQL
> contenant un dump "par user" qu'il n'y aurait plus qu'à restaurer dans le
> bon schema.
>
> Que pensez-vous de tout ça ?
>
> D'avance merci pour votre aide,
>
> --
> Pierre Yager
>

Bonjour

il me semble que postgres gere tres bien ce volume de donnees non?
Si vous avez des requetes lentes ou autres ne serait il pas preferable de voir la configuration de postgres?

L utilisation de pgbadger ou pgfouine vous donnerais beaucoup d info pour vous orientez vers une bonne configuration( ...optimisation des requetes existants, ajout d index, deplacement des index sur des disques ssd ce qui ameliorerai leur parcours...)

Niveau securite effectivement a partir du moment ou les utilisateurs sont melange dans la meme base il peut y avoir des risques si certaines requetes sont mal ecrite mais les test unitaires sont la pour verifier tout cela;)

Cordialement,
Mançaux Pierre-alexandre
Le 29 janv. 2013 à 17:10, "Pierre Y." <pierre(dot)y(at)gmail(dot)com> a écrit :

> Je viens de me rendre compte que j'avais répondu en personne à ceux qui sont intervenus dans cette discussion dans prendre la peine de mettre la liste en copie.
>
> Veuillez m'en excuser. Je vais essayer d'y penser pour les prochains messages.
>
> (De manière générale, il ressort que 2000 schemas * 50 tables ou la même chose en utilisant le partionnement grâce à l'héritage de tables ca va faire beaucoup trop de tables. Sachant que je ne compte pas m'arrêter à 2000 clients, je crois qu'il faut que je continue à réfléchir...)
>
>
>
> 2013/1/29 Pierre Y. <pierre(dot)y(at)gmail(dot)com>
>> Bonjour à tous,
>>
>> Nous avons une application (écrite avec Ruby on Rails) qui utilise une base PG.
>>
>> La plupart des lignes des tables ont une clé "user_id" et le tout commence à grossir assez sérieusement. (J'ai une table en particulier qui contient déjà plus de 26 millions de lignes)
>>
>> Je pense que je pourrais améliorer les performances et la sécurité du système en isolant les users et leurs données dans des schemas (corrigez-moi si je me trompe)
>>
>> Il existe des outils dans Ruby On Rails comme le gem "Appartment" qui sait faire ça tout bien et de manière assez transparente
>>
>> Les questions maintenant : si j'arrive à disons 2000 ou 3000 users et que chaque schema contient environ 50 tables (+ une dizaine de tables dans "public") est-ce que ça va poser un problème à PG une base de 100 000 tables avec leurs index ?
>>
>> Et dernière question : est-ce qu'il existe une manière maline de migrer les données de la base existante vers la nouvelle avec des schemas ?
>>
>> L'idée que j'ai là tout de suite serait d'arriver à faire script SQL contenant un dump "par user" qu'il n'y aurait plus qu'à restaurer dans le bon schema.
>>
>> Que pensez-vous de tout ça ?
>>
>> D'avance merci pour votre aide,
>>
>> --
>> Pierre Yager
>

Je suis toujours en train de réfléchir à cette question... Je me demandais
s'il serait possible, plutôt que partitionner toute la base par user, de ne
partitionner que les plus grosses tables ?

Après il reste l'idée de déplacer les vieilles données par forcément utiles
dans un serveur d'archivage et de faire le ménage dans la base principale
mais je doute que ça puisse se faire automatiquement, il va me falloir
scripter ça et appeller ce système d'archivage dans une tâche cron.

L'idée de vérifier que les performances de ma base et de mon application
sont optimales est aussi à l'ordre du jour, je vais me faire une sessions
pgbadger quand les logs seront pleins.

Merci pour votre aide.

2013/1/29 Pierre Y. <pierre(dot)y(at)gmail(dot)com>

> Je viens de me rendre compte que j'avais répondu en personne à ceux qui
> sont intervenus dans cette discussion dans prendre la peine de mettre la
> liste en copie.
>
> Veuillez m'en excuser. Je vais essayer d'y penser pour les prochains
> messages.
>
> (De manière générale, il ressort que 2000 schemas * 50 tables ou la même
> chose en utilisant le partionnement grâce à l'héritage de tables ca va
> faire beaucoup trop de tables. Sachant que je ne compte pas m'arrêter à
> 2000 clients, je crois qu'il faut que je continue à réfléchir...)
>
>
>
> 2013/1/29 Pierre Y. <pierre(dot)y(at)gmail(dot)com>
>
> Bonjour à tous,
>>
>> Nous avons une application (écrite avec Ruby on Rails) qui utilise une
>> base PG.
>>
>> La plupart des lignes des tables ont une clé "user_id" et le tout
>> commence à grossir assez sérieusement. (J'ai une table en particulier qui
>> contient déjà plus de 26 millions de lignes)
>>
>> Je pense que je pourrais améliorer les performances et la sécurité du
>> système en isolant les users et leurs données dans des schemas
>> (corrigez-moi si je me trompe)
>>
>> Il existe des outils dans Ruby On Rails comme le gem "Appartment" qui
>> sait faire ça tout bien et de manière assez transparente
>>
>> Les questions maintenant : si j'arrive à disons 2000 ou 3000 users et
>> que chaque schema contient environ 50 tables (+ une dizaine de tables dans
>> "public") est-ce que ça va poser un problème à PG une base de 100 000
>> tables avec leurs index ?
>>
>> Et dernière question : est-ce qu'il existe une manière maline de migrer
>> les données de la base existante vers la nouvelle avec des schemas ?
>>
>> L'idée que j'ai là tout de suite serait d'arriver à faire script SQL
>> contenant un dump "par user" qu'il n'y aurait plus qu'à restaurer dans le
>> bon schema.
>>
>> Que pensez-vous de tout ça ?
>>
>> D'avance merci pour votre aide,
>>
>> --
>> Pierre Yager
>>
>
>

> La plupart des lignes des tables ont une clé "user_id" et le tout
> commence à grossir assez sérieusement. (J'ai une table en particulier
> qui contient déjà plus de 26 millions de lignes)
>
> Je pense que je pourrais améliorer les performances et la sécurité du
> système en isolant les users et leurs données dans des schemas

Pourquoi pas utiliser des partitions, une par utilisateur?

A+,

Wolfgang

J'ai quand même l'impression que les partitions c'est une usine à gaz.

C'est bien de ça qu'on parle ?
http://www.postgresql.org/docs/current/static/ddl-partitioning.html

Les schemas dans une applicaton RoR avec le gem Appartment c'est
transparent 0 maintenance et 10 lignes de code à ajouter. Enfin... "à
priori".

2013/1/30 Wolfgang Keller <feliphil(at)gmx(dot)net>

> > La plupart des lignes des tables ont une clé "user_id" et le tout
> > commence à grossir assez sérieusement. (J'ai une table en particulier
> > qui contient déjà plus de 26 millions de lignes)
> >
> > Je pense que je pourrais améliorer les performances et la sécurité du
> > système en isolant les users et leurs données dans des schemas
>
> Pourquoi pas utiliser des partitions, une par utilisateur?
>
> A+,
>
> Wolfgang
>
>
> --
> Sent via pgsql-fr-generale mailing list (pgsql-fr-generale(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-fr-generale
>

"Pierre Y." <pierre(dot)y(at)gmail(dot)com> writes:
> Je suis toujours en train de réfléchir à cette question... Je me demandais
> s'il serait possible, plutôt que partitionner toute la base par user, de ne
> partitionner que les plus grosses tables ?

Dans ce genre d'approche on peut aussi envisager PL/Proxy, dont
l'inconvénient majeur est de devoir passer par une interface entièrement
basée sur des procédures stockées et dont l'avantage principal est de
savoir s'adapter très facilement à des variations importantes de volumes
de données à gérer.

> Après il reste l'idée de déplacer les vieilles données par forcément utiles
> dans un serveur d'archivage et de faire le ménage dans la base principale
> mais je doute que ça puisse se faire automatiquement, il va me falloir
> scripter ça et appeller ce système d'archivage dans une tâche cron.

S'il est possible de faire un script et de l'invoquer depuis cron, alors
je pense que « ça peut se faire automatiquement », non ?

> L'idée de vérifier que les performances de ma base et de mon application
> sont optimales est aussi à l'ordre du jour, je vais me faire une sessions
> pgbadger quand les logs seront pleins.

Ici, regarder Tsung et pg_stats_statement.

"Pierre Y." <pierre(dot)y(at)gmail(dot)com> writes:
> J'ai quand même l'impression que les partitions c'est une usine à gaz.
>
> C'est bien de ça qu'on parle ?
> http://www.postgresql.org/docs/current/static/ddl-partitioning.html

C'est relativement lourd en effet. Ce mécanisme a été développé pour la
version 8.1 de PostgreSQL avec une contrainte forte sur le temps de
développement, et donc en tirant le maximum de l'existant.

Le problème de cette approche est qu'elle fonctionne juste assez bien
pour avoir historiquement limité la motivation des sponsors à financer
le développement d'une solution intégrée de partitionnement. On arrive
enfin au bout de cette période, cependant.

Deux projects de développements sont à considérer ici :

- partitionnement automatique, ou Segment Partitionning

PostgreSQL se débrouille tout seul pour savoir où trouver dans le
stockage physique les données qui correspondent à certaines clauses
WHERE

- partitionnement déclaratif

Un nouveau système de déclaration à l'avance des partitions
permettant au planificateur de requêtes de ne pas avoir à deviner où
sont les données, et lui permettant surtout de savoir aiguiller les
modifications de données (DML) sans que l'utilisateur ne fournisse
de triggers

Nous (2ndQuadrant) avons déjà travaillé sur la conception de ces deux
systèmes et avons un plan de développement concret, appliquable dans le
cadre du développement de PostgreSQL 9.4. Quelques sponsors ont déjà
déclaré leur intérêt et souhaitent participer, vous êtes les bienvenues
égalemment ! :)

--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support