SQL Performance OptimizationSQL 성능 최적화 심화

-- 복합 인덱스 컬럼 순서: 선택도 높은 컬럼 먼저
-- 나쁜 예: (status, created) - status 선택도 낮음
-- 좋은 예: (user_id, created) - user_id 선택도 높음
CREATE INDEX idx_articles_user_date
  ON articles (user_id, created DESC)
  INCLUDE (title, status);  -- 커버링 인덱스 (힙 접근 제거)

-- 부분 인덱스 (조건부 인덱스)
CREATE INDEX idx_active_articles
  ON articles (created)
  WHERE published = true;  -- 발행된 글만 인덱싱 (인덱스 크기 감소)

-- 함수 인덱스
CREATE INDEX idx_lower_email
  ON users (LOWER(email));  -- 대소문자 무관 검색
-- WHERE LOWER(email) = 'user@example.com' 에서 사용됨

-- GIN 인덱스 (배열/JSONB/전문검색)
CREATE INDEX idx_tags ON articles USING gin (tags);
CREATE INDEX idx_data ON logs USING gin (metadata jsonb_ops);

-- 1. N+1 문제 해결: 조인으로 배치 로드
-- 나쁜 예 (N+1)
-- for article in articles: get_author(article.author_id)

-- 좋은 예: 한 번에 조인
SELECT a.*, u.name AS author_name
FROM articles a
JOIN users u ON u.id = a.author_id
WHERE a.id = ANY(ARRAY[1,2,3,...]);

-- 2. 페이지네이션: OFFSET 대신 Keyset
-- 나쁜 예 (OFFSET: 큰 페이지에서 느림)
SELECT * FROM articles ORDER BY id LIMIT 20 OFFSET 10000;

-- 좋은 예 (Keyset/Cursor: 항상 빠름)
SELECT * FROM articles
WHERE id < :last_seen_id  -- 이전 페이지 마지막 ID
ORDER BY id DESC LIMIT 20;

-- 3. 집계 쿼리 최적화: Materialized View
CREATE MATERIALIZED VIEW article_stats AS
SELECT
  author_id,
  COUNT(*) AS total_articles,
  SUM(view_count) AS total_views,
  MAX(created) AS last_published
FROM articles GROUP BY author_id;

CREATE UNIQUE INDEX ON article_stats (author_id);

-- 주기적 갱신
REFRESH MATERIALIZED VIEW CONCURRENTLY article_stats;

-- 범위 파티셔닝 (월별)
CREATE TABLE events (
  id       BIGSERIAL,
  user_id  INT,
  action   TEXT,
  created  TIMESTAMPTZ DEFAULT NOW()
) PARTITION BY RANGE (created);

CREATE TABLE events_2024_01 PARTITION OF events
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE events_2024_02 PARTITION OF events
  FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- 파티션별 인덱스 자동 상속
CREATE INDEX ON events (user_id, created);

-- 파티션 정리 (오래된 데이터)
DROP TABLE events_2023_01;  -- 즉각적 삭제 (DELETE보다 훨씬 빠름)