Forums PostgreSQL.fr

lah

Membre

organisation de la base de données (8.4 / schémas)

Bonjour à toutes et à tous,

Nous utilisons la version 8.4.

Nous avons une base de données de près de 140 tables.
5 d'entre elles contiennent un peu plus de 30 millions d'enregistrements.
Elles consistent en une liste de records représentant des lignes de documents (+- 800 documents)

A ce jour nous n'utilisons qu'un schéma (public).

Conceptuellement, nous pourrions créer + ou - 800 schémas (un par document) qui reprendraient, chacun, une définition de ces 5 tables plus importantes.
Nos requêtes les plus gourmandes s'adresseraient à un et un seul de ces schémas (dont les tables ne contiendraient plus 30 millions de records mais 40 000).

Pensez-vous que l'idée est bonne ou est-ce que nous allons nous heurter à l'un ou l'autre problème, à l'une autre ou l'autre limitation ?

Je vous remercie pour votre aide,

Laurent

Marc Cousin

Membre

Re : organisation de la base de données (8.4 / schémas)

Ça ressemble à du partitionnement. Cela peut aider à résoudre vos problèmes, mais ça risque de rajouter pas mal de complexité à votre système.

Histoire que nous puissions vraiment vous aider, pouvez vous décrire précisément le problème plutôt que la solution envisagée ? Il y a peut être des solutions beaucoup plus simples à mettre en oeuvre…

---

Marc.

lah

Membre

Re : organisation de la base de données (8.4 / schémas)

Bonjour,
En fait cette base de données nous sert à stocker des documents.
Chaque ligne texte d'un document étant un record dans une table.
Nous avons +- 800 documents représentant +- 5.000.000 de lignes (donc de records) - ceci pourra augmenter pas mal à l'avenir.
Une de ces tables contient aussi toutes les occurences des mots présents (en gardant le record dans lequel ils se trouvent,
leur position etc...). Cette table represente actuellement +- 30.000.000 de records.
Nous avons constaté que les queries dans ces grosses tables consomment un peu de temps (de l'ordre de 10 à 30 sec) ce qui est trop pour notre logiciel.
Vu que nos recherches se limitent document par document, nous avions pensé à splitter ces tables verticalement et donc d'avoir une occurence de chaque table par document (dans un schema par exemple) ceci réduirait donc la taille des tables et aussi les temps des queries (nous avons essayé avec de plus petites tables, les temps sont meilleurs ( < 200 ms).

Notre question est donc double:
- est-ce que postgres ne pose pas de problème avec notre solution (donc avoir 800 schema ou plus) ?
- peut-être existe-t-il une meilleure solution que celle à laquelle nous avons pensé.

Merci

Marc Cousin

Membre

Re : organisation de la base de données (8.4 / schémas)

Voici les premières interrogations que j'ai :
- vous avez un index de mots. Pourquoi ne pas avoir utilisé l'index full text de postgresql ? Il sera infiniment plus rapide que tout ce que vous pourrez proposer : http://docs.postgresql.fr/8.4/textsearch.html
- y a t'il une raison fonctionnelle à éclater le document en centaines de record ? (c'est probablement ça qui vous coûte le plus)

Une fois ces deux points éclaircis, il serait intéressant d'observer une de ces requêtes qui pose problème et son plan d'exécution, mais si vous pouviez déjà répondre à ces deux points…

---

Marc.

lah

Membre

Re : organisation de la base de données (8.4 / schémas)

- vous avez un index de mots. Pourquoi ne pas avoir utilisé l'index full text de postgresql ? Il sera infiniment plus rapide que tout ce que vous pourrez proposer : http://docs.postgresql.fr/8.4/textsearch.html

Nous l’avons essayé, les résultats n’étaient pas si bons que ça… de plus certains opérateurs manquent à l’appel (proximité, proximité ordonnée, …)

- y a t'il une raison fonctionnelle à éclater le document en centaines de record ? (c'est probablement ça qui vous coûte le plus)

Oui, pour des raisons conceptuelles nous devons pouvoir accéder directement à chaque ligne du document. Ceci ne peut pas être remis en cause.

Merci

Marc Cousin

Membre

Re : organisation de la base de données (8.4 / schémas)

Ok.

Avez vous un explain analyze d'une requête posant problème ? (ainsi que les définitions des objets impliqués dans la requête) ?

---

Marc.

lah

Membre

Re : organisation de la base de données (8.4 / schémas)

Je vous remercie pour votre aide.

Avez vous un explain analyze d'une requête posant problème ? (ainsi que les définitions des objets impliqués dans la requête) ?

La table:

CREATE TABLE sysindexterm
(
  sit_dicoitemid integer NOT NULL,
  sit_documentrecordid integer NOT NULL,
  sit_documentdescriptionid integer NOT NULL,
  sit_position smallint NOT NULL,
  sit_phrase bigint NOT NULL,
  sit_type smallint,
  CONSTRAINT sysindexterm_pkey PRIMARY KEY (sit_dicoitemid, sit_position, sit_documentrecordid, sit_documentdescriptionid, sit_phrase),
  CONSTRAINT "Ref_sysindexterm_to_dicoitem" FOREIGN KEY (sit_dicoitemid)
	  REFERENCES dicoitem (dci_id) MATCH SIMPLE
	  ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "Ref_sysindexterm_to_sysindexphrase" FOREIGN KEY (sit_phrase)
	  REFERENCES sysindexphrase (sip_id) MATCH SIMPLE
	  ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

CREATE INDEX sysindexterm_idx
  ON sysindexterm
  USING btree
  (sit_dicoitemid);

CREATE INDEX sysindexterm_idx1
  ON sysindexterm
  USING btree
  (sit_documentrecordid);

La table contient 27.000.000 de records

La query

select sit_documentrecordid from sysindexterm where sit_dicoitemid = 377

il trouve 70000 hits en 43 secondes

L'explain

"Bitmap Heap Scan on sysindexterm  (cost=1427.29..146791.02 rows=75893 width=4)"
"  Recheck Cond: (sit_dicoitemid = 377)"
"  ->  Bitmap Index Scan on sysindexterm_idx  (cost=0.00..1408.32 rows=75893 width=0)"
"        Index Cond: (sit_dicoitemid = 377)"

Laurent

Marc Cousin

Membre

Re : organisation de la base de données (8.4 / schémas)

Si votre table est faiblement updatée et que vous accédez toujours à cette table par itemid, vous pourriez la clusteriser sur sit_dicoitemid. Cette requête deviendra très largement plus rapide, les données à lire de la table étant contigues. Il est assez surprenant que cette requête mette 43 secondes (ça donne l'impression que les entrées ayant le même itemid sont vraiment éparpillées dans toute la table).

Évidemment, l'opération cluster prend un verrou exclusif sur la table pendant l'opération… qui peut être un peu longue sur une table ayant autant d'enregistrements.

Sinon, cette table pourrait être un bon candidat au partitionnement, plutôt que de créér des schémas. Ci joint l'introduction sur le sujet : http://docs.postgresql.fr/8.4/ddl-partitioning.html . Qu'en pensez vous ?

---

Marc.

lah

Membre

Re : organisation de la base de données (8.4 / schémas)

(...) vous pourriez la clusteriser (...)
(...) bon candidat au partitionnement (...)

Cluster: dans notre cas, le verrou exclusif nous pose problème.
Partitionnement: peut-être bien, en effet. Même si la fin du dernier paragraphe de http://docs.postgresql.fr/8.4/ddl-partitioning.html

(...)Un partitionnement qui utilise ces techniques fonctionne assez bien jusqu'environ une centaine de partitions ; il est impensable de vouloir atteindre des milliers de partitions.

nous fait quelque peu hésiter.

Pouvez-vous, svp, me dire en quoi notre proposition de solution consistant à passer par les schémas ne vous semble ne pas être indiqué(e) ?
Nous aimerions savoir qu'elles sont les limites PostgreSQL quant à une telle utilisation de schémas.
Postgres peut-il gérer autant de schema qu’on a de documents (+- 2000) ?

Merci

gleu

Administrateur

Re : organisation de la base de données (8.4 / schémas)

PostgreSQL pourra gérer autant de schémas sans perte de performance. Je pense que Marc voulait parler du code applicatif qui risque de sérieusement se complexifier avec autant de schémas. D'autre part, je ne pense pas non plus que Marc voulait dire qu'il fallait créer autant de partitions que ça. Il est tout à fait possible de placer cent documents par partition, ce qui nous ferait 8 partitions pour l'instant, chacune de 300000 enregistrements, ce qui est très raisonnable.

---

Guillaume.

lah

Membre

Re : organisation de la base de données (8.4 / schémas)

Je vous remercie, tous les deux, pour votre aide.

(...)code applicatif qui risque de sérieusement se complexifier avec autant de schémas.(...)

c'est principalement pour cette raison que nous souhaitions vous poser la question.

PostgreSQL pourra gérer autant de schémas sans perte de performance.

Impeccable.