Contents
see ListPostgreSQL 17 주요 변경사항
2024년 출시된 PostgreSQL 17은 쿼리 성능, JSON 처리, 복제 기능에서 큰 도약을 이루었습니다. 특히 IS NOT NULL 최적화(약 37% 성능 향상), B-tree 인덱스 개선, 상관 서브쿼리 자동 변환 기능이 주목받고 있습니다. MySQL 9.0은 VECTOR 데이터 타입을 추가하며 AI/ML 워크로드를 데이터베이스 레벨에서 직접 처리할 수 있게 되었습니다.
EXPLAIN ANALYZE로 실행 계획 분석
쿼리 최적화의 첫 번째 단계는 실행 계획 분석입니다. PostgreSQL의 EXPLAIN ANALYZE 명령은 실제 실행 통계를 보여줍니다.
-- 기본 EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at >= '2026-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 10;
-- 결과 해석:
-- Seq Scan: 전체 테이블 스캔 (느림, 인덱스 필요)
-- Index Scan: 인덱스 사용 (빠름)
-- Bitmap Heap Scan: 중간 (많은 행 조회 시)
-- Hash Join: 해시 기반 조인
-- Nested Loop: 중첩 루프 조인
-- BUFFERS 옵션으로 캐시 히트율 확인
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders WHERE user_id = 12345;
-- shared hit: 캐시에서 읽은 블록 수
-- shared read: 디스크에서 읽은 블록 수
-- 히트율 = hit / (hit + read) * 100%인덱스 전략: 상황별 최적 인덱스 선택
B-tree 인덱스 (기본)
-- 단일 컬럼 인덱스
CREATE INDEX idx_users_email ON users(email);
-- 복합 인덱스 (자주 조회되는 컬럼 순서 중요)
-- WHERE status = 'active' AND created_at >= '2026-01-01' 쿼리용
CREATE INDEX idx_orders_status_date ON orders(status, created_at DESC);
-- 부분 인덱스 (조건 있는 행만 인덱싱 -> 크기 절감)
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';
-- pending 상태 주문만 인덱싱하여 크기 대폭 감소
-- 표현식 인덱스 (계산된 값으로 인덱싱)
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- WHERE LOWER(email) = 'user@example.com' 쿼리 최적화
-- 커버링 인덱스 (INCLUDE로 추가 컬럼 포함)
CREATE INDEX idx_orders_user_covering
ON orders(user_id) INCLUDE (total_amount, status, created_at);
-- SELECT total_amount, status, created_at FROM orders WHERE user_id = ?
-- 테이블 접근 없이 인덱스만으로 응답 가능GIN 인덱스: JSON과 전문 검색
-- JSONB 컬럼에 GIN 인덱스
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
-- JSON 포함 여부 검색 (GIN 인덱스 활용)
SELECT * FROM products
WHERE metadata @> '{"category": "electronics", "brand": "Samsung"}';
-- JSON 특정 키 값 검색
SELECT name, metadata->>'price' as price
FROM products
WHERE metadata->>'category' = 'electronics'
AND (metadata->>'price')::numeric < 500;
-- 전문 검색(Full-Text Search)용 GIN 인덱스
CREATE INDEX idx_articles_fts ON articles
USING GIN (to_tsvector('korean', title || ' ' || content));
SELECT title, ts_rank(to_tsvector('korean', content), query) as rank
FROM articles, to_tsquery('korean', '인공지능 & 머신러닝') query
WHERE to_tsvector('korean', title || ' ' || content) @@ query
ORDER BY rank DESC
LIMIT 20;PostgreSQL 17 신기능: JSON_TABLE
-- PostgreSQL 17의 JSON_TABLE 함수 (SQL 표준 준수)
-- JSON 배열을 관계형 테이블로 변환
SELECT *
FROM JSON_TABLE(
'[{"id":1,"name":"김철수","age":30},
{"id":2,"name":"이영희","age":25}]'::jsonb,
'$[*]'
COLUMNS (
id INTEGER PATH '$.id',
name TEXT PATH '$.name',
age INTEGER PATH '$.age'
)
) AS users;
-- 결과:
-- id | name | age
-- ---+--------+----
-- 1 | 김철수 | 30
-- 2 | 이영희 | 25
-- API 응답 JSON 저장 후 분석에 유용
SELECT
jt.product_id,
jt.product_name,
jt.price,
api_response.created_at
FROM api_responses,
JSON_TABLE(
api_response.payload,
'$.products[*]'
COLUMNS (
product_id INTEGER PATH '$.id',
product_name TEXT PATH '$.name',
price NUMERIC PATH '$.price'
)
) AS jt
WHERE api_response.source = 'product-api';상관 서브쿼리 최적화 (PostgreSQL 17)
-- PostgreSQL 17은 상관 IN 서브쿼리를 자동으로 JOIN으로 변환
-- 이전: 외부 행마다 서브쿼리 반복 실행
-- 이후: 한 번의 해시 조인으로 처리
-- 이 쿼리가 자동 최적화됨
SELECT * FROM users u
WHERE u.id IN (
SELECT DISTINCT user_id FROM orders
WHERE total_amount > 100000
);
-- 성능이 나쁠 경우 EXISTS로 수동 최적화
SELECT u.*
FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.id
AND o.total_amount > 100000
);
-- NOT EXISTS vs NOT IN (NULL 처리 차이)
SELECT u.*
FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.id
AND o.status = 'cancelled'
);
-- NOT IN은 서브쿼리에 NULL 있으면 0건 반환 위험CTE(공통 테이블 표현식) 최적화
-- MATERIALIZED vs NOT MATERIALIZED 선택
-- 기본: PostgreSQL 12+에서 비용 기반으로 자동 결정
-- 명시적 MATERIALIZED: 서브쿼리 결과를 한 번만 실행 (재사용)
WITH expensive_calc AS MATERIALIZED (
SELECT user_id, SUM(amount) as total
FROM transactions
WHERE created_at >= '2026-01-01'
GROUP BY user_id
)
SELECT u.name, ec.total,
ec.total / SUM(ec.total) OVER () * 100 as percentage
FROM users u
JOIN expensive_calc ec ON ec.user_id = u.id
WHERE ec.total > 1000000;
-- NOT MATERIALIZED: 인라인 확장으로 옵티마이저 최적화 허용
WITH recent_orders AS NOT MATERIALIZED (
SELECT * FROM orders WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
)
SELECT user_id, COUNT(*)
FROM recent_orders
WHERE status = 'completed'
GROUP BY user_id;
-- 재귀 CTE: 계층 구조 조회
WITH RECURSIVE category_tree AS (
-- 최상위 카테고리
SELECT id, name, parent_id, 0 as depth, name::TEXT as path
FROM categories WHERE parent_id IS NULL
UNION ALL
-- 하위 카테고리 재귀
SELECT c.id, c.name, c.parent_id, ct.depth + 1,
ct.path || ' > ' || c.name
FROM categories c
JOIN category_tree ct ON ct.id = c.parent_id
)
SELECT * FROM category_tree ORDER BY path;파티셔닝으로 대용량 테이블 관리
-- 날짜 기반 범위 파티셔닝
CREATE TABLE orders (
id BIGSERIAL,
user_id INTEGER NOT NULL,
total_amount NUMERIC(10,2),
status VARCHAR(20),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);
-- 월별 파티션 생성
CREATE TABLE orders_2026_01 PARTITION OF orders
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE orders_2026_02 PARTITION OF orders
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
-- 자동 파티션 생성 함수
CREATE OR REPLACE FUNCTION create_monthly_partition(target_date DATE)
RETURNS VOID AS
$$
DECLARE
partition_name TEXT;
start_date DATE;
end_date DATE;
BEGIN
start_date := DATE_TRUNC('month', target_date);
end_date := start_date + INTERVAL '1 month';
partition_name := 'orders_' || TO_CHAR(target_date, 'YYYY_MM');
EXECUTE format(
'CREATE TABLE IF NOT EXISTS %I PARTITION OF orders
FOR VALUES FROM (%L) TO (%L)',
partition_name, start_date, end_date
);
END;
$$ LANGUAGE plpgsql;
-- 파티션 프루닝 활용 (자동으로 해당 파티션만 스캔)
SELECT COUNT(*) FROM orders
WHERE created_at BETWEEN '2026-03-01' AND '2026-03-31';
-- orders_2026_03 파티션만 스캔정리
PostgreSQL 17은 JSON 처리, 쿼리 최적화, 복제 성능 모두에서 큰 발전을 이루었습니다. 핵심은 실행 계획 분석 후 적절한 인덱스 전략을 수립하는 것입니다. B-tree 기본 인덱스, GIN으로 JSON/전문검색, 커버링 인덱스로 불필요한 테이블 접근 제거, 파티셔닝으로 대용량 데이터 분리 관리 등을 체계적으로 적용하면 쿼리 성능을 크게 개선할 수 있습니다. EXPLAIN ANALYZE를 일상적으로 활용하여 느린 쿼리를 선제적으로 발견하고 개선하는 습관이 중요합니다.