Hallo!

Seit 2019 entwickle ich ein Open-Source-Tool namens pg-index-health, das Datenbankstrukturen analysiert und potenzielle Probleme identifiziert. In einem meiner vorherigen Artikel habe ich die Geschichte erz?hlt, wie dieses Tool zum Leben erweckt wurde.

Im Laufe der Jahre hat sich pg-index-health weiterentwickelt und verbessert. Im Jahr 2024 gelang es mir mit der Unterstützung mehrerer Mitwirkender, die meisten seiner verbleibenden ?Wachstumsschwierigkeiten“ zu bew?ltigen und das Projekt in einen Zustand zu bringen, in dem es für eine gro? angelegte Erweiterung bereit ist.

Das Wachstum von Datenbanken mit dem Aufstieg von Microservices

Ich arbeite seit 2015 mit PostgreSQL und diese faszinierende Reise begann bei der in Jaroslawl ans?ssigen Firma Tensor.

Im Jahr 2015 herrschte noch die ?ra der Monolithen mit riesigen Datenbanken und einer gro?en Anzahl an Tabellen. Typischerweise erforderten alle ?nderungen an der Struktur solcher Datenbanken die zwingende Genehmigung eines Architekten oder Entwicklungsleiters, der als Hauptwissenstr?ger fungierte. Dies schützte zwar vor den meisten Fehlern, verlangsamte jedoch den ?nderungsprozess und war v?llig nicht skalierbar.

Nach und nach begannen die Menschen mit der Umstellung auf Microservices.
Die Anzahl der Datenbanken wuchs erheblich, die Anzahl der Tabellen innerhalb jeder Datenbank nahm jedoch umgekehrt ab. Nun begann jedes Team, die Struktur seiner eigenen Datenbank unabh?ngig zu verwalten. Die zentralisierte Quelle für Fachwissen verschwand und Datenbankdesignfehler begannen sich zu vervielfachen und von einem Dienst zum anderen zu übertragen.

Die Testpyramide und ihre Formen

Die meisten von Ihnen haben wahrscheinlich schon von der Testpyramide geh?rt. Für Monolithen hat es eine recht charakteristische Form mit einer breiten Basis an Unit-Tests. Für weitere Details empfehle ich den Artikel von Martin Fowler.

Microservices haben nicht nur den Entwicklungsansatz, sondern auch das Erscheinungsbild der Testpyramide ver?ndert. Diese Verschiebung wurde gr??tenteils durch den Aufstieg der Containerisierungstechnologien (Docker, Testcontainer) vorangetrieben. Heute ist die Testpyramide überhaupt keine Pyramide mehr. Es kann eine sehr bizarre Form haben. Die bekanntesten Beispiele sind die Honeycomb und die Testing Trophy.

Der moderne Trend geht dahin, so wenige Unit-Tests wie m?glich zu schreiben, sich auf Implementierungsdetails zu konzentrieren und Komponenten- und Integrationstests zu priorisieren, die die tats?chliche Funktionalit?t des Dienstes validieren.

Mein pers?nlicher Favorit ist die Testing Trophy. Die Grundlage bildet die statische Codeanalyse, die darauf ausgelegt ist, h?ufige Fehler zu verhindern.

Die Bedeutung der statischen Codeanalyse

Statische Analyse für Java- und Kotlin-Code ist mittlerweile g?ngige Praxis. Bei Kotlin-Diensten wird in der Regel das Tool der Wahl ermittelt. Für Java-Anwendungen ist die Palette der verfügbaren Tools (oft als Linters bezeichnet) breiter. Zu den Haupttools geh?ren Checkstyle, PMD, SpotBugs und Error Prone. Mehr darüber k?nnen Sie in meinem vorherigen Artikel lesen.

Bemerkenswert ist, dass sowohl detekt als auch Checkstyle auch die Codeformatierung übernehmen und effektiv als Formatierer fungieren.

Statische Analyse für Datenbankmigrationen

Moderne Microservices umfassen h?ufig Datenbankmigrationen zum Erstellen und Aktualisieren der Datenbankstruktur neben dem Anwendungscode.

Im Java-?kosystem sind die wichtigsten Tools zur Verwaltung von Migrationen Liquibase und Flyway. ?nderungen an der Datenbankstruktur müssen bei Migrationen immer dokumentiert werden. Auch wenn w?hrend eines Vorfalls in der Produktion manuell ?nderungen vorgenommen werden, muss sp?ter eine Migration erstellt werden, um diese ?nderungen auf alle Umgebungen anzuwenden.

Migrationen in einfachem SQL zu schreiben ist die beste Vorgehensweise, da es maximale Flexibilit?t bietet und im Vergleich zum Erlernen der XML-Dialekte von Tools wie Liquibase Zeit spart. Ich habe dies in meinem Artikel ?Sechs Tipps für die Verwendung von PostgreSQL in Funktionstests“ angesprochen.

überprüfen des SQL-Migrationscodes

Um den SQL-Code bei Migrationen zu überprüfen, empfehle ich die Verwendung von SQLFluff, das im Wesentlichen ein Checkstyle-?quivalent für SQL ist. Dieser Linter unterstützt mehrere Datenbanken und Dialekte (einschlie?lich PostgreSQL) und kann in Ihre CI-Pipeline integriert werden. Es bietet über 60 anpassbare Regeln, mit denen Sie Tabellen- und Spaltenaliase, Gro?- und Kleinschreibung von SQL-Befehlen, Einrückungen, Spaltenreihenfolge in Abfragen und vieles mehr verwalten k?nnen.

Vergleichen Sie die Abfrage mit und ohne Formatierung:

-- 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;

Gut formatierter SQL-Code ist viel einfacher zu lesen und zu verstehen. Am wichtigsten ist, dass Codeüberprüfungen nicht mehr durch Diskussionen über Formatierungseinstellungen behindert werden. SQLFluff erzwingt einen konsistenten Stil und spart so Zeit.

SQLFluff in Aktion

So sieht es in einem echten Pull-Request aus:

Hier hat SQLFluff ein Problem mit der Formatierung des Rückgabewerts in der select-Anweisung gefunden: Wenn nur eine Spalte zurückgegeben wird, setzen wir sie nicht auf eine separate Reihe. Der zweite Punkt ist die falsche Spaltenreihenfolge in den Auswahlergebnissen: Zuerst geben wir einfache Spalten zurück und erst dann die Berechnungsergebnisse. Und der dritte ist der falsche Fall für and in der join-Anweisung: Ich bevorzuge es, alle Abfragen in Kleinbuchstaben zu schreiben.

Weitere Beispiele für den Einsatz von SQLFluff finden Sie in meinen Open-Source-Projekten: eins, zwei.

Verwendung von Metadaten zur Analyse der Datenbankstruktur

Auch die Struktur der Datenbank selbst kann überprüft werden. Allerdings ist die Arbeit mit Migrationen ?u?erst umst?ndlich: Es kann viele davon geben; Eine neue Migration behebt m?glicherweise Fehler in einer früheren Migration usw. In der Regel interessiert uns mehr die endgültige Struktur der Datenbank als ihr Zwischenzustand.

Nutzung des Informationsschemas

PostgreSQL speichert (wie viele andere relationale Datenbanken) Metadaten über alle Objekte und Beziehungen zwischen ihnen und stellt sie extern in Form von information_schema bereit. Wir k?nnen Abfragen an information_schema verwenden, um etwaige Abweichungen, Probleme oder h?ufige Fehler zu identifizieren (genau das macht SchemaCrawler).

Da wir nur mit PostgreSQL arbeiten, k?nnen wir anstelle von information_schema Systemkataloge (das pg_catalog-Schema) verwenden, die viel mehr Informationen über die interne Struktur einer bestimmten Datenbank liefern.

Kumulatives Statistiksystem

Zus?tzlich zu den Metadaten sammelt PostgreSQL Informationen über den Betrieb jeder Datenbank: welche Abfragen ausgeführt werden, wie sie ausgeführt werden, welche Zugriffsmethoden verwendet werden usw. Für die Erfassung ist das Cumulative Statistics System verantwortlich diese Daten.

Indem wir diese Statistiken über Systemansichten abfragen und sie mit Daten aus den Systemkatalogen kombinieren, k?nnen wir:

• Identifizieren Sie nicht verwendete Indizes;
• Erkennen Sie Tabellen, denen eine ausreichende Indizierung fehlt.

Statistiken k?nnen manuell zurückgesetzt werden. Datum und Uhrzeit des letzten Zurücksetzens werden im System aufgezeichnet. Es ist wichtig, dies zu berücksichtigen, um zu verstehen, ob den Statistiken vertraut werden kann oder nicht. Wenn Sie beispielsweise über eine Gesch?ftslogik verfügen, die einmal im Monat/Quartal/Halbjahr ausgeführt wird, müssen die Statistiken mindestens für den oben genannten Zeitraum erfasst werden.

Wenn ein Datenbankcluster verwendet wird, werden die Statistiken unabh?ngig auf jedem Host erfasst und nicht innerhalb des Clusters repliziert.

pg-index-health und seine Struktur

Die Idee, die Datenbankstruktur anhand von Metadaten innerhalb der Datenbank selbst zu analysieren, wie oben beschrieben, wurde von mir in Form eines Tools namens pg-index-health umgesetzt.

Meine L?sung umfasst die folgenden Komponenten:

• Eine Reihe von Prüfungen in Form von SQL-Abfragen, die in einem separaten Repository abgelegt werden (derzeit bestehend aus 25 Prüfungen). Die Abfragen sind von der Java-Codebasis entkoppelt und k?nnen in Projekten, die in anderen Programmiersprachen geschrieben wurden, wiederverwendet werden.
• Ein Dom?nenmodell – ein minimaler Satz von Klassen, die die Ergebnisse der Prüfungen als Objekte darstellen.
• Die HighAvailabilityPgConnection-Abstraktion zum Herstellen einer Verbindung zu einem Datenbankcluster, der aus mehreren Hosts besteht.
• Dienstprogramme zum Ausführen von SQL-Abfragen und zum Serialisieren von Ergebnissen in Dom?nenmodellobjekte.
• Ein Spring Boot-Starter für die bequeme und schnelle Integration von Prüfungen in Unit-/Komponenten-/Integrationstests.
• Ein Migrationsgenerator, der korrigierende SQL-Migrationen für identifizierte Probleme erstellen kann.

Arten von Schecks

Alle Prüfungen (auch Diagnose genannt) sind in zwei Gruppen unterteilt:

• Laufzeitprüfungen (Statistiken erforderlich).
• Statische Prüfungen (keine Statistik erforderlich).

Laufzeitprüfungen

Laufzeitprüfungen sind nur dann sinnvoll, wenn sie auf einer Live-Datenbankinstanz in der Produktion ausgeführt werden. Diese Prüfungen erfordern akkumulierte Statistiken und aggregieren diese Daten von allen Hosts im Cluster.

Betrachten wir einen Datenbankcluster, der aus drei Hosts besteht: prim?rem, sekund?rem und asynchronem Replikat. Einige Dienste verwenden Cluster mit ?hnlichen Topologien und führen umfangreiche Leseabfragen nur auf dem asynchronen Replikat aus, um die Last auszugleichen. Solche Abfragen werden normalerweise nicht auf dem prim?ren Host ausgeführt, da sie zus?tzliche Last verursachen und sich negativ auf die Latenz anderer Abfragen auswirken.

Wie bereits erw?hnt, werden in PostgreSQL Statistiken separat auf jedem Host erfasst und nicht innerhalb des Clusters repliziert. Daher kann es leicht zu einer Situation kommen, in der bestimmte Indizes nur auf dem asynchronen Replikat verwendet werden und erforderlich sind. Um zuverl?ssig feststellen zu k?nnen, ob ein Index ben?tigt wird oder nicht, ist es notwendig, die Prüfung auf jedem Host im Cluster durchzuführen und die Ergebnisse zu aggregieren.

Statische Kontrollen

Statische Prüfungen erfordern keine akkumulierten Statistiken und k?nnen sofort nach der Anwendung von Migrationen auf dem prim?ren Host ausgeführt werden. Natürlich k?nnen sie auch auf einer Produktionsdatenbank verwendet werden, um Daten in Echtzeit zu erhalten. Die meisten Prüfungen sind jedoch statisch und besonders nützlich in Tests, da sie dabei helfen, h?ufige Fehler w?hrend der Entwicklungsphase zu erkennen und zu verhindern.

So verwenden Sie pg-index-health

Der Hauptanwendungsfall für pg-index-health ist das Hinzufügen von Tests zur überprüfung der Datenbankstruktur in Ihrer Testpipeline.

Für Spring Boot-Anwendungen müssen Sie den Starter zu Ihren Testabh?ngigkeiten hinzufügen:

-- 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;

Dann fügen Sie einen Standardtest hinzu:

-- 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;

In diesem Test werden alle verfügbaren Checks als Liste eingefügt. Dann werden nur statische Prüfungen gefiltert und auf der realen Datenbank ausgeführt, die in einem Container mit angewendeten Migrationen bereitgestellt wird.

Idealerweise sollte jede Prüfung eine leere Liste zurückgeben. Sollten beim Hinzufügen der n?chsten Migration Abweichungen auftreten, schl?gt der Test fehl. Der Entwickler wird gezwungen sein, darauf zu achten und das Problem auf irgendeine Weise zu l?sen: entweder es in seiner Migration beheben oder es explizit ignorieren.

Falsch positive Ergebnisse und das Hinzufügen von Ausschlüssen

Es ist wichtig zu verstehen, dass pg-index-health, wie jeder andere statische Analysator, falsch positive Ergebnisse erzeugen kann. Darüber hinaus sind einige Prüfungen m?glicherweise für Ihr Projekt nicht relevant. Beispielsweise gilt es als gute Praxis, die Datenbankstruktur zu dokumentieren. PostgreSQL erm?glicht das Hinzufügen von Kommentaren zu fast allen Datenbankobjekten. Bei einer Migration k?nnte dies wie folgt aussehen:

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

Innerhalb Ihres Teams sind Sie m?glicherweise damit einverstanden, dies nicht zu tun. In diesem Fall werden die Ergebnisse entsprechender Prüfungen (TABLES_WITHOUT_DESCRIPTION, COLUMNS_WITHOUT_DESCRIPTION, FUNCTIONS_WITHOUT_DESCRIPTION) für Sie irrelevant.

Sie k?nnen diese Prüfungen entweder komplett ausschlie?en:

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());
    }
}

Oder ignorieren Sie einfach ihre Ergebnisse:

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';

Bei der Einführung von pg-index-health kann es h?ufig zu Situationen kommen, in denen die Datenbankstruktur bereits einige Abweichungen aufweist, Sie diese aber nicht sofort beheben m?chten. Gleichzeitig ist die Prüfung relevant und ihre Deaktivierung ist keine Option. In solchen F?llen ist es am besten, alle Abweichungen im Code zu beheben:

@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());
}

Jetzt m?chte ich mich detaillierter auf die am h?ufigsten auftretenden Probleme konzentrieren.

Tabellen ohne Prim?rschlüssel

Aufgrund der Besonderheiten des MVCC-Mechanismus in PostgreSQL k?nnen Situationen wie ?Bloat“ auftreten, bei denen die Gr??e Ihrer Tabelle (oder Ihres Index) aufgrund einer gro?en Anzahl toter Tupel schnell zunimmt. Dies kann beispielsweise durch lang laufende Transaktionen oder eine einmalige Aktualisierung einer gro?en Anzahl von Zeilen passieren.

Die Speicherbereinigung innerhalb der Datenbank erfolgt durch den Autovacuum-Prozess, der jedoch den belegten physischen Speicherplatz nicht freigibt. Die einzige M?glichkeit, die physische Gr??e einer Tabelle effektiv zu reduzieren, ist die Verwendung des VACUUM FULL-Befehls, der eine exklusive Sperre für die Dauer des Vorgangs erfordert. Bei gro?en Tischen kann dies mehrere Stunden dauern, sodass ein vollst?ndiges Staubsaugen für die meisten modernen Dienste unpraktisch ist.

Um das Problem des Aufbl?hens von Tabellen ohne Ausfallzeiten zu beheben, werden h?ufig Erweiterungen von Drittanbietern wie pg_repack verwendet. Eine der zwingenden Anforderungen von pg_repack ist das Vorhandensein eines Prim?rschlüssels oder einer anderen Eindeutigkeitsbeschr?nkung für die Zieltabelle. Die Diagnose TABLES_WITHOUT_PRIMARY_KEY hilft bei der Erkennung von Tabellen ohne Prim?rschlüssel und verhindert zukünftige Wartungsprobleme.

Unten finden Sie ein Beispiel für eine Tabelle ohne Prim?rschlüssel. Wenn bloat in dieser Tabelle auftritt, kann pg_repack es nicht verarbeiten und gibt einen Fehler zurück.

-- 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;

Doppelte Indizes

Unsere Datenbanken laufen auf Hosts mit begrenzten Ressourcen, und Speicherplatz ist einer davon. Bei der Verwendung von Database-as-a-Service-L?sungen gibt es h?ufig eine physische Begrenzung der maximalen Datenbankgr??e, die nicht ge?ndert werden kann.

Jeder Index in einer Tabelle ist eine separate Einheit auf der Festplatte. Es belegt Platz und ben?tigt Ressourcen für die Wartung, was das Einfügen und Aktualisieren von Daten verlangsamt. Wir erstellen Indizes, um die Suche zu beschleunigen oder die Eindeutigkeit bestimmter Werte sicherzustellen. Die unsachgem??e Verwendung von Indizes kann jedoch dazu führen, dass ihre Gesamtgr??e die Gr??e der nützlichen Daten in der Tabelle selbst überschreitet. Daher sollte die Anzahl der Indizes in einer Tabelle minimal, aber für ihre Funktionalit?t ausreichend sein.

Ich bin auf zahlreiche F?lle gesto?en, in denen bei Migrationen unn?tige Indizes erstellt wurden. Beispielsweise wird automatisch ein Index für einen Prim?rschlüssel erstellt. Obwohl es technisch m?glich ist, die Spalte id manuell zu indizieren, ist dies v?llig sinnlos.

-- 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;

Eine ?hnliche Situation ergibt sich bei einzigartigen Einschr?nkungen. Wenn Sie eine Spalte (oder eine Gruppe von Spalten) mit dem Schlüsselwort unique markieren, erstellt PostgreSQL automatisch einen eindeutigen Index für diese Spalte (oder Gruppe von Spalten). . Das manuelle Erstellen zus?tzlicher Indizes ist nicht erforderlich. Wenn dies geschieht, führt dies zu doppelten Indizes. Solche redundanten Indizes k?nnen und sollten entfernt werden, und die Diagnose DUPLICATED_INDEXES kann dabei helfen, sie zu identifizieren.

-- 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;

überlappende (sich überschneidende) Indizes

Die meisten Indizes werden für eine einzelne Spalte erstellt. Wenn die Abfrageoptimierung beginnt, werden m?glicherweise komplexere Indizes hinzugefügt, die mehrere Spalten umfassen. Dies führt zu Szenarien, in denen Indizes für Spalten wie A, A B und A B C erstellt werden. Die ersten beiden Indizes in dieser Reihe k?nnen oft verworfen werden, da sie Pr?fixe des dritten sind (ich empfehle, sich dieses Video anzusehen). . Durch das Entfernen dieser redundanten Indizes kann erheblich Speicherplatz gespart werden, und die INTERSECTED_INDEXES-Diagnose ist darauf ausgelegt, solche F?lle zu erkennen.

-- 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;

Fremdschlüssel ohne Indizes

PostgreSQL erm?glicht die Erstellung von Fremdschlüsseleinschr?nkungen ohne Angabe eines unterstützenden Indexes, was bedeutet, dass eine Tabelle, die auf eine andere verweist, keinen Index erfordert und auch nicht automatisch erstellt wird. In einigen F?llen stellt dies m?glicherweise kein Problem dar und manifestiert sich m?glicherweise überhaupt nicht. Allerdings kann es manchmal zu Zwischenf?llen in der Produktion kommen.

Sehen wir uns ein kleines Beispiel an (ich verwende PostgreSQL 16.6):

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

Wir haben eine Tabelle Bestellungen und eine Tabelle Bestellartikel. Sie sind über einen Fremdschlüssel in der Spalte order_id verknüpft. Der Fremdschlüssel sollte immer entweder auf den Prim?rschlüssel oder eine eindeutige Einschr?nkung verweisen, die in unserem Fall erfüllt ist.

Lassen Sie uns die Tabellen mit Daten füllen und Statistiken sammeln. Wir werden 100.000 Bestellungen hinzufügen, wobei die H?lfte zwei Artikel und die anderen einen haben.

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());
    }
}

Wenn wir versuchen, Artikel für eine Bestellung mit der ID=100 abzurufen, sollten wir erfolgreich zwei Zeilen zurückgeben. Da es einen Index für die Spalte id in der Auftragstabelle gibt, scheint es, dass diese Abfrage schnell sein sollte.

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';

Wenn wir jedoch versuchen, diese Abfrage zu profilieren, werden wir im Ausführungsplan ein sequentielles Scannen der Tabelle sehen. Wir sollten uns auch Sorgen über die gro?e Anzahl von Seiten machen, die gelesen werden müssen (Puffer-Parameter).

@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();
            }
        });
}

Wenn wir einen Index für eine Spalte mit einem Fremdschlüssel erstellen, normalisiert sich die Situation wieder:

@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();
            }
        });
}

Der sequentielle Scan verschwindet aus dem Abfrageplan und die Anzahl der gelesenen Seiten wird erheblich reduziert:

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
);

Mit der Diagnose FOREIGN_KEYS_WITHOUT_INDEX k?nnen Sie solche F?lle frühzeitig w?hrend der Entwicklung erkennen und so Leistungsprobleme verhindern.

Nullwert in Indizes

Standardm??ig schlie?t PostgreSQL Nullwerte in Btree-Indizes ein, sie werden dort jedoch normalerweise nicht ben?tigt. Alle Nullwerte sind eindeutig und Sie k?nnen nicht einfach einen Datensatz abrufen, bei dem der Spaltenwert null ist. In den meisten F?llen ist es besser, Nullen aus dem Index auszuschlie?en, indem Teilindizes für nullable-Spalten erstellt werden, z.?B. wo ist nicht null. Die Diagnose INDEXES_WITH_NULL_VALUES hilft dabei, solche F?lle zu erkennen.

-- 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;

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

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());
    }
}

-- 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;

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

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());
    }
}

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';

@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());
}