PostgreSQL은 데이터 덮어쓰지 않고 매 UPDATE 및 DELETE마다 새로운 행 버전을 생성함으로써 **다중 버전 동시성 제어(MVCC)**를 구현합니다. 이러한 설계는 읽기 잠금을 없애지만, VACUUM 프로세스에 의해 회수해야 하는 "죽은 튜플"을 힙에 남깁니다. 테이블이 높은 변화 작업을 겪는 경우—예를 들어, 대기 기록에 대한 빈번한 상태 업데이트 또는 소프트 삭제 전환—죽은 튜플의 축적이 진공 처리를 초과하여 테이블 부풀림, 인덱스 부풀림 및 쿼리 성능 저하로 이어집니다.
핵심 문제는 VACUUM이 죽은 튜플을 식별하기 위해 전체 테이블을 스캔해야 한다는 점이며, 단일 다중 테라바이트 테이블에서는 이 작업이 I/O에 바인드되어 트랜잭션 ID 래핑이 발생하기 전에 완료되지 못할 수 있습니다. 개입이 없으면 실제 행 수는 안정적으로 유지되지만 테이블은 무한정 성장하여 과도한 저장소를 소모하고 인덱스 스캔 속도를 저하시킵니다.
해결책은 기본 파티셔닝을 RANGE 전략을 사용해 시간적 또는 논리적 키(예: 생성 날짜)로 구현하는 것입니다. 테이블을 더 작은 물리적 파티션으로 분할함으로써(예: 월별 세그먼트) 진공 작업이 전체 테이블 대신 개별 파티션에서 작동하도록 합니다. 또한, 파티션 가지치기는 쿼리가 관련 세그먼트만 액세스하도록 보장하고, 오래된 파티션은 분리하여 아카이브할 수 있어 진공 오버헤드 없이 즉시 저장소를 회수합니다.
-- 범위 파티셔닝을 가진 상위 테이블 CREATE TABLE iot_sensor_data ( sensor_id INT NOT NULL, temperature NUMERIC, recorded_at TIMESTAMP NOT NULL, status VARCHAR(20) ) PARTITION BY RANGE (recorded_at); -- 최근 고밀도 데이터에 대한 월별 파티션 CREATE TABLE iot_sensor_data_2024_06 PARTITION OF iot_sensor_data FOR VALUES FROM ('2024-06-01') TO ('2024-07-01'); -- 파티션 키에 대한 인덱스 CREATE INDEX idx_sensor_date ON iot_sensor_data (recorded_at);
스마트 제조 고객이 5만 개의 IoT 센서로부터 텔레메트리를 수집하는 PostgreSQL 14 데이터베이스를 운영하고 있으며, 매일 1천만 개의 데이터를 생성하고 30%는 24시간 이내에 상태 업데이트가 필요합니다. sensor_logs 테이블은 AUTOVACUUM이 끊임없는 상태 플래그 업데이트로부터 죽은 튜플을 회수할 만큼 테이블을 처리하지 못하여 6개월 만에 2TB로 부풀렸습니다. 쿼리 지연 시간은 30초로 급증했으며, pg_class는 테이블이 논리적 크기에 비해 400% 부풀어 있음을 나타냈습니다.
세 가지 아키텍처 솔루션이 평가되었습니다. 첫 번째 접근법은 수동적인 VACUUM 조정을 포함하여 autovacuum_vacuum_scale_factor를 0.02로 낮추고 maintenance_work_mem을 2GB로 증가시키는 것이었습니다. 이는 최소한의 도움이 되었지만, 전체 2TB 테이블을 지속적으로 스캔해야 했으며, 생산 쿼리와의 심각한 I/O 경합을 초래하고 야간 유지 관리 시간 내에 완료되지 못했습니다. 두 번째 전략은 오래된 데이터를 제외한 부분 인덱스를 제안하여 인덱스 부풀림을 줄였지만, 기초 힙 부풀림이나 테이블 내 죽은 튜플 축적 문제를 해결하지 못했습니다.
세 번째 솔루션은 recorded_at 타임스탬프에 대한 RANGE를 통한 기본 파티셔닝을 구현하여 약 300GB 규모의 월별 파티션을 생성했습니다. 이 접근법은 현재 월의 파티션에 높은 변화 활동을 국한시켜 VACUUM이 매 실행 시 2TB 대신 300GB를 처리할 수 있도록 했습니다. 나아가 1년 이상 된 파티션은 DETACH되어 콜드 스토리지를 통해 간단하게 공간을 회수할 수 있었습니다. 구현 후 진공 지속 시간은 8시간에서 45분으로 줄어들고 거래 ID 래핑 위험이 사라졌으며, 쿼리 성능은 파티션 가지치기를 통해 열 배 향상되었습니다.
PostgreSQL의 HOT(Heap-Only Tuple) 메커니즘이 인덱스 부풀림을 줄이는 방법과 그것이 적용되지 않는 특정 조건은 무엇인가?
HOT 업데이트는 행이 업데이트되지만 인덱스가 있는 열이 변경되지 않고 동일한 8KB 데이터 페이지 내에 충분한 여유 공간이 있는 경우 발생합니다. 이 경우 PostgreSQL은 새로운 튜플을 힙 내 이전 튜플에 체인으로 연결하고 새로운 인덱스 항목을 생성하지 않아 인덱스 유지 관리 오버헤드를 크게 줄입니다. 그러나 업데이트가 인덱스에 포함된 열 중 하나라도 변경되거나 페이지에 fillfactor 임계값 아래의 여유 공간이 없는 경우 HOT는 실패하여 PostgreSQL이 새로운 인덱스 포인터를 작성해야 하며 부풀림을 발생시킵니다. 후보자들은 종종 모든 UPDATE 작업이 인덱스에 동일한 영향을 미친다고 가정하며, HOT가 성공할 경우 인덱스가 없는 열을 업데이트하는 것이 상당히 저렴하다는 것을 인식하지 못합니다.
VACUUM, VACUUM FULL 및 CLUSTER의 잠금 동작 및 테이블 가용성 측면에서의 정확한 차이는 무엇인가?
VACUUM은 모든 작업과 동시에 실행되며, 죽은 튜플을 재사용 가능한 공간으로 표시하지만 저장소를 운영 체제로 반환하지 않고 사용자 데이터에 대한 잠금을 보유하지 않습니다. VACUUM FULL은 부풀림을 완전히 제거하기 위해 전체 테이블 파일을 재작성하지만 ACCESS EXCLUSIVE 잠금을 획득하여 영구적으로 모든 읽기와 쓰기를 차단하며, 큰 테이블에서 몇 시간이 걸릴 수 있습니다. CLUSTER는 테이블을 인덱스 순서에 맞게 물리적으로 재배열하며, 또한 ACCESS EXCLUSIVE 잠금을 요구하고 순수 공간 회수 측면에서 일반적으로 VACUUM FULL보다 느리지만 향후 클러스터 인덱스 스캔을 위해 정렬된 순서를 유지합니다. 후보자들은 종종 일상적인 유지 보수를 위해 VACUUM FULL을 위험하게 추천하며, 이는 테이블의 전체 가용성 상실을 초래함을 이해하지 못합니다.
가시성 맵이 인덱스 전용 스캔 성능에 미치는 영향은 무엇이며, 왜 이 접근 방식에 대한 진공 빈도가 중요한가?
가시성 맵은 테이블과 함께 저장되는 이진 비트맵으로, 현재 및 미래의 모든 트랜잭션에 대해 보이는 튜플만 포함하는 힙 페이지를 추적합니다. 인덱스 전용 스캔은 인덱스와 가시성 맵만 사용하여 쿼리를 처리할 수 있지만, 맵이 해당 페이지의 모든 행이 보임을 확인해야만 가능합니다. 페이지에 죽은 튜플이나 미확인 트랜잭션이 있는 경우, 가시성 비트가 설정되지 않아 데이터베이스가 개별 힙 튜플을 확인해야 합니다. 자주 VACUUM하면 가시성 맵 비트를 업데이트하여 진정한 인덱스 전용 액세스가 가능해지며, 이 과정 없이는 커버링 인덱스를 사용하는 쿼리조차도 튜플 가시성을 확인하기 위해 임의의 I/O가 발생하게 되어 최적화의 목적을 무너뜨리게 됩니다.