pg-index-health – alat analisis statik untuk anda pangkalan data PostgreSQL

Hello!

Sejak 2019, saya telah membangunkan alat sumber terbuka yang dipanggil pg-index-health, yang menganalisis struktur pangkalan data dan mengenal pasti isu yang berpotensi. Dalam salah satu artikel saya sebelum ini, saya berkongsi kisah bagaimana alat ini dihidupkan.

Sejak beberapa tahun, pg-index-health telah berkembang dan bertambah baik. Pada tahun 2024, dengan sokongan beberapa penyumbang, saya berjaya menangani kebanyakan baki "kesakitan yang semakin meningkat" dan membawa projek itu ke keadaan yang sedia untuk pengembangan berskala besar.

Pertumbuhan Pangkalan Data dengan Peningkatan Perkhidmatan Mikro

Saya telah bekerja dengan PostgreSQL sejak 2015, dan perjalanan yang menarik ini bermula di syarikat Tensor yang berpangkalan di Yaroslavl.

Pada tahun 2015, ia masih merupakan era monolit dengan pangkalan data yang besar dan sejumlah besar jadual. Lazimnya, sebarang perubahan pada struktur pangkalan data sedemikian memerlukan kelulusan mandatori daripada arkitek atau peneraju pembangunan, yang berkhidmat sebagai pemegang pengetahuan utama. Walaupun ini dilindungi daripada kebanyakan ralat, ia memperlahankan proses membuat perubahan dan tidak boleh berskala sepenuhnya.

Secara beransur-ansur, orang mula beralih kepada perkhidmatan mikro.
Bilangan pangkalan data meningkat dengan ketara, tetapi bilangan jadual dalam setiap pangkalan data, sebaliknya, menurun. Kini, setiap pasukan mula menguruskan struktur pangkalan datanya sendiri secara bebas. Sumber kepakaran terpusat hilang, dan ralat reka bentuk pangkalan data mula berlipat ganda dan merebak dari satu perkhidmatan ke perkhidmatan yang lain.

Piramid Pengujian dan Bentuknya

Kebanyakan daripada anda mungkin pernah mendengar tentang piramid ujian. Untuk monolit, ia mempunyai bentuk yang agak ciri dengan asas ujian unit yang luas. Untuk butiran lanjut, saya mengesyorkan artikel Martin Fowler.

Perkhidmatan mikro telah mengubah bukan sahaja pendekatan pembangunan tetapi juga rupa piramid ujian. Peralihan ini sebahagian besarnya didorong oleh peningkatan teknologi kontena (Docker, Testcontainers). Hari ini piramid ujian bukan lagi piramid sama sekali. Ia boleh mempunyai bentuk yang sangat pelik. Contoh yang paling terkenal ialah Honeycomb dan Trofi Ujian.

Arah aliran moden ialah menulis seberapa sedikit ujian unit yang mungkin, memfokuskan pada butiran pelaksanaan dan mengutamakan ujian komponen dan penyepaduan yang mengesahkan kefungsian sebenar yang disediakan oleh perkhidmatan.

Kegemaran peribadi saya ialah Trofi Ujian. Pada asasnya terletak analisis kod statik, yang direka untuk melindungi daripada ralat biasa.

Kepentingan Analisis Kod Statik

Analisis statik untuk kod Java dan Kotlin kini menjadi amalan biasa. Untuk perkhidmatan Kotlin, alat pilihan biasanya dikesan. Untuk aplikasi Java, julat alatan yang tersedia (sering dirujuk sebagai linters) adalah lebih luas. Alatan utama termasuk Gaya Semak, PMD, SpotBugs dan Ralat Rawan. Anda boleh membaca lebih lanjut tentang mereka dalam artikel saya sebelum ini.

Terutama, kedua-dua detekt dan Gaya semak turut mengendalikan pemformatan kod, berfungsi sebagai pemformat dengan berkesan.

Analisis Statik untuk Migrasi Pangkalan Data

Perkhidmatan mikro moden selalunya termasuk migrasi pangkalan data untuk mencipta dan mengemas kini struktur pangkalan data bersama kod aplikasi.

Dalam ekosistem Java, alatan utama untuk mengurus migrasi ialah Liquibase dan Flyway. Sebarang perubahan pada struktur pangkalan data mesti sentiasa didokumenkan dalam migrasi. Walaupun perubahan dibuat secara manual semasa insiden dalam pengeluaran, penghijrahan mesti dibuat kemudian untuk menggunakan perubahan tersebut merentas semua persekitaran.

Menulis migrasi dalam SQL biasa ialah amalan terbaik kerana ia memberikan fleksibiliti maksimum dan menjimatkan masa berbanding mempelajari dialek XML alat seperti Liquibase. Saya menyentuh perkara ini dalam artikel saya, "Enam Petua untuk Menggunakan PostgreSQL dalam Ujian Fungsian".

Mengesahkan Kod Migrasi SQL

Untuk mengesahkan kod SQL dalam migrasi, saya syorkan menggunakan SQLFluff, yang pada asasnya adalah Checkstyle yang setara untuk SQL. Linter ini menyokong berbilang pangkalan data dan dialek (termasuk PostgreSQL) dan boleh disepadukan ke dalam saluran paip CI anda. Ia menawarkan lebih 60 peraturan yang boleh disesuaikan, membolehkan anda mengurus alias jadual dan lajur, selongsong perintah SQL, lekukan, susunan lajur dalam pertanyaan dan banyak lagi.

Bandingkan pertanyaan dengan dan tanpa pemformatan:

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

Kod SQL yang diformat dengan baik lebih mudah dibaca dan difahami. Paling penting, semakan kod tidak lagi terganggu oleh perbincangan tentang pilihan pemformatan. SQLFluff menguatkuasakan gaya yang konsisten, menjimatkan masa.

SQLFluff dalam Tindakan

Ini adalah rupa dalam permintaan tarik sebenar:

Di sini SQLFluff menemui masalah dengan memformat nilai pulangan dalam pernyataan pilih: apabila hanya satu lajur dikembalikan, kami tidak meletakkannya pada baris berasingan. Perkara kedua ialah susunan lajur yang salah dalam hasil pemilihan: mula-mula kita kembalikan lajur mudah dan kemudian barulah hasil pengiraan. Dan yang ketiga ialah kes yang salah untuk dan dalam pernyataan join: Saya lebih suka menulis semua pertanyaan dalam huruf kecil.

Untuk lebih banyak contoh SQLFluff yang sedang digunakan, lihat projek sumber terbuka saya: satu, dua.

Menggunakan Metadata untuk Menganalisis Struktur Pangkalan Data

Struktur pangkalan data itu sendiri juga boleh disemak. Walau bagaimanapun, bekerja dengan migrasi adalah amat menyusahkan: mungkin terdapat banyak daripada mereka; migrasi baharu mungkin membetulkan ralat dalam migrasi sebelumnya, dan seterusnya. Sebagai peraturan, kami lebih berminat dengan struktur akhir pangkalan data daripada keadaan pertengahannya.

Memanfaatkan Skema Maklumat

PostgreSQL (seperti kebanyakan pangkalan data hubungan lain) menyimpan metadata tentang semua objek dan hubungan antara mereka dan menyediakannya secara luaran dalam bentuk information_schema. Kami boleh menggunakan pertanyaan untuk information_schema untuk mengenal pasti sebarang penyimpangan, masalah atau ralat biasa (inilah yang dilakukan oleh SchemaCrawler).

Memandangkan kami hanya bekerja dengan PostgreSQL, bukannya information_schema kami boleh menggunakan katalog sistem (skema pg_catalog), yang memberikan lebih banyak maklumat tentang struktur dalaman pangkalan data tertentu.

Sistem Statistik Kumulatif

Selain metadata, PostgreSQL mengumpul maklumat tentang pengendalian setiap pangkalan data: pertanyaan apakah yang dilaksanakan, cara ia dilaksanakan, kaedah akses yang digunakan, dll. Sistem Statistik Kumulatif bertanggungjawab untuk mengumpul data ini.

Dengan menanyakan statistik ini melalui paparan sistem dan menggabungkannya dengan data daripada katalog sistem, kami boleh:

• Kenal pasti indeks yang tidak digunakan;
• Kesan jadual yang tidak mempunyai pengindeksan yang mencukupi.

Statistik boleh ditetapkan semula secara manual. Tarikh dan masa tetapan semula terakhir direkodkan dalam sistem. Adalah penting untuk mempertimbangkan perkara ini untuk memahami sama ada statistik boleh dipercayai atau tidak. Sebagai contoh, jika anda mempunyai beberapa logik perniagaan yang dilaksanakan sekali sebulan/suku/setengah tahun, statistik perlu dikumpul untuk tempoh sekurang-kurangnya selang yang dinyatakan di atas.

Jika kluster pangkalan data digunakan, maka statistik dikumpulkan secara bebas pada setiap hos dan tidak direplikasi dalam kluster.

pg-index-health dan Strukturnya

Idea menganalisis struktur pangkalan data berdasarkan metadata dalam pangkalan data itu sendiri, seperti yang diterangkan di atas, telah saya laksanakan dalam bentuk alat yang dipanggil pg-index-health.

Penyelesaian saya termasuk komponen berikut:

• Satu set semakan dalam bentuk pertanyaan SQL, diletakkan dalam repositori berasingan (kini terdiri daripada 25 semakan). Pertanyaan dipisahkan daripada pangkalan kod Java dan boleh digunakan semula dalam projek yang ditulis dalam bahasa pengaturcaraan lain.
• Model domain — set minimum kelas yang mewakili hasil semakan sebagai objek.
• Abstraksi HighAvailabilityPgConnection untuk menyambung ke gugusan pangkalan data yang terdiri daripada berbilang hos.
• Utiliti untuk melaksanakan pertanyaan SQL dan mensiri hasil ke dalam objek model domain.
• Pemula Spring Boot untuk penyepaduan semakan yang mudah dan cepat ke dalam ujian unit/komponen/integrasi.
• Penjana migrasi yang boleh mencipta migrasi SQL pembetulan untuk isu yang dikenal pasti.

Jenis-jenis Cek

Semua semakan (juga dirujuk sebagai diagnostik) dibahagikan kepada dua kumpulan:

• Semakan masa jalan (memerlukan statistik).
• Semakan statik (tidak memerlukan statistik).

Semakan masa jalan

Semakan masa jalan hanya bermakna apabila dilaksanakan pada contoh pangkalan data langsung dalam pengeluaran. Semakan ini memerlukan statistik terkumpul dan mengagregatkan data ini daripada semua hos dalam kelompok.

Mari kita pertimbangkan kluster pangkalan data yang terdiri daripada tiga hos: replika primer, sekunder dan tak segerak. Sesetengah perkhidmatan menggunakan kluster dengan topologi yang serupa dan melaksanakan pertanyaan bacaan berat hanya pada replika tak segerak untuk mengimbangi beban. Pertanyaan sedemikian biasanya tidak dilaksanakan pada hos utama kerana ia menghasilkan beban tambahan dan memberi kesan negatif kepada kependaman pertanyaan lain.

Seperti yang dinyatakan sebelum ini, dalam PostgreSQL, statistik dikumpulkan secara berasingan pada setiap hos dan tidak direplikasi dalam kelompok. Oleh itu, anda boleh dengan mudah menghadapi situasi di mana indeks tertentu digunakan dan diperlukan hanya pada replika tak segerak. Untuk membuat penentuan yang boleh dipercayai tentang sama ada indeks diperlukan atau tidak, adalah perlu untuk menjalankan semakan pada setiap hos dalam kluster dan mengagregat hasilnya.

Pemeriksaan statik

Semakan statik tidak memerlukan statistik terkumpul dan boleh dilaksanakan pada hos utama serta-merta selepas menggunakan migrasi. Sudah tentu, ia juga boleh digunakan pada pangkalan data pengeluaran untuk mendapatkan data dalam masa nyata. Walau bagaimanapun, kebanyakan semakan adalah statik dan ia amat berguna dalam ujian, kerana ia membantu menangkap dan mencegah ralat biasa semasa fasa pembangunan.

Cara Menggunakan pg-index-health

Kes penggunaan utama untuk pg-index-health sedang menambahkan ujian untuk mengesahkan struktur pangkalan data dalam saluran paip ujian anda.

Untuk aplikasi Spring Boot, anda perlu menambah pemula pada kebergantungan ujian anda:

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

Kemudian tambahkan ujian standard:

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

Dalam ujian ini, semua cek yang ada disuntik sebagai senarai. Kemudian, hanya semakan statik ditapis dan dilaksanakan pada pangkalan data sebenar yang digunakan dalam bekas dengan migrasi yang digunakan.

Sebaik-baiknya, setiap cek harus mengembalikan senarai kosong. Jika terdapat sebarang penyimpangan semasa menambah penghijrahan seterusnya, ujian akan gagal. Pembangun akan dipaksa untuk memberi perhatian kepada perkara ini dan menyelesaikan masalah dalam apa jua cara: sama ada membetulkannya dalam penghijrahannya atau mengabaikannya secara jelas.

Positif Palsu dan Menambah Pengecualian

Adalah penting untuk memahami bahawa pg-index-health, seperti mana-mana penganalisis statik lain, mungkin menjana positif palsu. Selain itu, beberapa semakan mungkin tidak berkaitan dengan projek anda. Sebagai contoh, ia dianggap sebagai amalan yang baik untuk mendokumenkan struktur pangkalan data. PostgreSQL membenarkan penambahan komen pada hampir semua objek pangkalan data. Dalam penghijrahan, ini mungkin kelihatan seperti berikut:

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

Dalam pasukan anda, anda mungkin bersetuju untuk tidak melakukan perkara ini. Dalam kes itu, hasil semakan yang sepadan (JADUAL_TANPA_DESKRIPSI, LAjur_TANPA_DESKRIPSI, FUNGSI_TANPA_DESKRIPSI) menjadi tidak relevan untuk anda

>.

Anda boleh sama ada mengecualikan semakan ini sepenuhnya:

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

Atau abaikan keputusan mereka:

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

Apabila memperkenalkan pg-index-health, anda mungkin sering menghadapi situasi di mana struktur pangkalan data sudah mempunyai beberapa penyimpangan, tetapi anda tidak mahu menanganinya dengan segera. Pada masa yang sama, semakan adalah berkaitan dan melumpuhkannya bukanlah satu pilihan. Dalam kes sedemikian, adalah lebih baik untuk membetulkan semua penyimpangan dalam kod:

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

Kini, saya ingin menumpukan dengan lebih terperinci pada isu yang paling kerap dihadapi.

Jadual Tanpa Kekunci Utama

Disebabkan oleh spesifikasi mekanisme MVCC dalam PostgreSQL, situasi seperti kembung boleh berlaku, di mana saiz jadual (atau indeks) anda berkembang pesat disebabkan oleh sejumlah besar tupel mati. Ini boleh berlaku, sebagai contoh, hasil daripada urus niaga yang berjalan lama atau kemas kini satu kali bagi sejumlah besar baris.

Pengumpulan sampah dalam pangkalan data dikendalikan oleh proses autovakum, tetapi ia tidak mengosongkan ruang cakera fizikal yang diduduki. Satu-satunya cara untuk mengurangkan saiz fizikal jadual dengan berkesan ialah dengan menggunakan perintah VACUUM FULL, yang memerlukan kunci eksklusif untuk tempoh operasi. Untuk meja besar, ini boleh mengambil masa beberapa jam, menjadikan pemvakum penuh tidak praktikal untuk kebanyakan perkhidmatan moden.

Untuk menangani isu jadual mengembang tanpa masa henti, sambungan pihak ketiga seperti pg_repack sering digunakan. Salah satu keperluan wajib pg_repack ialah kehadiran kunci utama atau beberapa kekangan keunikan lain pada jadual sasaran. Diagnostik TABLES_WITHOUT_PRIMARY_KEY membantu mengesan jadual tanpa kunci utama dan menghalang masalah penyelenggaraan pada masa hadapan.

Di bawah ialah contoh jadual tanpa kunci utama. Jika bloat berlaku dalam jadual ini, pg_repack tidak akan dapat memprosesnya dan akan mengembalikan ralat.

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

Indeks Pendua

Pangkalan data kami beroperasi pada hos dengan sumber terhad, dan ruang cakera adalah salah satu daripadanya. Apabila menggunakan penyelesaian Pangkalan Data-sebagai-Perkhidmatan, selalunya terdapat had fizikal pada saiz pangkalan data maksimum yang tidak boleh diubah.

Setiap indeks dalam jadual ialah entiti berasingan pada cakera. Ia menempati ruang dan memerlukan sumber untuk penyelenggaraan, yang melambatkan pemasukan dan kemas kini data. Kami mencipta indeks untuk mempercepatkan carian atau memastikan keunikan nilai tertentu. Walau bagaimanapun, penggunaan indeks yang tidak betul boleh membawa kepada situasi di mana saiz gabungannya melebihi saiz data berguna dalam jadual itu sendiri. Oleh itu, bilangan indeks dalam jadual hendaklah minimum tetapi mencukupi untuk kefungsiannya.

Saya telah menemui banyak kes di mana indeks yang tidak perlu dibuat dalam migrasi. Contohnya, indeks untuk kunci utama dibuat secara automatik. Walaupun secara teknikal mungkin untuk mengindeks lajur id secara manual, berbuat demikian adalah sia-sia sama sekali.

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

Situasi yang sama timbul dengan kekangan unik. Apabila anda menandakan lajur (atau sekumpulan lajur) dengan kata kunci unik, PostgreSQL secara automatik mencipta indeks unik untuk lajur itu (atau kumpulan lajur) . Membuat indeks tambahan secara manual adalah tidak perlu. Jika dilakukan, ini menghasilkan indeks pendua. Indeks berlebihan sedemikian boleh dan harus dialih keluar, dan diagnostik DUPLICATED_INDEXES boleh membantu mengenal pastinya.

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

Indeks bertindih (bersilang).

Kebanyakan indeks dicipta untuk satu lajur. Apabila pengoptimuman pertanyaan bermula, indeks yang lebih kompleks boleh ditambah, melibatkan berbilang lajur. Ini membawa kepada senario di mana indeks dicipta untuk lajur seperti A, A B dan A B C. Dua indeks pertama dalam siri ini selalunya boleh dibuang kerana ia adalah awalan daripada yang ketiga (saya cadangkan menonton video ini) . Mengalih keluar indeks berlebihan ini boleh menjimatkan ruang cakera yang ketara, dan diagnostik INTERSECTED_INDEXES direka untuk mengesan kes sedemikian.

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

Kunci Asing Tanpa Indeks

PostgreSQL membenarkan penciptaan kekangan kunci asing tanpa menyatakan indeks sokongan, bermakna jadual yang merujuk yang lain tidak memerlukan dan tidak akan mencipta indeks secara automatik. Dalam sesetengah kes, ini mungkin tidak menjadi masalah dan mungkin tidak nyata sama sekali. Walau bagaimanapun, kadangkala ia boleh menyebabkan insiden dalam pengeluaran.

Mari kita lihat contoh kecil (saya menggunakan PostgreSQL 16.6):

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

Kami mempunyai jadual pesanan dan jadual barang_pesanan. Ia dipautkan melalui kunci asing pada lajur order_id. Kunci asing hendaklah sentiasa merujuk sama ada kunci utama atau beberapa kekangan unik, yang dipenuhi dalam kes kami.

Mari isikan jadual dengan data dan kumpulkan statistik. Kami akan menambah 100,000 pesanan, dengan separuh mempunyai dua item dan yang lain mempunyai satu.

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

Jika kami cuba mendapatkan semula item untuk pesanan dengan ID=100, kami sepatutnya berjaya mengembalikan 2 baris. Memandangkan terdapat indeks pada lajur id dalam jadual pesanan, nampaknya pertanyaan ini sepatutnya pantas.

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

Walau bagaimanapun, jika kami cuba memprofilkan pertanyaan ini, kami akan melihat pengimbasan berurutan jadual dalam pelan pelaksanaan. Kita juga harus mengambil berat tentang sejumlah besar halaman yang perlu dibaca (parameter Penampan).

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

Jika kita mencipta indeks untuk lajur dengan kunci asing, keadaan akan kembali normal:

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

Imbasan berurutan akan hilang daripada pelan pertanyaan dan bilangan halaman yang dibaca akan dikurangkan dengan ketara:

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

Diagnostik FOREIGN_KEYS_WITHOUT_INDEX akan membolehkan anda mengesan kes sedemikian awal semasa pembangunan, mencegah isu prestasi.

Perlukah saya mencipta indeks atau tidak?

Adalah penting untuk mengingati isu positif palsu: tidak semua lajur kunci asing perlu diindeks. Cuba anggaran saiz jadual anggaran dalam pengeluaran; semak kod anda untuk menapis, mencari atau menyertai lajur kunci asing. Jika anda 100% pasti bahawa anda tidak memerlukan indeks, anda hanya boleh menambahkannya pada pengecualian. Jika anda tidak pasti, lebih baik buat indeks (ia sentiasa boleh dialih keluar kemudian).

Saya sering mengalami insiden di mana pangkalan data "perlahan" kerana ketiadaan indeks pada kunci asing, tetapi saya tidak melihat sebarang insiden di mana pangkalan data "perlahan" kerana kehadiran indeks sedemikian . Oleh itu, saya tidak bersetuju dengan perkara yang dinyatakan dalam artikel blog Percona bahawa indeks kunci asing tidak boleh dibuat sama sekali dari awal. Ini ialah pendekatan DBA. Adakah anda mempunyai DBA khusus pada pasukan anda?

Nilai nol dalam indeks

Secara lalai, PostgreSQL termasuk nilai null dalam indeks btree, tetapi biasanya tidak diperlukan di sana. Semua nilai nol adalah unik dan anda tidak boleh mendapatkan semula rekod dengan nilai lajur adalah nol. Selalunya, adalah lebih baik untuk mengecualikan null daripada indeks dengan membuat indeks separa pada lajur nullable seperti tempat bukan batal. Diagnostik INDEXES_WITH_NULL_VALUES membantu mengesan kes sedemikian.

Mari kita pertimbangkan contoh dengan pesanan dan pesanan_item. Jadual order_item mempunyai lajur nullable warehouse_id, yang mewakili ID gudang.

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

Andaikan kita mempunyai beberapa gudang. Selepas pesanan dibayar, kami mula memasangnya. Kami akan mengemas kini status beberapa pesanan dan menandakannya sebagai berbayar.

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

Item individu dalam pesanan boleh dihantar dari gudang yang berbeza mengikut algoritma dalaman, mempertimbangkan logistik, stok, beban gudang, dll. Selepas menetapkan gudang dan mengemas kini stok, kami mengemas kini warehouse_id medan untuk setiap item dalam pesanan (yang pada mulanya batal).

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

Kami perlu mencari mengikut ID gudang tertentu untuk mengetahui item yang perlu dilengkapkan dan dihantar. Kami hanya menerima pesanan berbayar untuk julat masa tertentu.

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

Penyelesaian pertama mungkin ialah indeks biasa pada lajur 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;

Jika kami mencipta indeks sedemikian, ia akan digunakan tanpa masalah semasa mencari item untuk gudang tertentu. Nampaknya indeks ini sepatutnya membenarkan mencari dengan cekap semua item di mana gudang belum ditetapkan, menapis rekod dengan syarat warehouse_id adalah batal.

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

Walau bagaimanapun, jika kita melihat rancangan pelaksanaan pertanyaan, kita akan melihat akses berjujukan di sana — indeks tidak digunakan.

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

Sudah tentu, ini berkaitan dengan pengedaran data khusus dalam pangkalan data ujian. Lajur warehouse_id mempunyai kardinaliti yang rendah, bermakna bilangan nilai unik di dalamnya adalah rendah. Indeks pada lajur ini mempunyai selektiviti yang rendah. Pemilihan indeks merujuk kepada nisbah bilangan nilai diindeks yang berbeza (iaitu, kardinaliti) kepada jumlah bilangan baris dalam jadual berbeza / kiraan(). Contohnya, indeks unik mempunyai selektiviti satu.

Kami boleh meningkatkan selektiviti indeks dengan mengalih keluar nilai nol dan mencipta indeks separa pada lajur 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());
    }
}

Kami akan segera melihat indeks ini dalam pelan pertanyaan:

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

Jika kita membandingkan saiz indeks, kita akan melihat perbezaan yang ketara. Indeks separa adalah jauh lebih kecil dan akan dikemas kini kurang kerap. Menggunakan indeks ini, kami menjimatkan ruang cakera dan meningkatkan prestasi.

Pertanyaan untuk mendapatkan saiz indeks

@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

Rancangan untuk Masa Depan

Ini jauh daripada semua isu yang pg-index-health boleh mengesan. Senarai penuh diagnostik tersedia dalam README projek di GitHub, dan ia sentiasa dikembangkan.

Mengintegrasikan pg-index-health ke dalam aplikasi Spring Boot agak mudah. Overhed untuk menjalankan pemeriksaan adalah minimum. Akibatnya, anda akan mendapat perlindungan daripada ralat dan isu biasa. Saya menggalakkan anda untuk mencuba melaksanakannya!

Dalam masa terdekat, saya bercadang untuk menambah sokongan penuh untuk jadual pembahagian dalam semua semakan. Pada masa ini, ini hanya dilaksanakan untuk 11 daripada 25 semakan. Saya juga ingin menambah bilangan cek: sudah ada tiket untuk melaksanakan sekurang-kurangnya 5 cek baharu. Selain itu, pada tahun 2025, saya bercadang untuk beralih kepada Java 17 dan Spring Boot 3.

Pautan Repositori

• pg-index-health
• Pertanyaan sql mentah untuk semakan
• Aplikasi demo

Bahan Tambahan

• Siaran asal saya dalam bahasa Rusia
• Penyelesaian yang serupa - SchemaCrawler
• DBA: mencari indeks yang tidak berguna (dalam bahasa Rusia)
• Kesihatan indeks dalam PostgreSQL melalui pandangan pembangun Java (dalam bahasa Rusia)
• Analisis statik struktur pangkalan data (dalam bahasa Rusia)

Atas ialah kandungan terperinci pg-index-health – alat analisis statik untuk anda pangkalan data PostgreSQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!