pg-index-health – PostgreSQL データベース用の静的分析ツール

こんにちは！

2019 年以来、私はデータベース構造を分析し、潜在的な問題を特定する pg-index-health というオープンソース ツールを開発してきました。以前の記事の 1 つで、このツールがどのように誕生したかについてのストーリーを共有しました。

長年にわたって、pg-index-health は進化し、改善されてきました。 2024 年、私は数人の貢献者の支援を受けて、残っている「成長痛」のほとんどに対処し、プロジェクトを大規模な拡張の準備ができる状態にまで持ち込むことができました。

マイクロサービスの台頭によるデータベースの成長

私は 2015 年から PostgreSQL に取り組んでいますが、この魅力的な旅はヤロスラヴリに拠点を置く企業 Tensor から始まりました。

2015 年当時はまだ、大規模なデータベースと多数のテーブルを備えたモノリスの時代でした。通常、このようなデータベースの構造を変更するには、主要な知識保持者であるアーキテクトまたは開発リーダーからの強制的な承認が必要でした。これにより、ほとんどのエラーは防止されましたが、変更を加えるプロセスが遅くなり、まったく拡張性がありませんでした。

人々は徐々にマイクロサービスに移行し始めました。
データベースの数は大幅に増加しましたが、逆に、各データベース内のテーブルの数は減少しました。現在、各チームは独自のデータベースの構造を独立して管理し始めています。一元化された専門知識のソースがなくなり、データベース設計のエラーが増加し、サービス間で伝播し始めました。

試練のピラミッドとその形状

ほとんどの人は、テスト ピラミッドについて聞いたことがあるでしょう。モノリスの場合、単体テストの幅広いベースを備えたかなり特徴的な形状をしています。詳細については、Martin Fowler の記事をお勧めします。

マイクロサービスは、開発へのアプローチだけでなく、テストピラミッドの外観も変えました。この変化は主に、コンテナ化テクノロジー (Docker、Testcontainers) の台頭によって促進されました。今日、テストピラミッドはもはやピラミッドではありません。非常に奇妙な形をしている場合があります。最もよく知られている例は、ハニカムとテスト トロフィーです。

最近の傾向は、実装の詳細に重点を置いて単体テストをできるだけ少なく記述し、サービスによって提供される実際の機能を検証するコンポーネント テストと統合テストを優先することです。

私の個人的なお気に入りは テスト トロフィーです。そのベースには静的コード分析があり、一般的なエラーを防ぐように設計されています。

静的コード分析の重要性

Java および Kotlin コードの静的分析は現在では一般的な手法です。 Kotlin サービスの場合、通常、選択されるツールは detekt です。 Java アプリケーションの場合、利用可能なツール (リンターと呼ばれることが多い) の範囲がさらに広がります。主なツールには、Checkstyle、PMD、SpotBugs、および Error Prone が含まれます。詳細については、以前の記事をご覧ください。

特に、detekt と Checkstyle はどちらもコードのフォーマットも処理し、実質的にフォーマッタとして機能します。

データベース移行のための静的分析

最新のマイクロサービスには、アプリケーション コードとともにデータベース構造を作成および更新するためのデータベース移行が含まれることがよくあります。

Java エコシステムでは、移行を管理するための主なツールは Liquibase と Flyway です。データベース構造への変更は常に移行時に文書化する必要があります。実稼働環境でのインシデント中に手動で変更が加えられた場合でも、後で移行を作成して、それらの変更をすべての環境に適用する必要があります。

プレーン SQL で移行を記述することは、Liquibase などのツールの XML 言語を学習するよりも最大限の柔軟性が得られ、時間を節約できるため、ベスト プラクティスです。これについては、私の記事「機能テストで PostgreSQL を使用するための 6 つのヒント」で触れました。

SQL移行コードの検証

移行で SQL コードを検証するには、SQLFluff を使用することをお勧めします。これは本質的に SQL の Checkstyle と同等です。このリンターは複数のデータベースと方言 (PostgreSQL を含む) をサポートしており、CI パイプラインに統合できます。 60 を超えるカスタマイズ可能なルールが用意されており、テーブルと列のエイリアス、SQL コマンドの大文字と小文字、インデント、クエリ内の列の順序などを管理できます。

書式設定ありとなしのクエリを比較します:

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

適切にフォーマットされた SQL コードは、読みやすく、理解しやすくなります。最も重要なことは、コード レビューがフォーマット設定に関する議論で行き詰まることがなくなります。 SQLFluff は一貫したスタイルを強制し、時間を節約します。

SQLFluff の動作

実際のプルリクエストでは次のようになります:

ここで SQLFluff は、select ステートメントの戻り値のフォーマットに問題を見つけました。1 つの列のみが返される場合、それを別の行。 2 番目の点は、選択結果の列の順序が間違っていることです。最初に単純な列を返し、次に計算結果だけを返します。 3 番目は、join ステートメント内の と の間違ったケースです。私はすべてのクエリを小文字で書くことを好みます。

SQLFluff の使用例については、私のオープンソース プロジェクトをチェックしてください: 1 つ、2 つ。

メタデータを使用したデータベース構造の分析

データベース自体の構造も確認できます。ただし、移行の作業は非常に不便です。移行は多数存在する可能性があります。新しい移行により、以前の移行でのエラーが修正される場合があります。原則として、私たちは中間状態よりもデータベースの最終構造に興味を持ちます。

情報スキーマの活用

PostgreSQL (他の多くのリレーショナル データベースと同様) は、すべてのオブジェクトとオブジェクト間の関係に関するメタデータを保存し、それを information_schema の形式で外部に提供します。 information_schema へのクエリを使用して、逸脱、問題、または一般的なエラーを特定できます (これがまさに SchemaCrawler の機能です)。

PostgreSQL のみを使用しているため、information_schema の代わりに、特定のデータベースの内部構造に関するより多くの情報を提供するシステム カタログ (pg_catalog スキーマ) を使用できます。

累積統計システム

メタデータに加えて、PostgreSQL は、どのようなクエリが実行されるか、どのように実行されるか、どのようなアクセス方法が使用されるかなど、各データベースの操作に関する情報を収集します。累積統計システムは、その収集を担当します。このデータ。

システム ビューを通じてこれらの統計をクエリし、システム カタログのデータと組み合わせることで、次のことが可能になります。

• 未使用のインデックスを特定します;
• 適切なインデックスが作成されていないテーブルを検出します。

統計は手動でリセットできます。最後にリセットした日時がシステムに記録されます。統計が信頼できるかどうかを理解するには、これを考慮することが重要です。たとえば、月/四半期/半年に 1 回実行されるビジネス ロジックがある場合、少なくとも上記の間隔で統計を収集する必要があります。

データベース クラスターが使用されている場合、統計は各ホストで個別に収集され、クラスター内で複製されません。

pg-index-health とその構造

上記で説明した、データベース自体内のメタデータに基づいてデータベース構造を分析するというアイデアは、pg-index-health というツールの形で私によって実装されました。

私のソリューションには次のコンポーネントが含まれています:

• SQL クエリの形式のチェックのセット。別のリポジトリに配置されます (現在 25 のチェックで構成されています)。クエリは Java コードベースから分離されており、他のプログラミング言語で書かれたプロジェクトで再利用できます。
• ドメイン モデル — チェックの結果をオブジェクトとして表すクラスの最小限のセット。
• 複数のホストで構成されるデータベース クラスターに接続するための HighAvailabilityPgConnection 抽象化。
• SQL クエリを実行し、結果をドメイン モデル オブジェクトにシリアル化するためのユーティリティ。
• ユニット/コンポーネント/統合テストにチェックを便利かつ迅速に統合するための Spring Boot スターター。
• 特定された問題に対する修正 SQL 移行を作成できる移行ジェネレーター。

小切手の種類

すべてのチェック (診断とも呼ばれます) は 2 つのグループに分けられます:

• 実行時チェック (統計が必要)。
• 静的チェック (統計は必要ありません)。

実行時チェック

実行時チェックは、運用環境のライブデータベースインスタンスで実行される場合にのみ意味を持ちます。これらのチェックには蓄積された統計が必要であり、クラスター内のすべてのホストからこのデータを集約します。

プライマリ、セカンダリ、非同期レプリカの 3 つのホストで構成されるデータベース クラスターを考えてみましょう。一部のサービスは、同様のトポロジのクラスターを使用し、負荷を分散するために非同期レプリカ上でのみ大量の読み取りクエリを実行します。このようなクエリは、追加の負荷が発生し、他のクエリのレイテンシに悪影響を与えるため、通常、プライマリ ホストでは実行されません。

前述したように、PostgreSQL では、統計は各ホストで個別に収集され、クラスター内で複製されません。したがって、特定のインデックスが使用され、非同期レプリカでのみ必要となる状況が簡単に発生する可能性があります。インデックスが必要かどうかを確実に判断するには、クラスター内の各ホストでチェックを実行し、結果を集計する必要があります。

静的チェック

静的チェック は蓄積された統計を必要とせず、移行の適用直後にプライマリ ホストで実行できます。もちろん、実稼働データベースでリアルタイムにデータを取得するために使用することもできます。ただし、ほとんどのチェックは静的であり、開発段階で一般的なエラーを検出して防止するのに役立つため、テストで特に役立ちます。

pg-index-healthの使用方法

pg-index-health の主な使用例は、テスト パイプラインでデータベース構造を検証するテストを追加することです。

Spring Boot アプリケーションの場合は、スターターをテストの依存関係に追加する必要があります。

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

このテストでは、利用可能なすべてのチェックがリストとして挿入されます。次に、静的チェックのみがフィルタリングされ、移行が適用されたコンテナにデプロイされた実際のデータベースで実行されます。

理想的には、各チェックは空のリストを返す必要があります。次の移行を追加するときに逸脱がある場合、テストは失敗します。開発者はこれに注意を払い、移行時に修正するか、明示的に無視するかのいずれかの方法で問題を解決する必要があります。

誤検知と除外の追加

pg-index-health は、他の静的アナライザーと同様に、誤検知を生成する可能性があることを理解することが重要です。さらに、一部のチェックはプロジェクトに関連しない場合があります。たとえば、データベース構造を文書化することが推奨されます。 PostgreSQL では、ほぼすべてのデータベース オブジェクトにコメントを追加できます。移行では、これは次のようになります:

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

チーム内では、これを行わないことに同意するかもしれません。その場合、対応するチェックの結果 (TABLES_WITHOUT_DESCRIPTION、COLUMNS_WITHOUT_DESCRIPTION、FUNCTIONS_WITHOUT_DESCRIPTION) は無関係になります。

これらのチェックを完全に除外することもできます:

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

または単に結果を無視してください:

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

pg-index-health を導入する場合、データベース構造にすでにいくつかの 逸脱 があるにもかかわらず、すぐに対処したくないという状況に遭遇することがよくあります。同時に、このチェックは関連性があるため、無効にすることはできません。このような場合、コード内のすべての逸脱を修正するのが最善です:

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

ここでは、最も頻繁に発生する問題について詳しく説明したいと思います。

主キーのないテーブル

PostgreSQL の MVCC メカニズムの仕様により、多数の無効なタプルによりテーブル (またはインデックス) のサイズが急速に増大する肥大化のような状況が発生する可能性があります。これは、たとえば、長時間実行されるトランザクションや、多数の行の 1 回限りの更新の結果として発生する可能性があります。

データベース内のガベージ コレクションは自動バキューム プロセスによって処理されますが、占有されている物理ディスク領域は解放されません。テーブルの物理サイズを効果的に削減する唯一の方法は、VACUUM FULL コマンドを使用することです。これには、操作中に排他ロックが必要です。大きなテーブルの場合、これには数時間かかる可能性があるため、最新のサービスのほとんどでは完全なバキュームは現実的ではありません。

テーブル 肥大化 の問題をダウンタイムなしで解決するには、pg_repack などのサードパーティの拡張機能がよく使用されます。 pg_repack の必須要件の 1 つは、ターゲット テーブルに主キーまたはその他の一意性制約が存在することです。 TABLES_WITHOUT_PRIMARY_KEY 診断は、主キーのないテーブルを検出し、将来のメンテナンスの問題を防ぐのに役立ちます。

以下は主キーのないテーブルの例です。このテーブルで 膨張 が発生すると、pg_repack はそれを処理できず、エラーを返します。

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

重複したインデックス

当社のデータベースはリソースが限られたホスト上で動作しており、ディスク容量もその 1 つです。 Database-as-a-Service ソリューションを使用する場合、多くの場合、データベースの最大サイズには変更できない物理的な制限があります。

テーブル内の各インデックスは、ディスク上の別個のエンティティです。スペースを占有し、メンテナンスにリソースが必要となるため、データの挿入と更新が遅くなります。検索を高速化するため、または特定の値の一意性を確保するためにインデックスを作成します。ただし、インデックスを不適切に使用すると、それらの合計サイズがテーブル自体の有用なデータのサイズを超える状況が発生する可能性があります。したがって、テーブル内のインデックスの数は最小限でありながら、その機能にとって十分である必要があります。

移行中に不要なインデックスが作成されるケースに何度も遭遇しました。たとえば、主キーのインデックスは自動的に作成されます。 id 列に手動でインデックスを付けることは技術的には可能ですが、それを行うことは完全に無意味です。

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

一意制約でも同様の状況が発生します。 unique キーワードで列 (または列のグループ) をマークすると、PostgreSQL はその列 (または列のグループ) に一意のインデックスを自動的に作成します。 。追加のインデックスを手動で作成する必要はありません。これを実行すると、インデックスが重複してしまいます。このような冗長なインデックスは削除でき、削除する必要があります。DUPLICATED_INDEXES 診断はそれらを識別するのに役立ちます。

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

重複する (交差する) インデックス

ほとんどのインデックスは単一の列に対して作成されます。クエリの最適化が開始されると、複数の列を含む、より複雑なインデックスが追加される場合があります。これにより、A、A B、A B C などの列にインデックスが作成されるシナリオが発生します。このシリーズの最初の 2 つのインデックスは、3 番目のインデックスの プレフィックス であるため、多くの場合破棄される可能性があります (このビデオを見ることをお勧めします) 。これらの冗長なインデックスを削除すると、ディスク領域を大幅に節約できます。INTERSECTED_INDEXES 診断は、そのようなケースを検出するように設計されています。

-- 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 では、サポートするインデックスを指定せずに外部キー制約を作成できます。つまり、別のテーブルを参照するテーブルはインデックスを必要とせず、自動的に作成しません。場合によっては、これは問題ではなく、まったく現れないこともあります。ただし、場合によっては、本番環境でインシデントが発生する可能性があります。

小さな例を見てみましょう (私は PostgreSQL 16.6 を使用しています):

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

orders テーブルと order_item テーブルがあります。これらは、order_id 列の外部キーを介してリンクされています。外部キーは常に主キーまたは一意の制約のいずれかを参照する必要があり、この例では満たされています。

テーブルにデータを入力し、統計を収集しましょう。 100,000 件の注文を追加します。半分には 2 つの商品があり、残りには 1 つの商品があります。

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

ID=100 の注文の商品を取得しようとすると、正常に 2 行が返されるはずです。注文テーブルの id 列にインデックスがあるため、このクエリは高速であるように見えるかもしれません。

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

ただし、このクエリをプロファイリングしようとすると、実行計画でテーブルが順次スキャンされることがわかります。また、読み取る必要がある大量のページ (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();
            }
        });
}

外部キーを持つ列のインデックスを作成すると、状況は通常に戻ります。

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

順次スキャンがクエリ プランから削除され、読み取られるページ数が大幅に減少します。

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

FOREIGN_KEYS_WITHOUT_INDEX 診断を使用すると、開発中の早期にそのようなケースを検出し、パフォーマンスの問題を防ぐことができます。

インデックスを作成する必要がありますか?

誤検知の問題を覚えておくことが重要です。すべての外部キー列にインデックスを付ける必要があるわけではありません。実稼働環境でのおおよそのテーブル サイズを見積もってみてください。外部キー列のフィルタリング、検索、または結合のコードを確認してください。インデックスが必要ないことが 100% 確信できる場合は、インデックスを除外に追加するだけです。よくわからない場合は、インデックスを作成することをお勧めします (後でいつでも削除できます)。

外部キーにインデックスがないためにデータベースが「遅くなる」というインシデントにはよく遭遇しましたが、そのようなインデックスが存在するためにデータベースが「遅くなる」というインシデントは見たことがありません。 。したがって、外部キーインデックスは最初から作成すべきではないという Percona ブログ記事の指摘には同意しません。これはDBAのアプローチです。あなたのチームには専任の DBA がいますか?

インデックス内の null 値

デフォルトでは、PostgreSQL には btree インデックスに null 値 が含まれていますが、通常はそれらは必要ありません。すべての null 値 は一意であり、列値が null であるレコードを単純に取得することはできません。ほとんどの場合、null 許容 列に where のような部分インデックスを作成して、インデックスから null を除外する方が良いでしょう。はヌルではありません。診断 INDEXES_WITH_NULL_VALUES は、そのようなケースの検出に役立ちます。

orders と order_items の例を考えてみましょう。 order_item テーブルには、倉庫 ID を表す null 許容 列 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;

いくつかの倉庫があると仮定します。ご注文決済後、組み立てを開始いたします。一部の注文のステータスを更新し、支払い済みとしてマークします。

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

注文内の個々の商品は、物流、在庫、倉庫の負荷などを考慮した内部アルゴリズムに従って、異なる倉庫から出荷される場合があります。倉庫を割り当てて在庫を更新した後、warehouse_id フィールド (最初は null でした)。

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

どのアイテムを完了して出荷する必要があるかを知るには、特定の倉庫 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());
    }
}
</databasecheckonhost>

最初の解決策は、おそらく

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;

このようなインデックスを作成すると、特定の倉庫のアイテムを検索するときに問題なく使用されます。このインデックスを使用すると、倉庫がまだ割り当てられていないすべてのアイテムを効率的に検索でき、warehouse_id が null という条件でレコードをフィルタリングできるように見えるかもしれません。

-- 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")
}

もちろん、これはテスト データベース内のデータの特定の分布に関連しています。 warehouse_id 列のカーディナリティは低く、その列に含まれる一意の値の数が少ないことを意味します。この列のインデックスの選択性は低くなります。インデックスの選択性とは、テーブルの unique / count() 内の行の総数に対する、個別のインデックス値 (つまり、カーディナリティ) の数の比率を指します。たとえば、一意のインデックスの選択性は 1 です。

null 値を削除し、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());
    }
}
</databasecheckonhost>

このインデックスはクエリ プランにすぐに表示されます:

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

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

将来の計画

これらは、pg-index-health が検出できるすべての問題からは程遠いです。診断の完全なリストは、GitHub 上のプロジェクトの README で入手でき、定期的に拡張されます。

pg-index-health を Spring Boot アプリケーションに統合するのは非常に簡単です。チェックを実行するためのオーバーヘッドは最小限です。その結果、一般的なエラーや問題から保護されます。ぜひ導入してみてください!

近い将来、すべてのチェックでパーティション化されたテーブルの完全なサポートを追加する予定です。現在、これは 25 チェックのうち 11 チェックに対してのみ実装されています。また、チェックの数を拡張したいと考えています。少なくとも 5 つの新しいチェックを実装するためのチケットがすでにあります。さらに、2025 年には Java 17 と Spring Boot 3 に切り替える予定です。

リポジトリのリンク

• pg-index-health
• チェック用の生の SQL クエリ
• デモアプリケーション

追加資料

• ロシア語での私の元の投稿
• 同様のソリューション - SchemaCrawler
• DBA: 役に立たないインデックスの検索 (ロシア語)
• Java 開発者の目から見た PostgreSQL のインデックスの健全性 (ロシア語)
• データベース構造の静的分析 (ロシア語)

以上がpg-index-health – PostgreSQL データベース用の静的分析ツールの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。