시스템 사양별 PostgreSQL 튜닝 & 설정 가이드

PostgreSQL 리눅스 시스템 기반 튜닝 가이드

이 가이드는 리눅스 서버의 하드웨어 사양(메모리, CPU, 디스크)을 확인하고, 그 결과에 맞춰 PostgreSQL 성능을 최적화하기 위한 단계별 가이드입니다.

🚀 1단계: 내 서버 사양 확인하기 (터미널 명령어)

튜닝을 하려면 먼저 내 차(서버)의 엔진(CPU)과 트렁크(메모리)가 얼마나 큰지 알아야 합니다. 아래 명령어들을 터미널에 입력해서 사양을 확인하세요.

1-1. 메모리(RAM) 확인

bash

free -h

결과 확인: Total 항목을 보세요 (예: 16Gi, 32Gi 등).
중요: PostgreSQL 성능의 80%는 메모리 설정에서 결정됩니다.

1-2. CPU 코어 수 확인

bash

nproc
# 또는
lscpu | grep "CPU(s):"

결과 확인: 숫자 (예: 4, 8, 16).
용도: 병렬 처리(Parallel Query) 설정에 사용됩니다.

1-3. 디스크 종류 확인 (SSD vs HDD)

PostgreSQL은 디스크 I/O 속도에 매우 민감합니다. 저장 장치가 HDD인지 SSD(또는 NVMe) 인지에 따라 파라미터가 완전히 달라집니다.

방법 1: lsblk 명령어 (가장 추천)

bash

lsblk -d -o name,rota

결과 해석 (ROTA 컬럼 확인): sda, sdb, sdc 등이 있다면 HDD, 없으면 SSD
- 1: HDD (회전판이 있음) 🐢 → random_page_cost = 4.0 (기본값)
- 0: SSD (회전판이 없음) 🚀 → random_page_cost = 1.1 (필수 변경)

방법 2: 모델명으로 직접 확인
명확하지 않다면 아래 명령어로 모델명을 검색해보세요.

bash

cat /sys/block/*/device/model
# 또는
sudo lshw -class disk

1-4. OS 정보 확인 (CentOS, Ubuntu 등)

bash

cat /etc/os-release

1-5. (추천) 자동 진단 스크립트 실행

위 과정을 한 번에 수행하고, 추천 설정값까지 바로 보고 싶다면 아래 스크립트를 실행하세요.

bash

#!/bin/bash

# Colors
RED='\033[0;31m'
GREEN='\033[0;32m'
BLUE='\033[0;34m'
YELLOW='\033[1;33m'
CYAN='\033[0;36m'
NC='\033[0m' # No Color
BOLD='\033[1m'

print_header() {
    clear
    echo -e "${CYAN}================================================================${NC}"
    echo -e "${BOLD}       🐘 PostgreSQL System Tuning Wizard for Linux 🐧       ${NC}"
    echo -e "${CYAN}================================================================${NC}"
    echo ""
}

print_header

# 1. OS Check
echo -e "${YELLOW}[1/5] Checking OS Information...${NC}"
if [ -f /etc/os-release ]; then
    . /etc/os-release
    OS_NAME=$NAME
    echo -e "  ✅ OS: ${GREEN}${OS_NAME}${NC}"
else
    OS_NAME=$(uname -s)
    echo -e "  ✅ OS: ${GREEN}${OS_NAME}${NC}"
fi
echo ""

# 2. CPU Check
echo -e "${YELLOW}[2/5] Checking CPU Information...${NC}"
CPU_CORES=$(nproc)
echo -e "  ✅ CPU Cores: ${GREEN}${CPU_CORES} Core(s)${NC}"
echo ""

# 3. Memory Check
echo -e "${YELLOW}[3/5] Checking Memory Information...${NC}"
# Get Total Memory in KB
MEM_KB=$(grep MemTotal /proc/meminfo | awk '{print $2}')
# Convert to GB for display
MEM_GB=$(awk "BEGIN {printf \"%.1f\", $MEM_KB/1024/1024}")
# Convert to MB for calculation
MEM_MB=$(awk "BEGIN {printf \"%d\", $MEM_KB/1024}")

echo -e "  ✅ Total Memory: ${GREEN}${MEM_GB} GB${NC} (${MEM_MB} MB)"
echo ""

# 4. Disk Check (Guessing Root Partition)
echo -e "${YELLOW}[4/5] Checking Disk Type (Root Partition)...${NC}"
# Find the device for /
ROOT_DEVICE=$(df / | tail -1 | awk '{print $1}' | sed 's/[0-9]*//g')
ROOT_DISK_NAME=$(basename $ROOT_DEVICE)

# Check rotation (0=SSD, 1=HDD)
if [ -f "/sys/block/$ROOT_DISK_NAME/queue/rotational" ]; then
    IS_ROTATING=$(cat /sys/block/$ROOT_DISK_NAME/queue/rotational)
else
    # Fallback attempt
    IS_ROTATING=0 # Assume SSD if unknown in modern cloud
fi

if [ "$IS_ROTATING" -eq 0 ]; then
    DISK_TYPE="SSD"
    DISK_ICON="🚀"
    RANDOM_PAGE_COST=1.1
    IO_CONCURRENCY=200
else
    DISK_TYPE="HDD"
    DISK_ICON="🐢"
    RANDOM_PAGE_COST=4.0
    IO_CONCURRENCY=2
fi
echo -e "  ✅ Disk Type: ${GREEN}${DISK_TYPE} ${DISK_ICON}${NC} (Detected on $ROOT_DISK_NAME)"
echo ""

# 5. Calculate Parameters
echo -e "${YELLOW}[5/5] Calculating Optimal Parameters...${NC}"
echo ""

# Calculations
MAX_CONNECTIONS=200

# Shared Buffers: 25% of RAM
SHARED_BUFFERS_MB=$(awk "BEGIN {printf \"%d\", $MEM_MB * 0.25}")
# Effective Cache Size: 75% of RAM
EFFECTIVE_CACHE_MB=$(awk "BEGIN {printf \"%d\", $MEM_MB * 0.75}")
# Maintenance Work Mem: 5% of RAM (Cap at 2GB)
MAINT_WORK_MEM_MB=$(awk "BEGIN {printf \"%d\", $MEM_MB * 0.05}")
if [ "$MAINT_WORK_MEM_MB" -gt 2048 ]; then MAINT_WORK_MEM_MB=2048; fi
# Work Mem: (RAM - Shared_Buffers) / (max_connections * 3)
WORK_MEM_MB=$(awk "BEGIN {printf \"%d\", ($MEM_MB - $SHARED_BUFFERS_MB) / ($MAX_CONNECTIONS * 3)}")
# Minimum safety for work_mem
if [ "$WORK_MEM_MB" -lt 4 ]; then WORK_MEM_MB=4; fi

# Parallel Workers
MAX_WORKER_PROCESSES=$CPU_CORES
MAX_PARALLEL_WORKERS=$CPU_CORES
MAX_PARALLEL_WORKERS_PER_GATHER=$(awk "BEGIN {printf \"%d\", $CPU_CORES / 2}")
if [ "$MAX_PARALLEL_WORKERS_PER_GATHER" -lt 1 ]; then MAX_PARALLEL_WORKERS_PER_GATHER=1; fi


echo -e "${CYAN}================================================================${NC}"
echo -e "${BOLD}                     🎯 RECOMMENDED CONFIGURATION                ${NC}"
echo -e "${CYAN}================================================================${NC}"
echo -e "Add these lines to the bottom of your ${BOLD}postgresql.conf${NC}:"
echo ""

# Output Config
echo -e "# --- CONNECTIONS ---"
echo -e "max_connections = ${GREEN}${MAX_CONNECTIONS}${NC}"
echo ""
echo -e "# --- MEMORY ---"
echo -e "shared_buffers = ${GREEN}${SHARED_BUFFERS_MB}MB${NC}"
echo -e "effective_cache_size = ${GREEN}${EFFECTIVE_CACHE_MB}MB${NC}"
echo -e "maintenance_work_mem = ${GREEN}${MAINT_WORK_MEM_MB}MB${NC}"
echo -e "work_mem = ${GREEN}${WORK_MEM_MB}MB${NC}"
echo ""
echo -e "# --- DISK & WAL ---"
echo -e "random_page_cost = ${GREEN}${RANDOM_PAGE_COST}${NC}  # Based on ${DISK_TYPE}"
echo -e "effective_io_concurrency = ${GREEN}${IO_CONCURRENCY}${NC}"
echo -e "checkpoint_completion_target = ${GREEN}0.9${NC}"
echo -e "wal_buffers = ${GREEN}16MB${NC}"
echo ""
echo -e "# --- PARALLEL WORKERS ---"
echo -e "max_worker_processes = ${GREEN}${MAX_WORKER_PROCESSES}${NC}"
echo -e "max_parallel_workers = ${GREEN}${MAX_PARALLEL_WORKERS}${NC}"
echo -e "max_parallel_workers_per_gather = ${GREEN}${MAX_PARALLEL_WORKERS_PER_GATHER}${NC}"
echo ""
echo -e "${CYAN}================================================================${NC}"
echo -e "${YELLOW}⚠️  NOTE: Restart PostgreSQL after applying these changes!${NC}"
echo -e "${CYAN}================================================================${NC}"

기능: OS, CPU, RAM, Disk를 자동으로 탐지하고 postgresql.conf에 복사해 넣을 수 있는 설정값을 출력해 줍니다.

🛠 2단계: 핵심 파라미터 계산 공식

확인한 사양을 바탕으로 postgresql.conf에 입력할 값을 계산해봅시다.

A. 메모리 관련 설정 (가장 중요 ⭐)

파라미터 이름	권장 값 계산 공식	설명
`max_connections`	`200` ~ `300`	동시 접속자 수입니다. 웹 서비스라면 보통 수천 개를 잡기보다 PGBouncer 같은 풀링을 앞에 두고 DB는 적정선(200~300)을 유지하는 게 성능에 좋습니다.
`shared_buffers`	전체 RAM의 25%	DB가 데이터를 캐싱하는 전용 메모리 영역입니다. <br> (예: 16GB RAM -> `4GB`) <br> 주의: 아무리 높아도 8GB~16GB 이상으로는 잘 설정하지 않습니다.
`effective_cache_size`	전체 RAM의 75%	OS가 파일 캐싱에 쓸 수 있는 전체 메모리 추정치입니다. 실제 할당되는 메모리가 아니라, 옵티마이저가 인덱스 사용 여부를 결정하는 힌트입니다. <br> (예: 16GB RAM -> `12GB`)
`work_mem`	*공식: (RAM - shared_buffers) / (max_connections 3)**	정렬(Sort)이나 해시 조인 작업에 쓰이는 메모리입니다. <br> 추천값: 기본 `4MB` ~ `16MB`. <br> 너무 높게 잡으면 동시 접속 시 OOM(Out of Memory) 에러가 발생할 수 있습니다.
`maintenance_work_mem`	전체 RAM의 5% (최대 2GB)	인덱스 생성, 백업(Dump), VACUUM 작업용 메모리입니다. <br> (예: 1GB 이상 추천)

B. 디스크(SSD/HDD) 및 쓰기 관련 설정

파라미터 이름	HDD 권장값	SSD 권장값 (추천)	설명
`random_page_cost`	`4.0`	`1.1`	디스크에서 임의의 데이터를 읽어오는 비용입니다. SSD는 빠르므로 `1.1`로 낮춰주면 인덱스 스캔을 더 적극적으로 사용합니다.
`effective_io_concurrency`	`2`	`200`	여러 디스크 작업을 동시에 얼마나 수행할지 설정합니다. SSD는 동시성이 뛰어납니다.
`checkpoint_completion_target`	`0.5`	`0.9`	체크포인트(디스크 저장) 작업을 천천히 부드럽게 분산시켜 I/O 스파이크를 방지합니다.
`wal_buffers`	`16MB`	`16MB`	트랜잭션 로그(WAL)를 버퍼링하는 메모리입니다. 기본값 `-1`도 좋지만 `16MB`면 충분합니다.

C. 병렬 처리 (CPU 코어 활용)

CPU 코어가 4개 이상일 때 효과적입니다.

max_worker_processes: CPU 코어 수 (예: 8)
max_parallel_workers_per_gather: CPU 코어 수 / 2 (예: 4)
max_parallel_workers: CPU 코어 수 (예: 8)

D. (별첨) 웹/DB 공용 서버 (예: EC2 t3.medium 등)

Web Server(Node.js, Java)와 DB가 한 서버에 같이 있다면, DB가 메모리를 다 쓰면 서버가 다운됩니다. 이때는 훨씬 보수적으로 잡아야 합니다.

파라미터 이름	공용 서버 권장값	이유
`shared_buffers`	전체 RAM의 10% ~ 15%	웹 서버도 메모리를 많이 쓰므로 DB 할당량을 대폭 줄여야 합니다.
`effective_cache_size`	전체 RAM의 50%	OS 파일 캐시도 웹 서버 로그/파일 등에 의해 밀려날 수 있습니다.
`work_mem`	`2MB` ~ `4MB`	동시 접속이 몰릴 때 메모리 부족(OOM)을 막기 위해 최소한으로 설정합니다.
`max_connections`	`50` ~ `100`	사양이 낮다면 연결 수를 제한하고, 애플리케이션에서 커넥션 풀을 잘 관리해야 합니다.
`maintenance_work_mem`	`256MB`	백그라운드 작업이 과도하게 메모리를 점유하지 않도록 제한합니다.

📝 3단계: 실전 적용 예시 (Copy & Paste)

가장 흔한 RAM 16GB / 4 vCPU / SSD 서버 기준 예시 설정입니다.

파일 경로: /etc/postgresql/{버전}/main/postgresql.conf 또는 /var/lib/pgsql/{버전}/data/postgresql.conf

ini

# --- CONNECTION ---
max_connections = 200

# --- MEMORY ---
# 16GB RAM 기준
shared_buffers = 4GB
effective_cache_size = 12GB
maintenance_work_mem = 1GB
work_mem = 16MB  # 복잡한 쿼리가 많으면 늘리되, 연결 수 고려 필요

# --- DISK (SSD 최적화) ---
random_page_cost = 1.1        # SSD 필수 설정
effective_io_concurrency = 200 # SSD 필수 설정
seq_page_cost = 1.0

# --- CHECKPOINT & WAL ---
wal_level = replica
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9

# --- PARALLEL QUERY (쿼리 속도 향상) ---
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4

🔧 4단계: 설정 적용 방법

설정 파일 열기 (vi 또는 nano 에디터 사용)

bash
sudo nano /etc/postgresql/16/data/postgresql.conf # 경로는 설치 방식에 따라 다를 수 있습니다. # 못 찾겠다면: sudo find / -name postgresql.conf
값 수정하기
- 위에서 계산한 값들을 찾아 수정하거나, 파일 맨 아래에 추가하면 덮어씌워집니다.(기본겂은 그대로 두고 맨 아래에 추가하면 됩니다.)
서버 재시작 (필수)
- 메모리 관련 설정은 DB를 재시작해야 적용됩니다.
bash
# Ubuntu/Debian sudo systemctl restart postgresql # CentOS/RHEL sudo systemctl restart postgresql-16
적용 확인
- DB 접속 후 쿼리로 확인:
sql
SHOW shared_buffers; SHOW work_mem;

💡 전문가의 꿀팁 (Advanced)

Huge Pages (리눅스 메모리 최적화)
- shared_buffers가 크다(8GB 이상)면 리눅스 커널의 Huge Pages 기능을 켜서 CPU 부하를 줄일 수 있습니다.
PGBouncer 사용
- max_connections를 1000 이상으로 늘리는 것보다, 앞단에 PGBouncer 같은 커넥션 풀러를 두는 것이 성능상 훨씬 유리합니다.
PGTune 웹사이트 활용
- 계산이 복잡하다면 PGTune 사이트에 접속해서 사양만 입력하면 설정값을 자동으로 만들어줍니다. 이 가이드도 해당 로직을 기반으로 합니다.

시스템 사양별 PostgreSQL 튜닝 & 설정 가이드

중년개발자

PostgreSQL 리눅스 시스템 기반 튜닝 가이드

🚀 1단계: 내 서버 사양 확인하기 (터미널 명령어)

1-1. 메모리(RAM) 확인

1-2. CPU 코어 수 확인

1-3. 디스크 종류 확인 (SSD vs HDD)

1-4. OS 정보 확인 (CentOS, Ubuntu 등)

1-5. (추천) 자동 진단 스크립트 실행

🛠 2단계: 핵심 파라미터 계산 공식

A. 메모리 관련 설정 (가장 중요 ⭐)

B. 디스크(SSD/HDD) 및 쓰기 관련 설정

C. 병렬 처리 (CPU 코어 활용)

D. (별첨) 웹/DB 공용 서버 (예: EC2 t3.medium 등)

📝 3단계: 실전 적용 예시 (Copy & Paste)

🔧 4단계: 설정 적용 방법

💡 전문가의 꿀팁 (Advanced)

댓글 0