PostgreSQL의 VACUUM은 MVCC(다중 버전 동시성 제어, Multi-Version Concurrency Control)로 인해 발생하는 Dead Tuple(사용되지 않는 데이터)을 정리하는 과정.

• VACUUM이 없으면 PostgreSQL 테이블 크기가 계속 증가하고 성능이 저하됨.
• Dead Tuple이 많아지면 SELECT 속도가 느려지고, INSERT/UPDATE 시 불필요한 I/O가 증가.
• VACUUM을 효율적으로 설정하면 PostgreSQL 성능을 최적화할 수 있음.

1️⃣ VACUUM이 필요한 이유

🔹 PostgreSQL은 DELETE와 UPDATE 시 데이터를 실제로 삭제하지 않는다!

PostgreSQL의 MVCC 특성상 DELETE 또는 UPDATE가 실행되면 기존 데이터를 변경하지 않고 새로운 버전을 생성한다.

• DELETE: 데이터가 즉시 삭제되지 않고 삭제 표시(xmax 값이 설정됨)만 됨.
• UPDATE: 기존 데이터가 Dead Tuple이 되고, 새로운 Tuple이 생성됨.

UPDATE user SET name = 'Alice Smith' WHERE id = 1;

실제 데이터 변경 과정

✅ Dead Tuple이 많아지면 VACUUM이 필요함!

✅ VACUUM이 없으면 테이블 크기가 계속 증가하며 성능 저하 발생

2️⃣ VACUUM의 종류

PostgreSQL에서는 여러 가지 VACUUM 방식이 있다.

✅ 일반적으로는 AUTOVACUUM을 사용하지만, 필요 시 VACUUM FULL을 실행해야 함.

3️⃣ VACUUM 동작 과정

(1) VACUUM 기본 동작 원리

1. Dead Tuple 스캔: 테이블을 읽으며 Dead Tuple을 찾음.
2. Dead Tuple을 Free Space Map(FSM)에 등록: 추후 INSERT가 같은 공간을 활용할 수 있도록 함.
3. Index 유지보수: 인덱스에서 참조되지 않는 Tuple을 제거.
4. Transaction ID Wraparound 방지: autovacuum_freeze_max_age를 초과한 경우 FREEZE 실행.

✅ VACUUM을 실행하면 테이블 크기가 줄어드는 것이 아니라, Dead Tuple이 제거되고 기존 공간을 재사용 가능하게 됨. ✅ 디스크 공간을 줄이려면 VACUUM FULL을 실행해야 함.

(2) VACUUM ANALYZE

• VACUUM과 ANALYZE를 함께 실행하여 Dead Tuple 정리 후 통계 정보를 업데이트.
• Query Planner가 최신 통계를 반영하여 최적의 실행 계획을 선택 가능.
• SELECT 성능이 저하된 경우 VACUUM ANALYZE를 실행하면 해결될 가능성이 큼.

✅ 실행 방법

VACUUM ANALYZE user;

✅ Query Planner가 통계를 활용하는지 확인

EXPLAIN ANALYZE SELECT * FROM user WHERE name = 'Alice';

• Seq Scan(전체 테이블 스캔)이 발생하면 통계 정보가 최신이 아닐 수 있음.

(3) VACUUM FULL

• VACUUM과 다르게 테이블 크기를 실제로 줄여줌.
• Dead Tuple을 제거하고, 기존 테이블을 새로운 테이블로 재구성.
• 하지만, Exclusive Lock이 필요하기 때문에 실행 중에는 테이블을 사용할 수 없음.

✅ 실행 방법

VACUUM FULL user;

✅ VACUUM FULL 실행 시 고려해야 할 점

• 테이블 크기가 줄어들지만, 잠금(Lock)이 발생하여 서비스에 영향을 줄 수 있음.
• 일반적인 운영 환경에서는 피하는 것이 좋음 (가능하면 비활성 시간대에 실행).

4️⃣ Autovacuum (자동 VACUUM)

🔹 Autovacuum이란?

• PostgreSQL은 Dead Tuple이 일정 수준을 초과하면 자동으로 VACUUM을 실행함.
• 수동으로 VACUUM을 실행하지 않아도 PostgreSQL이 최적의 시점에서 자동으로 실행.

(필자 의견)

최적의 시점에 Vacuum 이 동작해도 리소스( CPU, 메모리 )를 사용한다.

테이블 사용이 peak 인 시간에 동작한다면, 리소스 고갈로 장애로 이어질 수 있다.

필자는 Autovacuum을 선호하지 않는다.

5️⃣ Dead Tuple 및 VACUUM 상태 확인

✅ Dead Tuple 확인

autovacuum = on
autovacuum_vacuum_threshold = 500  # 기본값: 50 → 변경이 500건 이상이면 VACUUM 실행
autovacuum_vacuum_scale_factor = 0.05  # 기본값: 0.2 → 5% 변경 시 VACUUM 실행
autovacuum_naptime = 30s  # 기본값: 1min → 더 자주 실행
vacuum_cost_limit = 2000  # 기본값: 200 → VACUUM 성능 향상

• dead_tuple_ratio > 20%이면 VACUUM 실행 필요!

✅ 최근 VACUUM 실행 여부 확인

SELECT relname, last_vacuum, last_autovacuum 
FROM pg_stat_user_tables
WHERE schemaname = 'public';

6️⃣ VACUUM 최적화 전략

✔ Dead Tuple이 많다면 VACUUM ANALYZE 실행

VACUUM ANALYZE [테이블명];

✔ 테이블 크기를 줄이고 싶다면 VACUUM FULL 실행

VACUUM FULL [테이블명];

✔ Autovacuum이 너무 자주 실행된다면 autovacuum_vacuum_threshold 값을 높이기

autovacuum_vacuum_threshold = 1000

✔ HOT(Heap-Only Tuple) 최적화로 VACUUM 부담 줄이기

ALTER TABLE users SET (fillfactor = 80);

✔ 테이블 크기가 너무 커지면 CLUSTER 실행

CLUSTER user USING user_pkey;

✅ 결론

🔹 PostgreSQL의 VACUUM은 MVCC로 인해 생성된 Dead Tuple을 정리하는 과정
🔹 VACUUM을 실행하지 않으면 테이블 크기가 계속 증가하고 SELECT 성능이 저하됨
🔹 VACUUM ANALYZE를 실행하면 Dead Tuple을 정리하고 Query Planner 통계를 최신화 가능
🔹 VACUUM FULL은 디스크 공간을 회수하지만, Exclusive Lock이 필요하여 신중하게 실행해야 함
🔹 Autovacuum을 적절히 튜닝하면 자동으로 Dead Tuple을 관리 가능

PostgreSQL에서 Tuple(튜플)은 테이블의 각 행(Row)을 의미하며, MVCC(Multi-Version Concurrency Control)와 VACUUM과 밀접한 관계를 가진다.

본 내용에서는 Tuple의 구조, 동작 방식, Dead Tuple 문제, VACUUM과의 관계, 성능 최적화 전략을 다뤄보고자 한다.

1️⃣ Tuple이란?

• PostgreSQL에서 Tuple은 테이블의 한 행(Row)을 의미
• MVCC(다중 버전 동시성 제어)를 지원하기 위해 하나의 논리적인 행이 여러 개의 버전(Old & New Tuple)을 가질 수 있음
• 데이터는 Heap Page(8KB 블록) 단위로 저장되며, 하나의 Page에 여러 개의 Tuple이 포함됨

✅ PostgreSQL은 기존 데이터를 직접 수정하지 않고, 새로운 버전을 생성하여 MVCC를 구현함.
✅ 이 방식은 동시성을 높이는 대신, Dead Tuple이 증가하여 VACUUM이 필요함.

MVCC에 대한 내용은 이전 장에서 확인 →  PostgreSQL MVCC (Multi-Version Concurrency Control)

2️⃣ Tuple의 내부 구조

각 Tuple(레코드)에는 사용자가 정의한 데이터 외에도 PostgreSQL이 관리하는 메타데이터가 포함된다.

✅ Tuple의 기본 구조

| ctid | xmin | xmax | column1 | column2 | ...

SELECT ctid, * FROM user;

 ctid  | id | name   | email
-------+----+--------+---------------
 (0,1) | 1  | Alice  | alice@email.com
 (0,2) | 2  | Bob    | bob@email.com

✅ ctid는 테이블이 업데이트되거나 VACUUM이 실행될 때 변경됨.

3️⃣ Tuple의 동작 방식 (INSERT, UPDATE, DELETE)

PostgreSQL에서 INSERT, UPDATE, DELETE가 실행될 때 어떻게 동작하는지를 살펴보자.

(1) INSERT (새로운 Tuple 생성)

INSERT INTO user (id, name) VALUES (1, 'Alice');

• 새로운 **Tuple(레코드)**이 Heap Page 내부에 저장됨
• xmin에는 해당 Tuple을 생성한 트랜잭션 ID가 기록됨

✅ xmin(1001)은 트랜잭션 ID를 의미하며, 이 트랜잭션이 커밋되면 데이터가 확정됨

(2) UPDATE (새로운 Tuple 생성 & 기존 Tuple은 Dead Tuple)

UPDATE user SET name = 'Alice Smith' WHERE id = 1;

• PostgreSQL의 MVCC 특성상 UPDATE는 기존 데이터를 수정하지 않고 새로운 Tuple을 생성
• 기존 Tuple은 xmax가 설정되어 "Dead Tuple"이 됨

✅ UPDATE는 DELETE + INSERT와 유사한 방식으로 동작
✅ Dead Tuple이 증가할수록 VACUUM 필요성이 커짐

(3) DELETE (Dead Tuple 생성)

DELETE FROM user WHERE id = 1;

• PostgreSQL에서 DELETE는 데이터를 실제로 삭제하지 않고 xmax 값을 업데이트하여 "삭제됨"으로 표시함.

✅ 데이터는 즉시 삭제되지 않으며, VACUUM을 실행해야 완전히 제거됨

4️⃣ Dead Tuple 문제와 VACUUM의 필요성

Dead Tuple이 많아지면 다음과 같은 문제가 발생

1. 테이블 크기 증가 → 디스크 공간 낭비
2. 쿼리 성능 저하 → SELECT 시 Dead Tuple도 검사해야 함
3. 인덱스 크기 증가 → 불필요한 인덱스 업데이트로 성능 저하
4. VACUUM이 필요함 → Dead Tuple을 정리해야 함

✅ Dead Tuple 상태 확인

SELECT relname, n_live_tup, n_dead_tup 
FROM pg_stat_all_tables 
WHERE schemaname = 'public' 
ORDER BY n_dead_tup DESC;

✅ Dead Tuple을 정리하는 VACUUM 실행

VACUUM ANALYZE user;

5️⃣ Fillfactor와 HOT(Heap-Only Tuple) 최적화

✅ HOT(Heap-Only Tuple)란?

• UPDATE 시 기존 페이지에서 데이터를 수정하는 최적화 기법
• 인덱스를 수정하지 않고 Heap 내에서만 변경 가능
• Fillfactor를 80~90으로 설정하면 HOT 적용 가능

✅ 결론

• PostgreSQL의 Tuple은 MVCC를 구현하기 위해 다중 버전(Dead & Live Tuple)으로 관리됨
• UPDATE 및 DELETE 시 Dead Tuple이 생성되며, 이를 정리하지 않으면 성능 저하 발생
• VACUUM을 실행하여 Dead Tuple을 제거해야 성능 유지 가능
• Fillfactor와 HOT Optimization을 활용하면 UPDATE 성능을 향상시킬 수 있음

🚀 PostgreSQL에서 Tuple을 최적화하면 INSERT, UPDATE, SELECT 성능을 극대화할 수 있음!

VACUUM과 Tuple을 이해하기 이전에 반드시 먼저 알아야 할 내용이다.

MVCC (Multi-Version Concurrency Control, 다중 버전 동시성 제어)는 PostgreSQL이 동시성을 관리하는 핵심 메커니즘이다.

• 트랜잭션이 서로 간섭하지 않도록 과거 버전을 유지하여, 락(Lock) 없이 동시성을 보장
• 읽기(SELECT)와 쓰기(UPDATE, DELETE)가 충돌하지 않음
• VACUUM과 함께 관리되며, 불필요한 데이터(Dead Tuple)를 자동 정리

1️⃣ MVCC란?

• PostgreSQL에서는 데이터 변경 시 기존 데이터를 덮어쓰지 않고 새로운 버전을 생성
• 과거 데이터를 유지하면서 새로운 트랜잭션이 최신 데이터를 조회할 수 있음
• 트랜잭션 격리 수준에 따라 과거 데이터 접근 방식이 달라짐

✅ 기존의 단일 버전 모델과의 차이점

2️⃣ PostgreSQL의 MVCC 동작 원리

PostgreSQL에서 INSERT, UPDATE, DELETE가 실행될 때 어떻게 동작하는지를 살펴보자.

(1) INSERT (새로운 Tuple 생성)

INSERT INTO user (id, name) VALUES (1, 'Alice');

컬럼이 'id(식별번호)', 'name(이름)'을 가진 'user(사용자)' 라는 테이블에 '1'과 'Alice'를 입력한다.

✅ 새로운 Tuple(레코드)가 Page(8KB 블록) 내부에 저장되며, 트랜잭션 정보를 포함한다.

• xmin: 이 튜플을 생성한 트랜잭션 ID (1001)
• xmax: 이 튜플이 삭제될 트랜잭션 ID (NULL이면 아직 삭제되지 않음)

UPDATE user SET name = 'Alice Smith' WHERE id = 1;

(3) DELETE (Dead Tuple 생성)

DELETE FROM user WHERE id = 1;

✅ DELETE는 기존 튜플을 제거하는 것이 아니라, xmax를 업데이트하여 더 이상 조회되지 않도록 함.

• 기존 튜플의 xmax = 1003으로 변경 (이제 이 튜플을 읽을 수 없음)
• 하지만 실제로 디스크에서 삭제된 것은 아님 (VACUUM이 필요함)

3️⃣ MVCC와 트랜잭션 격리 수준

PostgreSQL은 MVCC를 기반으로 다양한 트랜잭션 격리 수준을 지원한다.

✅ PostgreSQL 기본값은 READ COMMITTED이며, 성능과 일관성을 고려한 최적의 선택

✅ REPEATABLE READ 이상을 사용하면 MVCC가 더욱 강력하게 동작하여 일관성을 보장

4️⃣ MVCC와 Vacuum

MVCC에서는 과거 버전의 튜플(Dead Tuple)이 계속 남아 있으므로, 주기적으로 정리해야 함.

• Dead Tuple이 많아지면 성능 저하 발생
• PostgreSQL은 이를 해결하기 위해 VACUUM 프로세스를 실행

✅ Vacuum의 역할

• Dead Tuple을 제거하여 디스크 공간을 확보
• 트랜잭션 ID(XID) Wraparound 방지
• Query Planner가 최신 통계를 사용할 수 있도록 갱신 (ANALYZE)

5️⃣ MVCC 성능 최적화 전략

✔ Dead Tuple 모니터링 및 Vacuum 튜닝

• autovacuum_vacuum_scale_factor = 0.05 (5% 변경 시 자동 Vacuum 실행)
• autovacuum_naptime = 30s (더 자주 실행)

✔ Fillfactor 조정

• UPDATE가 많은 테이블은 fillfactor = 80으로 설정하여 HOT (Heap-Only Tuple) 최적화

✔ 인덱스 정리

• UPDATE가 많은 테이블은 REINDEX TABLE을 주기적으로 실행하여 인덱스 크기 최적화

✔ HOT (Heap-Only Tuple) 최적화

• UPDATE가 인덱스 키를 변경하지 않으면 기존 페이지에서 데이터 수정 가능
• 이를 위해 인덱스 컬럼을 변경하는 UPDATE를 줄이는 것이 성능 최적화의 핵심!

✅ 결론

🔹 PostgreSQL의 MVCC는 동시성 제어를 위한 핵심 기술로, 락 없이 여러 트랜잭션이 독립적으로 실행 가능
🔹 UPDATE 및 DELETE는 Dead Tuple을 생성하므로, 정기적인 VACUUM이 필수
🔹 트랜잭션 격리 수준에 따라 MVCC 동작 방식이 다름 (READ COMMITTED가 기본값)
🔹 autovacuum을 튜닝하여 Dead Tuple을 최소화하면 성능 최적화 가능

✅ MVCC의 원리를 이해하고 튜닝하면 PostgreSQL의 성능을 극대화할 수 있음!

Shared Buffer(공유 버퍼)는 PostgreSQL의 메모리 캐시 시스템으로,

디스크 I/O를 최소화하고 성능을 향상시키는 핵심 요소.
PostgreSQL은 데이터를 직접 디스크에서 읽지 않고,

Shared Buffer에 먼저 로드하여 읽기/쓰기 성능을 최적화한다.

1️⃣ Shared Buffer란?

• PostgreSQL이 데이터를 디스크에서 읽거나 변경할 때 사용하는 캐시 메모리
• 모든 백엔드 프로세스가 공유하여 반복적인 디스크 I/O를 줄이고 속도를 높임
• shared_buffers 파라미터를 통해 크기 조정 가능
• 운영 체제의 페이지 캐시보다 먼저 적용되어 PostgreSQL 내부에서 데이터를 직접 관리 가능

✅ Shared Buffer를 활용하면 데이터 읽기/쓰기 성능이 크게 향상됨.

🔹 Shared Buffer 크기 설정 가이드

서버 RAM 크기추천 shared_buffers 값

✅ RAM의 25% ~ 40% 정도 설정하면 성능 최적화 가능
✅ 너무 크게 설정하면 운영 체제 캐시(Page Cache)와 충돌 가능

2️⃣ Shared Buffer의 주요 역할

 (1) 데이터 캐싱 (Heap 및 Index)

• 테이블(Heap)과 인덱스(Index) 데이터를 캐싱하여 빠른 조회 가능
• 최근 사용된 데이터는 Shared Buffer에 남아 있어 성능 최적화

✅ 쿼리 실행 시 데이터가 Shared Buffer에 있다면 디스크 접근 없이 즉시 반환 가능!

 (2) 데이터 수정 및 트랜잭션 관리

• INSERT, UPDATE, DELETE 작업 시 Shared Buffer에서 먼저 반영됨
• 변경된 데이터는 바로 디스크에 쓰이지 않고 WAL(Write-Ahead Logging) 로그에 기록됨
• Checkpointer가 일정 시간마다 디스크로 데이터를 저장

✅ 변경된 데이터는 바로 디스크에 기록되지 않고, 먼저 Shared Buffer에서 처리됨 → 성능 최적화

 (3) Background Writer를 통한 버퍼 정리

• PostgreSQL은 Background Writer 프로세스를 사용하여 Shared Buffer에서 변경된 데이터를 주기적으로 디스크에 기록
• Checkpointer는 일정 시점마다 완전히 기록(Checkpoint)하여 데이터 무결성을 보장

✅ 이 메커니즘 덕분에 PostgreSQL은 데이터 무결성을 유지하면서 성능을 극대화할 수 있음

3️⃣ Shared Buffer의 동작 원리

 (1) 페이지 캐싱 전략

PostgreSQL은 LRU (Least Recently Used) 알고리즘을 기반으로 Shared Buffer를 관리.

• 자주 사용되는 페이지는 Shared Buffer에 남아 있음
• 오래 사용되지 않은 페이지는 새로운 데이터가 필요할 때 제거됨

✅ 가장 최근에 사용된 데이터는 유지되고, 사용되지 않는 데이터는 새로운 데이터로 교체됨

 (2) Buffer Mapping (버퍼 매핑)

Shared Buffer는 고정된 크기의 버퍼 블록으로 나뉘며, 각 블록은 테이블 및 인덱스 페이지를 저장.

✅ 버퍼가 가득 차면 PostgreSQL은 오래된 페이지를 버리고 새로운 데이터를 캐싱함

4️⃣ Shared Buffer 성능 분석 및 모니터링

🔹 현재 버퍼 상태 확인

SELECT usagecount, count(*) 
FROM pg_buffercache 
GROUP BY usagecount 
ORDER BY usagecount DESC;

• usagecount 값이 높을수록 자주 사용되는 데이터임
• 사용되지 않는 버퍼가 많다면 shared_buffers를 줄일 필요가 있음

🔹 Shared Buffer Hit Ratio(캐시 적중률) 분석

SELECT 
  sum(blks_hit) / (sum(blks_hit) + sum(blks_read)) AS buffer_hit_ratio
FROM pg_stat_database;

✅ 캐시 적중률이 90% 이상이면 최적화된 상태!
✅ 80% 이하이면 shared_buffers를 늘려야 함

🔹 디스크 I/O 감소 여부 확인

SELECT 
  sum(heap_blks_hit) / sum(heap_blks_hit + heap_blks_read) AS table_cache_hit_ratio,
  sum(idx_blks_hit) / sum(idx_blks_hit + idx_blks_read) AS index_cache_hit_ratio
FROM pg_statio_user_tables;

✅ 테이블 및 인덱스 캐시 적중률이 90% 이상이면 성능이 최적화됨

5️⃣ Shared Buffer 최적화 전략

✔ 서버 RAM의 25%~40%를 Shared Buffer에 할당
✔ 캐시 적중률(buffer hit ratio)을 90% 이상 유지
✔ 백그라운드 프로세스(BGWriter, Checkpointer)를 튜닝하여 효과적인 버퍼 관리
✔ 너무 큰 Shared Buffer는 OS Page Cache와 충돌할 수 있으므로 적절한 값 설정 필요

✅ 정리,

Shared Buffer는 PostgreSQL 성능 최적화의 핵심이며, 올바른 크기로 설정하면 I/O 성능을 획기적으로 개선할 수 있음!

Backend Buffer(백엔드 버퍼)는

PostgreSQL에서 각 클라이언트(세션)별로 개별적으로 할당되는 메모리 영역이다.
이 버퍼는 쿼리 실행 시 필요한 데이터를 저장하거나 연산을 수행하는 공간으로 활용되며,

공유 메모리(shared_buffers)와는 다르게 개별 프로세스에서만 사용된다.

1️⃣ Backend Buffer란?

• PostgreSQL은 클라이언트 요청마다 개별적인 Backend Process를 생성하고, 각 프로세스는 독립적인 메모리 공간을 갖는다.
• 이 개별 프로세스가 사용하는 메모리 버퍼가 Backend Buffer.
• 주요 구성 요소:
  • work_mem
  • temp_buffers
  • maintenance_work_mem
  • catalog_cache
  • optimizer/executor memory

✅ Backend Buffer는 개별 세션에만 영향을 주며, PostgreSQL 서버 전체 공유가 불가능하다.

2️⃣ Backend Buffer의 주요 구성 요소 분석

 (1) work_mem (쿼리 실행 시 작업 메모리)

• 정렬(SORT), 해시 조인(HASH JOIN), 그룹화(GROUP BY) 같은 작업을 수행할 때 사용다.
• work_mem을 초과하면 임시 디스크 저장소(temp file)를 사용하게 되어 성능이 저하될 수 있음.

💡 튜닝 포인트:

• work_mem이 너무 낮으면 쿼리 성능 저하 (디스크 I/O 증가)
• work_mem이 너무 높으면 메모리 부족 현상 발생 가능 (OOM, Out Of Memory)
• 일반적으로 1GB RAM당 4MB ~ 16MB 정도 설정 추천.

-- work_mem이 초과되어 임시 디스크를 사용하는지 확인.
SELECT datname, temp_bytes, temp_files FROM pg_stat_database;
-- temp_bytes 값이 크다면 work_mem이 부족하여 디스크를 사용 중인 상태

 (2) temp_buffers (임시 테이블을 위한 버퍼)

• 세션 내에서 생성된 임시 테이블의 데이터를 캐싱하는 메모리 공간.
• 개별 세션에서만 접근 가능하며, 세션이 종료되면 자동으로 메모리 해제됨.

💡 튜닝 포인트:

• 임시 테이블을 자주 사용하는 워크로드(ETL, 데이터 분석 등)에서는 값을 늘리면 성능 향상 가능
• 기본값은 8MB, ETL 작업이 많다면 64MB 이상으로 조정 가능

 (3) maintenance_work_mem (유지보수 작업을 위한 메모리)

• VACUUM, CREATE INDEX, ALTER TABLE, CLUSTER 같은 유지보수 작업을 수행할 때 사용되는 메모리.
• work_mem보다 더 많은 메모리가 필요할 수 있음.

💡 튜닝 포인트:

• VACUUM FULL, REINDEX 등을 실행할 때 충분한 메모리가 필요하므로, 일반적으로 work_mem의 10배 정도 설정 추천.
• 기본값은 64MB, 인덱스 크기가 큰 테이블에서는 1GB ~ 4GB로 증가 가능.

 (4) catalog_cache (시스템 카탈로그 캐싱)

• PostgreSQL의 시스템 카탈로그(메타데이터 테이블)를 저장하는 버퍼.
• 테이블 구조, 인덱스 정보, 권한 등 DDL 관련 정보를 빠르게 조회할 수 있도록 도와줌.

💡 튜닝 포인트:

• pg_class, pg_attribute, pg_proc 같은 시스템 카탈로그를 자주 조회하는 경우 증가 가능.
• 대규모 데이터베이스에서 DDL이 많다면 성능 향상을 위해 증가 가능.

(5) optimizer/executor (쿼리 최적화 및 실행 엔진 메모리)

• PostgreSQL의 쿼리 최적화 및 실행을 위한 메모리 공간.
• EXPLAIN ANALYZE 실행 시 사용하는 쿼리 실행 계획을 최적화하는데 필요한 공간.

💡 튜닝 포인트:

• 일반적으로 PostgreSQL이 자동으로 관리하므로 튜닝이 필요하지 않음.
• 대규모 조인 및 복잡한 쿼리를 실행할 때만 필요.

3️⃣ Backend Buffer 관련 최적화 전략

✔ 워크로드에 맞는 work_mem 설정

• work_mem이 너무 낮으면 정렬, 조인 시 성능 저하 발생
• work_mem을 높이면 쿼리 성능 향상 가능하지만 너무 크게 설정하면 메모리 부족(OOM) 발생
• 보통 1GB RAM당 4MB ~ 16MB 추천

✔ 임시 테이블을 많이 사용하면 temp_buffers 증가

• ETL, 데이터 분석 작업 시 temp_buffers를 64MB ~ 128MB로 조정 가능

✔ 자동 VACUUM이 너무 오래 걸린다면 maintenance_work_mem 증가

• maintenance_work_mem을 충분히 확보하면 VACUUM, CREATE INDEX 속도 향상

✔ 시스템 카탈로그 접근이 많다면 catalog_cache 튜닝 고려

• 데이터베이스 규모가 크다면 시스템 메타데이터 캐싱을 최적화하면 성능 향상 가능

정리

✅ Backend Buffer는 개별 세션에서만 사용되는 메모리 영역이며,
✅ work_mem, temp_buffers, maintenance_work_mem 등의 설정이 쿼리 성능에 직접적인 영향을 미침
✅ shared_buffers와 다르게 공유되지 않고 개별 프로세스에서만 유지됨
✅ 적절한 튜닝을 통해 정렬(SORT), 조인(JOIN), 임시 테이블 사용 성능을 최적화 가능

이번에 다룰 주제는 PostgreSQL에서 'Idle Session의 Memory 사용' 이다.

궁금하다. PostgreSQL에서 Idle Connection이 메모리를 차지할까?

시간을 아끼기 위해 결론부터 말하면,

PostgreSQL에서는 Idle Session(유휴 세션)이 일정량의 메모리를 유지하면서도 시스템 리소스를 점유한다.

work_mem, temp_buffers, shared_buffers 등의 메모리 설정이 높은 경우, Idle Session이 많으면 메모리 낭비가 발생한다.

1. PostgreSQL의 Idle Session이 차지하는 메모리

PostgreSQL에서 클라이언트가 연결되면 각 세션(백엔드 프로세스)이 고유한 메모리를 할당받습니다.

✅ Idle Session의 주요 메모리 점유 영역

1. Per-Connection Memory (개별 연결별 메모리)
   • work_mem: 한 쿼리당 할당되는 작업 메모리
   • temp_buffers: 개별 연결이 사용하는 임시 버퍼
   • maintenance_work_mem: VACUUM, CREATE INDEX 등의 유지보수 작업 시 사용됨
2. Shared Memory (PostgreSQL 전체 공유)
   • shared_buffers: PostgreSQL 전체에서 사용하는 캐시 영역
   • wal_buffers: WAL(Write-Ahead Logging) 관련 데이터 캐시

💡 Idle 상태에서도 기본적인 개별 연결 메모리는 유지되므로, Idle Connection이 많으면 메모리 낭비 발생!

2. Idle Session의 유형

PostgreSQL에서 pg_stat_activity.state를 확인하면 Idle 상태의 세션을 분석할 수 있다.

  (1) idle

• 트랜잭션이 열려 있지 않은 단순 유휴 세션
• 단순히 연결만 유지하면서 사용자 입력을 기다리는 상태
• 일반적으로 심각한 문제는 없지만 오래 유지되면 불필요한 메모리 소비 발생

  (2) idle in transaction

• 트랜잭션이 열려 있지만 아무 작업도 수행하지 않는 상태
• 예: BEGIN; 실행 후, COMMIT 또는 ROLLBACK 없이 대기 중
• 문제: 이 상태가 오래 지속되면 lock을 유지하면서 다른 트랜잭션을 블로킹할 수 있음

  (3) idle in transaction (aborted)

• 트랜잭션이 비정상적으로 중단(aborted) 되었으나 연결은 유지됨
• ROLLBACK을 실행해야 하지만, 클라이언트가 응답하지 않으면 트랜잭션이 종료되지 않음
• 문제: 메모리뿐만 아니라 다른 트랜잭션도 블로킹 가능.

🔹 Idle Session 확인 Query

SELECT pid, usename, state, query, age(clock_timestamp(), query_start) AS idle_time
FROM pg_stat_activity
WHERE state LIKE 'idle%'
ORDER BY idle_time DESC;

3. Idle Session이 많으면 어떤 문제가 발생할까?

Idle Session이 많아지면 다음과 같은 문제가 발생할 수 있다.

4. Idle Session이 많을 때 해결 방법

  (1) 오래된 Idle Session 강제 종료

오래된 Idle Connection을 자동으로 종료는 idle_in_transaction_session_timeout 설정으로.

-- 특정 시간이 지나면 자동으로 세션 종료
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
-- 5분 이상 idle in transaction 상태가 지속되면 자동 종료

-- 현재 오래된 Idle 세션을 즉시 종료
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction' AND now() - query_start > interval '10 minutes';
-- 10분 이상 유휴 상태의 트랜잭션을 종료

  (2) pg_bouncer를 이용한 Connection Pooling

Idle Session이 많으면 Connection Pooling을 활용하는 것이 효과적일 수 있다.
PgBouncer를 사용하면 짧은 연결을 효율적으로 관리하여 메모리 사용량을 줄일 수 있음.

  (3) max_connections 조정 및 커넥션 제한

Idle Session이 많아지면 PostgreSQL이 커넥션을 너무 많이 허용하지 않도록 조정하는 것이 중요하다.

-- max_connections 줄이기
max_connections = 200  -- 예를들어 기본값 1000 → 200으로 조정

📌 Idle Session이 CPU 사용률을 증가시킴.

→ 추가로 더 보면 좋은 내용 "Resources consumed by idle PostgreSQL connections"

난 흐름을 좋아한다. 

역사도 흐르고, 강물도 흐르고, 우리의 인생도 흐른다.

그래서 PostgreSQL 아키텍처를 흐르면서 이해를 하려 한다. (데이터의 흐름)

시작 - 시스템의 흐름은 요청에 의해 시작한다.

Client (클라이언트)

• 클라이언트는 TCP/IP or 소켓을 통해 서버에 요청을 보냄.
• 클라이언트가 Query를 실행하면, 시스템에서 최초 Postmaster 프로세스가 맞이한다. (도어맨?)
  • Client Application
    • 사용자가 PostgreSQL에 연결하는 애플리케이션 (예: 웹 애플리케이션, BI 툴, API 서버 등)
    • API를 통해 데이터베이스 요청을 수행
  • Client Interface Library
    • PostgreSQL과 통신하기 위한 클라이언트 라이브러리 (libpq, JDBC, psycopg2 등)
    • 클라이언트 애플리케이션이 PostgreSQL과 SQL 쿼리 및 결과를 주고받을 때 사용

Server(서버) - 처리

먼저 처리를 위해서는 아래 3가지 영역(부서?)이 필요하고 각 영역의 처리 담당자와 역할을 소개한다.

1. 프로세스(Process) 
  ; 프로세스라는 조직안에서는 2개의 파트로 구성되어 있고, 역할별로 일꾼들이 있다.

Server Processes (서버 프로세스)

• Postmaster (PostgreSQL Master Process , Postmaster Daemon Process, edb-postgres)
  최초 요청 Query를 맞이한 Postmaster는 PostgreSQL에서 최상위 프로세스로, 클라이언트 연결을 관리함.
  새로운 (연결) 요청이 오면 맞이하여, Backend Process(백엔드 프로세스)를 생성함.
• Backend Processes (Postgres Backend Process)
  백엔드 프로세스는 클라이언트와 1:1로 연결되며,
  요청(Query)를 실행하고 데이터를 검색한 후 클라이언트에 반환함.

Background Utility Processes (백그라운드 프로세스)
  ; PostgreSQL 성능 유지 및 장애 복구에 필수적인 역할

• Autovacuum
  MVCC (Multi-Version Concurrency Control) 구조로 인해 DELETE/UPDATE 후 삭제된 불필요한 튜플(Dead Tuple)을 자동으로 정리하여 성능 저하를 방지함.
  (튜플은 생활하면서 발생하는 쓰레기 같은 것이라고 이해했음. 튜플에 대해서는 Vaccum과 함께 별도 세션에서 또..)
  Vacuum 작업을 수행하여 데이터베이스를 최적화. (진공청소기로 방정리 해주는... 매우 그레이트 어마무시하게 중요!)
• Logger (Sys Logger)
  모든 PosgreSQL의 활동을 기록하는 로그 관리 프로세스. (나의 생활을 기록해주는.. 난 그럼 유명인?.. 죄송..)
  log_text_lines 파일에 SQL Query, 오류 메세지 등을 저장.
• Stats Collector
  데이터베이스의 통계를 수집, 쿼리 최적화 및 성능을 모니터링 함.
  pg_stat_*** 뷰를 통해 쿼리 성능 분석 가능.
• WAL Writer
  WAL(Write-Ahead Logging) 로그를 디스크에 기록하는 프로세스.
  데이터 변경 사항을 WAL 파일에 기록한 후 실제 데이터 파일에 반영함.
• WAL Receiver & WAL Sender
  스트리밍 복제(Streaming Replication)를 수행하는 프로세스
  WAL Sender가 마스터에서 WAL 데이터를 보내고, WAL Receiver가 슬레이브에서 이를 수신하여 적용.
• Checkpointer
  일정 주기마다 데이터를 디스크에 저장하고 체크 포인트를 생성. (모든 작업은 매번 디스크에 쓰지 않구나 추측)
  체크포인트는 크래시 발생 시 복구 시점을 결정하는 중요한 역할도 함.
• BGWriter (Background Writer)
  변경된 데이터를 디스크로 비동기로 기록하는 프로세스.
  데이터베이스의 성능을 향상(최적화)시키기 위해 캐시에서 디스크로 데이터를 저장하는 역할.
  
• Archiver
  WAL 로그를 압축 및 보관하여 장애 발생 시 복구할 수 있도록 보관함.
  
• Logical Replication Launcher
  논리적 복제(Logical Replication) 기능을 담당하는 프로세스

2. 메모리 (RAM)
작업을 위해서는 작업자들이 일을 처리하기 위한 작업공간이 필요하다. (개인책상, 회의실, 비품실 등등..)
PostgreSQL은 공유메모리와 개별 프로세스 메모리로 나뉨.

• Individual Memory (개별 프로세스 메모리) (Per Backend Memory, Backend Buffer)
  각 클라이언트 세션마다 할당되며, 세션이 종료되면 메모리가 해제 됨.
  
  • maintenance_work_mem: VACUUM, CREATE INDEX와 같은 유지보수 작업에 사용되는 메모리.
  • work_mem: 각 쿼리 실행 시 사용되는 메모리 (정렬, 해시 조인, 해시 집계 등).
  • autovacuum_work_mem: autovacuum 프로세스에서 사용하는 메모리.
  • temp_buffer: 개별 쿼리에서 임시 데이터 저장 메모리.
  • catalog_cache: 시스템 카탈로그 테이블을 캐싱하여 빠른 접근 가능.
  • optimizer/executor: 쿼리 최적화 및 실행을 위한 메모리.
    
    더 자세한 내용 :  https://jaihuni.tistory.com/entry/PostgreSQL-Backend-Buffer

• Shared Memory (PostgreSQL 인스턴스 전체에서 공유하는 메모리, Shared Buffer)
  공유 메모리는 PostgreSQL 인스턴스 전체에서 공용으로 사용되며, 서버 성능을 결정하는 중요한 요소!
  
  • shared_buffers: Heap 및 Index 데이터를 캐싱하여 디스크 I/O를 줄이고 성능을 향상시킴.
  • wal_buffers: WAL 로그를 임시로 저장하는 메모리 영역, shared_buffers와 별도로 관리되는 메모리 공간.
  • Temp Buffers: 각 세션이 필요할 때 사용하는 임시 테이블 데이터를 저장.
  • CLOG Buffers: 트랜잭션 상태를 관리하는 Commit Log (CLOG) 버퍼. 각 트랜잭션의 커밋 상태를 추적.
                             MVCC(Multi-Version Concurrency Control) 구현을 위한 필수 요소
  • Other Buffers: 다양한 내부 처리에 사용되는 추가적인 메모리 영역.
  • Lock Space: 데이터베이스의 잠금 정보를 관리하는 공유 메모리 영역,
                         max_locks_per_transaction 파라미터로 설정
    
    더 자세한 내용 :  https://jaihuni.tistory.com/entry/PostgreSQL-Shared-Buffer

3. 디스크 (Disk) / 파일 (File) - Data Cluster (데이터 저장)
처리 업무는 모두 작업공간에 계속 쌓아놓고 일할 수 없다. 처리 완료된 내용은 별도 공간에 저장함.
PostgreSQL은 데이터를 디스크에 저장하며, 아래 유형의 파일 시스템을 사용.

• Storage Manager: Data Files - Heap and Index
  • 실제 데이터(테이블,Heap)와 인덱스(Index)는 랜덤 읽기/쓰기(Random Access I/O)를 사용하여 저장됨.
  • shared_buffers를 통해 데이터가 캐시되며, 필요할 때만 디스크에서 읽어온다.
• Sub-directories (Segments)
  • 데이터 파일이 저장되는 물리적 디렉터리 구조.
• WAL (Write-Ahead Logging) Files
  • 모든 트랜잭션 변경 사항을 기록하는 WAL 로그를 저장.
  • WAL 로그는 순차적인 방식(Sequential Writing)으로 기록되어 빠른 성능을 제공.
  • 만약 데이터가 손상되면 WAL 로그를 사용하여 복구할 수 있음.
• Archived Files (아카이브 모드 활성화 시)
  • WAL 로그를 백업하여 장애 발생 시 복구할 수 있도록 보관.
• Log Files
  • PostgreSQL의 운영 로그 파일 (SQL 실행 기록, 오류 로그 등)
• Lock File
  • PostgreSQL이 실행 중인지 확인하는 파일

이제 데이터를 흘려보자.

📌  PostgreSQL의 데이터 흐름 (Query Execution Flow)

1️⃣ 클라이언트가 SQL 쿼리를 요청하면, Postmaster가 이를 수신,
2️⃣ Postmaster는 Backend Process를 생성하여 요청을 처리하고
3️⃣ Backend Process는 Shared Memory의 shared_buffers를 확인하여 데이터 조회,
4️⃣ 데이터가 shared_buffers에 없으면 디스크에서 데이터를 읽고 shared_buffers에 로드함.
5️⃣ 변경 사항이 있으면 WAL Buffers에 먼저 기록, WAL Writer가 WAL Buffers → WAL 파일로 저장
6️⃣ (Background) 일정 시간(Checkpoint)마다 Checkpointer가 변경된 데이터를 디스크에 저장하여 영구적으로 반영,
7️⃣ (Background) Autovacuum이 주기적으로 실행되어 불필요한 Dead Tuple을 정리하여 성능 유지.

8️⃣ 클라이언트에 쿼리 결과를 반환한다.

위 흐름을 이제 도식화해서 한눈으로 보자.

다른 형태

💡 PostgreSQL의 성능을 최적화하려면 중요한 부분. (더 자세히 다룰 내용들)

1️⃣ 메모리 최적화

• shared_buffers, work_mem, maintenance_work_mem 설정을 조정하여 성능 향상

2️⃣ 자동 관리 프로세스 튜닝

• autovacuum 설정을 조정(개인적으로 수동 Vacuum을 선호함) 하여 Dead Tuple을 빠르게 정리.
• checkpoint_timeout, checkpoint_completion_target 설정으로 Checkpoint 최적화

3️⃣ WAL 및 복구 관리

• wal_buffers, wal_writer_delay 조정하여 트랜잭션 처리 성능 개선
• archive_mode 활성화로 WAL 로그를 안전하게 보관

4️⃣ 디스크 I/O 최적화

• random_page_cost, seq_page_cost 조정하여 쿼리 최적화
• SSD 환경에서는 effective_io_concurrency 증가

5️⃣ 쿼리 최적화 기능 강화

• JIT(Just-In-Time) Compilation 최적화
• pg_stat_statements 개선으로 성능 분석 강화

부록 : Flow 좋아하는 노래

PostgreSQL Scan 방식의 종류

1. Seq Scan (Sequential Scan)

• 테이블의 모든 행을 순차적으로 읽음.
• 사용 조건: 인덱스가 없거나, 테이블 전체를 스캔하는 것이 효율적일 때.
• 장점: 작은 테이블에서 간단한 쿼리에 적합.
• 단점: 큰 테이블에서는 비효율적.

2. Index Scan

• 특정 조건에 해당하는 데이터를 빠르게 검색하기 위해 인덱스를 사용.
• 사용 조건: 인덱스가 쿼리 조건에 적합할 때.
• 장점: 조건 검색 시 빠른 성능 제공.
• 단점: 조건이 테이블 대부분의 행을 반환하면 비효율적.

3. Bitmap Index Scan

• 다수의 행을 읽어야 할 때 효율적인 방식으로, Bitmap 구조를 활용.
• 사용 조건: 쿼리가 많은 데이터를 반환하거나, 복합 조건을 처리할 때.
• 장점: Index Scan보다 대량 데이터 처리에 유리.
• 단점: 추가적인 Bitmap 처리 비용 발생.

4. Index Only Scan

• 인덱스에서만 데이터를 읽고, 테이블로 접근하지 않음.
• 사용 조건: 쿼리의 SELECT 목록이 모두 인덱스에 포함된 경우.
• 장점: 테이블 I/O가 없어서 빠름.
• 단점: ANALYZE로 통계가 최신 상태여야 함.

5. Bitmap Heap Scan

• Bitmap Index Scan과 함께 사용되며, 인덱스에서 찾은 행의 위치를 기반으로 테이블 데이터를 읽음.
• 사용 조건: 대량의 데이터를 선택적으로 읽어야 할 때.
• 장점: 대량 데이터 처리에서 효율적.
• 단점: 추가적인 Heap 접근 비용 발생.

6. Tid Scan (Tuple ID Scan)

• 테이블의 특정 튜플(Tuple ID)을 기반으로 데이터를 직접 읽음.
• 사용 조건: 정확한 튜플 위치가 알려져 있는 경우.
• 장점: 특정 행에 직접 접근 가능.
• 단점: 일반적으로 잘 사용되지 않음.

7. Subquery Scan

• 서브쿼리를 기반으로 데이터를 읽음.
• 사용 조건: 서브쿼리가 있는 SELECT 문에서 사용.
• 장점: 서브쿼리 결과를 효율적으로 처리.
• 단점: 서브쿼리 최적화가 필요.

8. Foreign Scan

• 외부 데이터 소스에서 데이터를 읽음 (Foreign Data Wrapper 사용).
• 사용 조건: PostgreSQL 외부 데이터를 접근할 때.
• 장점: 다양한 외부 소스와 통합 가능.
• 단점: 네트워크 지연 및 외부 소스 성능에 의존.

9. Custom Scan

• 사용자 정의 확장을 통해 추가된 Scan 방식.
• 사용 조건: 확장을 설치하여 특정 작업에 최적화된 Scan을 구현.
• 장점: 맞춤형 작업 가능.
• 단점: 구현 및 유지 관리 복잡.

10. Values Scan

• VALUES 구문에서 데이터를 읽음.
• 사용 조건: 간단한 데이터를 제공할 때.
• 장점: 메모리에서 데이터 처리.
• 단점: 큰 데이터셋에는 비효율적.

11. Function Scan

• SQL 또는 PL/pgSQL 함수에서 데이터를 읽음.
• 사용 조건: 함수 반환 값을 테이블처럼 사용할 때.
• 장점: 동적 데이터 제공.
• 단점: 함수가 복잡하면 성능 저하.

12. CTE Scan (Common Table Expression Scan)

• 공통 테이블 표현식(CTE)의 결과를 읽음.
• 사용 조건: WITH 구문을 사용하는 쿼리에서.
• 장점: 쿼리 가독성 증가.
• 단점: CTE는 기본적으로 물리적 처리.

최적화 Tip

1. 인덱스 생성: 조건에 따라 적합한 인덱스(Index, Composite Index 등)를 설계.
2. EXPLAIN ANALYZE 활용: 실행 계획을 분석하고 예상과 실제 실행의 차이를 확인.
3. 통계 업데이트: ANALYZE와 VACUUM 명령으로 최신 상태 유지.
4. 조인 방식 점검: Join과 Scan 방식이 잘 조화를 이루는지 확인.