Split View: PostgreSQL 17 성능 실험실: 쿼리, 인덱스, 운영 튜닝

# 실험 환경
# OS: Ubuntu 24.04 LTS
# CPU: AMD EPYC 7763 16코어
# RAM: 64GB
# Storage: NVMe SSD (Samsung PM9A3)
# PostgreSQL: 17.2

# Docker로 PG 17 실험 환경 구축
docker run -d \
  --name pg17-lab \
  -e POSTGRES_PASSWORD=labpass \
  -e POSTGRES_DB=perflab \
  -p 5432:5432 \
  -v pg17_data:/var/lib/postgresql/data \
  --shm-size=4g \
  postgres:17.2-bookworm

# 기본 성능 설정 적용
docker exec -i pg17-lab psql -U postgres -d perflab <<'SQL'
ALTER SYSTEM SET shared_buffers = '16GB';
ALTER SYSTEM SET effective_cache_size = '48GB';
ALTER SYSTEM SET work_mem = '128MB';
ALTER SYSTEM SET maintenance_work_mem = '2GB';
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
ALTER SYSTEM SET max_worker_processes = 16;
ALTER SYSTEM SET wal_buffers = '64MB';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
SQL

docker restart pg17-lab

-- 테스트 테이블 생성
CREATE TABLE lab_orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id integer NOT NULL,
    product_id integer NOT NULL,
    status varchar(20) NOT NULL DEFAULT 'pending',
    total_amount numeric(12,2) NOT NULL,
    metadata jsonb DEFAULT '{}',
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

-- 1000만 건 삽입 (약 2분 소요)
INSERT INTO lab_orders (user_id, product_id, status, total_amount, metadata, created_at)
SELECT
    (random() * 100000)::int,
    (random() * 5000)::int,
    (ARRAY['pending','confirmed','shipped','delivered','cancelled'])[1 + (random()*4)::int],
    round((random() * 500 + 10)::numeric, 2),
    jsonb_build_object(
        'channel', (ARRAY['web','app','api'])[1 + (random()*2)::int],
        'region', (ARRAY['kr','us','jp','eu'])[1 + (random()*3)::int]
    ),
    now() - (random() * 365)::int * interval '1 day'
FROM generate_series(1, 10000000);

-- 통계 수집
ANALYZE lab_orders;

-- 인덱스 생성
CREATE INDEX idx_lab_orders_user ON lab_orders (user_id);

-- 캐시 비우기 (cold start 측정 시)
-- DISCARD ALL;

-- 실험 A: 소규모 IN 절 (10개 값)
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, user_id, total_amount
FROM lab_orders
WHERE user_id IN (42, 128, 256, 512, 1024, 2048, 4096, 8192, 16384, 32768);

-- PG 17 결과 예시:
-- Index Scan using idx_lab_orders_user on lab_orders
--   Index Cond: (user_id = ANY ('{42,128,...}'))
--   Buffers: shared hit=3847
--   Execution Time: 12.3 ms

-- 실험 B: 대규모 IN 절 (100개 값)
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, user_id, total_amount
FROM lab_orders
WHERE user_id IN (
    SELECT (random() * 100000)::int FROM generate_series(1, 100)
);

-- 실험 C: IN 절 vs ANY(ARRAY[]) 성능 비교
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, user_id, total_amount
FROM lab_orders
WHERE user_id = ANY(ARRAY[42, 128, 256, 512, 1024, 2048, 4096, 8192, 16384, 32768]);

-- 실험 설정: effective_io_concurrency 값 변화에 따른 Seq Scan 성능
-- 테스트 1: 기본값 (1)
SET effective_io_concurrency = 1;
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT count(*), avg(total_amount)
FROM lab_orders
WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';

-- 테스트 2: NVMe 권장값 (200)
SET effective_io_concurrency = 200;
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT count(*), avg(total_amount)
FROM lab_orders
WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';

-- 대량 dead tuple 생성
UPDATE lab_orders SET updated_at = now()
WHERE id % 5 = 0;  -- 200만 건 갱신 -> 200만 dead tuple 생성

-- dead tuple 수 확인
SELECT n_dead_tup, n_live_tup,
       round(n_dead_tup * 100.0 / (n_live_tup + n_dead_tup), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE relname = 'lab_orders';
-- 예상 결과: dead_pct = 16.67%

-- VACUUM 실행 (메모리 사용량 관찰)
SET maintenance_work_mem = '256MB';
VACUUM (VERBOSE) lab_orders;

-- PG 16 출력 (비교 기준):
-- INFO: vacuuming "public.lab_orders"
-- INFO: index "idx_lab_orders_user" now contains 10000000 row versions
--   in 68425 pages
-- DETAIL: 2000000 dead row versions cannot be removed yet.
-- CPU: user: 8.42 s, system: 2.15 s, elapsed: 14.73 s
-- dead tuple tracking memory: 152MB

-- PG 17 출력:
-- INFO: vacuuming "public.lab_orders"
-- INFO: finished vacuuming "public.lab_orders":
--   index scans: 1
--   pages: 0 removed, 142857 remain
--   tuples: 2000000 removed, 10000000 remain
-- CPU: user: 5.89 s, system: 1.43 s, elapsed: 9.12 s
-- dead tuple tracking memory: 8MB (19x reduction)

# pgbench 초기화
pgbench -i -s 100 -U postgres perflab

# PG 17 WAL 성능 테스트: 32 클라이언트 동시 쓰기
pgbench -U postgres -d perflab \
  -c 32 -j 8 -T 60 \
  --protocol=prepared \
  --no-vacuum

# 결과 예시 (PG 17):
# transaction type: <builtin: TPC-B (sort of)>
# scaling factor: 100
# number of clients: 32
# number of threads: 8
# duration: 60 s
# tps = 28,456 (without initial connection establishing)
# latency average = 1.124 ms

# 비교: PG 16 동일 설정에서의 결과
# tps = 15,823
# latency average = 2.023 ms

-- 테스트 데이터: JSONB 배열이 포함된 컬럼
CREATE TABLE lab_events (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload jsonb NOT NULL
);

INSERT INTO lab_events (payload)
SELECT jsonb_build_object(
    'event_type', 'purchase',
    'items', jsonb_agg(
        jsonb_build_object(
            'sku', 'SKU-' || (random() * 10000)::int,
            'qty', (random() * 5 + 1)::int,
            'price', round((random() * 100)::numeric, 2)
        )
    )
)
FROM generate_series(1, 100000),
     LATERAL generate_series(1, (random() * 4 + 1)::int) AS items(n)
GROUP BY (random() * 100000)::int;

-- 방법 1: 기존 jsonb_to_recordset (PG 16 호환)
EXPLAIN (ANALYZE)
SELECT e.id, item.*
FROM lab_events e,
     LATERAL jsonb_to_recordset(e.payload->'items')
         AS item(sku text, qty int, price numeric)
WHERE e.id BETWEEN 1 AND 1000;

-- 방법 2: JSON_TABLE (PG 17 신규)
EXPLAIN (ANALYZE)
SELECT e.id, jt.*
FROM lab_events e,
     JSON_TABLE(
         e.payload, '$.items[*]'
         COLUMNS (
             sku text PATH '$.sku',
             qty integer PATH '$.qty',
             price numeric PATH '$.price'
         )
     ) AS jt
WHERE e.id BETWEEN 1 AND 1000;

-- 테스트용 테이블
CREATE TABLE lab_import (
    id integer NOT NULL,
    name text NOT NULL,
    score numeric(5,2)
);

-- 일부러 오류가 포함된 CSV 생성 (Python)
-- import csv
-- with open('/tmp/lab_data.csv', 'w') as f:
--     w = csv.writer(f)
--     for i in range(100000):
--         if i % 1000 == 0:  # 0.1% 오류
--             w.writerow([i, f'name_{i}', 'NOT_A_NUMBER'])
--         else:
--             w.writerow([i, f'name_{i}', round(50 + 50 * (i % 100) / 100, 2)])

-- PG 17: ON_ERROR = ignore로 에러 행 건너뛰기
COPY lab_import FROM '/tmp/lab_data.csv'
WITH (FORMAT csv, ON_ERROR stop);  -- 기본값: 첫 에러에서 중단

COPY lab_import FROM '/tmp/lab_data.csv'
WITH (FORMAT csv, ON_ERROR ignore);  -- 에러 행 건너뛰고 계속 진행
-- NOTICE: 100 rows were skipped due to data type incompatibility

-- 로드된 행 수 확인
SELECT count(*) FROM lab_import;
-- 99900 (100건 스킵)

# ===== 메모리 =====
shared_buffers = '16GB'              # RAM의 25% (64GB 기준)
effective_cache_size = '48GB'        # RAM의 75%
work_mem = '64MB'                    # 세션당 정렬/해시용 (동시 세션 수 고려)
maintenance_work_mem = '2GB'         # VACUUM, CREATE INDEX용
huge_pages = try                     # 대용량 shared_buffers 시 TLB miss 감소

# ===== I/O =====
effective_io_concurrency = 200       # NVMe SSD
maintenance_io_concurrency = 100     # VACUUM/인덱스 빌드용 I/O 동시성
random_page_cost = 1.1               # SSD 기준 (HDD는 4.0)
seq_page_cost = 1.0

# ===== WAL =====
wal_buffers = '64MB'
max_wal_size = '4GB'                 # 체크포인트 간격 늘림
min_wal_size = '1GB'
checkpoint_completion_target = 0.9
wal_compression = zstd               # PG 15+, WAL 크기 30-40% 감소

# ===== 병렬 처리 =====
max_parallel_workers_per_gather = 4
max_parallel_workers = 16
max_worker_processes = 20
parallel_tuple_cost = 0.01
min_parallel_table_scan_size = '8MB'

# ===== autovacuum =====
autovacuum_max_workers = 4
autovacuum_naptime = '30s'           # 기본 1min -> 30s
autovacuum_vacuum_cost_delay = '2ms' # 기본 2ms

# ===== 통계/모니터링 =====
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
track_io_timing = on                 # I/O 시간 측정 (EXPLAIN BUFFERS에 반영)
log_min_duration_statement = 500     # 500ms 이상 쿼리 로깅

-- 1. 캐시 적중률 (99% 미만이면 shared_buffers 증가 검토)
SELECT
    sum(blks_hit) * 100.0 / sum(blks_hit + blks_read) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname = current_database();

-- 2. 테이블별 Seq Scan vs Index Scan 비율
SELECT
    schemaname, relname,
    seq_scan, idx_scan,
    round(idx_scan * 100.0 / NULLIF(seq_scan + idx_scan, 0), 1) AS idx_pct,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE seq_scan + idx_scan > 100
ORDER BY seq_scan DESC
LIMIT 15;

-- 3. 현재 실행 중인 long-running 쿼리
SELECT
    pid,
    now() - query_start AS duration,
    state,
    wait_event_type,
    wait_event,
    left(query, 100) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
  AND query NOT LIKE '%pg_stat_activity%'
  AND now() - query_start > interval '10 seconds'
ORDER BY duration DESC;

-- 4. 복제 슬롯 지연 (streaming replication 사용 시)
SELECT
    slot_name,
    slot_type,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag
FROM pg_replication_slots;

-- 5. 테이블 크기 상위 10개
SELECT
    schemaname || '.' || relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_table_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS indexes_size,
    n_live_tup AS live_rows
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;