DevOps 지식 허브
DevOps 지식 허브

가장 일반적인 PostgreSQL 성능 병목 현상 7가지 및 해결책

가장 일반적인 7가지 병목 현상을 해결하여 최적의 PostgreSQL 성능을 확보하세요. 이 가이드는 쿼리 최적화, 인덱싱 전략, 효과적인 VACUUM 실행, 리소스 관리, 구성 튜닝, 연결 풀링 및 잠금 경합 해결을 위한 실행 가능한 통찰력과 실용적인 해결책을 제공합니다. 성능 문제를 식별하고 수정 사항을 구현하여 PostgreSQL 데이터베이스가 효율적이고 안정적으로 실행되도록 보장하는 방법을 배우십시오.

46 조회수

PostgreSQL 성능 병목 현상 및 해결책 상위 7가지

PostgreSQL은 강력하고 오픈 소스인 관계형 데이터베이스로, 견고성, 확장성, 그리고 SQL 표준 준수로 잘 알려져 있습니다. 하지만 다른 복잡한 시스템과 마찬가지로, 애플리케이션 응답성과 사용자 경험을 저해하는 성능 병목 현상에 직면할 수 있습니다. 이러한 문제를 식별하고 해결하는 것은 최적의 데이터베이스 효율성을 유지하는 데 매우 중요합니다. 이 글에서는 PostgreSQL에서 흔히 발생하는 상위 7가지 성능 병목 현상에 대해 자세히 알아보고, 이를 극복하기 위한 실용적이고 실행 가능한 해결책을 제공합니다.

이러한 일반적인 문제점을 이해하면 데이터베이스 관리자와 개발자는 PostgreSQL 인스턴스를 사전에 튜닝할 수 있습니다. 인덱싱, 쿼리 실행, 리소스 활용 및 구성과 관련된 문제를 해결함으로써 데이터베이스의 속도와 확장성을 크게 향상시켜, 과도한 부하 상황에서도 애플리케이션이 원활하게 실행되도록 할 수 있습니다.

1. 비효율적인 쿼리 실행 계획

느린 성능의 가장 빈번한 원인 중 하나는 제대로 최적화되지 않은 SQL 쿼리입니다. PostgreSQL의 쿼리 플래너는 정교하지만, 특히 복잡한 쿼리나 오래된 통계와 함께 사용될 때 비효율적인 실행 계획을 생성할 수 있습니다.

병목 현상 식별

PostgreSQL이 쿼리를 어떻게 실행하는지 이해하려면 EXPLAIN과 EXPLAIN ANALYZE를 사용하십시오. EXPLAIN은 계획된 실행을 보여주며, EXPLAIN ANALYZE는 실제로 쿼리를 실행하고 실제 시간 및 행 수를 제공합니다.

-- To view the execution plan:
EXPLAIN SELECT * FROM users WHERE email LIKE 'john.doe%';

-- To view the plan and actual execution details:
EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE 'john.doe%';

다음 사항을 확인하십시오:
* 인덱스가 유용할 수 있는 큰 테이블에서의 Sequential Scans.
* 실제 행 수에 비해 높은 비용 또는 높은 행 예상치.
* Hash Join 또는 Merge Join이 더 적절할 때의 Nested Loop joins.

해결책

  • 적절한 인덱스 추가: WHERE, JOIN, ORDER BY, GROUP BY 절에 사용되는 열에 인덱스가 있는지 확인하십시오. 선행 와일드카드(%)가 있는 LIKE 절의 경우 B-tree 인덱스는 종종 비효율적이므로, 전체 텍스트 검색 또는 트라이그램(trigram) 인덱스를 고려하십시오.
  • 쿼리 다시 작성: 때때로 더 간단하거나 다르게 구조화된 쿼리가 더 나은 계획으로 이어질 수 있습니다.
  • 통계 업데이트: PostgreSQL은 통계를 사용하여 술어(predicate)의 선택성을 추정합니다. 오래된 통계는 플래너를 잘못된 방향으로 이끌 수 있습니다.
    sql ANALYZE table_name; -- Or for all tables: ANALYZE;
  • 쿼리 플래너 매개변수 조정: work_mem과 random_page_cost는 플래너의 선택에 영향을 미칠 수 있지만, 신중하게 조정해야 합니다.

2. 누락되거나 비효율적인 인덱스

인덱스는 빠른 데이터 검색에 매우 중요합니다. 인덱스가 없으면 PostgreSQL은 일치하는 데이터를 찾기 위해 테이블의 모든 행을 읽는 Sequential Scan을 수행해야 하며, 이는 대규모 테이블에서는 매우 느립니다.

병목 현상 식별

  • EXPLAIN ANALYZE 출력: 쿼리 계획에서 큰 테이블의 Seq Scan을 찾으십시오.
  • 데이터베이스 모니터링 도구: pg_stat_user_tables와 같은 도구는 테이블 스캔 수를 보여줄 수 있습니다.

해결책

  • B-tree 인덱스 생성: B-tree 인덱스는 가장 일반적인 유형이며 같음(=), 범위(<, >, <=, >=) 및 LIKE (선행 와일드카드 없음) 연산에 적합합니다.
    sql CREATE INDEX idx_users_email ON users (email);
  • 다른 인덱스 유형 사용:
    • GIN/GiST: 전체 텍스트 검색, JSONB 작업 및 기하학적 데이터 유형에 사용됩니다.
    • Hash 인덱스: 같음(equality) 검사에 사용됩니다 (B-tree 개선으로 인해 최신 PostgreSQL 버전에서는 덜 일반적입니다).
    • BRIN (Block Range Index): 물리적으로 상관 관계가 있는 데이터가 있는 매우 큰 테이블에 사용됩니다.
  • 부분 인덱스(Partial Indexes): 행의 하위 집합만 인덱싱하며, 쿼리가 특정 조건을 자주 대상으로 할 때 유용합니다.
    sql CREATE INDEX idx_orders_pending ON orders (order_date) WHERE status = 'pending';
  • 표현식 인덱스(Expression Indexes): 함수 또는 표현식의 결과를 인덱싱합니다.
    sql CREATE INDEX idx_users_lower_email ON users (lower(email));
  • 과도한 인덱스 피하기: 너무 많은 인덱스는 쓰기 작업(INSERT, UPDATE, DELETE)을 느리게 하고 디스크 공간을 소모할 수 있습니다.

3. 과도한 Autovacuum 활동 또는 고갈(Starvation)

PostgreSQL은 MVCC(Multi-Version Concurrency Control) 시스템을 사용하는데, 이는 UPDATE 및 DELETE 작업이 즉시 행을 제거하지 않는다는 것을 의미합니다. 대신, 이러한 행을 사용되지 않는 것으로 표시합니다. VACUUM은 이 공간을 회수하고 트랜잭션 ID 랩어라운드(wraparound)를 방지합니다. Autovacuum은 이 과정을 자동화합니다.

병목 현상 식별

  • 높은 CPU/IO 부하: Autovacuum은 리소스를 많이 소모할 수 있습니다.
  • 테이블 블로트(Table bloat): 실제 데이터 크기 또는 예상 행 수와 pg_class.relpages, pg_class.reltuples의 큰 불일치로 나타납니다.
  • pg_stat_activity: 장시간 실행되는 autovacuum worker 프로세스를 찾으십시오.
  • pg_stat_user_tables: n_dead_tup(사용되지 않는 튜플 수) 및 last_autovacuum/last_autoanalyze 시간을 모니터링하십시오.

해결책

  • Autovacuum 매개변수 튜닝: postgresql.conf 또는 테이블별 설정을 조정하십시오.

    • autovacuum_vacuum_threshold: VACUUM을 트리거하는 최소 사용되지 않는 튜플 수.
    • autovacuum_vacuum_scale_factor: VACUUM을 고려할 테이블 크기의 비율.
    • autovacuum_analyze_threshold 및 autovacuum_analyze_scale_factor: ANALYZE에 대한 유사한 매개변수.
    • autovacuum_max_workers: 병렬 Autovacuum 워커의 수.
    • autovacuum_work_mem: 각 워커에 사용 가능한 메모리.

    테이블별 설정 예시:
    sql ALTER TABLE large_table SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_analyze_scale_factor = 0.02);
    * 수동 VACUUM: 즉각적인 공간 회수를 위해 또는 Autovacuum이 따라가지 못할 때 사용합니다.
    sql VACUUM (VERBOSE, ANALYZE) table_name;
    VACUUM FULL은 테이블을 잠그고 전체 테이블을 다시 작성하여 매우 방해가 될 수 있으므로, 절대적으로 필요한 경우에만 사용하십시오.
    * shared_buffers 증가: 더 효과적인 캐싱은 IO를 줄이고 VACUUM 속도를 높일 수 있습니다.
    * FREEZE_MIN_AGE 및 VACUUM_MAX_AGE 모니터링: 트랜잭션 ID 에이징(aging)을 이해하는 것은 랩어라운드(wraparound) 방지에 중요합니다.

4. 불충분한 하드웨어 리소스 (CPU, RAM, IOPS)

PostgreSQL의 성능은 기본 하드웨어에 직접적으로 연결되어 있습니다. 불충분한 CPU, RAM 또는 느린 디스크 I/O는 상당한 병목 현상을 유발할 수 있습니다.

병목 현상 식별

  • 시스템 모니터링 도구: Linux의 top, htop, iostat, vmstat; Windows의 Performance Monitor.
  • pg_stat_activity: 잠금을 기다리는 쿼리(wait_event_type = 'IO', 'LWLock' 등)를 찾으십시오.
  • 높은 CPU 사용률: 지속적으로 100%에 근접.
  • 높은 디스크 I/O 대기 시간: 디스크 작업을 기다리는 데 많은 시간을 소비하는 시스템.
  • 낮은 사용 가능 메모리 / 높은 스왑 사용량: RAM이 불충분함을 나타냅니다.

해결책

  • CPU: 특히 동시 워크로드에 충분한 코어가 사용 가능한지 확인하십시오. PostgreSQL은 병렬 쿼리 실행(최신 버전에서) 및 백그라운드 프로세스를 위해 여러 코어를 효과적으로 활용합니다.
  • RAM (shared_buffers, work_mem):
    • shared_buffers: 데이터 블록을 위한 캐시. 일반적인 권장 사항은 시스템 RAM의 25%이지만, 워크로드에 따라 튜닝하십시오.
    • work_mem: 정렬, 해싱 및 기타 중간 작업에 사용됩니다. work_mem이 불충분하면 디스크로의 스필(spills)을 강제합니다.
  • 디스크 I/O:
    • SSD 사용: 데이터베이스 워크로드에 HDD보다 훨씬 빠릅니다.
    • RAID 구성: 읽기/쓰기 성능을 위해 최적화하십시오 (예: RAID 10).
    • 별도의 WAL 드라이브: Write-Ahead Log (WAL)를 별도의 빠른 드라이브에 두면 쓰기 성능을 향상시킬 수 있습니다.
  • 네트워크: 특히 분산 환경에서 클라이언트-서버 통신을 위한 충분한 대역폭과 낮은 지연 시간을 확보하십시오.

5. 제대로 구성되지 않은 postgresql.conf

PostgreSQL의 postgresql.conf 파일에는 동작을 제어하는 수백 가지 매개변수가 포함되어 있습니다. 기본 설정은 종종 보수적이며 특정 워크로드나 하드웨어에 최적화되어 있지 않습니다.

병목 현상 식별

  • 전반적인 느려짐: 전반적으로 느린 쿼리 시간.
  • 과도한 디스크 I/O: 사용 가능한 RAM에 비해.
  • 메모리 사용량: 메모리 압력 징후를 보이는 시스템.
  • 성능 튜닝 가이드 참조: 일반적인 최적 값을 이해하십시오.

해결책

고려해야 할 주요 매개변수:

  • shared_buffers: (위에서 언급했듯이) 데이터 블록을 위한 캐시. 시스템 RAM의 약 25%로 시작하십시오.
  • work_mem: 정렬/해싱을 위한 메모리. 디스크 스필(disk spills)을 보여주는 EXPLAIN ANALYZE 출력에 따라 튜닝하십시오.
  • maintenance_work_mem: VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY를 위한 메모리. 더 큰 값은 이러한 작업의 속도를 높입니다.
  • effective_cache_size: 플래너가 OS와 PostgreSQL 자체에 의해 캐싱에 사용 가능한 메모리 양을 추정하는 데 도움을 줍니다.
  • wal_buffers: WAL 쓰기를 위한 버퍼. 쓰기 부하가 높으면 늘리십시오.
  • checkpoint_completion_target: 체크포인트 쓰기를 시간에 걸쳐 분산하여 I/O 스파이크를 줄입니다.
  • max_connections: 적절하게 설정하십시오. 너무 높으면 리소스를 소진할 수 있습니다.
  • log_statement: 디버깅에 유용하지만, ALL 문을 로깅하는 것은 성능에 영향을 미칠 수 있습니다.

팁: pgtune과 같은 도구를 사용하여 하드웨어에 기반한 초기 권장 사항을 얻으십시오. 변경 사항을 프로덕션에 적용하기 전에 항상 스테이징 환경에서 테스트하십시오.

6. 연결 풀링(Connection Pooling) 문제

새로운 데이터베이스 연결을 설정하는 것은 비용이 많이 드는 작업입니다. 빈번하고 짧은 수명의 데이터베이스 상호 작용을 하는 애플리케이션에서는 연결을 반복적으로 열고 닫는 것이 상당한 성능 병목 현상이 될 수 있습니다.

병목 현상 식별

  • 높은 연결 수: pg_stat_activity는 매우 많은 수의 연결을 보여주며, 그 중 다수가 유휴 상태입니다.
  • 느린 애플리케이션 시작/응답 시간: 데이터베이스 연결이 자주 이루어질 때.
  • 서버 리소스 고갈: 연결 관리로 인한 높은 CPU 또는 메모리 사용량.

해결책

  • 연결 풀링 구현: PgBouncer 또는 Odyssey와 같은 연결 풀러(connection pooler)를 사용하십시오. 이러한 도구는 열린 데이터베이스 연결 풀을 유지하고 들어오는 클라이언트 요청에 대해 이를 재사용합니다.
    • PgBouncer: 가볍고 고성능의 연결 풀러입니다. 트랜잭션, 세션 또는 문(statement) 풀링 모드에서 작동할 수 있습니다.
    • Odyssey: SCRAM-SHA-256과 같은 프로토콜을 지원하는 더 현대적이고 기능이 풍부한 연결 풀러입니다.
  • 풀러 적절하게 구성: 애플리케이션 요구 사항 및 데이터베이스 용량에 따라 풀 크기, 시간 초과 및 풀링 모드를 튜닝하십시오.
  • 애플리케이션 측 풀링: 일부 애플리케이션 프레임워크는 내장된 연결 풀링 기능을 제공합니다. 이러한 기능이 올바르게 구성되었는지 확인하십시오.

7. 잠금 경합(Lock Contention)

여러 트랜잭션이 동시에 동일한 데이터에 액세스하고 수정하려고 할 때, 충돌하는 잠금을 획득하면 서로를 기다려야 할 수 있습니다. 과도한 잠금 경합은 애플리케이션을 매우 느리게 만들 수 있습니다.

병목 현상 식별

  • pg_stat_activity: wait_event_type이 Lock인 행을 찾으십시오.
  • 애플리케이션 성능 저하: 특정 작업이 극도로 느려집니다.
  • 교착 상태(Deadlocks): 서로를 무기한으로 기다리는 트랜잭션.
  • 장기 실행 트랜잭션: 장기간 잠금을 유지합니다.

해결책

  • 트랜잭션 최적화: 트랜잭션을 짧고 간결하게 유지하십시오. 가능한 한 빨리 커밋하거나 롤백하십시오.
  • 애플리케이션 로직 검토: 잠재적인 경쟁 조건 또는 비효율적인 잠금 패턴을 식별하십시오.
  • 적절한 잠금 수준 사용: PostgreSQL은 다양한 잠금 수준(예: ACCESS EXCLUSIVE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE)을 제공합니다. 필요한 최소한의 제한적인 잠금을 이해하고 사용하십시오.
  • SELECT ... FOR UPDATE / SELECT ... FOR NO KEY UPDATE: 트랜잭션이 완료되기 전에 다른 트랜잭션이 행을 변경하는 것을 방지하기 위해 수정을 위해 행을 잠가야 할 때 이들을 신중하게 사용하십시오.
  • 정기적인 VACUUM: 앞에서 언급했듯이, VACUUM은 사용되지 않는 튜플을 정리하는 데 도움을 주며, 이는 때때로 장시간 VACUUM 작업을 방지하여 잠금 경합을 간접적으로 줄일 수 있습니다.
  • pg_locks 확인: pg_locks를 쿼리하여 어떤 프로세스가 다른 프로세스를 차단하고 있는지 확인하십시오.
    sql SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS current_statement_in_blocking_process FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.granted;

결론

PostgreSQL 성능 최적화는 신중한 쿼리 설계, 전략적 인덱싱, 부지런한 유지 관리, 적절한 구성 및 견고한 하드웨어의 조합을 요구하는 지속적인 과정입니다. 비효율적인 쿼리, 누락된 인덱스, Autovacuum 문제, 리소스 제약, 잘못된 구성, 연결 풀링 제한 및 잠금 경합이라는 상위 7가지 일반적인 병목 현상을 체계적으로 식별하고 해결함으로써 데이터베이스의 응답성, 처리량 및 전반적인 안정성을 크게 향상시킬 수 있습니다. 데이터베이스 성능을 정기적으로 모니터링하고 이러한 해결책을 사전에 적용하면 PostgreSQL 인스턴스가 애플리케이션을 위한 강력하고 신뢰할 수 있는 기반으로 유지될 것입니다.