Postgres: 팽창, pg_repack 및 지연 제약 조건

테이블과 인덱스에 대한 팽창의 영향은 널리 알려져 있으며 Postgres에만 존재하는 것이 아닙니다. VACUUM FULL이나 CLUSTER와 같이 즉시 처리할 수 있는 방법이 있지만 작동 중에 테이블을 잠그기 때문에 항상 사용할 수는 없습니다.

이 기사에는 팽창이 어떻게 발생하는지, 어떻게 대처할 수 있는지, 지연된 제약 조건 및 pg_repack 확장 사용 시 발생하는 문제에 대한 간단한 이론이 포함되어 있습니다.

이 글은 다음을 바탕으로 작성되었습니다. 나의 연설 PgConf.Russia 2020에서.

팽만감은 왜 발생하나요?

Postgres는 다중 버전 모델(MVCC). 그 본질은 테이블의 각 행이 여러 버전을 가질 수 있는 반면 트랜잭션은 이러한 버전 중 하나만 볼 수 있지만 반드시 동일한 버전은 아니라는 것입니다. 이를 통해 여러 트랜잭션이 동시에 작동하고 사실상 서로 영향을 미치지 않습니다.

분명히 이러한 버전은 모두 저장되어야 합니다. Postgres는 페이지 단위로 메모리를 사용하며, 페이지는 디스크에서 읽거나 쓸 수 있는 최소 데이터 양입니다. 이것이 어떻게 일어나는지 이해하기 위해 작은 예를 살펴보겠습니다.

여러 레코드를 추가한 테이블이 있다고 가정해 보겠습니다. 테이블이 저장된 파일의 첫 번째 페이지에 새로운 데이터가 나타났습니다. 이는 커밋 후 다른 트랜잭션에서 사용할 수 있는 라이브 버전의 행입니다(단순화를 위해 격리 수준이 커밋된 읽기라고 가정합니다).

그런 다음 항목 중 하나를 업데이트하여 이전 버전을 더 이상 관련이 없는 것으로 표시했습니다.

단계별로 행 버전을 업데이트하고 삭제하면서 데이터의 약 절반이 "쓰레기"인 페이지가 탄생했습니다. 이 데이터는 어떤 거래에도 표시되지 않습니다.

Postgres에는 메커니즘이 있습니다 진공, 더 이상 사용되지 않는 버전을 정리하고 새 데이터를 위한 공간을 만듭니다. 그러나 충분히 적극적으로 구성되지 않았거나 다른 테이블에서 작업 중이면 "가비지 데이터"가 남게 되며 새 데이터를 위해 추가 페이지를 사용해야 합니다.

따라서 이 예에서는 특정 시점에 테이블이 XNUMX페이지로 구성되지만 그 중 절반에만 라이브 데이터가 포함됩니다. 결과적으로 테이블에 액세스할 때 필요한 것보다 훨씬 많은 데이터를 읽게 됩니다.

이제 VACUUM이 관련 없는 행 버전을 모두 삭제하더라도 상황은 크게 개선되지 않습니다. 새 행을 위한 페이지 또는 전체 페이지에 여유 공간이 있지만 여전히 필요한 것보다 더 많은 데이터를 읽습니다.
그런데 완전히 빈 페이지(이 예에서는 두 번째 페이지)가 파일 끝에 있으면 VACUUM이 해당 페이지를 잘라낼 수 있습니다. 하지만 이제 그녀는 중간에 있으므로 그녀와는 아무것도 할 수 없습니다.

이렇게 비어 있거나 매우 희박한 페이지의 수가 많아지면 이를 팽창이라고 하며 성능에 영향을 미치기 시작합니다.

위에서 설명한 모든 것은 테이블에서 부풀어오르는 현상의 메커니즘입니다. 인덱스에서도 이는 거의 동일한 방식으로 발생합니다.

부풀어 오르나요?

팽만감이 있는지 확인하는 방법에는 여러 가지가 있습니다. 첫 번째 아이디어는 테이블의 행 수, "라이브" 행 수 등에 대한 대략적인 정보가 포함된 내부 Postgres 통계를 사용하는 것입니다. 인터넷에서 기성 스크립트의 다양한 변형을 찾을 수 있습니다. 우리는 기초로 삼았습니다 스크립트 토스트 및 팽창 btree 인덱스와 함께 팽창 테이블을 평가할 수 있는 PostgreSQL 전문가의 글입니다. 경험상 오류는 10~20%입니다.

또 다른 방법은 확장 프로그램을 사용하는 것입니다. pgstattuple, 페이지 내부를 살펴보고 예상 및 정확한 팽창 값을 모두 얻을 수 있습니다. 그러나 두 번째 경우에는 전체 테이블을 스캔해야 합니다.

우리는 최대 20%의 작은 팽창 값을 허용 가능한 것으로 간주합니다. fillfactor와 유사하다고 볼 수 있습니다. 테이블 и 인덱스. 50% 이상에서는 성능 문제가 시작될 수 있습니다.

팽만감을 퇴치하는 방법

Postgres에는 기본적으로 팽창을 처리하는 여러 가지 방법이 있지만 항상 모든 사람에게 적합한 것은 아닙니다.

블로트가 발생하지 않도록 AUTOVACUUM 구성. 또는 더 정확하게는 귀하가 수용할 수 있는 수준으로 유지하는 것입니다. 이는 '선장의' 조언처럼 보이지만 실제로는 항상 달성하기 쉬운 것은 아닙니다. 예를 들어, 데이터 스키마를 정기적으로 변경하여 개발이 활발히 이루어지고 있거나 일종의 데이터 마이그레이션이 진행되고 있습니다. 결과적으로 로드 프로필은 자주 변경될 수 있으며 일반적으로 테이블마다 다릅니다. 이는 지속적으로 미리 작업하고 각 테이블의 변화하는 프로필에 맞게 AUTOVACUUM을 조정해야 함을 의미합니다. 그러나 분명히 이것은 쉽지 않습니다.

AUTOVACUUM이 테이블을 따라갈 수 없는 또 다른 일반적인 이유는 해당 트랜잭션에 사용 가능한 데이터를 정리하지 못하게 하는 장기 실행 트랜잭션이 있기 때문입니다. 여기서 권장 사항도 분명합니다. "매달려 있는" 트랜잭션을 제거하고 활성 트랜잭션 시간을 최소화하는 것입니다. 그러나 애플리케이션의 로드가 OLAP와 OLTP의 혼합인 경우 빈번한 업데이트와 짧은 쿼리는 물론 보고서 작성과 같은 장기 작업도 동시에 수행할 수 있습니다. 이러한 상황에서는 서로 다른 기반에 부하를 분산시켜 각 기반을 더 세부적으로 조정할 수 있다는 점을 고려해 볼 가치가 있습니다.

또 다른 예 - 프로필이 동종이지만 데이터베이스의 부하가 매우 높은 경우에도 가장 공격적인 AUTOVACUUM도 대처할 수 없으며 팽창이 발생할 수 있습니다. 크기 조정(수직 또는 수평)이 유일한 솔루션입니다.

AUTOVACUUM을 설정했지만 부풀림이 계속 커지는 상황에서 해야 할 일.

팀 진공이 가득 참 테이블과 인덱스의 내용을 다시 작성하고 관련 데이터만 남겨둡니다. 부풀림을 제거하기 위해 완벽하게 작동하지만 실행 중에 테이블에 대한 배타적 잠금이 캡처되어(AccessExclusiveLock) 이 테이블에 대한 쿼리 실행을 허용하지 않으며 심지어 선택도 허용하지 않습니다. 서비스 또는 그 일부를 일정 시간 동안(데이터베이스 및 하드웨어의 크기에 따라 수십 분에서 몇 시간까지) 중지할 수 있는 경우 이 옵션이 가장 좋습니다. 아쉽게도 예정된 유지 관리 기간 동안 VACUUM FULL을 실행할 시간이 없으므로 이 방법은 우리에게 적합하지 않습니다.

팀 CLUSTER VACUUM FULL과 동일한 방식으로 테이블의 내용을 다시 작성하지만 디스크에서 데이터가 물리적으로 정렬되는 인덱스를 지정할 수 있습니다(그러나 미래에는 새 행에 대해 순서가 보장되지 않습니다). 특정 상황에서는 인덱스별로 여러 레코드를 읽는 여러 쿼리에 대한 좋은 최적화입니다. 이 명령의 단점은 VACUUM FULL과 동일합니다. 즉, 작동 중에 테이블을 잠급니다.

팀 재색인 이전 두 가지와 유사하지만 특정 인덱스 또는 테이블의 모든 인덱스를 다시 작성합니다. 잠금은 약간 약합니다. 테이블의 ShareLock(수정을 방지하지만 선택은 허용) 및 다시 작성되는 인덱스의 AccessExclusiveLock(이 인덱스를 사용하는 쿼리 차단)입니다. 하지만 Postgres 12 버전에서는 매개변수가 등장했습니다. 동시에, 레코드의 동시 추가, 수정 또는 삭제를 차단하지 않고 인덱스를 다시 작성할 수 있습니다.

이전 버전의 Postgres에서는 다음을 사용하여 REINDEX CONCURRENTLY와 유사한 결과를 얻을 수 있습니다. 동시에 인덱스 생성. 이를 통해 엄격한 잠금(병렬 쿼리를 방해하지 않는 ShareUpdateExclusiveLock) 없이 인덱스를 생성한 다음 이전 인덱스를 새 인덱스로 교체하고 이전 인덱스를 삭제할 수 있습니다. 이를 통해 애플리케이션을 방해하지 않고 인덱스 팽창을 제거할 수 있습니다. 인덱스를 재구축할 때 디스크 하위 시스템에 추가 로드가 발생한다는 점을 고려하는 것이 중요합니다.

따라서 인덱스의 경우 "즉시" 팽창을 제거할 수 있는 방법이 있다면 테이블에는 방법이 없습니다. 다양한 외부 확장이 작동하는 곳입니다. pg_repack (이전의 pg_reorg), pgcompact, pgcompactable 다른 사람. 이 기사에서는 그것들을 비교하지 않고 pg_repack에 대해서만 설명할 것입니다. pg_repack은 약간의 수정 후에 우리가 직접 사용합니다.

pg_repack 작동 방식

인덱스, 제한 사항, 그리고 불행하게도 팽창이 포함된 완전히 일반적인 테이블이 있다고 가정해 보겠습니다. pg_repack의 첫 번째 단계는 실행 중 모든 변경 사항에 대한 데이터를 저장하는 로그 테이블을 만드는 것입니다. 트리거는 모든 삽입, 업데이트 및 삭제에 대해 이러한 변경 사항을 복제합니다. 그런 다음 구조상 원본 테이블과 유사하지만 데이터 삽입 프로세스가 느려지지 않도록 인덱스와 제한 사항이 없는 테이블이 생성됩니다.

다음으로, pg_repack은 이전 테이블의 데이터를 새 테이블로 전송하고 관련 없는 모든 행을 자동으로 필터링한 다음 새 테이블에 대한 인덱스를 생성합니다. 이러한 모든 작업을 실행하는 동안 변경 사항이 로그 테이블에 누적됩니다.

다음 단계는 변경 사항을 새 테이블로 전송하는 것입니다. 마이그레이션은 여러 번의 반복을 통해 수행되며 로그 테이블에 20개 미만의 항목이 남아 있는 경우 pg_repack은 강력한 잠금을 획득하고 최신 데이터를 마이그레이션하며 Postgres 시스템 테이블의 이전 테이블을 새 테이블로 바꿉니다. 이것은 테이블 작업을 할 수 없는 유일하고 매우 짧은 시간입니다. 이후 기존 테이블과 로그가 있는 테이블이 삭제되고 파일 시스템에 여유 공간이 확보됩니다. 프로세스가 완료되었습니다.

이론적으로는 모든 것이 훌륭해 보이지만 실제로는 어떻게 될까요? 로드가 없는 상태와 로드가 있는 상태에서 pg_repack을 테스트하고 조기 중지(즉, Ctrl+C 사용) 시 작동을 확인했습니다. 모든 테스트는 긍정적이었습니다.

우리는 식품점에 갔는데 모든 것이 예상대로 진행되지 않았습니다.

첫 번째 팬케이크 판매

첫 번째 클러스터에서는 고유 제약 조건 위반에 대한 오류를 받았습니다.

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

이 제한에는 자동 생성된 이름 index_16508이 있으며 이는 pg_repack에 의해 생성되었습니다. 구성에 포함된 속성을 기반으로 이에 해당하는 "우리의" 제약 조건을 결정했습니다. 문제는 이것이 완전히 일반적인 제한이 아니라 지연된 제한이라는 것으로 밝혀졌습니다(지연된 제약), 즉. 해당 확인은 sql 명령보다 나중에 수행되므로 예기치 않은 결과가 발생합니다.

지연된 제약조건: 이것이 필요한 이유와 작동 방식

지연된 제한에 대한 작은 이론.
간단한 예를 생각해 보겠습니다. 디렉터리에 있는 자동차의 이름과 순서라는 두 가지 속성을 가진 자동차에 대한 테이블 참조 책이 있습니다.

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique
);

첫 번째 차량과 두 번째 차량을 교체해야 한다고 가정해 보겠습니다. 간단한 해결책은 첫 번째 값을 두 번째 값으로 업데이트하고 두 번째 값을 첫 번째 값으로 업데이트하는 것입니다.

begin;
  update cars set ord = 2 where name = 'audi';
  update cars set ord = 1 where name = 'bmw';
commit;

하지만 이 코드를 실행하면 테이블의 값 순서가 고유하기 때문에 제약 조건 위반이 예상됩니다.

[23305] ERROR: duplicate key value violates unique constraint “uk_cars”
Detail: Key (ord)=(2) already exists.

어떻게 다르게 할 수 있나요? 옵션 1: 테이블에 존재하지 않는 것이 보장되는 주문에 대체 값을 추가합니다(예: "-XNUMX"). 프로그래밍에서는 이를 "두 변수의 값을 세 번째 변수로 교환"이라고 합니다. 이 방법의 유일한 단점은 추가 업데이트입니다.

옵션 1: 순서 값에 정수 대신 부동 소수점 데이터 유형을 사용하도록 테이블을 다시 디자인합니다. 그런 다음 값을 2.5에서 XNUMX로 업데이트하면 첫 번째 항목이 두 번째 항목과 세 번째 항목 사이에 자동으로 "대기"됩니다. 이 솔루션은 작동하지만 두 가지 제한 사항이 있습니다. 첫째, 값이 인터페이스 어딘가에서 사용되면 작동하지 않습니다. 둘째, 데이터 유형의 정밀도에 따라 모든 레코드의 값을 다시 계산하기 전에 가능한 삽입 수가 제한됩니다.

옵션 XNUMX: 커밋 시에만 확인되도록 제약 조건을 연기합니다.

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique deferrable initially deferred
);

초기 요청의 논리는 커밋 시 모든 값이 고유함을 보장하므로 성공할 것입니다.

위에서 논의한 예는 물론 매우 합성적이지만 아이디어를 드러냅니다. 우리 애플리케이션에서는 지연된 제약 조건을 사용하여 사용자가 보드의 공유 위젯 개체와 동시에 작업할 때 충돌을 해결하는 논리를 구현합니다. 이러한 제한을 사용하면 애플리케이션 코드를 좀 더 간단하게 만들 수 있습니다.

일반적으로 제약 조건 유형에 따라 Postgres에는 행, 트랜잭션 및 표현식 수준이라는 세 가지 수준의 세분성 검사가 있습니다.

출처 : 베그리프

CHECK 및 NOT NULL은 항상 행 수준에서 확인되며, 표에서 볼 수 있듯이 다른 제한 사항에 대해서는 다른 옵션이 있습니다. 더 읽어보실 수 있습니다 여기에.

간단히 요약하자면, 다양한 상황에서 지연된 제약 조건은 더 읽기 쉬운 코드와 더 적은 명령을 제공합니다. 그러나 오류가 발생하는 순간과 오류를 발견하는 순간은 시간상 분리되어 있기 때문에 디버깅 과정을 복잡하게 만들어 이에 대한 대가를 치러야 합니다. 또 다른 가능한 문제는 요청에 지연된 제약 조건이 포함된 경우 스케줄러가 항상 최적의 계획을 구성할 수 없다는 것입니다.

pg_repack 개선

우리는 지연된 제약조건이 무엇인지 다루었습니다. 그러나 그것이 우리의 문제와 어떤 관련이 있습니까? 이전에 받은 오류를 기억해 보겠습니다.

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

로그 테이블에서 새 테이블로 데이터가 복사될 때 발생합니다. 이상해 보이는데 왜냐면... 로그 테이블의 데이터는 소스 테이블의 데이터와 함께 커밋됩니다. 원래 테이블의 제약 조건을 충족한다면 어떻게 새 테이블의 동일한 제약 조건을 위반할 수 있습니까?

알고 보니 문제의 근본 원인은 제약 조건이 아닌 인덱스만 생성하는 pg_repack의 이전 단계에 있습니다. 이전 테이블에는 고유 제약 조건이 있었고 새 테이블에는 대신 고유 인덱스가 생성되었습니다.

여기서 중요한 점은 제약 조건이 정상이고 지연되지 않은 경우 대신 생성된 고유 인덱스가 이 제약 조건과 동일하다는 점입니다. Postgres의 고유 제약 조건은 고유 인덱스를 생성하여 구현됩니다. 그러나 지연 제약 조건의 경우 인덱스를 지연할 수 없고 sql 명령이 실행될 때 항상 확인되므로 동작이 동일하지 않습니다.

따라서 문제의 본질은 검사의 "지연"에 있습니다. 원래 테이블에서는 커밋 시 발생하고 새 테이블에서는 sql 명령이 실행될 때 발생합니다. 이는 두 경우 모두에서 검사가 동일하게 수행되는지 확인해야 함을 의미합니다. 즉, 항상 지연되거나 항상 즉시 수행됩니다.

그럼 우리는 어떤 아이디어를 가지고 있었나요?

deferred와 유사한 인덱스 생성

첫 번째 아이디어는 즉시 모드에서 두 검사를 모두 수행하는 것입니다. 이로 인해 여러 가지 오탐 제한이 발생할 수 있지만 그 수가 적더라도 이러한 충돌은 정상적인 상황이기 때문에 사용자의 작업에 영향을 주어서는 안 됩니다. 예를 들어 두 명의 사용자가 동시에 동일한 위젯 편집을 시작하고 두 번째 사용자의 클라이언트가 첫 번째 사용자가 편집할 수 없도록 위젯이 이미 차단되었다는 정보를 받을 시간이 없는 경우에 발생합니다. 이러한 상황에서 서버는 두 번째 사용자를 거부하고 해당 클라이언트는 변경 사항을 롤백하고 위젯을 차단합니다. 잠시 후 첫 번째 사용자가 편집을 완료하면 두 번째 사용자는 위젯이 더 이상 차단되지 않고 해당 작업을 반복할 수 있다는 정보를 받게 됩니다.

검사가 항상 지연되지 않는 모드인지 확인하기 위해 원래 지연 제약 조건과 유사한 새 인덱스를 만들었습니다.

CREATE UNIQUE INDEX CONCURRENTLY uk_tablename__immediate ON tablename (id, index);
-- run pg_repack
DROP INDEX CONCURRENTLY uk_tablename__immediate;

테스트 환경에서는 예상되는 오류가 몇 개만 수신되었습니다. 성공! 프로덕션 환경에서 pg_repack을 다시 실행했는데 한 시간 동안 첫 번째 클러스터에서 5개의 오류가 발생했습니다. 이는 수용 가능한 결과입니다. 그러나 이미 두 번째 클러스터에서는 오류 수가 크게 증가하여 pg_repack을 중지해야 했습니다.

왜 그런 일이 일어났나요? 오류가 발생할 가능성은 동시에 동일한 위젯을 사용하여 작업하는 사용자 수에 따라 달라집니다. 분명히 그 순간에는 다른 클러스터보다 첫 번째 클러스터에 저장된 데이터에 대한 경쟁적 변경 사항이 훨씬 적었습니다. 우리는 단지 "행운"이었습니다.

아이디어가 작동하지 않았습니다. 그 시점에서 우리는 두 가지 다른 솔루션을 보았습니다. 지연된 제약 조건을 없애기 위해 애플리케이션 코드를 다시 작성하거나 pg_repack에 이를 사용하도록 "가르치는" 것입니다. 우리는 두 번째를 선택했습니다.

새 테이블의 인덱스를 원래 테이블의 지연된 제약 조건으로 교체

개정의 목적은 분명했습니다. 원본 테이블에 지연된 제약 조건이 있는 경우 새 테이블에 대해서는 인덱스가 아닌 이러한 제약 조건을 생성해야 합니다.

변경 사항을 테스트하기 위해 간단한 테스트를 작성했습니다.

• 지연된 제약 조건과 하나의 레코드가 있는 테이블;
• 기존 레코드와 충돌하는 루프에 데이터를 삽입합니다.
• 업데이트를 수행하세요. 데이터가 더 이상 충돌하지 않습니다.
• 변경 사항을 커밋합니다.

create table test_table
(
  id serial,
  val int,
  constraint uk_test_table__val unique (val) deferrable initially deferred 
);

INSERT INTO test_table (val) VALUES (0);
FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (0) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    COMMIT;
  END;
END LOOP;

pg_repack의 원본 버전은 항상 첫 번째 삽입 시 충돌이 발생했으며 수정된 버전은 오류 없이 작동했습니다. 엄청난.

프로덕션으로 이동하면 로그 테이블에서 새 테이블로 데이터를 복사하는 것과 동일한 단계에서 다시 오류가 발생합니다.

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

고전적인 상황: 테스트 환경에서는 모든 것이 작동하지만 프로덕션 환경에서는 작동하지 않습니까?!

APPLY_COUNT 및 두 배치의 교차점

우리는 코드를 문자 그대로 한 줄씩 분석하기 시작했고 중요한 점을 발견했습니다. 데이터는 로그 테이블에서 새 테이블로 배치로 전송되고 APPLY_COUNT 상수는 배치의 크기를 나타냅니다.

for (;;)
{
num = apply_log(connection, table, APPLY_COUNT);

if (num > MIN_TUPLES_BEFORE_SWITCH)
     continue;  /* there might be still some tuples, repeat. */
...
}

문제는 여러 작업이 잠재적으로 제약 조건을 위반할 수 있는 원래 트랜잭션의 데이터가 전송될 때 두 배치의 교차점에서 끝날 수 있다는 것입니다. 명령의 절반은 첫 번째 배치에서 커밋되고 나머지 절반은 커밋됩니다. 두 번째에. 그리고 여기서는 운에 따라 팀이 첫 번째 배치에서 아무 것도 위반하지 않으면 모든 것이 정상이지만 위반하면 오류가 발생합니다.

APPLY_COUNT는 1000개의 레코드와 동일합니다. 이는 테스트가 성공한 이유를 설명합니다. "배치 접합"의 경우는 다루지 않았습니다. 우리는 insert와 update라는 두 가지 명령을 사용했기 때문에 두 명령의 정확히 500개의 트랜잭션이 항상 배치로 배치되었으며 아무런 문제도 발생하지 않았습니다. 두 번째 업데이트를 추가한 후 편집 내용이 작동을 멈췄습니다.

FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (1) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    UPDATE test_table set val = i where id = v_id; -- one more update
    COMMIT;
  END;
END LOOP;

따라서 다음 작업은 한 트랜잭션에서 변경된 원본 테이블의 데이터가 역시 한 트랜잭션 내에서 새 테이블에 있는지 확인하는 것입니다.

일괄 처리 거부

그리고 우리에게는 두 가지 해결책이 있었습니다. 첫째, 배치로의 분할을 완전히 포기하고 하나의 트랜잭션으로 데이터를 전송합시다. 이 솔루션의 장점은 단순성이었습니다. 필요한 코드 변경이 최소화되었습니다(그런데 이전 버전에서는 pg_reorg가 정확히 그렇게 작동했습니다). 그러나 문제가 있습니다. 우리는 장기 거래를 생성하고 있으며 이는 이전에 말했듯이 새로운 팽창의 출현에 대한 위협입니다.

두 번째 해결 방법은 더 복잡하지만 아마도 더 정확할 것입니다. 즉, 테이블에 데이터를 추가한 트랜잭션의 식별자를 사용하여 로그 테이블에 열을 생성하는 것입니다. 그런 다음 데이터를 복사할 때 이 속성별로 그룹화하고 관련 변경 사항이 함께 전송되도록 할 수 있습니다. 배치는 여러 트랜잭션(또는 하나의 큰 트랜잭션)으로 구성되며 해당 트랜잭션에서 변경된 데이터의 양에 따라 크기가 달라집니다. 서로 다른 트랜잭션의 데이터가 무작위 순서로 로그 테이블에 입력되므로 더 이상 이전처럼 순차적으로 읽을 수 없게 된다는 점에 유의하는 것이 중요합니다. tx_id로 필터링하는 각 요청에 대한 seqscan은 비용이 너무 많이 들고 인덱스가 필요하지만 업데이트 오버헤드로 인해 방법 속도가 느려집니다. 일반적으로 항상 그렇듯이 무언가를 희생해야 합니다.

그래서 우리는 더 간단한 첫 번째 옵션부터 시작하기로 결정했습니다. 첫째, 긴 거래가 실제로 문제가 될지 이해하는 것이 필요했습니다. 이전 테이블에서 새 테이블로의 주요 데이터 전송도 하나의 긴 트랜잭션에서 발생하므로 질문은 "이 트랜잭션을 얼마나 늘릴 것인가?"로 전환됩니다. 첫 번째 트랜잭션의 기간은 주로 테이블 크기에 따라 달라집니다. 새로운 기간은 데이터 전송 중에 테이블에 누적된 변경 사항 수에 따라 달라집니다. 하중의 강도에 따라. pg_repack 실행은 서비스 로드가 최소인 시간에 발생했으며 테이블의 원래 크기에 비해 변경량이 너무 작았습니다. 우리는 새로운 거래 시간을 무시할 수 있다고 결정했습니다(비교를 위해 평균 1시간 2~3분입니다).

실험은 긍정적이었습니다. 생산도 시작하세요. 명확성을 위해 다음은 실행 후 데이터베이스 중 하나의 크기를 보여주는 그림입니다.

우리는 이 솔루션에 완전히 만족했기 때문에 두 번째 솔루션을 구현하려고 시도하지는 않았지만 확장 개발자와 논의할 가능성을 고려하고 있습니다. 불행하게도 우리의 현재 개정판은 아직 게시할 준비가 되지 않았습니다. 왜냐하면 우리는 고유한 지연된 제한 사항으로만 문제를 해결했고 완전한 패치를 위해서는 다른 유형에 대한 지원을 제공해야 하기 때문입니다. 우리는 미래에도 이것을 할 수 있기를 바랍니다.

아마도 당신은 질문이 있을 것입니다. 왜 우리는 pg_repack을 수정하여 이 이야기에 참여하고 예를 들어 그 유사체를 사용하지 않았습니까? 어느 시점에서 우리도 이것에 대해 생각했지만, 지연된 제약 조건이 없는 테이블에서 더 일찍 그것을 사용한 긍정적인 경험은 우리가 문제의 본질을 이해하고 해결하려고 노력하도록 동기를 부여했습니다. 또한 다른 솔루션을 사용하는 경우에도 테스트를 수행하는 데 시간이 필요하므로 먼저 문제를 해결하려고 노력하고 합리적인 시간 내에 이를 수행할 수 없다는 것을 깨닫게 되면 유사점을 살펴보기 시작했습니다. .

조사 결과

우리의 경험을 바탕으로 우리가 추천할 수 있는 것:

1. 당신의 부풀어 오른 부분을 모니터링하십시오. 모니터링 데이터를 통해 autovacuum이 얼마나 잘 구성되어 있는지 이해할 수 있습니다.
2. 팽창을 허용 가능한 수준으로 유지하려면 AUTOVACUUM을 조정하세요.
3. 부풀어오르는 부분이 여전히 커지고 기본 도구를 사용하여 이를 극복할 수 없는 경우 외부 확장 기능을 사용하는 것을 두려워하지 마십시오. 가장 중요한 것은 모든 것을 잘 테스트하는 것입니다.
4. 필요에 맞게 외부 솔루션을 수정하는 것을 두려워하지 마세요. 때로는 자신의 코드를 변경하는 것보다 이것이 더 효과적이고 더 쉬울 수도 있습니다.

출처 : habr.com