본문 바로가기

소프트웨어-이야기/데이터 저장소 + 시각화

(PostgreSQL) DB Lock을 줄이는 7가지 팁

해당 글은 When Postgres blocks: 7 tips for dealing with locks을 보고 정리한 글입니다

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);

참고 - Explaining CREATE INDEX CONCURRENTLY

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을 얻으려고, 무한정 기다릴거다.

When Postgres blocks: 7 tips for dealing with locks