PostgreSQL 성능 최적화 10가지 - DBA 없이도 쿼리 속도 10배 올리기

성능 문제의 근본 원인

대부분의 PostgreSQL 성능 문제는 인덱스 부재, 비효율적인 쿼리, 잘못된 설정에서 비롯됩니다.

1. EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
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 > '2025-01-01'
GROUP BY u.id;

2. 복합 인덱스

CREATE INDEX idx_good ON orders(user_id, status, created_at);
CREATE INDEX idx_active ON orders(created_at) WHERE status = 'active';

3. 커버링 인덱스

CREATE INDEX idx_covering ON users(email) INCLUDE (name, created_at);
SELECT name, created_at FROM users WHERE email = 'user@example.com';
-- 테이블 접근 0번!

4. N+1 쿼리 해결

SELECT u.*, json_agg(o.*) as orders
FROM users u LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

5. 파티셔닝

CREATE TABLE orders (
    id BIGSERIAL, created_at DATE NOT NULL
) PARTITION BY RANGE (created_at);
CREATE TABLE orders_2026 PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

6. postgresql.conf 튜닝 (RAM 16GB)

shared_buffers = 4GB
effective_cache_size = 12GB
random_page_cost = 1.1
max_parallel_workers_per_gather = 4
wal_buffers = 64MB

7. pg_stat_statements

CREATE EXTENSION pg_stat_statements;
SELECT round(mean_exec_time::numeric, 2) AS mean_ms,
       calls, left(query, 100) AS query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 10;

8. PgBouncer

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

9. VACUUM ANALYZE

VACUUM ANALYZE orders;
SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

10. JSONB 인덱싱

CREATE INDEX idx_meta ON products USING GIN(metadata);
SELECT * FROM products WHERE metadata @> '{"category": "electronics"}';

EXPLAIN ANALYZE와 pg_stat_statements로 실제 병목을 먼저 찾는 것이 첫 번째 단계입니다.