슬롯 2번의 t_ctid가 자기 자신이 아니라 (0,4)를 가리키고 있습니다. 이 값이 뜻하는 것은 무엇입니까?
이 튜플이 4번 슬롯으로 물리적으로 옮겨졌고 2번 슬롯은 비었습니다.
옛 튜플은 자리에 그대로 남습니다. 2번 슬롯의 lp_flags가 1(LP_NORMAL)이고 lp_off도 8128로 유효한 것이 그 증거입니다. 자리를 옮긴 것이 아니라 버전이 하나 더 생긴 것입니다.
4번 슬롯의 튜플이 이 튜플을 참조하는 외래 키 관계입니다.
t_ctid는 물리 위치 포인터일 뿐 논리적 참조 관계와는 무관합니다. 외래 키는 제약 조건과 인덱스로 관리되며 튜플 헤더에 흔적을 남기지 않습니다.
정답이 튜플의 다음 버전이 같은 페이지의 4번 슬롯에 있습니다.
t_ctid는 평소 자기 위치를 가리키다가, UPDATE로 새 버전이 생기면 그 위치로 갱신됩니다. 한 row의 여러 버전이 이 필드로 이어진 연결 리스트가 되고, 인덱스는 옛 위치만 알아도 이 사슬을 따라 최신 버전에 도달합니다.
이 페이지에서 4번 슬롯부터 빈 공간이 시작됩니다.
페이지의 빈 공간 경계는 개별 튜플의 t_ctid가 아니라 페이지 헤더의 pd_lower와 pd_upper가 정합니다. 튜플 헤더는 페이지 전체의 여유 공간을 알지 못합니다.
해설
UPDATE는 기존 튜플을 제자리에서 고치지 않고 새 튜플을 추가합니다. 옛 튜플에는 t_xmax에 UPDATE 트랜잭션의 xid가 찍히고 t_ctid가 새 버전의 위치로 갱신됩니다. 위 출력에서 슬롯 2의 t_xmax와 슬롯 4의 t_xmin이 똑같이 743인 것은 한 트랜잭션이 한쪽을 죽이고 한쪽을 만들었다는 뜻입니다. t_infomask2의 16386과 32770은 각각 HEAP_HOT_UPDATED(0x4000)와 HEAP_ONLY_TUPLE(0x8000)에 컬럼 수 2가 더해진 값이라, 이번 UPDATE가 HOT으로 처리되어 인덱스 엔트리가 추가되지 않았음까지 읽어낼 수 있습니다.
MAXALIGN을 적용한 크기가 정확히 1,024바이트인 튜플 하나를 이 페이지에 INSERT할 수 있습니까?
정답없습니다. line pointer 4바이트가 함께 필요해 1,028바이트가 있어야 합니다.
튜플 하나가 들어가려면 데이터 영역에 튜플 본체가, 슬롯 디렉터리에 그것을 가리킬 ItemId 4바이트가 동시에 자리를 잡아야 합니다. 여유는 pd_lower와 pd_upper 사이의 1,024바이트뿐이라 4바이트가 모자랍니다.
있습니다. 여유 공간이 정확히 1,024바이트라 딱 맞습니다.
튜플 크기만 세고 슬롯 디렉터리를 빠뜨린 계산입니다. 페이지는 양쪽에서 채워지므로 INSERT 한 건은 언제나 두 방향을 함께 소비합니다.
있습니다. special space가 비어 있어 8,192바이트 지점까지 쓸 수 있습니다.
힙 페이지의 special space 크기가 0인 것은 맞지만, pd_upper 아래쪽은 이미 기존 튜플들이 채우고 있습니다. 실제 여유는 pd_special까지의 거리가 아니라 pd_lower와 pd_upper 사이입니다.
판단할 수 없습니다. 이 테이블의 FILLFACTOR를 알아야 합니다.
FILLFACTOR는 이 페이지를 INSERT 대상으로 고를지 말지를 정하는 기준이라, 낮게 잡혀 있으면 오히려 더 일찍 다른 페이지로 넘어갑니다. 물리적으로 들어가느냐는 pd_lower와 pd_upper만으로 답이 나옵니다.
해설
페이지의 여유 공간은 pd_upper에서 pd_lower를 뺀 값입니다. 여기서는 1,116에서 92를 뺀 1,024바이트입니다. 그런데 INSERT 한 건은 이 공간을 양쪽에서 갉아먹습니다. 튜플 본체는 pd_upper를 앞으로 당기고, 그 튜플을 가리킬 ItemId 4바이트는 pd_lower를 뒤로 밉니다. 필요한 것은 1,028바이트이고 4바이트가 모자라 이 페이지는 후보에서 탈락합니다. PostgreSQL은 FSM을 뒤져 다른 페이지를 찾고, 없으면 파일 끝에 새 페이지를 붙입니다. 한 가지 덧붙이면, LP_UNUSED 상태의 빈 슬롯이 남아 있을 때는 그 ItemId를 재사용해 4바이트를 아낄 여지가 있습니다. 다만 페이지를 고를 때 쓰는 PageGetHeapFreeSpace는 재사용 가능한 슬롯이 있는지 따지지 않고 언제나 ItemId 4바이트를 미리 빼고 여유를 계산하므로, 이 페이지는 어느 쪽이든 후보에서 빠집니다.
CREATETABLE members (
id bigintPRIMARYKEY,
email text,statustext,
last_login timestamptz,
memo text);CREATEUNIQUEINDEXON members (lower(email));CREATEINDEXON members (id)WHEREstatus='active';
이 테이블의 페이지에는 새 튜플이 들어갈 여유가 충분합니다. 다음 UPDATE 중 HOT update로 처리될 수 있는 것은 무엇입니까?
UPDATE members SET status = 'inactive' WHERE id = 7;
status는 부분 인덱스의 WHERE predicate에 등장합니다. 값이 바뀌면 그 row가 인덱스에 포함되는지 여부 자체가 달라지므로 인덱스를 갱신해야 하고 HOT 조건이 깨집니다. 인덱스 키 목록만 훑으면 놓치는 지점입니다.
정답UPDATE members SET last_login = now() WHERE id = 7;
last_login은 인덱스 키에도, 표현식에도, 부분 인덱스의 predicate에도 등장하지 않습니다. 갱신된 컬럼이 어떤 인덱스와도 무관하고 같은 페이지에 자리가 있으므로, 새 인덱스 엔트리 없이 HOT chain으로 연결됩니다.
UPDATE members SET email = 'new@x.com' WHERE id = 7;
email 자체는 인덱스 키가 아니지만 lower(email) 표현식 인덱스가 이 컬럼을 참조합니다. 표현식이 계산에 쓰는 컬럼도 인덱스에 등장하는 컬럼으로 취급됩니다.
UPDATE members SET memo = NULL, id = id + 1000 WHERE id = 7;
id는 PRIMARY KEY라 인덱스 키 컬럼입니다. 한 UPDATE가 여러 컬럼을 건드릴 때는 그중 하나만 인덱스에 걸려도 전체가 HOT에서 제외됩니다.
해설
HOT update의 첫 번째 조건은 갱신된 컬럼이 그 테이블의 어떤 인덱스 정의에도 등장하지 않는 것인데, 여기서 등장한다는 말의 범위가 넓습니다. 인덱스 키 컬럼은 물론이고 표현식 인덱스가 계산에 쓰는 컬럼, 부분 인덱스의 WHERE predicate에 나오는 컬럼까지 전부 포함됩니다. BRIN 같은 summarizing 인덱스만 이 검사에서 빠집니다. 두 번째 조건인 같은 페이지의 여유 공간은 문제에서 충족된다고 주었으니 판정은 컬럼 조건만으로 끝납니다. 운영에서 HOT 비율이 기대보다 낮게 나온다면, 무심코 만들어둔 부분 인덱스나 표현식 인덱스가 조건을 조용히 깨고 있는 경우가 많습니다.
INSERTINTO docs
SELECT1, string_agg(md5(g::text),'')FROM generate_series(1,5000) g;SELECT pg_column_size(content)AS column_size,
pg_column_compression(content)AS compression
FROM docs WHERE id =1;-- column_size | compression-- -------------+--------------- 160000 |SELECT pg_relation_size('docs')AS main_size;-- main_size-- ------------- 8192
content 컬럼의 값에 실제로 무슨 일이 일어났습니까?
TOAST가 동작하지 않았습니다. column_size가 원본 크기 그대로입니다.
pg_column_size는 외부 저장된 값에 대해 TOAST 테이블에 실제로 들어간 크기를 돌려줍니다. 압축이 안 걸리면 그 크기가 원본과 같아지므로, 이 숫자만으로는 in-line인지 외부인지 구분되지 않습니다.
pglz로 압축되어 본 테이블 안에 in-line으로 저장되었습니다.
압축이 걸렸다면 pg_column_compression이 pglz를 돌려줍니다. md5 해시를 이어붙인 문자열은 반복 패턴이 없어 pglz가 거의 줄이지 못하고, 압축해도 이득이 없으면 압축본을 쓰지 않습니다.
값이 여러 chunk로 쪼개져 본 테이블의 여러 페이지에 나뉘어 저장되었습니다.
chunk로 쪼개지는 것은 맞지만 장소가 틀렸습니다. chunk는 본 테이블이 아니라 전용 TOAST 테이블에 저장됩니다. 본 테이블의 한 튜플이 여러 페이지에 걸치는 일은 없습니다.
정답압축이 통하지 않아 TOAST 테이블로 외부 저장되었고, 본 테이블에는 포인터만 남았습니다.
compression이 비어 있으니 압축은 걸리지 않았고, 본 테이블이 페이지 한 장 크기 그대로인 것은 160KB가 그 안에 없다는 뜻입니다. 두 지표를 합치면 값이 옆 테이블로 빠졌다는 결론만 남습니다.
해설
튜플 크기가 임계값(약 2KB)을 넘으면 PostgreSQL은 먼저 압축을 시도하고, 압축 후에도 임계값 아래로 내려가지 않으면 값을 떼어내 pg_toast.pg_toast_<oid> 테이블로 옮긴 뒤 본 테이블에는 18바이트짜리 포인터만 남깁니다. 판정하려면 세 지표를 함께 읽어야 합니다. pg_column_compression이 비어 있으면 압축은 없었고, pg_relation_size가 페이지 한 장이면 값이 본 테이블에 없으며, pg_column_size는 어느 경우든 실제 저장 크기를 돌려주므로 단독으로는 근거가 되지 못합니다. 외부 저장된 컬럼은 읽을 때마다 TOAST 테이블 조회가 따라붙으므로, 이런 컬럼이 있는 테이블에 SELECT * 를 습관적으로 쓰면 필요 없는 I/O를 매번 지불하게 됩니다.
-- update가 잦은 테이블의 HOT 비율을 올리려고 적용ALTERTABLE members SET(fillfactor=80);
VACUUM members;
며칠이 지 나도 기존 데이터가 들어 있는 페이지들의 밀도는 그대로이고 HOT update 비율도 거의 오르지 않았습니다. 원인은 무엇입니까?
정답fillfactor는 새로 쓰이는 페이지에만 적용되고, 일반 VACUUM은 페이지를 재구성하지 않습니다.
ALTER TABLE은 storage parameter만 바꿀 뿐 이미 놓인 튜플을 건드리지 않습니다. 일반 VACUUM도 dead tuple을 회수해 재사용 가능으로 표시할 뿐, 살아 있는 튜플을 옮겨 밀도를 낮추지는 않습니다. 기존 페이지에 여유를 만들려면 테이블을 통째로 다시 쓰는 VACUUM FULL, CLUSTER, pg_repack이 필요합니다.
ANALYZE는 플래너용 통계를 수집할 뿐 페이지 배치와 무관합니다. 어떤 통계 갱신도 이미 디스크에 놓인 튜플의 위치를 바꾸지 않습니다.
fillfactor를 80보다 더 낮춰야 효과가 나타납니다.
값이 아니라 적용 시점이 문제입니다. 기존 페이지가 다시 쓰이지 않는 한 70으로 낮춰도 결과는 같습니다.
해설
ALTER TABLE로 바꾼 fillfactor는 그 이후 새로 할당되거나 새로 채워지는 페이지에만 효력이 있습니다. 이미 가득 찬 페이지는 그대로 남고, 그 페이지의 row를 UPDATE하면 새 버전이 들어갈 자리가 없어 다른 페이지로 나가면서 HOT 조건이 깨집니다. 일반 VACUUM은 dead tuple의 line pointer를 LP_UNUSED로 되돌려 그 자리를 재사용 가능하게 만들 뿐, 남은 튜플을 밀어 모아 여유를 만드는 재구성은 하지 않습니다. 기존 페이지까지 새 밀도로 다시 쓰려면 테이블 전체를 다시 쓰는 작업이 필요하고, 운영 중이라면 락을 오래 잡지 않는 pg_repack이 현실적인 선택입니다.
이 snapshot을 들고 있는 트랜잭션에게, xid 750이 INSERT한 튜플은 보입니까?
보입니다. 750이 xmin(748)과 xmax(752) 사이에 있습니다.
그 구간에 있다는 사실만으로는 아무것도 결정되지 않습니다. 구간 안의 xid는 xip 목록에 있으면 진행 중, 없으면 이미 종료된 것으로 갈립니다. 749나 751이었다면 이 답이 맞습니다.
정답안 보입니다. 750이 xip 목록에 있어 snapshot을 찍은 순간 아직 진행 중이었습니다.
xip 목록은 snapshot 생성 시점에 끝나지 않은 트랜잭션의 명단입니다. INSERT가 확정되지 않은 상태이므로 이 튜플은 존재 자체가 보이지 않습니다. 750이 그 뒤에 커밋하더라도 이 snapshot을 쓰는 동안에는 판정이 바뀌지 않습니다.
보입니다. 750은 xmax(752)보다 작으므로 이미 커밋된 과거입니다.
xmax보다 작다는 것은 snapshot을 찍을 때 이미 시작은 했다는 뜻일 뿐입니다. 시작한 것과 끝난 것은 다릅니다.
CLOG를 읽어봐야 알 수 있습니다.
CLOG는 종료된 트랜잭션이 커밋인지 롤백인지 가릴 때 필요합니다. 750은 xip 목록에 있어 아직 종료되지 않았으므로, CLOG를 볼 것도 없이 안 보인다고 판정됩니다.
해설
xmin:xmax:xip_list 형식은 xid 번호선을 세 구간으로 나눕니다. 748 미만은 모두 종료된 과거, 752 이상은 아직 시작하지 않은 미래, 그 사이는 xip 목록으로 갈립니다. 목록에 있으면 진행 중이라 안 보이고, 목록에 없으면 종료된 것이므로 CLOG에서 커밋 여부를 확인한 뒤 판정합니다. 여기서 749와 751은 구간 안에 있지만 목록에 없으므로 이미 끝난 트랜잭션이고, 커밋되었다면 그들이 만든 튜플은 보입니다. 구간 안에 있다는 것과 보인다는 것을 같은 말로 읽으면 이후 판정이 전부 어긋납니다.
Session A는 Repeatable Read로 트랜잭션을 열어 snapshot 743:749:743을 확보했습니다. 그 뒤 Session B가 xid 749로 balance를 2000으로 UPDATE하고 커밋했습니다. 지금 Session A가 SELECT balance FROM accounts WHERE id = 1을 실행하면 무엇이 나옵니까?
2000. Session B가 커밋을 마쳤으므로 최신 값이 보입니다.
커밋 여부는 판정의 한 축일 뿐이고 다른 축이 snapshot입니다. 749는 Session A가 snapshot을 찍은 뒤에 시작한 트랜잭션이라, 커밋을 했든 안 했든 이 snapshot에서는 존재하지 않는 미래입니다.
두 행이 모두 나옵니다. 페이지에 튜플이 두 개 있습니다.
물리적으로 두 개가 있는 것은 맞지만 가시성 판정이 그중 하나만 통과시킵니다. 여러 버전이 동시에 존재하면서도 결과 집합에는 하나만 나오게 하는 것이 MVCC가 하는 일입니다.
에러가 납니다. 다른 트랜잭션이 같은 row를 이미 커밋했습니다.
읽기만으로는 충돌이 나지 않습니다. Repeatable Read에서 에러가 발생하는 것은 이 트랜잭션이 같은 row를 쓰려고 할 때입니다.
정답1000. 구 버전이 보입니다.
구 버전의 t_xmin은 740이라 snapshot의 xmin(743)보다 작은 확정된 과거이고, t_xmax는 749라 snapshot의 xmax(749)와 같아 아직 시작하지 않은 미래로 분류됩니다. 만든 트랜잭션은 보이고 죽인 트랜잭션은 보이지 않으므로 이 튜플은 살 아 있는 것으로 판정됩니다.
해설
판정은 두 단계로 나뉩니다. 먼저 t_xmin이 이 snapshot에서 보이는가, 그다음 t_xmax가 유효한 삭제인가. 구 버전은 t_xmin 740이 snapshot의 xmin(743) 아래라 1단계를 통과하고, t_xmax 749가 snapshot의 xmax(749) 이상이라 아직 일어나지 않은 삭제로 판정되어 살아남습니다. 신 버전은 t_xmin 749가 같은 이유로 미래에 속해 1단계에서 탈락합니다. Session B의 커밋은 실제로 끝났지만 Session A의 snapshot은 그 사건보다 먼저 찍혔고, 그 사진을 트랜잭션 끝까지 그대로 들고 가는 것이 Repeatable Read입니다.
-- Session A, 앞의 트랜잭션을 그대로 유지한 채 이어서 실행UPDATE accounts SET balance = balance +500WHERE id =1;
Session A가 같은 트랜잭션 안에서 이어서 이 UPDATE를 실행합니다. 무슨 일이 일어납니까?
정답직렬화 실패 에러가 나고 트랜잭션이 중단됩니다.
Repeatable Read에서 UPDATE는 자기 snapshot이 보는 버전이 아니라 row의 최신 버전을 잡으려 합니다. 그런데 그 최신 버전은 snapshot을 찍은 뒤에 커밋된 것이라, PostgreSQL은 조용히 덮어쓰는 대신 could not serialize access due to concurrent update 에러로 트랜잭션을 중단시킵니다.
1000에 500을 더한 1500이 저장됩니다.
그렇게 되면 Session B가 커밋한 2000이 흔적 없이 사라집니다. lost update를 막기 위해 Repeatable Read는 이 상황을 성공이 아니라 에러로 처리합니다.
2000에 500을 더한 2500이 저장됩니다.
Read Committed의 동작입니다. 그쪽은 UPDATE 직전에 snapshot을 다시 찍어 최신 버전 위에서 계산합니다. snapshot을 다시 찍지 않는 Repeatable Read는 같은 결말에 도달할 수 없습니다.
Session B의 트랜잭션이 끝날 때까지 대기하다가 진행됩니다.
Session B는 이미 커밋했으므로 기다릴 대상이 없습니다. 대기는 상대가 아직 진행 중일 때의 이야기이고, 여기서는 기다림 없이 즉시 판정이 납니다.
해설
읽기는 snapshot이 보여주는 버전을 그대로 쓰면 되지만 쓰기는 그럴 수 없습니다. row의 최신 버전을 잡지 않으면 다른 트랜잭션이 커밋한 변경을 덮어쓰게 되기 때문입니다. Repeatable Read는 이 모순을 감추지 않고 에러로 드러냅니다. 그래서 이 격리 수준을 쓰는 애플리케이션은 직렬화 실패를 잡아 트랜잭션을 통째로 재시도하는 경로를 반드시 갖춰야 합니다. 재시도 없이 격리 수준만 올리면 이상 현상 대신 실패율을 얻게 됩니다. Read Committed는 매 문장마다 snapshot을 다시 찍어 이 에러를 피하는 대신, 한 트랜잭션 안에서 같은 SELECT가 다른 값을 돌려주는 non-repeatable read를 허용합니다.
대량 INSERT 직후, 아무것도 변경하지 않고 SELECT count(*)만 반복해서 돌렸습니다. 그런데 첫 실행에서 상당한 양의 페이지가 dirty로 표시되고 이후 checkpoint에서 디스크 쓰기가 발생했습니다. 원인은 무엇입니까?
SELECT가 통계를 갱신하느라 시스템 카탈로그에 write가 발생했습니다.
플래너용 통계는 ANALYZE와 autovacuum이 갱신합니다. SELECT는 실행 계획을 세울 때 그 통계를 읽기만 합니다.
count(*)가 중간 집계 결과를 디스크에 쓰는 과정에서 발생했습니다.
임시 파일은 work_mem을 넘기는 정렬이나 해시에서 생기고, 그 경우에도 shared_buffers의 페이지를 dirty로 만들지는 않습니다. 단 순 집계는 임시 파일 자체를 만들지 않습니다.
정답가시성 판정 결과를 튜플 헤더의 hint bit에 캐싱하면서 페이지가 변경되었습니다.
첫 조회는 각 튜플의 xmin이 커밋되었는지 CLOG를 뒤져야 합니다. 그 결과를 t_infomask에 HEAP_XMIN_COMMITTED 같은 비트로 적어두면 다음 조회부터 CLOG를 건너뛸 수 있고, 이 기록이 페이지를 dirty로 만듭니다.
Seq Scan이 읽은 페이지를 shared_buffers에 올리는 것 자체가 dirty 표시입니다.
읽어서 올리기만 한 버퍼는 clean 상태입니다. dirty는 버퍼의 내용이 디스크와 달라졌다는 뜻이라, 페이지를 실제로 수정해야만 붙습니다.
해설
가시성 판정의 진실 소스는 CLOG(pg_xact)입니다. 트랜잭션당 2비트로 커밋 여부를 기록하는데, 튜플마다 이걸 조회하면 비쌉니다. 그래서 PostgreSQL은 한 번 확인한 결과를 튜플 헤더 t_infomask의 hint bit에 캐시해둡니다. 읽기만 하는 쿼리가 페이지를 수정하는 이 동작 때문에, 대량 INSERT 직후의 첫 조회는 읽기 쿼리인데도 쓰기 부하를 만듭니다. 같은 쿼리를 두 번째 돌릴 때 빨라지는 이유의 상당 부분이 여기에 있습니다. 벤치마크에서 첫 회차 수치를 그대로 쓰면 이 일회성 비용을 조회 성능으로 잘못 읽게 됩니다.
CLOG는 freeze가 진행되면서 오래된 구간이 잘려나가므로 무한히 자라지 않습니다. 문제는 저장 공간이 아니라 번호 공간의 소비 속도입니다.
정답읽기만 하는 트랜잭션에도 xid가 강제로 할당되어 xid 소비 속도가 올라갑니다.
이 함수는 xid가 없는 트랜잭션에 xid를 그 자리에서 발급합니다. 1초에 하나면 하루 약 8만 6천 개, 한 달이면 약 260만 개가 데이터 변경 없이 소비됩니다. 값만 확인할 목적이라면 발급하지 않는 pg_current_xact_id_if_assigned()를 써야 합니다.
부작용은 없습니다. 읽기 전용 쿼리는 트랜잭션 ID를 소비하지 않습니다.
일반적으로는 맞지만 이 함수가 예외입니다. read-only 트랜잭션은 원래 xid를 받지 않는데, 이 함 수는 돌려줄 값을 만들기 위해 xid를 강제로 할당합니다.
매 호출이 snapshot을 새로 찍어 oldest xmin이 계속 앞당겨지고 VACUUM이 막힙니다.
방향이 반대입니다. 짧게 끝나는 트랜잭션은 oldest xmin을 붙잡지 않습니다. VACUUM을 막는 것은 오래 열려 있는 트랜잭션입니다.
해설
pg_current_xact_id()는 현재 트랜잭션의 xid를 돌려주는데, 아직 xid가 없으면 그 자리에서 발급합니다. PostgreSQL이 read-only 트랜잭션에 xid를 주지 않는 이유가 32비트뿐인 번호 공간을 아끼기 위해서인데, 이 호출이 그 절약을 무력화합니다. 한 달에 260만 개면 21억 개인 번호 공간을 당장 위협하지는 않습니다. 문제는 age(relfrozenxid)가 데이터 변경과 무관하게 계속 자라 anti-wraparound VACUUM이 그만큼 일찍 발동한다는 점이고, 쓰기가 거의 없는 인스턴스일수록 이 인위적인 소비가 차지하는 비중이 커집니다. 확인만 할 목적이라면 xid가 이미 할당된 경우에만 값을 돌려주고 아니면 NULL을 주는 pg_current_xact_id_if_assigned()가 정확한 도구입니다. 관측 도구가 관측 대상의 상태를 바꿔놓는 사례라, 모니터링 쿼리를 상시로 걸 때는 이런 부작용을 먼저 확인하는 편이 안전합니다.
dead tuple은 전부 사라졌는데 파일 크기는 UPDATE 직후와 똑같은 13MB입니다. VACUUM이 실패한 것입니까?
실패했습니다. dead tuple 수거를 막는 long-running 트랜잭션이 있습니다.
그런 상황이면 dead_tuple_count가 0으로 떨어지지 않습니다. 이 출력은 오히려 수거가 완료되었다는 증거입니다.
통계가 갱신되지 않았을 뿐입니다. ANALYZE를 돌리면 크기가 줄어듭니다.
pg_relation_size는 통계가 아니라 실제 파일 크기를 읽습니다. ANALYZE는 플래너용 통계만 갱신하며 파일에 손대지 않습니다.
인덱스 정리 단계가 남아 있어 다음 VACUUM에서 줄어듭니다.
인덱스 정리는 이번 VACUUM 안에서 이미 끝났고, 인덱스 파일과 힙 파일의 크기는 별개입니다. 몇 번을 더 돌려도 일반 VACUUM은 힙 파일 중간을 잘라내지 않습니다.
정답정상입니다. 일반 VACUUM은 공간을 재사용 가능으로 표시할 뿐 OS에 반환하지 않습니다.
dead tuple의 line pointer를 LP_UNUSED로 되돌리고 FSM에 여유 공간을 기록하는 것까지가 일반 VACUUM의 일입니다. 이후 INSERT가 그 자리부터 채우므로 테이블이 더 커지지는 않지만, 파일 길이 자체는 줄지 않습니다.
해설
일반 VACUUM이 하는 일은 dead tuple이 쓰던 자리를 비워 재사용 가능하게 만드는 것까지입니다. line pointer가 LP_UNUSED로 돌아가고 FSM에 여유 공간이 기록되면 이후 INSERT와 UPDATE가 그 자리를 먼저 채우므로 테이블은 더 커지지 않습니다. 다만 파일 길이는 그대로입니다. 예외가 하나 있는데, 파일 끝쪽 페이지가 통째로 비면 VACUUM이 그만큼을 잘라냅니다. 실제 테이블에서 dead tuple은 파일 전체에 흩어져 있어 이 조건이 거의 성립하지 않습니다. 파일 크기를 실제로 줄이려면 살아 있는 튜플만 새 파일에 옮겨 쓰는 VACUUM FULL이나 pg_repack이 필요하고, 그 대가로 테이블 크기만큼의 임시 공간과 락을 내주게 됩니다.
직렬화 실패 에러를 받은 Session A가 롤백도 커밋도 하지 않고 idle in transaction 상태로 몇 시간 방치되었습니다. 이 세션 하나가 데이터베이스에 만드는 영향은 무엇입니까?
정답이 트랜잭션의 xmin이 수거 기준선을 붙잡아, 이후 죽은 튜플이 어느 테이블에서도 회수되지 않습니다.
VACUUM은 활성 중인 모든 snapshot에서 보이지 않는 튜플만 수거합니다. 열려 있는 트랜잭션 하나가 옛 snapshot을 붙들고 있으면 기준선이 그 시점에 못 박히고, 그 뒤에 죽 은 튜플은 전부 대상에서 빠집니다. 정확히는 같은 데이터베이스에 접속한 백엔드끼리 공유하는 기준선이라, 이 세션이 건드리지도 않은 테이블까지 함께 영향을 받습니다.
Session A가 실제로 읽은 accounts 테이블에서만 VACUUM이 회수에 실패합니다.
수거 기준선은 테이블별 값이 아닙니다. 같은 데이터베이스에 접속한 모든 백엔드의 xmin 중 가장 오래된 값 하나로 정해지므로, 어느 테이블을 읽었는지와 무관하게 모든 테이블의 기준이 함께 밀립니다.
에러가 난 트랜잭션은 이미 무효 상태라 수거 기준선에 영향을 주지 않습니다.
문장이 실패해도 트랜잭션은 열린 상태로 남습니다. ROLLBACK이나 COMMIT이 들어와야 자원이 풀리고, 그전까지는 snapshot을 그대로 붙들고 있습니다.
잠금이 걸린 row에만 영향이 있고 VACUUM 동작과는 무관합니다.
락과 가시성은 별개의 축입니다. 이 세션은 락을 하나도 잡고 있지 않아도 xmin horizon만으로 VACUUM을 막을 수 있습니다.
해설
열린 트랜잭션 하나가 만드는 피해는 그 트랜잭션이 만진 데이터에 한정되지 않습니다. VACUUM이 튜플을 수거하려면 현재 활성 중인 어떤 snapshot에서도 보이지 않는다는 것이 확인되어야 하고, 그 기준선은 같은 데이터베이스에서 실행 중인 모든 트랜잭션의 xmin 중 가장 오래된 값입니다. 다른 데이터베이스의 세션은 공유 카탈로그에만 영향을 주지만, 한 인 스턴스에 데이터베이스를 하나만 두는 흔한 구성에서는 사실상 전역입니다. 몇 시간 열려 있는 세션이 하나만 있어도 그 시점 이후의 dead tuple이 전부 살아남아 n_dead_tup은 계속 오르고, autovacuum은 열심히 돌면서도 아무것도 회수하지 못합니다. 같은 이유로 relfrozenxid도 전진하지 못해 오래 방치되면 wraparound 압력까지 커집니다. idle_in_transaction_session_timeout으로 이런 세션을 자동으로 끊는 것이 표준적인 방어이고, 오래된 replication slot이 xmin을 고정하고 있을 때도 증상이 똑같이 나타납니다.
-- PostgreSQL 18, 관련 파라미터는 모두 기본값-- autovacuum_vacuum_threshold = 50-- autovacuum_vacuum_scale_factor = 0.2-- autovacuum_vacuum_max_threshold = 100000000SELECT reltuples FROM pg_class WHERE relname ='events';-- reltuples-- --------------- 1000000000
10억 행짜리 events 테이블에서 autovacuum은 dead tuple이 몇 개를 넘겼을 때 발동합니까?
약 2억 개. 50 + 0.2 × 10억입니다.
PG 17까지의 답입니다. PG 18에서 추가된 상한이 이 계산 결과를 잘라내므로 실제 발동 시점은 절반으로 당겨집니다.
정답약 1억 개. 계산값 2억이 max_threshold에서 잘립니다.
기본 공식으로는 50 + 0.2 × 10억 = 2억 50이지만, PG 18의 autovacuum_vacuum_max_threshold가 상한이라 1억에서 잘립니다. 이 상한이 없던 시절에는 2억 개가 쌓일 때까지 기다렸습니다.
50개. threshold가 절대 기준이고 scale_factor는 보조 조건입니다.
두 값은 선택 관계가 아니라 덧셈입니다. threshold는 행이 아주 적은 테이블에서 scale_factor 항이 0에 가까워지는 것을 막는 하한 역할을 합니다.
판단할 수 없습니다. reltuples는 추정치라 임계값이 확정되지 않습니다.
reltuples가 추정치인 것은 맞지만 autovacuum은 그 추정치를 그대로 공식에 넣습니다. 임계값은 매번 계산되어 확정되며, 통계가 낡으면 기준이 어긋날 뿐 계산이 불가능해지지는 않습니다.
해설
autovacuum의 발동 기준은 autovacuum_vacuum_threshold에 autovacuum_vacuum_scale_factor 곱하기 reltuples를 더한 값입니다. 기본값으로 풀면 전체 행의 20%에 50행을 더한 만큼이라, 테이블이 클수록 기준이 느슨해집니다. 10억 행이면 2억 개가 죽을 때까지 기다린다는 뜻이고, 그쯤이면 Seq Scan 성능은 이미 크게 떨어져 있습니다. PG 18에서 추가된 autovacuum_vacuum_max_threshold(기본 1억)가 계산값을 잘라 이 문제를 완화하지만 1억도 여전히 큰 숫자입니다. 대용량 테이블은 테이블별로 기준을 따로 잡는 것이 실무 표준이라, autovacuum_vacuum_scale_factor를 0.01로, threshold를 1000으로 내리면 1,000만 개 남짓에서 발동해 기준이 열 배가량 빡빡해집니다.
orders 테이블
현재 파일 크기 500 GB
bloat 비율 60% (살아 있는 데이터 200 GB)
남은 디스크 여유 150 GB
서비스 가동 24시간, 중단 불가
이 상황에서 취할 수 있는 조치로 가장 타당한 것은 무엇입니까?
VACUUM FULL을 트래픽이 적은 새벽 시간대에 실행합니다.
시간대를 옮겨 락 문제를 완화해도 디스크 문제는 그대로 남습니다. 작업 중에는 원본 500GB와 사본 200GB가 함께 존재해야 하는데 여유가 150GB라 시작조차 하지 못합니다.
정답먼저 autovacuum 기준을 조여 악화를 멈추고, 공간 회수는 디스크를 확보한 뒤 pg_repack으로 진행합니다.
VACUUM FULL과 pg_repack은 둘 다 살아 있는 튜플을 새 파일에 복사하므로 사본 200GB가 들어갈 자리가 먼저 있어야 합니다. 여유가 150GB뿐이면 어느 쪽도 시작할 수 없어 공간 확보가 선행 조건이고, 그동안 bloat가 더 자라지 않게 막아두는 것이 순서상 먼저입니다.
pg_repack을 바로 실행합니다. 온라인 도구라 지금 상태에서도 안전합니다.
pg_repack이 온라인인 것은 락 측면의 이야기이고, 디스크 측면에서는 VACUUM FULL과 같습니다. 새 테이블에 복사한 뒤 swap하는 방식이라 사본이 들어갈 공간이 똑같이 필요합니다.
일반 VACUUM을 공격적으로 돌려 파일 크기를 200GB로 되돌립니다.
일반 VACUUM은 공간을 재사용 가능으로 표시할 뿐 파일을 잘라내지 않습니다. 아무리 자주 돌려도 이미 부푼 500GB가 줄지는 않습니다. 다만 더 부풀지 않게 막는 효과는 확실해서, 다른 조치의 앞 단계로는 의미가 있습니다.
해설
세 도구의 차이를 락으로만 외워두면 이 상황에서 막힙니다. 락 측면에서는 일반 VACUUM이 가장 가볍고, pg_repack은 마지막 swap 순간에만 배타 락을 잡아 온라인으로 쓸 수 있으며, VACUUM FULL은 작업 내내 모든 접근을 막습니다. 그런데 디스크 측면에서는 pg_repack과 VACUUM FULL이 같은 편입니다. 둘 다 살아 있는 튜플을 새 파일에 복 사한 뒤 옛 파일을 지우므로, 작업 중 한때 원본과 사본이 함께 존재합니다. 여기서는 순간 최대 700GB가 필요한데 쓸 수 있는 것은 650GB라 시작 자체가 막힙니다. 인덱스도 함께 다시 만들어지므로 실제로는 이보다 더 필요합니다. 그래서 순서가 중요합니다. 먼저 autovacuum 기준을 조여 증가를 멈추고, 공간을 확보하고, 그다음에 서비스 영향이 가장 작은 도구로 회수합니다.
cost 한도에 도달했을 때 쉬는 시간을 늘리는 파라미터라, VACUUM은 더 자주 더 길게 멈춥니다. 운영 쿼리에 주는 영향은 줄지만 소요 시간은 오히려 늘어납니다.
정답조회에 쓰이지 않는 인덱스를 찾아 DROP합니다.
2단계 비용은 인덱스 개수에 정비례합니다. 8개 중 2개를 걷어내면 순회 자체가 2회 줄고, 그 인덱스에 dead 엔트리를 만들던 UPDATE 비용과 WAL도 함께 사라집니다.
INDEX_CLEANUP off를 기본 운영 방식으로 삼습니다.
그 회차의 2단계는 사라지지만 인덱스의 dead 엔트리는 그대로 쌓입니다. 인덱스 bloat가 커지면 Index Scan이 느려지고, 결국 훨씬 비싼 정리를 한 번에 치르게 됩니다. 긴급 상황에서 한 번 쓰는 옵션이지 상시 설정이 아닙니다.
정답maintenance_work_mem을 늘려 dead tuple TID가 한 패스에 담기게 합니다.
수집한 TID가 메모리에 다 들어가지 않으면 VACUUM은 구간을 나눠 여러 패스로 돌고, 패스마다 인덱스 8개를 전부 다시 순회합니다. 요약의 2패스가 그 흔적입니다. 한 패스로 끝나면 인덱스 순회 횟수가 절반이 됩니다.
해설
VACUUM의 2단계는 인덱스 개수와 패스 수의 곱만큼 반복됩니다. 그래서 줄이는 길도 두 갈래입니다. 인덱스 개수를 줄이거나 패스 수를 줄이는 것입니다. 패스가 여러 번으로 갈리는 이유는 1단계에서 모은 dead tuple TID가 maintenance_work_mem에 다 들어가지 않기 때문이고, 한도를 넘으면 그때까지 모은 것으로 2단계와 3단계를 수행한 뒤 남은 구간을 다시 스캔합니다. PG 17부터 TID 저장 구조가 radix tree 기반으로 바뀌어 같은 메모리로 훨씬 많은 TID를 담게 되었고 옛 1GB 상한도 사라졌습니다. 더 근본적인 해법은 dead tuple을 덜 만드는 쪽입니다. HOT update가 성립하면 인덱스에 dead 엔트리 자체가 생기지 않아 2단계가 치울 것이 줄어듭니다.