읽기 및 쓰기 성능 최적화를 위한 `postgresql.conf` 매개변수 튜닝

PostgreSQL은 일반적으로 패키지 기본값으로 수용 가능한 성능을 제공하지만, 실제 트래픽이 발생하면 "수용 가능"이 느린 읽기, 급격한 쓰기 또는 무작위 지연 시간으로 변할 수 있습니다. postgresql.conf 파일은 기본 리소스 예산을 설정하는 곳입니다. PostgreSQL이 공유 캐시에 사용할 수 있는 메모리 양, 각 쿼리 작업이 디스크로 넘어가기 전에 사용할 수 있는 메모리 양, 체크포인트가 더티 페이지를 얼마나 적극적으로 쓰는지, 그리고 플래너가 기본 시스템에 대해 얻는 힌트를 설정합니다.

제가 가장 자주 보는 실수는 PostgreSQL 튜닝을 마법의 숫자 목록처럼 취급하는 것입니다. 누군가 shared_buffers = RAM의 25%를 복사하고, work_mem을 큰 값으로 설정하고, max_connections를 두 배로 늘린 다음 데이터베이스가 더 빨라지기를 바랍니다. 때로는 효과가 있습니다. 때로는 보고 작업 중에 스와핑이 시작되거나 체크포인트 중에 벽에 부딪힙니다.

더 안전한 방법은 증상에 따라 튜닝하는 것입니다. 작업 세트가 캐시되지 않아 읽기가 느린가요? 보고서가 정렬을 디스크로 유출하고 있나요? 체크포인트 중에 쓰기가 뭉쳐지고 있나요? 너무 많은 애플리케이션 연결이 메모리를 놓고 경쟁하고 있나요? 이 가이드는 일반적으로 먼저 중요한 매개변수를 안내하며, 맹목적으로 복사하지 않고 적용할 수 있는 예제를 제공합니다.

핵심 메모리 매개변수 이해

효율적인 메모리 관리는 고성능 데이터베이스 시스템에 가장 중요합니다. PostgreSQL은 다양한 메모리 영역을 활용하며, 가장 중요한 두 가지는 자주 액세스하는 데이터를 캐싱하기 위한 shared_buffers와 내부 쿼리 작업을 위한 work_mem입니다.

`shared_buffers`

shared_buffers는 튜닝해야 할 가장 중요한 메모리 매개변수 중 하나입니다. PostgreSQL이 데이터 블록을 캐싱하는 데 사용하는 전용 메모리 양을 정의합니다. 이러한 블록에는 테이블 데이터, 인덱스 데이터 및 시스템 카탈로그가 포함됩니다. 쿼리가 데이터를 요청하면 PostgreSQL은 먼저 shared_buffers를 확인합니다. 데이터가 거기에서 발견되면(캐시 적중) 디스크에서 읽어야 하는 것보다 훨씬 빠르게 검색됩니다.

성능에 미치는 영향

읽기 성능: shared_buffers 값이 클수록 캐시 적중 가능성이 높아져 읽기 중심 워크로드의 디스크 I/O가 크게 줄어듭니다. 이는 더 빠른 쿼리 응답으로 이어집니다.
쓰기 성능: shared_buffers는 "더티" 페이지(수정되었지만 아직 디스크에 기록되지 않은 데이터 블록)도 보유합니다. 버퍼가 클수록 더 많은 쓰기를 흡수하여 시스템이 쓰기를 더 적고 더 큰 쓰기로 일괄 처리할 수 있으므로 쓰기 처리량이 향상됩니다. 그러나 너무 크면 체크포인트 시간이 길어지고 체크포인트 중 I/O 스파이크가 증가할 수 있습니다.

튜닝 지침

시작점: 일반적인 권장 사항은 shared_buffers를 전체 물리적 RAM의 25% 로 설정하는 것입니다. 예를 들어 16GB RAM 서버에서 shared_buffers는 4GB가 됩니다.
대용량 RAM 시스템: 64GB 이상의 RAM이 있는 서버에서는 25%를 할당하는 것이 과도할 수 있습니다. PostgreSQL은 또한 운영 체제의 파일 시스템 캐시에 의존합니다. 특정 지점을 넘어서면 shared_buffers를 늘려도 OS 캐시가 나머지 캐싱을 효과적으로 처리할 수 있으므로 수익이 감소할 수 있습니다. 이러한 경우 15-20%면 충분할 수 있으며 OS 캐시나 work_mem에 더 많은 RAM을 할당할 수 있습니다.
모니터링: pg_stat_database에서 캐시 적중률을 주시하되, 하나의 백분율이 모든 것이 정상이라는 증거로 취급하지 마십시오. 높은 적중률은 몇 가지 매우 비싼 쿼리를 숨길 수 있으며, 낮은 비율은 대규모 테이블을 한 번 스캔하는 배치 작업의 경우 정상일 수 있습니다. 또한 체크포인트 동작과 디스크 대기 시간을 모니터링하십시오.

구성 예시

postgresql.conf에서 shared_buffers를 4GB로 설정하려면:

shared_buffers = 4GB

팁: shared_buffers를 변경한 후에는 변경 사항을 적용하려면 PostgreSQL 서비스를 다시 시작해야 합니다.

변경 후 실용적인 확인:

SELECT
    datname,
    blks_hit,
    blks_read,
    round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1');

shared_buffers를 늘렸는데도 애플리케이션이 여전히 디스크 읽기를 기다리고 있다면 문제는 쿼리 형태, 누락된 인덱스, 테이블 블로트 또는 메모리보다 큰 작업 세트일 수 있습니다. 더 많은 캐시는 더 나은 실행 계획을 대체할 수 없습니다.

`work_mem`

work_mem은 쿼리 작업(예: 정렬 또는 해시 테이블)이 임시 데이터를 디스크에 쓰기 전에 사용할 수 있는 최대 메모리 양을 지정합니다. 이 메모리는 세션당, 작업당 할당됩니다. 복잡한 쿼리에 여러 정렬 또는 해시 작업이 포함된 경우 단일 세션 내에서 work_mem을 여러 번 소비할 수 있습니다.

성능에 미치는 영향

복잡한 쿼리: work_mem은 ORDER BY, GROUP BY, DISTINCT, 해시 조인 및 구체화와 관련된 쿼리에 상당한 영향을 미칩니다. 정렬 또는 해시 작업이 work_mem 제한을 초과하면 PostgreSQL은 초과 데이터를 임시 디스크 파일로 유출하여 실행 속도가 훨씬 느려집니다.
동시성: work_mem은 작업별, 세션별로 할당되므로 높은 전역 work_mem 값과 많은 동시 복잡한 쿼리가 결합되면 사용 가능한 RAM이 빠르게 소진되어 스와핑 및 심각한 성능 저하가 발생할 수 있습니다.

튜닝 지침

과도한 전역 값 방지: 맹목적으로 work_mem을 매우 큰 값으로 전역 설정하지 마십시오. 대신 애플리케이션의 일반적인 동시성과 가장 리소스 집약적인 쿼리의 메모리 사용량을 고려하십시오.
디스크 유출 모니터링: 문제가 있는 쿼리에 EXPLAIN ANALYZE를 사용하십시오. Sort Method: external merge Disk: NkB 또는 HashAggregate batches: N (disk)와 같은 줄을 찾으십시오. 이는 work_mem이 충분하지 않아 데이터가 디스크로 유출되었음을 나타냅니다.
대상 튜닝: 특정 장기 실행 보고서 또는 배치 작업의 경우 전역적으로 설정하는 대신 쿼리를 실행하기 전에 세션 수준에서 work_mem을 설정하는 것을 고려하십시오. 이렇게 하면 다른 동시 세션에 영향을 주지 않고 해당 특정 쿼리에 더 높은 메모리 사용량을 허용할 수 있습니다.

구성 예시

postgresql.conf에서 work_mem을 전역적으로 16MB로 설정하려면:

work_mem = 16MB

특정 세션(예: psql 또는 애플리케이션 연결)에 대해 work_mem을 설정하려면:

SET work_mem = '256MB';
SELECT * FROM large_table ORDER BY some_column;

경고: work_mem을 늘릴 때는 주의하십시오. 100개의 동시 쿼리가 각각 1GB의 work_mem이 필요한 경우 100GB의 RAM이 필요합니다! 항상 스테이징 환경에서 변경 사항을 테스트하고 시스템의 메모리 사용량을 모니터링하십시오.

work_mem을 사용하는 더 현실적인 방법은 전역 값을 적당하게 유지한 다음 알려진 보고 세션에 대해서만 높이는 것입니다:

BEGIN;
SET LOCAL work_mem = '256MB';

SELECT customer_id, sum(total_amount)
FROM orders
WHERE created_at >= current_date - interval '90 days'
GROUP BY customer_id
ORDER BY sum(total_amount) DESC;

COMMIT;

이 패턴은 모든 웹 요청에 대해 전역 값을 높이는 것보다 안전합니다. 짧은 쿼리가 많은 웹 애플리케이션은 예측 가능한 메모리 사용이 필요합니다. 야간 보고서는 더 큰 쿼리당 예산을 감당할 수 있습니다.

체크포인트로 쓰기 성능 및 내구성 관리

체크포인트는 PostgreSQL에서 데이터 내구성을 보장하고 트랜잭션 로그(WAL - Write-Ahead Log)를 관리하기 위한 중요한 메커니즘입니다. 주기적으로 shared_buffers에서 수정된 데이터 블록을 디스크와 동기화하여 이전 변경 사항이 모두 영구 저장소에 기록된 지점을 표시합니다.

`checkpoint_timeout`

checkpoint_timeout은 자동 WAL 체크포인트 사이의 최대 시간을 정의합니다. 마지막 체크포인트 이후 생성된 WAL 세그먼트 양이 max_wal_size를 초과하는 경우에도 체크포인트가 발생합니다.

성능에 미치는 영향

빈번한 체크포인트(짧은 checkpoint_timeout): 더티 페이지가 디스크로 플러시됨에 따라 더 빈번한 I/O 스파이크가 발생합니다. 이는 충돌 후 복구 시간을 줄이지만(재생할 WAL 감소), 집중된 쓰기 활동으로 인해 활성 워크로드 성능에 부정적인 영향을 미칠 수 있습니다.
드문 체크포인트(긴 checkpoint_timeout): I/O 스파이크의 빈도를 줄여 정상 작동 중에 더 부드러운 성능을 제공합니다. 그러나 충돌 시 WAL에서 더 많은 데이터를 재생해야 할 수 있으므로 데이터베이스 복구 시간이 길어집니다. 또한 축적된 WAL 세그먼트를 저장하려면 더 큰 max_wal_size가 필요합니다.

튜닝 지침

균형: 목표는 원활한 지속적인 성능과 허용 가능한 복구 시간 사이의 균형을 찾는 것입니다. 많은 프로덕션 시스템이 5-15분 정도에서 시작한 다음 WAL 볼륨 및 복구 목표에 따라 조정합니다.
max_wal_size와의 상호 작용: 이 두 매개변수는 함께 작동합니다. checkpoint_timeout이 길지만 max_wal_size가 너무 작으면 checkpoint_timeout보다 max_wal_size에 의해 더 자주 체크포인트가 트리거됩니다. checkpoint_timeout이 기본 트리거가 되도록 max_wal_size를 충분히 크게 조정하십시오.
모니터링: pg_stat_bgwriter를 사용하여 checkpoints_timed 및 checkpoints_req 카운터를 관찰하십시오. checkpoint_timeout이 기본 트리거인 경우 checkpoints_timed가 checkpoints_req(WAL 크기 제한으로 인해 요청된 체크포인트)보다 훨씬 높아야 합니다.

구성 예시

postgresql.conf에서 checkpoint_timeout을 10분으로 설정하려면:

checkpoint_timeout = 10min
# 또한 그에 따라 max_wal_size를 조정하는 것을 고려하십시오
max_wal_size = 4GB # 예시, 워크로드에 따라 조정

모범 사례: max_wal_size보다는 checkpoint_timeout에 의해 체크포인트가 주로 트리거되도록 하는 것을 목표로 하십시오. 이는 더 예측 가능한 I/O 패턴을 제공합니다. max_wal_size가 자주 체크포인트를 트리거하는 경우 값을 늘리십시오.

다음으로 패턴을 확인하십시오:

SELECT
    checkpoints_timed,
    checkpoints_req,
    checkpoint_write_time,
    checkpoint_sync_time,
    buffers_checkpoint
FROM pg_stat_bgwriter;

checkpoints_req가 빠르게 증가하면 PostgreSQL은 타이머가 만료되었기 때문이 아니라 WAL이 max_wal_size를 초과하여 증가했기 때문에 체크포인트를 수행하는 것입니다. 이는 종종 쓰기 I/O의 버스트로 나타납니다. max_wal_size를 늘리면 워크로드를 부드럽게 할 수 있지만 더 많은 WAL을 재생해야 할 수 있으므로 충돌 복구 시간이 늘어날 수 있습니다.

확인할 가치가 있는 플래너 및 WAL 설정

세 가지 설정이 종종 큰 메모리 및 체크포인트 매개변수 옆에 있습니다.

effective_cache_size는 PostgreSQL이 할당하는 메모리가 아닙니다. PostgreSQL 공유 버퍼와 운영 체제 파일 캐시 전반에 걸쳐 사용 가능한 캐시 양에 대한 플래너 추정치입니다. 너무 낮게 설정하면 플래너가 읽기 비용이 많이 들 것이라고 가정하여 인덱스 스캔을 피할 수 있습니다. 전용 데이터베이스 서버에서 일반적인 시작점은 RAM의 큰 부분이지만 올바른 값은 호스트에서 실행되는 다른 항목에 따라 다릅니다.

effective_cache_size = 12GB

maintenance_work_mem은 CREATE INDEX, ALTER TABLE ADD FOREIGN KEY 및 VACUUM과 같은 유지 관리 작업에 영향을 미칩니다. 일반 쿼리 정렬에 work_mem과 같은 방식으로 사용되지 않습니다. 유지 관리 기간 동안 인덱스 빌드가 고통스러울 정도로 느린 경우 세션에 대해 이 값을 높이면 도움이 될 수 있습니다:

SET maintenance_work_mem = '1GB';
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);

wal_buffers는 WAL 레코드가 기록되기 전에 사용되는 메모리를 제어합니다. PostgreSQL이 자동으로 크기를 조정할 수 있으므로 기본값은 일반적으로 괜찮지만, 쓰기 집약적인 워크로드와 대규모 트랜잭션이 있는 경우 변경하기 전에 WAL 쓰기가 병목인지 확인하는 것이 좋습니다. 체크리스트에 나타난다고 해서 튜닝하지 마십시오.

다양한 워크로드에 대한 다양한 시작점

OLTP 웹 애플리케이션의 경우 우선 순위는 동시성에서 안정적인 대기 시간입니다. work_mem을 보수적으로 유지하고, 수천 개의 직접 연결을 허용하는 대신 연결 풀러를 사용하고, shared_buffers를 비난하기 전에 잠금 대기 및 잘못된 계획을 주시하십시오. 일반적인 문제는 다음과 같습니다. 릴리스에서 수백만 행에 걸쳐 ORDER BY created_at DESC가 있는 대시보드 쿼리를 추가하고, 쿼리가 디스크로 유출되고, 데이터베이스가 임시 파일 I/O를 수행하고 있기 때문에 갑자기 모든 요청이 느려집니다. 수정 사항은 더 큰 전역 work_mem이 아니라 인덱스 또는 더 좁은 쿼리일 수 있습니다.

분석 또는 보고 데이터베이스의 경우 대규모 정렬 및 해시 집계가 일반적입니다. 보고 역할에 대해 work_mem을 높이고, 대량 인덱스 작업을 위해 maintenance_work_mem을 늘리고, 더 오래 실행되는 쿼리를 허용할 수 있습니다. 위험은 동시성입니다. 10명의 분석가가 동시에 메모리 집약적인 보고서를 실행하면 하나의 성공적인 테스트 쿼리가 제안한 것보다 훨씬 더 많은 메모리를 소비할 수 있습니다.

쓰기 집약적인 시스템의 경우 체크포인트와 WAL이 더 중요합니다. 애플리케이션에 주기적인 쓰기 중단이 있는 경우 체크포인트와 일치하는지 확인하십시오. 또한 스토리지 대기 시간, WAL 디스크 포화, 자동 진공 활동 및 장기 트랜잭션이 정리를 방해하는지 확인하십시오. checkpoint_timeout만 늘리는 것으로는 평균 쓰기 볼륨을 따라잡을 수 없는 디스크를 고칠 수 없습니다.

간단한 튜닝 워크플로

현재 구성을 기록하는 것으로 시작하십시오:

SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN (
    'shared_buffers',
    'work_mem',
    'maintenance_work_mem',
    'effective_cache_size',
    'checkpoint_timeout',
    'max_wal_size',
    'wal_buffers',
    'max_connections'
)
ORDER BY name;

그런 다음 아무것도 변경하기 전에 증상을 포착하십시오. EXPLAIN (ANALYZE, BUFFERS)를 사용하여 느린 쿼리 계획을 하나 또는 두 개 저장하십시오. 유출이 의심되는 경우 임시 파일 로깅을 확인하십시오:

log_temp_files = 0

이 설정은 모든 임시 파일을 기록하므로 사용량이 많은 시스템에서는 주의해서 사용하거나 64MB와 같은 임계값으로 설정하십시오. 동일한 쿼리 형태에서 많은 대용량 임시 파일이 표시되면 쿼리를 튜닝하고, 인덱스를 추가하거나 해당 워크로드에 대해 work_mem을 높이십시오.

한 번에 하나씩 변경하십시오. 일부 설정은 다시 시작해야 하고, 일부는 다시 로드만 필요하며, 일부는 세션별로 설정할 수 있습니다. PostgreSQL은 어떤 것이 무엇인지 알려줍니다:

SELECT name, context
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'checkpoint_timeout', 'max_wal_size');

postmaster 컨텍스트는 다시 시작을 의미합니다. sighup은 다시 로드를 의미합니다. user는 세션 수준 변경이 가능함을 의미합니다.

일반적인 튜닝 팁 및 모범 사례

반복 튜닝: 작고 점진적인 변경으로 시작하십시오. 한 번에 하나의 매개변수를 변경하고 영향을 관찰한 다음 필요한 경우 추가로 조정하십시오. 튜닝은 일회성 작업이 아니라 지속적인 프로세스입니다.
모든 것을 모니터링하십시오: PostgreSQL의 내장 통계 뷰(pg_stat_database, pg_stat_bgwriter, pg_stat_activity), OS 수준 모니터링 도구(예: iostat, vmstat, top) 및 외부 모니터링 솔루션을 활용하여 CPU, 메모리, 디스크 I/O 및 쿼리 성능에 대한 데이터를 수집하십시오.
워크로드를 이해하십시오: 애플리케이션이 읽기 중심입니까, 쓰기 중심입니까? 복잡한 분석 쿼리를 수행합니까, 아니면 간단한 트랜잭션 작업을 수행합니까? 특정 워크로드 특성에 맞게 구성을 조정하십시오.
다른 매개변수 고려: shared_buffers, work_mem 및 checkpoint_timeout이 중요하지만 다른 많은 매개변수가 성능에 영향을 미칠 수 있습니다. 예를 들어 effective_cache_size(사용 가능한 OS 캐시에 대한 쿼리 플래너 힌트) 및 wal_buffers(플러시 전 WAL 레코드용 메모리)는 종종 이들과 함께 튜닝됩니다.
EXPLAIN ANALYZE 사용: 이 매우 귀중한 도구는 PostgreSQL이 쿼리를 실행하는 방법을 이해하고, 병목 현상을 식별하며, work_mem이 충분하지 않은지 여부를 밝힐 수 있습니다.

가장 좋은 PostgreSQL 튜닝 작업은 좋은 의미에서 지루합니다. 측정하고, 하나의 설정을 변경하고, 다시 측정하고, 롤백 경로를 유지하십시오. shared_buffers, work_mem 및 체크포인트 설정은 실제 차이를 만들 수 있지만 쿼리 계획, 인덱스, 자동 진공, 연결 수 및 스토리지와 함께 작동합니다. 이러한 부분이 건강하지 않으면 구성만으로는 데이터베이스를 구할 수 없습니다.

읽기 및 쓰기 성능 최적화를 위한 postgresql.conf 매개변수 튜닝

핵심 메모리 매개변수 이해

shared_buffers

성능에 미치는 영향

튜닝 지침

구성 예시

work_mem

성능에 미치는 영향

튜닝 지침

구성 예시

체크포인트로 쓰기 성능 및 내구성 관리

checkpoint_timeout

성능에 미치는 영향

튜닝 지침

구성 예시

확인할 가치가 있는 플래너 및 WAL 설정

다양한 워크로드에 대한 다양한 시작점

간단한 튜닝 워크플로

일반적인 튜닝 팁 및 모범 사례

읽기 및 쓰기 성능 최적화를 위한 `postgresql.conf` 매개변수 튜닝

`shared_buffers`

`work_mem`

`checkpoint_timeout`