Partitionner : quand la table unique ne suffit plus

La plupart des tables qu'on me propose de partitionner ont un problème d'index, pas un problème de taille. Une table de 400 millions de lignes avec le bon index répond en une milliseconde sur une recherche par clé, partitionnée ou non, car un B-tree ne gagne un niveau que lorsque le volume est multiplié par quelques centaines. Le partitionnement ne rend donc pas ces requêtes plus rapides. Il sert à supprimer, archiver et entretenir des données par blocs entiers, et sans ce besoin, on paie ses contraintes sans toucher son bénéfice.

Purger par DETACH plutôt que par DELETE

Soit une table d'événements d'audit qui reçoit 30 millions de lignes par mois et dont il faut garder douze mois. Chaque nuit, un traitement supprime ce qui a dépassé la rétention :

DELETE FROM audit_event WHERE occurred_at < now() - interval '12 months';

Ce DELETE écrit un million de lignes mortes par jour et génère autant de WAL qu'une insertion. Il laisse l'espace dans la table sans le rendre au système et donne à l'autovacuum un travail qu'il ne finit pas toujours avant le passage suivant. Les index, eux, gonflent sur leur bord gauche. Au bout d'un an, la table occupe sensiblement plus que ses données vivantes, et les lectures tombent sur des pages à moitié vides.

Partitionnée par mois, la même rétention devient une opération de catalogue :

CREATE TABLE audit_event (
    id          uuid        NOT NULL,
    tenant_id   uuid        NOT NULL,
    occurred_at timestamptz NOT NULL,
    payload     jsonb       NOT NULL,
    PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);

CREATE TABLE audit_event_2026_10 PARTITION OF audit_event
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

-- rétention : détacher puis supprimer, sans une seule ligne morte
ALTER TABLE audit_event DETACH PARTITION audit_event_2025_10 CONCURRENTLY;
DROP TABLE audit_event_2025_10;

Aucune ligne morte, presque pas de WAL, l'espace rendu immédiatement. C'est le premier bénéfice, et de loin le plus solide.

Les deux autres en découlent. L'entretien se fait par morceau ; un VACUUM, un REINDEX ou un changement de stockage porte sur une partition de 20 Go au lieu d'une table de 300 Go, tient donc dans une fenêtre et se reprend s'il échoue. Et les données chaudes restent en mémoire. Si 95 % des lectures portent sur le mois courant, l'index de la partition courante tient dans le cache, alors que l'index unique d'une grosse table y disperse ses pages chaudes parmi les froides, surtout quand la clé est aléatoire.

Les requêtes qui ne portent pas la clé

Le planificateur n'élimine les partitions inutiles que si la requête filtre sur la clé de partitionnement. Toute la mécanique repose là-dessus, et le revers est brutal, puisqu'une requête qui ne porte pas cette clé interroge toutes les partitions.

-- élagage : une partition lue
SELECT * FROM audit_event
WHERE tenant_id = $1 AND occurred_at >= '2026-10-01';

-- aucun élagage : une descente d'index par partition
SELECT * FROM audit_event WHERE id = $1;

Avec douze partitions, la seconde requête fait douze descentes d'index au lieu d'une. Avec trois cents partitions quotidiennes, elle en fait trois cents, et le temps de planification devient mesurable à lui seul. La recherche par identifiant, qui était la requête la plus rapide, devient l'une des plus lentes : on a accéléré la purge au prix de la lecture.

Toute contrainte d'unicité doit en outre inclure la clé de partitionnement, comme le montre la clé primaire plus haut, (id, occurred_at). PostgreSQL ne sait garantir l'unicité que partition par partition, faute d'index global. Si le modèle exigeait UNIQUE (tenant_id, reference), cette garantie disparaît, sauf à ajouter la date dans la contrainte, qui ne garantit alors plus la même chose. Par ricochet, une clé étrangère qui pointait vers audit_event(id) doit maintenant porter les deux colonnes.

Viennent enfin les verrous. Créer ou attacher une partition prend un verrou sur la table parente. Il est bref, mais il attend derrière les transactions longues, et tout le trafic attend derrière lui. DETACH ... CONCURRENTLY existe pour cette raison ; on l'utilise, avec un lock_timeout sur chaque opération de maintenance de partitions.

La partition du mois suivant qui n'existe pas

L'incident type du partitionnement est d'une banalité désarmante. Le premier du mois à minuit, la partition du nouveau mois n'existe pas, et chaque insertion échoue avec une erreur de routage. Le traitement qui devait la créer a échoué trois semaines plus tôt, sans alerte, parce que personne ne surveille un cron qui ne fait rien onze jours sur douze.

Le réflexe consiste à ajouter une partition par défaut, qui recueille ce qui ne correspond à aucune autre. Elle transforme une panne franche en panne lente. Les lignes d'octobre s'y accumulent, et le jour où l'on veut enfin créer la partition d'octobre, PostgreSQL refuse parce que la partition par défaut contient des lignes qui lui reviendraient. Il faut alors les déplacer à la main, sous verrou, sur une table qui reçoit du trafic.

Chez nous, les tables à fort débit n'ont pas de partition par défaut, les partitions sont créées avec trois périodes d'avance, et l'alerte porte sur l'état plutôt que sur le traitement.

-- alerte si la partition la plus lointaine couvre moins de 45 jours d'avance
SELECT max(pg_get_expr(c.relpartbound, c.oid)) AS derniere_borne
FROM pg_inherits i
JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'audit_event'::regclass;

Vérifier que la partition future existe protège contre toutes les causes d'échec du traitement, alors que vérifier que le traitement a tourné n'en couvre qu'une partie.

Une partition par tenant : presque toujours non

Dans un SaaS multi-tenant, l'idée revient à chaque revue d'architecture : une partition par client, pour l'isolation et pour supprimer un client d'un seul DROP. Je la refuse dans la plupart des cas.

Avec une partition par liste et quelques milliers de tenants, on obtient quelques milliers de partitions par table, multipliées par le nombre de tables partitionnées. Le catalogue enfle, les migrations de schéma touchent des milliers de relations, et la distribution est déséquilibrée, trois gros clients occupant 80 % du volume quand les autres ont des partitions de quelques kilo-octets. Une partition par hash donne des morceaux équilibrés mais fait perdre l'unique bénéfice recherché, puisqu'un client ne correspond plus à une partition.

Un index composite commençant par tenant_id donne déjà la localité de lecture. Le partitionnement par tenant ne se justifie que pour une poignée de très gros clients dont le cycle de vie est réellement distinct (rétention contractuelle différente, export ou suppression en bloc). J'isole alors ceux-là et je laisse tous les autres dans une partition commune.

Migrer une table vivante, et décider si ça vaut la peine

Une table existante ne devient pas partitionnée par un ALTER. Il faut créer la table partitionnée à côté, y copier les données par lots pendant que l'ancienne continue de recevoir des écritures, maintenir les deux en phase (double écriture applicative ou déclencheur), puis échanger les noms dans une transaction courte. Sur une table de plusieurs centaines de gigaoctets, cela représente plusieurs jours de copie, le double de l'espace disque pendant la durée, et une revue de chaque requête pour vérifier qu'elle porte la clé de partitionnement.

Cette revue concentre l'essentiel du travail. La copie est mécanique, l'audit des requêtes ne l'est pas, et c'est lui qui décide si l'on sort de la migration avec une base plus saine ou avec douze descentes d'index là où il y en avait une.

Je partitionne quand les trois premières réponses du tableau sont oui, et je m'abstiens si une seule des deux dernières est non.

Avant même ce tableau, il vaut la peine de vérifier si un index partiel sur les lignes actives, ou un DELETE par petits lots avec un autovacuum réglé pour cette table, ne suffit pas. Cela suffit plus souvent qu'on ne le croit, et ces deux solutions laissent la clé primaire intacte.