Partitioning PostgreSQL : range, hash et list — tutoriel pratique 2026

📍 Article principal : PostgreSQL en surcharge pour PME. Ce tutoriel détaille les trois modes de partitioning natifs PostgreSQL et leurs cas d’usage concrets pour une PME en croissance.

Le partitioning de tables est la technique la plus puissante pour soutenir des volumes massifs sans dégradation de performance. PostgreSQL propose depuis la version 10 un partitioning déclaratif qui découpe une table logique en plusieurs partitions physiques selon une clé. Le planificateur de requêtes ne scanne automatiquement que les partitions pertinentes — pruning intelligent qui multiplie les performances par 10 ou 100 sur les tables géantes. Ce tutoriel pose les trois modes (range, hash, list), montre la mise en place pas à pas avec exemples concrets, et explique quand chaque mode brille.

Prérequis

• PostgreSQL 16 ou 17 (le partitioning a beaucoup mûri ces dernières versions)
• Tuning de base déjà appliqué (voir tutoriel tuning shared_buffers)
• Notions SQL avancées (DDL, contraintes, foreign keys)
• Niveau : avancé
• Temps estimé : 4 à 6 heures

Étape 1 — Quand partitionner et quand ne pas le faire

Le partitioning n’est pas une solution magique. Il introduit de la complexité opérationnelle et certaines limitations (les UNIQUE constraints doivent inclure la clé de partition, les foreign keys vers une table partitionnée demandent attention). Avant de partitionner, vérifier que les performances dégradées ne viennent pas simplement d’index manquants ou d’un tuning mémoire insuffisant. Souvent, ajouter un index BRIN sur created_at ou augmenter shared_buffers résout déjà 80 % du problème sans introduire la complexité du partitioning.

Le partitioning devient pertinent quand la table dépasse 50 millions de lignes ou plusieurs centaines de gigaoctets, quand on veut archiver facilement des données anciennes sans bloquer les écritures, quand on a des charges très différentes par dimension (région, client, période), ou quand la maintenance VACUUM/REINDEX prend tellement de temps qu’elle perturbe l’exploitation. Pour une PME standard avec moins de 10 millions de lignes par table, attendre avant de partitionner — la complexité ne se justifie pas.

Étape 2 — Partitioning par range (le plus courant)

Le partitioning par range découpe la table selon des intervalles de valeurs d’une colonne — typiquement la date de création. Chaque mois ou chaque année a sa propre partition. Cette approche brille pour les données temporelles : rapports analytiques rapides sur une période donnée, archivage facile des anciennes périodes, scan minimal pour les requêtes filtrées par date.

-- Créer la table parent partitionnée
CREATE TABLE orders (
  id uuid PRIMARY KEY,
  customer_id uuid NOT NULL,
  amount_xof integer NOT NULL,
  created_at timestamptz NOT NULL
) PARTITION BY RANGE (created_at);

-- Créer les partitions mensuelles
CREATE TABLE orders_2026_04 PARTITION OF orders
  FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE orders_2026_05 PARTITION OF orders
  FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
-- etc

Toute requête avec un filtre WHERE created_at BETWEEN ... AND ... n’attaque que les partitions concernées. Une requête sur le mois courant ne touche qu’une seule partition de quelques gigaoctets, alors que la table totale peut peser plusieurs centaines de gigaoctets. Le gain en performance est immédiat et massif.

Étape 3 — Automatiser la création de partitions

Créer manuellement une partition par mois est fastidieux et risqué — oublier la partition d’avril et toutes les écritures de ce mois échouent. L’extension pg_partman automatise complètement ce processus. Configurer une fois la politique (mensuel ou journalier, retention), pg_partman crée automatiquement les futures partitions et détache les anciennes selon la rétention configurée.

CREATE EXTENSION pg_partman;
SELECT partman.create_parent(
  p_parent_table := 'public.orders',
  p_control := 'created_at',
  p_type := 'native',
  p_interval := 'monthly',
  p_premake := 4
);
-- Crée 4 partitions futures à l'avance
-- Configure la retention
UPDATE partman.part_config
SET retention = '24 months', retention_keep_table = false
WHERE parent_table = 'public.orders';

Un job cron PostgreSQL (via pg_cron ou un scheduler externe) appelle SELECT partman.run_maintenance() chaque jour pour matérialiser la maintenance — création de futures partitions, archivage ou suppression des partitions trop anciennes selon la rétention configurée. La PME ne se soucie plus jamais des partitions, tout fonctionne automatiquement en arrière-plan.

Étape 4 — Partitioning par hash

Le partitioning par hash distribue uniformément les données entre N partitions selon le hash d’une colonne — typiquement customer_id ou un autre identifiant naturel. Utile quand on n’a pas de dimension temporelle évidente mais qu’on veut paralléliser les écritures et les lectures sur plusieurs partitions. Avec 8 partitions hash sur customer_id, les requêtes filtrées par customer_id n’attaquent qu’une seule partition, divisant la charge par 8.

L’inconvénient du partitioning par hash : impossible d’archiver simplement (les données sont mélangées entre toutes les partitions selon le hash), impossible d’ajouter des partitions facilement (changer le nombre de partitions demande une migration des données). Réserver le partitioning par hash aux cas où le volume est élevé mais relativement stable, et où la performance prime sur la flexibilité d’archivage.

Étape 5 — Partitioning par list (multi-tenant)

Le partitioning par list assigne explicitement chaque valeur à une partition — particulièrement adapté aux applications multi-tenant où chaque client a ses propres données. Une partition par tenant permet : isolation forte des données, archivage facile par client, performance optimale (chaque tenant n’attaque que sa propre partition), et offboarding simple (DROP de la partition quand un client part).

L’inconvénient : limité à quelques dizaines de tenants. Au-delà, la gestion devient lourde. Pour les SaaS qui ciblent des dizaines de PME clientes, c’est exactement le bon outil. Pour les SaaS B2C avec des millions d’utilisateurs, basculer sur partitioning par hash ou ne pas partitionner du tout selon le volume.

Étape 6 — Migration d’une table existante

Pour les tables existantes en production, la migration vers une structure partitionnée demande une coupure ou une bascule progressive. La méthode classique : créer la nouvelle table partitionnée à côté de l’ancienne, copier les données par batch (pour éviter le lock long), basculer le trafic via un renommage atomique des tables, supprimer l’ancienne après vérification. Pour les très grosses tables, l’extension pg_partman propose un assistant de migration qui orchestre tout cela proprement.

Tester impérativement la migration sur un VPS bac à sable avec un volume réel de données. La durée totale de migration peut surprendre : une table de 100 Go prend plusieurs heures à copier même avec parallélisme. Anticiper la fenêtre de bascule et communiquer avec les utilisateurs si une coupure est nécessaire. Une fois la migration validée, le bénéfice opérationnel est durable — les futures opérations sur cette table seront rapides pendant des années.

Index sur tables partitionnées

Les index sur une table partitionnée demandent attention. PostgreSQL ne crée pas automatiquement les index sur toutes les partitions — il faut soit les déclarer sur la table parent (qui propage à toutes les partitions existantes et futures), soit les créer manuellement par partition. La pratique recommandée : déclarer les index essentiels sur le parent (clé primaire, foreign keys, index sur les colonnes de filtre fréquentes), créer des index spécifiques par partition uniquement pour des cas particuliers (par exemple un index full-text sur la partition active uniquement, pour économiser l’espace sur les partitions archivées).

Pour les tables partitionnées par range temporel, les index BRIN deviennent particulièrement efficaces — ils exploitent la corrélation naturelle entre l’ordre d’insertion et la valeur de la clé de partition. Un index BRIN sur created_at d’une table partitionnée mensuellement fait quelques mégaoctets seulement et offre des performances équivalentes à un B-Tree de plusieurs gigaoctets. Combinaison gagnante pour les tables d’audit ou de logs qui grossissent en continu.

Maintenance et opérations courantes

L’opération la plus fréquente sur une table partitionnée par range temporel est l’archivage des anciennes périodes. Le mécanisme DETACH PARTITION sépare proprement une partition de la table parent — elle devient une table indépendante qu’on peut ensuite déplacer vers un stockage froid via pg_dump puis DROP. Cette opération est quasi-instantanée et ne bloque pas les écritures sur les autres partitions, contrairement à un DELETE de masse qui aurait pris des heures et déclenché du bloat.

Pour le ré-attachement (par exemple pour rejouer une période archivée temporairement), ATTACH PARTITION fait l’inverse — la table devient à nouveau une partition de la table parent et reçoit son trafic normalement. Cette flexibilité ouvre des cas d’usage avancés : archivage rolling, mise en avant temporaire de données historiques pour un audit, gestion de données froides sur du stockage moins coûteux.

Limites connues et alternatives

Le partitioning PostgreSQL natif a quelques limites historiques en cours d’amélioration. Les jointures entre tables partitionnées peuvent être plus complexes à optimiser. Les requêtes qui ne filtrent pas sur la clé de partition scannent toutes les partitions — pruning inefficace. Le nombre maximal de partitions performant tient autour de 1000 — au-delà, les opérations de planification ralentissent.

Pour les cas qui dépassent ces limites, deux alternatives. TimescaleDB est une extension PostgreSQL spécialisée dans les séries temporelles qui automatise le partitioning et propose des optimisations avancées (compression native, agrégats continus). Citus distribue PostgreSQL sur plusieurs nœuds avec sharding automatique — solution pour les charges au-delà du téraoctet ou pour les besoins de scale-out massif. Ces deux options se déploient en remplacement de PostgreSQL natif et apportent leurs propres complexités opérationnelles.

Erreurs fréquentes

Adaptation au contexte ouest-africain

Pour les PME ouest-africaines, le partitioning par range mensuel sur les tables transactionnelles (commandes, paiements, journaux) devient pertinent dès qu’elles dépassent quelques millions de lignes. Le bénéfice immédiat : les rapports comptables mensuels qui prennent plusieurs minutes deviennent quasi-instantanés. L’archivage devient simple aussi — détacher les partitions de plus de deux ans et les exporter vers un stockage froid (Hetzner Storage Box) libère drastiquement l’espace utilisé sur le SSD principal et accélère les sauvegardes quotidiennes.

Cas concret : e-commerce ivoirien à 10 millions de commandes

Pour ancrer dans un cas réel : une PME e-commerce ivoirienne qui accumule 10 millions de commandes sur trois ans. La table monolithique pèse 80 Go avec un index B-Tree de 25 Go. Les rapports analytiques mensuels prennent 15 minutes, les sauvegardes nocturnes 4 heures. Migration vers partitioning par range mensuel avec pg_partman, retention 24 mois actifs, archivage trimestriel des plus anciennes partitions vers Hetzner Storage Box.

Résultat post-migration : rapport mensuel de 15 minutes à 30 secondes (gain de 30 fois), sauvegarde quotidienne de 4 heures à 25 minutes (gain de 10 fois), espace disque actif divisé par 3 grâce à l’archivage. La PME économise environ 500 €/an d’infrastructure et gagne en confort opérationnel. Investissement initial : deux semaines de travail d’un senior, environ 1 million de XOF de prestation. ROI atteint en moins de deux ans.

Partitioning et réplication logique

Combiner partitioning et réplication logique ouvre des architectures avancées. Une PME peut répliquer uniquement les partitions actives vers un cluster analytique, gardant l’historique complet sur le cluster principal. Cela permet de séparer la charge OLTP (transactions courantes) de la charge OLAP (analytique) sans dupliquer toutes les données. La configuration passe par CREATE PUBLICATION qui filtre les partitions à publier, et un abonné qui consomme uniquement ces partitions.

Cette architecture spécialisée se justifie pour les PME au-dessus de 100 millions de lignes. En dessous, un seul PostgreSQL tuné suffit largement. La règle de l’évolution progressive : démarrer simple, complexifier l’architecture uniquement quand le besoin est démontré par les mesures, jamais par anticipation.

Tester en bac à sable avant production

Le partitioning d’une table existante est l’une des opérations les plus risquées de l’administration PostgreSQL. Les écarts entre théorie et réalité sur de gros volumes peuvent coûter des heures de coupure non prévue. La règle absolue : répéter l’intégralité de la procédure sur un VPS bac à sable avec un dump récent de la production. Mesurer les durées, identifier les pièges, ajuster le script. Documenter chaque étape avec timestamp précis pour anticiper la fenêtre de coupure réelle.

Pour les PME qui n’ont pas le luxe d’un environnement de bac à sable identique, externaliser la prestation à un consultant expérimenté. Le coût d’une migration ratée (plusieurs jours de coupure, perte de données dans le pire cas) dépasse largement le coût d’une prestation externe par un expert qui a déjà fait l’opération plusieurs fois.

Cette discipline opérationnelle prudente fait toute la différence entre une migration réussie et un incident majeur potentiellement business-critical pour la PME.

Pour aller plus loin

🔝 Retour à l’article principal : PostgreSQL en surcharge pour PME. Tutoriel précédent : tuning shared_buffers. Documentation pg_partman : github.com/pgpartman/pg_partman, partitioning natif PostgreSQL : postgresql.org/docs/17/ddl-partitioning.

Le partitioning bien fait transforme la trajectoire d’évolution d’une PME — de la lente dégradation inexorable des performances à mesure que les volumes grossissent, à une plateforme qui scale linéairement pendant des années sans nécessiter de refonte. Cette discipline initiale est l’un des investissements les plus rentables d’une équipe technique mature, qui anticipe les défis de demain au lieu de subir les douleurs du présent.