pg-index-health – PostgreSQL 데이터베이스를 위한 정적 분석 도구

안녕하세요!

2019년부터 저는 데이터베이스 구조를 분석하고 잠재적인 문제를 식별하는 pg-index-health라는 오픈 소스 도구를 개발해 왔습니다. 이전 기사 중 하나에서 이 도구가 어떻게 구현되었는지에 대한 이야기를 공유했습니다.

수년에 걸쳐 pg-index-health는 발전하고 개선되었습니다. 2024년에는 여러 기여자의 지원을 받아 남은 "성장통"을 대부분 해결하고 프로젝트를 대규모 확장이 가능한 상태로 만들었습니다.

마이크로서비스의 등장에 따른 데이터베이스의 성장

저는 2015년부터 PostgreSQL을 사용해 왔으며 이 흥미로운 여정은 야로슬라블에 본사를 둔 Tensor에서 시작되었습니다.

2015년에도 여전히 방대한 데이터베이스와 수많은 테이블을 갖춘 단일체 시대였습니다. 일반적으로 이러한 데이터베이스 구조를 변경하려면 핵심 지식 보유자 역할을 하는 설계자나 개발 책임자의 필수 승인이 필요했습니다. 이는 대부분의 오류로부터 보호되지만 변경 프로세스가 느려지고 확장이 전혀 불가능합니다.

점차적으로 사람들은 마이크로서비스로 전환하기 시작했습니다.
데이터베이스 수는 크게 늘어났지만, 반대로 각 데이터베이스 내의 테이블 수는 감소했습니다. 이제 각 팀은 자체 데이터베이스의 구조를 독립적으로 관리하기 시작했습니다. 중앙화된 전문 지식의 원천이 사라지고 데이터베이스 설계 오류가 한 서비스에서 다른 서비스로 급증하고 전파되기 시작했습니다.

테스트 피라미드와 그 모양

테스팅 피라미드에 대해 들어보신 분들이 많을 것입니다. 모놀리스의 경우 광범위한 단위 테스트 기반을 갖춘 상당히 특징적인 모양을 갖습니다. 자세한 내용은 Martin Fowler의 글을 추천드립니다.

마이크로서비스는 개발 접근 방식뿐만 아니라 테스트 피라미드의 모습도 바꿔 놓았습니다. 이러한 변화는 주로 컨테이너화 기술(Docker, Testcontainers)의 증가에 의해 주도되었습니다. 오늘날 테스트 피라미드는 더 이상 피라미드가 아닙니다. 매우 기괴한 모양을 가질 수 있습니다. 가장 잘 알려진 예로는 벌집과 테스트 트로피가 있습니다.

최신 추세는 구현 세부 사항에 초점을 맞춰 단위 테스트를 최대한 적게 작성하고 서비스에서 제공하는 실제 기능을 검증하는 구성 요소 및 통합 테스트의 우선 순위를 지정하는 것입니다.

제가 개인적으로 가장 좋아하는 것은 테스트 트로피입니다. 그 기반에는 일반적인 오류를 방지하도록 설계된 정적 코드 분석이 있습니다.

정적 코드 분석의 중요성

Java 및 Kotlin 코드에 대한 정적 분석은 이제 일반적인 관행입니다. Kotlin 서비스의 경우 일반적으로 선택되는 도구는 decekt입니다. Java 애플리케이션의 경우 사용 가능한 도구(흔히 Linter라고도 함)의 범위가 더 넓습니다. 주요 도구로는 Checkstyle, PMD, SpotBugs, Error Prone 등이 있습니다. 이에 대한 자세한 내용은 이전 기사에서 읽어보실 수 있습니다.

특히 detekt와 Checkstyle 모두 코드 서식을 처리하여 효과적으로 서식 지정 도구로 작동합니다.

데이터베이스 마이그레이션에 대한 정적 분석

최신 마이크로서비스에는 애플리케이션 코드와 함께 데이터베이스 구조를 생성하고 업데이트하기 위한 데이터베이스 마이그레이션이 포함되는 경우가 많습니다.

Java 생태계에서 마이그레이션을 관리하는 주요 도구는 Liquibase와 Flyway입니다. 데이터베이스 구조에 대한 모든 변경 사항은 항상 마이그레이션 시 문서화되어야 합니다. 프로덕션에서 문제가 발생하는 동안 수동으로 변경하더라도 해당 변경 사항을 모든 환경에 적용하려면 나중에 마이그레이션을 생성해야 합니다.

일반 SQL로 마이그레이션을 작성하는 것은 Liquibase와 같은 XML 언어 도구를 배우는 것에 비해 최대의 유연성을 제공하고 시간을 절약하므로 모범 사례입니다. 이에 대해서는 내 기사 "기능 테스트에서 PostgreSQL 사용을 위한 6가지 팁"에서 다루었습니다.

SQL 마이그레이션 코드 확인

마이그레이션에서 SQL 코드를 확인하려면 기본적으로 SQL에 해당하는 Checkstyle인 SQLFluff를 사용하는 것이 좋습니다. 이 린터는 여러 데이터베이스와 언어(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 문에서 반환 값 형식 지정과 관련된 문제를 발견했습니다. 단 하나의 열만 반환되면 이를 열에 넣지 않습니다. 별도의 행. 두 번째 점은 선택 결과의 잘못된 열 순서입니다. 먼저 간단한 열을 반환한 다음 계산 결과만 반환합니다. 세 번째는 join 문의 및에 대한 잘못된 대소문자입니다. 모든 쿼리를 소문자로 작성하는 것을 선호합니다.

사용 중인 SQLFluff의 더 많은 예를 보려면 내 오픈 소스 프로젝트인 one, two를 확인하세요.

메타데이터를 사용하여 데이터베이스 구조 분석

데이터베이스 자체의 구조도 확인할 수 있습니다. 그러나 마이그레이션 작업은 매우 불편합니다. 마이그레이션이 많을 수 있습니다. 새로운 마이그레이션은 이전 마이그레이션의 오류를 수정할 수도 있습니다. 일반적으로 우리는 중간 상태보다 데이터베이스의 최종 구조

에 더 관심이 있습니다.

정보 스키마 활용

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 마이그레이션을 생성할 수 있는 마이그레이션 생성기

수표 유형

모든 점검(진단이라고도 함)은 두 그룹으로 나뉩니다.

• 런타임 확인(통계 필요)
• 정적 검사(통계가 필요하지 않음).

런타임 확인

런타임 검사는 프로덕션의 라이브 데이터베이스 인스턴스에서 실행될 때만 의미가 있습니다. 이러한 검사에는 누적된 통계가 필요하며 클러스터의 모든 호스트에서 이 데이터를 집계합니다.

기본, 보조, 비동기 복제본의 세 가지 호스트로 구성된 데이터베이스 클러스터를 생각해 보겠습니다. 일부 서비스는 유사한 토폴로지를 가진 클러스터를 사용하고 부하 분산을 위해 비동기 복제본에서만 대량 읽기 쿼리를 실행합니다. 이러한 쿼리는 추가 로드를 생성하고 다른 쿼리의 대기 시간에 부정적인 영향을 미치기 때문에 일반적으로 기본 호스트에서 실행되지 않습니다.

앞서 언급했듯이 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 메커니즘의 특성으로 인해 데드 튜플 수가 많아 테이블(또는 인덱스)의 크기가 급격히 커지는 팽창과 같은 상황이 발생할 수 있습니다. 예를 들어 장기 실행 트랜잭션이나 많은 수의 행에 대한 일회성 업데이트로 인해 이런 일이 발생할 수 있습니다.

데이터베이스 내의 가비지 수집은 autovacuum 프로세스에 의해 처리되지만 점유된 물리적 디스크 공간을 확보하지는 않습니다. 테이블의 물리적 크기를 효과적으로 줄이는 유일한 방법은 작업 기간 동안 독점 잠금이 필요한 VACUUM FULL 명령을 사용하는 것입니다. 대형 테이블의 경우 몇 시간이 걸릴 수 있으므로 대부분의 최신 서비스에서는 완전 진공 청소가 불가능합니다.

다운타임 없이 테이블 블로트 문제를 해결하기 위해 pg_repack과 같은 타사 확장 프로그램을 사용하는 경우가 많습니다. pg_repack의 필수 요구 사항 중 하나는 대상 테이블에 기본 키 또는 기타 고유성 제약 조건이 있다는 것입니다. TABLES_WITHOUT_PRIMARY_KEY 진단은 기본 키가 없는 테이블을 감지하고 향후 유지 관리 문제를 방지하는 데 도움이 됩니다.

다음은 기본 키가 없는 테이블의 예입니다. 이 테이블에 bloat이 발생하면 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;

중복된 인덱스

저희 데이터베이스는 리소스가 제한된 호스트에서 작동하며 디스크 공간도 그중 하나입니다. 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와 같은 열에 대해 인덱스가 생성되는 시나리오가 발생합니다. 이 시리즈의 처음 두 인덱스는 세 번째 인덱스의 접두사이므로 종종 삭제될 수 있습니다(이 비디오를 시청하는 것이 좋습니다). . 이러한 중복 인덱스를 제거하면 상당한 디스크 공간을 절약할 수 있으며 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이 아닙니다. 진단 INDEXES_WITH_NULL_VALUES는 이러한 사례를 감지하는 데 도움이 됩니다.

orders 및 order_items를 예로 들어 보겠습니다. order_item 테이블에는 창고 ID를 나타내는 nullable 열 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임)
어떤 품목을 완성하고 배송해야 하는지 알아보려면 특정 창고 ID로 검색해야 합니다. 특정 기간 동안은 유료 주문만 받습니다.

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());
    }
}
</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 열의 카디널리티가 낮습니다. 즉, 열에 포함된 고유 값의 수가 적습니다. 이 열의 인덱스는 선택성이 낮습니다. 인덱스 선택성은 테이블의 총 행 수에 대한 개별 인덱스 값(예: 카디널리티) 수의 비율을 나타냅니다. 예를 들어 고유 인덱스의 선택성은 1입니다.

warehouse_id 열에서 null 값을 제거하고 부분 인덱스를 생성하여 인덱스의 선택성을 높일 수 있습니다.

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-색인-건강
• 검사를 위한 원시 SQL 쿼리
• 데모 애플리케이션

추가 자료

• 러시아어로 된 내 원본 게시물
• 유사한 솔루션 - SchemaCrawler
• DBA: 쓸모없는 인덱스 찾기(러시아어)
• Java 개발자의 눈으로 본 PostgreSQL의 인덱스 상태(러시아어)
• 데이터베이스 구조의 정적 분석(러시아어)

위 내용은 pg-index-health – PostgreSQL 데이터베이스를 위한 정적 분석 도구의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!