[PostgreSQL] DB Lock을 줄이는 7가지 팁

1. Default 값이 있는 필드를 추가하면 안된다

PostgreSQL 10이하 버전에서, Default 값이 있는 필드를 추가하면, 테이블 락이 걸릴 수 있다. 그리고 엄청 느리다.

그래서 이런 쿼리를 날리면 안된다.

ALTER TABLE items ADD COLUMN updated_at timestamptz DEFAULT now();

 

기본값이 없는 필드를 추가한 후, UPDATE를 날리거나

ALTER TABLE items ADD COLUMN updated_at timestamptz; 

UPDATE items SET updated_at = now();

 

이렇게 쪼개서, UPDATE를 해주는게 좋다. ( 한번에 전체 테이블을 Update를 하면, 오래걸리니까. )

do { 
	numRowsUpdated = executeUpdate( 
    	"UPDATE items SET updated_at = ? " + 
        "WHERE ctid IN (SELECT ctid FROM items WHERE updated_at IS NULL LIMIT 5000)", 
        now); 
   } while (numRowsUpdate > 0);

 

2. Lock 때문에 트랜잭션들이 큐에 쌓이는걸 피하려면, Lock에 TimeOut을 거는게 좋다.

트랜잭션 A가 락을 쥐고 있는데, 트랜잭션 B가 A에서 쥐고있는 Lock Level과 컨플릭 나는 Lock Level을 얻어야하는 경우, 트랜잭션 B는 트랜잭션 A가 쥐고 있는 Lock이 풀릴 때까지 기다린다.

이때 트랜잭션 C가 트랜잭션 B가 얻어야하는 Lock과 컨플릭나는 Lock을 얻어야하는 경우 트랜잭션 C도 대기해야한다.

트랜잭션 C가 필요로하는 Lock Level이 트랜잭션 A의 Lock Level과 충돌나지 않더라도, 그냥 대기해야한다.

그러면 큐가 무한정 쌓이게된다.

그래서 이렇게 하는게 아니라

ALTER TABLE items ADD COLUMN updated_at timestamptz DEFAULT now();

이렇게 타임아웃을 넣어주는게 좋다.

SET lock_timeout TO '2s' ALTER TABLE items ADD COLUMN updated_at timestamptz;

 

3. 인덱스를 추가할 때, CONCURRENTLY 옵션을 넣어주는게 좋다.

큰 테이블에 인덱스를 추가하는 경우, 길면 며칠 동안 인덱싱이 되는 경우가 있다.

일반적인 CREATE INDEX 실행문은, 실행되는 동안 모든 Write를 막는다.

이렇게 CREATE INDEX를 하면 모든 updates/inserts/deletes을 막지만

CREATE INDEX items_value_idx ON items USING GIN (value jsonb_path_ops);

CONCURRENTLY CREATE INDEX을 쓰면, DDL만 막는다.

CREATE INDEX CONCURRENTLY items_value_idx ON items USING GIN (value jsonb_path_ops);

 

Postgresql 문서에는 있으나 버전별로 CONCURRENTLY 옵션을 사용하지 못하는 문제에 대해 더 많은 자료가 필요함.

CREATE INDEX CONCURRENTLY -- PostgreSQL 12 버전이상에서만 가능한 명령어.

일반적으로 인덱스 만들기는 해당 테이블을 읽기 전용으로 잠근다. 인덱스를 만드는 중에는 해당 테이블에 대해 쓰기 작업이 금지되며 읽기 작업(SELECT)은 가능하나 INSERT, UPDATE, DELETE 같은 DDL 작업은 인덱스 생성이 완료될 때 까지 대기상태가 된다. 이런 환경은 운영 환경에서 여러 문제점을 발생시키며, 해당 테이블의 크기에 따라 인덱스 생성 작업은 몇 시간이 걸릴 수도 있다. 

이런 문제를 해결하기 위해 테이블을 잠그지 않고 인덱스를 만드는 방법을 제공하는데 CREATE INDEX 명령에서 CONCURRENTLY 옵션을 사용하는 것이다. PostgreSQL 서버는 해당 테이블에 대해 두번 탐색을 수행하며 인덱스 작업 전 수행된 트랜잭션들이 모두 종료될 때 까지 기다렸다가 작업을 진행한다. (명령을 실행하고, 다른 세션에서 이 명령으로 인덱스 만들기 작업을 시작했는지, 다른 트랜잭션들 때문에 이 명령이 대기 중인지 확인할 필요가 있다.) 이런 이유로, 테이블을 잠그는 일반적인 인덱스 만들기보다 시간이 더 많이 걸린다. 하지만, 이렇게 인덱스를 만들면, 만드는 동안에도 일반적인 작업들을 할 수 있어, 운영 환경에서 새 인덱스를 만들 때 유용하게 쓰인다. 물론 인덱스 만드는 작업에 따른 CPU, I/O 추가 비용은 발생하고, 이 때문에, 다른 작업들이 느려질 수 있다.

이 테이블을 잠그지 않는 인덱스 만들기는 하나의 트랜잭션으로 해당 인덱스가 시스템 카탈로그에 추가되고, 다음, 두번의 테이블 탐색 작업은 두 개 이상의 트랜잭션으로 처리된다. 첫번째 테이블 탐색 전에, 인덱스 만들기 작업은 이미 있는, 해당 테이블 대상 변경 작업용 모든 트랜잭션이 종료되기를 기다린다. 두번째 테이블 탐색 후에, 인덱스 만들기 작업은, 이 테이블 탐색이 끝나기 전에 만들어진 모든 스냅샷 트랜잭션이 종료 되기를 기다린다. 모든 작업이 완료되면, 해당 인덱스를 사용 가능한 인덱스로 표시하고, CREATE INDEX 명령을 종료한다. 하지만, 그 인덱스가 즉시 쿼리에서 사용되지 않을 수도 있다: 최악의 경우는 해당 테이블을 사용하는 트랜잭션이 제때에 종료되지 않아, 아에 해당 인덱스를 사용하지 못하는 상황이 발생할 수도 있다.

인덱스 만들기 작업 중 교착상태 deadlock나 유니크 제약 조건 위반 같은 문제가 발생하면, 그 작업은 중지된다. 하지만, 이 때 해당 인덱스를 없애는 것이 아니라, “INVALID” 인덱스로 남겨둔다. 이 인덱스는 불완적한 것이기 때문에 쿼리에서 사용되지는 않지만, 인덱스 변경 작업은 계속 되어 불필요한 비용이 발생한다. psql에서 이런 잘못된 인덱스를 확인하는 방법은 \d 명령 결과에서 인덱스 설명에 INVALID가 있는지 확인하는 것이다.

postgres=# \d tab
       Table "public.tab"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 col    | integer |           |          | 
Indexes:
    "idx" btree (col) INVALID

이런 경우 복구 방법은 해당 인덱스를 지우고, 다시 CREATE INDEX CONCURRENTLY 명령을 실행하는 것이다. (다른 방법은 REINDEX 명령을 사용하는 것인데, REINDEX 명령은 테이블을 잠그기 때문에 잘 판단 해야 한다.)

테이블을 잠그지 않고, 유니크 인덱스를 만들 때 주의해야할 또 다른 사항은, 두번째 테이블 탐색 전에 이미 다른 트랜잭션에서 유니크 제약 조건을 위반하는 경우가 발생할 수 있다는 것이다. 인덱스를 만들고 유효한 인덱스로 표시하기 전이나, 아니면, 아에 인덱스를 만들기 작업 실패로 처리하기 전에, 다른 쿼리에서 유니크 제약 조건 위반 오류가 발생할 수 있다. 또한, 이 실패가 두번째 탐색 작업 중에 발생하면, 이 “잘못된” 인덱스 때문에, 향후 계속 해서 유니크 제약 조건 위반 문제가 발생할 수도 있다.

표현식 기반 인덱스나 부분 인덱스도 해당 테이블을 잠그지 않고 만들수 있다. 이 때도 유니크 제약 조건 문제처럼 이 비슷한 문제가 생길 수 있기 때문에 주의해야 한다.

테이블을 잠그는 일반 인덱스 만들기는 동일 테이블에 대해서 여러 인덱스를 동시에 만들 수 있지만, 테이블을 잠그지 않는 인덱스 만들기는 한 테이블에 대해서 하나의 인덱스만 만들 수 있다. 모든 인덱스 만들기 작업 중에는 테이블 정의 변경 작업은 불가능하다. 테이블을 잠그는 일반 인덱스 만들기는 트랜잭션 내에서 사용할 수 있지만, 테이블을 잠그지 않는 인덱스 만들기는 트랜잭션 내에서 사용할 수 없다.

 

4. 부하가 걸릴만한 Lock은 최대한 늦게 잡는게 좋다

테이블의 모든 레코드를 지우고, 기존 파일을 덤프뜨는 방식보다는

BEGIN;
-- reads and writes blocked from here: 
TRUNCATE items; 
-- long-running operation: 
\COPY items 
FROM 'newdata.csv' WITH CSV
COMMIT;

 

새로운 테이블을 만들어서 기존 파일을 덤프뜨고, 테이블명을 바꿔치기하는게 더 좋다.

BEGIN; 

CREATE TABLE items_new (LIKE items INCLUDING ALL); 
-- long-running operation: 
\COPY items_new FROM 'newdata.csv' WITH CSV 
-- reads and writes blocked from here: 
DROP TABLE items; 
ALTER TABLE items_new RENAME TO items; 

COMMIT;

 

5. 기존 테이블에 PK를 추가할 때는, 인덱스를 만들고, 인덱스를 PK로 걸자.

아래처럼 기존 테이블에 PK를 추가하는 경우, 테이블 크기가 크면 시간이 오래걸릴 수도 있다. 그러면, 아래의 명령문이 실행되는 동안 모든 쿼리가 블락된다.

ALTER TABLE items ADD PRIMARY KEY (id);

 

그래서 아래처럼 인덱스를 추가하고, 인덱스를 PK로 바꿔주는게 좋다.

CREATE UNIQUE INDEX CONCURRENTLY items_pk ON items (id); 

ALTER TABLE items ADD CONSTRAINT items_pk PRIMARY KEY USING INDEX items_pk;

PK 생성 방식을 두단계로 나누면, 사용자에게 큰 영향을 주지 않는다. ( 인덱스 만들 때, CONCURRENTLY을 넣어줘야 빠르다! )

 

6. VACUUM FULL은 절대 쓰면 안된다

VACCUM FULL은 디스트에 전체 테이블을 다시 쓰기 때문에, 이 실행문이 완료될때까지 며칠이 걸릴 수도 있다. 그리고 그 사이에 모든 쿼리가 막히게 된다.

필요하다면 VACUUM을 써야지, VACUUM FULL은 쓰면 안된다.

 

7. 데드락을 발생시킬만한 순서로 쿼리를 쓰지 말자

BEGIN; 
UPDATE items SET counter = counter + 1 WHERE key = 'americano'; 
UPDATE items SET counter = counter + 1 WHERE key = 'people'; 
END;

BEGIN;
UPDATE items SET counter = counter + 1 WHERE key = 'people'; 
UPDATE items SET counter = counter + 1 WHERE key = 'americano'; 
END;

요런 순서로 두 트랜잭션이 동시에 돌면, 서로의 LOCK을 얻으려고, 무한정 기다리게됨

 

출처: https://americanopeople.tistory.com/292?category=695027