pgAssistant 버전 2.8 이후의 새로운 기능

버전 2.8 이후로 pgAssistant는 크게 진화했습니다.

초기 목표는 스키마 설계(schema design), 인덱스(indexes), 유지 관리 통계(maintenance statistics), 설정(configuration), 워크로드 활동(workload activity) 등 여러 PostgreSQL 신호를 결합하여 더 높은 수준의 권장 사항을 제공할 수 있는 Global Advisor를 도입하는 것이었습니다.

몇 차례의 릴리스를 거치며, 이 실험적인 기능은 훨씬 더 성숙한 전문가 시스템(expert system)이 되었습니다. 동시에 Query Advisor, Index Advisor, 랭킹 엔진(ranking engine), 수집기 통합(collector integrations), 그리고 개발자용 유지 관리 뷰(maintenance views) 또한 개선되었습니다.

이 글은 pgAssistant 2.8.0에서 2.9.2 사이에 도입된 주요 변경 사항을 요약합니다.

Global Advisor: 실험에서 더 넓은 전문가 시스템으로

버전 2.8은 Global Advisor의 첫 번째 버전을 도입했습니다.

그 목적은 고립된 쿼리 분석을 넘어 데이터베이스 전체를 평가하는 것이었습니다. pgAssistant는 단일 실행 계획(execution plan)만 보는 대신, 다음과 같은 신호들을 상관 분석하기 시작했습니다:

• 데이터베이스 스키마 (database schemas)
• 외래 키 (foreign keys)
• 인덱스 (indexes)
• 테이블 통계 (table statistics)
• vacuum 및 analyze 활동 (vacuum and analyze activity)
• 설정 (configuration settings)
• 스토리지 사용량 (storage usage)

첫 번째 릴리스는 의도적으로 실험적이었으나, 어드바이저는 빠르게 확장되었습니다.

버전 2.8.2에 이르러 Global Advisor는 다음과 같은 기능과 함께 14가지의 서로 다른 권장 사항을 포함하게 되었습니다:

• 탐지된 결과의 요약
• 더 명확한 권장 사항 그룹화
• 생성된 모든 SQL 제안을 표시하는 버튼
• 모든 제안된 명령어를 한 번의 클릭으로 복사

이를 통해 어드바이저는 진단 도구이자 검토 체크리스트로서 더욱 유용해졌습니다.

더 나은 외래 키(foreign-key) 진단

외래 키는 초기 Global Advisor 출시 이후 개선된 첫 번째 영역 중 하나였습니다.

데이터 타입 불일치 (Data type mismatches)

이제 pgAssistant는 참조되는 컬럼과 데이터 타입이 다른 외래 키 컬럼을 감지합니다.

감지 로직은 오탐(false positives)을 줄이고 더 명확한 수정 가이드를 제공하도록 정교화되었습니다.

또한 권장 사항은 컬럼 유형을 변경할 때 다음과 같은 사항이 발생할 수 있음을 고려합니다:

• 테이블 재작성 (rewrite the table);
• 종속된 인덱스 재구축 (rebuild dependent indexes);
• ACCESS EXCLUSIVE 잠금 (lock) 획득;
• 추가 디스크 공간 필요;
• 유지 관리 시간 (maintenance window) 필요.

제안된 SQL에는 유형 변경과 그에 따른 ANALYZE가 모두 포함됩니다.

누락된 외래 키 인덱스 (Missing foreign-key indexes)

Advisor는 외래 키 (foreign-key) 컬럼에 누락된 유용한 인덱스도 감지합니다.

이제 로직이 자식 테이블과 참조 테이블의 크기를 모두 고려하여, 매우 작은 테이블에 대한 노이즈를 줄이고 인덱스가 성능을 개선할 가능성이 더 높은 다음의 경우를 우선시합니다:

• 조인 (joins);
• 조회 (lookups);
• 부모 테이블의 UPDATE 작업;
• 부모 테이블의 DELETE 작업.

더 강력해진 인덱스 진단 (Stronger index diagnostics)

인덱스 분석에 몇 가지 중요한 개선 사항이 적용되었습니다.

실행 계획으로부터의 더 나은 인덱스 권장 사항

Index Advisor는 실행 계획 (execution-plan) 노드가 이미 기존 인덱스를 사용하고 있는 경우에도 잠재적으로 더 나은 인덱스를 식별할 수 있습니다.

이전에는 인덱스 스캔 (index scan)의 존재가 더 선택도가 높거나 더 잘 정렬된 인덱스를 사용할 기회를 가릴 수 있었습니다. 새로운 로직은 기존 인덱스가 실제로 사용 가능한 최선의 액세스 경로 (access path)인지 평가합니다.

중복 및 불필요한 인덱스

Global Advisor는 이제 다음을 식별합니다:

• 엄격하게 중복된 인덱스 (strictly duplicate indexes);
• 사용되지 않는 인덱스 (unused indexes);
• 동일한 유니크 인덱스 (unique indexes)에 의해 완전히 커버되는 비유니크 인덱스;
• 인덱스 대 테이블 크기 비율이 비정상적으로 높은 테이블.

엄격하게 중복된 인덱스의 경우, pgAssistant는 단순히 객체 식별자 (object identifier)만으로 하나를 선택하는 대신 **가장 많이 사용되는 동일 인덱스 (most-used equivalent index)**를 유지합니다.

이는 동일한 인덱스 정의가 반드시 동일한 운영 가치를 갖는 것은 아니기 때문에 중요합니다.

더 안전한 미사용 인덱스 감지

idx_scan = 0인 인덱스가 자동으로 쓸모없는 것은 아닙니다.

카운터가 최근에 재설정되었을 수도 있고, 관찰된 워크로드 (workload)가 아직 대표성을 띠지 못할 수도 있습니다.

미사용 인덱스 체크는 이제 다음을 노출합니다:

• 데이터베이스 통계 재설정 타임스탬프 (database statistics reset timestamp);
• 관찰 기간 (observation age);
• 테이블 스캔 활동 (table scan activity);
• 테이블 쓰기 활동 (table write activity).

일반적인 미사용 인덱스 (unused index)는 다음 조건이 충족될 때만 보고됩니다:

• 최소 24시간 이상의 통계 데이터가 사용 가능할 때;
• 그리고 해당 테이블에 유의미한 활동이 있었을 때.

현재 임계값 (thresholds)은 최소한 다음 중 하나를 요구합니다:

• 100회의 테이블 스캔;
• 또는 1,000회의 행 수정 (row modifications).

구조적으로 중복된 인덱스 (structurally redundant indexes)의 경우, 중복성은 PostgreSQL 시스템 카탈로그 (system catalogs)를 통해 이미 증명될 수 있으므로, 통계의 신선도 (statistics age)는 권장 사항을 차단하기보다는 정보 제공 목적으로만 유지됩니다.

유효하지 않은 인덱스 처리 (Invalid index handling)

유효하지 않은 인덱스 (invalid-index) 권장 사항 또한 재설계되었습니다.

항상 삭제 후 수동으로 재생성할 것을 제안하는 대신, pgAssistant는 이제 다음 방식을 선호합니다:

REINDEX INDEX schema.index_name;

이 방식은 인덱스 정의를 보존하며, 제약 조건 (constraints)을 지원하는 인덱스의 경우 일반적으로 더 안전합니다.

또한 어드바이저 (advisor)는 실패한 동시 재빌드 (concurrent rebuilds)로 인해 남겨진 일반적인 아티팩트 (artifacts)를 인식합니다:

• _ccnew;
• _ccold.

이러한 케이스에는 특정 가이드가 제공됩니다. 왜냐하면 이러한 인덱스들을 맹목적으로 재빌드하거나 삭제할 경우, 중복 데이터가 남거나 잘못된 객체가 삭제될 수 있기 때문입니다.

PostgreSQL 릴리스 및 지원 확인 (PostgreSQL release and support checks)

버전 2.9.1에서는 Global Advisor에 PostgreSQL 릴리스 확인 기능이 도입되었습니다.

pgAssistant는 다음에서 공식 릴리스 정보를 가져옵니다:

https://www.postgresql.org/versions.json

이제 두 가지 서로 다른 상황을 감지할 수 있습니다.

더 최신의 마이너 릴리스가 사용 가능한 경우

예시:

PostgreSQL 17.6은 17 브랜치에서 사용 가능한 최신 마이너 릴리스가 아닙니다. 최신 버그 수정, 보안, 신뢰성 및 데이터 무결성 (data-integrity) 수정을 적용받으려면 2026-05-14에 출시된 PostgreSQL 17.10으로 업그레이드하십시오.

권장 사항에는 다음이 포함됩니다:

• 설치된 릴리스 버전;
• 동일한 메이저 브랜치 내의 최신 릴리스 버전;
• 해당 릴리스의 게시 날짜;
• 중간 릴리스 노트 (release notes)를 검토하라는 권장 사항.

PostgreSQL 브랜치의 지원이 종료된 경우

어드바이저(advisor)는 마지막 마이너 릴리스(minor release)가 이미 설치되어 있더라도 지원 종료(end-of-life) 버전을 보고합니다.

예시:

PostgreSQL 13.23은 13 브랜치의 최신 마이너 릴리스이지만, 이 브랜치는 더 이상 지원되지 않습니다. 지원되는 PostgreSQL 버전으로의 메이저 업그레이드(major upgrade)를 계획하십시오. 지원 종료(End-of-life) 날짜: 2025-11-13.

최신 마이너 릴리스를 설치한다고 해서 더 이상 사용되지 않는 구형 메이저 브랜치(major branch)에 대한 지원이 복구되는 것은 아니기 때문에 이러한 구분은 중요합니다.

더 정확한 유지 관리 권장 사항

불필요한 탐지 결과를 줄이기 위해 유지 관리 권장 사항이 개선되었습니다.

한 번도 Vacuum되지 않은 테이블

이전 구현 방식은 단지 vacuum 타임스탬프(timestamp)를 사용할 수 없다는 이유만으로 매우 작은 테이블을 보고할 수 있었습니다.

이제 규칙은 다음 요소들을 결합하여 더 강력한 근거를 요구합니다:

• 테이블 크기 (table size);
• 데드 튜플 (dead tuples)의 절대 수;
• 데드 튜플 비율 (dead tuple percentage);
• 기록된 vacuum 활동의 부재.

이를 통해 pgAssistant가 단 몇 개의 행(row)이나 데드 튜플만을 포함하는 미미한 테이블에 대해 유지 관리를 권장하는 것을 방지합니다.

오래된 통계 정보 (Stale statistics)

오래된 통계 정보(stale-statistics) 체크는 다음을 고려합니다:

• 마지막 수동 또는 자동 analyze의 경과 시간;
• 마지막 analyze 이후 수정된 데이터 양;
• 테이블 크기;
• 수정 비율 (modification ratio).

따라서 권장 사항은 단순히 타임스탬프에만 의존하는 것이 아니라, 워크로드(workload)와 테이블의 중요성을 기반으로 합니다.

Vacuum 권장 사항

버전 2.8.4부터 pgAssistant는 가장 최근의 관련 유지 관리 작업이 6일보다 오래된 경우에만 ANALYZE 또는 VACUUM을 권장합니다.

이를 통해 최근에 유지 관리된 테이블에 대해 반복적인 조언을 하는 것을 줄여줍니다.

Autovacuum 긴급도

전용 권장 사항을 통해 테이블이 유효한 autovacuum 임계값(threshold)을 초과했는지 여부를 계산합니다.

계산 시 다음을 고려합니다:

• 전역 autovacuum 설정 (global autovacuum settings);
• 테이블별 reloptions;
• 추정 행 수 (estimated row count);
• 데드 튜플 수 (dead tuple count);
• 구성된 최대 임계값 (maximum thresholds).

이는 단순한 데드 튜플 비율보다 더 의미 있는 신호를 제공합니다.

버전 인식 PostgreSQL 구성 체크

이제 구성 권장 사항(Configuration recommendations)이 설치된 PostgreSQL 버전을 고려합니다.

이는 메이저 릴리스(major releases) 간에 기본값(default values)이 변경되었기 때문에 필요합니다.

어드바이저(advisor)는 다음과 같은 설정들을 점검합니다:

• autovacuum;
• track_counts;
• track_activities;
• log_checkpoints;
• log_autovacuum_min_duration;
• checkpoint_completion_target;
• checkpoint_timeout.

예를 들어, checkpoint_completion_target은 이전 PostgreSQL 릴리스에서 더 낮은 기본값을 사용했던 반면, 로깅(logging) 기본값 또한 최신 버전에서 변경되었습니다.

이제 권장 사항 텍스트에는 해당 값이 해당 PostgreSQL 버전에서 역사적으로 정상적이었는지, 아니면 현대적인 기본값과 다른지를 설명하는 내용이 포함됩니다.

시퀀스 고갈 감지 (Sequence exhaustion detection)

버전 2.8.3에서는 최대값에 도달하고 있는 시퀀스(sequences)에 대한 권장 사항 기능이 도입되었습니다.

pgAssistant는 사용 가능한 시퀀스 범위 중 이미 소비된 비율을 계산하여 다음과 같이 보고합니다:

• 75% 초과 시 중간 수준(medium-level) 경고;
• 90% 초과 시 높은 수준(high-level) 경고.

이를 통해 시퀀스가 고갈되기 전에 향후 발생할 수 있는 삽입(insert) 실패를 식별할 수 있습니다.

더 나은 워크로드 우선순위 지정 (Better workload prioritization)

쿼리 순위 지정 알고리즘(query ranking algorithm)이 버전 2.8.4에서 개선되었습니다.

평균 실행 시간(mean execution time)이나 총 실행 시간(total execution time)만으로 쿼리를 정렬하면 종종 오해의 소지가 있는 우선순위가 생성됩니다.

한 번 실행된 쿼리는 느릴 수 있지만 전체적인 영향은 미미할 수 있는 반면, 적당히 느린 쿼리가 수천 번 실행되면 훨씬 더 많은 워크로드(workload)를 소비할 수 있습니다.

업데이트된 순위 지정 방식은 다음 요소들에 더 많은 가중치를 부여합니다:

• 실행 빈도 (execution frequency);
• 총 워크로드 영향 (total workload impact);
• 반복 가능성 (repeatability);
• 기술적 신호 (technical signals).

동시에, 영향력이 낮은 일회성 쿼리의 영향력은 줄였습니다.

그 결과, 다음과 같은 질문에 대해 더욱 실용적인 답변을 제공합니다:

어떤 PostgreSQL 쿼리를 가장 먼저 최적화해야 하는가?

AI 지원 쿼리 분석을 위한 더 풍부한 컨텍스트 (Richer context for AI-assisted query analysis)

이제 쿼리 분석 프롬프트(query-analysis prompt)가 pgAssistant에 의해 수집된 컬럼 통계(column statistics)로 더욱 풍부해졌습니다.

실행 계획(Execution plans)만으로는 정확한 권장 사항을 위한 충분한 컨텍스트를 제공하기 어려운 경우가 많습니다.

추가된 통계 정보는 AI가 다음과 같은 사항들을 추론하는 데 도움을 줍니다:

• 카디널리티 (cardinality);
• Null 비율 (null fractions);
• 공통 값 (common values);
• 데이터 분포 (data distributions);
• 선택도 (selectivity);
• 상관관계 (correlations).

이는 pgAssistant의 하이브리드 접근 방식을 강화합니다:

• 신뢰할 수 있는 기술적 진단을 위한 결정론적 전문가 시스템 규칙 (deterministic expert-system rules);
• 설명, 합성 및 컨텍스트 분석을 위한 AI.

테이블 정의 헬퍼 (Table Definition Helper) 재설계

테이블 정의 헬퍼 (Table Definition Helper) 인터페이스가 다음과 같은 정보를 더 명확하게 볼 수 있도록 재설계되었습니다:

• 테이블 크기 (table size);
• 인덱스 점유 공간 (index footprint);
• 행 추정치 (row estimates);
• 데드 튜플 (dead tuples);
• 예상 블로트 (estimated bloat);
• 스키마 정보 (schema information).

새로운 카드 기반 인터페이스에는 다음 기능이 포함됩니다:

• 요약 지표 (summary indicators);
• 타이핑 중 즉시 검색 (immediate search while typing);
• 클라이언트 측 필터 (client-side filters);
• 정렬 (sorting);
• 페이지네이션 (pagination);
• 시각적 심각도 수준 (visual severity levels).

이를 통해 서버 측 검색을 반복적으로 제출하지 않고도 대규모 데이터베이스를 더 쉽게 탐색할 수 있습니다.

새로운 테이블 상태 (Table Health) 뷰

버전 2.9.2에서는 DBA 코너(DBA Corner)에 새로운 테이블 상태 (Table Health) 페이지를 도입합니다.

그 위치에도 불구하고, 목표는 pgAssistant를 범용 DBA 콘솔로 만드는 것이 아닙니다.

목적은 여전히 개발자 지향적입니다:

문제를 DBA에게 에스컬레이션(escalating)하기 전에 개발자가 테이블의 상태를 이해할 수 있도록 돕는 것.

모든 스키마와 테이블에 대해, 이 뷰는 다음을 표시합니다:

• 테이블 크기 (table size);
• 인덱스 크기 (index size);
• 예상 행 수 (estimated row count);
• 데드 튜플 수 및 비율 (dead tuple count and percentage);
• 마지막 분석(analyze) 이후 수정된 튜플 (tuples modified since the last analyze);
• 업데이트 활동 (update activity);
• HOT 업데이트 비율 (HOT update percentage);
• 최근 수동 진공 (latest manual vacuum);
• 최근 자동 진공 (latest autovacuum);
• 최근 수동 분석 (latest manual analyze);
• 최근 자동 분석 (latest autoanalyze);
• 진공 경과 시간 (vacuum age);
• 분석 경과 시간 (analyze age);
• 데이터베이스 통계 초기화 타임스탬프 (database statistics reset timestamp).

테이블은 다음과 같은 상태를 사용하여 분류됩니다:

• HEALTHY;
• ANALYZE_DUE;
• NEVER_ANALYZED;
• HIGH_DEAD_TUPLES.

사용자는 즉시 테이블을 필터링, 검색, 정렬 및 조사할 수 있습니다.

또한 다음 명령을 실행할 수 있습니다:

ANALYZE schema.table;

또는:

VACUUM (ANALYZE) schema.table;

기존 SQL 실행 엔드포인트(endpoint)를 통해 수행되며, 정확한 명령과 실행 결과를 보여주는 확인 대화 상자가 표시됩니다.

Collector 및 Grafana 통합

2.9.0 버전에서는 다음 도구들이 사용하는 두 개의 API 엔드포인트(endpoint)가 추가되었습니다:

• pgAssistant Collector
• pgAssistant Grafana

이를 통해 pgAssistant의 탐지 결과(findings)를 시간에 따라 수집하고 여러 PostgreSQL 인스턴스에 걸쳐 시각화할 수 있습니다.

이 통합을 통해 다음과 같은 플릿 수준(fleet-level)의 뷰를 구축할 수 있습니다:

• 권장 사항의 변화(recommendation evolution);
• 데이터베이스 설계 문제;
• 유지보수 리스크;
• 수정된 탐지 결과(corrected findings);
• 환경 수준의 비교.

더 넓은 스키마 범위 지원

여러 Global Advisor 규칙들이 의도치 않게 다음과 같이 제한되어 있었습니다: