PostgreSQL에서 대규모 비 차단 업데이트를 수행하려면 어떻게합니까?

PostgreSQL에서 대규모 비 차단 업데이트를 수행하려면 어떻게합니까?

---

PostgreSQL의 테이블에 대한 대규모 업데이트를 수행하고 싶지만 전체 작업에서 트랜잭션 무결성을 유지할 필요가 없습니다. 변경하는 열이 동안 쓰거나 읽히지 않을 것임을 알고 있기 때문에 업데이트. psql 콘솔 에 이러한 유형의 작업을 더 빠르게 수행 할 수 있는 쉬운 방법이 있는지 알고 싶습니다 .

예를 들어 3 천 5 백만 개의 행이있는 "orders"라는 테이블이 있는데 다음과 같이하려고합니다.

UPDATE orders SET status = null;

주제를 벗어난 토론으로 전환되는 것을 방지하기 위해 3 천 5 백만 열의 모든 상태 값이 현재 동일한 (null이 아닌) 값으로 설정되어 인덱스를 쓸모 없게 만든다고 가정 해 보겠습니다.

이 명령문의 문제점은 적용되는 데 매우 오랜 시간이 걸리고 (오직 잠금 때문에) 전체 업데이트가 완료 될 때까지 변경된 모든 행이 잠긴다는 것입니다. 이 업데이트는 5 시간이 소요될 수 있지만

UPDATE orders SET status = null WHERE (order_id > 0 and order_id < 1000000);

1 분이 소요될 수 있습니다. 3 천 5 백만 개가 넘는 행을 위의 작업을 수행하고이를 35 개의 청크로 분할하면 35 분 밖에 걸리지 않고 4 시간 25 분을 절약 할 수 있습니다.

스크립트 (여기 의사 코드 사용)를 사용하여 더 자세히 분석 할 수 있습니다.

for (i = 0 to 3500) {
  db_operation ("UPDATE orders SET status = null
                 WHERE (order_id >" + (i*1000)"
             + " AND order_id <" + ((i+1)*1000) " +  ")");
}

이 작업은 35 분이 아니라 몇 분 안에 완료 될 수 있습니다.

그래서 이것이 제가 정말로 요구하는 것입니다. 이렇게 큰 일회성 업데이트를 원할 때마다 작업을 분해하는 이상한 스크립트를 작성하고 싶지 않습니다. 내가 원하는 것을 SQL 내에서 완수 할 수있는 방법이 있습니까?

---

열 / 행

... 변경하는 열이 업데이트 중에 쓰거나 읽히지 않을 것임을 알고 있기 때문에 전체 작업에서 트랜잭션 무결성을 유지할 필요가 없습니다.

어떤 UPDATE에서 의 PostgreSQL의 MVCC 모델 의 새 버전 기록 전체 행을 . 동시 트랜잭션 이 동일한 행의 열을 변경 하면 시간이 많이 걸리는 동시성 문제가 발생합니다. 설명서의 세부 사항. 같은 알면 열하는 것은 동시 트랜잭션에 감동하지 않습니다는 피할 몇 가지 가능한 합병증,하지만 다른 사람을.

인덱스

주제를 벗어난 토론으로 전환되는 것을 방지하기 위해 3 천 5 백만 열의 모든 상태 값이 현재 동일한 (null이 아닌) 값으로 설정되어 인덱스를 쓸모 없게 만든다고 가정 해 보겠습니다.

전체 테이블 (또는 그 주요 부분)을 업데이트 할 때 Postgres 는 인덱스를 사용하지 않습니다 . 순차 스캔은 모든 또는 대부분의 행을 읽어야 할 때 더 빠릅니다. 반대로, 인덱스 유지 관리는 UPDATE.

공연

예를 들어 3 천 5 백만 개의 행이있는 "orders"라는 테이블이 있는데 다음과 같이하려고합니다.

UPDATE orders SET status = null;

보다 일반적인 솔루션을 목표로하고 있음을 이해합니다 (아래 참조). 그러나 실제 질문 을 해결하기 위해 : 이것은 테이블 크기에 관계없이 몇 밀리 초 만에 처리 할 수 ​​있습니다 .

ALTER TABLE orders DROP column status
                 , ADD  column status text;

문서 별 :

열이로 추가 ADD COLUMN되면 테이블의 모든 기존 행이 열의 기본값으로 초기화됩니다 ( 절이 지정 NULL되지 않은 DEFAULT경우). DEFAULT 절이 없으면 메타 데이터 변경 일뿐입니다.

과:

이 DROP COLUMN양식은 열을 물리적으로 제거하지 않지만 단순히 SQL 작업에 보이지 않게 만듭니다. 테이블의 후속 삽입 및 업데이트 작업은 열에 대해 null 값을 저장합니다. 따라서 열을 삭제하는 것은 빠르지 만 삭제 된 열이 차지하는 공간이 재 확보되지 않기 때문에 테이블의 디스크 크기가 즉시 줄어들지는 않습니다. 기존 행이 업데이트되면 시간이 지남에 따라 공간이 재 확보됩니다. (이러한 명령문은 시스템 OID 열을 삭제할 때 적용되지 않으며 즉시 다시 작성하여 수행됩니다.)

열 (외래 키 제약 조건, 인덱스, 뷰 등)에 따라 개체가 없는지 확인하십시오. 삭제 / 재 작성해야합니다. 이를 제외하고 시스템 카탈로그 테이블에 대한 작은 작업 pg_attribute이 작업을 수행합니다. 동시로드가 많은 경우 문제가 될 수있는 테이블에 대한 독점 잠금이 필요합니다 . 몇 밀리 초 밖에 걸리지 않기 때문에 괜찮습니다.

유지하려는 열 기본값이 있는 경우 별도의 명령으로 다시 추가하십시오 . 동일한 명령으로 수행하면 모든 행에 즉시 적용되어 효과가 무효화됩니다. 그런 다음 기존 열을 일괄 적으로 업데이트 할 수 있습니다. 설명서 링크 를 따라 설명서 의 참고 사항 을 읽으십시오 .

일반 솔루션

dblink다른 답변에서 언급되었습니다. 암시 적 개별 연결에서 "원격"Postgres 데이터베이스에 액세스 할 수 있습니다. "원격"데이터베이스는 현재 데이터베이스가 될 수 있으므로 "자율 트랜잭션" 을 달성 할 수 있습니다. 함수가 "원격"db에 기록하는 내용은 커밋되고 롤백 할 수 없습니다.

이를 통해 작은 부분에서 큰 테이블을 업데이트하고 각 부분이 개별적으로 커밋되는 단일 함수를 실행할 수 있습니다. 매우 많은 수의 행에 대한 트랜잭션 오버 헤드를 방지하고 더 중요한 것은 각 부분 후에 잠금을 해제합니다. 이렇게하면 동시 작업이 많은 지연없이 진행되고 교착 상태가 발생할 가능성이 줄어 듭니다.

동시 액세스 권한이없는 ROLLBACK경우 예외 후 피하는 경우를 제외하고는 거의 유용하지 않습니다 . SAVEPOINT그 경우 도 고려하십시오 .

부인 성명

우선, 많은 소규모 거래가 실제로 더 비쌉니다. 이것은 큰 테이블에서만 의미가 있습니다 . 스위트 스팟은 여러 요인에 따라 달라집니다.

수행중인 작업이 확실하지 않은 경우 : 단일 트랜잭션이 안전한 방법 입니다. 이것이 제대로 작동하려면 테이블에 대한 동시 작업이 함께 수행되어야합니다. 예를 들어 동시 쓰기 는 이미 처리 된 것으로 추정되는 파티션으로 행을 이동할 수 있습니다. 또는 동시 읽기는 일관성없는 중간 상태를 볼 수 있습니다. 경고를 받았습니다.

단계별 지침

추가 모듈 dblink를 먼저 설치해야합니다.

• PostgreSQL에서 dblink를 사용 (설치)하는 방법은 무엇입니까?

dblink와의 연결 설정은 DB 클러스터 설정 및 적절한 보안 정책에 따라 크게 달라집니다. 까다로울 수 있습니다. dblink와 연결하는 방법에 대한 자세한 내용은 나중에 답변 합니다 .

• 함수가 중단 된 경우에도 UDF에 지속적 삽입

연결을 단순화하고 간소화하기 위해 지침에 따라 FOREIGN SERVER및 a USER MAPPING를 만듭니다 (이미 연결이없는 경우). 약간의 간격이 있거나없는 것으로
가정합니다 serial PRIMARY KEY.

CREATE OR REPLACE FUNCTION f_update_in_steps()
  RETURNS void AS
$func$
DECLARE
   _step int;   -- size of step
   _cur  int;   -- current ID (starting with minimum)
   _max  int;   -- maximum ID
BEGIN
   SELECT INTO _cur, _max  min(order_id), max(order_id) FROM orders;
                                        -- 100 slices (steps) hard coded
   _step := ((_max - _cur) / 100) + 1;  -- rounded, possibly a bit too small
                                        -- +1 to avoid endless loop for 0
   PERFORM dblink_connect('myserver');  -- your foreign server as instructed above

   FOR i IN 0..200 LOOP                 -- 200 >> 100 to make sure we exceed _max
      PERFORM dblink_exec(
       $$UPDATE public.orders
         SET    status = 'foo'
         WHERE  order_id >= $$ || _cur || $$
         AND    order_id <  $$ || _cur + _step || $$
         AND    status IS DISTINCT FROM 'foo'$$);  -- avoid empty update

      _cur := _cur + _step;

      EXIT WHEN _cur > _max;            -- stop when done (never loop till 200)
   END LOOP;

   PERFORM dblink_disconnect();
END
$func$  LANGUAGE plpgsql;

요구:

SELECT f_update_in_steps();

테이블 이름, 열 이름, 값 등 필요에 따라 모든 부분을 매개 변수화 할 수 있습니다. SQL 삽입을 방지하려면 식별자를 삭제해야합니다.

• PostgreSQL 함수 매개 변수로서의 테이블 이름

빈 UPDATE 피하기 :

• 여러 열에서 DISTINCT를 어떻게 선택합니까 (또는 수 있습니까)?

---

You should delegate this column to another table like this:

create table order_status (
  order_id int not null references orders(order_id) primary key,
  status int not null
);

Then your operation of setting status=NULL will be instant:

truncate order_status;

---

I would use CTAS:

begin;
create table T as select col1, col2, ..., <new value>, colN from orders;
drop table orders;
alter table T rename to orders;
commit;

---

First of all - are you sure that you need to update all rows?

Perhaps some of the rows already have status NULL?

If so, then:

UPDATE orders SET status = null WHERE status is not null;

As for partitioning the change - that's not possible in pure sql. All updates are in single transaction.

One possible way to do it in "pure sql" would be to install dblink, connect to the same database using dblink, and then issue a lot of updates over dblink, but it seems like overkill for such a simple task.

Usually just adding proper where solves the problem. If it doesn't - just partition it manually. Writing a script is too much - you can usually make it in a simple one-liner:

perl -e '
    for (my $i = 0; $i <= 3500000; $i += 1000) {
        printf "UPDATE orders SET status = null WHERE status is not null
                and order_id between %u and %u;\n",
        $i, $i+999
    }
'

I wrapped lines here for readability, generally it's a single line. Output of above command can be fed to psql directly:

perl -e '...' | psql -U ... -d ...

Or first to file and then to psql (in case you'd need the file later on):

perl -e '...' > updates.partitioned.sql
psql -U ... -d ... -f updates.partitioned.sql

---

Postgres uses MVCC (multi-version concurrency control), thus avoiding any locking if you are the only writer; any number of concurrent readers can work on the table, and there won't be any locking.

So if it really takes 5h, it must be for a different reason (e.g. that you do have concurrent writes, contrary to your claim that you don't).

---

I am by no means a DBA, but a database design where you'd frequently have to update 35 million rows might have… issues.

A simple WHERE status IS NOT NULL might speed up things quite a bit (provided you have an index on status) – not knowing the actual use case, I'm assuming if this is run frequently, a great part of the 35 million rows might already have a null status.

However, you can make loops within the query via the LOOP statement. I'll just cook up a small example:

CREATE OR REPLACE FUNCTION nullstatus(count INTEGER) RETURNS integer AS $$
DECLARE
    i INTEGER := 0;
BEGIN
    FOR i IN 0..(count/1000 + 1) LOOP
        UPDATE orders SET status = null WHERE (order_id > (i*1000) and order_id <((i+1)*1000));
        RAISE NOTICE 'Count: % and i: %', count,i;
    END LOOP;
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

It can then be run by doing something akin to:

SELECT nullstatus(35000000);

You might want to select the row count, but beware that the exact row count can take a lot of time. The PostgreSQL wiki has an article about slow counting and how to avoid it.

Also, the RAISE NOTICE part is just there to keep track on how far along the script is. If you're not monitoring the notices, or do not care, it would be better to leave it out.

---

Are you sure this is because of locking? I don't think so and there's many other possible reasons. To find out you can always try to do just the locking. Try this: BEGIN; SELECT NOW(); SELECT * FROM order FOR UPDATE; SELECT NOW(); ROLLBACK;

To understand what's really happening you should run an EXPLAIN first (EXPLAIN UPDATE orders SET status...) and/or EXPLAIN ANALYZE. Maybe you'll find out that you don't have enough memory to do the UPDATE efficiently. If so, SET work_mem TO 'xxxMB'; might be a simple solution.

Also, tail the PostgreSQL log to see if some performance related problems occurs.

---

Some options that haven't been mentioned:

Use the new table trick. Probably what you'd have to do in your case is write some triggers to handle it so that changes to the original table also go propagated to your table copy, something like that... (percona is an example of something that does it the trigger way). Another option might be the "create a new column then replace the old one with it" trick, to avoid locks (unclear if helps with speed).

Possibly calculate the max ID, then generate "all the queries you need" and pass them in as a single query like update X set Y = NULL where ID < 10000 and ID >= 0; update X set Y = NULL where ID < 20000 and ID > 10000; ... then it might not do as much locking, and still be all SQL, though you do have extra logic up front to do it :(

---

PostgreSQL version 11 handles this for you automatically with the Fast ALTER TABLE ADD COLUMN with a non-NULL default feature. Please do upgrade to version 11 if possible.

An explanation is provided in this blog post.

참고URL : https://stackoverflow.com/questions/1113277/how-do-i-do-large-non-blocking-updates-in-postgresql