운영은 관찰에서 시작한다
개발 단계에서는 좋은 테이블 설계와 좋은 SQL이 성능의 대부분을 결정한다. 운영 단계에서는 여기에 한 가지가 더 필요하다. 데이터베이스의 상태를 계속 관찰하고, 작은 이상 신호가 장애로 커지기 전에 조정하는 일이다.
PostgreSQL은 시간이 지나면서 상태가 변한다. 주문이 쌓이고, 게시글이 수정되고, 로그 테이블이 커지고, 애플리케이션 서버 수가 늘어난다. 처음에는 빠르던 쿼리도 데이터 분포가 바뀌면 다른 실행 계획을 선택할 수 있다. 연결 수가 늘면 메모리 사용량이 증가하고, 오래 열린 트랜잭션은 VACUUM을 방해한다.
운영 관리의 원칙은 단순하다.
현재 상태를 숫자로 본다.
병목의 원인을 하나씩 좁힌다.
설정이나 쿼리를 바꾼다.
바꾼 뒤 같은 지표로 다시 확인한다.
이 장에서는 이 흐름에 필요한 도구와 점검 루틴을 다룬다.
AI 생성 이미지
VACUUM과 ANALYZE가 필요한 이유
PostgreSQL은 MVCC를 사용한다. 한 행을 수정할 때 기존 행을 바로 덮어쓰지 않고 새 버전을 만든다. 삭제된 행도 즉시 파일에서 사라지지 않는다. 이렇게 더 이상 보이지 않지만 아직 테이블 안에 남아 있는 행 버전을 dead tuple이라고 부른다.
예를 들어 게시판의 posts 테이블에서 조회 수가 자주 갱신된다고 하자.
UPDATE posts
SET view_count = view_count + 1
WHERE id = 100;논리적으로는 한 행이 바뀐 것처럼 보인다. 내부적으로는 예전 버전이 남고 새 버전이 추가된다. 이런 작업이 반복되면 테이블과 인덱스에 불필요한 공간이 쌓인다. 이를 보통 bloat, 즉 팽창이라고 부른다. 테이블 파일이 커질 수도 있고, 인덱스 안에 더 이상 필요 없는 항목이 많이 남을 수도 있다.
VACUUM은 dead tuple이 차지하던 공간을 재사용 가능하게 표시한다. 일반 VACUUM은 보통 운영 중에도 실행할 수 있으며, 파일 크기를 즉시 줄이는 것이 아니라 이후 INSERT나 UPDATE가 그 공간을 다시 쓰게 만든다. 파일 크기 자체를 줄여 운영체제에 공간을 돌려주는 VACUUM FULL도 있지만, 테이블을 다시 쓰고 강한 잠금을 잡기 때문에 운영 중에는 매우 신중해야 한다.
ANALYZE는 테이블의 통계 정보를 갱신한다. PostgreSQL의 플래너는 통계를 보고 몇 행이 조건에 맞을지 추정한다. 통계가 오래되면 인덱스를 써야 할 쿼리에서 순차 스캔을 선택하거나, 조인 순서를 잘못 잡을 수 있다.
PostgreSQL은 기본적으로 autovacuum을 사용해 필요한 테이블에 자동으로 VACUUM과 ANALYZE를 실행한다. autovacuum은 고정 임계값과 테이블 크기에 비례한 scale factor를 함께 사용해 실행 여부를 판단하며, 테이블별로 설정을 덮어쓸 수 있다1.
먼저 어떤 테이블에 dead tuple이 쌓이는지 확인한다.
SELECT
relname,
n_live_tup,
n_dead_tup,
n_mod_since_analyze,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;읽는 기준은 다음과 같다.
n_live_tup,n_dead_tup은 정확한 카운트라기보다 통계 기반 추정치에 가깝다. 한 번의 숫자보다 증가 추세를 보는 편이 좋다.n_dead_tup이 계속 증가하면 VACUUM이 따라가지 못하고 있을 수 있다.n_mod_since_analyze가 크면 통계가 오래되었을 수 있다.last_autovacuum,last_autoanalyze가 오래전이면 자동 작업이 실제로 도는지 확인해야 한다.
수정과 삭제가 많은 테이블은 기본 autovacuum 설정이 늦을 수 있다. 주문 상태가 자주 바뀌는 orders 테이블이라면 테이블 단위로 더 빠르게 동작하도록 조정할 수 있다.
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_vacuum_threshold = 1000,
autovacuum_analyze_scale_factor = 0.02,
autovacuum_analyze_threshold = 1000
);이 설정은 모든 테이블에 무작정 적용하지 않는다. 자주 바뀌고 큰 테이블부터 적용한다. 적용 후에는 pg_stat_user_tables와 로그를 보고 실제 autovacuum 주기와 쿼리 지연 변화를 확인한다. autovacuum을 끄는 방식으로 문제를 피하려고 하면 나중에 더 큰 bloat와 통계 오류를 만나기 쉽다.
필요하면 수동으로 실행할 수 있다.
VACUUM (ANALYZE) orders;
ANALYZE order_items;수동 실행은 장애 대응이나 대량 변경 직후에 유용하다. 하지만 매번 사람이 직접 실행해야 한다면 autovacuum 설정이나 트랜잭션 사용 방식에 문제가 없는지 먼저 봐야 한다. 특히 오래 열린 트랜잭션이 있으면 VACUUM이 정리할 수 있는 범위가 줄어들 수 있다.
상태를 읽는 기본 통계 뷰
PostgreSQL 운영에서 가장 자주 보는 뷰는 pg_stat_activity다. 현재 접속 중인 세션, 실행 중인 쿼리, 대기 상태를 보여준다. 누적 통계는 약간 늦게 반영될 수 있지만, 현재 활동 정보는 문제 상황을 볼 때 핵심 단서가 된다2.
SELECT
pid,
usename,
application_name,
state,
wait_event_type,
wait_event,
backend_xmin,
now() - xact_start AS xact_age,
now() - query_start AS query_age,
left(query, 120) AS query_sample
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY xact_start NULLS LAST;주의해서 볼 상태는 idle in transaction이다. 애플리케이션이 트랜잭션을 시작한 뒤 커밋이나 롤백을 하지 않고 대기하는 상태다. 이런 세션은 잠금을 오래 잡거나, VACUUM이 오래된 행 버전을 정리하지 못하게 만들 수 있다. xact_age가 길고 backend_xmin이 오래 남아 있는 세션이 있다면 우선 확인해야 한다.
잠금 대기를 찾을 때는 다음 쿼리가 유용하다.
SELECT
blocked.pid AS blocked_pid,
blocked.usename AS blocked_user,
blocker.pid AS blocker_pid,
blocker.usename AS blocker_user,
left(blocked.query, 100) AS blocked_query,
left(blocker.query, 100) AS blocker_query
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocker
ON blocker.pid = ANY(pg_blocking_pids(blocked.pid));데이터베이스 전체 상태는 pg_stat_database에서 본다.
SELECT
datname,
numbackends,
xact_commit,
xact_rollback,
blks_hit,
blks_read,
round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_pct,
temp_files,
pg_size_pretty(temp_bytes) AS temp_bytes,
deadlocks
FROM pg_stat_database
WHERE datname = current_database();cache_hit_pct는 참고 지표로 볼 수 있지만, 이 숫자 하나만으로 성능을 판단하지는 않는다. 읽기 패턴, 운영체제 캐시, 쿼리 특성이 함께 영향을 주기 때문이다. temp_files와 temp_bytes가 계속 늘면 정렬, 해시 조인, 집계가 메모리에 담기지 못하고 임시 파일을 만들고 있을 수 있다. 이때는 EXPLAIN (ANALYZE, BUFFERS)의 Buffers: temp read=... written=... 같은 단서나 pg_stat_statements의 temp_blks_written을 함께 본다.
테이블 단위의 사용 패턴은 pg_stat_user_tables에서 확인한다.
SELECT
relname,
seq_scan,
idx_scan,
n_tup_ins,
n_tup_upd,
n_tup_del,
n_dead_tup,
n_mod_since_analyze
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 20;순차 스캔이 많다고 항상 나쁜 것은 아니다. 작은 테이블이나 대부분의 행을 읽는 쿼리에서는 자연스럽다. 문제는 큰 테이블에서 소수 행만 찾는 업무 쿼리가 반복적으로 순차 스캔을 하는 경우다. 이때는 pg_stat_statements로 어떤 쿼리가 비용을 만들고 있는지 찾아야 한다.
pg_stat_statements는 SQL 문장별 실행 횟수, 총 실행 시간, 평균 실행 시간, 읽은 블록, 임시 블록, WAL 발생량 등을 모아 보여주는 확장이다. 사용하려면 shared_preload_libraries에 추가하고 서버를 재시작한 뒤 데이터베이스에서 확장을 생성해야 한다3.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT
queryid,
calls,
round(total_exec_time::numeric, 1) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
rows,
shared_blks_read,
temp_blks_written,
left(query, 120) AS query_sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;운영에서는 평균 시간이 가장 긴 쿼리만 보면 부족하다. 한 번에 2초 걸리는 쿼리보다 20ms지만 하루에 수백만 번 실행되는 쿼리가 더 큰 비용을 만들 수 있다. total_exec_time, calls, mean_exec_time을 함께 봐야 한다. 임시 블록이나 디스크 읽기가 많은 쿼리는 실행 시간이 아직 크게 튀지 않았더라도 데이터가 더 늘었을 때 병목이 될 가능성이 있다.
메모리와 주요 설정값 읽기
설정 변경은 성능 튜닝의 마지막 단계에 가깝다. 먼저 쿼리, 인덱스, 통계, 연결 수를 확인하고도 문제가 남을 때 설정을 조정한다.
현재 값을 확인한다.
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN (
'shared_buffers',
'work_mem',
'maintenance_work_mem',
'autovacuum_work_mem',
'effective_cache_size',
'max_connections'
)
ORDER BY name;shared_buffers는 PostgreSQL이 직접 사용하는 공유 버퍼다. 전용 데이터베이스 서버라면 전체 메모리의 25% 정도가 흔한 출발점이며, 운영체제 캐시도 함께 쓰기 때문에 무작정 크게 잡는다고 항상 좋아지지 않는다4.
work_mem은 정렬, 해시 조인, 해시 집계 같은 작업 하나가 임시 파일을 쓰기 전에 사용할 수 있는 메모리 기준이다. 중요한 점은 세션당 하나가 아니라 작업 단위라는 것이다. 복잡한 쿼리 하나가 여러 번 work_mem을 쓸 수 있고, 동시에 실행되는 세션 수만큼 곱해진다.
전역 work_mem을 크게 올리는 대신 무거운 배치나 리포트 작업에서만 일시적으로 올리는 방식이 안전하다.
BEGIN;
SET LOCAL work_mem = '128MB';
SELECT customer_id, sum(total_amount)
FROM orders
WHERE ordered_at >= date '2026-01-01'
GROUP BY customer_id
ORDER BY sum(total_amount) DESC;
COMMIT;maintenance_work_mem은 VACUUM, CREATE INDEX, 외래 키 추가 같은 유지보수 작업에 쓰인다. work_mem보다 크게 잡을 수 있지만, autovacuum worker가 여러 개 동시에 실행되면 그만큼 메모리를 사용할 수 있다. autovacuum만 따로 제한하려면 autovacuum_work_mem을 검토한다.
effective_cache_size는 실제로 메모리를 할당하는 값이 아니다. 플래너가 인덱스 사용 비용을 계산할 때 사용할 수 있다고 가정하는 캐시 크기다. PostgreSQL shared buffers와 운영체제 파일 캐시를 함께 고려해 정한다5. 이 값을 올린다고 캐시가 늘지는 않는다. 실행 계획의 선택에 영향을 줄 뿐이다.
max_connections는 동시에 열 수 있는 서버 연결 수다. 값이 커지면 연결을 받아낼 여지는 늘지만, 각 연결이 사용할 수 있는 메모리와 프로세스 관리 비용도 함께 커진다. 그래서 연결 수 문제는 설정값 하나로만 해결하려고 하지 말고, 실제 동시 실행 쿼리 수와 풀링 구조를 함께 봐야 한다.
연결 수가 늘 때는 풀링을 먼저 검토한다
PostgreSQL은 연결마다 서버 프로세스를 사용한다. 애플리케이션 서버가 늘고 각 서버가 많은 커넥션을 직접 열면 데이터베이스는 실제 쿼리 처리보다 연결 유지에 메모리와 CPU를 더 쓰게 된다.
이때 흔한 실수는 max_connections만 크게 올리는 것이다. 연결 수를 늘리면 동시에 실행될 수 있는 work_mem 사용량도 함께 늘어난다. 그래서 연결 폭증 문제는 보통 커넥션 풀링으로 해결한다.
PgBouncer는 애플리케이션과 PostgreSQL 사이에서 많은 클라이언트 연결을 적은 수의 서버 연결로 재사용하게 해 주는 풀러다. 기본 포트는 보통 6432이며, pool_mode에 따라 서버 연결을 언제 풀에 돌려줄지 결정한다. session 모드는 클라이언트 연결이 끝날 때, transaction 모드는 트랜잭션이 끝날 때, statement 모드는 각 문장이 끝날 때 반환한다6.
웹 애플리케이션에서는 transaction 모드가 자주 쓰인다.
[databases]
shop = host=127.0.0.1 port=5432 dbname=shop
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction
max_client_conn = 500
default_pool_size = 30
reserve_pool_size = 5단, transaction 모드는 세션 상태에 의존하는 기능과 충돌할 수 있다. 임시 테이블을 트랜잭션 밖에서 계속 사용하거나, 세션 단위 advisory lock, LISTEN/NOTIFY, 세션에 남는 SET 설정, 드라이버의 서버 측 prepared statement를 사용한다면 호환성을 확인해야 한다. 이런 경우 session 모드가 더 안전할 수 있다. statement 모드는 제약이 더 많으므로, 일반적인 웹 애플리케이션에서 무심코 선택하기보다는 동작 방식을 충분히 확인한 뒤 사용해야 한다.
PgBouncer 자체도 관찰해야 한다.
-- PgBouncer 관리 데이터베이스에 접속한 뒤 실행
SHOW POOLS;
SHOW CLIENTS;
SHOW SERVERS;
SHOW STATS;대기 중인 클라이언트가 계속 쌓이면 풀이 너무 작거나, 쿼리가 느려서 서버 연결을 오래 붙잡고 있는 것이다. 이때는 풀 크기만 늘리지 말고 pg_stat_statements에서 오래 연결을 점유하는 쿼리를 함께 찾아야 한다.
로그, 디스크, 체크포인트를 루틴으로 본다
통계 뷰는 현재 상태와 누적 수치를 보여준다. 로그는 그 순간에 발생한 사건을 남긴다. 운영 환경에서는 최소한 느린 쿼리, 잠금 대기, 임시 파일, autovacuum, 체크포인트 로그를 볼 수 있어야 한다.
예시 설정은 다음과 같다.
log_line_prefix = '%m [%p] %q%u@%d/%a '
log_min_duration_statement = '500ms'
log_lock_waits = on
log_temp_files = '64MB'
log_autovacuum_min_duration = '5s'
log_checkpoints = onlog_line_prefix에는 시간, 프로세스 ID, 사용자, 데이터베이스, 애플리케이션 이름을 넣어 두는 편이 좋다. log_min_duration_statement는 기준보다 오래 걸린 쿼리를 남긴다. 너무 낮게 잡으면 로그가 폭증할 수 있으므로, 운영 환경의 트래픽과 저장 공간을 보고 조정한다. log_temp_files는 큰 정렬이나 해시 작업이 디스크 임시 파일을 만들 때 단서를 준다. log_checkpoints는 체크포인트 때 기록된 버퍼 수와 소요 시간을 로그에 남긴다7.
디스크 사용량도 정기적으로 본다.
SELECT pg_size_pretty(pg_database_size(current_database())) AS db_size;
SELECT
relname,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(pg_relation_size(relid)) AS table_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;테이블 크기가 빠르게 늘면 세 가지를 구분한다.
실제 데이터가 늘었는가?
dead tuple과 bloat가 늘었는가?
인덱스가 과도하게 커졌는가?
체크포인트는 변경된 버퍼를 디스크에 기록하는 중요한 작업이다. 너무 자주 요청 체크포인트가 발생하면 쓰기 I/O가 불안정해질 수 있다. PostgreSQL 16에서는 pg_stat_bgwriter의 체크포인트 관련 열을 보고, 17 이후 버전에서는 pg_stat_checkpointer를 함께 확인한다. 버전별 뷰가 다를 수 있으므로 운영 서버에서 d pg_stat_bgwriter, d pg_stat_checkpointer로 열 이름을 확인하는 습관이 필요하다.
운영 점검 루틴
매일 볼 항목은 짧아야 한다.
pg_stat_activity에서 오래 실행 중인 쿼리와idle in transaction확인잠금 대기와 deadlock 로그 확인
디스크 여유 공간과 WAL 증가량 확인
에러 로그와 느린 쿼리 로그 확인
매주 볼 항목은 추세 중심이다.
pg_stat_statements의 총 실행 시간 상위 쿼리 비교pg_stat_user_tables의 dead tuple, 마지막 autovacuum, 마지막 analyze 확인큰 테이블과 큰 인덱스 증가 추세 확인
temp_files,temp_bytes,log_temp_files로 메모리가 부족한 작업 확인체크포인트 로그에서 쓰기 시간이 튀는 구간 확인
설정을 바꿀 때는 한 번에 하나씩 바꾼다. work_mem과 연결 수를 동시에 올리면 좋아진 원인도, 나빠진 원인도 알기 어렵다. 변경 전 지표를 저장하고, 변경 후 같은 쿼리와 같은 통계로 비교한다.
PostgreSQL 운영의 목표는 모든 쿼리를 항상 빠르게 만드는 것이 아니다. 데이터 증가와 트래픽 변화 속에서도 성능이 예측 가능한 범위 안에 머물게 하는 것이다. 이를 위해 VACUUM, 통계, 연결 수, 메모리, 로그, 디스크를 반복해서 확인해야 한다. 좋은 운영은 특별한 명령 하나가 아니라 같은 기준으로 꾸준히 보는 습관에서 나온다.
참고
1PostgreSQL Documentation: Vacuuming
2PostgreSQL Documentation: The Cumulative Statistics System
3PostgreSQL Documentation: pg_stat_statements
4PostgreSQL Documentation: Resource Consumption
이 노트는 보도·비평·교육·연구 목적으로 작성되었습니다. 저작권 문의가 있으시면 에서 알려주세요.