7월 6, 2020
Articles
댓글이 없습니다

큰 테이블에 새 열을 채우는 가장 좋은 방법은 무엇입니까?

Postgres에 7,801,611 개의 행이있는 2.2GB 테이블이 있습니다. 여기에 uuid / guid 열을 추가하고 있는데 해당 열을 채우는 가장 좋은 방법이 무엇인지 궁금합니다 (NOT NULL 제약 조건을 추가하려는 경우).

Postgres를 올바르게 이해하면 업데이트는 기술적으로 삭제 및 삽입이므로 기본적으로 전체 2.2GB 테이블을 다시 빌드합니다. 또한 슬레이브가 실행 중이므로 “늦어지지 않도록합니다.

시간이 지남에 따라 천천히 채우는 스크립트를 작성하는 것보다 더 좋은 방법이 있습니까?

댓글 h3>

  • 이미 ALTER TABLE .. ADD COLUMN ...를 실행 했습니까? 아니면 그 부분도 응답해야합니까?
  • 실행하지 않았습니다. 테이블 수정은 아직 계획 단계입니다. 이전에 열을 추가하고 채운 다음 제약 조건이나 인덱스를 추가하여이 작업을 수행했습니다. 그러나이 테이블은 상당히 커져서로드, 잠금, 복제 등이 걱정됩니다. …

답변

설정 및 요구 사항의 세부 사항에 따라 다릅니다.

Postgres 11부터는 휘발성 DEFAULT가있는 열만 추가합니다. div> 는 여전히 테이블 다시 쓰기를 트리거합니다 . 안타깝게도이 경우가 있습니다.

If 디스크에 충분한 여유 공간 이 있습니다- pg_size_pretty((pg_total_relation_size(tbl)) – 공유 잠금 일정 시간 동안 독점 잠금 을 매우 짧은 시간 동안 만든 다음 새 테이블 ( uuid 열 포함) CREATE TABLE AS 를 사용합니다. 그 이유는 무엇입니까?

아래 코드는 추가 uuid-oss 모듈 의 함수를 사용합니다.

  • SHARE 모드 에서 동시 변경에 대해 테이블을 잠급니다 (여전히 동시 읽기 허용). 테이블에 쓰려는 시도는 대기하고 결국 실패합니다. 아래를 참조하세요.

  • 즉석에서 새 열을 채우면서 전체 테이블을 복사합니다. 행을 순서대로 정렬 할 수 있습니다.
    If 행을 재정렬하려면 work_mem 를 RAM에서 정렬을 수행 할 수있을만큼 높게 설정해야합니다. 당신이 감당할 수있는 한 (전역 적으로가 아니라 당신의 세션을 위해서만).

  • 그런 다음 제약, 외래 키, 인덱스, 트리거 등을 새로운 표. 테이블의 많은 부분을 업데이트 할 때 행을 반복적으로 추가하는 것보다 처음부터 인덱스를 만드는 것이 훨씬 더 빠릅니다. 매뉴얼의 관련 조언

  • 새 테이블이 준비되면 이전 테이블을 삭제하고 새 테이블의 이름을 드롭 인 교체로 만들 수 있습니다. 이 마지막 단계에서만 나머지 트랜잭션에 대해 이전 테이블에 대한 배타적 잠금을 획득합니다. 현재 매우 짧아야합니다.
    또한 테이블 유형 (테이블 유형을 사용하는 뷰, 함수)에 따라 모든 객체를 삭제해야합니다. 서명에 …)하고 나중에 다시 만듭니다.

  • 불완전한 상태를 피하기 위해 한 번의 트랜잭션으로 모두 수행합니다. 

BEGIN; LOCK TABLE tbl IN SHARE MODE; SET LOCAL work_mem = "???? MB"; -- just for this transaction CREATE TABLE tbl_new AS SELECT uuid_generate_v1() AS tbl_uuid, <list of all columns in order> FROM tbl ORDER BY ??; -- optionally order rows favorably while being at it. ALTER TABLE tbl_new ALTER COLUMN tbl_uuid SET NOT NULL , ALTER COLUMN tbl_uuid SET DEFAULT uuid_generate_v1() , ADD CONSTRAINT tbl_uuid_uni UNIQUE(tbl_uuid); -- more constraints, indices, triggers? DROP TABLE tbl; ALTER TABLE tbl_new RENAME tbl; -- recreate views etc. if any COMMIT;

가장 빠릅니다. 제자리에서 업데이트하는 다른 방법은 더 비싼 방식으로 전체 테이블을 다시 작성해야합니다. 디스크에 충분한 여유 공간이 없거나 전체 테이블을 잠 그거나 동시 쓰기 시도에 대한 오류를 생성 할 여유가없는 경우에만 해당 경로를 사용합니다.

동시 쓰기는 어떻게됩니까?

INSERT / UPDATE / DELETE를 시도하는 다른 트랜잭션 (다른 세션에서) 트랜잭션이 SHARE 잠금을 수행 한 후 동일한 테이블에서 잠금이 해제되거나 시간 초과가 시작될 때까지 기다립니다. >

실패 어느 쪽이든 쓰려고했던 테이블이 그 아래에서 삭제 되었기 때문입니다.

새 테이블에는 새 테이블이 있습니다. 테이블 OID이지만 동시 트랜잭션은 이미 테이블 이름을 이전 테이블 의 OID로 확인했습니다. 잠금이 마침내 해제되면 테이블에 쓰기 전에 테이블 자체를 잠그려고 시도하고이를 발견합니다. ” 사라졌다.Postgres는 다음과 같이 답변합니다.

ERROR: could not open relation with OID 123456

여기서 123456는 이전 테이블의 OID입니다. 예외를 포착하고이를 방지하기 위해 앱 코드에서 쿼리를 재 시도해야합니다.

발생할 여유가 없다면 원래 테이블을 유지 해야합니다.

기존 테이블 유지, 대안 1

NOT NULL 제약 조건을 추가하기 전에 적절한 위치에 업데이트 (한 번에 작은 세그먼트에서 업데이트 실행 가능) . NULL 값이 있고 NOT NULL 제약 조건없이 새 열을 추가하는 것은 저렴합니다.
Postgres 이후 9.2 iv를 사용하여 CHECK 제약 조건을 만들 수도 있습니다. id = “8ebe9fb9ce”> :

제약 조건은 계속 유지됩니다. 후속 삽입 또는 업데이트에 대해 시행

행을 업데이트 할 수 있습니다. peu à peu – 여러 개의 개별 트랜잭션 . 이렇게하면 행 잠금이 너무 오래 유지되는 것을 방지 할 수 있으며 사용 불능 행을 재사용 할 수도 있습니다. (autovacuum이 시작될 시간이 충분하지 않으면 수동으로 VACUUM를 실행해야합니다.) 마지막으로 NOT NULL 제약 조건 및 NOT VALID CHECK 제약 조건 제거 : 

ALTER TABLE tbl ADD CONSTRAINT tbl_no_null CHECK (tbl_uuid IS NOT NULL) NOT VALID; -- update rows in multiple batches in separate transactions -- possibly run VACUUM between transactions ALTER TABLE tbl ALTER COLUMN tbl_uuid SET NOT NULL; ALTER TABLE tbl ALTER DROP CONSTRAINT tbl_no_null;

NOT VALID 자세히 :

기존 테이블 유지, 대안 2

임시 테이블 에서 새 상태 준비 , TRUNCATE 원본 및 refill . 모두 하나의 트랜잭션 에 있습니다. SHARE 잠금 전에 수행해야합니다. 홍보 동시 쓰기 손실을 방지하기 위해 새 테이블을 준비합니다.

SO에 대한 관련 답변의 세부 정보 :

댓글

  • 환상적인 답변입니다! 내가 찾던 바로 그 정보. 두 가지 질문 1. 이와 같은 작업이 얼마나 오래 걸리는지 쉽게 테스트 할 수있는 방법에 대해 알고 있습니까? 2. 5 분이 걸리는 경우 5 분 동안 해당 테이블의 행을 업데이트하려는 작업은 어떻게됩니까?
  • @CollinPeters : 1. 사자 ‘의 시간 점유율은 큰 테이블을 복사하고 인덱스와 제약 조건을 다시 만드는 데 사용됩니다 (따라 달라짐). 삭제 및 이름 변경은 저렴합니다. 테스트하려면 LOCK없이 DROP를 제외하고 준비된 SQL 스크립트를 실행할 수 있습니다. 나는 거칠고 쓸모없는 추측 만 할 수 있었다. 2.에 관해서는 내 대답에 대한 부록을 고려하십시오.
  • @ErwinBrandstetter 뷰를 다시 만들려면 계속하십시오. 따라서 테이블 이름을 변경 한 후에도 여전히 이전 테이블 (oid)을 사용하는 뷰가 수십 개 있으면됩니다. 전체 뷰 새로 고침 / 생성을 다시 실행하는 대신 전체 교체를 수행하는 방법이 있습니까?
  • @CodeFarmer : 테이블 이름 만 변경하면 뷰는 이름이 변경된 테이블로 계속 작동합니다. 대신 새 테이블을 사용하도록 뷰를 만들려면 새 테이블을 기반으로 뷰를 다시 만들어야합니다. (또한 이전 테이블을 삭제하도록 허용합니다.) 주위에 (실용적인) 방법이 없습니다.
  • 9.2 postgres는 ‘

답변

최상의답변은 없지만 작업을 상당히 빠르게 완료 할 수있는 “가장 나쁜”답변이 있습니다.

내 테이블에 2MM 행이 있고 첫 번째로 기본 설정된 보조 타임 스탬프 열을 추가하려고 할 때 업데이트 성능이 급격히 떨어졌습니다. . 

ALTER TABLE mytable ADD new_timestamp TIMESTAMP ; UPDATE mytable SET new_timestamp = old_timestamp ; ALTER TABLE mytable ALTER new_timestamp SET NOT NULL ;

40 분 동안 멈춘 후,이 작업이 얼마나 걸릴 수 있는지 알아보기 위해 작은 배치로 시도했습니다. 8 시간.

허용되는 대답은 확실히 더 낫습니다.하지만이 테이블은 제 데이터베이스에서 많이 사용됩니다. FKEY를 사용하는 테이블이 수십 개 있습니다. 너무 많은 테이블에서 FOREIGN KEYS를 전환하는 것을 피하고 싶었습니다. . 그리고 뷰가 있습니다.

문서, 사례 연구 및 StackOverflow를 약간 검색하고 “A-Ha!”순간을 보냈습니다. 핵심 UPDATE가 아니라 모든 INDEX 작업에 있습니다. 내 테이블에는 고유 한 제약 조건을위한 인덱스, 쿼리 플래너 속도 향상을위한 인덱스, 전체 텍스트 검색을위한 인덱스 등 12 개의 인덱스가 있습니다.

업데이트 된 모든 행은 DELETE / INSERT 작업뿐만 아니라 각 인덱스를 변경하고 제약 조건을 확인하는 오버 헤드이기도했습니다.

내 해결책은 모든 인덱스를 삭제하고 제약 조건, 테이블을 업데이트 한 다음 모든 인덱스 / 제약 조건을 다시 추가하십시오.

다음을 수행하는 SQL 트랜잭션을 작성하는 데 약 3 분이 소요되었습니다.

  • BEGIN;
  • 삭제 된 색인 / 제약 조건
  • 테이블 업데이트
  • 색인 / 제약 조건 다시 추가
  • COMMIT;

스크립트를 실행하는 데 7 분이 걸렸습니다.

수용된 답변은 확실히 더 좋고 더 적절하며 실제로 다운 타임이 필요하지 않습니다. 제 경우에는 훨씬 더 많은 시간이 소요되었을 것입니다. ” Developer “는 해당 솔루션을 사용하기 위해 작업했으며이를 달성 할 수있는 30 분의 예정된 다운 타임이있었습니다. 우리 솔루션은 10 분 만에 해결했습니다.

댓글

  • 동시 쓰기를 허용하는 것은 말할 것도 없습니다. 동시 읽기는 인덱스가 없으면 느려질 수 있습니다. s 그래도 🙂 다른 옵션과 비교하여 벤치마킹하는 것이 흥미로울 것입니다 …

답글 남기기

이메일 주소를 발행하지 않을 것입니다. 필수 항목은 *(으)로 표시합니다