0Pricing
SQL Academy · 강의

인덱스 유지 관리와 비대화

인덱스 비대화를 진단하고 REINDEX CONCURRENTLY로 재구성하며 사용하지 않는 인덱스를 안전하게 삭제해보세요.

인덱스 유지 관리와 비대화은(는) CoddyKit의 무료 SQL Academy 강의입니다. 이것은 4개 중 4번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 SQL Academy 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. SQL Academy 강의에는 총 4개의 강의가 포함되어 있습니다.

인덱스가 비대해지는 이유

PostgreSQL은 MVCC를 사용합니다. UPDATE는 새 행 버전을 기록하고, 기존 트랜잭션에서 볼 수 있도록 이전 버전을 남겨 둡니다. 두 버전에 대한 인덱스 항목이 모두 존재합니다. 시간이 지나면 다음과 같은 일이 발생합니다.

  • UPDATE가 많은 테이블에 사용되지 않는 인덱스 항목이 쌓임
  • 인덱스가 필요 이상으로 커짐
  • B-트리가 깊어지면서 조회가 느려짐

비대화 진단

비대화를 확인하는 쿼리는 간단하지 않습니다. 일반적으로 다음 도구를 사용합니다.

  • pgstattuple 확장 기능
  • pg_repack 보고서
  • check_postgres 또는 모니터링 도구에서 제공하는 비대화 쿼리
CREATE EXTENSION pgstattuple;
SELECT * FROM pgstatindex('orders_user_id_idx');

REINDEX

인덱스를 다시 만듭니다. 일반적인 형식은 ACCESS EXCLUSIVE 잠금을 사용하므로 운영 환경에서는 적합하지 않습니다.

REINDEX INDEX orders_user_id_idx;       -- blocks writes!

REINDEX CONCURRENTLY (PG 12 이상)

차단을 발생시키지 않는 방식입니다. 인덱스를 다시 만드는 동안에도 읽기와 쓰기 작업을 계속할 수 있습니다.

REINDEX INDEX CONCURRENTLY orders_user_id_idx;

사용되지 않는 인덱스

사용되지 않는 인덱스는 모든 쓰기 작업을 느리게 만들지만 읽기 작업은 전혀 빠르게 하지 못합니다. 다음과 같이 찾아보십시오.

SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

사용되지 않는 인덱스 삭제

삭제하십시오. 단, 먼저 모든 환경과 기간대에 걸쳐 사용 여부를 확인해야 합니다. 가끔 실행되는 보고서에 사용되는 인덱스는 대부분의 시간 동안 사용되지 않는 것처럼 보입니다.

DROP INDEX CONCURRENTLY old_unused_idx;

중복 인덱스

제약 조건과 수동 CREATE INDEX가 같은 인덱스를 각각 만드는 경우가 있습니다. pg_indexes에서 중복을 확인하고 불필요한 인덱스를 삭제하십시오.

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

인덱스 쓰기 증폭

모든 INSERT/UPDATE/DELETE는 관련된 모든 인덱스를 업데이트합니다. 자주 변경되는 테이블에 인덱스가 세 개 있으면 쓰기 비용이 3배가 됩니다. 효과가 있는 인덱스만 추가하십시오.

GIN 보류 목록

GIN 인덱스는 보류 목록에 업데이트를 모아 둡니다. 수동으로 비우거나 autovacuum에 맡길 수 있습니다.

SELECT gin_clean_pending_list('events_data_gin');

VACUUM으로 인덱스 항목 정리

VACUUM은 MVCC 강의에서 다룬 것처럼 힙 페이지에서 사용되지 않는 인덱스 항목을 제거합니다. autovacuum이 없으면 인덱스가 계속 커집니다.

인덱스 크기 모니터링

시간에 따른 인덱스 크기를 추적하십시오.

SELECT pg_size_pretty(pg_indexes_size('orders')) AS index_size,
       pg_size_pretty(pg_total_relation_size('orders')) AS total_size;

pg_repack: 온라인으로 테이블 다시 작성

비대화가 심한 경우 pg_repack은 전체 테이블을 잠그지 않고 테이블과 인덱스를 온라인으로 다시 작성합니다. OS 패키지와 PostgreSQL 확장 기능으로 설치하십시오.

복습

인덱스에는 관리가 필요합니다.

  • MVCC로 인한 비대화는 정상적인 현상이며 VACUUM과 REINDEX CONCURRENTLY로 관리할 수 있음
  • 사용되지 않는 인덱스 삭제
  • 중복 방지
  • 새 인덱스를 추가할 때마다 쓰기가 느려지므로 신중하게 결정

빠른 확인

쓰기 작업을 차단하지 않고 인덱스를 다시 만드는 PostgreSQL 명령은 무엇일까요?

자주 묻는 질문

“인덱스 유지 관리와 비대화” 강의는 무료인가요?

네 — “인덱스 유지 관리와 비대화” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 SQL Academy 강의 전체를 잠금 해제할 수 있습니다. SQL Academy 강의에는 총 4개의 강의가 포함되어 있습니다.

“인덱스 유지 관리와 비대화”에서 뭘 배우나요?

인덱스 비대화를 진단하고 REINDEX CONCURRENTLY로 재구성하며 사용하지 않는 인덱스를 안전하게 삭제해보세요. 브라우저에서 직접 실행하는 실습 코드로 SQL Academy을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

SQL Academy을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 SQL Academy은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 4번째 강의입니다.

“인덱스 유지 관리와 비대화” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 SQL Academy 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 SQL Academy 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

  1. B-tree와 Hash와 GiST와 GIN 인덱스
  2. 복합 인덱스와 열 순서
  3. 부분 인덱스와 표현식 인덱스
  4. 인덱스 유지 관리와 비대화
← SQL Academy(으)로 돌아가기