TOAST는 PostgreSQL에서 8KB 페이지 크기를 초과하는 행 데이터를 처리하기 위해 도입되어 큰 열을 별도의 물리적 저장소에 압축합니다. 논리적 복제는 WAL을 통해 변경 사항을 스트리밍할 때 REPLICA IDENTITY 설정이 포함된 이전 튜플 값을 결정합니다. 기본 REPLICA IDENTITY DEFAULT는 기본 키만 전송하지만 REPLICA IDENTITY FULL은 전체 이전 행 이미지를 전송합니다.
테이블에 ~2KB를 초과하고 TOAST로 압축된 JSONB 또는 TEXT 열이 포함되어 있을 때, 비-TOAST 열만 수정하는 UPDATE 작업은 WAL 레코드에 대한 외부 TOAST 값을 가져오지 못할 수 있습니다. 논리적 디코딩 프로세스는 I/O를 줄이기 위해 변경되지 않은 TOAST 포인터를 건너뛰므로, 구독자는 충돌 해결 중 이러한 큰 필드의 NULL 또는 누락된 값을 수신하게 됩니다.
REPLICA IDENTITY FULL로 전환하면 PostgreSQL이 WAL 레코드에 전체 이전 튜플을 포함하도록 강제하여 커밋 중 외부 저장소에서 모든 TOAST된 값을 명시적으로 가져옵니다. 이는 UPSERT 작업에 대한 데이터 완전성을 보장하지만, 모든 UPDATE가 행의 전체 이전 이미지를 기록해야 하므로 WAL 볼륨이 300-500% 증가합니다.
한 금융 거래 플랫폼은 규제 보고를 위해 주요 PostgreSQL 15 클러스터에서 데이터 웨어하우스로 주문서 스냅샷을 복제해야 했습니다. market_data 테이블은 깊이 정보가 포함된 큰 JSONB 페이로드(10-50KB)를 보관하고 있었습니다. 복제는 기본 키만 사용한 REPLICA IDENTITY DEFAULT와 함께 pglogical을 사용했습니다. 창고 쪽의 ETL 프로세스는 감사 로그의 델타 변경을 유지하기 위해 오래된 JSONB 값을 필요로 하는 느리게 변하는 차원 테이블을 유지하기 위한 UPSERT 작업을 수행하려고 했습니다.
주문서가 자주 업데이트되는 동안 JSONB 페이로드가 변경되지 않았던 고볼륨 거래 기간 동안, 논리적 복제 스트림은 신규 튜플 데이터와 기본 키만 포함하는 UPDATE 레코드를 보냈습니다. UPDATE 문이 단지 updated_at 타임스탬프 열만 수정했기 때문에 TOAST된 JSONB 이전 값은 변경 집합에 포함되지 않았습니다. ETL 프로세스는 사전 업데이트된 JSONB 상태에 접근할 수 없어 감사 추적을 위한 정확한 가격 변동 델타를 계산할 수 없었습니다. 이는 MiFID II 준수 요구를 위반했습니다.
솔루션 1: REPLICA IDENTITY FULL로 전환 이 접근 방식은 퍼블리셔가 모든 UPDATE에 대한 WAL에 전체 이전 행 이미지를 작성하도록 강제하며, TOAST 저장소에서 전체 JSONB 콘텐츠를 포함합니다. 장점에는 데이터 완전성이 보장되며 스키마 변경이 필요하지 않은 간단한 구현이 포함됩니다. 그러나 단점은 상당했습니다: 50KB 페이로드를 고려할 때 WAL 생성이 약 400% 증가하여 주요 스토리지의 공간 소진 및 창고와의 네트워크 지연 증가 위험이 있었습니다. 초당 10,000건의 업데이트를 처리하는 테이블에 대해 이는 생산 안정성을 위해 너무 위험한 것으로 간주되었습니다.
솔루션 2: 별도의 이력 테이블로 응용 프로그램 수준 저널링
팀은 업데이트 전에 오래된 JSONB 값을 별도의 market_data_history 테이블로 복사하는 트리거를 만드는 것을 고려했습니다. 장점은 논리적 복제가 이력 테이블을 별도로 복제할 수 있어 기본 테이블의 TOAST 생략 문제를 피하면서 기본 테이블의 WAL 작은 발자국을 유지할 수 있다는 점이었습니다. 하지만 단점은 주요 스토리지에서 이중 쓰기 오버헤드(거래 대기 시간이 증가)를 발생시키고, 추가 스토리지 요구 사항이 2배로 증가하며, 거래 ID 및 타임스탬프를 사용하여 이력 레코드와 주요 테이블 변경을 연관짓기 위한 ETL 논리의 복잡성이 포함되었습니다.
솔루션 3: JSONB의 해시를 포함하는 커버링 인덱스를 사용한 REPLICA IDENTITY 사용
이 전략은 md5(jsonb_column::text)에 대한 함수형 인덱스를 생성하고 해당 해시를 복합 REPLICA IDENTITY 인덱스에 포함하는 것이었습니다. 장점은 WAL 내 해시 변경을 통해 JSONB 콘텐츠의 변경 사항이 감지될 수 있다는 점입니다. 단점은 실제 이전 값을 검색할 수 없게 되어 규제 요구 사항으로 세부적인 사전 업데이트 상태를 보여주기에는 불충분하며, 높은 변동 테이블에 대한 인덱스 유지 관리 오버헤드를 발생시킵니다.
팀은 **솔루션 2(응용 프로그램 수준 저널링)**를 수정하여 선택했습니다. 그들은 PostgreSQL의 14+ 버전에서 사용할 수 있는 JSONB 부분 업데이트 최적화를 활용하고, 전체 이전 행 대신 변경된 경로(차이)를 저장하는 BEFORE UPDATE 트리거를 구현했습니다. 이로 인해 이력 테이블의 성장률이 줄어들면서 필요한 모든 이전 이미지를 확보할 수 있었습니다. 그들은 WAL 부풀림을 방지하기 위해 주요 테이블에 대한 REPLICA IDENTITY DEFAULT를 유지하면서 감사 재구성을 위해 이력 테이블을 조인하도록 ETL을 지시했습니다.
복제 스트림 크기는 안정적으로 유지되어 주요 스토리지 압력이 방지되었습니다. ETL 프로세스는 현재 행 상태를 이력 테이블에서 저장된 차이와 병합하여 완전한 감사 추적을 성공적으로 복원했습니다. 규제 준수는 400%가 아닌 주요 저장소에서 단 15%의 증가로 달성되었으며, 트랜잭션 처리량에 미치는 영향이 최소화되었습니다.
왜 PostgreSQL의 논리적 디코딩이 열이 수정되더라도 TOAST 값을 생략합니까?
많은 후보자는 모든 UPDATE가 자동으로 모든 TOAST된 값을 WAL에 가져온다고 가정합니다. 그러나 PostgreSQL은 실제로 수정하기 위해 데이터 항목을 읽을 때만 "튜플 unTOASTing"을 수행합니다. UPDATE가 다른 열을 수정하면(예: SET updated_at = NOW()) JSONB 열을 대상 목록이나 WHERE 절에 참조하지 않으면 TOAST 포인터는 변경되지 않으며 외부 저장소에 접근하지 않습니다. 따라서 WAL 레코드는 포인터와 함께 디스크에 있는 튜플만 포함됩니다. 논리적 디코딩은 이전 버전의 WAL에서 튜플을 복원하기 때문에 TOAST 테이블에 접근하지 않으며, 누락된 값은 변경 스트림에서 NULL로 나타납니다.
REPLICA IDENTITY FULL이 HOT(히프 전용 튜플) 업데이트와 어떻게 상호 작용합니까?
후보자들은 종종 REPLICA IDENTITY FULL이 테이블에 대한 HOT 업데이트를 비활성화한다는 점을 놓칩니다. HOT 업데이트는 PostgreSQL이 동일한 데이터 페이지 내에서 행 버전을 연결할 수 있게 해주며, 인덱싱된 열이 변경되지 않는 한 모든 인덱스 항목을 업데이트 하지 않고도 그렇게 가능합니다. REPLICA IDENTITY FULL이 활성화되면, 모든 UPDATE는 복제를 위해 전체 이전 행 이미지를 기록해야 하므로, 시스템은 전체 콘텐츠로 행을 고유하게 식별해야 합니다. 이는 논리적 복제가 완전한 튜플 비교 데이터를 요구하므로 HOT 최적화를 무효화합니다. 결과적으로 이 설정이 있는 테이블은 더 높은 인덱스 부풀림 및 증가한 I/O를 경험하며, 이는 높은 변동 테이블에 대한 중요한 거래입니다.
TOAST 압축과 PostgreSQL의 WAL 압축은 무엇이며, 논리적 복제 중에 어떻게 상호 작용합니까?
이 질문은 깊은 시스템 지식과 표면 수준의 이해를 구분합니다. TOAST 압축은 큰 열을 외부 테이블에 저장하기 전에 LZ4 또는 PGLZ를 사용하여 행 크기를 줄입니다. WAL 압축(wal_compression=lz4로 활성화됨)은 충돌 복구 효율성을 위해 WAL에 기록된 전체 페이지 이미지를 압축합니다. 그러나 REPLICA IDENTITY FULL이 사용되면, 논리적 디코딩으로 전송된 이전 튜플 데이터는 저장을 위해 WAL 레코드가 압축되기 전에 추출됩니다. 따라서 논리적 디코더는 TOAST 데이터를 가져올 경우 압축되지 않은 상태로 수신하고, 물리적 WAL 파일은 전체 페이지 이미지의 일부인 경우 압축 상태로 저장될 수 있어 네트워크 대역폭과 디스크 I/O에 서로 다른 영향을 미칩니다.