こんにちは！

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

長年にわたって、pg-index-health は進(jìn)化し、改善されてきました。 2024 年、私は數(shù)人の貢獻(xiàn)者の支援を受けて、殘っている「成長痛」のほとんどに対処し、プロジェクトを大規(guī)模な拡張の準(zhǔn)備ができる狀態(tài)にまで持ち込むことができました。

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

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

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

人々は徐々にマイクロサービスに移行し始めました。
データベースの數(shù)は大幅に増加しましたが、逆に、各データベース內(nèi)のテーブルの數(shù)は減少しました?，F(xiàn)在、各チームは獨(dú)自のデータベースの構(gòu)造を獨(dú)立して管理し始めています。一元化された専門知識のソースがなくなり、データベース設(shè)計(jì)のエラーが増加し、サービス間で伝播し始めました。

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

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

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

最近の傾向は、実裝の詳細(xì)に重點(diǎn)を置いて単體テストをできるだけ少なく記述し、サービスによって提供される実際の機(jī)能を検証するコンポーネント テストと統(tǒng)合テストを優(yōu)先することです。

私の個(gè)人的なお?dú)荬巳毪辘?テスト トロフィーです。そのベースには靜的コード分析があり、一般的なエラーを防ぐように設(shè)計(jì)されています。

靜的コード分析の重要性

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

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

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

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

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

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

SQL移行コードの検証

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

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

-- 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 コードは、読みやすく、理解しやすくなります。最も重要なことは、コード レビューがフォーマット設(shè)定に関する議論で行き詰まることがなくなります。 SQLFluff は一貫したスタイルを強(qiáng)制し、時(shí)間を節(jié)約します。

SQLFluff の動作

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

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

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

メタデータを使用したデータベース構(gòu)造の分析

データベース自體の構(gòu)造も確認(rèn)できます。ただし、移行の作業(yè)は非常に不便です。移行は多數(shù)存在する可能性があります。新しい移行により、以前の移行でのエラーが修正される場合があります。原則として、私たちは中間狀態(tài)よりもデータベースの最終構(gòu)造に興味を持ちます。

情報(bào)スキーマの活用

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

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

累積統(tǒng)計(jì)システム

メタデータに加えて、PostgreSQL は、どのようなクエリが実行されるか、どのように実行されるか、どのようなアクセス方法が使用されるかなど、各データベースの操作に関する情報(bào)を収集します。累積統(tǒng)計(jì)システムは、その収集を擔(dān)當(dāng)します。このデータ。

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

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

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

データベース クラスターが使用されている場合、統(tǒng)計(jì)は各ホストで個(gè)別に収集され、クラスター內(nèi)で複製されません。

pg-index-health とその構(gòu)造

上記で説明した、データベース自體內(nèi)のメタデータに基づいてデータベース構(gòu)造を分析するというアイデアは、pg-index-health というツールの形で私によって実裝されました。

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

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

小切手の種類

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

• 実行時(shí)チェック (統(tǒng)計(jì)が必要)。
• 靜的チェック (統(tǒng)計(jì)は必要ありません)。

実行時(shí)チェック

実行時(shí)チェックは、運(yùn)用環(huán)境のライブデータベースインスタンスで実行される場合にのみ意味を持ちます。これらのチェックには蓄積された統(tǒng)計(jì)が必要であり、クラスター內(nèi)のすべてのホストからこのデータを集約します。

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

前述したように、PostgreSQL では、統(tǒng)計(jì)は各ホストで個(gè)別に収集され、クラスター內(nèi)で複製されません。したがって、特定のインデックスが使用され、非同期レプリカでのみ必要となる狀況が簡単に発生する可能性があります。インデックスが必要かどうかを確実に判斷するには、クラスター內(nèi)の各ホストでチェックを?qū)g行し、結(jié)果を集計(jì)する必要があります。

靜的チェック

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

pg-index-healthの使用方法

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

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;

次に、標(biāo)準(zhǔn)テストを追加します:

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

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

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

誤検知と除外の追加

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

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

チーム內(nèi)では、これを行わないことに同意するかもしれません。その場合、対応するチェックの結(jié)果 (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());
    }
}

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

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 を?qū)毪工雸龊?、データベース構(gòu)造にすでにいくつかの 逸脫 があるにもかかわらず、すぐに対処したくないという狀況に遭遇することがよくあります。同時(shí)に、このチェックは関連性があるため、無効にすることはできません。このような場合、コード內(nèi)のすべての逸脫を修正するのが最善です:

@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 メカニズムの仕様により、多數(shù)の無効なタプルによりテーブル (またはインデックス) のサイズが急速に増大する肥大化のような狀況が発生する可能性があります。これは、たとえば、長時(shí)間実行されるトランザクションや、多數(shù)の行の 1 回限りの更新の結(jié)果として発生する可能性があります。

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

テーブル 肥大化 の問題をダウンタイムなしで解決するには、pg_repack などのサードパーティの拡張機(jī)能がよく使用されます。 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;

重複したインデックス

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

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

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

-- 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 はその列 (または列のグループ) に一意のインデックスを自動的に作成します。 。追加のインデックスを手動で作成する必要はありません。これを?qū)g行すると、インデックスが重複してしまいます。このような冗長なインデックスは削除でき、削除する必要があります。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;

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

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

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

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

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

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

テーブルにデータを入力し、統(tǒng)計(jì)を収集しましょう。 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());
    }
}

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

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

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

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 診斷を使用すると、開発中の早期にそのようなケースを検出し、パフォーマンスの問題を防ぐことができます。

インデックス內(nèi)の null 値

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

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