DevOps 도구 및 베스트 프랙티스 마스터 - DevOps 지식 허브 DevOps 도구 및 베스트 프랙티스 마스터 - DevOps 지식 허브

PostgreSQL 성능 및 보안을 위한 상위 10가지 모범 사례

더 빠른 쿼리, 안전한 액세스, 더 나은 유지 관리 및 복구 가능한 백업을 위한 실용적인 PostgreSQL 모범 사례입니다.

PostgreSQL 성능 및 보안을 위한 상위 10가지 모범 사례

PostgreSQL 모범 사례는 데이터베이스가 실제 프로덕션 트래픽을 처리하기 시작할 때 중요합니다. 올바르게 설정하면 쿼리를 예측 가능하게 유지하고, 데이터를 보호하며, 문제 발생 시 복구 경로를 제공합니다.

이 10가지 체크리스트를 새로운 PostgreSQL 서버나 속도가 느려지기 시작한 기존 시스템에 대한 실용적인 검토 목록으로 사용하세요.

1. 인덱스 최적화 및 EXPLAIN ANALYZE 이해

인덱스는 데이터 검색 속도를 높이는 데 중요하지만, 잘못 선택되거나 과도한 인덱스는 쓰기 작업 중 성능을 저하시킬 수 있습니다. 다양한 인덱스 유형(B-tree, GIN, GiST, BRIN 등)을 언제, 어떻게 사용해야 하는지 이해하는 것이 가장 중요합니다.

항상 EXPLAIN ANALYZE를 사용하여 PostgreSQL이 쿼리를 실행하는 방식을 이해하세요. 각 단계의 실행 시간을 포함한 쿼리 계획에 대한 자세한 정보를 제공하여 병목 현상과 인덱스 최적화 기회를 식별하는 데 도움을 줍니다.

실용 예제: EXPLAIN ANALYZE 사용

EXPLAIN ANALYZE
SELECT customer_name, order_date
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date > '2023-01-01'
ORDER BY order_date DESC;

출력 결과를 분석하면 o.order_date 또는 c.customer_id(아직 기본 키가 아닌 경우)에 인덱스가 유용한지 알 수 있습니다.

팁

pg_stat_statements(활성화된 경우)를 사용하여 느린 쿼리를 정기적으로 검토하고 EXPLAIN ANALYZE를 적용하세요.

2. 쿼리 최적화 및 스키마 효과적 설계

인덱싱 외에도 효율적인 쿼리 작성과 신중한 스키마 설계는 성능에 큰 영향을 미칩니다. 프로덕션 코드에서 SELECT *를 피하고 필요한 열만 선택하세요. 적절한 WHERE 절을 사용하여 데이터를 조기에 필터링하고 조인 유형을 이해하세요. 데이터 중복을 줄이기 위해 데이터베이스 스키마를 정규화하되, 특정 읽기 중심 시나리오에서는 역정규화가 유용할 수 있으므로 실용적으로 접근하세요.

쿼리 모범 사례

  • 조인이 더 나은 경우 서브쿼리 피하기: 일반적으로 데이터 결합 시 서브쿼리보다 JOIN 연산이 더 효율적입니다.
  • ORDER BY와 함께 LIMIT 사용: 페이지 매김 또는 상위 N개 레코드 검색 시 ORDER BY를 LIMIT과 함께 사용하고 적절한 인덱스가 있는지 확인하세요.
  • 올바른 데이터 유형 선택: 더 작고 정확한 데이터 유형(예: 범위가 허용하는 경우 BIGINT 대신 SMALLINT)을 사용하면 저장 공간을 줄이고 성능을 향상시킬 수 있습니다.

3. 최적 유지 관리를 위한 Autovacuum 구성

PostgreSQL은 MVCC(다중 버전 동시성 제어) 모델을 사용하므로 UPDATE 및 DELETE 작업이 이전 데이터 버전을 즉시 제거하지 않습니다. 이러한 "데드 튜플"은 시간이 지남에 따라 누적되어 테이블 블로트와 성능 저하를 초래합니다. VACUUM과 ANALYZE는 각각 데드 튜플 정리와 통계 업데이트에 중요합니다.

AUTOVACUUM은 이러한 작업을 자동화하는 PostgreSQL의 내장 프로세스입니다. postgresql.conf에서 autovacuum 매개변수를 적절히 구성하는 것이 중요합니다.

주요 autovacuum 매개변수

  • autovacuum = on (기본값)
  • autovacuum_vacuum_scale_factor (기본값: 0.2, 즉 테이블 크기의 20%)
  • autovacuum_vacuum_threshold (기본값: 50)
  • autovacuum_analyze_scale_factor (기본값: 0.1)
  • autovacuum_analyze_threshold (기본값: 50)

매우 바쁜 테이블의 경우 임계값이나 스케일 팩터를 낮추어 조정해야 할 수 있습니다.

명령 예시

autovacuum 활동을 확인하려면:

SELECT * FROM pg_stat_activity WHERE backend_type = 'autovacuum worker';

4. 연결 풀링 구현

새 데이터베이스 연결을 설정하는 것은 CPU와 메모리 측면에서 비용이 많이 드는 작업입니다. 수명이 짧은 연결이 많거나 동시 사용자가 많은 애플리케이션의 경우 이 오버헤드가 성능에 큰 영향을 미칠 수 있습니다. PgBouncer 또는 Pgpool-II와 같은 연결 풀러는 애플리케이션과 PostgreSQL 사이에 위치하여 열린 연결 풀을 유지하고 필요에 따라 재사용합니다.

이를 통해 연결 설정 오버헤드를 줄이고, 동시 연결을 더 효율적으로 관리하며, 로드 밸런싱 기능도 제공할 수 있습니다.

연결 풀링을 사용하는 이유

  • 연결 설정/해제 오버헤드 감소
  • 데이터베이스에 대한 총 연결 수를 제한하여 리소스 고갈 방지
  • 애플리케이션 확장성 향상

5. postgresql.conf 매개변수 신중하게 조정

postgresql.conf 파일에는 PostgreSQL의 동작, 리소스 사용 및 성능을 제어하는 수많은 매개변수가 포함되어 있습니다. 일반적인 기본값은 종종 보수적이므로 서버 하드웨어와 워크로드에 따라 이러한 값을 조정하는 것이 중요합니다.

고려해야 할 주요 매개변수

  • shared_buffers: PostgreSQL이 데이터 페이지 캐싱에 사용하는 메모리 양. 많은 전용 서버가 전체 RAM의 약 25%에서 시작한 후 테스트 후 조정합니다.
  • work_mem: 디스크에 쓰기 전 정렬 및 해시 작업에 사용되는 메모리. 디스크 정렬을 피하기 위해 충분히 높게 설정하되 세션별로 설정되므로 주의하세요.
  • maintenance_work_mem: VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY에 사용되는 메모리. work_mem보다 훨씬 높게 설정할 수 있습니다.
  • wal_buffers: 디스크에 플러시하기 전 WAL(Write-Ahead Log) 데이터용 메모리. 작지만 중요합니다.
  • effective_cache_size: PostgreSQL과 OS가 디스크 캐싱에 사용할 수 있는 메모리 양을 쿼리 플래너에 알려줍니다. 많은 배포에서 RAM의 큰 부분으로 설정한 후 실제 쿼리로 계획을 검증합니다.
  • max_connections: 허용되는 최대 동시 연결 수.

경고

postgresql.conf 변경 사항은 종종 데이터베이스 재시작 또는 리로드(pg_ctl reload)가 필요합니다. 잘못된 조정은 성능을 저하시키거나 안정성 문제를 일으킬 수 있습니다.

6. 하드웨어 모니터링 및 적절한 크기 조정

완벽한 데이터베이스 튜닝에도 불구하고 하드웨어가 부족하면 병목 현상이 발생합니다. 서버의 CPU, RAM, 디스크 I/O(IOPS, 처리량) 및 네트워크 사용량을 정기적으로 모니터링하세요. pg_stat_statements, pg_stat_activity 및 OS 수준 모니터링 도구(예: vmstat, iostat, top)는 귀중한 통찰력을 제공합니다.

주요 모니터링 영역

  • CPU 사용률: 높은 CPU는 비효율적인 쿼리나 처리 능력 부족을 나타낼 수 있습니다.
  • 메모리 사용량: 과도한 스와핑은 RAM 부족을 나타냅니다.
  • 디스크 I/O: 느린 디스크 액세스는 데이터베이스 성능을 심각하게 제한할 수 있습니다. 더 빠른 스토리지(SSD/NVMe) 또는 RAID 구성을 고려하세요.
  • 네트워크 지연 시간: 애플리케이션과 데이터베이스 간의 높은 지연 시간은 요청 속도를 저하시킬 수 있습니다.

하드웨어 크기 조정은 현재 및 예상 워크로드를 처리하기에 충분한 리소스(CPU, RAM, 빠른 스토리지)를 할당하는 것을 의미합니다. 클라우드 제공업체는 확장을 더 쉽게 만들지만 리소스의 효율적인 사용은 항상 중요합니다.

7. 강력한 인증 구현 및 pg_hba.conf 제한

보안은 강력한 인증에서 시작됩니다. 항상 강력한 암호 정책을 적용하고 안전한 인증 방법을 사용하세요. PostgreSQL은 pg_hba.conf(호스트 기반 인증)에 정의된 다양한 방법을 지원합니다. 프로덕션 환경에서는 암호 인증에 md5 또는 password보다 scram-sha-256을 선호하세요. 더 안전하기 때문입니다.

pg_hba.conf에서 신뢰할 수 있는 호스트 또는 네트워크로만 액세스를 제한하세요. 강력한 방화벽 규칙과 함께 사용하지 않는 한 host all all 0.0.0.0/0 scram-sha-256을 피하세요.

pg_hba.conf 예시

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             my_app_user     192.168.1.0/24          scram-sha-256

팁

필요한 액세스만 허용되도록 pg_hba.conf 파일을 정기적으로 감사하세요.

8. 최소 권한 원칙 준수 (RBAC)

최소 권한 원칙은 사용자와 프로세스가 작업을 수행하는 데 필요한 최소한의 권한만 가져야 한다는 것을 의미합니다. PostgreSQL에서는 RBAC(역할 기반 액세스 제어)를 통해 이를 달성합니다.

  • 특정 역할 생성: 애플리케이션 액세스에 postgres 슈퍼유저를 사용하지 마세요.
  • 최소 권한 부여: GRANT 및 REVOKE 명령을 사용하여 데이터베이스, 스키마, 테이블, 시퀀스 및 함수에 대한 권한을 정확하게 할당하세요.
  • PUBLIC 권한 검토: PostgreSQL은 PUBLIC에 일부 기본 권한(예: 데이터베이스에 대한 CONNECT, 이전 기본 설정에서 public 스키마에 대한 USAGE)을 부여합니다. 애플리케이션에 필요하지 않은 경우 광범위한 액세스를 취소하세요.

예시: 읽기 전용 사용자 생성

CREATE ROLE app_readonly_user WITH LOGIN PASSWORD 'strongpassword';
GRANT CONNECT ON DATABASE mydatabase TO app_readonly_user;
GRANT USAGE ON SCHEMA public TO app_readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly_user;

9. 방화벽 및 SSL/TLS로 네트워크 액세스 보호

데이터베이스 서버는 공용 인터넷에 직접 노출되어서는 안 됩니다. 강력한 방화벽 규칙을 구현하여 PostgreSQL의 기본 포트(5432)로의 수신 연결을 신뢰할 수 있는 애플리케이션 서버 또는 특정 IP 주소로만 제한하세요.

또한 SSL/TLS를 사용하여 애플리케이션과 PostgreSQL 간의 모든 통신을 암호화하세요. 이는 도청 및 중간자 공격을 방지합니다. postgresql.conf에서 ssl = on을 구성하고 클라이언트가 SSL을 사용하도록 설정되어 있는지 확인하세요(sslmode=require 또는 verify-full).

postgresql.conf SSL 구성

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
# ssl_ca_file = 'root.crt' # 클라이언트 인증서가 필요한 경우

참고

postgresql.conf의 listen_addresses가 특정 IP 또는 모든 인터페이스에 대해 *로 설정되어 있는지 확인하세요(외부에서 방화벽으로 보호되는 경우에만).

10. 강력한 백업 및 복구 전략 구현

데이터 손실은 치명적입니다. 강력한 백업 및 복구 전략은 필수입니다. 단순히 백업만 하지 말고 복구 프로세스를 정기적으로 테스트하여 백업이 유효하고 RTO(복구 시간 목표) 내에 성공적으로 복원될 수 있는지 확인하세요.

백업 방법

  • pg_dump / pg_dumpall: 논리적 백업(SQL 스크립트)으로 소규모 데이터베이스 또는 스키마 전용 백업에 적합합니다. 사용하기 쉽지만 대규모 데이터베이스의 경우 느릴 수 있습니다.
  • pg_basebackup: 데이터 디렉토리의 전체 복사본을 생성하는 물리적 기본 백업입니다. PITR(특정 시점 복구)에 필수적입니다.
  • WAL 아카이빙: pg_basebackup과 함께 연속 아카이빙(WAL 세그먼트 전송)을 통해 PITR이 가능하므로 데이터베이스를 특정 시점으로 복원할 수 있습니다.

백업을 오프사이트에 저장하고 암호화하세요. 자동화된 백업 솔루션을 고려하고 성공/실패를 모니터링하세요.

예시: pg_dump

pg_dump -Fc -f mydatabase_$(date +%Y%m%d).bak mydatabase

예시: pg_basebackup

pg_basebackup -h localhost -p 5432 -U backup_user -D /var/lib/postgresql/backups/base_backup_$(date +%Y%m%d) -F tar -z -v

핵심 요점

느린 쿼리, 백업 및 액세스 제어부터 시작하세요. 이 세 가지 영역이 가장 고통스러운 실수를 조기에 잡아냅니다. 그런 다음 자체 워크로드의 측정값을 기반으로 메모리, autovacuum, 연결 풀링 및 하드웨어를 조정하세요.