Question 1

커넥션이 하나 더 열려 backend가 fork될 때, 그 backend만을 위해 새로 확보되는 메모리는 무엇입니까?

Accepted Answer

backend가 쓰는 메모리는 두 갈래입니다. shared memory는 서버 시작 시 한 번 잡혀 모든 프로세스가 attach하는 공용 영역이고, private 메모리는 fork된 각 backend가 자기 쿼리를 처리하려고 따로 쓰는 영역입니다. 이 구분이 커넥션 비용의 실체입니다. 커넥션을 늘려도 shared_buffers는 그대로지만 private 메모리는 커넥션 수에 비례해 늘어납니다. pooler가 하는 일이 바로 이 비례 관계를 끊는 것입니다. 클라이언트 연결 수천 개를 backend 수십 개로 접어서, 늘어나는 쪽이 프로세스가 아니라 대기 큐가 되게 만듭니다.

Question 2

실제로 일하는 backend는 23개뿐입니다. 놀고 있는 742개가 성능을 갉아먹는 경로는 무엇입니까?

Accepted Answer

커넥션 수가 성능에 미치는 영향은 동시 실행량과 별개입니다. Postgres는 커넥션마다 프로세스를 두고, 살아있는 모든 프로세스의 상태를 proc array라는 공유 배열에 모아둡니다. 트랜잭션이 시작될 때 뜨는 스냅샷은 이 배열 전체를 훑어 진행 중인 xid 명단을 만드는 작업이라, 배열이 길어지면 아무 일도 하지 않는 커넥션이 모든 쿼리의 앞단에 비용을 붙입니다. 여기에 커넥션당 private 메모리까지 더해지면, 커넥션 수를 늘려도 처리량이 더는 늘지 않고 오히려 꺾이는 구간이 생깁니다.

Question 3

앞의 서버 앞단에 pooler를 두어 backend를 60개로 줄였습니다. 커넥션을 재사용하게 되면서 쿼리 한 번이 건너뛰는 구간은 어디까지입니까?

Accepted Answer

pooler의 이득은 두 갈래이고 성격이 다릅니다. 하나는 연결 재사용입니다. fork와 인증, 세션 초기화는 커넥션 하나당 한 번만 내면 되는 비용이라, 요청마다 연결을 열고 닫는 애플리케이션에서 곧바로 사라집니다. 다른 하나는 동시 backend 수 제한입니다. proc array 스캔과 private 메모리, 락 경합처럼 살아있는 프로세스 수에 비례하는 비용을 눌러줍니다. 반면 parser부터 executor까지는 어느 쪽으로도 줄어들지 않습니다. 쿼리 자체가 느린 문제를 pooler로 풀려고 하면 여기서 막힙니다.

Question 4

checkpoint마다 디스크 쓰기가 몰리는 것을 보고 background writer를 더 공격적으로 돌리기로 했습니다. 이 설정으로 실제로 얻게 되는 효과는 무엇입니까?

Accepted Answer

background writer를 checkpoint 부담을 나눠 지는 프로세스로 설명하는 경우가 있는데, 공식 문서가 말하는 주 목적은 clean buffer 확보입니다. 노리는 것은 사용자 쿼리를 처리하던 backend가 버퍼를 비우느라 디스크 쓰기를 기다리는 일을 없애는 것이고, checkpoint가 쓸 페이지가 줄어드는 것은 그 결과로 따라오는 부수 효과에 가깝습니다. 이 구분이 중요한 이유는 진단 방향이 달라지기 때문입니다. checkpoint 구간에서만 지연이 튄다면 checkpoint 쪽 파라미터를 봐야 하고, 특정 구간과 무관하게 쓰기가 많은 쿼리의 응답이 들쭉날쭉하다면 clean buffer가 모자란 것을 의심해야 합니다.

Question 5

이 결과를 가장 잘 설명하는 것은 무엇입니까?

Accepted Answer

Postgres는 자기 버퍼 풀과 OS 페이지 캐시라는 두 겹의 캐시 위에서 동작합니다. shared_buffers를 키우면 첫 겹은 두꺼워지지만 두 번째 겹은 그만큼 얇아집니다. 데이터가 메모리에 다 들어가는 규모라면 첫 겹만으로 끝나므로 키울수록 이득이지만, 데이터가 메모리보다 크면 어느 쪽에서도 못 찾는 요청이 남고 그 요청은 디스크까지 갑니다. 두 겹에 같은 페이지가 중복으로 올라가는 낭비까지 겹치면 전체 적중률은 오히려 떨어질 수 있습니다. 공식 문서가 40%를 넘겨 잡는 것이 더 작게 잡는 것보다 나을 가능성은 낮다고 못 박은 배경이 이것입니다. 값을 정하기 전에 데이터 크기와 메모리 크기의 관계를 먼저 봐야 합니다.

Question 6

8,136개 페이지를 read했는데 디스크 대기는 잡히지 않았습니다. 두 관측은 서로 모순입니까?

Accepted Answer

hit과 read는 executor가 storage 경계를 넘은 횟수이지 디스크에 닿은 횟수가 아닙니다. 경계는 shared_buffers 바깥에 그어져 있고 그 너머에 OS 페이지 캐시가 한 겹 더 있습니다. 그래서 read가 크게 잡혀도 지연은 작을 수 있고, 반대로 서버를 막 재시작해 두 캐시가 모두 비어 있으면 같은 read 수치에 훨씬 큰 지연이 붙습니다. 같은 쿼리가 어떤 날은 빠르고 어떤 날은 느린 현상 대부분이 이 두 겹의 상태 차이입니다. read 수치만 보고 shared_buffers를 키우는 진단으로 넘어가기 전에, 그 read가 실제로 기다림을 만들고 있는지를 대기 이벤트나 track_io_timing으로 확인해야 합니다.

Question 7

슬롯 2번의 t_ctid가 자기 자신이 아니라 (0,4)를 가리키고 있습니다. 이 값이 뜻하는 것은 무엇입니까?

Accepted Answer

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으로 처리되어 인덱스 엔트리가 추가되지 않았음까지 읽어낼 수 있습니다.

Question 8

MAXALIGN을 적용한 크기가 정확히 1,024바이트인 튜플 하나를 이 페이지에 INSERT할 수 있습니까?

Accepted Answer

페이지의 여유 공간은 pd_upper에서 pd_lower를 뺀 값입니다. 여기서는 1,120에서 96을 뺀 1,024바이트입니다. 그런데 INSERT 한 건은 이 공간을 양쪽에서 갉아먹습니다. 튜플 본체는 pd_upper를 앞으로 당기고, 그 튜플을 가리킬 ItemId 4바이트는 pd_lower를 뒤로 밉니다. 필요한 것은 1,028바이트이고 4바이트가 모자라 이 페이지는 후보에서 탈락합니다. PostgreSQL은 FSM을 뒤져 다른 페이지를 찾고, 없으면 파일 끝에 새 페이지를 붙입니다. 한 가지 덧붙이면, LP_UNUSED 상태의 빈 슬롯이 남아 있을 때는 그 ItemId를 재사용해 4바이트를 아낄 여지가 있습니다. 다만 페이지를 고를 때 쓰는 PageGetHeapFreeSpace는 재사용 가능한 슬롯이 있는지 따지지 않고 언제나 ItemId 4바이트를 미리 빼고 여유를 계산하므로, 이 페이지는 어느 쪽이든 후보에서 빠집니다.

Question 9

이 테이블의 페이지에는 새 튜플이 들어갈 여유가 충분합니다. 다음 UPDATE 중 HOT update로 처리될 수 있는 것은 무엇입니까?

Accepted Answer

HOT update의 첫 번째 조건은 갱신된 컬럼이 그 테이블의 어떤 인덱스 정의에도 등장하지 않는 것인데, 여기서 등장한다는 말의 범위가 넓습니다. 인덱스 키 컬럼은 물론이고 표현식 인덱스가 계산에 쓰는 컬럼, 부분 인덱스의 WHERE predicate에 나오는 컬럼까지 전부 포함됩니다. BRIN 같은 summarizing 인덱스만 이 검사에서 빠집니다. 두 번째 조건인 같은 페이지의 여유 공간은 문제에서 충족된다고 주었으니 판정은 컬럼 조건만으로 끝납니다. 운영에서 HOT 비율이 기대보다 낮게 나온다면, 무심코 만들어둔 부분 인덱스나 표현식 인덱스가 조건을 조용히 깨고 있는 경우가 많습니다.

Question 10

content 컬럼의 값에 실제로 무슨 일이 일어났습니까?

Accepted Answer

튜플 크기가 임계값(약 2KB)을 넘으면 PostgreSQL은 먼저 압축을 시도하고, 압축 후에도 임계값 아래로 내려가지 않으면 값을 떼어내 pg_toast.pg_toast_<oid> 테이블로 옮긴 뒤 본 테이블에는 18바이트짜리 포인터만 남깁니다. 판정하려면 세 지표를 함께 읽어야 합니다. pg_column_compression이 비어 있으면 압축은 없었고, pg_relation_size가 페이지 한 장이면 값이 본 테이블에 없으며, pg_column_size는 어느 경우든 실제 저장 크기를 돌려주므로 단독으로는 근거가 되지 못합니다. 외부 저장된 컬럼은 읽을 때마다 TOAST 테이블 조회가 따라붙으므로, 이런 컬럼이 있는 테이블에 SELECT * 를 습관적으로 쓰면 필요 없는 I/O를 매번 지불하게 됩니다.

Question 11

며칠이 지나도 기존 데이터가 들어 있는 페이지들의 밀도는 그대로이고 HOT update 비율도 거의 오르지 않았습니다. 원인은 무엇입니까?

Accepted Answer

ALTER TABLE로 바꾼 fillfactor는 그 이후 새로 할당되거나 새로 채워지는 페이지에만 효력이 있습니다. 이미 가득 찬 페이지는 그대로 남고, 그 페이지의 row를 UPDATE하면 새 버전이 들어갈 자리가 없어 다른 페이지로 나가면서 HOT 조건이 깨집니다. 일반 VACUUM은 dead tuple의 line pointer를 LP_UNUSED로 되돌리고 남은 튜플을 페이지 안에서 밀어 모아 빈틈을 없애기는 합니다. 다만 그렇게 되찾는 것은 죽은 튜플이 쓰던 공간뿐이고, 살아 있는 튜플을 다른 페이지로 옮기지는 못하므로 라이브 튜플로 꽉 찬 페이지의 밀도는 그대로입니다. 기존 페이지까지 새 밀도로 다시 쓰려면 테이블 전체를 다시 쓰는 작업이 필요하고, 운영 중이라면 락을 오래 잡지 않는 pg_repack이 현실적인 선택입니다.

Question 12

이 테이블의 row 하나가 페이지의 튜플 영역에서 차지하는 바이트 수와, 빈 페이지 한 장에 최대 몇 개가 들어가는지를 바르게 짝지은 것은 무엇입니까?

Accepted Answer

튜플 크기는 세 층의 정렬을 차례로 통과해야 나옵니다. 첫째, 사용자 데이터의 시작 지점인 t_hoff입니다. HeapTupleHeaderData의 struct 크기는 23바이트지만 t_hoff는 MAXALIGN의 배수여야 하므로 24부터 데이터가 시작합니다. NULL이 있으면 헤더 뒤에 null bitmap이 끼어드는데, 컬럼당 1비트라 8컬럼까지는 1바이트로 끝나고 23 + 1 = 24가 그대로 8의 배수라 t_hoff는 여전히 24입니다. 8컬럼 이하 테이블에서 bitmap은 정렬 패딩 자리를 얻어 쓰는 셈이라 공짜입니다. 둘째, 컬럼마다 자기 타입의 경계에서 시작합니다. sensor_id는 24부터 27까지 쓰고, 다음 taken_at은 8의 배수인 32부터 시작해야 하므로 28부터 31까지 4바이트가 패딩으로 버려집니다. 이어서 taken_at이 32부터 39, amount가 40부터 47, tag가 48부터 51이라 튜플 길이는 52바이트입니다. 셋째, 튜플이 페이지에 놓일 때 전체 크기가 다시 MAXALIGN되어 56바이트를 소비합니다. 페이지에서 쓸 수 있는 자리는 8,192에서 페이지 헤더 24를 뺀 8,168바이트이고 INSERT 한 건은 튜플 56에 ItemId 4를 더한 60바이트를 먹으므로 136개가 한계입니다. 실무에서 중요한 것은 두 번째 층입니다. 컬럼을 넓은 것부터 좁은 것 순으로 선언하면 이 테이블은 48바이트로 줄어 같은 페이지에 157개가 들어갑니다. 데이터는 한 글자도 안 바뀌었는데 선언 순서만으로 저장 공간이 15% 넘게 차이 납니다.

Question 13

이 snapshot을 들고 있는 트랜잭션에게, xid 750이 INSERT한 튜플은 보입니까?

Accepted Answer

xmin:xmax:xip_list 형식은 xid 번호선을 세 구간으로 나눕니다. 748 미만은 모두 종료된 과거이고, 752 이상은 snapshot을 찍은 시점에 아직 완료되지 않은 구간입니다(시작하지 않았거나 진행 중입니다). 그 사이는 xip 목록으로 갈립니다. 목록에 있으면 진행 중이라 안 보이고, 목록에 없으면 종료된 것이므로 CLOG에서 커밋 여부를 확인한 뒤 판정합니다. 여기서 749와 751은 구간 안에 있지만 목록에 없으므로 이미 끝난 트랜잭션이고, 커밋되었다면 그들이 만든 튜플은 보입니다. 구간 안에 있다는 것과 보인다는 것을 같은 말로 읽으면 이후 판정이 전부 어긋납니다.

Question 14

Session A는 Repeatable Read로 트랜잭션을 열어 snapshot 806:812:806을 확보했습니다. 그 뒤 Session B가 xid 812로 balance를 7000으로 UPDATE하고 커밋했습니다. 지금 Session A가 SELECT balance FROM accounts WHERE id = 1을 실행하면 무엇이 나옵니까?

Accepted Answer

판정은 두 단계로 나뉩니다. 먼저 t_xmin이 이 snapshot에서 보이는가, 그다음 t_xmax가 유효한 삭제인가. 구 버전은 t_xmin 799가 snapshot의 xmin(806) 아래라 1단계를 통과하고, t_xmax 812가 snapshot의 xmax(812) 이상이라 아직 확정되지 않은 삭제로 판정되어 살아남습니다. 신 버전은 t_xmin 812가 같은 이유로 미완료 구간에 속해 1단계에서 탈락합니다. Session B의 커밋은 실제로 끝났지만 Session A의 snapshot은 그 사건보다 먼저 찍혔고, 그 사진을 트랜잭션 끝까지 그대로 들고 가는 것이 Repeatable Read입니다.

Question 15

Session A가 같은 트랜잭션 안에서 이어서 이 UPDATE를 실행합니다. 무슨 일이 일어납니까?

Accepted Answer

읽기는 snapshot이 보여주는 버전을 그대로 쓰면 되지만 쓰기는 그럴 수 없습니다. 자기 snapshot이 보여준 버전을 그대로 갱신하면 다른 트랜잭션이 커밋한 변경이 덮여 사라지기 때문입니다. Repeatable Read는 이 모순을 감추지 않고 에러로 드러냅니다. 그래서 이 격리 수준을 쓰는 애플리케이션은 직렬화 실패를 잡아 트랜잭션을 통째로 재시도하는 경로를 반드시 갖춰야 합니다. 재시도 없이 격리 수준만 올리면 이상 현상 대신 실패율을 얻게 됩니다. Read Committed는 매 문장마다 snapshot을 다시 찍어 이 에러를 피하는 대신, 한 트랜잭션 안에서 같은 SELECT가 다른 값을 돌려주는 non-repeatable read를 허용합니다.

Question 16

대량 INSERT 직후, 아무것도 변경하지 않고 SELECT count(*)만 반복해서 돌렸습니다. 그런데 첫 실행에서 상당한 양의 페이지가 dirty로 표시되고 이후 checkpoint에서 디스크 쓰기가 발생했습니다. 원인은 무엇입니까?

Accepted Answer

가시성 판정의 진실 소스는 CLOG(pg_xact)입니다. 트랜잭션당 2비트로 커밋 여부를 기록하는데, 튜플마다 이걸 조회하면 비쌉니다. 그래서 PostgreSQL은 한 번 확인한 결과를 튜플 헤더 t_infomask의 hint bit에 캐시해둡니다. 읽기만 하는 쿼리가 페이지를 수정하는 이 동작 때문에, 대량 INSERT 직후의 첫 조회는 읽기 쿼리인데도 쓰기 부하를 만듭니다. 같은 쿼리를 두 번째 돌릴 때 빨라지는 이유의 상당 부분이 여기에 있습니다. 벤치마크에서 첫 회차 수치를 그대로 쓰면 이 일회성 비용을 조회 성능으로 잘못 읽게 됩니다.

Question 17

이 스크립트를 몇 달간 계속 돌리면 어떤 부작용이 생깁니까?

Accepted Answer

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()가 정확한 도구입니다. 관측 도구가 관측 대상의 상태를 바꿔놓는 사례라, 모니터링 쿼리를 상시로 걸 때는 이런 부작용을 먼저 확인하는 편이 안전합니다.

Question 18

t_xmin이 412로 같은 이 두 튜플에 대해 가시성 판정은 각각 어떻게 납니까?

Accepted Answer

freeze는 튜플을 옮기거나 값을 다시 쓰는 작업이 아니라 헤더에 표식 하나를 남기는 작업입니다. VACUUM이 충분히 오래된 튜플을 만나면 t_infomask에 HEAP_XMIN_FROZEN을 세팅하고, 가시성 검사는 이 비트가 켜져 있으면 snapshot과의 xmin 비교를 아예 수행하지 않고 커밋된 과거로 확정합니다. 그래서 lp=1은 t_xmin이 412로 남아 있어도 그 값이 판정에 쓰이지 않습니다. 9.4 이전에는 t_xmin을 FrozenTransactionId(2)로 덮어쓰는 방식이었고, 지금은 원래 xid를 보존해 이 튜플을 누가 넣었는지 나중에 확인할 수 있게 합니다. 값이 남아 있다는 것과 그 값으로 비교한다는 것은 별개입니다. 표식이 없는 lp=2가 겪는 일이 wraparound입니다. xid 순서는 modulo 2^32 비교라 어떤 xid에서 보든 과거로 읽히는 범위는 2^31개(약 21억 4700만)뿐이고, 그보다 멀어진 xid는 미래로 뒤집힙니다. 412는 현재 xid에서 약 26억 떨어져 있어 미래에 해당하고, 미래 트랜잭션이 만든 튜플은 아직 확정되지 않은 INSERT로 취급되어 Step 1을 통과하지 못합니다. 디스크에는 데이터가 그대로 있는데 SELECT 결과에서만 사라지는 형태의 유실이고, freeze는 오래된 튜플을 이 비교의 대상에서 빼내 그 사고를 원천적으로 막습니다.

Question 19

ORDER BY created_at DESC LIMIT 10이 정렬 노드 없이 인덱스만으로 끝날 수 있는 구조적 근거는 무엇입니까?

Accepted Answer

B-tree의 리프가 좌우로 연결된 리스트라는 사실 하나가 range 조회와 정렬을 동시에 설명합니다. equality 조회는 루트에서 리프까지 한 번 내려가면 끝나지만, BETWEEN이나 ORDER BY LIMIT은 시작점을 찾은 뒤 옆으로 걸어가야 합니다. 이때 매번 루트부터 다시 내려간다면 range가 길어질수록 비용이 폭증하겠지만, sibling 포인터 덕분에 다음 리프로 곧장 넘어갑니다. DESC 정렬도 같은 체인을 반대 방향으로 읽으면 되므로 별도 구조가 필요 없습니다.

Question 20

세 컬럼 조건이 모두 리프에서 읽을 구간의 시작과 끝을 좁히는 데 쓰이는 쿼리는 어느 것입니까?

Accepted Answer

복합 인덱스의 선두 컬럼 규칙은 정렬 방식에서 그대로 따라나옵니다. (tenant_id, status, created_at)은 tenant_id로 먼저 줄을 세우고, 같은 tenant_id 안에서 status로, 같은 status 안에서만 created_at으로 정렬합니다. 어떤 컬럼이 구간의 경계를 좁히려면 그 앞의 컬럼들이 모두 하나의 값으로 고정되어 있어야 합니다. 그래서 설계 규칙이 equality를 앞에 몰고 range는 마지막에 하나만 두는 형태가 됩니다. range가 중간에 끼면 그 뒤 컬럼은 인덱스 안에 있어도 읽을 양을 줄여주지 못하고, 이미 읽은 엔트리를 걸러내는 데만 쓰입니다.

Question 21

이름은 Index Only Scan인데 Heap Fetches가 47,106입니다. 무슨 일이 일어나고 있습니까?

Accepted Answer

Index-Only Scan은 힙을 안 읽겠다는 약속이 아니라 읽지 않아도 되면 건너뛰겠다는 최적화입니다. 판단 근거가 Visibility Map이고, 이 비트를 세우는 주체는 VACUUM입니다. 그래서 대량 INSERT나 UPDATE 직후에는 이름만 Index Only Scan이고 실제로는 튜플마다 힙을 읽는 상태가 됩니다. read=45938이라는 숫자가 그 증거입니다. 인덱스 리프 몇 장으로 끝날 스캔이 힙 페이지 수만 장을 읽고 있습니다. 대상 테이블에 VACUUM을 돌리고 나서 같은 계획의 Heap Fetches가 0으로 떨어지는지 보면 진단이 확정됩니다.

Question 22

email에 B-tree 인덱스가 있는데도 prefix 검색이 Seq Scan으로 갑니다. 원인은 무엇입니까?

Accepted Answer

B-tree가 잘하는 일은 정렬된 순서 위에서 연속된 구간을 뽑는 것 하나입니다. LIKE 'kim%'는 그 구간으로 번역될 수 있어 인덱스를 쓸 수 있고, LIKE '%kim'은 정렬 어디에도 연속 구간이 없어 쓸 수 없습니다. 여기에 한 겹이 더 있습니다. 번역이 성립하려면 인덱스의 정렬 순서가 바이트 순서와 같아야 합니다. 로케일 비교는 대소문자나 부호를 무시하는 등 바이트 순서와 다르게 줄을 세우므로, 같은 prefix를 가진 문자열이 인덱스에서 붙어 있다고 보장할 수 없습니다. text_pattern_ops는 이 보장을 돌려주는 대신 ORDER BY email이 기대하는 로케일 정렬을 포기하게 만들므로, 둘 다 필요하면 인덱스를 두 개 두게 됩니다.

Question 23

VACUUM으로 Heap Fetches를 0으로 되돌린 뒤 SELECT 목록에 total_amount를 추가했더니 계획이 Index Scan으로 바뀌었습니다. INCLUDE (total_amount)로 인덱스를 다시 만들 때 실제로 얻는 것은 무엇입니까?

Accepted Answer

INCLUDE는 인덱스를 검색용 자료구조에서 결과 전달용 저장소로 조금 확장하는 장치입니다. 키 컬럼은 정렬과 탐색에 쓰이면서 내부 노드에도 복사되지만, INCLUDE 컬럼은 리프에만 붙어 Index-Only Scan을 성립시키는 역할만 합니다. 그래서 자주 함께 조회되지만 조건으로는 쓰이지 않는 컬럼이 INCLUDE의 자리입니다. 반대로 그 컬럼으로 범위를 좁히거나 정렬하고 싶다면 키 컬럼이어야 합니다. 어느 쪽이든 인덱스가 커지고 쓰기 비용이 늘어나므로, 힙 접근이 실제로 병목인지를 Heap Fetches와 buffers로 먼저 확인한 뒤에 결정하는 순서가 맞습니다.

Question 24

created_at은 이 인덱스의 유일한 키 컬럼입니다. 그런데도 인덱스를 타지 않는 이유는 무엇입니까?

Accepted Answer

partial index는 인덱스 크기와 갱신 비용을 조건에 맞는 행만큼으로 줄여주는 대신, 쓸 수 있는 쿼리를 좁힙니다. 판단 기준은 단순합니다. 쿼리의 조건이 인덱스의 predicate를 논리적으로 함의해야 합니다. status = 'pending'은 함의하고, status IN ('pending', 'paid')는 함의하지 않습니다. 후자를 인덱스로 처리하려면 pending 구간은 partial index로, paid 구간은 다른 경로로 읽어 합쳐야 하는데 플래너는 그런 조합을 만들지 않습니다. 같은 함정이 expression index에도 있습니다. LOWER(email)로 만든 인덱스는 쿼리도 LOWER(email)로 써야 걸리고, email로 쓰면 표현식이 달라 후보에 오르지 않습니다.

Question 25

tags jsonb 컬럼에 B-tree 인덱스를 만들어도 tags @> '["urgent"]' 조건이 인덱스를 타지 못하는 구조적 이유는 무엇입니까?

Accepted Answer

B-tree의 전제는 한 행이 하나의 키로 결정된다는 것입니다. 이 전제가 깨지는 질문이 두 종류 있습니다. 하나는 한 행이 여러 조각으로 쪼개져야 하는 경우입니다. 문서 안의 단어, 배열의 원소, JSON의 경로가 전부 여기에 해당하고 GIN이 담당합니다. 다른 하나는 값끼리 크고 작음으로 줄을 세울 수 없는 경우입니다. 도형이 겹치는지, 시간 범위가 겹치는지 같은 질문이고 GiST가 담당합니다. 어떤 인덱스를 고를지는 데이터 타입이 아니라 던지는 질문의 모양에서 결정됩니다.

Question 26

검색 지연이 간헐적으로 튀는 원인은 무엇입니까?

Accepted Answer

GIN은 쓰기가 비쌉니다. 문서 하나를 넣을 때 그 안의 단어 수백 개 각각의 posting list를 건드려야 하기 때문입니다. fastupdate는 이 비용을 뒤로 미루는 장치라, 새 엔트리를 정렬되지 않은 pending list에 모아두었다가 나중에 한꺼번에 병합합니다. 병합은 VACUUM이나 autoanalyze가 지나갈 때, gin_clean_pending_list()를 직접 부를 때, 리스트가 gin_pending_list_limit(기본 4MB)을 넘을 때 일어납니다. 그래서 쓰기가 몰리는 구간에 리스트가 부풀고 검색이 그만큼 느려집니다. 쓰기 폭주가 상시라면 해당 테이블의 autovacuum 주기를 줄이거나, 검색 지연의 일관성이 더 중요하면 fastupdate를 끄고 쓰기 쪽에서 비용을 내는 선택이 있습니다.

Question 27

같은 컬럼, 같은 확장인데 (B)만 인덱스를 못 씁니다. (B)를 인덱스로 풀려면 무엇이 필요합니까?

Accepted Answer

pg_trgm은 GIN과 GiST 양쪽에 opclass를 제공해서 둘 중 무엇을 골라야 하는지가 헷갈리는 대표 지점입니다. 기준은 질문이 들어있냐 없냐인지, 얼마나 가까운지인지입니다. LIKE '%kim%'은 포함 여부만 묻고 결과에 순서가 필요 없으므로 역색인에서 ctid를 긁어모아 비트맵을 만드는 GIN이 빠릅니다. ORDER BY name <-> 'kim' LIMIT 10은 거리라는 순서를 요구하므로, 요약을 들고 있는 노드부터 거리를 재며 가지를 쳐낼 수 있는 GiST가 필요합니다. 두 쿼리를 다 써야 한다면 인덱스를 두 개 두게 되고, 그때는 쓰기 비용이 두 배로 드는 것을 감수할 만한지가 판단 기준이 됩니다.

Question 28

BRIN이 제 몫을 못 하는 이유로 맞는 것은 무엇입니까?

Accepted Answer

BRIN은 인덱스가 작아지는 대신 정밀도를 포기하는 거래입니다. 그 거래가 성립하려면 디스크 저장 순서와 값의 순서가 거의 일치해야 합니다. 일치하면 각 묶음의 min과 max 구간이 서로 겹치지 않아 조건에 걸린 몇 개만 읽고 나머지를 통째로 버릴 수 있습니다. 어긋나면 모든 묶음의 구간이 전체 범위를 덮어버려 어느 것도 배제하지 못하고, 결국 순차 스캔에 요약 읽기 비용만 얹은 꼴이 됩니다. 판단 지표가 pg_stats.correlation이고 절댓값 0.9 이상이 실질 하한선입니다. append-only 로그는 이 조건을 자연스럽게 만족하지만, 갱신이 잦은 OLTP 테이블은 시간이 지나며 이 값이 떨어지므로 주기적으로 다시 봐야 합니다.

Question 29

같은 인덱스인데 두 번째 조건만 후보에 오르지 못합니다. 이유는 무엇입니까?

Accepted Answer

인덱스가 있는데도 계획에 등장조차 하지 않는다면 cost 비교 이전에 자격 문제입니다. 어떤 연산자를 인덱스로 처리할 수 있는지는 opclass가 정합니다. jsonb에는 두 가지가 있습니다. 기본 jsonb_ops는 키와 값을 모두 별도 엔트리로 넣어 포함 관계와 키 유무를 다 지원하고, jsonb_path_ops는 경로와 값을 묶어 해싱해서 포함 관계 계열만 지원하는 대신 인덱스가 작습니다. 어느 쪽을 고를지는 앞으로 던질 조건에 ? 계열이 들어가는지로 갈립니다. 이미 만들어둔 뒤에 조건이 늘어나면 이 문제가 나타나므로, Seq Scan이 나왔을 때 opclass부터 확인하는 습관이 진단 시간을 줄여줍니다.

Question 30

token 인덱스를 B-tree에서 Hash로 바꾸는 판단에 대한 설명으로 맞는 것은 무엇입니까?

Accepted Answer

Hash가 B-tree를 이기는 구간은 좁고 분명합니다. 키가 커서 저장 비용 차이가 크고, 조회가 equality 하나뿐이고, 유일성 제약이 필요 없고, 행 수가 급격히 늘지 않는 경우입니다. 네 조건 중 하나만 어긋나도 B-tree가 낫습니다. 키가 작으면 아끼는 공간이 얼마 안 되고, 범위나 정렬이 하나라도 있으면 인덱스를 하나 더 만들어야 하며, UNIQUE가 필요하면 Hash로는 애초에 불가능하고, 행 수가 빠르게 늘면 버킷을 늘리는 작업이 사용자 INSERT와 같은 흐름에서 일어나 쓰기 지연을 키웁니다. 한번 커진 Hash 인덱스는 REINDEX 말고는 줄일 방법이 없다는 점도 함께 봐야 합니다.

Question 31

status(고유값 3개)와 amount(값이 거의 다 다름)에 ANALYZE가 남기는 통계는 어떻게 갈립니까?

Accepted Answer

플래너가 selectivity를 구하는 경로는 두 갈래이고 컬럼의 성격에 따라 어느 쪽이 일하는지가 갈립니다. 등치 조건의 값이 MCV에 있으면 저장된 빈도를 그대로 씁니다. MCV 밖이면 MCV가 덮지 못한 나머지 비율을 MCV에 없는 고유값 개수로 나눠 추정합니다. 범위 조건은 histogram 경계선 배열에서 그 값이 어디쯤인지를 보간해 비율을 계산합니다. 그래서 범주형 컬럼의 등치 조건은 MCV가, 연속형 컬럼의 범위 조건은 histogram이 받쳐줍니다. 어느 쪽도 없는 경우가 문제가 되는데, 표현식으로 감싼 컬럼이 대표적입니다.

Question 32

같은 컬럼에 같은 인덱스인데 조건 값에 따라 계획이 갈립니다. 이 선택을 지배하는 것은 무엇입니까?

Accepted Answer

인덱스가 있으면 항상 인덱스를 타는 것이 아니라, 읽어야 할 행의 비중이 어느 선을 넘으면 순차 읽기가 이깁니다. 그 선을 계산하는 재료가 통계입니다. 그래서 같은 SQL의 파라미터만 바꿔도 계획이 뒤집힐 수 있고, 이는 버그가 아니라 설계된 동작입니다. 실무에서 이 성질이 문제가 되는 지점은 prepared statement입니다. 파라미터 값이 정해지지 않은 채 만들어진 일반 계획은 특정 값의 빈도를 반영하지 못하므로, 값의 분포가 심하게 치우친 컬럼에서는 어느 쪽 값에도 최적이 아닌 계획이 굳어질 수 있습니다.

Question 33

추정 3,813에 실제 10,000입니다. 플래너가 3,813이라는 숫자를 만들어낸 경로는 무엇입니까?

Accepted Answer

플래너는 여러 조건이 붙으면 각각의 선택도를 구해 곱합니다. 이 곱셈은 두 조건이 서로 무관하다는 가정 위에 서 있고, 그 가정이 깨지는 순간 추정이 어긋납니다. 도시와 나라, 우편번호와 시도, 제품 코드와 카테고리처럼 한쪽이 다른 쪽을 결정하는 관계가 실무에 흔합니다. 이런 컬럼 쌍에서는 조건을 하나 더 붙여도 실제로는 걸러지는 행이 거의 없는데, 플래너는 매번 곱하므로 조건을 붙일수록 추정이 실제보다 작아집니다. 어긋나는 방향이 항상 과소 추정이라는 점이 특히 위험합니다. 작게 잡힌 행 수가 위쪽 노드의 알고리즘 선택을 좌우하기 때문입니다.

Question 34

이 오차를 CREATE STATISTICS로 고치기로 했습니다. 이 조치가 노드 하나를 넘어 중요한 이유는 무엇입니까?

Accepted Answer

추정 오차를 판단할 때 그 노드에서 시간이 얼마나 더 걸리는지가 아니라 그 숫자가 위쪽에서 무엇을 결정하는지를 봐야 합니다. dependencies 통계는 city 값을 알면 country가 정해진다는 함수 종속을 표본에서 찾아내, 두 조건의 선택도를 곱하지 않도록 막아줍니다. 강하게 연관된 컬럼 쌍에서는 거의 항상 효과가 있습니다. 다만 확장 통계도 만들어두면 자동으로 갱신되는 것이 아니라 ANALYZE가 다시 지나가야 채워지고, 컬럼 조합마다 따로 만들어야 하므로 조합이 많으면 관리 대상이 늘어납니다.

Question 35

email이 UNIQUE라 많아야 한 행일 텐데 추정이 50행입니다. 이 숫자는 어디서 나왔습니까?

Accepted Answer

통계는 컬럼 단위로 수집되므로 컬럼을 함수로 감싸는 순간 참조할 것이 사라집니다. 그러면 플래너는 연산자마다 정해둔 기본 선택도로 후퇴하고, 등치 조건의 기본값은 0.5%입니다. 이 값은 데이터를 전혀 반영하지 않으므로 어떤 방향으로든 크게 틀릴 수 있습니다. 해법은 그 표현식 자체에 인덱스를 만드는 것입니다. 표현식 인덱스가 있으면 ANALYZE가 그 표현식을 평가한 값으로 별도의 통계를 수집합니다. 인덱스를 실제로 타지 않더라도 통계만으로 추정이 제자리를 찾는 경우가 있고, 같은 이유로 통계를 얻으려고 표현식 인덱스를 만드는 것이 정당한 선택이 되기도 합니다.

Question 36

계획도 cost도 추정 행 수도 같은데 실행 시간만 20배 차이가 납니다. 어디에서 갈렸습니까?

Accepted Answer

cost가 낮은데 실제로 느리다면 계획을 더 들여다보기 전에 Buffers의 read 값을 먼저 봐야 합니다. cost 모델이 캐시 상태를 모른다는 사실이 이 진단의 근거입니다. 플래너는 페이지를 몇 장 읽어야 하는지까지만 계산하고, 그 페이지가 메모리에 있는지 디스크에서 와야 하는지는 계산에 넣지 않습니다. 그래서 같은 계획이 캐시가 더운 상태와 찬 상태에서 전혀 다른 시간을 보입니다. 이 경우 손댈 곳은 계획이 아니라 shared_buffers 크기, 배치 순서, 또는 대량 스캔이 캐시를 밀어내지 않도록 작업을 분리하는 쪽입니다.

Question 37

outer 조건을 u.id = 42에서 u.id < 10000으로 바꾸자 계획이 Nested Loop에서 Hash Join으로 넘어갔습니다. 두 알고리즘의 cost는 입력 크기에 어떻게 다르게 반응합니까?

Accepted Answer

세 알고리즘의 성격은 cost 공식의 모양에 다 들어 있습니다. Nested Loop는 outer 행 수와 안쪽 조회 비용의 곱, Hash Join은 build와 probe의 합, Merge Join은 양쪽 정렬 비용에 병합 비용을 더한 값입니다. 곱셈인 Nested Loop는 outer가 작을 때 무적이고 커질수록 급격히 나빠집니다. 덧셈인 Hash Join은 양쪽이 아무리 커도 선형으로만 늘어나는 대신 등가 조건에서만 쓸 수 있고 메모리를 요구합니다. 어떤 알고리즘이 좋고 나쁜 것이 아니라, 어느 구간에 있느냐로 승자가 바뀝니다.

Question 38

41초가 걸렸습니다. 가장 먼저 손댈 곳은 어디입니까?

Accepted Answer

Nested Loop가 위험해지는 지점은 알고리즘 자체가 아니라 outer 크기를 잘못 추정했을 때입니다. 추정이 1행이면 안쪽 조회 비용이 1회분으로만 잡혀 어떤 대안보다 싸 보이고, 실제로 수만 행이 들어오면 그 조회가 수만 번 돌아갑니다. 같은 오차가 Hash Join에서는 훨씬 덜 아픕니다. 덧셈 구조라 입력이 커진 만큼만 선형으로 늘어나기 때문입니다. 그래서 계획을 읽을 때 안쪽 노드의 loops와 바깥 노드의 추정 대 실측을 함께 보는 습관이 중요합니다. 한 번의 시간이 0에 가까워도 loops가 크면 그 곱이 전체를 지배합니다.

Question 39

이 계획에서 가장 먼저 시도할 조정과 그 대가는 무엇입니까?

Accepted Answer

Hash Join에서 Batches가 2 이상이면 그 자리에 없앨 수 있는 디스크 왕복이 있다는 신호입니다. 확인은 쉽습니다. 세션에서만 한도를 올려 다시 찍어보고 Batches가 1로 떨어지는지, temp 지표가 사라지는지 보면 됩니다. 어려운 쪽은 그 다음입니다. work_mem은 세션 하나에 하나씩 주어지는 값이 아니라 정렬과 해시 노드마다 따로 잡히는 값이라, 노드가 셋인 쿼리를 커넥션 100개가 동시에 돌리면 상한이 그만큼 곱해집니다. 그래서 전역으로 올리기보다 문제가 확인된 쿼리나 배치 세션에만 걸어주는 편이 안전합니다.

Question 40

Merge Join은 셋 중 가장 드물게 보이지만 특정 상황에서는 Hash Join을 이깁니다. 어떤 상황입니까?

Accepted Answer

Merge Join의 cost는 정렬 둘에 병합 하나입니다. 정렬 비용이 살아 있으면 Hash Join의 덧셈에 밀리지만, 그 비용이 0이 되는 순간 경쟁력이 생깁니다. 0이 되는 경로는 두 가지입니다. 양쪽 조인 키에 B-tree 인덱스가 있어 인덱스 스캔이 정렬된 순서를 그대로 내보내는 경우, 그리고 아래쪽 노드가 이미 정렬을 만들어 올려보내는 경우입니다. 여기에 결과가 정렬된 상태로 필요한 쿼리라면 이득이 한 번 더 붙습니다. Hash Join으로 갔다면 마지막에 Sort를 얹어야 하는데 Merge Join은 그대로 내보내면 됩니다.

Question 41

양쪽 다 큰데 플래너가 Nested Loop만 내놓습니다. 다른 두 알고리즘이 후보에 오르지 못하는 이유는 무엇입니까?

Accepted Answer

조인 알고리즘의 선택은 비용 비교 이전에 자격 심사를 통과해야 합니다. Hash Join과 Merge Join은 등가 조건 전용이고, 범위나 비등가 조건은 Nested Loop만 처리할 수 있습니다. IP 대역 매칭, 유효 기간 매칭, 구간별 요율표 조인이 실무에서 이 함정에 자주 걸립니다. 해법은 조건을 등가로 바꿔줄 무언가를 만드는 것입니다. 범위를 고정 크기 버킷으로 쪼개 버킷 키를 등가 조인에 쓰거나, range 타입과 GiST 인덱스를 써서 안쪽 조회 단가를 낮추는 방향입니다. 안쪽이 인덱스로 싸지면 Nested Loop 자체는 나쁜 선택이 아닙니다.

Question 42

여기서 먼저 확인할 설정은 무엇입니까?

Accepted Answer

조인 순서는 알고리즘 선택과 별개의 축이고, 테이블이 늘수록 조합이 폭발하므로 플래너는 탐색을 스스로 제한합니다. 그래서 테이블 수가 어느 선을 넘는 순간 계획의 성격이 달라질 수 있습니다. 12개를 넘으면 전수 비교에서 근사 탐색으로 바뀌고, 명시적 JOIN을 쓴 쿼리는 8개를 넘는 부분이 작성 순서에 묶입니다. 사람이 보기에 이상한 순서가 나왔다면 이 두 한계선부터 확인하는 것이 순서입니다. 한계를 올리면 탐색 시간이 늘어나므로 무한정 키울 수는 없고, 대안으로 쿼리를 CTE로 쪼개 조인 블록을 나누는 방법이 있습니다.

Question 43

Workers Launched는 2인데 안쪽 노드의 loops는 3입니다. 나머지 하나는 어디서 왔습니까?

Accepted Answer

병렬 계획을 읽을 때 실행 흐름 수를 worker 수와 헷갈리면 처리량 계산이 어긋납니다. leader는 결과를 모으기만 하는 관리자가 아니라 자기 몫을 함께 처리하는 참여자입니다. Workers Planned가 2면 셋이 나눠 읽고, 4면 다섯이 나눠 읽습니다. 계산할 때는 loops를 보는 것이 가장 확실합니다. leader가 참여하지 않기를 원한다면 parallel_leader_participation을 끌 수 있는데, leader가 위쪽 노드 처리에 바빠 병목이 되는 경우에나 의미가 있고 보통은 그대로 두는 편이 낫습니다.

Question 44

같은 SELECT인데 INSERT 안에 넣자 병렬이 사라졌습니다. 이유는 무엇입니까?

Accepted Answer

병렬이 켜지는 조건은 세 가지가 AND로 붙습니다. 테이블이 하한 크기를 넘고, 병렬 오버헤드를 감수할 만큼 cost가 크고, 쿼리의 모든 요소가 병렬 안전해야 합니다. 실무에서 가장 자주 걸리는 것은 세 번째이고 그중에서도 쓰기와 행 잠금입니다. SELECT ... FOR UPDATE, DECLARE CURSOR로 만든 커서, PARALLEL UNSAFE 함수가 같은 이유로 병렬을 끕니다. 특히 사용자 정의 함수는 명시하지 않으면 기본이 unsafe이므로, 집계 쿼리에 자체 함수를 하나 끼워넣은 순간 병렬이 통째로 사라지는 일이 생깁니다.

Question 45

Workers Planned는 4인데 하나도 뜨지 않았습니다. 어디를 확인해야 합니까?

Accepted Answer

Workers Planned는 계획이고 Workers Launched는 실행 결과입니다. 둘이 다르면 원인은 플래너가 아니라 실행 시점의 자원 상황에 있습니다. worker는 커넥션마다 따로 잡히는 자원이 아니라 서버 하나가 max_parallel_workers만큼만 들고 있는 공용 자원이라, 병렬 쿼리 여럿이 동시에 몰리면 뒤에 온 쿼리부터 요청이 거절됩니다. max_parallel_workers_per_gather를 올려 쿼리 하나의 병렬도를 키우는 조정이 이 상황을 악화시킬 수 있다는 점도 함께 봐야 합니다. 한 쿼리가 더 많이 가져갈수록 다른 쿼리에 남는 몫이 줄어듭니다.

Question 46

파티션 키로 조건을 걸었는데도 36개가 전부 스캔됩니다. 원인과 해법은 무엇입니까?

Accepted Answer

pruning이 걸렸는지 확인하는 방법은 Append 아래 자식 노드 수를 세는 것입니다. 잘려나간 파티션은 EXPLAIN 출력에 흔적조차 남지 않기 때문입니다. 걸리지 않는 원인은 몇 가지로 수렴합니다. 파티션 키를 함수나 CAST로 감싼 경우, 파티션 키 타입과 다른 타입의 리터럴을 비교해 세션 타임존에 의존하는 변환이 끼는 경우, 그리고 prepared statement가 값과 무관한 일반 계획으로 굳은 경우입니다. 세 번째는 실행 시점 pruning이 어느 정도 보완하지만 완전하지는 않아서, 의심스러우면 plan_cache_mode를 세션에서 바꿔 차이를 확인해볼 수 있습니다.

Question 47

이 도입으로 얻는 것과 얻지 못하는 것을 바르게 짝지은 것은 무엇입니까?

Accepted Answer

파티셔닝을 도입할 때 기대와 실제가 가장 크게 갈리는 지점이 보관 정책과 VACUUM입니다. 오래된 데이터를 지우는 일이 DELETE에서 DROP TABLE로 바뀌는 것은 성능 개선이 아니라 작업의 성격이 바뀌는 것입니다. 몇 시간짜리 배치가 순간에 끝나고, 그 뒤에 따라오던 VACUUM과 인덱스 정리도 함께 사라집니다. 반면 살아 있는 데이터를 관리하는 비용은 줄지 않고, 파티션 수만큼 관리 대상이 늘고, planning 비용도 늘고, 유일성 제약과 파티션 키를 바꾸는 UPDATE에는 새로운 제약이 붙습니다. 그래서 판단 기준은 테이블이 큰가가 아니라 조건이 파티션 키로 자주 좁혀지는가와 보관 정책이 파티션 경계와 맞는가입니다.

Question 48

파티션 12개짜리 테이블 둘을 같은 파티션 키로 조인하는 리포트가 느립니다. enable_partitionwise_join을 켜면 무엇이 달라집니까?

Accepted Answer

partitionwise 최적화는 파티션 경계라는 정보를 조인과 집계에까지 활용하는 장치입니다. 같은 키로 같은 경계에 나뉜 두 테이블이라면 조인은 파티션 쌍 안에서만 일어날 수 있으므로, 큰 조인 하나를 작은 조인 여럿으로 바꿀 수 있습니다. 여기서 오는 이득은 두 갈래입니다. 각 조인의 입력이 작아져 메모리 안에서 끝나고, 파티션마다 독립적이라 병렬화도 쉬워집니다. 대가는 플래너가 다뤄야 할 경우의 수입니다. 기본값이 off인 이유가 여기 있고, 그래서 파티션 수가 적당하고 조인이 실제로 병목인 환경에서만 켜는 것이 맞습니다.

Question 49

dead tuple은 전부 사라졌는데 파일 크기는 UPDATE 직후와 똑같은 13MB입니다. VACUUM이 실패한 것입니까?

Accepted Answer

일반 VACUUM이 하는 일은 dead tuple이 쓰던 자리를 비워 재사용 가능하게 만드는 것까지입니다. line pointer가 LP_UNUSED로 돌아가고 FSM에 여유 공간이 기록되면 이후 INSERT와 UPDATE가 그 자리를 먼저 채우므로 테이블은 더 커지지 않습니다. 다만 파일 길이는 그대로입니다. 예외가 하나 있는데, 파일 끝쪽 페이지가 통째로 비면 VACUUM이 그만큼을 잘라냅니다. 실제 테이블에서 dead tuple은 파일 전체에 흩어져 있어 이 조건이 거의 성립하지 않습니다. 파일 크기를 실제로 줄이려면 살아 있는 튜플만 새 파일에 옮겨 쓰는 VACUUM FULL이나 pg_repack이 필요하고, 그 대가로 테이블 크기만큼의 임시 공간과 락을 내주게 됩니다.

Question 50

이 세션 하나가 데이터베이스에 만드는 영향은 무엇입니까?

Accepted Answer

열린 트랜잭션 하나가 만드는 피해는 그 트랜잭션이 만진 데이터에 한정되지 않습니다. VACUUM이 튜플을 수거하려면 현재 활성 중인 어떤 snapshot에서도 보이지 않는다는 것이 확인되어야 하고, 그 기준선은 같은 데이터베이스에서 실행 중인 모든 트랜잭션의 xmin 중 가장 오래된 값입니다. 다른 데이터베이스의 세션은 공유 카탈로그에만 영향을 주지만, 한 인스턴스에 데이터베이스를 하나만 두는 흔한 구성에서는 사실상 전역입니다. 몇 시간 열려 있는 세션이 하나만 있어도 그 시점 이후의 dead tuple이 전부 살아남아 n_dead_tup은 계속 오르고, autovacuum은 열심히 돌면서도 아무것도 회수하지 못합니다. 같은 이유로 relfrozenxid도 전진하지 못해 오래 방치되면 wraparound 압력까지 커집니다. idle_in_transaction_session_timeout으로 이런 세션을 자동으로 끊는 것이 표준적인 방어이고, 오래된 replication slot이 xmin을 고정하고 있을 때도 증상이 똑같이 나타납니다. 한 가지 구분할 것은, 에러를 받고 idle in transaction (aborted)로 남은 세션은 여기에 해당하지 않는다는 점입니다. 문장이 실패하는 순간 abort가 기록되면서 그 백엔드는 xid와 xmin을 이미 내려놓았고 락도 풀었기 때문에, 접속 슬롯만 붙들고 있을 뿐 VACUUM을 막지는 않습니다.

Question 51

10억 행짜리 events 테이블에서 autovacuum은 dead tuple이 몇 개를 넘겼을 때 발동합니까?

Accepted Answer

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만 개 남짓에서 발동해 기준이 열 배가량 빡빡해집니다.

Question 52

이 상황에서 취할 수 있는 조치로 가장 타당한 것은 무엇입니까?

Accepted Answer

세 도구의 차이를 락으로만 외워두면 이 상황에서 막힙니다. 락 측면에서는 일반 VACUUM이 가장 가볍고, pg_repack은 마지막 swap 순간에만 배타 락을 잡아 온라인으로 쓸 수 있으며, VACUUM FULL은 작업 내내 모든 접근을 막습니다. 그런데 디스크 측면에서는 pg_repack과 VACUUM FULL이 같은 편입니다. 둘 다 살아 있는 튜플을 새 파일에 복사한 뒤 옛 파일을 지우므로, 작업 중 한때 원본과 사본이 함께 존재합니다. 여기서는 순간 최대 700GB가 필요한데 쓸 수 있는 것은 650GB라 시작 자체가 막힙니다. 인덱스도 함께 다시 만들어지므로 실제로는 이보다 더 필요합니다. 그래서 순서가 중요합니다. 먼저 autovacuum 기준을 조여 증가를 멈추고, 공간을 확보하고, 그다음에 서비스 영향이 가장 작은 도구로 회수합니다.

Question 53

이 테이블의 2단계 소요를 지속적으로 줄이는 조치는 무엇입니까? (정답 2개)

Accepted Answer

VACUUM의 2단계는 인덱스 개수와 패스 수의 곱만큼 반복됩니다. 그래서 줄이는 길도 두 갈래입니다. 인덱스 개수를 줄이거나 패스 수를 줄이는 것입니다. 패스가 여러 번으로 갈리는 이유는 1단계에서 모은 dead tuple TID가 maintenance_work_mem에 다 들어가지 않기 때문이고, 한도를 넘으면 그때까지 모은 것으로 2단계와 3단계를 수행한 뒤 남은 구간을 다시 스캔합니다. PG 17부터 TID 저장 구조가 radix tree 기반으로 바뀌어 같은 메모리로 훨씬 많은 TID를 담게 되었고 옛 1GB 상한도 사라졌습니다. 더 근본적인 해법은 dead tuple을 덜 만드는 쪽입니다. HOT update가 성립하면 인덱스에 dead 엔트리 자체가 생기지 않아 2단계가 치울 것이 줄어듭니다.

Question 54

large_orders는 지금 어느 단계에 있고, 이대로 두면 다음에 무엇이 일어납니까?

Accepted Answer

네 임계값은 age(relfrozenxid) 하나를 놓고 순서대로 걸리는 관문입니다. 관측값 164,300,000을 그 위에 얹으면 위치가 바로 나옵니다. 5천만은 이미 한참 지났고, 1.5억을 1,430만 넘겼으며, 2억까지 3,570만이 남았고, 16억은 아직 자릿수가 다릅니다. 그래서 지금은 aggressive 구간입니다. 일반 VACUUM은 VM에서 all-visible로 표시된 페이지를 건너뛰지만, 이 구간에서는 all-frozen이 아닌 페이지를 전부 스캔하며 오래된 xid를 걷어냅니다. 여기서 실무 판단이 갈립니다. 이 테이블은 autovacuum_enabled가 off라 dead tuple이 아무리 쌓여도 autovacuum이 찾아오지 않고, 그래서 age가 여기까지 밀린 것입니다. 하지만 2억은 그 설정이 통하지 않는 선이라, 3,570만 트랜잭션 뒤 anti-wraparound VACUUM이 시각을 가리지 않고 발동하며 취소도 되지 않습니다. 그때 이 VACUUM은 16억에 한참 못 미치므로 failsafe가 아니라 평소의 cost 스로틀링을 그대로 받으며 도는데, 12GB짜리 테이블이면 트래픽이 몰리는 시간에 몇 시간을 끌 수 있습니다. 그래서 손이 닿을 때 한산한 시간대에 VACUUM을 직접 걸어두는 편이 낫습니다. 한 번 aggressive로 돌고 나면 relfrozenxid가 전진해 age가 많아야 vacuum_freeze_min_age 수준인 5천만으로 내려가고, 0이 되지는 않습니다. 이 값이 다음 관문까지의 여유를 정하므로, age가 0으로 리셋된다고 가정하고 주기를 잡으면 계산이 어긋납니다.

Question 55

SELECT ... FOR UPDATE로 10만 행을 한 번에 잠갔습니다. 이때 서버에서 벌어지는 일로 맞는 것은 무엇입니까?

Accepted Answer

PostgreSQL의 행 락에는 별도의 저장소가 없습니다. UPDATE나 DELETE가 튜플의 xmax에 자기 xid를 적는 행위 자체가 잠갔다는 표시이고, 다른 트랜잭션은 그 값을 보고 대기 여부를 정합니다. 이 설계의 이점은 잠근 행 수와 무관하게 메모리가 늘지 않는다는 것입니다. 대량 갱신이 락 자원 때문에 실패하는 일이 없습니다. 여러 트랜잭션이 같은 행에 공유 락을 걸어야 할 때는 xid 하나로 표현할 수 없으므로 여러 xid를 묶은 MultiXactId를 쓰고, infomask 비트로 그 사실을 표시합니다.

Question 56

ALTER는 아직 시작도 못 했는데 그 뒤에 들어온 SELECT까지 멈췄습니다. 왜 그렇습니까?

Accepted Answer

ALTER TABLE이 위험한 이유는 락이 세서가 아니라 락을 기다리는 동안에도 뒤를 막기 때문입니다. 앞에 긴 트랜잭션 하나만 있으면 그 뒤로 오는 모든 요청이 함께 멈춥니다. 그래서 DDL을 돌리기 전에 lock_timeout을 3초에서 5초 정도로 짧게 걸어두는 것이 표준 관행입니다. 그 안에 락을 못 잡으면 에러로 빠져나오므로 뒤에 줄이 길게 늘어서기 전에 상황이 정리되고, 긴 트랜잭션을 먼저 정리한 뒤 다시 시도하면 됩니다. 같은 이유로 idle_in_transaction_session_timeout을 걸어 열어둔 채 방치된 트랜잭션을 자동으로 끊는 설정도 함께 씁니다.

Question 57

두 세션이 모두 Read Committed일 때 B가 풀린 뒤 balance는 얼마가 되고, 둘 다 Repeatable Read였다면 무엇이 달라집니까?

Accepted Answer

같은 충돌에서 두 격리 수준이 정반대로 반응합니다. Read Committed는 현실에 맞춰 갈아타고, Repeatable Read는 자기가 본 세상과 어긋나면 포기합니다. 어느 쪽이 옳은가는 무엇을 쓰는지에 달려 있습니다. balance = balance - 100처럼 계산이 데이터베이스 안에서 끝나면 Read Committed의 재평가로 충분합니다. 반대로 값을 애플리케이션으로 가져와 계산한 뒤 그 결과를 상수로 쓰는 코드라면, 재평가가 일어나도 이미 낡은 값으로 만든 결과를 쓰게 되므로 SELECT ... FOR UPDATE로 먼저 잠그거나 Repeatable Read로 올려 재시도하는 쪽을 택해야 합니다.

Question 58

이 현상의 이름과, Repeatable Read가 막지 못하는 이유는 무엇입니까?

Accepted Answer

read-modify-write 패턴에서 읽는 대상과 쓰는 대상이 다르면 snapshot isolation의 사각지대에 들어갑니다. 규칙은 여러 행에 걸쳐 성립하는데 검사는 각자 따로 하고, 쓰기는 서로 다른 행에 하기 때문입니다. 해결책은 세 가지입니다. Serializable로 올리고 40001 에러에 재시도를 붙이거나, 검사 대상을 하나의 행으로 만들어 그 행에 SELECT ... FOR UPDATE를 걸거나, 규칙 자체를 데이터베이스 제약으로 표현하는 것입니다. 세 번째가 가능하면 가장 튼튼하지만 합계 상한 같은 규칙은 제약으로 옮기기 어려워서, 실무에서는 두 번째가 가장 자주 쓰입니다.

Question 59

Serializable로 바꾼 뒤 실행 중에는 아무도 대기하지 않는데 커밋에서 실패하는 트랜잭션이 생깁니다. 이 동작을 설명하는 것은 무엇입니까?

Accepted Answer

SSI가 다른 격리 수준과 크게 다른 점은 비용을 실행 중이 아니라 커밋 시점에 청구한다는 것입니다. 읽기는 아무도 막지 않고 대기도 만들지 않으므로 처리량이 크게 떨어지지 않지만, 대신 어떤 트랜잭션은 다 해놓고 마지막에 취소됩니다. 판정은 안전한 쪽으로 기울어 있어서, 실제로는 문제가 없었을 조합도 취소될 수 있습니다. 놓치는 경우는 없으므로 커밋에 성공한 것들만 놓고 보면 결과는 반드시 어떤 직렬 실행으로 설명됩니다. 그래서 Serializable을 쓰기로 했다면 40001을 잡아 트랜잭션 전체를 처음부터 다시 실행하는 재시도 wrapper가 선택이 아니라 전제 조건입니다.

Question 60

이 코드를 어떻게 고치면 deadlock이 애초에 만들어지지 않습니까?

Accepted Answer

deadlock은 버그가 아니라 동시성이 있는 시스템의 정상적인 가능성이고, PostgreSQL의 감지 기능은 그것을 처리하는 정상 메커니즘입니다. 그래도 만들지 않을 수 있으면 만들지 않는 편이 낫고, 가장 단순한 원칙이 잠그는 순서를 전역에서 하나로 고정하는 것입니다. 문제는 비즈니스 로직이 복잡해지면 어떤 행을 어떤 순서로 잠그게 될지 코드만 보고 알기 어려워진다는 점입니다. 그래서 현실적인 대응은 순서 고정과 재시도를 함께 두는 것입니다. 간헐적으로 나는 deadlock은 재시도로 흡수하고, 초당 수십 건씩 난다면 트랜잭션 범위와 잠금 순서를 다시 설계합니다.

Question 61

COMMIT 응답을 받은 직후 전원이 나갔습니다. 변경된 페이지가 아직 data file에 쓰이지 않았는데도 데이터가 살아남는 이유는 무엇입니까?

Accepted Answer

WAL의 규칙은 한 줄입니다. 데이터 페이지의 변경이 디스크에 쓰이기 전에 그 변경을 기술하는 record가 먼저 디스크에 써져야 합니다. 이 순서만 지키면 data file은 얼마든지 뒤처져 있어도 됩니다. 뒤처진 만큼은 WAL에 남아 있고, 재시작할 때 그 구간을 다시 적용하면 되기 때문입니다. 여기서 얻는 실질적 이득은 쓰기 패턴의 변화입니다. 흩어진 페이지를 매번 랜덤으로 내리는 대신, 이어붙이기만 하는 파일 하나를 순차로 쓰고 COMMIT에서 그것만 확정합니다.

Question 62

checkpoint_timeout은 기본값 5분 그대로입니다. 그런데 26초마다 checkpoint가 돕니다. 어떻게 이런 일이 가능합니까?

Accepted Answer

checkpoint는 시간과 WAL 양이라는 두 개의 방아쇠를 가집니다. 어느 쪽이 먼저 당겨지는지는 워크로드가 정합니다. 읽기 위주라면 시간이 먼저 오고, 쓰기가 많으면 WAL이 먼저 찹니다. 로그의 checkpoint starting 줄에 time인지 wal인지가 그대로 찍히므로 진단은 어렵지 않고, 그래서 log_checkpoints를 켜두는 것이 기본입니다. 26초 간격은 여기에 더해 write=22초와 함께 읽어야 합니다. 쓰는 데 22초가 걸리는 작업이 26초마다 돌아온다면 checkpointer가 거의 쉬지 않고 일하고 있다는 뜻입니다.

Question 63

checkpoint가 잦아진 것이 WAL 생성량을 3배로 키운 경로는 무엇입니까?

Accepted Answer

Full Page Write는 반쪽만 쓰인 페이지에서 복구하기 위한 장치입니다. 디스크는 8KB를 한 번에 쓰지 않으므로 쓰는 도중 전원이 나가면 앞뒤가 섞인 페이지가 남을 수 있고, 그 위에 차분을 적용하면 결과가 어긋납니다. 그래서 checkpoint 이후 첫 변경에는 페이지 전체를 함께 기록합니다. 비용은 WAL 볼륨이고, 그 비용의 크기를 정하는 것이 checkpoint 간격입니다. 간격이 짧을수록 첫 변경이 자주 돌아와 같은 워크로드가 훨씬 많은 WAL을 만듭니다. 대응은 두 갈래입니다. max_wal_size를 늘려 간격을 되돌리고, wal_compression을 켜 FPI 한 장의 크기를 줄이는 것입니다.

Question 64

crash recovery 도중 서버가 또 죽어도 다시 시작하면 결국 올바른 상태에 도달합니다. 이 멱등성을 만드는 것은 무엇입니까?

Accepted Answer

crash recovery가 안전한 이유는 두 장치가 겹쳐 있기 때문입니다. 하나는 페이지가 자기 마지막 변경 위치를 기억한다는 것이고, 다른 하나는 깨진 페이지를 통째로 되돌릴 수 있다는 것입니다. 앞의 것만 있으면 정상적인 페이지에 대해서는 중복 적용을 피할 수 있지만 torn page 앞에서 무너지고, 뒤의 것만 있으면 매번 처음부터 전부 다시 적용해야 합니다. 둘이 함께 있으므로 recovery는 몇 번을 다시 시작해도 같은 결과에 도달합니다. 이 성질이 있어야 recovery 도중의 장애를 별도로 처리하지 않아도 됩니다.

PostgreSQL 전체 문항

PostgreSQL 아키텍처 한눈에 보기