SECTION 11 원문 백업

재편집 전 원문 백업 | SECTION 11 | 2026-10-14

원본: SECTION 11. SQL 성능과 실행계획 분석

PostgreSQL에서는 SQL 문법이 같아 보여도 자료형·NULL·파티션·트랜잭션·MVCC의 동작이 다를 수 있다.

개발 단계에서 이런 특성을 모르고 작성한 SQL은 성능뿐 아니라 결과 정확성·동시성·운영 안정성 문제를 만들 수 있다.

이번 SECTION은 SECTION 01. SQL REMINDER 소개과 원본 교재의 PostgreSQL 전용 실습을 사용한다. SECTION 02~10이 검색·JOIN·집계 작성법을 다뤘다면, 여기서는 PostgreSQL로 개발할 때 별도로 기억해야 하는 동작을 다룬다.

실행 여부와 근거

•

아래 예상 실행계획은 실측 결과가 아닌 개념적 형태다. cost, actual rows, 실행시간, Buffers 등의 값을 임의로 만들지 않는다.

•

오류·트랜잭션 격리·잠금·COMMIT은 일반적인 EXPLAIN으로 확인할 수 없으므로 예상 오류/세션 동작을 별도로 적는다.

•

tr_ord_big_p는 실습 교재에서 별도로 생성한 ord_dtm RANGE 파티션 테이블이다. tr_ord_big과 이름·구조를 혼동하지 않는다.

•

원본 교재에서 직접 확인되는 실습과 PostgreSQL 17 공식 문서를 근거로 보강한 내용을 항목별로 구분한다. DML·DDL·잠금 실습 코드는 운영 DB에서 그대로 실행하지 않는다.

이번 섹션의 핵심 규칙

ID	구분	기억할 규칙
11-01	MUST	문자형 컬럼을 숫자와 무심코 비교하지 말자
11-02	MUST	PostgreSQL DESC 정렬에서 NULL은 기본적으로 앞에 온다
11-03	MUST	파티션 키를 검색 가능한 범위 조건으로 작성하자
11-04	CHECK	파티션 키 없는 조인의 반복 접근을 확인하자
11-05	MUST	READ COMMITTED에서는 문장마다 읽는 시점이 달라질 수 있다
11-06	MUST	트랜잭션 오류 후에는 ROLLBACK 처리를 명확히 하자
11-07	MUST	SELECT 후 열린 트랜잭션도 DDL을 기다리게 할 수 있다
11-08	TIP	FOR UPDATE의 잠금 시간과 NOWAIT·SKIP LOCKED를 구분하자
11-09	MUST	불필요한 UPDATE로 Dead Tuple을 늘리지 말자
11-10	CHECK	통계와 AUTOVACUUM 상태를 점검하자

MUST: 결과 정확성·안정성을 위한 기본 규칙 / TIP: 필요한 업무에 적용할 기능 / CHECK: 실제 DB 상태와 계획 확인 필요

REMINDER 11-01. 문자형 컬럼을 숫자와 무심코 비교하지 말자

MUST · 상황: 실습에서 추가한 tr_ord_big.ord_ymd는 8자리 문자형 날짜(YYYYMMDD)다. 특정 일자의 주문 건수를 조회한다.

BAD — 자료형이 다른 숫자 리터럴과 직접 비교

SELECT COUNT(*) AS ord_cnt
FROM tr_ord_big AS o
WHERE o.ord_ymd = 20230401;

SQL

복사

BAD 우회 — 오류를 피하려고 컬럼을 numeric으로 캐스팅

SELECT COUNT(*) AS ord_cnt
FROM tr_ord_big AS o
WHERE o.ord_ymd::numeric = 20230401;

SQL

복사

GOOD — 컬럼과 맞는 자료형을 사용

SELECT COUNT(*) AS ord_cnt
FROM tr_ord_big AS o
WHERE o.ord_ymd = '20230401';

SQL

복사

예상 오류 및 실행계획

BAD 1: PostgreSQL에서 varchar = integer 비교 연산자를
       찾지 못해 SQL 오류가 발생할 수 있음 (계획 없음)

BAD 2 (ord_ymd 일반 B-tree 인덱스가 있어도 가능한 형태)
Aggregate
  -> Index Only Scan / Seq Scan on tr_ord_big
       Filter: ((ord_ymd)::numeric = 20230401)
  → 원래 ord_ymd 인덱스의 직접 검색 조건이 아님

GOOD (tr_ord_big_x05(ord_ymd) 선택 시)
Aggregate
  -> Index Only Scan using tr_ord_big_x05
       Index Cond: (ord_ymd = '20230401')

Plain Text

복사

기억할 이유: PostgreSQL은 문자 컬럼과 숫자 리터럴의 비교를 다른 DBMS처럼 항상 암묵적 변환으로 처리하지 않는다. 비교 값 쪽을 컬럼의 자료형과 맞추고, 인덱스 컬럼을 불필요하게 CAST하지 않는다.

주의: ord_ymd는 원본 ERD에는 표시되지 않고, 튜닝 연습을 위해 추가된 반정규화 컬럼이다. 일반적인 날짜 모델에서 문자를 저장하라는 권장이 아니다. 상세한 WHERE 컬럼 가공 규칙은 SECTION 03에서 배웠다.

교재 근거: PDF p.143, SQL-XPERF3-VARCHAR, SQL-CRIX-TR_ORD_BIG_X05.

REMINDER 11-02. DESC에서 NULLS LAST를 명시하자

MUST · 상황: 회원 M00019의 주문 중 가장 최근 픽업일시가 존재하는 주문을 한 건 보여준다.

BAD — NULL이 있으면 NULL 행이 맨 앞에 올 수 있음

SELECT o.ord_no, o.pkup_dtm
FROM tr_ord_big AS o
WHERE o.mbr_id = 'M00019'
ORDER BY o.pkup_dtm DESC, o.ord_no DESC
LIMIT 1;

SQL

복사

GOOD — 실제 픽업일시가 있는 주문만 조회

SELECT o.ord_no, o.pkup_dtm
FROM tr_ord_big AS o
WHERE o.mbr_id = 'M00019'
  AND o.pkup_dtm IS NOT NULL
ORDER BY o.pkup_dtm DESC NULLS LAST, o.ord_no DESC
LIMIT 1;

SQL

복사

예상 실행계획과 결과 차이

BAD / GOOD 모두 가능한 기본 형태
Limit
  -> Sort / Index Scan on tr_ord_big
       Sort Key: pkup_dtm DESC [NULLS FIRST 또는 LAST], ord_no DESC
  → 정렬을 지원하는 인덱스가 없다면 Sort 발생

결과 차이
BAD: NULL pkup_dtm 주문을 최신 1건으로 선택할 수 있음
GOOD: 실제 픽업일시가 있는 주문 중 최신 1건 선택

Plain Text

복사

기억할 이유: PostgreSQL에서 기본 DESC 정렬의 NULL 위치는 NULLS FIRST다. SQL을 읽는 개발자가 가장 늦은 값으로 착각하지 않도록 NULLS LAST를 명시하고, 업무상 NULL을 제외해야 하면 IS NOT NULL도 넣는다.

주의: GOOD은 업무 요구사항이 픽업일시가 실제로 존재하는 주문일 때만 맞다. NULL 주문도 목록에 필요하다면 IS NOT NULL을 임의로 추가하면 안 된다. NULLS LAST를 명시한 인덱스와 정렬 방향이 다르면 Sort가 발생할 수 있다.

자료 구분: BIG 모델의 pkup_dtm 컬럼은 PDF p.5에서 확인된다. DESC의 기본 NULL 정렬 규칙은 PostgreSQL 17 ORDER BY 공식 문서 기반 추가 보강 내용이다.

REMINDER 11-03. 파티션 키를 조건에 직접 사용하자

MUST · 상황: 월별 ord_dtm RANGE 파티션 테이블 tr_ord_big_p에서 2024년 3월 카드 결제 주문만 집계한다.

BAD — 파티션 키를 TO_CHAR로 가공

SELECT COUNT(*) AS ord_cnt
FROM tr_ord_big_p AS o
WHERE TO_CHAR(o.ord_dtm, 'YYYYMM') = '202403'
  AND o.pay_tp = 'CARD';

SQL

복사

GOOD — 파티션 키의 반열린 날짜 범위로 작성

SELECT COUNT(*) AS ord_cnt
FROM tr_ord_big_p AS o
WHERE o.ord_dtm >= TIMESTAMP '2024-03-01'
  AND o.ord_dtm <  TIMESTAMP '2024-04-01'
  AND o.pay_tp = 'CARD';

SQL

복사

예상 실행계획

BAD (파티션 가지치기가 어려운 경우)
Aggregate
  -> Append
       -> Seq Scan / Index Scan on tr_ord_big_p_p_min
       -> Seq Scan / Index Scan on tr_ord_big_p_p_202301
       -> ... 여러 월별 파티션 ...
       Filter: (TO_CHAR(ord_dtm, 'YYYYMM') = '202403')

GOOD (실제 월별 파티션이 정의되어 있을 때)
Aggregate
  -> Seq Scan / Index Scan on 2024년 3월 파티션
       Filter: (pay_tp = 'CARD')
       또는 ord_dtm을 활용한 Index Cond
  → 다른 월 파티션을 제외(Partition Pruning)할 수 있음

Plain Text

복사

기억할 이유: RANGE 파티션을 잘라 읽으려면 파티션 키의 비교 범위가 SQL에 명확해야 한다. TO_CHAR(ord_dtm,...)가 있으면 PostgreSQL이 파티션 경계를 사용해 제외하기 어렵다.

주의: tr_ord_big_p는 PDF p.312에서 PARTITION BY RANGE (ord_dtm)으로 생성하는 실습 테이블이다. 파티션 이름, 실제 생성된 월, 인덱스와 데이터 분포에 따라 노드 이름은 달라진다. 한 파티션의 대부분을 읽는다면 GOOD에서도 그 파티션의 Seq Scan이 올바른 선택일 수 있다.

교재 근거: PDF p.312~313, SQL-파티션-생성, SQL-파티션-조회1. 컬럼 TO_CHAR 가공은 PDF p.142의 인덱스 사례를 파티션 검색에 적용한 교육용 비교다.

REMINDER 11-04. 파티션 키 없는 조인에서는 반복 접근을 확인하자

CHECK · 상황: PLAT 회원의 전체 기간 주문금액을 회원별로 합산한다. 업무 요구상 기간 조건이 없으므로 임의 날짜 조건을 추가할 수 없다.

BAD 후보 — 회원을 Driving으로 NL JOIN해 파티션을 반복 조회하는 상황

SELECT m.mbr_id, SUM(o.ord_amt) AS ord_amt
FROM ms_mbr_big AS m
JOIN tr_ord_big_p AS o
  ON o.mbr_id = m.mbr_id
WHERE m.mbr_gd = 'PLAT'
  AND m.join_tp = 'INV'
  AND m.join_dtm < TIMESTAMP '2022-05-01'
GROUP BY m.mbr_id;

SQL

복사

GOOD 비교 후보 — 전체 주문을 한 번 집계한 뒤 필요한 회원과 JOIN

SELECT m.mbr_id, x.ord_amt
FROM ms_mbr_big AS m
JOIN (
  SELECT o.mbr_id, SUM(o.ord_amt) AS ord_amt
  FROM tr_ord_big_p AS o
  GROUP BY o.mbr_id
) AS x ON x.mbr_id = m.mbr_id
WHERE m.mbr_gd = 'PLAT'
  AND m.join_tp = 'INV'
  AND m.join_dtm < TIMESTAMP '2022-05-01';

SQL

복사

예상 실행계획 — 둘 다 모든 월 파티션을 대상으로 한다

BAD (회원 Driving NL JOIN을 선택한 경우)
HashAggregate
  -> Nested Loop
       -> ms_mbr_big 회원 필터
       -> Append
            -> Index Scan on 여러 월 파티션 (mbr_id 조건)
  → 파티션 키 제한이 없어 다수 파티션에 반복 접근 가능

GOOD (선집계가 먼저 수행되는 경우)
Hash Join
  -> 회원 필터 집합
  -> Hash
       -> HashAggregate (mbr_id)
            -> Append
                 -> 모든 월별 파티션을 한 번씩 스캔
  → 파티션 전체를 읽지만 반복 NL 접근을 피할 가능성

Plain Text

복사

기억할 이유: tr_ord_big_p가 ord_dtm을 기준으로 나뉘어 있는데 조인 조건이 mbr_id뿐이라면 해당 회원의 행이 여러 파티션에 흩어져 있을 수 있다. NL의 Driven에 파티션 테이블이 있으면 월별 인덱스를 여러 번 확인할 수 있다.

주의·예외: BAD SQL 텍스트 자체가 틀렸다는 의미는 아니다. PostgreSQL은 BAD에서도 Hash Join을 선택하거나 각 파티션 접근을 합리적으로 계획할 수 있다. GOOD 역시 회원 집합이 극히 적으면 불필요한 전량 집계가 더 비쌀 수 있다. 원본 PDF는 pg_hint_plan으로 NL을 강제한 특수 실습이며 기본 옵티마이저의 실제 결과라고 해석하면 안 된다. 전체 기간 업무에 성능을 이유로 날짜 조건을 임의로 추가해서도 안 된다.

교재 근거: PDF p.314, SQL-파티션-조회2. 선집계 GOOD은 원본의 직접 비교 SQL이 아니라 반복 파티션 접근 위험을 설명하기 위한 교육용 대안이다.

REMINDER 11-05. READ COMMITTED에서는 문장마다 결과가 달라질 수 있다

MUST · 상황: 트랜잭션 안에서 같은 회원의 상태를 두 번 조회한다. 그 사이 다른 세션이 회원 상태를 변경·COMMIT한다.

BAD — BEGIN만 하면 모든 SELECT가 같은 시점의 데이터를 본다고 가정

-- Session A: 하나의 트랜잭션
BEGIN;
SHOW TRANSACTION ISOLATION LEVEL;  -- 보통 read committed
SELECT mbr_st FROM ms_mbr_big WHERE mbr_id = 'M00019';

-- Session B가 같은 회원 상태를 변경하고 COMMIT한 뒤
SELECT mbr_st FROM ms_mbr_big WHERE mbr_id = 'M00019';
COMMIT;

SQL

복사

GOOD — 일관된 스냅샷이 필요한 업무에서는 격리 수준을 먼저 결정

-- Session A: 동일한 트랜잭션 안에서 일관된 스냅샷 필요
BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT mbr_st FROM ms_mbr_big WHERE mbr_id = 'M00019';
-- 그 사이 Session B가 변경하고 COMMIT했다고 가정
SELECT mbr_st FROM ms_mbr_big WHERE mbr_id = 'M00019';

COMMIT;

SQL

복사

예상 동작 — 실행계획 차이가 핵심이 아님

BAD (READ COMMITTED)
첫 SELECT: 해당 문장 시작 시 보이는 COMMIT 데이터 조회
다음 SELECT: 그다음 문장 시작 시의 새 스냅샷 사용
→ Session B가 그 사이 COMMIT했다면 값이 달라질 수 있음

GOOD (REPEATABLE READ)
같은 트랜잭션에서 일반 SELECT는 동일 스냅샷을 기준으로 읽음
→ 그 사이 다른 트랜잭션이 COMMIT한 변경을 보지 않을 수 있음

두 경우의 예상 조회 노드
Index Scan on ms_mbr_big (mbr_id = 'M00019') 또는 Seq Scan
→ 실행계획만 봐서는 스냅샷 차이를 판정할 수 없음

Plain Text

복사

기억할 이유: PostgreSQL의 기본 격리 수준은 READ COMMITTED다. 한 트랜잭션 안이라도 서로 다른 SQL 문장 사이에 다른 세션의 COMMIT이 개입할 수 있다.

주의·예외: REPEATABLE READ를 모든 업무에 적용하라는 권고는 아니다. 동시 수정·충돌 처리, 재시도 로직, 잠금 유지 시간까지 고려해야 한다. 애플리케이션 커넥션 풀에서는 트랜잭션 상태와 격리 수준의 시작 위치가 중요하다. 위 Session B의 변경은 설명을 위한 가정이며 이 페이지에서는 UPDATE를 실행하지 않았다.

교재 근거: PDF p.318~320(격리 수준·자동 COMMIT). 상세한 스냅샷 설명은 PostgreSQL 17 Transaction Isolation 공식 문서를 근거로 보강했다.

REMINDER 11-06. 트랜잭션에서 오류가 나면 ROLLBACK 처리를 하자

MUST · 상황: 수동 트랜잭션으로 여러 SQL을 실행하다 한 문장에서 오류가 발생한다.

BAD — 실패 후 같은 트랜잭션에서 다음 SQL을 그대로 실행

-- 오류 동작을 설명하기 위한 데모. 그대로 실행하면 에러 발생.
BEGIN;
SELECT 1 / 0;    -- division by zero
SELECT COUNT(*) FROM ms_mbr_big; -- 트랜잭션 오류 상태
COMMIT;

SQL

복사

GOOD — 오류를 처리하고 트랜잭션을 종료한 뒤 재시도

-- 애플리케이션의 예외 처리 흐름을 SQL로 표현
BEGIN;
-- 여러 SELECT / DML을 실행한다.
-- 중간에 오류가 발생했다면:
ROLLBACK;

-- 필요한 경우 새 트랜잭션으로 처음부터 재시도
BEGIN;
SELECT COUNT(*) FROM ms_mbr_big;
COMMIT;

SQL

복사

예상 오류·동작

BAD
SELECT 1 / 0
  ERROR: division by zero

그 뒤 같은 트랜잭션의 SELECT
  ERROR: current transaction is aborted,
         commands ignored until end of transaction block

COMMIT 요청은 실패한 트랜잭션을 성공 상태로 되돌리지 못함
ROLLBACK으로 실패한 트랜잭션을 끝내야 함

GOOD
ROLLBACK -> 오류 트랜잭션 종료
새 BEGIN -> 정상적인 새 트랜잭션 시작 가능

Plain Text

복사

기억할 이유: 명시적으로 시작한 PostgreSQL 트랜잭션에서 문장 오류가 발생하면 이후 SQL을 계속할 수 없는 실패 상태가 된다. 연결을 계속 쓰는 애플리케이션은 예외 처리에서 ROLLBACK을 보장해야 한다.

주의·예외: 모든 중간 오류에서 전체 거래를 취소해야 하는지는 업무 정책에 따른다. 부분 복구가 명확히 필요한 경우 사전에 SAVEPOINT를 두고 ROLLBACK TO SAVEPOINT하는 방식도 있다. DBeaver의 Manual Commit과 애플리케이션 드라이버의 자동 COMMIT은 다르게 설정될 수 있다.

교재 근거: PDF p.316(에러가 발생해도 자동 ROLLBACK이 수행되지 않음), p.320(자동 COMMIT). 오류 코드·실패 상태의 설명은 PostgreSQL 일반 트랜잭션 동작을 교육용으로 구체화했다.

REMINDER 11-07. SELECT 뒤에 열린 트랜잭션도 DDL을 막을 수 있다

MUST · 상황: 세션 A가 트랜잭션을 시작하고 조회만 한 채 방치했다. 세션 B는 같은 테이블에 컬럼을 추가하려 한다.

BAD — 조회 후 트랜잭션을 종료하지 않음

-- 설명용 별도 실습 테이블(sql_reminder_tx_demo)이
-- 이미 생성되어 있다고 가정. 운영 테이블 사용 금지!

-- Session A
BEGIN;
SELECT id FROM sql_reminder_tx_demo LIMIT 1;
-- COMMIT/ROLLBACK 없이 트랜잭션을 열어 둠

-- Session B (별도 연결)
ALTER TABLE sql_reminder_tx_demo ADD COLUMN note text;
-- Session A가 종료될 때까지 대기할 수 있음

SQL

복사

GOOD — 트랜잭션 범위를 필요한 만큼만 유지

-- Session A
BEGIN;
SELECT id FROM sql_reminder_tx_demo LIMIT 1;
COMMIT;  -- 조회 완료 후 종료

-- Session B (Session A 종료 후)
BEGIN;
ALTER TABLE sql_reminder_tx_demo ADD COLUMN note text;
COMMIT;  -- DDL도 트랜잭션에 포함됨

SQL

복사

예상 잠금 동작

BAD
Session A의 SELECT
  → 테이블 ACCESS SHARE 락을 트랜잭션 종료까지 유지
Session B의 ALTER TABLE
  → ACCESS EXCLUSIVE 락 요청이 충돌하여 WAIT 가능

GOOD
Session A COMMIT → 해당 테이블 락 해제
Session B ALTER TABLE → 다른 충돌 락이 없으면 진행
Session B COMMIT → DDL 변경 확정

Plain Text

복사

기억할 이유: PostgreSQL에서는 명시적 트랜잭션 안의 SELECT도 락을 보유할 수 있다. 또한 일반적인 ALTER TABLE 등 DDL도 트랜잭션과 함께 COMMIT/ROLLBACK된다. 조회와 DDL을 같은 연결에서 장시간 묶지 않는다.

주의: 위 예제는 임시로 만든 별도 테스트 테이블에만 적용할 시나리오다. 실제 운영 BIG 테이블에 ALTER를 실행하지 않는다. 모든 DDL이 똑같은 락을 요구하는 것은 아니며, CREATE INDEX CONCURRENTLY처럼 일반 트랜잭션 블록에서 실행할 수 없는 명령도 있다. DDL의 필요 락은 개별 명령마다 확인한다.

교재 근거: PDF p.329, SQL-TRAN-DDL-S1/S2. 실제 교재는 m_acc 테이블의 SELECT 후 COMMIT 누락과 ALTER 대기를 두 세션에서 실습한다.

REMINDER 11-08. FOR UPDATE 잠금의 대기 정책을 명시하자

TIP · 상황: 다른 세션이 회원 한 건을 변경하고 있을 때 행 잠금을 획득하려 한다.

BAD — Oracle식 WAIT 5 구문을 PostgreSQL에 그대로 사용

SELECT m.mbr_id, m.mbr_st
FROM ms_mbr_big AS m
WHERE m.mbr_id = 'M00019'
FOR UPDATE WAIT 5;  -- PostgreSQL에서 지원하지 않는 구문

SQL

복사

GOOD — PostgreSQL lock_timeout과 FOR UPDATE 사용

-- 독립된 실습 트랜잭션에서만 실행
BEGIN;
SET LOCAL lock_timeout = '5s';

SELECT m.mbr_id, m.mbr_st
FROM ms_mbr_big AS m
WHERE m.mbr_id = 'M00019'
FOR UPDATE;

COMMIT;

SQL

복사

대기 정책에 따라 선택할 다른 방식

-- 즉시 락을 얻지 못하면 오류 (NOWAIT)
SELECT m.mbr_id FROM ms_mbr_big AS m
WHERE m.mbr_id = 'M00019'
FOR UPDATE NOWAIT;

-- 이미 잠긴 행을 건너뛰고 다음 행을 처리 (SKIP LOCKED)
SELECT m.mbr_id FROM ms_mbr_big AS m
WHERE m.mbr_st = 'INAC'
FOR UPDATE SKIP LOCKED;

SQL

복사

예상 오류·실행계획

BAD: FOR UPDATE WAIT 5는 PostgreSQL 문법 오류

GOOD (잠금을 얻는 정상 경우의 가능한 형태)
LockRows
  -> Index Scan / Seq Scan on ms_mbr_big
       Index Cond 또는 Filter: (mbr_id = 'M00019')

다른 세션이 해당 행을 잠근 상태라면
- 기본 FOR UPDATE : 잠금 해제까지 기다릴 수 있음
- lock_timeout      : 설정된 대기 시간 초과 시 오류
- NOWAIT            : 즉시 오류
- SKIP LOCKED       : 잠긴 행을 반환 대상에서 제외

Plain Text

복사

기억할 이유: 행 잠금은 DB에서 대기 시간을 만들 수 있다. FOR UPDATE가 언제 시작되는지, 어떤 잠금을 기다릴지, 실패·재시도 정책은 무엇인지 애플리케이션에서 명확하게 결정한다.

주의: SKIP LOCKED는 처리 대기열처럼 다른 작업자가 잡은 행을 건너뛰어도 되는 업무에 적합하다. 계좌 잔액·권한 확인처럼 반드시 특정 행을 읽어야 하는 조회에 쓰면 결과를 누락할 수 있다. SET LOCAL lock_timeout은 현재 트랜잭션에만 적용된다. 잠금 타임아웃으로 SQL 오류가 발생했다면 트랜잭션 롤백 처리가 필요하다. 예제는 실제 잠금 측정을 수행하지 않았다.

교재 근거: PDF p.327~328, SQL-TRAN-FOR-S1/S2, FOR UPDATE NOWAIT, SKIP LOCKED, lock_timeout.

REMINDER 11-09. 불필요한 UPDATE를 줄여 Dead Tuple을 최소화하자

MUST · 상황: 매일 실행되는 배치가 이미 ACTV인 회원까지 동일한 상태로 UPDATE한다.

BAD — 값이 같아도 대상 회원을 UPDATE

-- 설명용 SQL. 실제 BIG 테이블에 실행하지 않는다.
UPDATE ms_mbr_big AS m
SET mbr_st = 'ACTV'
WHERE m.mbr_id = 'M00019';

SQL

복사

GOOD — 실제 값이 달라야만 UPDATE

UPDATE ms_mbr_big AS m
SET mbr_st = 'ACTV'
WHERE m.mbr_id = 'M00019'
  AND m.mbr_st IS DISTINCT FROM 'ACTV';

SQL

복사

예상 실행계획 및 변경 동작

BAD / GOOD 모두 가능한 DML 기본 형태
Update on ms_mbr_big
  -> Index Scan / Seq Scan on ms_mbr_big
       Index Cond 또는 Filter: (mbr_id = 'M00019')

BAD: 이미 ACTV이더라도 PostgreSQL UPDATE는
     기존 행 버전을 대체하는 새 행 버전을 만들 수 있음

GOOD: 현재 상태가 ACTV라면 0건 UPDATE
      실제 상태가 다르면 해당 행을 UPDATE
→ 불필요한 새 행 버전·WAL·인덱스 유지 작업을 줄일 가능성

Plain Text

복사

기억할 이유: PostgreSQL은 MVCC에 따라 UPDATE 시 새로운 행 버전을 생성하고, 더는 필요 없는 예전 버전은 VACUUM의 정리 대상이 될 수 있다. 애플리케이션에서 변경할 필요 없는 행까지 UPDATE하지 않는 습관이 중요하다.

주의·예외: 이 변경은 최종 mbr_st 값이 같더라도 UPDATE 건수, UPDATE 트리거 실행 여부, 감사 로그, 갱신시각 등 부수 효과가 달라질 수 있다. 이런 동작까지 업무 요구사항에 포함된다면 단순히 변경 조건을 붙여서는 안 된다. IS DISTINCT FROM은 NULL도 비교 대상으로 처리한다. Dead Tuple이 자동으로 즉시 제거된다는 뜻이 아니며, 오래 유지되는 트랜잭션은 VACUUM 정리를 지연시킬 수 있다.

교재 근거: PDF p.331(Update/DELETE와 Dead Tuple), p.336~337(UPDATE에 의한 용량 변화와 Dead Tuple 최소화). IS DISTINCT FROM을 이용한 무변경 UPDATE 생략 예제는 일반 PostgreSQL 기능을 적용한 교육용 보강이다.

REMINDER 11-10. 통계·AUTOVACUUM 상태를 확인하자

CHECK · 상황: 회원상태 INAC는 매우 적고 ACTV는 많다. 같은 인덱스가 있는데도 두 조건의 실행계획이 다르다.

BAD — 값의 분포를 무시하고 '인덱스가 있으니 항상 Index Scan'이라고 판단

-- 같은 인덱스 ms_mbr_big_x11(mbr_st)을 전제
SELECT m.mbr_gd, COUNT(*) AS cnt
FROM ms_mbr_big AS m
WHERE m.mbr_st = 'ACTV'
GROUP BY m.mbr_gd;

SQL

복사

GOOD — 통계·유지보수 상태를 확인한 다음 SQL 계획 비교

-- 값의 분포를 확인 (교재의 pg_stats 예제)
SELECT tablename, attname, n_distinct,
       most_common_vals, most_common_freqs
FROM pg_stats
WHERE schemaname = current_schema()
  AND tablename = 'ms_mbr_big'
  AND attname = 'mbr_st';

-- VACUUM / ANALYZE 상황을 확인
SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = current_schema()
  AND relname = 'ms_mbr_big';

-- 실제 실행하지 않고 두 조건의 예상 계획만 비교
EXPLAIN (COSTS OFF)
SELECT m.mbr_gd, COUNT(*) AS cnt
FROM ms_mbr_big AS m
WHERE m.mbr_st = 'INAC'
GROUP BY m.mbr_gd;

EXPLAIN (COSTS OFF)
SELECT m.mbr_gd, COUNT(*) AS cnt
FROM ms_mbr_big AS m
WHERE m.mbr_st = 'ACTV'
GROUP BY m.mbr_gd;

SQL

복사

예상 실행계획 — 실제 교재의 분포 상황을 전제로 한 가능성

INAC (극히 일부)
HashAggregate / GroupAggregate
  -> Index Scan using ms_mbr_big_x11
       Index Cond: (mbr_st = 'INAC')

ACTV (대부분)
HashAggregate / GroupAggregate
  -> Seq Scan on ms_mbr_big
       Filter: (mbr_st = 'ACTV')

이 차이는 인덱스의 존재 유무보다
값 분포·통계·읽어야 할 비율에 의해 발생할 수 있음

Plain Text

복사

통계가 오래되었거나 대량 데이터를 적재한 경우에 검토할 관리 작업

-- 아래 명령은 실제 통계를 갱신한다.
-- 운영 권한과 배포 절차를 확인한 뒤 담당자와 협의하여 사용
ANALYZE ms_mbr_big;

-- 자동 관리 기능의 활성 상태 확인
SHOW autovacuum;

SQL

복사

기억할 이유: PostgreSQL의 옵티마이저는 통계와 값의 분포를 참고해 Index Scan·Seq Scan을 결정한다. 또한 VACUUM은 Dead Tuple 재활용과 visibility map 유지에 관여하며, ANALYZE는 계획에 사용할 통계를 갱신한다.

주의·예외: n_live_tup, n_dead_tup은 정확한 실시간 행 수가 아닌 추정·누적 통계다. ANALYZE를 수행해도 원하는 인덱스가 반드시 선택되는 것은 아니다. 파티션 부모 테이블은 자식 파티션의 변경만으로 부모 ANALYZE가 자동 실행되지 않을 수 있어 별도 계획이 필요하다. VACUUM FULL은 강한 락과 테이블 재작성 비용이 있어 임의 실행하면 안 된다. autovacuum_enabled=false는 원본 교재의 격리된 성능 테스트용 설정이지 운영 권장 사항이 아니다.

교재 근거: PDF p.284 SQL-맛튜닝-값분포1/2, p.331~338(VACUUM·Autovacuum 설정과 Dead Tuple). 파티션 부모 통계 관리와 VACUUM의 유지보수 범위는 PostgreSQL 17 Routine Vacuuming 공식 문서에 근거해 추가했다.

PostgreSQL 개발자가 특히 구분해야 할 동작

대상	SQL 실행계획으로 확인할 항목	별도 확인해야 할 항목
자료형·NULL	Index Cond / Filter / Sort	형변환 오류·NULL 정렬·결과 정확성
파티션	Append / 파티션 Scan / Subplans Removed	실제 파티션 범위·조인 대상·인덱스 구성
트랜잭션	Index Scan 등 조회 경로	격리 수준·스냅샷·COMMIT/ROLLBACK 상태
잠금	LockRows·실제 실행 시 대기	다른 세션과의 경합·lock_timeout·락 획득 시점
MVCC·통계	Index/Seq Scan·계획 행 수	Dead Tuple·AUTOVACUUM·ANALYZE·실측 Buffers

실행계획은 매우 유용하지만 트랜잭션의 성공 여부나 업무상 결과의 정확성까지 검증하지는 않는다. 이번 SECTION은 SECTION 12의 실행계획 자가 점검과 연결되는 사전 지식이다.

실습 환경에서 우선 확인할 읽기 전용 SQL

-- 1. 현재 데이터베이스·세션 정보
SELECT current_database() AS database_name,
       current_schema() AS schema_name,
       current_setting('server_version') AS pg_version;

-- 2. 현재 트랜잭션 격리 수준
SHOW TRANSACTION ISOLATION LEVEL;

-- 3. 현재 DB의 파티션 정의 확인 (tr_ord_big_p가 있을 때)
SELECT parent.relname AS parent_table,
       child.relname AS partition_table,
       pg_get_expr(child.relpartbound, child.oid) AS partition_bound
FROM pg_inherits AS inh
JOIN pg_class AS parent ON parent.oid = inh.inhparent
JOIN pg_class AS child ON child.oid = inh.inhrelid
WHERE inh.inhparent = 'tr_ord_big_p'::regclass
ORDER BY child.relname;

SQL

복사

tr_ord_big_p는 원본 실습에서 별도로 생성하므로, 실제 DB에 존재해야 3번 파티션 정의 조회를 실행할 수 있다.

예상 실행계획 확인 예제

-- 실제 쿼리 결과를 읽지 않고 계획만 확인
EXPLAIN (COSTS OFF)
SELECT COUNT(*) AS ord_cnt
FROM tr_ord_big_p AS o
WHERE o.ord_dtm >= TIMESTAMP '2024-03-01'
  AND o.ord_dtm < TIMESTAMP '2024-04-01';

-- 실제 실행·측정은 테스트 데이터가 있는 환경에서만
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT COUNT(*) AS ord_cnt
FROM tr_ord_big_p AS o
WHERE o.ord_dtm >= TIMESTAMP '2024-03-01'
  AND o.ord_dtm < TIMESTAMP '2024-04-01';

SQL

복사

EXPLAIN ANALYZE는 SQL을 실제 실행한다. DML·DDL과 잠금 명령은 운영 환경에서 연습하지 않는다.

개발 전 체크리스트

SQL의 비교 값이 컬럼 자료형과 일치하는가?

ORDER BY ... DESC의 NULL 정렬을 업무 요구사항에 맞게 명시했는가?

파티션 테이블 검색에 실제 파티션 키 조건이 있는가?

파티션 키 없는 JOIN에서 모든 파티션 반복 접근을 확인했는가?

READ COMMITTED에서 두 SELECT 결과가 달라질 가능성을 고려했는가?

트랜잭션 중 오류 발생 시 ROLLBACK 경로가 보장되는가?

조회만 하는 트랜잭션도 불필요하게 오래 열어 두지 않는가?

FOR UPDATE의 잠금 대기·NOWAIT·SKIP LOCKED가 업무 요구에 맞는가?

이미 같은 값인 데이터를 불필요하게 UPDATE하지 않는가?

변경 없는 UPDATE를 생략할 때 트리거·감사 로그에 영향이 없는가?

대량 적재·UPDATE 이후 통계의 최신성을 점검했는가?

n_dead_tup과 AUTOVACUUM 상태를 담당 DBA와 확인할 수 있는가?

예상 실행계획과 실제 트랜잭션·잠금 동작을 구분했는가?

출처 및 재구성 범위

제공된 PostgreSQL 교재·실습에서 직접 확인한 사항

•

StartUPTuning_PG_20250616.pdf p.143 — ord_ymd 문자형과 숫자 비교 오류, 컬럼 캐스팅 시 인덱스 활용.

•

p.284 — INAC와 ACTV의 값 분포, pg_stats 조회와 인덱스·Seq Scan 선택.

•

p.308~314 — tr_ord_big_p 파티션 구조, PARTITION BY RANGE(ord_dtm), 파티션별 Scan·파티션 키 없는 NL JOIN.

•

p.316~329 — 트랜잭션, READ COMMITTED, 자동 COMMIT, 두 세션의 WAIT, FOR UPDATE NOWAIT/SKIP LOCKED, DDL의 트랜잭션 포함.

•

p.331~338 — MVCC·Dead Tuple·VACUUM·AUTOVACUUM·ANALYZE 관련 기초.

•

StartUPTuning_PG_SQL_Script.sql 및 StartUPTuning_PG_SQL_Script_실습용.sql — 관련 실습 구문과 파티션·트랜잭션 SQL을 참고.

•

 — 운영 환경의 쿼리 성능 튜닝 사례 모음. 이번 섹션의 트랜잭션·MVCC 예제들을 그 사례의 실제 실측 결과로 주장하지 않는다.

별도 PostgreSQL 일반 동작을 확인·보강한 사항

•

PostgreSQL 17: Sorting Rows — DESC에서의 NULLS FIRST 기본 동작.

•

PostgreSQL 17: Transaction Isolation — READ COMMITTED 문장별 스냅샷과 REPEATABLE READ.

•

PostgreSQL 17: Routine Vacuuming — VACUUM/ANALYZE, visibility map, 파티션 부모 통계, VACUUM FULL의 강한 락.

•

IS DISTINCT FROM을 통한 무변경 UPDATE 생략, pg_inherits 조회 등은 개발자가 활용할 수 있도록 추가한 교육용 SQL이다.

실행 여부: 전체 BAD/GOOD SQL 및 실행계획은 실습 자료의 전제를 반영해 재구성했으며, 실제로 실행한 SQL·실측 계획·성능 향상 수치는 없다. 일부 코드는 의도적으로 오류·락 대기를 보여주므로 테스트용으로만 참고한다.

그림 제작 메모 — 이미지 미삽입

•

그림 A · 자료형 오류와 인덱스: varchar = integer 오류 → 컬럼 캐스팅 Filter → 올바른 문자 비교 Index Cond의 3단 비교

•

그림 B · NULL 정렬: DESC에서 NULL이 앞에 오는 BAD와 DESC NULLS LAST의 순서 비교

•

그림 C · 파티션 Pruning: TO_CHAR로 모든 월 파티션에 접근하는 경로와 ord_dtm 범위로 한 달만 스캔하는 경로

•

그림 D · 파티션 Driven 반복: 회원 N명이 월별 파티션 전부를 반복 탐색하는 NL 구조

•

그림 E · 트랜잭션 격리: 두 세션의 COMMIT 시점과 READ COMMITTED/REPEATABLE READ의 스냅샷 차이

•

그림 F · 잠금과 DDL: SELECT의 미종료 트랜잭션이 ALTER TABLE을 기다리게 만드는 타임라인

•

그림 G · MVCC: UPDATE 전후 행 버전과 Dead Tuple, VACUUM 정리·ANALYZE 통계 갱신의 역할 비교

SECTION 11 한 줄 요약: 자료형·NULL·파티션·트랜잭션·MVCC의 PostgreSQL 동작을 알아야 SQL이 실행되는 것 이상으로 정확하고 안정적인 프로그램을 만들 수 있다.