Partitionnement PostgreSQL pour tables d'événements : apprenez quand cela vaut le coup, comment choisir la clé de partition, et ce que cela change pour les filtres du panneau admin et la rétention.
Les tables d'événements et les tables d'audit se ressemblent, mais elles existent pour des raisons différentes.
Une table d'événements enregistre ce qui se passe : vues de page, e-mails envoyés, appels webhook, exécutions de jobs. Une table d'audit enregistre qui a changé quoi et quand : un changement d'état, une mise à jour de permission, une approbation de paiement, souvent avec les détails « avant » et « après ».
Les deux croissent rapidement parce qu'elles sont en grande partie append-only. On supprime rarement des lignes individuelles et de nouvelles lignes arrivent chaque minute. Même un petit produit peut produire des millions de lignes de logs en quelques semaines lorsqu'on inclut les jobs en arrière-plan et les intégrations.
La douleur se manifeste dans le travail quotidien. Les panneaux d'administration ont besoin de filtres rapides comme « erreurs d'hier » ou « actions par cet utilisateur ». À mesure que la table grossit, ces écrans de base commencent à ralentir.
Vous remarquerez généralement quelques symptômes :
Le partitionnement est une façon de traiter cela. En termes simples, il divise une grande table en plusieurs tables plus petites (partitions) qui partagent un nom logique. PostgreSQL place chaque nouvelle ligne dans la partition appropriée selon une règle, généralement temporelle.
C'est pour cela que les équipes envisagent le partitionnement PostgreSQL pour les tables d'événements : il peut garder les données récentes dans des morceaux plus petits, permettant à PostgreSQL d'ignorer des partitions entières quand une requête concerne seulement une fenêtre temporelle.
Le partitionnement n'est pas un interrupteur magique de vitesse. Il peut beaucoup aider pour des requêtes comme « 7 derniers jours » et il simplifie la rétention (supprimer d'anciennes partitions est rapide). Mais il peut aussi créer de nouveaux problèmes :
Si votre panneau d'administration repose fortement sur les filtres temporels et des règles de rétention prévisibles, le partitionnement peut être un vrai avantage. Si la plupart des requêtes sont « trouver toutes les actions de l'utilisateur X sur tout l'historique », cela peut créer des maux de tête sauf si vous concevez l'interface et les index avec soin.
Les tables d'événements et d'audit croissent dans une seule direction : vers le haut. Elles reçoivent un flux continu d'inserts et presque aucune mise à jour. La plupart des lignes sont écrites une fois, puis relues plus tard lors d'actions de support, d'examens d'incidents ou de vérifications de conformité.
Cette nature « append-only » est importante. Les performances en écriture sont une préoccupation constante car les inserts se font toute la journée, tandis que les lectures sont critiques par à-coups (quand le support ou les ops ont besoin de réponses rapides).
La plupart des lectures sont des filtres, pas des recherches aléatoires. Dans un panneau d'administration, quelqu'un commence généralement large (dernier 24 heures) puis affine par utilisateur, entité ou action.
Les filtres courants incluent :
La plage temporelle est le « premier filtre » naturel car elle est presque toujours présente. C'est l'idée clé derrière le partitionnement PostgreSQL pour les tables d'événements : beaucoup de requêtes demandent une tranche de temps, et tout le reste est un filtre secondaire à l'intérieur de cette tranche.
La rétention est l'autre constante. Les logs ne vivent rarement indéfiniment. Les équipes conservent souvent les événements détaillés 30 ou 90 jours, puis suppriment ou archivents. Les logs d'audit peuvent avoir des exigences plus longues (365 jours ou plus), mais même dans ce cas, on veut un moyen prévisible de retirer les anciennes données sans bloquer la base.
La journalisation d'audit comporte aussi des attentes supplémentaires. On veut généralement une histoire immuable, chaque enregistrement traçable (qui/quoi/quand plus le contexte de requête ou de session), et un contrôle d'accès (tout le monde ne devrait pas voir des événements liés à la sécurité).
Ces patterns se reflètent directement dans le design de l'UI. Les filtres attendus par défaut — sélecteurs de date, sélection d'utilisateur, recherche d'entité, menu d'action — sont les mêmes filtres que la table et les index doivent supporter si vous voulez que l'expérience admin reste rapide avec la croissance du volume.
Le partitionnement n'est pas une meilleure pratique par défaut pour les logs d'audit. Il devient rentable quand une table devient si grande que les requêtes quotidiennes et la maintenance de routine commencent à s'affronter.
Une simple indication de taille : une fois qu'une table d'événements atteint des dizaines de millions de lignes, il vaut la peine de mesurer. Quand la table et ses index occupent des dizaines de gigaoctets, même les recherches sur une plage de dates peuvent devenir lentes ou imprévisibles parce que plus de pages de données sont lues depuis le disque et que les index coûtent à maintenir.
Le signal le plus clair côté requêtes est lorsque vous demandez régulièrement une petite tranche de temps (dernier jour, dernière semaine) mais que PostgreSQL touche encore une large partie de la table. Vous le verrez comme des écrans « activité récente » lents, ou des audits filtrés par date plus utilisateur, type d'action, ou ID d'entité. Si les plans de requête montrent de grands scans ou si les lectures de buffers sont systématiquement élevées, vous payez pour des données que vous ne vouliez pas lire.
Les signaux de maintenance comptent tout autant :
Les coûts opérationnels poussent souvent les équipes vers le partitionnement : sauvegardes et restaurations qui ralentissent, stockage qui augmente, tâches de rétention coûteuses car de grands DELETE provoquent du bloat et du travail de vacuum supplémentaire.
Si vos objectifs principaux sont une politique de rétention propre et des requêtes « période récente » plus rapides, le partitionnement mérite généralement un examen sérieux. Si la table est modérée et que les requêtes sont déjà rapides avec de bons index, le partitionnement peut ajouter de la complexité sans gain clair.
Pour la plupart des données de logs et d'audit, le choix le plus simple est le partitionnement par plage temporelle. Les logs arrivent dans l'ordre du temps, les requêtes se concentrent souvent sur « dernières 24 heures » ou « 30 derniers jours », et la rétention se fait en fonction du temps. Avec des partitions temporelles, supprimer les anciennes données peut être aussi simple que retirer une partition ancienne plutôt que d'exécuter un DELETE massif qui bloat la table.
Le partitionnement temporel garde aussi les index plus petits et mieux ciblés. Chaque partition a ses propres index, donc une requête pour la semaine dernière n'a pas besoin de parcourir un énorme index couvrant des années d'historique.
D'autres styles de partitionnement existent, mais ils correspondent à moins de cas d'usage :
Si vous choisissez des plages temporelles, choisissez une taille de partition qui correspond à la façon dont vous naviguez et conservez les données. Des partitions quotidiennes ont du sens pour des tables à très fort volume ou une rétention stricte. Des partitions mensuelles sont plus faciles à gérer pour un volume modéré.
Un exemple pratique : si une équipe admin vérifie les tentatives de connexion échouées chaque matin et filtre sur les 7 derniers jours, des partitions quotidiennes ou hebdomadaires signifient que la requête ne touche que les partitions récentes. PostgreSQL peut ignorer le reste.
Quelle que soit l'approche, prévoyez les parties ennuyeuses : créer des partitions futures, gérer les événements arrivant en retard, et définir ce qui se passe à chaque frontière (fin de journée, fin de mois). Le partitionnement paye quand ces routines restent simples.
Une bonne clé de partition correspond à la façon dont vous lisez la table, pas à la façon dont les données apparaissent sur un schéma.
Pour les logs et audits, commencez par votre panneau d'administration : quel filtre les gens utilisent-ils en premier, presque tout le temps ? Pour la plupart des équipes c'est une plage temporelle (dernier 24 heures, 7 derniers jours, dates personnalisées). Si c'est votre cas, le partitionnement temporel donne généralement le gain le plus important et le plus prévisible parce que PostgreSQL peut ignorer des partitions entières en dehors de la plage sélectionnée.
Considérez la clé comme une promesse à long terme. Vous optimisez pour les requêtes que vous continuerez à exécuter pendant des années.
La plupart des écrans admin suivent un schéma : plage temporelle plus optionnellement utilisateur, action, statut ou ressource. Partitionnez par l'élément qui réduit les résultats tôt et de manière consistante.
Une vérification rapide :
tenant_id peut avoir du sens, mais seulement si les tenants sont assez volumineux pour le justifier.user_id peut sembler tentant, mais il crée généralement trop de partitions à gérer.Le partitionnement fonctionne mieux quand chaque partition est un morceau de données significatif. Des clés comme user_id, session_id, request_id ou device_id peuvent mener à des milliers ou millions de partitions. Cela augmente la surcharge de métadonnées, complique la maintenance et ralentit souvent le planificateur.
Les partitions basées sur le temps gardent le nombre de partitions prévisible. Vous choisissez quotidien, hebdomadaire ou mensuel selon le volume. Trop peu de partitions (une par an) n'aideront pas beaucoup. Trop (une par heure) ajoute vite de la surcharge.
created_at vs occurred_atSoyez explicite sur ce que représente le temps :
occurred_at : quand l'événement s'est produit dans le produit.created_at : quand la base de données l'a enregistré.Pour les audits, « occurred » est souvent ce qui intéresse les admins. Mais la livraison tardive (clients mobiles hors ligne, retries, queues) signifie que occurred_at peut arriver en retard. Si les arrivées tardives sont courantes, partitionner par created_at et indexer occurred_at pour le filtrage peut être plus stable opérationnellement. L'autre option est de définir une politique de backfill claire et d'accepter que d'anciennes partitions reçoivent occasionnellement des événements en retard.
Décidez aussi comment stocker le temps. Utilisez un type cohérent (souvent timestamptz) et traitez UTC comme source de vérité. Formatez selon le fuseau horaire du visiteur dans l'UI. Cela rend les frontières de partitions stables et évite les surprises liées à l'heure d'été.
Le partitionnement est plus simple quand vous le traitez comme un petit projet de migration, pas comme un tweak rapide. L'objectif est des écritures simples, des lectures prévisibles et une rétention qui devient une opération de routine.
Choisissez une taille de partition adaptée à votre volume. Les partitions mensuelles conviennent généralement pour quelques centaines de milliers de lignes par mois. Si vous insérez des dizaines de millions par mois, des partitions hebdomadaires ou quotidiennes maintiendront les index plus petits et le travail de vacuum plus contenu.
Concevez les clés et contraintes pour les tables partitionnées. Dans PostgreSQL, une contrainte d'unicité doit inclure la clé de partition (ou être appliquée autrement). Un schéma courant est (created_at, id), où id est généré et created_at est la clé de partition. Cela évite les surprises quand vous découvrez qu'une contrainte attendue n'est pas autorisée.
Créez des partitions futures avant d'en avoir besoin. N'attendez pas que des inserts échouent faute de partition correspondante. Décidez à quelle horizon les créer (par exemple 2–3 mois) et faites-en une tâche de routine.
Garder les index par partition petits et ciblés. Le partitionnement ne rend pas les index gratuits. La plupart des tables d'événements ont besoin de la clé de partition plus un ou deux index qui reflètent les filtres réels de l'admin, comme actor_id, entity_id ou event_type. Évitez les index « au cas où ». Vous pouvez les ajouter plus tard sur les nouvelles partitions et rétropliquer sur les anciennes si nécessaire.
Planifiez la rétention autour de la suppression de partitions, pas des DELETE. Si vous conservez 180 jours de logs, supprimer une partition ancienne est rapide et évite les DELETEs massifs et le bloat. Documentez la règle de rétention, qui l'exécute et comment vérifier que cela a fonctionné.
Si votre table d'audit reçoit 5 millions de lignes par semaine, des partitions hebdomadaires sur created_at sont un bon point de départ. Créez des partitions pour 8 semaines à l'avance et conservez deux index par partition : un pour les recherches communes par actor_id et un pour entity_id. Quand la fenêtre de rétention est dépassée, supprimez la partition hebdomadaire la plus ancienne au lieu de supprimer des millions de lignes.
Si vous construisez des outils internes avec AppMaster, il est utile de décider tôt de la clé de partition et des contraintes pour que le modèle de données et le code généré suivent les mêmes hypothèses dès le départ.
Une fois que vous partitionnez une table de logs, les filtres du panneau admin ne sont plus « juste l'UI ». Ils deviennent le facteur principal qui détermine si une requête touche quelques partitions ou scanne des mois de données.
Le plus grand changement pratique : le temps ne peut plus être optionnel. Si les utilisateurs peuvent lancer une recherche non bornée (pas de plage de date, juste « tout pour l'utilisateur X »), PostgreSQL peut devoir vérifier chaque partition. Même si chaque vérification est rapide, ouvrir de nombreuses partitions ajoute une surcharge et la page devient lente.
Une règle efficace : exiger une plage temporelle pour les recherches de logs et d'audit et la définir par défaut sur quelque chose de raisonnable (comme les dernières 24 heures). Si quelqu'un a réellement besoin de « tout le temps », faites-en un choix délibéré et avertissez que les résultats peuvent être plus lents.
Le partition pruning n'aide que si la clause WHERE inclut la clé de partition sous une forme que PostgreSQL peut utiliser. Des filtres comme created_at BETWEEN X AND Y permettent un bon pruning. Les motifs qui brisent souvent le pruning incluent le cast du timestamp en date, l'enveloppement de la colonne dans une fonction, ou le filtrage principalement sur une autre colonne temporelle que la clé de partition.
À l'intérieur de chaque partition, les index doivent correspondre à la manière dont les gens filtrent réellement. En pratique, les combinaisons utiles sont le temps plus une autre condition : tenant/workspace, utilisateur, type d'action, ID d'entité ou statut.
Le partitionnement ne résout pas la pagination lente à lui seul. Si le panneau admin trie par les plus récents et que les utilisateurs sautent à la page 5000, une pagination profonde avec OFFSET force PostgreSQL à parcourir beaucoup de lignes.
La pagination par curseur se comporte mieux pour les logs : « charger les événements avant ce timestamp/id ». Elle permet d'utiliser les index plutôt que de sauter de larges offsets.
Les préréglages aident aussi : quelques options suffisent généralement : dernières 24 heures, 7 jours, aujourd'hui, hier, plage personnalisée. Les préréglages réduisent les recherches « tout balayer » accidentelles et rendent l'expérience admin plus prévisible.
La plupart des projets de partitionnement échouent pour des raisons simples : le partitionnement fonctionne, mais les requêtes et l'UI ne sont pas alignées. Pour que le partitionnement rapporte, basez-le sur des filtres réels et une rétention réelle.
Le pruning ne fonctionne que si la clause WHERE correspond à la clé de partition. Une erreur fréquente est de partitionner par created_at alors que l'UI filtre par event_time (ou l'inverse). Si l'équipe de support demande toujours « que s'est-il passé entre 10:00 et 10:15 », mais que la table est partitionnée par le temps d'ingestion, vous pouvez toucher plus de données que prévu.
Des partitions horaires (ou plus petites) semblent propres, mais elles ajoutent de la surcharge : plus d'objets à gérer, plus de travail pour le planificateur et plus de risques d'index manquants ou de permissions dépareillées.
Sauf si vous avez un volume d'écriture extrêmement élevé et une rétention stricte, les partitions quotidiennes ou mensuelles sont plus faciles à exploiter.
Les tables partitionnées ont des contraintes : certaines contraintes d'unicité doivent inclure la clé de partition, sinon PostgreSQL ne peut pas les appliquer à travers toutes les partitions.
Cela surprend souvent les équipes qui s'attendent à ce que event_id soit unique globalement. Si vous avez besoin d'un identifiant unique, utilisez un UUID et rendez-le unique avec la clé temporelle, ou appliquez l'unicité au niveau applicatif.
Les panneaux admin sont souvent livrés avec une boîte de recherche conviviale qui s'exécute sans filtres. Sur une table de logs partitionnée, cela peut impliquer le scan de toutes les partitions.
La recherche en texte libre sur les payloads est particulièrement risquée. Mettez des garde-fous : exigez une plage temporelle, limitez la plage par défaut et faites de « tout le temps » un choix volontaire.
Le partitionnement ne résout pas automatiquement la rétention. Sans politique, vous vous retrouvez avec une pile de partitions anciennes, un stockage en désordre et une maintenance plus lente.
Un jeu de règles opérationnelles simple évite cela : définir combien de temps garder les événements bruts, automatiser la création des partitions futures et la suppression des anciennes, appliquer les index de façon cohérente, surveiller le nombre de partitions et les dates de frontière, et tester les filtres admin les plus lents sur des volumes réalistes.
Le partitionnement peut être un gros avantage pour les logs d'audit, mais il ajoute du travail de routine. Avant de modifier le schéma, vérifiez comment les gens utilisent réellement la table.
Si votre douleur principale est que les pages admin expirent quand quelqu'un ouvre « Dernières 24 heures » ou « Cette semaine », vous êtes proche d'un bon candidat. Si la plupart des requêtes sont « user ID sur tout l'historique », le partitionnement aide moins sauf si vous modifiez aussi la façon dont l'UI guide les recherches.
Une courte checklist pour rester honnête :
Un test pratique : regardez les trois filtres que votre équipe support ou ops utilise le plus. Si deux d'entre eux sont généralement satisfaits par « plage temporelle + une condition », le partitionnement PostgreSQL pour les tables d'événements vaut souvent la peine d'être sérieusement considéré.
Une équipe support garde deux écrans ouverts toute la journée : « Événements de connexion » (connexions réussies et échouées) et « Audits de sécurité » (réinitialisations de mot de passe, changements de rôle, mises à jour de clés API). Lorsqu'un client signale une activité suspecte, l'équipe filtre par utilisateur, regarde les dernières heures et exporte un court rapport.
Avant le partitionnement, tout est dans une grande table events. Elle grossit vite et même les recherches simples commencent à traîner parce que la base parcourt beaucoup de lignes anciennes. La rétention est aussi pénible : un job nocturne supprime les anciennes lignes, mais les DELETEs massifs prennent du temps, créent du bloat et concurrencent le trafic normal.
Après partitionnement par mois (en utilisant le timestamp d'événement), le flux s'améliore. Le panneau admin exige un filtre temporel, donc la plupart des requêtes ne touchent qu'une ou deux partitions. Les pages se chargent plus vite parce que PostgreSQL peut ignorer les partitions hors plage. La rétention devient une routine : au lieu de supprimer des millions de lignes, on supprime de vieilles partitions.
Une chose reste difficile : la recherche en texte libre sur « tout le temps ». Si quelqu'un cherche une adresse IP ou une phrase vague sans limite de date, le partitionnement ne rend pas cela bon marché. La solution est souvent dans le comportement produit : définir par défaut une fenêtre temporelle et rendre « dernières 24 heures / 7 jours / 30 jours » le chemin évident.
Étapes pratiques suivantes qui fonctionnent bien :
Si vous construisez un panneau admin interne avec AppMaster (appmaster.io), il vaut la peine de modéliser ces hypothèses tôt : considérez les filtres limités dans le temps comme faisant partie du modèle de données, pas seulement un choix UI. Cette petite décision protège les performances des requêtes à mesure que le volume de logs augmente.
When is PostgreSQL partitioning actually worth it for event or audit tables?Le partitionnement est particulièrement utile lorsque vos requêtes courantes sont limitées dans le temps (par exemple « dernières 24 heures » ou « 7 derniers jours ») et que la table est suffisamment volumineuse pour que les index et la maintenance deviennent pénibles. Si vos requêtes principales sont « tout l'historique pour l'utilisateur X », le partitionnement peut ajouter de la complexité à moins que vous n'imposiez des filtres temporels dans l'interface et que vous ajoutiez les bons index par partition.
What partitioning method is the best fit for logs and audit data?Le partitionnement par plage temporelle est généralement le meilleur choix par défaut pour les logs et les audits : les écritures arrivent dans l'ordre chronologique, les requêtes commencent souvent par une fenêtre temporelle et la rétention se fait en fonction du temps. Le partitionnement par liste ou par hash peut convenir à des cas particuliers, mais complique souvent la navigation et la rétention pour des flux d'audit.
How do I choose the right partition key for an audit table?Choisissez le champ que les utilisateurs filtrent en premier et presque toujours. Dans la plupart des panneaux d'administration, il s'agit d'une plage temporelle, donc le partitionnement par le temps est le choix le plus prévisible. Considérez-le comme un engagement à long terme, car changer la clé de partition plus tard implique une vraie migration.
Why is partitioning by user_id usually a bad idea?N'utilisez des clés à haute cardinalité comme user_id que si elles créent un nombre gérable de partitions. Évitez user_id, session_id ou request_id car elles peuvent générer des milliers de partitions, alourdir le planificateur et compliquer l'exploitation sans garantir d'accélérations constantes.
Should I partition by created_at or occurred_at?Partitionnez par created_at si vous avez besoin de stabilité opérationnelle et que vous ne pouvez pas garantir l'arrivée immédiate des événements (queues, retries, clients hors ligne). Partitionnez par occurred_at si votre cas d'usage principal est « que s'est-il passé pendant cette fenêtre » et que l'heure d'événement est fiable. Une approche fréquente est de partitionner par created_at et d'indexer occurred_at pour le filtrage.
Do I really need to require a time range filter in the admin UI?Oui : la plupart des panneaux d'administration devraient exiger une plage temporelle une fois la table partitionnée. Sans filtre temporel, PostgreSQL peut devoir vérifier de nombreuses partitions, ce qui ralentit les pages même si chaque partition est indexée. Un bon défaut est « dernières 24 heures », avec « tout le temps » comme option volontaire.
What can accidentally break partition pruning in my queries?Souvent, oui. Envelopper la clé de partition dans une fonction (par exemple caster en date) peut empêcher le pruning, et filtrer sur une colonne temporelle différente de la clé de partition force l'examen de partitions supplémentaires. Gardez les filtres simples, par exemple created_at BETWEEN X AND Y, pour assurer un pruning fiable.
What’s the best pagination approach for partitioned event tables?Évitez la pagination profonde avec OFFSET pour les vues de logs : elle force la base à parcourir beaucoup de lignes. Privilégiez la pagination par curseur, par exemple « charger les événements avant ce (timestamp, id) », qui reste index-friendly et stable avec la croissance.
How does partitioning affect unique constraints and IDs?Dans PostgreSQL, certaines contraintes d'unicité sur les tables partitionnées doivent inclure la clé de partition, donc une contrainte globale sur id peut ne pas fonctionner. Un schéma pratique est une unicité composite comme (created_at, id) lorsque created_at est la clé de partition. Si vous avez besoin d'un identifiant unique externe, conservez un UUID et gérez l'unicité globale avec précaution.
What’s the simplest retention strategy once a table is partitioned?Supprimer les anciennes partitions est rapide et évite le bloat et le travail de vacuum causés par de grands DELETE. L'important est d'aligner les règles de rétention sur les limites des partitions et d'automatiser la routine : créer des partitions futures et supprimer les partitions expirées selon un calendrier. Sans automatisation, le partitionnement devient une charge manuelle.
21 janv. 2026
8 min
20 janv. 20268 min
20 janv. 20268 minExpérimentez avec AppMaster avec un plan gratuit.
Lorsque vous serez prêt, vous pourrez choisir l'abonnement approprié.
