인덱스 및 성능 최적화

4️⃣ 인덱스 및 성능 최적화

1. PostgreSQL 인덱스 종류 및 활용

PostgreSQL은 다양한 인덱스 타입을 제공하며, 각 인덱스는 특정한 사용 사례에 최적화되어 있습니다.

• B-Tree 인덱스: 기본 인덱스로 대부분의 조회 연산에 적합
• Hash 인덱스: 동등 비교(=) 연산에 최적화
• GIN(Generalized Inverted Index): JSONB, Full-Text Search 등의 검색 최적화
• GiST(Generalized Search Tree): 공간 데이터 및 범위 검색에 유용
• BRIN(Block Range INdex): 매우 큰 테이블에서 성능 최적화를 위해 사용

📌 인덱스 생성 예제

-- B-Tree 인덱스 생성
CREATE INDEX idx_users_name ON users (name);

-- GIN 인덱스 생성 (JSONB 필드)
CREATE INDEX idx_users_data ON users USING GIN (data);

2. 실행 계획(Execution Plan) 분석

쿼리 성능을 최적화하려면 실행 계획을 분석하는 것이 중요합니다.

• EXPLAIN 명령어를 사용하여 쿼리 실행 계획 확인
• EXPLAIN ANALYZE를 사용하여 실제 실행된 쿼리의 비용 분석
• pg_stat_statements 확장 모듈을 활용하여 자주 실행되는 쿼리 분석

📌 실행 계획 분석 예제

EXPLAIN ANALYZE SELECT * FROM users WHERE name = 'Alice';

3. Vacuum 및 Autovacuum 튜닝

PostgreSQL은 MVCC를 사용하기 때문에 정기적인 VACUUM과 ANALYZE가 필요합니다.

• VACUUM: 테이블 내 삭제된 행을 정리하여 디스크 공간 확보
• ANALYZE: 통계를 수집하여 쿼리 최적화
• REINDEX: 인덱스 성능 저하 방지를 위해 재생성

📌 Autovacuum 튜닝 예제

ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 2000;
ALTER SYSTEM SET autovacuum_naptime = '30s';

4. 파티셔닝 및 샤딩 전략

PostgreSQL은 대용량 데이터를 효율적으로 관리하기 위해 파티셔닝 및 샤딩 기능을 제공합니다.

• Table Partitioning: 테이블을 여러 개의 작은 테이블로 분할하여 관리
• 샤딩 (Sharding): 여러 노드에 데이터를 분산하여 처리 성능 향상
• Citus 확장 모듈: 분산 데이터베이스 솔루션을 통한 샤딩 구현

📌 파티셔닝 예제

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    sale_date DATE NOT NULL,
    amount NUMERIC
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2024 PARTITION OF sales FOR VALUES FROM ('2024-01-01') TO ('2024-12-31');