Discussion :

[AmeryCourtz]

Bonjour,

je me permets de solliciter votre matière grise.

Je me mets à peine à postgresql et je rencontre un problème de croissance d'espace disque pour l'une de mes tables.

Celle-ci fait 30 millions de lignes pour 16 colonnes et 11 index et je la découpe en 500 partitions sur une des colonnes.

Je la mets régulièrement à jour avec ses partitions et ses indexs et je constate une croissance de mon espace disque assez conséquente (55 Go à chaque mise à jour).

Voici la Table "public.prs_data_agregation" :

Column	Type	Collation	Nullable	Default
siren	character varying(9)		not null
nic	character varying(5)		not null
title	text		not null
description	text		not null
apet	character varying(6)		not null
categorie_entreprise	character varying(3)		not null
categorie_juridique	character varying(4)		not null
effectif	character varying(2)		not null
adresse	character varying(150)		not null
code_postal	character varying(5)		not null
ville	character varying(60)		not null
latitude	character varying(12)		not null
longitude	character varying(12)		not null
date_creation	timestamp without time zone
date_dernier_update	timestamp without time zone
etat	integer		not null

Partition key: RANGE (code_postal)
Indexes:

"prs_data_agregation-sirennic-key" PRIMARY KEY, btree (siren, nic, code_postal)
"prs_data_agregation-apet" btree (apet)
"prs_data_agregation-categorie_juridique" btree (categorie_juridique)
"prs_data_agregation-code_postal" btree (code_postal)
"prs_data_agregation-date_creation" btree (date_creation)
"prs_data_agregation-effectif" btree (effectif)
"prs_data_agregation-latitude" btree (latitude)
"prs_data_agregation-latitude_longitude" btree (latitude, longitude)
"prs_data_agregation-longitude" btree (longitude)
"prs_data_agregation-siren" btree (siren)
"prs_data_agregation-sirennic" btree (siren, nic)

Number of partitions: 500 (Use \d+ to list them.)

J'imagine que c'est normal et qu'il me faut la "nettoyer", mais je voulais connaitre la meilleure façon de faire pour vous.

Est-ce que je dois juste faire un VACUUM FULL ou dois-je supprimer les index et les remonter ?
Ou dois-je carrément supprimer la table et la refaire à chaque fois ?
En termes de temps sur une table de 30 millions de données quelle est la meilleure solution sans que cela paralyse mes utilisateurs et l'utilisation de l'outil ?

Cordialement.

[SQLpro]

C'est un problème bien connu sous PostGreSQL que vous ayez un problème de volume anormalement élevé dans vos tables si vous faites beaucoup de mises à jour.... En effet, chaque UPDATE se comporte comme si vous aviez un DELETE et un UPDATE. Il y a donc une ligne fantôme générée à chaque UPDATE aussi bien dans la table que dans les index contenant les données modifiées. C'est un des défaut du moteur...
À lire : https://www.cybertec-postgresql.com/...in-postgresql/

Pour savoir pourquoi c'est mal conçu, https://www.developpez.net/forums/d2.../#post11684481

Je viens de commencer une série d'article pour comparer PostGreSQL à SQL Server et vais en parler pas mal dans la 3e partie de ma série d'article...
https://sqlpro.developpez.com/tutori...ndes-pour-dba/

Pour y remédier, il faut utiliser VACUUM, qui est une plaie aussi car il génère de nombreux blocages et des verrous mortels. Donc a faire hors production...

A +

[AmeryCourtz]

Bonjour SQLpro et merci de ton retour.

c'est bien ce qu'il me semblait aussi.

je vais donc garder mon idée de travailler sur une table parallèle et remplacer ensuite celle de prod ensuite.

Cordialement

[SQLpro]

Situ as des heures creuses, VACUUM est une solution. Si tu es dans la version 13, il peut agir en parallèle pour aller plus vite....
https://blog.dbi-services.com/postgr...m-for-indexes/

A +

[ced]

Bonjour,

Vous pouvez aussi regarder du côté de l'extension pg_squeeze, présentée dans le billet blog suivant : https://www.cybertec-postgresql.com/...resql-storage/

[escartefigue]

Bonjour AmeryCourtz

Vous seriez déjà grandement gagnant de supprimer tous ces index redondants, onze index quand on a seize colonnes c'est énorme !
Par exemple, pour ces 3 là, le premier suffit. Les deux autres, redondants, sont par la force des choses multiples et donc encore plus coûteux !

"prs_data_agregation-sirennic-key" PRIMARY KEY, btree (siren, nic, code_postal)
"prs_data_agregation-siren" btree (siren)
"prs_data_agregation-sirennic" btree (siren, nic)

De plus, comme le NIC est lié à la localisation géographique de l'établissement, l'ajout du code postal est probablement inutile (sauf si l'index est utilisé comme index couvrant, à vérifier)
Auquel cas, seul le deuxième index serait utile.


Et pour cet autre index :

"prs_data_agregation-effectif" btree (effectif)

Vérifiez le nombre de valeurs distinctes pour la colonne et la répartition de ces valeurs, seul un index discriminant est pertinent (hors considération de stratégie sur index couvrant, peu probable avec un index mono-colonne)

[AmeryCourtz]

Merci pour vos infos, je vais regarder tous cela.

Je suis en version 11. Je suis sous docker c'est plutôt simple à changer, mais il y a t-il de grosses modifications derrière ?

Et merci pour pg_squeeze, je ne connaissait pas.

Je vais tenter de comparer ces deux solutions.

[SQLpro]

J'en rajoute par rapport à mon confrère le joueur de carte de Marcel Pagnol....

Ces index :
"prs_data_agregation-latitude" btree (latitude)
"prs_data_agregation-latitude_longitude" btree (latitude, longitude)
"prs_data_agregation-longitude" btree (longitude)
N'ont aucun intérêt et ne seront jamais utilisés...

SI vous voulez faire du spatial, il faut utiliser PostGis et une colonne de type GEOMETRY contenant un point, et rajouter un index spatial...

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
ALTER TABLE public.prs_data_agregation
   ADD GEO_POINT GEOMETRY;
 
UPDATE public.prs_data_agregation
   SET GEO_POINT  = ST_MakePoint(longitude, latitude);
 
ALTER TABLE public.prs_data_agregation
   DROP COLUMN longitude;
 
ALTER TABLE public.prs_data_agregation
   DROP COLUMN latitude;
 
CREATE INDEX X_SPATIAL_GEO_POINT
   ON  public.prs_data_agregation
   USING GIST (GEO_POINT);

Après vous pourrez faire des recherches par proximité, calculer le plus court chemin...

A +

[alassanediakite]

Salut
Si les experts me permettent...
Questions...

• Il y a-t-il une dépendance entre siren, nic, adresse, code_postal, ville?
• Les villes sont elles déjà connues? si oui une table des villes

Pour l’alignement des données (mes excuses j'ai perdu l'article en français)...

• etat, date_creation, date_dernier_update, effectif,
• Les varchar dans l'ordre des tailles
• title, description

Par ailleurs, pourquoi 500 partitions? comment se fait la mise à jour?
@+

[fsmrel]

Salut Bunny,


Côté Siren et Nic en tant que tels, pas de problème a priori, ce sont des codes INSEE permettant d’identifier les entreprises. Selon l’INSEE, un SIREN est affecté à une entreprise et un SIRET (SIREN + NIC) est affecté ipso facto à chaque établissement de cette entreprise.

Par contre, indépendamment des problèmes de performance, tu as raison concernant le code postal. En effet, la clé primaire de la table est le triplet {siren, nic, code_postal}. Si le code postal est présent dans la clé, c’est que celle-ci n’est pas réductible à la paire {siren, nic}, donc la table est sensée représenter un granule plus fin que l’établissement. Mais alors l’APET (encore l’INSEE) qui désigne l’activité principale d’un établissement est déterminé par une partie de la clé, à savoir la paire {siren, nic}, il y a donc un viol de 2NF.

Par ailleurs, dans la mesure où un code postal détermine une ville, une latitude et une longitude, il y a aussi un viol de 3NF dans l’air, que la clé soit réduite ou non à la paire {siren, nic}...

[escartefigue]

Bonjour François,

Par contre, indépendamment des problèmes de performance, tu as raison concernant le code postal. En effet, la clé primaire de la table est le triplet {siren, nic, code_postal}. Si le code postal est présent dans la clé, c’est que celle-ci n’est pas réductible à la paire {siren, nic}, donc la table est sensée représenter un granule plus fin que l’établissement. Mais alors l’APET (encore l’INSEE) qui désigne l’activité principale d’un établissement est déterminé par une partie de la clé, à savoir la paire {siren, nic}, il y a donc un viol de 2NF.

Que nenni : comme je le disais plus haut, le NIC est un attribut de l'établissement, il n'y a donc forcément qu'un et un seul code postal pour un NIC d'un même SIREN
La seule raison d'être éventuelle du CP dans l'index est donc l'index couvrant.
Il n'en reste pas moins que l'adresse et le CP localisés dans la table des établissements est sujet à débat (pour le moins) à voir selon les règles de gestion

Par ailleurs, dans la mesure où un code postal détermine une ville, une latitude et une longitude, il y a aussi un viol de 3NF dans l’air, que la clé soit réduite ou non à la paire {siren, nic}...

Non plus : un code postal peut être partagé par plusieurs communes et une commune peut avoir plusieurs codes postaux.
cf. https://www.data.gouv.fr/fr/datasets/codes-postaux/
La latitude et la longitude ne sont pas identiques au nord est et au sud ouest d'une ville de grande étendue (selon la finesse de la mesure : une seconde d'angle représente environ 30 mêtres à l'équateur).

[SQLpro]

N'oubliez pas que PostGreSQL permet maintenant de rajouter des colonnes surnuméraires dans l'index en dehors de la clé avec la clause INCLUDE (présente depuis 15 ans dans SQL Server).

A +

[fsmrel]

Bonsoir Capitaine,

Que nenni : comme je le disais plus haut, le NIC est un attribut de l'établissement, il n'y a donc forcément qu'un et un seul code postal pour un NIC d'un même SIREN.

Que nenni quoi ? Qu’il y a un viol de 2NF ? Dans son message initial, AmeryCourtz présente le triplet {siren, nic, code_postal} comme étant clé primaire :

"prs_data_agregation-sirennic-key" PRIMARY KEY, btree (siren, nic, code_postal)

De deux choses l’une, soit le triplet est bien clé primaire (unicité et irréductibilité garanties), et la 2NF est violée comme je l’ai montré, soit cette clé est réductible à la paire {siren, nic}, c’est-à-dire au SIRET, auquel cas la 2NF est respectée, sous réserve que chaque attribut dépende pleinement de la paire en question.

Si la clé est réductible au SIRET, chaque ligne de la table correspond à au moins et au plus un établissement, mais alors je trouve pour le moins bizarre autant qu’étrange d’avoir une table de 30 millions d’établissements, alors que selon l’INSEE il y a seulement 5 millions et quelques établissements dans notre doulce France, « cher pays de mon enfance » comme dit le poète...

Si la clé n’est pas réductible, même si la table contenait tous les établissements sis en France, 30 millions de lignes ça interpelle encore.

=> Comme disait Paul Volfoni, « Y’a aut’ chose »...

[fsmrel]

Non plus : un code postal peut être partagé par plusieurs communes et une commune peut avoir plusieurs codes postaux.

Oui, bon, remplace le code postal par le code commune INSEE. Je ne cherche pas à vérifier si un code commune détermine ou non une commune, tu me diras. En tout cas, si d’aventure la table est en 2NF, elle viole au moins à 99% la 3NF.
Merci à toi.

[escartefigue]

Mon propos concernait la pertinence des index.
La modélisation pose évidemment question, comment peut on avoir 30 millions de lignes alors que les attributs semblent tous de niveau établissement

Mais peu importe les attributs non clef, l'ajout du code postal, quand on a déjà SIREN + NIC ne peut en aucun cas être discriminant puisque un SIRET n'a qu'un et un seul code postal.