PostgreSQL Dead Tuple 완벽 이해: Duplicate Key와 제약 조건 에러의 숨겨진 차이

블로그 내용

PostgreSQL에서 Dead Tuple은 DELETE나 UPDATE에 의해 생성된다고 많이 알려져 있습니다. 하지만 놀랍게도, Duplicate Key 에러에서도 Dead Tuple이 발생하는 반면, 자릿수 초과나 도메인 제약 조건 위반에서는 Dead Tuple이 생성되지 않는 사실을 아시나요? 이번 글에서는 이러한 차이를 자세히 살펴보며 PostgreSQL의 MVCC(Multi-Version Concurrency Control)와 관련된 동작 원리를 설명해드리겠습니다.

1. PostgreSQL의 MVCC와 Dead Tuple

PostgreSQL은 MVCC를 사용하여 데이터의 동시성 및 트랜잭션 격리성을 보장합니다. MVCC의 핵심은 동일한 데이터에 대해 여러 버전의 tuple을 유지하는 것입니다.

• INSERT: 새로운 데이터를 삽입하면서 새로운 tuple이 생성됩니다.
• UPDATE: 기존 tuple을 "삭제 상태"로 표시하고 새로운 tuple을 삽입합니다.
• DELETE: tuple을 "삭제 상태"로 표시합니다.

이처럼, 삭제된 것처럼 보이는 tuple이 Dead Tuple입니다.

2. Duplicate Key 에러와 Dead Tuple 생성

Duplicate Key 에러는 PRIMARY KEY 또는 UNIQUE 제약 조건 위반 시 발생하며, 이 경우 Dead Tuple이 생성됩니다. 그 이유는 PostgreSQL이 트랜잭션 내에서 새로운 데이터를 삽입하려고 새로운 tuple을 생성하지만, 중복된 키로 인해 삽입이 실패하고 해당 tuple이 롤백되기 때문입니다. 이때 생성된 tuple은 Dead Tuple로 남게 됩니다.

예시: Duplicate Key 에러 발생

CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    value TEXT
);

BEGIN; -- 트랜잭션 시작
INSERT INTO test_table (id, value) VALUES (1, 'A');  -- 데이터 삽입

-- 다른 세션에서 같은 키로 데이터 삽입 시도
BEGIN;
INSERT INTO test_table (id, value) VALUES (1, 'B');  -- duplicate key 에러 발생

이 경우, 첫 번째 트랜잭션이 삽입한 데이터가 Dead Tuple로 남게 됩니다.

3. 자릿수 초과와 도메인 에러에서 Dead Tuple이 생성되지 않는 이유

자릿수 초과, 타입 불일치, 도메인 제약 조건 위반은 데이터가 물리적으로 테이블에 기록되기 전에 발생합니다. 이 과정에서 tuple이 생성되지 않기 때문에 Dead Tuple이 생기지 않습니다.

예시: 자릿수 초과

CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    value NUMERIC(5, 2) -- 최대 5자리, 소수점 이하 2자리
);

INSERT INTO test_table (value) VALUES (123456.78); -- 자릿수 초과 에러 발생

PostgreSQL은 데이터가 테이블에 기록되기 전에 제약 조건을 먼저 검증합니다. 자릿수 초과 에러가 발생하면, 데이터는 저장되지 않고 트랜잭션이 롤백되며, 이때 Dead Tuple이 생성되지 않습니다.

예시: 도메인 제약 조건 위반

CREATE DOMAIN pos_int AS INTEGER CHECK (VALUE > 0);

CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    value pos_int
);

INSERT INTO test_table (value) VALUES (-10); -- 도메인 제약 조건 위반 에러 발생

도메인 제약 조건 역시 데이터가 테이블에 기록되기 전 검증되므로, 위반된 데이터는 저장되지 않으며 Dead Tuple이 생성되지 않습니다.

4. Duplicate Key 에러와 자릿수 초과/도메인 에러 비교

구분	Duplicate Key 에러	자릿수 초과/도메인 에러
검증 단계	데이터 저장 후 UNIQUE/PK 검증	데이터 저장 전 제약 조건 검증
Dead Tuple 발생	임시로 저장된 데이터가 롤백되며 Dead Tuple 생성	데이터 저장되지 않음, Dead Tuple 없음
발생 원인	MVCC로 인해 새로운 tuple 생성 후 제약 조건 충돌	데이터가 테이블에 기록되지 않음

5. Dead Tuple 최소화 방법

• Duplicate Key 방지: ON CONFLICT 구문을 사용하여 Dead Tuple 생성 가능성을 줄일 수 있습니다.

INSERT INTO test_table (id, value)
VALUES (1, 'B')
ON CONFLICT (id) DO NOTHING;

• VACUUM을 통한 Dead Tuple 관리: Dead Tuple이 누적되지 않도록 autovacuum을 최적화하거나 수동으로 VACUUM을 실행합니다.

• 제약 조건 사전 검증: 트랜잭션 설계 시 데이터 충돌 및 제약 조건 위반 가능성을 사전에 최소화합니다.

6. Duplicate Key 에러와 제약 조건 비교하기

Step 1: 환경 설정

CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    value NUMERIC(5, 2) -- 최대 5자리, 소수점 이하 2자리
);

Step 2: Duplicate Key 에러 확인

BEGIN;
INSERT INTO test_table (id, value) VALUES (1, 123.45);

-- 다른 세션에서 동일 키 삽입 시도
BEGIN;
INSERT INTO test_table (id, value) VALUES (1, 678.90); -- duplicate key 에러

Step 3: 자릿수 초과 에러 확인

INSERT INTO test_table (value) VALUES (123456.78); -- 자릿수 초과 에러

Step 4: 도메인 제약 조건 에러 확인

CREATE DOMAIN pos_int AS INTEGER CHECK (VALUE > 0);
ALTER TABLE test_table ADD COLUMN domain_value pos_int;

INSERT INTO test_table (domain_value) VALUES (-10); -- 도메인 제약 조건 위반 에러

7. Dead Tuple 관리 포인트와 해결책

Dead Tuple은 PostgreSQL 성능 최적화에 있어 중요한 요소입니다. 이를 효과적으로 관리하면 스토리지 효율성과 쿼리 성능을 크게 개선할 수 있습니다. 아래에서는 Dead Tuple을 줄이고, 관리하기 위한 주요 방법과 함께 예제 쿼리를 소개합니다.

1. ON CONFLICT 구문 활용

Duplicate Key 에러로 인해 Dead Tuple이 생성되는 것을 방지하려면 ON CONFLICT 구문을 사용하세요. 이를 통해 충돌 상황에서 새로운 데이터를 삽입하지 않거나 기존 데이터를 업데이트하도록 처리할 수 있습니다.

예제: ON CONFLICT를 사용한 Dead Tuple 방지

INSERT INTO test_table (id, value)
VALUES (1, 'B')
ON CONFLICT (id) DO NOTHING;

또는, 충돌 시 데이터를 업데이트:

INSERT INTO test_table (id, value)
VALUES (1, 'Updated Value')
ON CONFLICT (id)
DO UPDATE SET value = EXCLUDED.value;

2. AUTOVACUUM 최적화

Dead Tuple이 누적되지 않도록 PostgreSQL의 autovacuum을 적절히 설정하세요. autovacuum이 비활성화되었거나 설정이 부적절하면 Dead Tuple이 쌓여 성능이 저하될 수 있습니다.

권장 autovacuum 설정:

• postgresql.conf 파일에서 다음 매개변수를 조정합니다:

autovacuum_vacuum_cost_limit = 2000   # autovacuum의 리소스 한계 설정
autovacuum_vacuum_scale_factor = 0.05 # 테이블 데이터 5% 변경 시 VACUUM 실행
autovacuum_analyze_scale_factor = 0.02 # 테이블 데이터 2% 변경 시 ANALYZE 

--변경 후 PostgreSQL 재시작
sudo systemctl restart postgresql

3. 수동 VACUUM 실행

Dead Tuple이 많아져 성능 저하가 발생하면, 수동으로 VACUUM 또는 VACUUM FULL을 실행하여 테이블을 최적화하세요.

예제: Dead Tuple 정리

VACUUM test_table; -- Dead Tuple 정리
VACUUM FULL test_table; -- 공간 재사용 최적화

-- ANALYZE와 함께 실행:
VACUUM ANALYZE test_table; -- 쿼리 플래너 최적화

4. FILLFACTOR 활용

FILLFACTOR를 설정하면 테이블 페이지에 여유 공간을 남겨 UPDATE와 같은 연산에서 Dead Tuple 생성을 줄일 수 있습니다.

예제: FILLFACTOR 설정

ALTER TABLE test_table SET (FILLFACTOR = 70);

• FILLFACTOR는 페이지 공간의 최대 사용 비율(%)을 설정하며, 기본값은 100입니다.

5. 제약 조건 미리 검증

애플리케이션 로직에서 데이터 검증을 사전에 수행해 제약 조건 위반으로 Dead Tuple이 생성되는 상황을 줄입니다.

예제: INSERT 전에 값 검증

DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM test_table WHERE id = 1
    ) THEN
        RAISE NOTICE 'Duplicate Key Detected!';
    ELSE
        INSERT INTO test_table (id, value) VALUES (1, 'Validated Data');
    END IF;
END;
$$;

6. HOT (Heap-Only Tuple) 최적화

HOT 업데이트를 활용하면 Dead Tuple을 줄일 수 있습니다. 이 기능은 테이블의 동일 페이지 내에서 수정이 이루어질 때 Dead Tuple 생성을 최소화합니다.

HOT 업데이트가 가능한 경우:

• 인덱스 필드를 수정하지 않을 때
• 충분한 페이지 여유 공간이 있을 때

7. Dead Tuple 모니터링

pg_stat_user_tables 뷰를 활용하여 Dead Tuple의 상태를 주기적으로 점검하세요.

예제: Dead Tuple 상태 확인

SELECT
    relname AS table_name,
    n_dead_tup AS dead_tuple_count,
    n_live_tup AS live_tuple_count,
    pg_size_pretty(pg_relation_size(relid)) AS table_size
FROM
    pg_stat_user_tables
WHERE
    n_dead_tup > 0
ORDER BY
    n_dead_tup DESC;

8. 분석 및 리포트

Dead Tuple이 많은 테이블은 주기적으로 분석하여 적절한 VACUUM 주기를 설정하거나 테이블 재구성을 고려합니다.

예제: Dead Tuple 비율 계산

SELECT
    relname AS table_name,
    n_dead_tup AS dead_tuple_count,
    n_live_tup AS live_tuple_count,
    ROUND((n_dead_tup::NUMERIC / (n_live_tup + n_dead_tup)) * 100, 2) AS dead_tuple_ratio
FROM
    pg_stat_user_tables
WHERE
    n_dead_tup > 0;

결론
Dead Tuple 관리의 핵심은 사전 예방과 주기적 점검입니다. ON CONFLICT 구문과 VACUUM을 적극 활용하고, FILLFACTOR와 autovacuum 설정을 최적화하면 Dead Tuple을 효과적으로 줄일 수 있습니다. PostgreSQL의 MVCC 특성을 잘 이해하고 이를 활용해 최적화된 데이터베이스 환경을 구축해보세요! 🚀

8. 결론

이번 글에서는 PostgreSQL의 MVCC와 Dead Tuple에 대해 알아보며 Duplicate Key 에러와 자릿수 초과/도메인 제약 조건 위반의 차이를 상세히 분석해 보았습니다. 이 과정을 통해 PostgreSQL이 어떻게 데이터를 처리하고 제약 조건을 검증하며, 이러한 동작이 성능과 Dead Tuple 생성에 어떤 영향을 미치는지 이해할 수 있었습니다.

데이터베이스를 운영하다 보면 성능 최적화와 안정성 유지는 늘 중요한 과제입니다. PostgreSQL의 동작 원리를 깊이 이해한다면, 효율적인 트랜잭션 설계와 Dead Tuple 관리를 통해 시스템 성능을 한층 더 끌어올릴 수 있습니다.

다음에도 PostgreSQL의 흥미로운 주제를 다루며 실무와 이론을 연결할 수 있는 내용을 준비하겠습니다. PostgreSQL과 함께 더 나은 데이터베이스 운영 환경을 만들어 가세요! 🚀

궁금한 점이나 추가로 다뤄줬으면 하는 주제가 있다면 댓글로 남겨주세요. 감사합니다! 🙌

#PostgreSQL #MVCC #DeadTuple #DatabaseOptimization #DBA