PostgreSQL 쿼리 성능 최적화: EXPLAIN부터 파티셔닝까지

데이터베이스 성능은 서비스 품질을 결정하는 핵심 요소입니다. 특히 사용자가 증가하고 데이터가 누적될수록 느린 쿼리는 전체 시스템의 병목이 되어 사용자 경험을 크게 저하시킵니다. 본 가이드에서는 PostgreSQL의 강력한 성능 최적화 기법들을 실전 예제와 함께 상세히 살펴봅니다.

1. EXPLAIN ANALYZE로 쿼리 실행 계획 분석하기

PostgreSQL의 EXPLAIN ANALYZE는 쿼리가 실제로 어떻게 실행되는지 보여주는 강력한 도구입니다. 단순히 계획만 보여주는 EXPLAIN과 달리, ANALYZE 옵션을 추가하면 실제 실행 시간과 처리된 행 수를 확인할 수 있습니다.

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 100;

실행 계획을 읽을 때 주목해야 할 핵심 지표는 다음과 같습니다:

• Cost: 0..1000 형태로 표시되며, 첫 번째 숫자는 첫 행을 반환하는 비용, 두 번째는 전체 비용입니다
• Rows: 예상 반환 행 수 (실제 행 수와 차이가 크면 통계 업데이트 필요)
• Actual time: 실제 소요된 시간 (밀리초 단위)
• Seq Scan vs Index Scan: Sequential Scan은 테이블 전체를 읽고, Index Scan은 인덱스를 활용합니다

Seq Scan이 나타나는 경우, 해당 컬럼에 인덱스를 추가하면 성능이 극적으로 개선될 수 있습니다. 특히 WHERE 절이나 JOIN 조건에 사용되는 컬럼은 인덱스 후보입니다.

2. 인덱스 전략: 올바른 타입 선택하기

PostgreSQL은 다양한 인덱스 타입을 제공하며, 각각의 용도가 다릅니다.

B-tree 인덱스 (기본)

가장 범용적으로 사용되는 인덱스입니다. 등호(=), 부등호(<, >), BETWEEN, IN, IS NULL 연산에 효과적입니다.

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_created_at ON orders(created_at);

Hash 인덱스

등호 연산(=)만 사용하는 경우 B-tree보다 빠르지만, 범위 검색이나 정렬에는 사용할 수 없습니다.

CREATE INDEX idx_users_uuid ON users USING HASH (uuid);

GIN (Generalized Inverted Index)

배열, JSONB, 전문 검색에 최적화되어 있습니다. 여러 값을 포함하는 데이터 타입에 적합합니다.

CREATE INDEX idx_products_tags ON products USING GIN (tags);
CREATE INDEX idx_documents_content ON documents USING GIN (to_tsvector('english', content));

GiST (Generalized Search Tree)

지리 정보, 범위 데이터, 전문 검색 등 복잡한 데이터 타입에 사용됩니다.

CREATE INDEX idx_stores_location ON stores USING GIST (location);

3. 복합 인덱스 순서의 중요성

복합 인덱스는 여러 컬럼을 조합한 인덱스로, 컬럼 순서가 성능에 큰 영향을 미칩니다. 일반적으로 선택도가 높은(unique 값이 많은) 컬럼을 앞에 배치하는 것이 유리합니다.

-- 잘못된 예: status는 선택도가 낮음 (active/inactive 정도)
CREATE INDEX idx_bad ON orders(status, created_at, user_id);

-- 올바른 예: user_id가 가장 선택도가 높음
CREATE INDEX idx_good ON orders(user_id, created_at, status);

이 인덱스는 다음 쿼리들에 효과적으로 사용됩니다:

SELECT * FROM orders WHERE user_id = 123;
SELECT * FROM orders WHERE user_id = 123 AND created_at > '2024-01-01';
SELECT * FROM orders WHERE user_id = 123 AND created_at > '2024-01-01' AND status = 'active';

하지만 첫 번째 컬럼(user_id)이 WHERE 절에 없으면 인덱스를 효과적으로 사용할 수 없습니다.

4. VACUUM과 ANALYZE로 통계 최신화

PostgreSQL은 MVCC(Multi-Version Concurrency Control) 방식으로 동작하기 때문에, UPDATE와 DELETE 작업 시 죽은 행(dead rows)이 남습니다. VACUUM은 이를 정리하고, ANALYZE는 통계 정보를 업데이트합니다.

-- 특정 테이블 정리 및 통계 업데이트
VACUUM ANALYZE users;

-- 전체 데이터베이스
VACUUM ANALYZE;

-- 디스크 공간 회수를 위한 FULL VACUUM (테이블 락 발생)
VACUUM FULL users;

자동 VACUUM 설정 조정 (postgresql.conf):

autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50

통계 정보가 오래되면 쿼리 플래너가 잘못된 실행 계획을 선택할 수 있으므로, 대량의 데이터 변경 후에는 반드시 ANALYZE를 실행해야 합니다.

5. Connection Pooling으로 연결 오버헤드 줄이기

PostgreSQL 연결 생성은 비용이 큰 작업입니다. 매 요청마다 새로운 연결을 만들면 성능이 크게 저하됩니다. pgBouncer는 가벼운 커넥션 풀링 솔루션으로, 연결을 재사용하여 오버헤드를 최소화합니다.

pgBouncer 설치 및 설정

# Ubuntu/Debian
sudo apt-get install pgbouncer

# pgbouncer.ini 설정
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25

pool_mode 옵션:

• session: 클라이언트 연결당 하나의 서버 연결 (가장 안전)
• transaction: 트랜잭션 단위로 연결 재사용 (권장)
• statement: SQL 문장 단위로 재사용 (가장 공격적)

애플리케이션에서는 pgBouncer 포트(6432)로 연결하면 됩니다:

const pool = new Pool({
  host: 'localhost',
  port: 6432,
  database: 'mydb',
  user: 'myuser',
  password: 'mypassword',
  max: 20, // 애플리케이션 레벨 풀 크기
});

6. 대용량 테이블 파티셔닝

1억 건 이상의 데이터를 가진 테이블은 파티셔닝으로 성능을 획기적으로 개선할 수 있습니다. PostgreSQL 10부터 선언적 파티셔닝을 지원합니다.

Range 파티셔닝 (날짜 기준)

-- 부모 테이블 생성
CREATE TABLE orders (
    id BIGSERIAL,
    user_id INTEGER,
    amount DECIMAL(10,2),
    created_at TIMESTAMP NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- 월별 파티션 생성
CREATE TABLE orders_2024_01 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE orders_2024_02 PARTITION OF orders
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- 자동으로 해당 파티션만 스캔됨
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-15' AND '2024-01-20';

List 파티셔닝 (지역별)

CREATE TABLE sales (
    id BIGSERIAL,
    region TEXT NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

CREATE TABLE sales_asia PARTITION OF sales
    FOR VALUES IN ('Korea', 'Japan', 'China');

CREATE TABLE sales_europe PARTITION OF sales
    FOR VALUES IN ('UK', 'Germany', 'France');

Hash 파티셔닝 (균등 분산)

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email TEXT NOT NULL
) PARTITION BY HASH (id);

CREATE TABLE users_p0 PARTITION OF users
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE users_p1 PARTITION OF users
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

-- 나머지 파티션들...

파티셔닝의 주요 이점:

• 쿼리 시 필요한 파티션만 스캔 (Partition Pruning)
• 오래된 데이터 삭제 시 DROP TABLE로 빠른 처리
• 병렬 처리 효율 증가

7. Materialized View로 복잡한 집계 미리 계산

복잡한 JOIN과 집계가 포함된 쿼리는 Materialized View로 결과를 미리 계산해둘 수 있습니다.

-- 일별 매출 통계 Materialized View
CREATE MATERIALIZED VIEW daily_sales_stats AS
SELECT 
    DATE(created_at) as sale_date,
    COUNT(*) as order_count,
    SUM(amount) as total_amount,
    AVG(amount) as avg_amount,
    COUNT(DISTINCT user_id) as unique_users
FROM orders
GROUP BY DATE(created_at)
WITH DATA;

-- 인덱스 추가
CREATE INDEX idx_daily_sales_date ON daily_sales_stats(sale_date);

-- 데이터 갱신 (일배치로 실행)
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_stats;

CONCURRENTLY 옵션을 사용하면 갱신 중에도 조회가 가능하지만, unique 인덱스가 필요합니다.

8. Full Text Search로 고성능 검색 구현

PostgreSQL의 Full Text Search는 ElasticSearch 없이도 강력한 검색 기능을 제공합니다.

-- tsvector 컬럼 추가
ALTER TABLE articles ADD COLUMN search_vector tsvector;

-- 검색 벡터 업데이트
UPDATE articles SET search_vector = 
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(content, ''));

-- GIN 인덱스 생성
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- 검색 쿼리
SELECT id, title, ts_rank(search_vector, query) as rank
FROM articles, to_tsquery('english', 'postgresql & performance') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

한국어 검색을 위해서는 별도의 파서(mecab 등)가 필요할 수 있습니다.

9. pg_stat_statements로 슬로우 쿼리 모니터링

-- 확장 설치
CREATE EXTENSION pg_stat_statements;

-- 가장 느린 쿼리 Top 10
SELECT 
    query,
    calls,
    total_exec_time / 1000 as total_sec,
    mean_exec_time / 1000 as mean_sec,
    max_exec_time / 1000 as max_sec
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 통계 초기화
SELECT pg_stat_statements_reset();

10. postgresql.conf 튜닝 파라미터

# 메모리 설정 (총 메모리의 25% 권장)
shared_buffers = 4GB

# 정렬/해시 작업용 메모리
work_mem = 64MB

# 유지보수 작업용 메모리
maintenance_work_mem = 1GB

# WAL 설정
wal_buffers = 16MB
checkpoint_completion_target = 0.9

# 쿼리 플래너
effective_cache_size = 12GB
random_page_cost = 1.1  # SSD는 낮게

# 병렬 처리
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

11. Replication과 Read Replica

읽기 부하가 높은 서비스는 Read Replica로 분산할 수 있습니다.

-- 마스터에서 복제 슬롯 생성
SELECT * FROM pg_create_physical_replication_slot('replica_1');

-- 스탠바이 서버의 postgresql.conf
primary_conninfo = 'host=master port=5432 user=replicator'
primary_slot_name = 'replica_1'
hot_standby = on

애플리케이션에서는 쓰기는 마스터로, 읽기는 레플리카로 분산합니다.

결론

PostgreSQL 성능 최적화는 한 번에 끝나는 작업이 아니라 지속적인 모니터링과 개선이 필요한 과정입니다. EXPLAIN ANALYZE로 문제를 진단하고, 적절한 인덱스와 파티셔닝을 적용하며, 통계를 최신으로 유지하는 것이 핵심입니다.

GMI는 PostgreSQL 기반의 고성능 웹 애플리케이션 개발을 지원하며, 데이터베이스 설계부터 최적화까지 엔터프라이즈급 컨설팅을 제공합니다. 성능 문제로 고민 중이시라면 언제든지 문의해주세요.