Accueil › SQL & bases de données › Vivre avec de très grosses tables
Vivre avec de très grosses tables
Performance en productionavance
Passé quelques dizaines de millions de lignes, des problèmes nouveaux apparaissent : purges interminables, VACUUM débordé, index qui ne tiennent plus en mémoire.
Première question : faut-il vraiment tout garder en ligne ? L'archivage — déplacer les données froides ailleurs — est souvent plus simple et plus efficace qu'un partitionnement.
Le partitionnement découpe une table selon une clé :
CREATE TABLE evenement (
id bigint GENERATED ALWAYS AS IDENTITY,
creee_le timestamptz NOT NULL,
type text NOT NULL,
charge jsonb
) PARTITION BY RANGE (creee_le);
CREATE TABLE evenement_2026_09 PARTITION OF evenement
FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
Son bénéfice principal n'est pas la vitesse des requêtes mais la purge :
ALTER TABLE evenement DETACH PARTITION evenement_2025_09;
DROP TABLE evenement_2025_09; -- instantané, au lieu d'un DELETE de 40 millions de lignes
Le piège : si les requêtes ne filtrent pas sur la clé de partitionnement, elles parcourent toutes les partitions. Le partitionnement se décide donc à partir des requêtes réelles, pas de la taille.
Enfin, surveillez l'autovacuum. Ses seuils par défaut (20 % de la table) sont trop prudents sur les très grosses tables.
Les termes à connaître
- Partitionnement déclaratif
Découper une table en partitions selon une clé (
PARTITION BY RANGE (creee_le)). PostgreSQL route les écritures et n'interroge que les partitions concernées (partition pruning).- Élagage de partitions (*pruning*)
L'optimiseur élimine les partitions qui ne peuvent pas contenir de résultat, à la planification ou à l'exécution. C'est tout l'intérêt du partitionnement.
- Archivage
Déplacer les données anciennes vers une table ou une base séparée. Souvent plus simple et plus efficace qu'un partitionnement, quand les données froides ne sont presque jamais lues.
- DETACH PARTITION
Détacher une partition entière : opération quasi instantanée qui remplace la suppression de millions de lignes. La grande force du partitionnement par date.
- autovacuum
Le processus qui nettoie les versions mortes et met à jour les statistiques. Sur une grosse table très écrite, ses réglages par défaut sont souvent trop prudents.
- fillfactor
Le pourcentage de remplissage d'une page laissé libre pour les mises à jour ultérieures. Abaisser le
fillfactorfavorise les mises à jour HOT sur les tables très modifiées.
Cette page présente la partie essentielle de la leçon. Les exemples commentés, les pièges courants et les trois questions de quiz sont dans l'application.