pg-index-health – un outil d'analyse statique pour votre base de données PostgreSQL

Bonjour !

Depuis 2019, je développe un outil open source appelé pg-index-health, qui analyse les structures des bases de données et identifie les problèmes potentiels. Dans l'un de mes articles précédents, j'ai raconté comment cet outil a vu le jour.

Au fil des années, pg-index-health a évolué et s'est amélioré. En 2024, avec le soutien de plusieurs contributeurs, j'ai réussi à résoudre la plupart de ses « difficultés de croissance » restantes et à amener le projet à un état où il est prêt pour une expansion à grande échelle.

La croissance des bases de données avec l’essor des microservices

Je travaille avec PostgreSQL depuis 2015, et ce voyage fascinant a commencé au sein de la société Tensor, basée à Yaroslavl.

En 2015, c'était encore l'ère des monolithes avec des bases de données massives et un grand nombre de tables. En règle générale, toute modification apportée à la structure de ces bases de données nécessitait l'approbation obligatoire d'un architecte ou d'un responsable du développement, qui servait de détenteur clé des connaissances. Même si cela protégeait contre la plupart des erreurs, cela ralentissait le processus de modification et était totalement inévolutif.

Peu à peu, les gens ont commencé à migrer vers les microservices.
Le nombre de bases de données a augmenté de manière significative, mais le nombre de tables au sein de chaque base de données, à l'inverse, a diminué. Désormais, chaque équipe a commencé à gérer indépendamment la structure de sa propre base de données. La source centralisée d'expertise a disparu et les erreurs de conception des bases de données ont commencé à se multiplier et à se propager d'un service à l'autre.

La pyramide des tests et ses formes

La plupart d’entre vous ont probablement entendu parler de la pyramide des tests. Pour les monolithes, il a une forme assez caractéristique avec une large base de tests unitaires. Pour plus de détails, je vous recommande l'article de Martin Fowler.

Les microservices ont changé non seulement l'approche du développement mais aussi l'apparence de la pyramide des tests. Ce changement est largement dû à l’essor des technologies de conteneurisation (Docker, Testcontainers). Aujourd’hui, la pyramide des tests n’est plus du tout une pyramide. Cela peut avoir une forme très bizarre. Les exemples les plus connus sont le Honeycomb et le Testing Trophy.

La tendance moderne est d'écrire le moins de tests unitaires possible, en se concentrant sur les détails d'implémentation, et de donner la priorité aux tests de composants et d'intégration qui valident la fonctionnalité réelle fournie par le service.

Mon préféré est le Trophée des tests. À sa base se trouve l'analyse de code statique, conçue pour se prémunir contre les erreurs courantes.

L'importance de l'analyse du code statique

L'analyse statique du code Java et Kotlin est désormais une pratique courante. Pour les services Kotlin, l’outil de choix est généralement détecté. Pour les applications Java, la gamme d'outils disponibles (souvent appelés linters) est plus large. Les principaux outils incluent Checkstyle, PMD, SpotBugs et Error Prone. Vous pouvez en savoir plus à leur sujet dans mon article précédent.

Notamment, detekt et Checkstyle gèrent également le formatage du code, fonctionnant efficacement comme formateurs.

Analyse statique pour les migrations de bases de données

Les microservices modernes incluent souvent des migrations de bases de données pour créer et mettre à jour la structure de la base de données ainsi que le code de l'application.

Dans l'écosystème Java, les principaux outils de gestion des migrations sont Liquibase et Flyway. Toute modification apportée à la structure de la base de données doit toujours être documentée lors des migrations. Même si des modifications sont apportées manuellement lors d'un incident en production, une migration doit ensuite être créée pour appliquer ces modifications dans tous les environnements.

L'écriture des migrations en SQL simple est la meilleure pratique car elle offre une flexibilité maximale et permet de gagner du temps par rapport à l'apprentissage des dialectes XML d'outils comme Liquibase. J'en ai parlé dans mon article « Six conseils pour utiliser PostgreSQL dans les tests fonctionnels ».

Vérification du code de migration SQL

Pour vérifier le code SQL dans les migrations, je recommande d'utiliser SQLFluff, qui est essentiellement un équivalent Checkstyle pour SQL. Ce linter prend en charge plusieurs bases de données et dialectes (y compris PostgreSQL) et peut être intégré à votre pipeline CI. Il propose plus de 60 règles personnalisables, vous permettant de gérer les alias de tables et de colonnes, la casse des commandes SQL, l'indentation, l'ordre des colonnes dans les requêtes et bien plus encore.

Comparez la requête avec et sans mise en forme :

-- well-formatted SQL
select
    pc.oid::regclass::text as table_name,
    pg_table_size(pc.oid) as table_size
from
    pg_catalog.pg_class pc
    inner join pg_catalog.pg_namespace nsp on nsp.oid = pc.relnamespace
where
    pc.relkind = 'r' and
    pc.oid not in (
        select c.conrelid as table_oid
        from pg_catalog.pg_constraint c
        where c.contype = 'p'
    ) and
    nsp.nspname = :schema_name_param::text
order by table_name;

-- poorly formatted SQL
SELECT pc.oid::regclass::text AS table_name, pg_table_size(pc.oid) AS table_size
FROM pg_catalog.pg_class  pc
JOIN pg_catalog.pg_namespace AS nsp
ON nsp.oid =  pc.relnamespace
WHERE pc.relkind = 'r’
and pc.oid NOT in (
  select c.conrelid as table_oid
  from pg_catalog.pg_constraint   c
  where    c.contype = 'p’
)
and nsp.nspname  = :schema_name_param::text
ORDER BY  table_name;

Un code SQL bien formaté est beaucoup plus facile à lire et à comprendre. Plus important encore, les révisions de code ne seront plus enlisées par des discussions sur les préférences de formatage. SQLFluff applique un style cohérent, ce qui permet de gagner du temps.

SQLFluff en action

Voici à quoi cela ressemble dans une vraie pull request :

Ici SQLFluff a trouvé un problème avec le formatage de la valeur de retour dans l'instruction select : lorsqu'une seule colonne est renvoyée, nous ne la mettons pas sur un rangée séparée. Le deuxième point est l'ordre incorrect des colonnes dans les résultats de la sélection : nous renvoyons d'abord des colonnes simples et ensuite seulement les résultats du calcul. Et le troisième est la casse incorrecte pour et dans l'instruction join : je préfère écrire toutes les requêtes en minuscules.

Pour plus d'exemples d'utilisation de SQLFluff, consultez mes projets open source : un, deux.

Utiliser des métadonnées pour analyser la structure de la base de données

La structure de la base de données elle-même peut également être vérifiée. Cependant, travailler avec des migrations est extrêmement gênant : il peut y en avoir beaucoup ; une nouvelle migration peut corriger les erreurs d'une migration précédente, et ainsi de suite. En règle générale, nous sommes plus intéressés par la structure finale de la base de données que par son état intermédiaire.

Tirer parti du schéma d’information

PostgreSQL (comme beaucoup d'autres bases de données relationnelles) stocke les métadonnées sur tous les objets et les relations entre eux et les fournit en externe sous la forme d'un schéma d'information. Nous pouvons utiliser des requêtes vers information_schema pour identifier tout écart, problème ou erreur courante (c'est exactement ce que fait SchemaCrawler).

Puisque nous travaillons uniquement avec PostgreSQL, au lieu de information_schema nous pouvons utiliser des catalogues système (le schéma pg_catalog), qui fournissent beaucoup plus d'informations sur la structure interne d'une base de données spécifique.

Système de statistiques cumulatives

En plus des métadonnées, PostgreSQL collecte des informations sur le fonctionnement de chaque base de données : quelles requêtes sont exécutées, comment elles sont exécutées, quelles méthodes d'accès sont utilisées, etc. Le système de statistiques cumulées est chargé de collecter ces données.

En interrogeant ces statistiques via les vues système et en les combinant avec les données des catalogues système, nous pouvons :

• Identifier les index inutilisés ;
• Détectez les tables qui ne disposent pas d'une indexation adéquate.

Les statistiques peuvent être réinitialisées manuellement. La date et l'heure de la dernière réinitialisation sont enregistrées dans le système. Il est important d’en tenir compte pour comprendre si les statistiques sont fiables ou non. Par exemple, si vous disposez d'une logique métier qui est exécutée une fois par mois/trimestre/semestre, les statistiques doivent être collectées pour une période d'au moins l'intervalle mentionné ci-dessus.

Si un cluster de bases de données est utilisé, les statistiques sont collectées indépendamment sur chaque hôte et ne sont pas répliquées au sein du cluster.

pg-index-health et sa structure

L'idée d'analyser la structure d'une base de données en fonction des métadonnées au sein de la base de données elle-même, comme décrit ci-dessus, a été mise en œuvre par moi sous la forme d'un outil appelé pg-index-health.

Ma solution comprend les composants suivants :

• Un ensemble de contrôles sous forme de requêtes SQL, placés dans un référentiel séparé (actuellement composé de 25 contrôles). Les requêtes sont découplées de la base de code Java et peuvent être réutilisées dans des projets écrits dans d'autres langages de programmation.
• Un modèle de domaine — un ensemble minimal de classes qui représentent les résultats des vérifications sous forme d'objets.
• L'abstraction HighAvailabilityPgConnection pour la connexion à un cluster de bases de données composé de plusieurs hôtes.
• Utilitaires pour exécuter des requêtes SQL et sérialiser les résultats dans des objets de modèle de domaine.
• Un démarreur Spring Boot pour une intégration pratique et rapide des contrôles dans les tests unitaires/composants/intégration.
• Un générateur de migration capable de créer des migrations SQL correctives pour les problèmes identifiés.

Types de chèques

Tous les contrôles (également appelés diagnostics) sont divisés en deux groupes :

• Contrôles d'exécution (nécessitent des statistiques).
• Contrôles statiques (ne nécessitent pas de statistiques).

Vérifications d'exécution

Les

Les contrôles d'exécution n'ont de sens que lorsqu'ils sont exécutés sur une instance de base de données active en production. Ces vérifications nécessitent des statistiques accumulées et regroupent ces données de tous les hôtes du cluster.

Considérons un cluster de bases de données composé de trois hôtes : réplica principal, secondaire et asynchrone. Certains services utilisent des clusters avec des topologies similaires et exécutent des requêtes de lecture lourdes uniquement sur le réplica asynchrone pour équilibrer la charge. De telles requêtes ne sont généralement pas exécutées sur l'hôte principal car elles créent une charge supplémentaire et ont un impact négatif sur la latence des autres requêtes.

Comme mentionné précédemment, dans PostgreSQL, les statistiques sont collectées séparément sur chaque hôte et ne sont pas répliquées au sein du cluster. Ainsi, vous pouvez facilement rencontrer une situation où certains index sont utilisés et nécessaires uniquement sur la réplique asynchrone. Pour déterminer de manière fiable si un index est nécessaire ou non, il est nécessaire d'exécuter la vérification sur chaque hôte du cluster et d'agréger les résultats.

Contrôles statiques

Les

Les contrôles statiques ne nécessitent pas de statistiques accumulées et peuvent être exécutés sur l'hôte principal immédiatement après l'application des migrations. Bien entendu, ils peuvent également être utilisés sur une base de données de production pour obtenir des données en temps réel. Cependant, la plupart des contrôles sont statiques et sont particulièrement utiles dans les tests, car ils permettent de détecter et de prévenir les erreurs courantes pendant la phase de développement.

Comment utiliser pg-index-health

Le principal cas d'utilisation de pg-index-health consiste à ajouter des tests pour vérifier la structure de la base de données dans votre pipeline de tests.

Pour les applications Spring Boot, vous devez ajouter le starter à vos dépendances de test :

-- well-formatted SQL
select
    pc.oid::regclass::text as table_name,
    pg_table_size(pc.oid) as table_size
from
    pg_catalog.pg_class pc
    inner join pg_catalog.pg_namespace nsp on nsp.oid = pc.relnamespace
where
    pc.relkind = 'r' and
    pc.oid not in (
        select c.conrelid as table_oid
        from pg_catalog.pg_constraint c
        where c.contype = 'p'
    ) and
    nsp.nspname = :schema_name_param::text
order by table_name;

Ajoutez ensuite un test standard :

-- poorly formatted SQL
SELECT pc.oid::regclass::text AS table_name, pg_table_size(pc.oid) AS table_size
FROM pg_catalog.pg_class  pc
JOIN pg_catalog.pg_namespace AS nsp
ON nsp.oid =  pc.relnamespace
WHERE pc.relkind = 'r’
and pc.oid NOT in (
  select c.conrelid as table_oid
  from pg_catalog.pg_constraint   c
  where    c.contype = 'p’
)
and nsp.nspname  = :schema_name_param::text
ORDER BY  table_name;

Dans ce test, tous les chèques disponibles sont injectés sous forme de liste. Ensuite, seules les vérifications statiques sont filtrées et exécutées sur la base de données réelle déployée dans un conteneur avec les migrations appliquées.

Idéalement, chaque vérification devrait renvoyer une liste vide. S'il y a des écarts lors de l'ajout de la prochaine migration, le test échouera. Le développeur sera obligé d'y prêter attention et de résoudre le problème de n'importe quelle manière : soit le corriger dans sa migration, soit l'ignorer explicitement.

Faux positifs et ajout d'exclusions

Il est important de comprendre que pg-index-health, comme tout autre analyseur statique, peut générer des faux positifs. De plus, certaines vérifications peuvent ne pas être pertinentes pour votre projet. Par exemple, il est considéré comme une bonne pratique de documenter la structure de la base de données. PostgreSQL permet d'ajouter des commentaires à presque tous les objets de la base de données. Dans une migration, cela pourrait ressembler à ceci :

dependencies {
    testImplementation("io.github.mfvanek:pg-index-health-test-starter:0.14.4")
}

Au sein de votre équipe, vous pourriez être d’accord pour ne pas faire cela. Dans ce cas, les résultats des contrôles correspondants (TABLES_WITHOUT_DESCRIPTION, COLUMNS_WITHOUT_DESCRIPTION, FUNCTIONS_WITHOUT_DESCRIPTION) ne vous concernent plus.

Vous pouvez soit exclure complètement ces chèques :

import io.github.mfvanek.pg.core.checks.common.DatabaseCheckOnHost;
import io.github.mfvanek.pg.core.checks.common.Diagnostic;
import io.github.mfvanek.pg.model.dbobject.DbObject;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;

import java.util.List;

import static org.assertj.core.api.Assertions.assertThat;

@SpringBootTest
@ActiveProfiles("test")
class DatabaseStructureStaticAnalysisTest {

    @Autowired
    private List<DatabaseCheckOnHost<? extends DbObject>> checks;

    @Test
    void checksShouldWork() {
        assertThat(checks)
            .hasSameSizeAs(Diagnostic.values());

        checks.stream()
            .filter(DatabaseCheckOnHost::isStatic)
            .forEach(c -> assertThat(c.check())
                .as(c.getDiagnostic().name())
                .isEmpty());
    }
}

Ou ignorez simplement leurs résultats :

create table if not exists demo.warehouse
(
    id bigint primary key generated always as identity,
    name varchar(255) not null
);

comment on table demo.warehouse is 'Information about the warehouses';
comment on column demo.warehouse.id is 'Unique identifier of the warehouse';
comment on column demo.warehouse.name is 'Human readable name of the warehouse';

Lors de l'introduction de pg-index-health, vous pouvez souvent rencontrer des situations dans lesquelles la structure de la base de données présente déjà quelques écarts, mais vous ne souhaitez pas y remédier immédiatement. En même temps, la vérification est pertinente et sa désactivation n'est pas une option. Dans de tels cas, il est préférable de corriger tous les écarts dans le code :

@Test
void checksShouldWork() {
    assertThat(checks)
        .hasSameSizeAs(Diagnostic.values());

    checks.stream()
        .filter(DatabaseCheckOnHost::isStatic)
        .filter(c -> c.getDiagnostic() != Diagnostic.TABLES_WITHOUT_DESCRIPTION &&
            c.getDiagnostic() != Diagnostic.COLUMNS_WITHOUT_DESCRIPTION)
        .forEach(c -> assertThat(c.check())
            .as(c.getDiagnostic().name())
            .isEmpty());
}

Maintenant, j'aimerais me concentrer plus en détail sur les problèmes les plus fréquemment rencontrés.

Tables sans clés primaires

En raison des spécificités du mécanisme MVCC dans PostgreSQL, des situations telles que le ballonnement peuvent survenir, où la taille de votre table (ou index) augmente rapidement en raison d'un grand nombre de tuples morts. Cela peut se produire, par exemple, à la suite de transactions de longue durée ou d'une mise à jour unique d'un grand nombre de lignes.

Le garbage collection au sein de la base de données est géré par le processus autovacuum, mais il ne libère pas l'espace disque physique occupé. La seule façon de réduire efficacement la taille physique d'une table est d'utiliser la commande VACUUM FULL, qui nécessite un verrouillage exclusif pendant toute la durée de l'opération. Pour les grandes tables, cela peut prendre plusieurs heures, ce qui rend l'aspiration complète peu pratique pour la plupart des services modernes.

Pour résoudre le problème du ballonnement des tables sans temps d'arrêt, des extensions tierces comme pg_repack sont souvent utilisées. L'une des exigences obligatoires de pg_repack est la présence d'une clé primaire ou d'une autre contrainte d'unicité sur la table cible. Le diagnostic TABLES_WITHOUT_PRIMARY_KEY aide à détecter les tables sans clés primaires et évite les problèmes de maintenance à l'avenir.

Vous trouverez ci-dessous un exemple de table sans clé primaire. Si bloat se produit dans ce tableau, pg_repack ne pourra pas le traiter et renverra une erreur.

-- well-formatted SQL
select
    pc.oid::regclass::text as table_name,
    pg_table_size(pc.oid) as table_size
from
    pg_catalog.pg_class pc
    inner join pg_catalog.pg_namespace nsp on nsp.oid = pc.relnamespace
where
    pc.relkind = 'r' and
    pc.oid not in (
        select c.conrelid as table_oid
        from pg_catalog.pg_constraint c
        where c.contype = 'p'
    ) and
    nsp.nspname = :schema_name_param::text
order by table_name;

Index dupliqués

Nos bases de données fonctionnent sur des hôtes aux ressources limitées, et l'espace disque en fait partie. Lors de l'utilisation de solutions Database-as-a-Service, il existe souvent une limitation physique sur la taille maximale de la base de données qui ne peut pas être modifiée.

Chaque index d'une table est une entité distincte sur le disque. Il occupe de l'espace et nécessite des ressources pour la maintenance, ce qui ralentit l'insertion et les mises à jour des données. Nous créons des index pour accélérer les recherches ou garantir l'unicité de certaines valeurs. Cependant, une mauvaise utilisation des index peut conduire à une situation dans laquelle leur taille combinée dépasse la taille des données utiles dans la table elle-même. Ainsi, le nombre d'index dans une table doit être minimal mais suffisant pour sa fonctionnalité.

J'ai rencontré de nombreux cas où des index inutiles sont créés lors des migrations. Par exemple, un index pour une clé primaire est créé automatiquement. Bien qu'il soit techniquement possible d'indexer manuellement la colonne id, cela est totalement inutile.

-- poorly formatted SQL
SELECT pc.oid::regclass::text AS table_name, pg_table_size(pc.oid) AS table_size
FROM pg_catalog.pg_class  pc
JOIN pg_catalog.pg_namespace AS nsp
ON nsp.oid =  pc.relnamespace
WHERE pc.relkind = 'r’
and pc.oid NOT in (
  select c.conrelid as table_oid
  from pg_catalog.pg_constraint   c
  where    c.contype = 'p’
)
and nsp.nspname  = :schema_name_param::text
ORDER BY  table_name;

Une situation similaire se présente avec des contraintes uniques. Lorsque vous marquez une colonne (ou un groupe de colonnes) avec le mot-clé unique, PostgreSQL crée automatiquement un index unique pour cette colonne (ou groupe de colonnes) . La création manuelle d'index supplémentaires n'est pas nécessaire. Si cela est fait, cela entraîne des index dupliqués. De tels index redondants peuvent et doivent être supprimés, et le diagnostic DUPLICATED_INDEXES peut aider à les identifier.

-- well-formatted SQL
select
    pc.oid::regclass::text as table_name,
    pg_table_size(pc.oid) as table_size
from
    pg_catalog.pg_class pc
    inner join pg_catalog.pg_namespace nsp on nsp.oid = pc.relnamespace
where
    pc.relkind = 'r' and
    pc.oid not in (
        select c.conrelid as table_oid
        from pg_catalog.pg_constraint c
        where c.contype = 'p'
    ) and
    nsp.nspname = :schema_name_param::text
order by table_name;

Index qui se chevauchent (se croisent)

La plupart des index sont créés pour une seule colonne. Lorsque l'optimisation des requêtes commence, des index plus complexes peuvent être ajoutés, impliquant plusieurs colonnes. Cela conduit à des scénarios dans lesquels des index sont créés pour des colonnes comme A, A B et A B C. Les deux premiers index de cette série peuvent souvent être ignorés car ils sont des préfixes du troisième (je recommande de regarder cette vidéo) . La suppression de ces index redondants peut économiser un espace disque important, et le diagnostic INTERSECTED_INDEXES est conçu pour détecter de tels cas.

-- poorly formatted SQL
SELECT pc.oid::regclass::text AS table_name, pg_table_size(pc.oid) AS table_size
FROM pg_catalog.pg_class  pc
JOIN pg_catalog.pg_namespace AS nsp
ON nsp.oid =  pc.relnamespace
WHERE pc.relkind = 'r’
and pc.oid NOT in (
  select c.conrelid as table_oid
  from pg_catalog.pg_constraint   c
  where    c.contype = 'p’
)
and nsp.nspname  = :schema_name_param::text
ORDER BY  table_name;

Clés étrangères sans index

PostgreSQL permet la création de contraintes de clé étrangère sans spécifier d'index de support, ce qui signifie qu'une table référençant une autre ne nécessite pas et ne créera pas automatiquement d'index. Dans certains cas, cela peut ne pas poser de problème, voire ne pas se manifester du tout. Cependant, cela peut parfois entraîner des incidents de production.

Regardons un petit exemple (j'utilise PostgreSQL 16.6) :

dependencies {
    testImplementation("io.github.mfvanek:pg-index-health-test-starter:0.14.4")
}

Nous avons une table commandes et une table order_item. Ils sont liés via une clé étrangère sur la colonne order_id. La clé étrangère doit toujours faire référence soit à la clé primaire, soit à une contrainte unique, ce qui est satisfait dans notre cas.

Remplissons les tableaux avec des données et collectons des statistiques. Nous ajouterons 100 000 commandes, la moitié ayant deux articles et les autres en ayant un.

import io.github.mfvanek.pg.core.checks.common.DatabaseCheckOnHost;
import io.github.mfvanek.pg.core.checks.common.Diagnostic;
import io.github.mfvanek.pg.model.dbobject.DbObject;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;

import java.util.List;

import static org.assertj.core.api.Assertions.assertThat;

@SpringBootTest
@ActiveProfiles("test")
class DatabaseStructureStaticAnalysisTest {

    @Autowired
    private List<DatabaseCheckOnHost<? extends DbObject>> checks;

    @Test
    void checksShouldWork() {
        assertThat(checks)
            .hasSameSizeAs(Diagnostic.values());

        checks.stream()
            .filter(DatabaseCheckOnHost::isStatic)
            .forEach(c -> assertThat(c.check())
                .as(c.getDiagnostic().name())
                .isEmpty());
    }
}

Si nous essayons de récupérer des articles pour une commande avec l'ID=100, nous devrions réussir à renvoyer 2 lignes. Puisqu'il y a un index sur la colonne id dans la table des commandes, il peut sembler que cette requête devrait être rapide.

create table if not exists demo.warehouse
(
    id bigint primary key generated always as identity,
    name varchar(255) not null
);

comment on table demo.warehouse is 'Information about the warehouses';
comment on column demo.warehouse.id is 'Unique identifier of the warehouse';
comment on column demo.warehouse.name is 'Human readable name of the warehouse';

Cependant, si nous essayons de profiler cette requête, nous verrons une analyse séquentielle de la table dans le plan d'exécution. Il faut également s'inquiéter du grand nombre de pages à lire (paramètre Buffers).

@Test
void checksShouldWork() {
    assertThat(checks)
        .hasSameSizeAs(Diagnostic.values());

    checks.stream()
        .filter(DatabaseCheckOnHost::isStatic)
        .filter(c -> c.getDiagnostic() != Diagnostic.TABLES_WITHOUT_DESCRIPTION &&
            c.getDiagnostic() != Diagnostic.COLUMNS_WITHOUT_DESCRIPTION)
        .forEach(c -> assertThat(c.check())
            .as(c.getDiagnostic().name())
            .isEmpty());
}

@Test
void checksShouldWork() {
    assertThat(checks)
        .hasSameSizeAs(Diagnostic.values());

    checks.stream()
        .filter(DatabaseCheckOnHost::isStatic)
        .forEach(c -> {
            final ListAssert<? extends DbObject> listAssert = assertThat(c.check())
                .as(c.getDiagnostic().name());
            switch (c.getDiagnostic()) {
                case TABLES_WITHOUT_DESCRIPTION, COLUMNS_WITHOUT_DESCRIPTION -> listAssert.hasSizeGreaterThanOrEqualTo(0); // ignored

                default -> listAssert.isEmpty();
            }
        });
}

Si on crée un index pour une colonne avec une clé étrangère, la situation reviendra à la normale :

@Test
void checksShouldWorkForAdditionalSchema() {
    final PgContext ctx = PgContext.of("additional_schema");
    checks.stream()
        .filter(DatabaseCheckOnHost::isStatic)
        .forEach(c -> {
            final ListAssert<? extends DbObject> listAssert = assertThat(c.check(ctx))
                .as(c.getDiagnostic().name());

            switch (c.getDiagnostic()) {
                case TABLES_WITHOUT_DESCRIPTION, TABLES_NOT_LINKED_TO_OTHERS ->
                    listAssert.hasSize(1)
                        .asInstanceOf(list(Table.class))
                        .containsExactly(
                            Table.of(ctx, "additional_table")
                        );

                default -> listAssert.isEmpty();
            }
        });
}

L'analyse séquentielle disparaîtra du plan de requête, et le nombre de pages lues sera considérablement réduit :

create table if not exists demo.payment
(
    id bigint not null, -- column is not marked as primary key
    order_id bigint references demo.orders (id),
    status int not null,
    created_at timestamp not null,
    payment_total decimal(22, 2) not null
);

Le diagnostic FOREIGN_KEYS_WITHOUT_INDEX vous permettra de détecter de tels cas au début du développement, évitant ainsi les problèmes de performances.

Dois-je créer des index ou non ?

Il est important de se rappeler le problème des faux positifs : toutes les colonnes de clé étrangère n'ont pas besoin d'être indexées. Essayez d'estimer la taille approximative de la table en production ; vérifiez votre code pour filtrer, rechercher ou rejoindre la colonne de clé étrangère. Si vous êtes sûr à 100 % de ne pas avoir besoin de l’index, vous pouvez simplement l’ajouter aux exclusions. En cas de doute, il est préférable de créer l'index (il pourra toujours être supprimé ultérieurement).

J'ai souvent rencontré des incidents où la base de données « ralentissait » à cause de l'absence d'index sur une clé étrangère, mais je n'ai vu aucun incident où la base de données « ralentissait » à cause de la présence de tels index . Par conséquent, je ne suis pas d'accord avec le point soulevé dans l'article du blog Percona selon lequel les index de clés étrangères ne devraient pas du tout être créés dès le départ. Il s’agit d’une approche DBA. Avez-vous un DBA dédié dans votre équipe ?

Valeur nulle dans les index

Par défaut, PostgreSQL inclut des valeurs nulles dans les index btree, mais elles n'y sont généralement pas nécessaires. Toutes les valeurs nulles sont uniques et vous ne pouvez pas simplement récupérer un enregistrement dont la valeur de colonne est nulle. La plupart du temps, il est préférable d'exclure les valeurs nulles de l'index en créant des index partiels sur les colonnes nullable comme Where n'est pas nul. Le diagnostic INDEXES_WITH_NULL_VALUES permet de détecter de tels cas.

Prenons un exemple avec commandes et order_items. La table order_item a une colonne nullable warehouse_id, qui représente l'ID de l'entrepôt.

-- well-formatted SQL
select
    pc.oid::regclass::text as table_name,
    pg_table_size(pc.oid) as table_size
from
    pg_catalog.pg_class pc
    inner join pg_catalog.pg_namespace nsp on nsp.oid = pc.relnamespace
where
    pc.relkind = 'r' and
    pc.oid not in (
        select c.conrelid as table_oid
        from pg_catalog.pg_constraint c
        where c.contype = 'p'
    ) and
    nsp.nspname = :schema_name_param::text
order by table_name;

Supposons que nous ayons plusieurs entrepôts. Une fois la commande payée, nous commençons à l'assembler. Nous mettrons à jour le statut de certaines commandes et les marquerons comme payées.

-- poorly formatted SQL
SELECT pc.oid::regclass::text AS table_name, pg_table_size(pc.oid) AS table_size
FROM pg_catalog.pg_class  pc
JOIN pg_catalog.pg_namespace AS nsp
ON nsp.oid =  pc.relnamespace
WHERE pc.relkind = 'r’
and pc.oid NOT in (
  select c.conrelid as table_oid
  from pg_catalog.pg_constraint   c
  where    c.contype = 'p’
)
and nsp.nspname  = :schema_name_param::text
ORDER BY  table_name;

Les articles individuels d'une commande peuvent être expédiés depuis différents entrepôts selon un algorithme interne, en tenant compte de la logistique, du stock, de la charge de l'entrepôt, etc. Après avoir attribué l'entrepôt et mis à jour le stock, nous mettons à jour le warehouse_id champ pour chaque article de la commande (qui était initialement nul).

dependencies {
    testImplementation("io.github.mfvanek:pg-index-health-test-starter:0.14.4")
}

Nous devrons effectuer une recherche par identifiant d'entrepôt spécifique pour savoir quels articles doivent être complétés et expédiés. Nous prenons uniquement les commandes payantes pour une certaine plage horaire.

import io.github.mfvanek.pg.core.checks.common.DatabaseCheckOnHost;
import io.github.mfvanek.pg.core.checks.common.Diagnostic;
import io.github.mfvanek.pg.model.dbobject.DbObject;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;

import java.util.List;

import static org.assertj.core.api.Assertions.assertThat;

@SpringBootTest
@ActiveProfiles("test")
class DatabaseStructureStaticAnalysisTest {

    @Autowired
    private List<DatabaseCheckOnHost<? extends DbObject>> checks;

    @Test
    void checksShouldWork() {
        assertThat(checks)
            .hasSameSizeAs(Diagnostic.values());

        checks.stream()
            .filter(DatabaseCheckOnHost::isStatic)
            .forEach(c -> assertThat(c.check())
                .as(c.getDiagnostic().name())
                .isEmpty());
    }
}

La première solution serait probablement un index régulier sur la colonne warehouse_id :

-- well-formatted SQL
select
    pc.oid::regclass::text as table_name,
    pg_table_size(pc.oid) as table_size
from
    pg_catalog.pg_class pc
    inner join pg_catalog.pg_namespace nsp on nsp.oid = pc.relnamespace
where
    pc.relkind = 'r' and
    pc.oid not in (
        select c.conrelid as table_oid
        from pg_catalog.pg_constraint c
        where c.contype = 'p'
    ) and
    nsp.nspname = :schema_name_param::text
order by table_name;

Si nous créons un tel index, il sera utilisé sans problème lors de la recherche d'articles pour un entrepôt spécifique. Il peut sembler que cet index devrait permettre de retrouver efficacement tous les articles pour lesquels l'entrepôt n'est pas encore attribué, en filtrant les enregistrements avec la condition Warehouse_id est nulle.

-- poorly formatted SQL
SELECT pc.oid::regclass::text AS table_name, pg_table_size(pc.oid) AS table_size
FROM pg_catalog.pg_class  pc
JOIN pg_catalog.pg_namespace AS nsp
ON nsp.oid =  pc.relnamespace
WHERE pc.relkind = 'r’
and pc.oid NOT in (
  select c.conrelid as table_oid
  from pg_catalog.pg_constraint   c
  where    c.contype = 'p’
)
and nsp.nspname  = :schema_name_param::text
ORDER BY  table_name;

Cependant, si nous examinons le plan d'exécution des requêtes, nous y verrons un accès séquentiel — l'index n'est pas utilisé.

dependencies {
    testImplementation("io.github.mfvanek:pg-index-health-test-starter:0.14.4")
}

Bien sûr, cela est lié à la répartition spécifique des données dans la base de données de test. La colonne warehouse_id a une faible cardinalité, ce qui signifie que le nombre de valeurs uniques qu'elle contient est faible. Un index sur cette colonne a une faible sélectivité. La sélectivité de l'index fait référence au rapport entre le nombre de valeurs indexées distinctes (c'est-à-dire la cardinalité) et le nombre total de lignes dans la table distinct / count(). Par exemple, un index unique a une sélectivité de un.

Nous pouvons augmenter la sélectivité de l'index en supprimant les valeurs nulles et en créant un index partiel sur la colonne warehouse_id.

import io.github.mfvanek.pg.core.checks.common.DatabaseCheckOnHost;
import io.github.mfvanek.pg.core.checks.common.Diagnostic;
import io.github.mfvanek.pg.model.dbobject.DbObject;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;

import java.util.List;

import static org.assertj.core.api.Assertions.assertThat;

@SpringBootTest
@ActiveProfiles("test")
class DatabaseStructureStaticAnalysisTest {

    @Autowired
    private List<DatabaseCheckOnHost<? extends DbObject>> checks;

    @Test
    void checksShouldWork() {
        assertThat(checks)
            .hasSameSizeAs(Diagnostic.values());

        checks.stream()
            .filter(DatabaseCheckOnHost::isStatic)
            .forEach(c -> assertThat(c.check())
                .as(c.getDiagnostic().name())
                .isEmpty());
    }
}

Nous verrons immédiatement cet index dans le plan de requête :

create table if not exists demo.warehouse
(
    id bigint primary key generated always as identity,
    name varchar(255) not null
);

comment on table demo.warehouse is 'Information about the warehouses';
comment on column demo.warehouse.id is 'Unique identifier of the warehouse';
comment on column demo.warehouse.name is 'Human readable name of the warehouse';

Si l'on compare les tailles des index, nous verrons une différence significative. L'index partiel est beaucoup plus petit et sera mis à jour moins fréquemment. Grâce à cet index, nous économisons de l'espace disque et améliorons les performances.

Requête pour obtenir la taille des index

@Test
void checksShouldWork() {
    assertThat(checks)
        .hasSameSizeAs(Diagnostic.values());

    checks.stream()
        .filter(DatabaseCheckOnHost::isStatic)
        .filter(c -> c.getDiagnostic() != Diagnostic.TABLES_WITHOUT_DESCRIPTION &&
            c.getDiagnostic() != Diagnostic.COLUMNS_WITHOUT_DESCRIPTION)
        .forEach(c -> assertThat(c.check())
            .as(c.getDiagnostic().name())
            .isEmpty());
}

table_name	index_name	index_size_bytes
demo.order_item	demo.idx_order_item_warehouse_id	1056768
demo.order_item	demo.idx_order_item_warehouse_id_without_nulls	16384

Plans pour l'avenir

Ce sont loin d'être tous les problèmes que pg-index-health peut détecter. La liste complète des diagnostics est disponible dans le README du projet sur GitHub, et elle est régulièrement enrichie.

Intégrer pg-index-health dans une application Spring Boot est assez simple. Les frais généraux liés à l’exécution des contrôles sont minimes. En conséquence, vous bénéficierez d’une protection contre les erreurs et problèmes courants. Je vous encourage à essayer de le mettre en œuvre !

Dans un avenir proche, je prévois d'ajouter la prise en charge complète des tables partitionnées dans toutes les vérifications. Actuellement, cela n’est mis en œuvre que pour 11 contrôles sur 25. Je souhaite également augmenter le nombre de contrôles : il existe déjà des tickets pour la mise en œuvre d'au moins 5 nouveaux contrôles. De plus, en 2025, je prévois de passer à Java 17 et Spring Boot 3.

Liens vers le référentiel

• pg-index-santé
• Requêtes SQL brutes pour les chèques
• Applications de démonstration

Matériel supplémentaire

• Mon message original en russe
• Une solution similaire - SchemaCrawler
• DBA : trouver des index inutiles (en russe)
• La santé des index dans PostgreSQL à travers les yeux d'un développeur Java (en russe)
• Analyse statique de la structure de la base de données (en russe)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!