Formations — 1 037 leçons en français

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 fillfactor favorise les mises à jour HOT sur les tables très modifiées.

Lire l'explication détaillée et faire le quiz →

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.