PostgreSQL 문제 해결 시 흔히 저지르는 5가지 실수와 이를 피하는 방법

대부분의 PostgreSQL 장애는 특이한 상황에서 시작되지 않습니다. 느린 엔드포인트, 차단된 세션의 대기열, 예상보다 빠르게 증가한 테이블, 또는 최악의 타이밍에 WAL 파티션에서 발생하는 디스크 경고로 시작됩니다. 어려운 점은 PostgreSQL에 인덱스, autovacuum, 메모리 설정, 잠금, WAL이 있다는 것을 아는 것이 아닙니다. 어려운 점은 지금 어떤 것이 문제인지 파악하고 다음 장애를 더 악화시키는 수정을 피하는 것입니다.

아래의 PostgreSQL 문제 해결 실수는 실제 운영 작업에서 가장 자주 목격되는 것들입니다. 단순히 "이 매개변수를 튜닝하세요"라는 팁이 아닙니다. 각각에는 증상, 함정, 그리고 프로덕션을 변경하기 전에 문제를 더 안전하게 추론하는 방법이 포함되어 있습니다.

실수 1: 인덱스 부족 및 오용

PostgreSQL 성능 저하의 가장 흔한 원인 중 하나는 부실한 인덱싱입니다. 많은 DBA가 자동으로 생성된 기본 키 인덱스에만 의존하여 특정 쿼리 패턴을 고려하지 못하고, 효율적인 인덱스 스캔 대신 값비싼 순차 스캔이 자주 발생하게 됩니다.

진단: 순차 스캔

쿼리 성능이 좋지 않을 때는 실행 계획부터 확인하십시오. 쿼리가 데이터를 변경하거나 오래 실행될 수 있는 경우 먼저 일반 EXPLAIN을 사용하십시오. 안전하게 실행할 수 있고 실제 타이밍과 I/O 동작이 필요할 때는 EXPLAIN (ANALYZE, BUFFERS)를 사용하십시오.

EXPLAIN ANALYZE
SELECT * FROM user_data WHERE last_login > '2023-10-01' AND status = 'active';

실수 피하기: 복합 인덱스 및 부분 인덱스

쿼리가 WHERE 절에서 여러 열을 사용하는 경우 복합 인덱스가 도움이 될 수 있지만, 열 순서는 쿼리 형태에 따라 달라집니다. 동등 조건 필터는 일반적으로 범위 조건 필터보다 앞에 위치해야 합니다. WHERE status = 'active' AND last_login > ...와 같은 쿼리의 경우 (last_login, status)보다 (status, last_login)에 대한 인덱스가 더 유용한 경우가 많습니다. PostgreSQL이 하나의 상태로 범위를 좁힌 다음 날짜 범위를 스캔할 수 있기 때문입니다. ORDER BY last_login DESC LIMIT 50의 경우 최적의 인덱스는 다를 수 있습니다.

또한 특정 조건을 충족할 때만 인덱싱이 필요한 열에 대해 부분 인덱스를 고려하십시오. 이렇게 하면 인덱스 크기가 줄어들고 인덱스 생성 및 유지 관리 속도가 빨라집니다.

-- 위 예제 쿼리에 대한 복합 인덱스 생성
CREATE INDEX idx_user_login_status ON user_data (status, last_login);

-- 활성 사용자만을 위한 부분 인덱스 생성
CREATE INDEX idx_active_users_email ON user_data (email) WHERE status = 'active';

idx_scan이 오늘 0이라고 해서 인덱스를 바로 삭제하지 마십시오. 통계는 재시작 및 수동 재설정 후에 재설정되며, 일부 인덱스는 드물지만 중요한 작업을 위해 존재합니다. 더 안전한 검토는 다음과 같습니다.

SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

인덱스가 크고 전체 비즈니스 주기 동안 사용되지 않으며 제약 조건을 지원하지 않는 경우 삭제 대상이 될 수 있습니다. 사용량이 많은 시스템에서는 DROP INDEX CONCURRENTLY를 사용하여 전체 작업 동안 일반 읽기 및 쓰기가 차단되지 않도록 하십시오.

실수 2: Autovacuum 데몬 무시

PostgreSQL은 MVCC(Multi-Version Concurrency Control)를 사용합니다. 업데이트와 삭제는 vacuum이 정리할 때까지 이전 행 버전을 남겨둡니다. Autovacuum은 선택적인 하우스키핑이 아닙니다. 이는 정상적인 데이터베이스 운영의 일부입니다. 죽은 튜플을 제거하고, autoanalyze를 통해 플래너 통계를 업데이트하며, 트랜잭션 ID 랩어라운드를 방지합니다.

진단: 과도한 블로트

Autovacuum을 무시하면 테이블 블로트가 발생하여 파일 시스템이 사용되지 않는 공간을 보유하게 되고 순차 스캔 속도가 크게 느려집니다. Autovacuum이 높은 쓰기 트래픽을 따라잡지 못하면 XID 소비가 가속화됩니다.

일반적인 증상: 높은 I/O 대기, 증가하는 테이블 파일, 오래된 행 추정치, 라이브 행 수는 대부분 안정적임에도 계속 증가하는 테이블 크기.

유용한 첫 번째 확인 사항:

SELECT schemaname, relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze,
       vacuum_count, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

실수 피하기: Autovacuum 튜닝

많은 팀이 기본값을 그대로 두었다가 하나 또는 두 개의 변동이 심한 테이블에 대해 기본값이 충분히 공격적이지 않다는 것을 발견합니다. 전체 클러스터를 시끄럽게 만들지 말고 해당 테이블을 직접 튜닝하십시오.

먼저 이해해야 할 설정은 다음과 같습니다.

autovacuum_vacuum_scale_factor: vacuum이 트리거되기 전에 변경되어야 하는 테이블의 비율입니다. 큰 테이블은 일반적으로 더 낮은 값이 필요합니다.
autovacuum_vacuum_threshold: 스케일 팩터 계산에 추가되는 고정 행 임계값입니다.
autovacuum_vacuum_cost_delay 및 autovacuum_vacuum_cost_limit: 제한 제어입니다. Autovacuum을 더 빠르게 만들면 I/O 압력이 증가할 수 있으므로 변경 후 시스템을 관찰하십시오.

postgresql.conf에서 전역적으로 또는 스토리지 매개변수를 사용하여 테이블별로 이러한 설정을 튜닝하여 autovacuum이 변동이 심한 테이블을 관리할 수 있을 만큼 적극적으로 실행되도록 하십시오.

ALTER TABLE high_churn_table SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 5000
);

함정은 성능 문제 중에 autovacuum이 나타났다고 해서 비활성화하는 것입니다. Autovacuum이 지속적으로 표시된다면 일반적으로 쓰기 변동을 따라잡으려고 한다는 의미입니다. 이를 autovacuum이 원인이라는 증거가 아니라 용량 및 튜닝 신호로 취급하십시오.

실수 3: `shared_buffers` 및 `work_mem` 딜레마

메모리 할당을 잘못 구성하는 것은 데이터베이스 I/O 성능에 직접적인 영향을 미치는 흔한 실수입니다. 두 가지 매개변수가 이 영역을 지배합니다: shared_buffers(데이터 블록 캐싱) 및 work_mem(세션 내 정렬 및 해시 작업에 사용되는 메모리).

진단: 높은 디스크 I/O 및 스필

워크로드에 비해 shared_buffers가 너무 작으면 PostgreSQL은 운영 체제 캐시와 스토리지에 더 많이 의존하게 됩니다. work_mem이 너무 작으면 정렬 및 해시 작업이 임시 파일을 디스크로 스필합니다. work_mem이 전역적으로 너무 크면 동시 쿼리가 급증할 때 메모리가 고갈될 수 있습니다.

디스크 스필을 확인하려면 EXPLAIN ANALYZE를 사용하십시오. 다음과 같은 줄을 찾으십시오.

Sort Method: external merge Disk: 1234kB

실수 피하기: 전략적 메모리 할당

1. `shared_buffers`

shared_buffers의 일반적인 시작점은 시스템 RAM의 약 25%이지만 보편적인 규칙은 아닙니다. 더 작은 인스턴스, 컨테이너 메모리 제한, 혼합 워크로드 및 관리형 데이터베이스 플랫폼은 모두 적절한 값을 변경할 수 있습니다. PostgreSQL은 또한 운영 체제 페이지 캐시의 이점을 얻으므로 모든 메모리를 shared_buffers에 할당하는 것은 일반적으로 실수입니다.

2. `work_mem`

이 매개변수는 세션별로 다릅니다. 일반적인 실수는 높은 전역 work_mem을 설정하는 것입니다. 이는 수백 개의 동시 연결과 곱해질 경우 시스템 RAM을 빠르게 고갈시켜 스와핑 및 충돌을 유발할 수 있습니다. 대신 보수적인 전역 기본값을 설정하고 복잡한 보고서나 배치 작업을 실행하는 특정 세션에 대해 SET work_mem을 사용하여 값을 늘리십시오.

# postgresql.conf 예시
shared_buffers = 12GB   # 총 48GB RAM 가정
work_mem = 4MB          # 보수적인 전역 기본값

보고 작업의 경우 해당 세션 또는 트랜잭션에 대해서만 설정하십시오.

BEGIN;
SET LOCAL work_mem = '128MB';
-- 보고 쿼리 실행
COMMIT;

단일 쿼리가 work_mem을 두 번 이상 사용할 수 있다는 점을 기억하십시오. 여러 정렬 또는 해시 노드가 있는 병렬 쿼리는 실제 사용 메모리를 배가시킬 수 있습니다.

실수 4: 장기 실행 쿼리 및 잠금 무시

제한이 없거나 잘못 작성된 쿼리 또는 애플리케이션 오류로 인해 연결이 몇 시간 동안 활성 상태를 유지하여 리소스를 소비하고, 더 나쁜 경우 다른 프로세스를 차단하는 트랜잭션 잠금을 유지할 수 있습니다. 이러한 쿼리를 모니터링하고 관리하지 않으면 주요 안정성 위험이 발생합니다.

진단: 활성 세션 모니터링

pg_stat_activity 뷰를 사용하여 장기 실행 쿼리, 실행 중인 특정 SQL 및 현재 상태(예: 잠금 대기, 활성)를 신속하게 식별하십시오.

SELECT pid, usename, client_addr, backend_start, state, query_start, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '5 minutes';

잠금 대기의 경우 차단 PID를 포함하십시오.

SELECT a.pid,
       a.usename,
       a.state,
       now() - a.query_start AS age,
       pg_blocking_pids(a.pid) AS blocked_by,
       a.query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY age DESC;

실수 피하기: 타임아웃 및 종료

세션 및 문 타임아웃을 구현하여 문제가 되는 프로세스가 심각한 피해를 입히기 전에 자동으로 종료되도록 하십시오.

statement_timeout: 단일 문이 취소되기 전에 실행될 수 있는 최대 시간입니다. 전역적으로 또는 애플리케이션 연결별로 설정해야 합니다.
lock_timeout: 문이 잠금을 포기하기 전에 잠금을 기다리는 최대 시간입니다.

즉각적인 완화를 위해 pg_stat_activity에서 식별된 프로세스 ID(PID)를 사용하여 문제가 되는 프로세스를 종료할 수 있습니다.

-- 전역 문 타임아웃을 10분(600000ms)으로 설정
ALTER SYSTEM SET statement_timeout = '600s';

-- PID를 사용하여 특정 쿼리 종료
SELECT pg_terminate_backend(12345);

쿼리가 단지 비용이 많이 드는 경우 먼저 pg_cancel_backend(pid)를 선호하십시오. 현재 문을 취소하지만 세션은 활성 상태로 둡니다. 세션이 트랜잭션에서 유휴 상태이거나, 잠금을 보유하고 있거나, 취소에 응답하지 않는 경우 pg_terminate_backend(pid)를 사용하십시오. 잘못된 백엔드를 종료하면 애플리케이션이 완료되기를 기대하는 작업이 롤백될 수 있으므로 조치를 취하기 전에 쿼리, 사용자, 클라이언트 주소 및 차단 관계를 캡처하십시오.

실수 5: 부실한 WAL 관리 및 디스크 용량 계획

PostgreSQL은 내구성과 복제를 위해 WAL(Write-Ahead Logging)에 의존합니다. WAL 세그먼트는 쓰기 트래픽이 많을 때 빠르게 누적됩니다. 일반적인 운영상의 실수는 WAL 아카이브와 관련된 디스크 공간 사용량을 모니터링하지 못하거나 적절한 스토리지 계획 없이 공격적인 WAL 매개변수를 설정하는 것입니다.

진단: 데이터베이스 중단

WAL 관리 부실의 가장 심각한 증상은 pg_wal을 보유한 파티션의 공간이 부족하여 데이터베이스가 중단되는 것입니다. 이는 아카이빙이 실패하거나, 대기 서버가 다운되거나, 더 이상 존재하지 않는 소비자를 위해 복제 슬롯이 WAL을 보유하고 있을 때 자주 발생합니다.

실수 피하기: 크기 조정 및 아카이빙

1. WAL 크기 제어

max_wal_size 매개변수는 체크포인트 목표이지 하드 디스크 할당량이 아닙니다. PostgreSQL은 아카이빙, 복제 또는 복구를 위해 WAL을 보유해야 할 때 이 값을 초과할 수 있습니다. 너무 낮게 설정하면 빈번한 체크포인트와 추가 I/O가 발생합니다. 더 높게 설정하면 체크포인트 압력을 완화할 수 있지만 여전히 디스크 모니터링과 아카이브 모니터링이 필요합니다.

# postgresql.conf 예시
# 부하가 높을 때 체크포인트 빈도를 줄이기 위해 증가
max_wal_size = 4GB 
min_wal_size = 512MB

2. 아카이브 전략

PITR(Point-in-Time Recovery) 또는 복제를 위해 WAL 아카이빙(archive_mode = on)이 활성화된 경우 아카이브 프로세스는 반드시 안정적이어야 합니다. 아카이브 대상(예: 네트워크 스토리지)에 접근할 수 없게 되면 PostgreSQL은 계속해서 세그먼트를 보유하여 결국 로컬 디스크를 가득 채웁니다. archive_command 실패가 지속될 경우 DBA에게 알리는 모니터링이 마련되어 있는지 확인하십시오.

또한 복제 슬롯을 확인하십시오.

SELECT slot_name, slot_type, active, restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC NULLS LAST;

보유 WAL이 증가하는 비활성 슬롯은 프라이머리를 빠르게 채우는 방법 중 하나입니다.

실용적인 문제 해결 순서

압박을 받을 때는 무작정 증상을 쫓지 않도록 고정된 순서를 사용하십시오.

디스크 공간, 특히 데이터 디렉토리, pg_wal 및 임시 파일 위치를 확인하십시오.
pg_stat_activity에서 활성 세션과 차단자를 확인하십시오.
EXPLAIN (ANALYZE, BUFFERS)를 사용하여 느린 쿼리 계획이 실제로 예상대로 작동하는지 확인하십시오.
테이블 변동, 죽은 튜플 및 autovacuum 기록을 확인하십시오.
WAL 아카이빙, 복제 지연 및 슬롯 보존을 확인하십시오.
한 번에 하나씩 변경하고 변경 전후 증거를 보관하십시오.

가장 큰 PostgreSQL 문제 해결 실수는 모든 장애를 튜닝 문제로 취급하는 것입니다. 때로는 올바른 수정이 인덱스인 경우도 있습니다. 때로는 타임아웃이 누락된 경우입니다. 때로는 복제 슬롯이 멈춘 경우입니다. 데이터베이스는 일반적으로 충분한 증거를 제공합니다. 중요한 것은 설정을 변경하기 전에 그 증거를 읽는 규율입니다.