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.
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 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.
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.
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.
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.
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.
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.
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.
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:
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.
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:
Alle Prüfungen (auch Diagnose genannt) sind in zwei Gruppen unterteilt:
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 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.
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.
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.
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;
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;
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;
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.
Es ist wichtig, sich an das Problem der falsch positiven Ergebnisse zu erinnern: Nicht alle Fremdschlüsselspalten müssen indiziert werden. Versuchen Sie, die ungefähre Tischgröße in der Produktion abzuschätzen; Überprüfen Sie Ihren Code zum Filtern, Suchen oder Verknüpfen in der Fremdschlüsselspalte. Wenn Sie zu 100 % sicher sind, dass Sie den Index nicht benötigen, können Sie ihn einfach zu den Ausschlüssen hinzufügen. Wenn Sie sich nicht sicher sind, ist es besser, den Index zu erstellen (er kann später jederzeit entfernt werden). Ich bin oft auf Vorfälle gestoßen, bei denen die Datenbank aufgrund des Fehlens eines Index für einen Fremdschlüssel „verlangsamt“ wurde, aber ich habe noch keine Vorfälle gesehen, bei denen die Datenbank aufgrund des Vorhandenseins solcher Indizes „verlangsamt“ wurde . Daher bin ich mit der Aussage im Percona-Blogartikel nicht einverstanden, dass Fremdschlüsselindizes überhaupt nicht von Anfang an erstellt werden sollten. Dies ist ein DBA-Ansatz. Haben Sie einen engagierten DBA in Ihrem Team?Soll ich Indizes erstellen oder nicht?
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.
Betrachten wir ein Beispiel mit Bestellungen und order_items. Die order_item-Tabelle enthält eine nullable-Spalte warehouse_id, die die Lager-ID darstellt.
-- 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;
Angenommen, wir haben mehrere Lager. Nachdem die Bestellung bezahlt ist, beginnen wir mit der Montage. Wir werden den Status einiger Bestellungen aktualisieren und sie als bezahlt markieren.
-- 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;
Einzelne Artikel in einer Bestellung können nach einem internen Algorithmus aus verschiedenen Lagern versendet werden, der Logistik, Lagerbestand, Lagerauslastung usw. berücksichtigt. Nach der Zuweisung des Lagers und der Aktualisierung des Lagerbestands aktualisieren wir die Lager_IDFeld für jeden Artikel in der Bestellung (das ursprünglich null war).
dependencies {
testImplementation("io.github.mfvanek:pg-index-health-test-starter:0.14.4")
}
Wir müssen anhand einer bestimmten Lager-ID suchen, um zu wissen, welche Artikel fertiggestellt und versendet werden müssen. Wir nehmen nur bezahlte Bestellungen für einen bestimmten Zeitraum entgegen.
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());
}
}
Die erste Lösung wäre wahrscheinlich ein regulärer Index für die Spalte 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;
Wenn wir einen solchen Index erstellen, kann er problemlos bei der Suche nach Artikeln für ein bestimmtes Lager verwendet werden. Es scheint, dass dieser Index das effiziente Auffinden aller Artikel ermöglichen sollte, denen das Lager noch nicht zugewiesen ist, indem Datensätze mit der Bedingung „Warehouse_ID ist null“ gefiltert werden.
-- 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;
Wenn wir uns jedoch den Abfrageausführungsplan ansehen, werden wir dort sequentiellen Zugriff sehen – der Index wird nicht verwendet.
dependencies {
testImplementation("io.github.mfvanek:pg-index-health-test-starter:0.14.4")
}
Dies hängt natürlich mit der spezifischen Verteilung der Daten in der Testdatenbank zusammen. Die Spalte warehouse_id weist eine niedrige Kardinalität auf, was bedeutet, dass die Anzahl der eindeutigen Werte darin gering ist. Ein Index für diese Spalte weist eine geringe Selektivität auf. Die Indexselektivität bezieht sich auf das Verhältnis der Anzahl eindeutiger indizierter Werte (d. h. Kardinalität) zur Gesamtzahl der Zeilen in der Tabelle eindeutig / count(). Beispielsweise hat ein eindeutiger Index eine Selektivität von eins.
Wir können die Selektivität des Index erhöhen, indem wir Nullwerte entfernen und einen Teilindex für die Spalte warehouse_id erstellen.
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());
}
}
Diesen Index sehen wir sofort im Abfrageplan:
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 die Größen der Indizes vergleichen, werden wir einen deutlichen Unterschied feststellen. Der Teilindex ist viel kleiner und wird seltener aktualisiert. Mit diesem Index sparen wir Speicherplatz und verbessern die Leistung.
Abfrage, um die Größe der Indizes zu erhalten
@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 |
Das sind bei weitem nicht alle Probleme, die pg-index-health erkennen kann. Die vollständige Liste der Diagnosen ist in der README-Datei des Projekts auf GitHub verfügbar und wird regelmäßig erweitert.
Die Integration von pg-index-health in eine Spring Boot-Anwendung ist ganz einfach. Der Aufwand für die Durchführung der Prüfungen ist minimal. Dadurch erhalten Sie Schutz vor häufigen Fehlern und Problemen. Ich ermutige Sie, die Umsetzung auszuprobieren!
In naher Zukunft plane ich, bei allen Prüfungen die volle Unterstützung für partitionierte Tabellen hinzuzufügen. Derzeit ist dies nur bei 11 von 25 Kontrollen implementiert. Ich möchte auch die Anzahl der Prüfungen erweitern: Es gibt bereits Tickets für die Implementierung von mindestens 5 neuen Prüfungen. Darüber hinaus plane ich im Jahr 2025 die Umstellung auf Java 17 und Spring Boot 3.
Das obige ist der detaillierte Inhalt vonpg-index-health – ein statisches Analysetool für Ihre PostgreSQL-Datenbank. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!
![[Web-Frontend] Node.js-Schnellstart](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
