Optimisation de table

Version imprimable

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.

11/03/2021, 07h44
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 +
11/03/2021, 09h00
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
11/03/2021, 10h07
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 +
11/03/2021, 10h39
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/
11/03/2021, 14h44
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 !

Citation:

Envoyé par AmeryCourtz

"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 :

Citation:

Envoyé par AmeryCourtz

"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)
11/03/2021, 14h59
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.

Column	Type	Nullable
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

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:

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 +

11/03/2021, 20h13
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:oops:)...
- 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?
@+
12/03/2021, 02h51
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}...
12/03/2021, 08h18
escartefigue

Bonjour François,

Citation:

Envoyé par fsmrel

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

Citation:

Envoyé par fsmrel

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).
12/03/2021, 12h17
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 +
13/03/2021, 03h24
fsmrel

Bonsoir Capitaine,

Citation:

Envoyé par escartefigue

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... :koi:

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. 8O

=> Comme disait Paul Volfoni, « Y’a aut’ chose »...
13/03/2021, 04h32
fsmrel

Citation:

Envoyé par escartefigue

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.
13/03/2021, 11h20
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 :koi:

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.