✍️ 필사 모드: PostgreSQL 프로덕션 운영 완전 가이드 2025: 확장(Extensions), 튜닝, 모니터링, 고가용성

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = 'all'
pg_stat_statements.max = 10000

-- TOP 10 가장 느린 쿼리
SELECT
  substring(query, 1, 80) as query,
  calls,
  total_exec_time::int as total_ms,
  mean_exec_time::int as avg_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

CREATE EXTENSION IF NOT EXISTS vector;

-- 768차원 임베딩 저장
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  content TEXT,
  embedding vector(768)
);

-- HNSW 인덱스 (PostgreSQL 16+)
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

-- 의미 기반 검색
SELECT id, content, 1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;

CREATE EXTENSION IF NOT EXISTS timescaledb;

CREATE TABLE metrics (
  time TIMESTAMPTZ NOT NULL,
  device_id TEXT,
  temperature FLOAT,
  humidity FLOAT
);

-- 하이퍼테이블로 변환 (자동 파티셔닝)
SELECT create_hypertable('metrics', 'time', chunk_time_interval => INTERVAL '1 day');

-- 자동 데이터 보존 (30일 후 삭제)
SELECT add_retention_policy('metrics', INTERVAL '30 days');

-- 연속 집계 (Continuous Aggregates)
CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', time) AS bucket,
  device_id,
  AVG(temperature) AS avg_temp
FROM metrics
GROUP BY bucket, device_id;

# 너무 자주 체크포인트 → I/O 폭증
# 너무 드물게 체크포인트 → 복구 시간 증가

checkpoint_timeout = 15min          # 기본 5분 → 15분
checkpoint_completion_target = 0.9  # 9분에 걸쳐 완료
max_wal_size = 4GB                  # WAL 크기 한계
min_wal_size = 1GB

random_page_cost = 1.1              # SSD인 경우 (HDD는 4.0)
effective_io_concurrency = 200      # SSD/NVMe (HDD는 1)
default_statistics_target = 100     # 통계 정확도 (높으면 느린 ANALYZE)

max_connections = 100               # PgBouncer가 있다면 충분

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0), 2) AS dead_ratio,
  last_autovacuum,
  autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

autovacuum = on
autovacuum_max_workers = 5         # 기본 3 → 5
autovacuum_naptime = 30s            # 기본 1분 → 30초
autovacuum_vacuum_scale_factor = 0.05  # 기본 0.2 → 0.05 (5% dead tuple에서 트리거)
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_limit = 2000  # 더 빨리 작업

ALTER TABLE big_table SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.005
);

-- 락 없이 인덱스 재구성 (PG 12+)
REINDEX INDEX CONCURRENTLY idx_users_email;

-- 락 없이 테이블 재구성 (pg_repack 확장 필요)
SELECT pg_repack.repack_table('public.users');

-- 현재 실행 중인 쿼리
SELECT pid, usename, state, query_start, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- 락 대기
SELECT
  blocked.pid AS blocked_pid,
  blocking.pid AS blocking_pid,
  blocked.query AS blocked_query,
  blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_locks AS blocked_lock ON blocked.pid = blocked_lock.pid
JOIN pg_locks AS blocking_lock
  ON blocked_lock.locktype = blocking_lock.locktype
  AND blocked_lock.relation = blocking_lock.relation
JOIN pg_stat_activity AS blocking ON blocking.pid = blocking_lock.pid
WHERE NOT blocked_lock.granted AND blocking_lock.granted;

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 12345;

# 슬로우 쿼리 로깅 활성화
log_min_duration_statement = 1000  # 1초 이상 쿼리 로깅
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

# pgBadger로 분석
pgbadger -j 4 /var/log/postgresql/postgresql-*.log -o report.html

┌──────────┐       ┌──────────┐       ┌──────────┐
│ etcd-1   │       │ etcd-2   │       │ etcd-3   │
└────┬─────┘       └────┬─────┘       └────┬─────┘
     └──────────────────┴──────────────────┘
                        │
        ┌───────────────┼───────────────┐
        │               │               │
   ┌────▼────┐     ┌────▼────┐     ┌────▼────┐
   │ Patroni │     │ Patroni │     │ Patroni │
   │ + PG    │     │ + PG    │     │ + PG    │
   │ Primary │     │ Replica │     │ Replica │
   └─────────┘     └─────────┘     └─────────┘

# patroni.yml
scope: postgres-cluster
namespace: /service/
name: postgres-1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 192.168.1.10:8008

etcd3:
  hosts: 192.168.1.20:2379,192.168.1.21:2379,192.168.1.22:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        max_connections: 100
        shared_buffers: 4GB
        wal_level: replica
        hot_standby: 'on'
        max_wal_senders: 10

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.10:5432
  data_dir: /var/lib/postgresql/16/main
  authentication:
    replication:
      username: replicator
      password: secret

# /etc/pgbackrest.conf
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=4
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=YOUR_PASSWORD
repo1-s3-bucket=my-pg-backups
repo1-s3-region=us-east-1
process-max=4
log-level-console=info
start-fast=y

[main]
pg1-path=/var/lib/postgresql/16/main
pg1-port=5432

# 전체 백업
pgbackrest --stanza=main backup --type=full

# 증분 백업
pgbackrest --stanza=main backup --type=incr

# PITR 복구 (특정 시점으로)
pgbackrest --stanza=main \
  --type=time \
  --target="2025-04-15 14:30:00" \
  restore

# postgresql.conf
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
wal_level = replica
max_wal_senders = 10

-- 인덱스 크기 확인
SELECT
  schemaname,
  relname,
  indexrelname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 10;

-- 사용되지 않는 인덱스
SELECT
  schemaname || '.' || relname AS table,
  indexrelname AS unused_index,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- 위험도 확인
SELECT datname, age(datfrozenxid)
FROM pg_database
ORDER BY age(datfrozenxid) DESC;