PostgreSQL에서 인덱스를 삭제하거나 바꿔야 할 때가 몇 가지 있어:
- 인덱스가 너무 많을 때: 인덱스를 너무 많이 만들어서 더 이상 안 쓰면,
INSERT,UPDATE,DELETE같은 쓰기 작업이 느려질 수 있어. - 인덱스가 깨졌을 때: 가끔 시스템 장애나 PostgreSQL이 제대로 종료 안 됐을 때 인덱스가 깨질 수도 있어.
- 최적화: 쿼리에 더 잘 맞는 인덱스 타입이 있다는 걸 알게 되면 기존 인덱스를 바꾸고 싶을 수 있지.
- 테이블 구조 변경: 테이블에 컬럼을 추가하거나 삭제하면, 그 컬럼에 의존하는 인덱스가 더 이상 쓸모없어질 수 있어.
이제 인덱스를 관리할 때 쓰는 DROP INDEX랑 REINDEX 명령어를 어떻게 쓰는지 알아보자.
DROP INDEX로 인덱스 삭제하기
DROP INDEX 명령어는 데이터베이스에서 인덱스를 삭제할 때 써. 기본 문법은 이래:
DROP INDEX [ IF EXISTS ] index_name [ CASCADE ];
좀 더 자세히 보면:
IF EXISTS: 지정한 이름의 인덱스가 없을 때 에러를 막아줘. PostgreSQL은 그냥 경고만 보여줘.index_name: 삭제하려는 인덱스 이름이야.CASCADE: 인덱스랑 연결된 모든 객체도 같이 삭제해. 근데 인덱스는 보통 의존성이 없어서 잘 안 써.
예제 1: 인덱스 간단히 삭제하기
예를 들어 students 테이블에 email 필드에 인덱스가 있다고 해보자. 이제 이 인덱스가 필요 없으면 이렇게 삭제할 수 있어:
DROP INDEX idx_students_email;
여기서 idx_students_email은 인덱스 이름이야. 이 쿼리를 실행하면 인덱스가 데이터베이스에서 사라져.
예제 2: 인덱스가 있는지 확인하고 삭제하기
인덱스가 있는지 확실하지 않으면 IF EXISTS 옵션을 써봐:
DROP INDEX IF EXISTS idx_students_email;
인덱스가 없으면 PostgreSQL이 에러를 안 내고 경고만 보여줄 거야.
예제 3: 의존 객체까지 같이 삭제하기 (CASCADE)
만약 인덱스가 어떤 제약조건이랑 연결돼 있다고 해보자. 예를 들어 UNIQUE 제약조건 때문에 자동으로 만들어진 유니크 인덱스가 있을 수 있어. 이런 인덱스를 삭제하려면 CASCADE를 꼭 써야 해:
DROP INDEX idx_students_email CASCADE;
이 방법은 신중하게 써야 해! 뭔가를 무턱대고 CASCADE로 지우면 나중에 골치 아플 수 있으니까 조심하자.
REINDEX로 인덱스 복구하기
REINDEX 명령어는 인덱스가 깨지거나 오래돼서 성능이 떨어질 때 복구하는 데 써. 파일 시스템 에러, DB 장애, 혹은 그냥 오래 써서 문제가 생길 때 쓸 수 있어.
기본 문법
REINDEX { INDEX | TABLE | SCHEMA | DATABASE } name;
옵션을 좀 더 보면:
INDEX: 특정 인덱스만 복구해.TABLE: 지정한 테이블의 모든 인덱스를 복구해.SCHEMA: 지정한 스키마의 모든 테이블 인덱스를 복구해.DATABASE: 현재 데이터베이스의 모든 인덱스를 복구해.
예제 1: 특정 인덱스 복구하기
idx_students_email 인덱스가 느려졌다고 느껴지면 이렇게 복구할 수 있어:
REINDEX INDEX idx_students_email;
예제 2: 테이블의 모든 인덱스 복구하기
students 테이블의 인덱스가 다 느려진 것 같으면 이렇게 복구해:
REINDEX TABLE students;
예제 3: 데이터베이스 전체 인덱스 복구하기
시스템 장애가 났을 때 데이터베이스 전체 인덱스가 깨졌을 수도 있어. 이럴 땐 이렇게 복구해:
REINDEX DATABASE university;
참고: 이 명령어는 슈퍼유저 권한이 필요해.
DROP INDEX랑 REINDEX 쓸 때 팁
특히 자동화 스크립트 쓸 때는 IF EXISTS를 꼭 써서 에러를 피하자.
인덱스를 지우기 전에 진짜 안 쓰는지 꼭 확인해봐. 인덱스가 사용되는지 확인하려면 이런 쿼리를 써봐:
SELECT *
FROM pg_stat_user_indexes
WHERE indexrelname = 'idx_students_email';
CASCADE 옵션은 진짜 조심해서 써! 의존된 제약조건이나 객체가 데이터 무결성에 중요할 수 있어.
정기적으로 REINDEX를 써서 데이터베이스를 관리하자. 특히 자주 바뀌는 테이블이나 데이터가 많은 경우에 유용해.
어떤 에러가 날 수 있을까?
인덱스를 삭제하거나 바꿀 때 흔히 만나는 에러가 있어.
없는 인덱스를 삭제하려고 할 때. IF EXISTS를 안 쓰면 PostgreSQL이 에러를 내:
ERROR: index "idx_nonexistent" does not exist
시스템 인덱스 삭제 시도. 실수로 시스템 인덱스를 삭제하려고 하면 큰일 나. 예를 들어, 기본키 같은 중요한 컬럼은 인덱스가 연결돼 있는데, 이런 건 직접 삭제 못 하고 ALTER TABLE DROP CONSTRAINT로 제약조건부터 지워야 해.
테이블 락. DROP INDEX나 REINDEX 작업 중에 테이블이 락 걸릴 수 있어. 특히 동시에 쿼리가 실행 중이면 더 그렇지. 락이 부담되면 REINDEX 대신 CONCURRENTLY 옵션으로 인덱스를 새로 만들어보는 것도 방법이야.
실전 프로젝트에서 활용하기
쿼리 최적화: 더 이상 안 쓰는 인덱스는 삭제해서 DB 리소스를 아끼자.
인덱스 청소: 개발하다 보면 실험용으로 만든 "쓰레기" 인덱스가 생길 수 있어. 필요 없는 건 정기적으로 지우자.
성능 유지: REINDEX로 인덱스를 복구해서 계속 빠르고 정확하게 동작하게 하자.
이제 이런 도구들로 인덱스를 만들기만 하는 게 아니라, PostgreSQL에서 인덱스를 효율적으로 관리할 수 있어. 이게 데이터베이스 최적화와 성능 유지에 진짜 중요하니까, 인덱스 관리 잘 하자!
GO TO FULL VERSION